DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
What's in store for DevOps in 2023? Hear from the experts in our "DZone 2023 Preview: DevOps Edition" on Fri, Jan 27!
Save your seat
  1. DZone
  2. Data Engineering
  3. Databases
  4. JDBC Tutorial Part 2: Running SQL Queries
Content provided by MariaDB logo

JDBC Tutorial Part 2: Running SQL Queries

In Part 2 of this tutorial series covering the basics of JDBC, learn how to execute SELECT, INSERT, UPDATE, and DELETE statements against an SQL database.

Alejandro Duarte user avatar by
Alejandro Duarte
CORE ·
Jan. 11, 22 · Tutorial
Like (8)
Save
Tweet
Share
34.38K Views

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:

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

MariaDB SQL
 
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:

Plain Text
 
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.


Comments

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: