What is JDBC? Creating a Java JDBC Connection

Understanding JDBC in Java

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.

JDBC components

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 drivers

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.

Relationship between SQL (a relational database) and JDBC

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.

How to create a Java JDBC connection

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:

1. Import the packages:

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.

2. Register the drivers:

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() 

3. Establish a connection:

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

4. Create a statement:

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.

5. Execute the query:

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

• ResultSet executeQuery(String sql)
• executeUpdate(String sql)
• execute(String sql)
• executeBatch()

6. Retrieve results:

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.

7. Close the connections:

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.

Conclusion

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
• Processing the results