Java Database Connectivity (JDBC)

• Java Database Connectivity (JDBC) is an Application Programming Interface (API) used to connect Java application with Database. JDBC is used to interact with the various type of Database such as Oracle, MS Access, My SQL and SQL Server and it can be stated as the platform-independent interface between a relational database and Java programming.

• This JDBC blog is designed for Java programmers who would like to understand the JDBC framework in detail along with its architecture and actual usage.

• Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database. It is part of the Java Standard Edition platform, from Oracle Corporation. It provided methods to query and update data in a database and related towards relational databases. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the Java virtual machine (JVM) host environment.

1.  Create a connection to a database.
2.  Creating SQL or MySQL statements.
3.  Executing SQL or MySQL queries in the database.
4.  Viewing & modifying the resulting records.

JDBC is a specification that provides a comprehensive set of interfaces that allows for portable access to an underlying database. Java can be used to write different types of executable, such as:

1. Java Applications

2. Java Applets

3. Java Servlets

4. Java Server Pages (JSPs)

5. Enterprise JavaBeans (EJBs).

• Having a direct relationship means you directly connect from the client to the database without an average service. To get a direct connection to a database, a programmer must have the correct direct connect drivers installed on the connecting client.

• When attached to a database server and the databases stored on it, you must use a Windows-authenticated login. It is a process of identifying an individual user with credentials supplied by the Windows operating system of the user's computer. Hence, the login with which you log in to the client computer is the login that uses the connection. When connecting to a remote database server make use of a domain login. When connecting to a local database server, use either a local or a domain login. If you use a domain login while connecting to a local database server, you may not be able to log into the database server.

• When connecting to database servers which reside on the same computer as the connecting client application, make use of a local or domain account to log in.

• For example, if your local login is mymachine\myuser, creating a login with the same name on the remote machine, your computer, results in this login: yourmachine\myuser. These are essentially two different login names.

• The JDBC API support both two-tier and three-tier processing models for database access but in usually, JDBC Architecture consists of two layers:

1. JDBC Driver API: JDBC Driver API supports the JDBC Manager-to-Driver Connection.

2. JDBC API: This provides the application-to-JDBC Manager connection.

• The JDBC API applies a database-specific drivers and driver manager to provide good connectivity to create heterogeneous databases.

• The JDBC driver manager secures that the correct driver is used to obtain each data source. The driver manager is able to support multiple concurrent drivers connected to multiple heterogeneous databases.
• Following is the JDBC architectural diagram, which shows the location of the driver manager on the JDBC drivers and the Java application:

• Driver Manager: This class manages a list of database drivers. Matches connection requests from the Java application with the proper database driver using communication sub protocol. The first driver that recognizes a certain sub protocol under JDBC will be used to establish a database Connection.

• Driver: This interface handles the connections with the database server. You will interact directly with Driver objects very rarely. Instead, JDBC uses Driver Manager objects, which manages objects of this type. It also abstracts the details associated with working with Driver objects.

• Connection: This interface with all methods for contacting a database. The connection object represents communication context, i.e., all communication with the database is through connection object only.

• Statement: You use objects created to submit the SQL statements to the database. Some derived interfaces accept parameters in addition to executing stored procedures.

• ResultSet: These objects hold data retrieved from a database after you execute an SQL query using Statement objects. It acts as an iterator to allow you to move through its data.

• SQLException: This class handles any errors that occur in a database application.

• JDBC driver is used for interacting with the database server which implements the stated interfaces in the JDBC API.

For example, JDBC drivers interact with the database to open database connections by sending SQL or database commands and receive the results with Java:

• Any proprietary APIs are implemented by a JDBC driver.

There are four different types of JDBC drivers:

1. Type 1: JDBC - ODBC bridge driver: 

                  A thin Java layer that uses an ODBC driver under the hood.

2. Type 2: Java - Native code driver: 

                 Provides an interface from Java to the native database client.

3. Type 3: Network - Protocol driver (Middleware driver):

                  A universal interface (“middleware”) between Java and the RDBMS’s vendor-specific protocol.

4. Type 4: Database - Protocol driver (Pure Java driver):

                  A driver that implements the vendor-specific protocol directly in Java.