JDBC ( Java Database Connectivity)

1. Prepared by : Raithatha Yash Roll No : 6540 JDBC( Java Database Connectivity)

2. What is JDBC ? • A java API that provides cross DBMS connectivity to a wide varity of SQL Databases • It contains various java classes & interfaces which can be implemented by vendors of different DBMS • It is included in J2SE & J2EE both Yash Raithatha ,6540

3. History • Microsoft ODBC • ODBC uses CLI specifications from SQL Access group • ODBC in C so no Platform independency • Sun decided to make such an API through JAVA code Yash Raithatha ,6540

4. JDBC Driver • The JDBC Driver provides vendor-specific implementations of the abstract classes & interfaces provided by the JDBC API. • Used to connect to the database. • To connect different DBMS we need to have the corresponding JDBC driver which is generally provided by the vendor of DBMS Yash Raithatha ,6540

5. Yash Raithatha ,6540

6. JDBC Architecture Application JDBC Driver • Java code calls JDBC library • JDBC loads a driver • Driver talks to a particular database • Can have more than one driver -> more than one database • Best : can change database engines without changing any application code Yash Raithatha ,6540

7. JDBC Drivers • Type I: “Bridge” • Type II: “Native” • Type III: “Middleware” • Type IV: “Pure” Yash Raithatha ,6540

8. Type I Drivers i.e Bridge • Converts JDBC calls into ODBC function calls which in turn communicates with the ODBC driver • ODBC driver manipulates the database Data Source JDBC ODBC ODBC Driver Yash Raithatha ,6540

9. Advantages & disadvantages Advantages if JDBC driver for any DBMS is not available easy to install Disadvantages ODBC driver needs to be installed on client Application will become platform dependent as ODBC driver is bound with machine Performance overhead Yash Raithatha ,6540

10. Type II Drivers i.e Native • Converts JDBC calls into native Database API calls using CLI library • Not written entirely in java • Usually not thread-safe • Mostly obsolete now • e.g. Intersolv Oracle Driver, WebLogic drivers Data source Native DB API JDBC Yash Raithatha ,6540

11. Advantages & disadvantages Advantages Better Performance than type 1 driver Disadvantages No Platform independency  Vendor client library needs to be installed on the client Yash Raithatha ,6540

12. Type III Drivers i.e. Middleware • Convert JDBC calls into server protocol which in turn converts them to DBMS specific protocol • Written entirely in java • Best :Only need to download one driver to support multiple database e.g. Symantec DBAnywhere Yash Raithatha ,6540

13. MiddleWare DS1 Middleware Server DS 2 JDBC DS 3 Yash Raithatha ,6540

14. Advantages & disadvantages Advantages No need for vendor db library on client machine middle ware services like caching , loading ,auditing , etc Disadvantage database specific coding must be done in middle ware server Yash Raithatha ,6540

15. Type IV Drivers • 100% Pure Java -- the Holy Grail • Use Java networking libraries to talk directly to database engines • Only disadvantage: need to download a new driver for each database engine • e.g. Oracle, mSQL Data Source JDBC Yash Raithatha ,6540

16. JDBC JDBC Drivers (Fig.) Type I “Bridge” ODBC ODBC Driver Type II “Native” CLI (.lib) Middleware Server Type III “Middleware” Type IV “Pure” Yash Raithatha ,6540

17. JDBC URLs jdbc:subprotocol:source • each driver has its own subprotocol • each subprotocol has its own syntax for the source jdbc:odbc:DataSource • e.g. jdbc:odbc:Northwind jdbc:msql://host[:port]/database • e.g. jdbc:msql://foo.nowhere.com:4333/accounting Yash Raithatha ,6540

18. java.sql • JDBC is implemented via classes & interfaces in the java.sql package Yash Raithatha ,6540

19. JDBC Object Classes • DriverManager • Loads, chooses drivers • Driver • connects to actual database • Connection • a series of SQL statements to and from the DB • Statement • a single SQL statement • ResultSet • the records returned from a Statement Yash Raithatha ,6540

20. DriverManager • DriverManager tries all the drivers • Uses the first one that works • When a driver class is first loaded, it registers itself with the DriverManager • Therefore, to register a driver, just load it! Yash Raithatha ,6540

21. Driver Driver d = new foo.bar.MyDriver(); Connection c = d.connect(...); • Not recommended, use DriverManager instead • Useful if you know you want a particular driver Yash Raithatha ,6540

22. Connection • A Connection represents a session with a specific database. • Within the context of a Connection, SQL statements are executed and results are returned. • Can have multiple connections to a database • Also provides “metadata” -- information about the database, tables, and fields • Also methods to deal with transactions Yash Raithatha ,6540

23. Statement • A Statement object is used for executing a static SQL statement and obtaining the results produced by it. Yash Raithatha ,6540

24. ResultSet • A ResultSet provides access to a table of data generated by executing a Statement. • Only one ResultSet per Statement can be open at once. • A ResultSet maintains a cursor pointing to its current row of data. • The 'next' method moves the cursor to the next row. Yash Raithatha ,6540

25. Example • String DBDriver = "sun.jdbc.odbc.JdbcOdbcDriver"; • String url = "jdbc:odbc:;DRIVER=Microsoft Access Driver (*.mdb);DBQ=c:/My Project/mydata.mdb"; • Connection conn; Yash Raithatha ,6540

26. try{ Class.forName(DBDriver); conn = DriverManager.getConnection(url); } catch(Exception e){ JOptionPane.showMessageDialog(null, "Error in establishing Connection"); } // catch ends Yash Raithatha ,6540

27. try{ String sql = “ Your SQL Query “; PreparedStatementps = conn.prepareStatement(sql); ResultSetrs = ps.executeQuery( ); Or introwsAffeted=ps.executeUpdate( ); } Catch(Exception e){ } Yash Raithatha ,6540

28. Any Questions ? Yash Raithatha ,6540