Download JDBC: Java Database Connectivity - A Comprehensive Guide and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
JDBC
- JDBC is an API that enables database access
from Java programs
- JDBC for DB access provides
- Portability across database servers
- Portability across hardware architectures
- JDBC drivers have been implemented for a
number of DB servers (Oracle, SQL Server,
DB2, Access, etc.)
JDBC
- Java Database Connectivity (JDBC) has been
part of the standard Java standard edition
since JDK 1.
- Current version is JDBC 3.0 (as of J2SE 1.4)
- JDBC classes are contained in the Java package java.sql
- A JDBC-ODBC bridge is included so that any ODBC data source available in the JVM host environment can be accessed
JDBC
- Steps in JDBC applications
- Load JDBC classes (java.sql.*)
- Load the JDBC drivers
- Class.forName(“oracle.jdbc.driver.OracleDriver”)
- Connect to the database
- Connection conn = DriverManager.getConnection(url,userid,passwd)
- jdbc:oracle:drivertype@database
- Interact with DB using JDBC
- Disconnect from DB
JDBC Example
Connection conn = DriverManager.getConnection( "jdbc:oracle:oci8:"+user+"/"+pass); Statement stmt = conn.createStatement (); ResultSet rset = stmt.executeQuery ("select distinct eno,ename,zip,hdate from employees"); while (rset.next ()) { System.out.println(rset.getInt(1) + " " + rset.getString(2) + " " + rset.getInt(3) + " " + rset.getDate(4)); } stmt.close(); conn.close(); }
JDBC Example
//readEntry function -- to read input string static String readEntry(String prompt) { try { StringBuffer buffer = new StringBuffer(); System.out.print(prompt); System.out.flush(); int c = System.in.read(); while(c != '\n' && c != -1){ buffer.append((char)c); c = System.in.read(); } return buffer.toString().trim(); } catch (IOException e) { return ""; } } }
JDBC SQL Statement Classes
- There are three classes for sending SQL to the
DB server
- Statement. Used for SQL statements with no parameters.
- PreparedStatement. Used when the same statement, possibly with different parameters is to be called multiple times.
- CallableStatement. Used for calling stored procedures.
Other Connection Class Methods
- public abstract void close() throws SQLException
- Immediately release the Connection’s database and JDBC resources
- public abstract void setAutoCommit (boolean autoCommit) throws SQLException - Pass true to enable autocommit, false to disable it
- public abstract void rollback() throws SQLException
- Useful when autocommit is false
- public abstract DatabaseMetaData getMetaData() throws SQLException
ResultSet Class
- The result of an executeQuery is a single ResultSet
object
- This class provides access to the table resulting from the query
- A cursor points to the current row (initially positioned before the first row.
- Use the next method to fetch the next row
- The current row’s column values are retrieved using the getXXX methods, where XXX is a Java type. - Pass either an index or the column name to specify which column
ResultSet Class
- Use the wasNull method to check whether the
previously read column value was Null.
- Use getMetaData to obtain info like number,
types and properties of the columns of a
ResultSet
Errors and Warnings
try { some JDBC statement to access the DB; } catch (SQLException e) { System.out.println(“SQL Exception caught!”); while (e!= null) { System.out.println(“Error Message = “ + e.getMessage()); System.out.println(“SQL State = “ + e.getSQLState()); System.out.println(“Error Code = “ + e.getErrorCode()); e = e.getNextException(); } }
Errors and Warnings
- The SQLWarning class provides info about
warnings generated during DB access
- Use getMessage to get the warning string
- Use getSQLState to get warning according to X/Open SQL Spec
- Use getErrorCode for vendor specific code
- Use getNextWarning for next warning generated
