JDBC

1. JDBC CS-328

2. JDBC • Java API for accessing RDBMS • Allows use of SQL for RDBMS programming • Can be used for: • embedded SQL • execution of stored queries

3. SQL Statement Execution • Four primary classes used to • load a driver • (java.sql.DriverManager) • connect to the DB • (java.sql.Connection) • create a SQL statement • (java.sql.Statement) • execute a SQL statement • (java.sql.ResultSet)

4. Example try { Class.forName (“sun.jdbc.odbc.JdbcOdbcDriver”); String url = “jdbc:odbc:msaccessdb”; Connection con = DriverManager.getConnection(url,””,””); // create sql statement String qs = “select * from loadtest”; Statement stmt = con.createStatement(); // execute the statement ResultSet rs = stmt.executeQuery(qs); // process the result set boolean more = rs.next; while (more) { System.out.println ............}}

5. Driver Manager Object • Loads the proper driver into your java object • multiple drivers may be loaded to allow connections to multiple databases • Provides a common interface to RDBMSs for JDBC Drivers

6. Driver Manager Object JAVA Application Driver Manager Driver ‘A’ Driver ‘B’ Connection Connection Statement Statement Result Set Result Set

7. Driver Manager Class Methods • getConnection(String url) • getDriver • registerDriver • deregisterDriver • getDrivers • setLoginTimeout • getLoginTimeout • setLogStream • getLogStream

8. Connection Object • Establish link between the JAVA application and RDBMS • allows application to select proper driver when it needs to • uses the database URL to make the connection • jdbc:<subprotocol>:<subname) • jdbc:odbc:Mydatabase • jdbc:db2:157.287.43.11/Mydatabse

9. Connection Class Methods • createStatement • prepareStatement • prepareCall • NativeSQL • setAutoCommit • GetAutoCommit • commit • rollback • close • isclosed

10. Statement Object • Wrapper of a string containing a SQL statement • allows JDBC to decompose the SQL into a set of steps to present to the database via the driver • the connection object forwards the statement object to the database to obtain a results set object

11. Statement Class Methods • executeQuery • executeUpdate • insert, update, delete • close • getResultSet

12. ResultSet Object • A container for the rows and columns (a table) acquired as a result of presenting a statement object to the RDBMs using the “executeQuery” statement method

13. ResultSet Class Methods • next • close • wasNull • getString • getBoolean • getByte • getShort

14. ResultSet Class Methods(cont.) • getInt • getLong • getFloat • getDouble • getNumeric • getBytes • getDate

15. ResultSet Class Methods (cont.) • getTime • getTimeStamp • getAsciiStream • getUnicodeStream • getBinaryStream • getMetaData • etc...

16. Char - String Varchar - String Longvarchar - String Numeric - java.sql.Numeric Decimal - java.sql.Numeric Bit - boolean Tinyint - byte Smallint = short Integer - int Bigint - long Real - float Float - double Double - double Binary - byte[ ] JDBC Type Mapping

17. Varbinary - byte[ ] Longvarbinary - byte[ ] Date - java.sql.Date Time - java.sql.Time Timestamp - java.sql.Timestamp JDBC Type Mapping (cont.)