JDBC Tutorial Part 2: Running SQL Queries
Tags: java, sql, database, beginners
Published: 2022-01-11 08:31:10

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](https://dev.to/alejandro_du/jdbc-tutorial-part-1-connecting-to-a-database-kfc) of the tutorial left off, which means this article assumes you have a `Connection` object ready. Refer to [part 1](https://dev.to/alejandro_du/jdbc-tutorial-part-1-connecting-to-a-database-kfc) of this tutorial for more details. The source code is available on [GitHub](https://github.com/mariadb-developers/java-quickstart/tree/main/jdbc).

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

[![JDBC Tutorial part 3/3 – Connection pools](https://img.youtube.com/vi/nWhZl4cMjRw/hqdefault.jpg)](https://www.youtube.com/watch?v=nWhZl4cMjRw)

## Adding CRUD Operations

In this tutorial, we are building a simple Java application that interacts with a [MariaDB database](https://mariadb.com) 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:
  

```java
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:

```java
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`:

```java
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:

| MariaDB types                                                                                  | Java types                                                                                                   |
|------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------|
| `CHAR`, `VARCHAR`, `BLOB`, `TEXT`                                                                      | `java.lang.String`, `java.io.InputStream`, `java.io.Reader`, `java.sql.Blob`, `java.sql.Clob`                          |
| `FLOAT`, `REAL`, `DOUBLE PRECISION`, `NUMERIC`, `DECIMAL`, `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INTEGER`, `BIGINT` | `java.lang.String`, `java.lang.Short`, `java.lang.Integer`, `java.lang.Long`, `java.lang.Double`, `java.math.BigDecimal` |
| `DATE`, `TIME`, `DATETIME`, `TIMESTAMP`                                                                | `java.lang.String`, `java.sql.Date`, `java.sql.Timestamp`                                                          |

## Modifying Data Using JDBC

The _Update_ CRUD operation looks similar to the _Create_ operation:

```java
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:

```java
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:

```java
// 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.