JDBC API Components

Components of JDBC

Every API is composed of components, each responsible for its part of the API's functionality. This structure can also be likened to a production line, where each employee has a role in each process.

For instance, we can logically divide the processes into several sub-processes:

Establish a connection to the database;
Send an SQL query to the database;
Receive data in the form of a response;
Properly interpret this data, adapting it to Java code;
Transform the data into a more convenient data structure (e.g., a list, collection, or map).

For each of these processes, there is a corresponding component responsible for successfully executing its instructions. Let's get acquainted with these components in more detail:

Connection: This interface establishes a connection to the database. You use it to create instances of Statement or PreparedStatement;
Statement and PreparedStatement: These interfaces are used to execute SQL queries. Statement is used for executing static SQL queries without parameters, while PreparedStatement is designed for executing SQL queries with one or more parameters;
ResultSet: When you execute an SQL query to retrieve data (SELECT), the ResultSet stores the retrieved data, allowing you to process each row of the result sequentially;
DriverManager: This class manages a list of available JDBC drivers. It selects the appropriate driver when establishing a connection to the database.

So, from this, we can draw a conclusion about how responsibilities are distributed in the code:

The DriverManager and Connection classes perform the first process: Establish a connection to the database. The DriverManager selects the appropriate driver for the specific DBMS, and the Connection uses this driver to connect the code to the database;
Statement and PreparedStatement handle the second process, as they allow us to send SQL queries to the database and instruct the database on what data we want to retrieve;
ResultSet handles the remaining processes. It is with the help of this class and its methods that we can obtain the data in a format convenient for us, such as a List.

Thus, we can see that each of the components serves its purpose. This structure is correct and adheres to SOLID principles.

Note

We will discuss SOLID principles in a separate course. These principles define well-written application code by providing guidelines for writing code correctly.

JDBC Interaction with a Database

Let's examine the interaction of JDBC with a database through code examples.

It may seem complicated at first, but as you progress through the course, especially when working on practical assignments, you will gain a better understanding of how it works and what needs to be done.

To begin with, let's see what the database we will be working with and connecting to looks like. We will be working with the testDatabase schema and the employees table:

A fairly simple table in the database. Now, let's set ourselves the task of retrieving data such as id, name, and salary from the first row in the table (id=1):

Note

You can find instructions on how to properly configure MySQL and retrieve the db_username and db_password here: article.

Let's go through it step by step:

Step	Action	Description
1	Variable Creation	Create `String` variables to store the database URL (which includes the database name, equivalent to SQL's `USE DatabaseName`), the MySQL `username`, and the user's `password`. Details are hidden for privacy.
2	Connection Establishment	Use the `Connection` interface and the `DriverManager` class to establish a database connection. This involves calling the `getConnection()` method with the database URL, username, and password as parameters, thereby establishing a connection for sending SQL queries.
3	Preparing Statement	Employ the `prepareStatement()` method on the connection object, and assign the resulting prepared statement object to a variable. This object contains the SQL query specified in the method's parameter.
4	Executing Query	Utilize the `executeQuery()` method on the prepared statement object, assigning the results to a `ResultSet` object. This object now holds the results of the executed SQL query.
5	Retrieving Data	Check for data in the `resultSet` using the `next()` method. If data exists, variables are created and assigned values from the first row of the table. The `next()` method positions us at the 1st row in the table initially, and it's used again to move to subsequent rows.
6	Assigning Values	Assign values to variables by referencing column names, such as `id`, `name`, and `salary`. Values are taken from the FIRST row in the table, with the process repeated for additional rows as needed using the `next()` method.

The output will look like this:

If we want to display all the elements on the screen, we need to change the if condition to a while loop so that we process each element.

It will look like this:

Output:

Conclusion

For every interaction with a database using JDBC, the same algorithm is used, as shown in the example above. We won't use this exact algorithm frequently because we will use Hibernate to interact with the database, but it's essential for everyone to be aware of this method of database interaction provided by the JDK.

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 3