JDBC Tutorial Part 2: How to Run SQL Queries

In Part 2 of this tutorial series, you’ll learn how to execute SELECT, INSERT, UPDATE, and DELETE statements against an SQL database using JDBC. These actions are known as CRUD operations (Create, Read, Update, Delete), which form most of the functionality in an application.

Note: We’ll continue where the previous step of the tutorial left off, which means this article assumes you have a Connection object ready. Refer to part 1 of this tutorial for more details. The source code is available on GitHub.

Here’s a video version of this article, in case you want to see the concepts in action:

Adding CRUD Operations

In this tutorial, we are building a simple Java application that interacts with a MariaDB database using JDBC. The database contains a table with the name programming_languages where we want to keep track of - you guessed it - programming languages and their rating.

We won’t implement an application with useful functionality from an end-user perspective. We’ll simply make calls to methods for each CRUD operation and call them from a Java standard main method. The methods that implement the CRUD operations against the database are not implemented yet, but here’s what we want to do:

public static void main(String[] args) throws SQLException {
    try {
        initDatabaseConnection();
        deleteData("%");
        readData();
        createData("Java", 10);
        createData("JavaScript", 9);
        createData("C++", 8);
        readData();
        updateData("C++", 7);
        readData();
        deleteData("C++");
        readData();
    } finally {
        closeDatabaseConnection();
    }
}

This method tells a fictional story of the popularity of some programming languages. We begin by deleting all the programming languages from the database so that we start fresh.

This allows us to run the application as many times as we want and get the same output without errors. We read the data, meaning showing the programming languages and their ratings on the screen, after each operation in order to check that the operation worked. We create programming languages, update the rating for one of them, and delete it later.

Let’s implement these methods now.

Inserting Data Using JDBC

Let’s start with the Create CRUD operation:

private static void createData(String name, int rating) throws SQLException {
    try (PreparedStatement statement = connection.prepareStatement("""
                INSERT INTO programming_language(name, rating)
                VALUES (?, ?)
            """)) {
        statement.setString(1, name);
        statement.setInt(2, rating);
        int rowsInserted = statement.executeUpdate();
        System.out.println("Rows inserted: " + rowsInserted);
    }
}

The createData(String, int) method receives the programming language name and its rating. To execute SQL we need to build a Statement object using the connection object. PreparedStatement extends Statement adding useful methods.

Most of the time you’ll be using PreparedStatament because it allows you to set parameters. We in fact need to use parameters: the name and rating of the programming language to insert. We set these parameters using the setString(int, String) and setInt(int, int) methods.

Notice the multiline string with the SQL sentence to execute. The VALUES clause contains two question marks. The first question mark is assigned the index 1, and the second question mark the index 2. Using these indexes we can set the parameters accordingly.

A Statement, and consequently, a PreparedStatement, is a resource that needs to be released or closed properly. This is done by calling the close() method in objects of these types. Statement is a Java Closable which makes it work with try-with-resources blocks, which is exactly what we did. Instead of calling the close() method directly, we let Java call it for us. This is equivalent to calling close() in a finally block to always close the resource even when exceptions or errors occur. We’ll use the same approach every time we create Statement objects.

Notice how we use the executeUpdate() method, which returns the number of rows that were updated by the operation.

Retrieving Data Using JDBC

To implement the Read CRUD operation, we need to get an object of type ResultSet:

private static void readData() throws SQLException {
    try (PreparedStatement statement = connection.prepareStatement("""
                SELECT name, rating
                FROM programming_language
                ORDER BY rating DESC
            """)) {
        try (ResultSet resultSet = statement.executeQuery()) {
            boolean empty = true;
            while (resultSet.next()) {
                empty = false;
                String name = resultSet.getString("name");
                int rating = resultSet.getInt("rating");
                System.out.println("\t> " + name + ": " + rating);
            }
            if (empty) {
                System.out.println("\t (no data)");
            }
        }
    }
}

This time we are calling the executeQuery() method on the statement object. This method returns a ResultSet that once again, we need to close when we are done with the resource (that’s why we instantiated it in a try-with-resources block).

The resultSet object holds a cursor initially positioned before the first row that we can move using the next() method, which returns false when there are no more rows to iterate over. To read the values in each column, we use get methods specifying the name of the column (as returned by the SQL SELECT statement) or alternatively using an integer corresponding to the column index (starting with 1). We can use the values returned by these get methods to print a line in the standard output of the Java program. There are different methods for different Java types. You have some degree of flexibility to convert SQL types to Java types depending on the JDBC driver. For example, the following table matches MariaDB types with potential Java types:

Modifying Data Using JDBC

The Update CRUD operation looks similar to the Create operation:

private static void updateData(String name, int newRating) throws SQLException {
    try (PreparedStatement statement = connection.prepareStatement("""
                UPDATE programming_language
                SET rating = ?
                WHERE name = ?
            """)) {
        statement.setInt(1, newRating);
        statement.setString(2, name);
        int rowsUpdated = statement.executeUpdate();
        System.out.println("Rows updated: " + rowsUpdated);
    }
}

Nothing new here when compared to the Create operation, except that we used an UPDATE SQL statement instead of INSERT.

Removing Data Using JDBC

Finally, the Delete CRUD operation, which is similar to the Create, and Update operations, from a JDBC perspective:

private static void deleteData(String nameExpression) throws SQLException {
    try (PreparedStatement statement = connection.prepareStatement("""
                DELETE FROM programming_language
                WHERE name LIKE ?
            """)) {
        statement.setString(1, nameExpression);
        int rowsDeleted = statement.executeUpdate();
        System.out.println("Rows deleted: " + rowsDeleted);
    }
}

Notice that JDBC will throw an SQLException in case of an error. In this example application, we simply add a throws clause to the methods, but in more serious applications, you can catch this exception and, for example, show an error message.

A Word on SQL Injection

SQL injection is a type of attack done by inserting malicious SQL code as the input of a program with the intention to get the code executed. Since we used PreparedStatement and the set methods to pass parameters to the SQL sentences, the examples above don’t allow SQL injection. Never concatenate strings to set parameter values in SQL statements. Here’s an example of what NOT to do:

// DON’T DO THIS:
private static void updateWithSqlInjection(String name, int newRating) throws SQLException {
    System.out.println("Performing SQL injection...");
    try (PreparedStatement statement = connection.prepareStatement(
        "UPDATE programming_language SET rating = " + newRating + " WHERE name = '" + name + "'"
    )) {
        int rowsUpdated = statement.executeUpdate();
        System.out.println("Rows updated: " + rowsUpdated);
    }
}

If the value in the name variable comes from user input, a malicious user can introduce a string like '; TRUNCATE programming_language; SELECT 'boom!; which would result in an SQL statement like the following:

UPDATE programming_language SET rating = 5 WHERE name = ''; TRUNCATE programming_language; SELECT 'boom!;'

All the programming languages are gone. Not a good thing! So, in conclusion, always set the parameters using the set methods. They perform proper character escaping which prevents SQL injection attacks.

Note: If you want to try this vulnerability, you have to enable multi queries using a configuration parameter in the JDBC URL connection string:

jdbc:mariadb://localhost:3306/jdbc_demo?allowMultiQueries=true

In the next article, we’ll learn how to use connection pools to improve the performance of applications.

Also Published Here