In Part 2 of this tutorial series, you’ll learn how to execute , , , and statements against an SQL database using JDBC. These actions are known as (Create, Read, Update, Delete), which form most of the functionality in an application. SELECT INSERT UPDATE DELETE CRUD operations We’ll continue where the of the tutorial left off, which means this article assumes you have a object ready. Refer to of this tutorial for more details. The source code is available on . Note: previous step Connection part 1 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 using JDBC. The database contains a table with the name where we want to keep track of - you guessed it - programming languages and their rating. MariaDB database programming_languages 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 method. The methods that implement the CRUD operations against the database are not implemented yet, but here’s what we want to do: main 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 CRUD operation: Create 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 method receives the programming language name and its rating. To execute SQL we need to build a object using the object. extends adding useful methods. createData(String, int) Statement connection PreparedStatement Statement Most of the time you’ll be using 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 and methods. PreparedStatament setString(int, String) setInt(int, int) Notice the multiline string with the SQL sentence to execute. The 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. VALUES A , and consequently, a , is a resource that needs to be released or closed properly. This is done by calling the method in objects of these types. is a Java which makes it work with try-with-resources blocks, which is exactly what we did. Instead of calling the method directly, we let Java call it for us. This is equivalent to calling in a block to always close the resource even when exceptions or errors occur. We’ll use the same approach every time we create objects. Statement PreparedStatement close() Statement Closable close() close() finally Statement Notice how we use the method, which returns the number of rows that were updated by the operation. executeUpdate() Retrieving Data Using JDBC To implement the CRUD operation, we need to get an object of type : Read 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 method on the object. This method returns a 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). executeQuery() statement ResultSet The object holds a cursor initially positioned before the first row that we can move using the method, which returns when there are no more rows to iterate over. To read the values in each column, we use methods specifying the name of the column (as returned by the SQL statement) or alternatively using an integer corresponding to the column index (starting with 1). We can use the values returned by these 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: resultSet next() false get SELECT get 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 CRUD operation looks similar to the operation: Update Create 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 operation, except that we used an SQL statement instead of . Create UPDATE INSERT Removing Data Using JDBC Finally, the CRUD operation, which is similar to the , and operations, from a JDBC perspective: Delete Create Update 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 in case of an error. In this example application, we simply add a clause to the methods, but in more serious applications, you can catch this exception and, for example, show an error message. SQLException throws 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 and the 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: PreparedStatement set // 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 variable comes from user input, a malicious user can introduce a string like which would result in an SQL statement like the following: name '; TRUNCATE programming_language; SELECT 'boom!; 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, methods. They perform proper character escaping which prevents SQL injection attacks. always set the parameters using the set If you want to try this vulnerability, you have to enable multi queries using a configuration parameter in the JDBC URL connection string: Note: 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

YouTube

JDBC Tutorial Part 3: How to Use Database Connection Pools

JDBC Tutorial Part 1: How to Connect to a Database

Visit my personal Blog!

Nominated for 2022 - HackerNoon Contributor of the Year - Database

Nominated for 2022 - HackerNoon Contributor of the Year - Git

Nominated for 2022 - HackerNoon Contributor of the Year - Java

Nominated for 2022 - HackerNoon Contributor of the Year - Docker

Nominated for 2022 - HackerNoon Contributor of the Year - Sql

Nominated for 2022 - HackerNoon Contributor of the Year - Analytics

Too Long; Didn't Read

Make resilience your competitive advantage

JDBC Tutorial Part 2: How to Run SQL Queries

JDBC Tutorial Part 2: How to Run SQL Queries

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

9 Useful Interactive CLI Tools for Linux

All the Firebase Functions you Need to Build Your Next Serverless Application

Build a FullStack Tinder Clone Without Writing A Single Line Of Code

Build a Job Application Tracker in 10 minutes Using ToolJet's Low Code Tool

Building a CRUD RESTful API/Web Service with Spring Boot [A How-To Guide]

9 Useful Interactive CLI Tools for Linux

All the Firebase Functions you Need to Build Your Next Serverless Application

Build a FullStack Tinder Clone Without Writing A Single Line Of Code

Build a Job Application Tracker in 10 minutes Using ToolJet's Low Code Tool

Building a CRUD RESTful API/Web Service with Spring Boot [A How-To Guide]

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps