^

JDBC [Java Database Connectivity]

Up to now , you have created simple Java applications, which input data from user, Process it and display output on the screen . But in some Java applications, it is desirable to state the processed date permanently somewhere else. You can do it either using file system or using a database management system, such as Oracle, My-Sql, Ms-access and DB2 etc. Using database is more effective to manage records in a Java application than file system. For example Search engines use databases to store information about products, customers and orders and the list goes on.
In this chapter, you will learn how to work with relational data bases. You can use any database such as Oracle, My-sql, SQL-Server and Ms-Access . Java has an API for working with database and this technology is known as JDBC (Java Data Base Connectivity).

What is JDBC

JDBC (Java Database Connectivity) is a set of JavaAPI’s that enables the developers to create platform and database independent application in java. For Example, an application written to access the Ms-Access database on windows platform can work on Linux operating system, against oracle database, only by changing the name of driver in code. Thus JDBC is Sun’s attempt to write a platform – neutral interface between databases and Java to create Java applications.

JDBC Driver

JDBC API – defines a set of interfaces that encapsulates major database functionality , including running queries processing results and setting configuration information. A database vendor or a third party developer, writes a JDBC driver for a particular database, which is a set of classes that implements the interfaces provided by the JDBC API. The following figure shows how an application uses JDBC to interact with different databases using their specific drivers.
Depicts that using JDBC API a Java application can interact with database to save and retrive data from database tables. Figure – 1 it is also clear that different database provides corresponding database drives to enable JDBC API to communicate with the underlying database management system. i.e Oracle, DB2, My-Sql MS-Access etc.
Types of JDBC Drivers
There are Four Categories of Drivers:
  1. Type 1 : JDBC – ODBC Bridge Driver.
  2. Type 2 : JDBC – Native API Driver.
  3. Type 3 : JDBC – Network Protocol Driver.
  4. Type 4 : JDBC – Native Protocol Driver (Pure Java Driver).
1. Type 1 : JDBC – ODBC Bridge Driver
Type 1 driver is also known as JDBC-ODBC bridge driver. In type 1, a JDBC bridge is used to access ODBC drivers installed on a client machine. Sun provides a JDBC-ODBC Bridge driver: “sun.jdbc.odbc.Driver”. This driver is platform dependent because it uses ODBC, which depends on native libraries of operating system. It is used only when there is no PURE-JAVA driver available for a specific database eg Ms-Access.
Advantages:
  1. It can connect to any database, for which ODBC driver is installed
  2. Easy to install and use, as compared to others.
Disadvantages:
  1. ODBC driver needs to installed.
  2. It is not purely platform independent because ODBC depends on native libraries.
2. Type 2: JDBC – Native API Driver:
In type 2, JDBC API, Calls are converted into native C/C++ API Calls. Native C/C++ APIs are database specific. These driver are Provided by the database vendors. To communicate with database, its corresponding drivers must be installed on client machine. Type 2 is more speedy as compared to type 1, because it removes ODBC overhead . The Oracle Call Interface (OCI) drivers is an example of Type 2 Driver.
Advantage:
1. Its performance is better than Type 1.
Disadvantages:
1. Extra installation of database specific native API(Driver) is required.
2. It cannot be used in web based applications.
3. Type 3: JDBC – Network Protocol Driver
Type 3 driver is a tier approach used to interact with database. It uses a middle tier i.e. application server ( Such as web logic ) between the java program and a database. A Java Program (JDBC Client) use Standard sockets to communicate with middle tier (application server). The application server receives information from the socket and translate it into format required by the specific database server. Type 3 driver is more flexible and efficient because it can be used to access multiple database.
Advantage:
1. No need to install database specific library on client machine, because middle tier communication with database
2. Type 3 driver can be used in any web application.
3. The application server also provides auditing, logging and load balancing services.
Disadvanges:
1. Communication speed can be slow due to an addition layer added.
2. To devlop an effective middle tier, the database specific coding is required, which increased the complexity.
4. Type 4: Native protocol Driver ( Pure Java Driver )
Type 4 is pure Java-based driver. It communicates with database server directly through Socket Connection on specific port. This is the fastest port. This is the fastest way to communicate with database without any intermediary system. It is completely written in java, that is why also known as “Direct to database pure Java driver”. This is highest performance driver usually provided by the database vendor itself.
Modern JDBC driver are type 4. My Sql’s Connector “JDriver” and “Oracle Thin” are type 4 driver for Oracle 8i, 9i and 10g database.
Advantages:
1. It is platform independent because it is 100% pure Java Driver.
2. No more translation layer needed, which make it faster.
Disadvantages:
1. A separate driver is required for each database.
2. Different database vendors use different network protocols.
Version of JDBC:
The following are the version and implementation of past version:
Year JDBC Version JDK Implementation
2006 JDBC 4.0 Java SE 6/JDK 1.6
2001 JDBC 3.0 JDK 1.4
1999 JDBC 2.1 JDK 1.2
1997 JDBC 1.2 JDK 1.1
Main features of JDBC 4.0 API:
1. Automatic loading of JDBC Drivers.
2. JDBC 4.0 introduced the concept of wrapped JDBC objects.
3. Added new streaming methods in callableStatement , PreparedStatement and ResultSet classes.
4. Added new methods in Connection , DatabaseMetadata and Statement.

JDBC API

JDBC API (Application programming Interface) is a collection of pre-written classes and interfaces with their respective methods to connect with a database. These classes and interfaces are available in java.sql package. Some of the commonly used classes and interfaces are listed out below to program various tasks with databases :-
Interface/Class Description
java.sql.Connection (Interface) Used to create connection with specific database.
java.sql.DriverManager (Class) Its basic service is to manage the set of JDBC drivers.
java.sql.Statement (Interface) It is used to execute a static SQL statement and retrieve the result.
java.sql.PreparedStatement (Interface) It represents a pre-compiled SQL statement.
java.sql.CallableStatement (Interface) It is used to execute stored procedures.
java.sql.ResultSet (Interface) It is represented a table of data generated by executing a query.
java.sql.ResultSetMetaData (Interface) It is used to get information about ResultSet.
Java.sql.DataBaseMetaData (Interface) It is used to collect information about the database.

Java.sql.DriverManager Class

Java.sql.DriverManager class extends java.lang.Object class. Its basic job is to manage a set of JDBC drivers. As a parts of its initialization, the DriverManager class attempts to load the driver. A program can load JDBC drivers at any time.
For Example:
Drivers for “My-Sql Database” can be loaded with the following statement:
Class.forName(“com.mysql.jdbc.Driver”);
In the above statement, Driver is a class of “com.mysql.jdbc” package provided by the My-Sql database vendor. Note: Do not forget to copy “mysqlConnector.jar” (must download it from google) in your “jdkFolder\jre\lib\ext” folder. Driver for “Oracle Database” can be loaded with the following statement:-
Class.forName(“com.mysql.jdbc.Driver”);
In the above statement, OracleDriver is a class of “oracle.jdbc.driver” package, provided by the Oracle database vendor.
When the method getConnection() is called, the DriverManager will attempt to locate a suitable driver from those loaded at initialization.
DriverManager class provides the following commonly used methods:
Method Description
Public void deregister Driver (Driver obj) throws SQL Exception Drops a Driver from the DriverManager’s list.
Public static synchronized void registerDriver (Driver Obj) throws SQLException Register the given driver with the DriverManager.
Connection getConnection (String url, String uid, String pwd) throws SQLException Attempts to establish a connection to the given database URL.
Void registerDriver (Driver obj) throws SQL Exception Register the given driver with the DriverManager.

About the Author
Rajesh K. Bansal (SCJP-Sun Certified Java Programmer)
20 Years experience in Training & Development. Founder of realJavaOnline.com, loves coding in Java(J2SE, J2EE), C++,PHP, Python, AngularJS, Android,MERN Stack(MongoDB,Express,ReactJS,NodeJS). If you like tutorials and want to know more in depth about Java , buy his book "Real Java" available on amazon.in.
#Email : bcebti@gmail.com #Contact : 98722-46056
Available on Amazon
Card image cap
Under the guidance of Founder & Author of "realJavaOnline.com". M:9872246056
Card image cap