Introduction to JDBC: Understanding Database Connectivity in Java

1. Introduction To JDBC JDBC stands for Java Database Connectivity. In the summer of 1996, Sun released the first version of the Java Database Connectivity (JDBC) kit. JDBC is an API developed by Sun Microsystems to work with the most common type of database: the relational database. By- Rashmi Deshmukh

2. Cont… Various database vendors like Oracle, Informix, Sybase etc. provide their own JDBC drivers, which are implementation of the JDBC API for their database engines. The use of Java with JDBC has advantages over other database programming environments. By- Rashmi Deshmukh

3. Cont… The JDBC API makes it possible to do three things: • Establish a connection with a database or access any tabular data source • Send SQL statements • Process the results By- Rashmi Deshmukh

4. Cont… Programs developed with the Java programming language and JDBC are platform independent and vendor independent. The database can be transferred from one vendor to another and the same Java programs can be used without alteration. Programs written using the JDBC API communicate with a JDBC driver manager, which uses the current driver loaded. By- Rashmi Deshmukh

5. Cont… JDBC provides rich, object-oriented access to databases by defining classes and interfaces that represent objects such as: • Database connections • SQL statements • Result sets • Database metadata • Prepared statements • Binary Large Objects (BLOBs) • Character Large Objects (CLOBs) • Callable statements • Database drivers • Driver manager By- Rashmi Deshmukh

6. Cont… The JDBC API uses a driver manager and database- specific drivers to provide transparent connectivity to heterogeneous databases. JDBC driver manager ensures that the correct driver is used to access each data source. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases. By- Rashmi Deshmukh

7. Driver A driver is a program that converts the Java method calls to the corresponding method calls understandable by the database in use. The set of classes that implement the JDBC interfaces for a particular database engine is called a JDBC driver. A JDBC driver is a software component enabling a Java application to interact with a database. By- Rashmi Deshmukh

8. Conti… To connect with individual databases, JDBCAPI requires drivers for each database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database. By- Rashmi Deshmukh

9. Conti… Note:-- 1. A JDBC driver allows a Java application/client to communicate with a SQL database. 2. A JDBC driver is a Java class that implements the JDBC's java.sql.Driver interface. 3. A JDBC driver converts program (and typically SQL) requests for a particular database. By- Rashmi Deshmukh

10. The Design of JDBC • JDBC-to-database communication path By- Rashmi Deshmukh

11. Conti… • JavaSoft's JDBC consists of two layers: the JDBC API and the JDBC Driver Manager API. • The JDBC API is the top layer and is the programming interface in Java to structured query language (SQL) which is the standard for accessing relational databases. By- Rashmi Deshmukh

12. Conti… • The JDBC API communicates with the JDBC Driver Manager API, sending it various SQL statements. The manager communicates (transparent to the programmer) with the various third party drivers (provided by Database vendors like Oracle) that actually connect to the database and return the information from the query. By- Rashmi Deshmukh

13. Conti… • Some database vendors already have JDBC drivers (e.g. Oracle). • For those databases that do not have a JDBC driver, you need to install the database's ODBC driver (available for most databases) and the JDBC to ODBC bridge supplied by JavaSoft. By- Rashmi Deshmukh

14. Conti… There are two architectures to communicate with the database:- In the first architecture, the JDBC driver communicates directly with the database. The driver connects to the database and executes SQL statements on behalf of the Java program. Results are sent back from the driver to the driver manager and finally to the application. By- Rashmi Deshmukh

15. Conti… In the second architecture, the JDBC driver communicates with an ODBC driver via a "bridge". A single JDBC driver can communicate with multiple ODBC drivers. Each of the ODBC drivers execute SQL statements for specific databases. The results are sent back up the chain as before. By- Rashmi Deshmukh

16. Types of Drivers. Java has categorized drivers into four categories: A type 1 driver [JDBC/ODBC bridge] A type 2 driver [Native-API partly Java Driver] A type 3 driver [Net-Protocol All-Java Driver] A type 4 driver [Native Protocol All-Java Driver]-Thin Note:-Mostly we use Type1 drivers for general use and Thin drivers for database specific and security purposes. If we don’t want MS – DSN than we should use Type4. By- Rashmi Deshmukh

17. A type 1 driver [JDBC/ODBC bridge] By- Rashmi Deshmukh

18. Cont… The JDBC type 1 driver, also known as the JDBC-ODBC Bridge, is a database driver implementation that employs the ODBC driver to connect to the database. Translates all JDBC API calls to ODBC API calls and sends them to the ODBC driver. The JDBC-ODBC Bridge driver is recommended only for experimental use or when no other alternative is available. Relies on an ODBC driver to communicate with the database. Sun includes one such driver, the JDBC/ODBC bridge, with the JDK. However, the bridge requires deployment and proper configuration of an ODBC driver. By- Rashmi Deshmukh

19. Cont… Translates query obtained by JDBC into corresponding ODBC query, which is then handled by the ODBC driver. Sun provides a JDBC-ODBC Bridge driver. sun.jdbc.odbc.JdbcOdbcDriver This driver is native code and not Java, and is closed source. Client -> JDBC Driver -> ODBC Driver -> Database There is some overhead associated with the translation work to go from JDBC to ODBC. Type1 Driver - e.g. ODBC Bridge By- Rashmi Deshmukh

20. Advantages • Almost any database, for which ODBC driver is installed, can be accessed. • A type 1 driver is easy to install. By- Rashmi Deshmukh

21. Disadvantages • A performance issue is seen as a JDBC call goes through the bridge to the ODBC driver, then to the database, and this applies even in the reverse process. They are the slowest of all driver types. • The ODBC driver needs to be installed on the client machine. • Not good for the Web. • Since the Bridge driver is not written fully in Java, Type 1 drivers are not portable. By- Rashmi Deshmukh

22. A type 2 driver [Native-API partly Java Driver] The JDBC type 2 driver, also known as the Native-API driver, is a database driver implementation that uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API. Written partly in Java & partly in native code, that communicates with the client API of a database. Therefore, should install some platform-specific code in addition to Java library. The driver uses native ‘C’ language lib calls for conversion. Type2 Driver –e.g WebLogic By- Rashmi Deshmukh

23. Cont… By- Rashmi Deshmukh

24. Advantage Better performance than Type 1 Driver (JDBC-ODBC bridge):- Better performance than the type 1 driver as it does not have the overhead of the additional ODBC function calls. By- Rashmi Deshmukh

25. Disadvantages • The vendor client library needs to be installed on the client machine. • Cannot be used in web-based application due to the client side software needed. • Not all databases have a client side library • This driver is platform dependent. • If we change the Database we have to change the native api as it is specific to a database. By- Rashmi Deshmukh

26. A type 3 driver [Net-Protocol All-Java Driver] The JDBC type 3 driver, also known as the Pure Java Driver for Database Middleware. It is a database driver implementation which makes use of a middle tier between the calling program and the database. The middle-tier (application server) converts JDBC calls directly or indirectly into the vendor-specific database protocol. This differs from the type 4 driver in that the protocol conversion logic resides not at the client, but in the middle-tier. By- Rashmi Deshmukh

27. Cont… Like type 4 drivers, the type 3 driver is written entirely in Java. The same driver can be used for multiple databases. It depends on the number of databases the middleware has been configured to support. The type 3 driver is platform-independent as the platform- related differences are taken care by the middleware. Also, making use of the middleware provides additional advantages of security and firewall access. By- Rashmi Deshmukh

28. Cont… Follows a three tier communication approach. Can interface to multiple databases - Not vendor specific. The JDBC Client driver written in java, communicates with a middleware-net-server using a database independent protocol, and then this net server translates this request into database commands for that database. Thus the client driver to middleware communication is database independent. Client -> JDBC Driver -> Middleware-Net Server -> Any database,. By- Rashmi Deshmukh

29. Cont… By- Rashmi Deshmukh

30. Advantage Since the communication between client and the middleware server is database independent, there is no need for the vendor db library on the client machine. Also the client to middleware need not be changed for a new database. This driver is fully written in Java and hence Portable. It is suitable for the web. There are many opportunities to optimize portability, performance, and scalability. The net protocol can be designed to make the client JDBC driver very small and fast to load. Type4 Driver-e.g. Symantec DBAnywhere By- Rashmi Deshmukh

31. Cont… This driver is very flexible allows access to multiple databases using one driver. They are the most efficient amongst all driver types. By- Rashmi Deshmukh

32. Disadvantage Requires database-specific coding to be done in the middle tier. It requires another server application to install and maintain. Traversing the recordset may take longer, since the data comes through the backend server. By- Rashmi Deshmukh

33. A type 4 driver [Native Protocol All-Java Driver]-Thin The JDBC type 4 driver, also known as the Direct to Database Pure Java Driver, is a database driver implementation that converts JDBC calls directly into a vendor-specific database protocol. They install inside the Java Virtual Machine of the client. This provides better performance than the type 1 and type 2 drivers as it does not have the overhead of conversion of calls into ODBC or database API calls. Type 4 drivers, coded entirely in Java, communicate directly with a vendor's database, usually through socket connections. No translation or middleware layers are required, improving performance. By- Rashmi Deshmukh

34. Cont… The driver converts JDBC calls into the vendor-specific database protocol so that client applications can communicate directly with the database server. But driver usually comes only from DB-vendor. Most database vendors supply either a type 3 or type 4 driver with their database. Client -> Native-protocol JDBC Driver -> database server Type4 Driver-e.g. Oracle By- Rashmi Deshmukh

35. Cont… By- Rashmi Deshmukh

36. Advantage 1. The major benefit of using a type 4 jdbc drivers are that they are completely written in Java to achieve platform independence and eliminate deployment administration issues. It is most suitable for the web. 2. Better performance than the type 1 and type 2 drivers as it does not have the overhead of conversion of calls into ODBC or database API calls. 3. You don’t need to install special software on the client or server. Further, these drivers can be downloaded dynamically. 4. The JVM can manage all aspects of the application-to-database connection; this can facilitate debugging By- Rashmi Deshmukh

37. Disadvantage With type 4 drivers, the user needs a different driver for each database. Drivers are database dependent. By- Rashmi Deshmukh

38. ODBC • A driver manager for managing drivers for SQL based databases. • Developed by Microsoft to allow generic access to disparate database systems on windows platform. • J2SDK comes with JDBC-to-ODBC bridge database driver to allow a java program to access any ODBC data source. By- Rashmi Deshmukh

39. JDBC Vs ODBC • ODBC is a ‘C’ API • ODBC is hard to learn – because of low-level native ODBC. • ODBC most suited for only Windows platform • No platform independence By- Rashmi Deshmukh

40. Goal of using JDBC • In summary, the ultimate goal of the JDBC is to make possible the following: • Programmers can write applications in the Java programming language to access any database, using standard SQL statements following Java language conventions. (All JDBC drivers must support at least the entry-level version of SQL 92.) By- Rashmi Deshmukh

41. Typical Uses of JDBC • JDBC can be used in both applications and applets. • Applets that use JDBC are only able to open a database connection to the server from which they are downloaded. That means the Web server and the database server must be the same machine, which is not a typical setup. • Applications, on the other hand, have complete freedom to access remote database servers. By- Rashmi Deshmukh

42. Architecture ResultSet ResultSet Statement Statement Connection Driver Manager JDBC-ODBC Bridge Sybase Driver Oracle Driver ODBC Driver database database database By- Rashmi Deshmukh

43. Steps to use JDBC There are seven standard steps in querying databases: • Load the JDBC driver. • Define the connection URL. • Establish the connection. • Create a statement object. • Execute a query or update. • Process the results. • Close the connection. By- Rashmi Deshmukh

44. The JDBC Classes for Creating a Connection JDBC uses one class (java.sql.DriverManager) and two interfaces (java.sql.Driver and java.sql.Connection) for connecting to a database. 1. java.sql.Driver :- We have to deal with this class only when you are writing your own custom JDBC implementation. It simply gives JDBC a launching point for database connectivity by responding to DriverManager connection requests and provides information about the implementation in question. By- Rashmi Deshmukh

45. java.sql.DriverManager Unlike most other parts of JDBC, DriverManager is a class instead of an interface. It locates the driver for a particular database. Provides support for managing database connection. Provides a entry point for JDBC communication. Its main responsibility is to maintain a list of Driver implementations and present an application with one that matches a requested URL. By- Rashmi Deshmukh

46. Conti… Methods used: getConnection(Stringurl, String user, String password):- This method will try to establish a connection to the given database URL. The DriverManager provides registerDriver() and deregisterDriver() methods, which allow a Driver implementation to register itself with the DriverManager or remove itself from that list. You can get an enumeration of registered drivers through the getDrivers() method. By- Rashmi Deshmukh

47. Using DriverManager.registerDriver() //String className = "org.gjt.mm.mysql.Driver"; try {   // Registers the given driver with the DriverManager.   DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());   // here the class is loaded}catch (SQLException e) {   e.printStackTrace();} By- Rashmi Deshmukh

48. java.sql.Connection The Connection class represents a single logical database transaction. In other words, you use the Connection class for sending a series of SQL statements to the database and managing the committing or aborting of those statements. A connection (session) with a specific database. Within the context of a Connection, SQL statements are executed and results are returned. By- Rashmi Deshmukh

49. Conti… Methods :- 1.close() 2.commit() 3.rollback() 4.setAutoCommit(boolean) 5.createStatement() By- Rashmi Deshmukh

50. Conti… The Connection object has the following capabilities: • Creates SQL statements • Executes SQL queries, inserts, updates, and deletes • Handles commits and rollbacks • Provides metadata regarding the database connection • By default, a Connection object is in autocommit mode, which means it automatically commits changes after executing each statement. By- Rashmi Deshmukh