Get to know all about JDBC right from its introduction to creating a connection to a database for handling SQL queries...

Java Database Connectivity is an API used for creating an independent database connection between databases and the Java.

What is JDBC? Understanding and Creating JDBC Connection

What is JDBC? Creating a Java JDBC Connection

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.

Understanding JDBC in Java

The JDBC core comes with the following interfaces and classes:

Driver: This is the interface that controls communication with the database server. It also withdraws information associated with driver objects.

Driver Manager: It manages any required set of JDBC drivers

Connection: This is an interface or session that houses all the methods to connect to any database.

Statements: This is used to carry out a static SQL statement

ResultSet: This is used to access the result row-by-row

JDBC components

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.

JDBC drivers

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.

Relationship between SQL (a relational database) and JDBC

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:

How 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.

1. Import the packages:

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:

Class.forName()
DriverManager.registerDriver() 

2. Register the drivers:

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

3. Establish a connection:

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

Statement: This is used to create simple SQL statements with no parameter. An example is: Statement statemnt1 = conn.createStatement();. This statement returns the ResultSet object.

PreparedStatement: This extends the Statement interface. It improves the application's performance because it has more features and compiles the query only once. It is used for precompiled SQL statements that have parameters.

CallableStatement: CallableStatements also extends the PreparedStatement interface. It is used for SQL statements with parameters that invoke procedure or function in the database. It is simply created by calling the prepare all method of the connection object.

4. Create a statement:

This uses a type statement object to build and submit SQL statements to a database. It has four distinct methods:

5. Execute the query:

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.

6. Retrieve results:

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.

7. Close the connections:

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:

Establishing a connection with a data source

Send queries and update statements to the data source

Conclusion

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.

Java uses the JDBC to handle connections to relational databases such as Microsoft SQL Server, Oracle Database, MySQL, and IBM DB2. Java uses JDBC as an adaptive layer for these databases.

JDBC is used in Java because JDBC enables the establishment of a connection with data sources, send queries, update statements, and process the results.

There are four types of JDBC drivers and they are JDBC-ODBC bridge driver (Type 1), Native-API driver (Type 2), Network Protocol driver (Type 3), and Thin driver (Type 4).

Frequently Asked Questions

What’s up with Turing?
Get the latest news about us here.

Press

Know more about remote work.
Checkout our blog here.

Blog

Have any questions?
We’d love to hear from you.

FOR DEVELOPERS

What is JDBC? Creating a Java JDBC Connection

Author

Gospel Bassey

Machine Learning or Artificial Intelligence - The Right Way Forward for Data Science

Mathematical Optimization vs Machine Learning: A Brief Comparison

Which Programming Language is More Popular in Robotics? Java or Python

SQL vs NoSQL Databases: Fundamental Differences, Use-Cases, and Features

Frequently Asked Questions

Press

Blog

Contact

Hire remote developers