Database Connection in Java (JDBC) Tutorial – Java Programming Tutorials

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:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.34</version>
</dependency>

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

Register driver
Get Connection from DriverManager
Create a new Statement on Connection
Execute query on Statement and get ResultSet
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.

class Student {
 
    private String name;
    private int age;
 
    public Student(String name, int age) {
        this.name = name;
        this.age = age;
    }
 
    @Override
    public String toString() {
        return "Student{" +
                "name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

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

JDBC SQL Select Example:

package com.explainjava;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
 
public class JdbcExample {
 
    private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/";
    private static final String DATABASE_NAME = "explainjava";
    private static final String DATABASE_USER = "root";
    private static final String DATABASE_PASSWORD = "";
 
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        List<Student> students = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection(DATABASE_URL + DATABASE_NAME, DATABASE_USER, DATABASE_PASSWORD);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT * FROM student")) {
 
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                students.add(new Student(name, age));
            }
        }
        System.out.println(students);
    }
}

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:

[Student{name='John', age=19}, Student{name='Henry', age=22}]

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:

SELECT * FROM student WHERE age < ?

? is a variable inside of query.

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

Example:

package com.explainjava;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
public class JdbcExample {
 
    private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/";
    private static final String DATABASE_NAME = "explainjava";
    private static final String DATABASE_USER = "root";
    private static final String DATABASE_PASSWORD = "";
 
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        List<Student> students = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection(DATABASE_URL + DATABASE_NAME, DATABASE_USER, DATABASE_PASSWORD);
             PreparedStatement statement = connection.prepareStatement("SELECT * FROM student WHERE age < ?")) {
            statement.setInt(1, 20);
            try (ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    String name = resultSet.getString("name");
                    int age = resultSet.getInt("age");
                    students.add(new Student(name, age));
                }
            }
        }
        System.out.println(students);
    }
}

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

statement.setInt(1, 20);

So the query will be transformed to:

SELECT * FROM student WHERE age < 20

Output:

[Student{name='John', age=19}]

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:

SELECT * FROM student WHERE name IN (?)

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

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

private static final String FIND_BY_NAMES = "SELECT name, age FROM student WHERE name IN (%s)";
 
public static List<Student> findByNames(List<String> names) {
    try (Connection connection = connection()) {
        List<Student> student = new ArrayList<>();
 
        String inClauseParameters = Collections.nCopies(names.size(), "?").stream().collect(Collectors.joining(","));
        String query = String.format(FIND_BY_NAMES, inClauseParameters);
 
        try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
            for (int i = 0; i < names.size(); i++) {
                 preparedStatement.setString(i + 1, names.get(i));
            }
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        }
        return students;
    } catch (SQLException e) {
        throw new IllegalStateException("Unable to find students by names " + names, e);
    }
}

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

For example, result query for names size 3:

SELECT * FROM student WHERE name IN (?,?,?)

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.

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `students_older_than` $$
CREATE PROCEDURE `students_older_than`(
  IN student_age INT
)
  BEGIN
    SELECT *
    FROM student
    WHERE age &gt;= student_age;
  END $$
 
DELIMITER ;

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:

CALL students_older_than(21);

In Java code it looks like this:

package com.explainjava;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
public class JdbcExample {
 
    private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/";
    private static final String DATABASE_NAME = "explainjava";
    private static final String DATABASE_USER = "root";
    private static final String DATABASE_PASSWORD = "";
 
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        List<Student> students = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection(DATABASE_URL + DATABASE_NAME, DATABASE_USER, DATABASE_PASSWORD);
             CallableStatement statement = connection.prepareCall("{CALL students_older_than(?)}")) {
            statement.setInt(1, 21);
            try (ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    String name = resultSet.getString("name");
                    int age = resultSet.getInt("age");
                    students.add(new Student(name, age));
                }
            }
        }
        System.out.println(students);
    }
}

Note that you should wrap stored procedure with {}.

Output:

[Student{name='Henry', age=22}]

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:

private static final String INSERT_STUDENT_STMT = "INSERT INTO student (name, age) VALUES (?, ?)";
 
public static void insert(List<Student> students) {
    try (Connection connection = connection()) {
        try (PreparedStatement preparedStatement = connection.prepareStatement(INSERT_STUDENT_STMT)) {
            for (Student student : students) {
                preparedStatement.setString(1, student.getName());
                preparedStatement.setInt(2, student.getAge());
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
        }
    } catch (SQLException e) {
        throw new IllegalStateException("Unable to insert students", e);
    }
}

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 (Statement, PreparedStatement, 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.

connection.setAutoCommit(false);

and call commit explicitly:

connection.commit();

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

package com.explainjava;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class JdbcExample {
 
    private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/";
    private static final String DATABASE_NAME = "explainjava";
    private static final String DATABASE_USER = "root";
    private static final String DATABASE_PASSWORD = "";
 
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        try (Connection connection = DriverManager.getConnection(DATABASE_URL + DATABASE_NAME, DATABASE_USER, DATABASE_PASSWORD)) {
            connection.setAutoCommit(false);
            try (PreparedStatement statement = connection.prepareStatement("UPDATE student SET age = ? WHERE name = ?")) {
                statement.setInt(1, 23);
                statement.setString(2, "Henry");
                int count = statement.executeUpdate();
                connection.commit();
                System.out.println(count);
            }
        }
    }
}

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.

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

Statement Example in Java

Prepared Statement Example In Java

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

Callable Statement Example in Java

Batch Processing

Transaction Management

Conclusion

Leave a Comment Cancel reply