Database Connection in Java (JDBC) Tutorial

Establish a database connection in Java is a common problem.

Java provides a JDBC (Java Database Connectivity) API to do that.

In this article, I’ll explain what is a JDBC driver, how to create the JDBC connection and execute SQL query.

I prepared an example of manual transactions management inside of JDBC connections as well.

How To Connect to The Database in Java Using MySQL JDBC Driver

First of all, you have to be sure that your MySQL DB is installed and running.

Then download and add MySQL JDBC driver to the classpath.

Or just add maven dependency:

To connect to the database you need to do following steps:

  1. Register driver
  2. Get Connection  from  DriverManager
  3. Create a new  Statement on Connection
  4. Execute query on  Statement and get  ResultSet
  5. Iterate through  ResultSet and collect results

I have to mention that 3 types of Statements exist in Java:

Statement
Used to execute static SQL queries and return the results it produces.
PreparedStatemnet
Represents a precompiled SQL statement, that is used to efficiently execute this statement multiple times.
CallableStatement
Used to execute SQL stored procedures.

Statement Example in Java

Let’s imagine we have a student table in the database.

Student class represents it in the Java code.

I want to select all students and print it out, that’s why I need to override toString method.

JDBC SQL Select Example:

I created a database connection, then get Statement and execute a SELECT query.

Then I collected students to the list by iterating through the ResultSet.

Output:

Prepared Statement Example In Java

For example, I want to select all students below N years.

So I need to create a prepared statement like this:

? is a variable inside of query.

I want to use this query to select all students below 20 years.

Example:

As you can see I set an age parameter for a query.

So the query will be transformed to:

Output:

How to Pass a List to Prepared Statement (IN clause)

You will be wondered, but it’s not possible to execute this SQL statement with more than one parameter:

You can’t pass a list to the prepared statement directly.

That’s why we have to use “dirty tricks”:

We’re building a query with the same amount of “?” as input list size.

For example, result query for names size 3:

Then we’re setting parameters by index and execute a query.

This is the only one way that I know to pass a list in the prepared statement if you know a better solution please write in comments.

Callable Statement Example in Java

For example, you have a stored procedure to find all students older than student_age.

We want to use this procedure to find all student that are allowed to buy an alcohol.

In MySQL you can call it like this:

In Java code it looks like this:

Note that you should wrap stored procedure with {}.

Output:

Batch Processing

Jdbc Batch processing allows executing a group of related SQL statements in one database call.

It’s more efficient way then connect to the database and execute for each SQL query.

Batch insert example:

As you can see I used a prepared insert query to add a single student.

I’m iterating through students, settings parameters on the prepared statement and at the end of each iteration calling  statement.addBatch(), that adds parameters to the object’s batch of commands.

You can add queries directly to the batch using  statement.addBatch(query) method.

When all statements are ready you have to call  statement.executeBatch() method, that submits a batch of commands to the database.

Each type of statement ( StatementPreparedStatement, and CallableStatement) can be used for batch processing.

JDBC batch mechanism can be used for update, insert or delete table rows and it could contain DDL commands like CREATE TABLE  or DROP TABLE.

Transaction Management

if we’re talking about databases I have to say about transactions.

JDBC connection has an auto-commit mode and it’s enabled by default.

That means each query is executed inside of a transaction.

If you want to manage transactions manually you have to set aut0-commit to false.

and call commit explicitly:

For example, I want to connect to DB and update student’s age by its name:

The output is a number of updated rows.

In our case, it’s 1.

Conclusion

JDBC connection is a pretty easy thing, but not so powerful as Hibernate or MyBatis frameworks.

You should know how pure JDBC works because all frameworks are based on it.

Raw JDBC queries are fast as possible, but comparing to Hibernate you have to write manually boilerplate code like converting results to target objects, caching mechanism, fancy API to build queries etc.

Personally, I recommend using Spring Data JPA  to connect to the database.

Spring JPA repositories are awesome technology.

I was really impressed when I saw it the first time.

We’ll have a deeper look into ORMs in the future.

Please, ask me questions in comments.

1 Star2 Stars3 Stars4 Stars5 Stars
Loading...
Scroll Up