I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

If you have a few years of experience in the Java ecosystem, and you're interested in sharing that experience with the community (and getting paid for your work of course), have a look at the "Write for Us" page.
Cheers. Eugen

1. Overview

In this article, we’re going to take a look at JDBC (Java Database Connectivity) which is an API for connecting and executing queries on a database.

JDBC can work with any database as long as proper drivers are provided.

2. JDBC Drivers

A JDBC driver is a JDBC API implementation used for connecting to a particular type of database. There are several types of JDBC drivers:

Type 1 – contains a mapping to another data access API; an example of this is the JDBC-ODBC driver

Type 2 – is an implementation that uses client-side libraries of the target database; also called a native-API driver

Type 4 – connect directly to a database by converting JDBC calls into database-specific calls; known as database protocol drivers or thin drivers,

The most commonly used type is type 4, as it has the advantage of being platform independent. Connecting directly to a database server provides better performance compared to other types. The downside of this type of driver is that it’s database-specific – given each database has its own specific protocol.

3. Connecting to a Database

To connect to a database, we simply have to initialize the driver and open a database connection.

The insertEmployee procedure above will insert a new record into the employees table using the given parameters and return the id of the new record in the emp_id out parameter.

To be able to run a stored procedure from Java, the connection user needs to have access to the stored procedure’s metadata. This can be achieved by granting rights to the user on all stored procedures in all databases:

GRANT ALL ON mysql.proc TO 'user1';

Alternatively, we can open the connection with the property noAccessToProcedureBodies set to true:

Retrieving the value for each table cell can be done using methods of type getX() where X represents the type of the cell data.

The getX() methods can be used with an int parameter representing the order of the cell, or a String parameter representing the name of the column. The latter option is preferable in case we change the order of the columns in the query.

5.2. Updatable ResultSet

Implicitly, a ResultSet object can only be traversed forward and cannot be modified.

If we want to use the ResultSet to update data and traverse it in both directions, we need to create the Statement object with additional parameters:

7. Handling Transactions

By default, each SQL statement is committed right after it is completed. However, it’s also possible to control transactions programmatically.

This may be necessary in cases when we want to preserve data consistency, for example when we only want to commit a transaction if a previous one has completed successfully.

First, we need to set the autoCommit property of Connection to false, then use the commit() and rollback() methods to control the transaction.

Let’s add a second update statement for the salary column after the employee position column update and wrap them both in a transaction. This way, the salary will be updated only if the position was successfully updated:

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

The examples could teach new programmers to bad habits 🙁 There is no mention that Statement and ResultSet also have close() method and it’s a good idea to use them. Using close() method also could be error-prone and best practice is to use try-with-resources that guarantees that resources will be closed even when exception occurs.

And to protect newcomers from possible SQL-injections, I suggest mention to never use Statement with a query that has user supplied data (and use PreparedStatement instead)!