Inspired by the original ODBC (Open Database Connectivity), the JDBC stands for Java Database Connectivity. JDBC is a Java database API that was created as an industry standard for making connections between Java applications and other databases. The Java JDBC connection in Java API defines all classes that handle various aspects of the database, such as connection details, result sets, and database metadata. Originally, JDBC was intended to be a client-side API that could connect to a data source. In version 2.0, it was added with an optional package that supports server-side connections. Since then, every new release of JDBC has included updates to both the client-side and server-side packages. The most recent version, which was released in September 2017, was part of Java SE 9.
More so, it supports various types of relational databases which use SQL (Structured Query Language) such as DB2, ORACLE, and Sybase. Each of these possesses a unique JDBC driver name, that encodes access request statements, issues commands, and handles result sets obtained from the database.
Before we delve into the detailed processes of creating a JDBC connection with databases like SQL, we have to understand all the concepts, tools, classes, and interfaces involved such as JDBC components, JDBC drivers, as well as the relations between relational databases like SQL and JDBC.
The JDBC core comes with the following interfaces and classes:
JBDC drivers are unique implementation tools used for connecting to a particular database for interaction with the database server. There are various types of these drivers:
JDBC-ODBC bridge driver (Type 1): This JDBC bridge driver contains a mapping to another data access API, such as ODBC drivers, which requires configuring on each system a Data Source Name (DSN) that represents the targeted database.
JDBC-Native API (Type 2): This is a driver that utilizes the client-side libraries of the target database, also called a native-API driver.
JDBC-Net pure Java (Type 3): Type 3 drivers use a three-tier approach to access databases through middleware to change JDBC calls into each database-specific call
100% pure Java (Type 4): This type of driver is the highest performance driver and is used for databases such as Oracle, IBM, and Sybase. It works directly with the vendor’s database using a socket connection.
A relational database like SQL is a structured repository that stores records in tables with columns and rows. Although NoSQL has gained popularity over the past decade, relational databases like SQL are still the most commonly used type of datastore. The main language used by data architects to perform various tasks related to a relational database is SQL. It can perform various tasks such as creating, reading, and updating records.
In Java JDBC connection, the JDBC acts as an adapter layer that provides adaptability to SQL from Java. This enables the Java developers to connect to a database and allows them to perform various tasks like managing responses and queries.
We have learned what a JDBC is, what it is used for, its components, driver types, and its relationship with relational databases like SQL. Now, we will be going through detailed steps through which we use JDBC to create a connection to a database in Java. Here is the 7 step process to create a Java JDBC connection:
This includes uploading all the packages containing the JDBC classes, interfaces, and subclasses used during the database programming. More often than not, using import java.sql.* is enough. However, other classes can be imported if needed in the program.
Before connecting to the database, we’ll need to load or register the drivers once per database. This is done to create a communication channel with the database. Loading a driver can be done in two ways:
For the next step here, the getConnection() method is used to create a connection object that will correspond to a physical connection with the database. To get the getConnection() to access the database, the three parameters are a username, string data type URL, and a password. Two methods can be used to achieve this:
getConnection(URL, username, password): This uses three parameters URL, a password, and a username
getConnection(URL): This has only one parameter - URL. The URL has both a username and password. There are several JDBC connection strings for different relational databases and some are listed below:
a. IBM DB2 database: jdbc:db2://HOSTNAME:PORT/DATABASE_NAME
b. Oracle database: jdbc:oracle:thin:@HOST_NAME:PORT:SERVICE_NAME
c. My SQL database: jdbc:mysql://HOST_NAME:PORT/DATABASE_NAME
The statement can now be created to perform the SQL query when the connection has been established. There are three statements from the createStatement method of the connection class to establish the query. These statements are
This uses a type statement object to build and submit SQL statements to a database. It has four distinct methods:
When queries are executed using the executeQuery() method, it produces results stored in the ResultSet object. The ResultSet object is then used to access the retrieved data from the database.
The JDBC connection can now be closed after all is done. The resource has to be closed to avoid running out of connections. It can be done automatically using ‘conn.close();’. But for versions of Java 7 and above, it can be closed using a try-catch block
The JDBC API is a universal data access mechanism that can be used by any program that uses Java. With the JDBC API, you can access almost any type of data source, such as relational databases and flat files. It also provides a common base for developing tools and alternate interfaces. After creating the connection, it can allow the programmer to access request statements and issue commands and handle result sets obtained from the database. The process of things done within the Java application is summarized in three steps, and they are:
Gospel Bassey is a creative technical writer who harnesses the power of words to break down complex concepts into simple terms. He is an experienced Technical Writer who has developed content in various fields of technology, such as Blockchain technology, Information Technology, and Data Science, to mention but a few.
Tell us the skills you need and we'll find the best developer for you in days, not weeks.