JDBC

1. JDBC Java API for Database Connectivity

2. Layout of this recitation • Introduction to JDBC API • JDBC Architecture • Understanding the design of JDBC API • Classes and relations among them

3. What is an API? • application program interface • a set of routines, protocols, and tools for building software applications • provides all the building blocks

4. What you expect in DB API Java Application Java DB API • Open a Connection • Send a statement • Retrieve results • Close a connection DBMS Engine • Create a connection Session • Execute statement • Send results • Close the session

5. Big picture Java Application JDBC API JDBC Manager JDBC Driver API JDBC Drivers Grey Area Data Sources

6. JDBC driver type-1 • Uses the existing API • Not the fastest JDBC-ODBC Bridge Java ODBC Driver Native Driver Non Java Net Driver

7. JDBC driver type-2 • Uses vendor-provided local libraries • Most efficient JDBC Driver Java Native Driver Non Java Net Driver

8. JDBC driver type-3 & 4 • 100% java. JDBC – Net Driver Java JDBC Driver Implements a proprietary Protocol in java Java

9. Complete JDBC Architecture Java Application JDBC Interface JDBC Driver Manager JDBC Driver Interface JDBC Net Driver A JDBC–ODBC Bridge B JDBC Driver C JDBC Driver D ODBC Driver Native Driver C Native Driver B A B C D

10. On Different Platforms JDBC Driver Classes JDBC API PC DBMS MAC Unix

11. JDBC Interface classes • Java.sql package • DriverManager • Connection • Statement • CallableStatement • PreparedStatement • Resultset • ResultSetMetaData • DatabaseMetatData

12. JDBC Interfaces Driver Manager Connection Connection Connection Statement Prepared Statement ResultSet

13. Simple Example Import java.sql; class SimpleExample { public static void main(String args[]) { String url = “jdbc:odbc:mysource”; try { Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); Connection myConnection = DriverManager.getConnection(url,”Bond”,”TopSecret”); myConnection.close(); } catch(java.lang.Exception) { ex.printStackTrace(); } } }

14. Sending SQL statements …… String query= “Select name,id,salary FROM employees ORDER By salary”; Connection myConnection = DriverManager.getConnection(…..); Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery(query); While(rs.next){ String empName = rs.getString(1); String empId = rs.getString(2); String empSalary = rs.getString(3); System.out.println(“Employee ” + empName + “ with id ” + empId + “ earns ” + empSalary); } myStatement.close(); myConnection.close(); …….

15. Statement’s Execute Methods • ResultSet executeQuery() • For SQL selects • int executeUpdate() • For Update,Delete,Insert and Create etc. • boolean execute() • For either type • Returns resultSetisAvailable

16. Statements • Statement • PreparedStatement • CallableStatement

17. Additional stuff • Datatypes: basic to CLOB, BLOB • Transaction Management • DDA with/without cursors • Batch updates • Metadata • DatabaseMetaData • ResultSetMetaData • ParameterMetaData

18. Advanced Techniques • Connection Pooling • Dynamic SQL with Prepared Statements • Auto-generated keys

19. References • www.google.com • http://java.sun.com/j2se/1.4/docs/api/ • JDBC 3.0: Java Database Connectivity. • Bernard Van Haecke