In this article, the readers will get a complete understanding of JDBC, the reasons for developing it, how it works, and the procedure to connect Java based applications with databases.
What defines JDBC?
Java Database Connectivity is an application programming interface that has been developed in Java and is mostly used to work with relational databases. Using JDBC, a Java developer can formulate SQL statements, obtain the data, and be able to insert, update or delete records in the database.
To begin with, JDBC is not categorized as a data management tool. Instead, it has been characterized as a collection of interfaces and classes that facilitates the link between a Java application and respective databases. Furthermore, the JDBC enables Java applications to issue SQL instructions to the native DBMS without the need to worry about the standard.
Also, the JDBC API allows users to make SQL queries to any DBMS and execute the statements. From the end-user point of view, this means that any Java application can work with any DBMS, for example, MySQL, PostgreSQL, Oracle, SQL Server, etc. There is a good amount of statistics that supports the claim that JDBC is common in Java web applications, Java enterprise applications, and Java desktop applications hence it is an important toolkit for Java developers.
JDBC Architecture
To add on, the JDBC architecture should be detailed with an aim of enabling Java applications to interact with databases; such as relational databases, Sybase and HP Neoview all of which allow for the interacting of objects. As a mechanism that enables multi-tier applications to interact with a variety of data sources, it is important to note that all these components are structured in a manner such that a Java program is not affected regardless of the database it uses.
The various architectures can also be highlighted based on the following areas of focus:
1. JDBC API:
This category encompasses all interfaces and classes that contain methods that enable connecting and issuing SQL commands to different databases and obtaining results that include fundamental components, e.g.
Connection , Statement , PreparedStatement , ResultSet among others.2. JDBC Driver:
JDBC driver means a range of classes that offer communication between Java applications and a particular DBMS by implementing JDBC interfaces. Variations in the types of drivers depend on different databases, but in all cases, API supports JDBC specification enabling Java applications access through the same API to different databases.
3. Database:
The database itself is the storage part of the system and is left with the responsibility of execution of SQL queries. JDBC interfaces with the database through its appropriate driver and adds up in the required interface to perform the action.
4. JDBC Connection Pool:
A connection pool is a technique that allows database connections to be pooled together instead of new connections created each time. Such an approach has its drawbacks because every time a query is made, a new connection is set up and torn down, instead of reusing a set of already opened connections.
Key Components of JDBC
As a Java developer to work with the databases through the JDBC, there are several components which one would need to use and that give a clear way to work with different databases.
1. Connection
The first object that has to be created while connecting a specific Java application to the database is the
Connection , which also helps in sending SQL Statements and retrieving the results. A connection object gives the session with the database which allows SQL statements to be sent and results to be retrieved. Connection also gives some functions to handle transactions, create statements and finalize/cancel the connection. DriverManager.getConnection() is the method that is commonly used by developers to connect to a specific database. The method would usually receive the URL of the database, the username and the password of the user. This functionality results in a specific Connection object that allows further engagement with the database.
2. Statement
If connecting is successful, the next phase requires a
Statement object to be constructed in order to send SQL queries through as there will be connections established. Connecting to the database allows the developing team to issue or run SQL commands such as SELECT, INSERT, UPDATE, DELETE as well as return the information received through the Result Set. This allows developers to create a Statement object as it passes SQL commands to the database while receiving any results in return. JDBC allows for the implementation of multiple forms of queries including:- Statement: Used to perform a basic select query which does not have required parameters.
- PreparedStatement: Used to perform a precompiled select query which has some required parameters.
- CallableStatement: Used to call specific database stored procedures.
3. ResultSet
The SQL query which has been executed through a Statement or PreparedStatement is stored in the
ResultSet object. When a SELECT query is processed against the database, the database returns a set of results with the name ResultSet that actually consists of all the rows which were obtained from the database. The ResultSet acts as a pointer through the rows of data and allows developers to interact with the dataset returned from the database.Furthermore,
ResultSet contains the methods for retrieving data stored in a particular column of the result set like getResult() , getIntResult() and so on which enables the developers in getting the data in the required form.4. PreparedStatement
A subclass of the
Statement interface which is called PreparedStatement is used to implement more complex SQL queries. This object is used to set the precompiled parameter, which makes the database more effective than simply using a Statement . Also, when using PreparedStatement , input parameters are passed through the query, but only in cases when the query has user input.Moreover, with the use of
PreparedStatement , the parameter is secured to the query through binding and thus a SQL Injection is less likely to occur.5. Transaction Management
Through JDBC, one has a capability of managing transactions which comes in handy in multi operations that either need to succeed together or be undone. For example in JDBC, every SQL statement gets treated as an individual transaction when it is sent to the database. This is standard JDBC behavior due If you look closely, there is a term titled as automatically committed transactions.
However, developers can set it off using the
Connection.setAutoCommit(false) method and then save their changes by providing a set of commands to either execute an update or replace the old data by committing the changes using Connection.commit() or execute the rollback command to abandon those changes using Connection.rollback() . In order for transaction management to be efficient, a JDBC driver must use an appropriate transaction isolation level.Types of JDBC Drivers
A JDBC driver enables Java applications to access a database. There are four interfaces in the JDBC API, there are differences in their approach in establishing communication with the database, here are the four main types of JDBC drivers:
1. Type 1: JDBC-ODBC Bridge Driver:
This makes use of ODBC (Open Database Connectivity) interface to access a database which is rather inefficient and introduces a reliance on ODBC rendering it inappropriate for use in production settings.
2. Type 2: Native-API Driver:
This driver employs low-level calls as appropriate in an efficient fashion by making use of class libraries. This Type II driver is much more effective than its other Type I variant but in stark contrast to Type I, it still heavily relies and is dependent on the database in question.
3. Type 3: Network Protocol Driver:
A network protocol driver has the capability to connect to several databases all at once thanks to its middle-tier server that converts JDBC calls into calls unique to a database.
4. Type 4: Thin Driver:
A type 4 driver may also be referred to as a pure Java driver as it does not require any native library or interlibrary call and it connects directly with the database. Its widespread popularity in applications is attributed to its level of efficiency and ease of use.
