[Java] [JDBC ③] I tried inputting from the main method using placeholders and arguments.

5 minute read

Last article In INSERT statement/UPDATE statement/DELETE statement/SELECT statement of SQL, divide by method and execute SQL statement in main method I was successful. It was not practical because the data to be added, changed or deleted was fixed in the SQL statement.

Therefore, I thought it would be more useful source code if I could pass the data that I want to add when calling each method of the SQL statement from the main method, so I tried.

Environment

The environment this time is as follows.

  • Ubuntu 18.04 LTS
  • OpenJDK 11.0.8
  • SQLite3 3.20.0

Looking back on the last time

First, let’s look at the method of the INSERT statement in the previous code

method of previous INSERT statement


/**
 * INSERT statement
 */
public static void insertData() {
    try {
        // create a database connection
        connection = DriverManager.getConnection(URL);
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(30); // set timeout to 30 sec.

        statement.executeUpdate("INSERT INTO person VALUES(1,'Satou')");
        statement.executeUpdate("INSERT INTO person VALUES(2,'Tanaka')");
        statement.executeUpdate("INSERT INTO person VALUES(3,'Suzuki')");
    } catch(SQLException e) {
        // if the error message is "out of memory",
        // it probably means no database file is found
        System.err.println(e.getMessage());
    } finally {
        try {
            if(connection != null)
            connection.close();
        } catch(SQLException e) {
            // connection close failed.
            System.err.println(e);
        }
    }
}

As you can see, the data you want to add is fixed in the SQL statement, so you cannot add arbitrary data just by calling it from the main method.


What you need to pass arbitrary data

In order to pass the data you want to add when calling from the main method, it is necessary to satisfy the following (2).

  • Use placeholders
  • Use PreparedStatement instead of Statement

Arbitrary character strings can be passed to SQL statements by using placeholders. Also, when using a placeholder, it is necessary to define an SQL statement in advance, so you can now use PreparedStatement instead of Statement to pass data in the following way.

Code using

Statement


// ...

Statement statement = connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.

statement.executeUpdate("INSERT INTO person VALUES(1,'Satou')");
statement.executeUpdate("INSERT INTO person VALUES(2,'Tanaka')");
statement.executeUpdate("INSERT INTO person VALUES(3,'Suzuki')");

// ...

Code using

PreparedStatement


// ...

String sql = "INSERT INTO person (name) VALUES(?)";
try {
PreparedStatement ps = null;
ps = connection.prepareStatement(sql);
ps.setString(1, "Satou");
ps.executeUpdate();
connection.commit();
ps.close();
} catch (SQLException e){

// ...

Note) At this point, PRIMARY KEY AUTO INCREMENT is added to id to make the source code a little easier to understand.


Improve method reusability

Next, in order to pass the data from the main method, pass the data part of ps.setString(1, "Satou"); as an argument so that the data part of the SQL statement can be arbitrarily set by the operation from the main method. I was able to change.

The source code and the execution result are shown below.

This goal


import java.sql.*;

/**
 * TestDataBaseAccess
 */
public class TestDataBaseAccess {
    static Connection connection;
    static PreparedStatement ps;
    static String URL = "jdbc:sqlite:sample.db";

    public static void main(String[] args) throws ClassNotFoundException {
       // load the sqlite-JDBC driver using the current class loader
        Class.forName("org.sqlite.JDBC");
        connection = null;
        ps = null;

        dropTable();
        createTable();
        insertData("Satou");
        insertData("Tanaka");
        insertData("Suzuki");
        loadData();

        System.out.println("---------");

        updateData(1, "Takahashi");
        loadData();

        System.out.println("---------");

        deleteData(3);
        loadData();
    }
    /**
     * SELECT statement
     */
    public static void loadData() {
        try {
            // create a database connection
            connection = DriverManager.getConnection(URL);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30); // set timeout to 30 sec.

            ResultSet rs = statement.executeQuery("SELECT * FROM person");
            while(rs.next()){
                // read the result set
                System.out.println("id = "+ rs.getInt("id") + "| name = "+ rs.getString("name"));
            }
        } catch(SQLException e) {
            // if the error message is "out of memory",
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null)
                connection.close();
            } catch(SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

    /**
     * INSERT statement
     */
    public static void insertData(String name) {
        String sql = "INSERT INTO person (name) VALUES(?)";
        
        try {
            connection = DriverManager.getConnection(URL);
            connection.setAutoCommit(false);

            ps = connection.prepareStatement(sql);
            ps.setString(1, name);
            ps.executeUpdate();
            connection.commit();
            ps.close();
        } catch(SQLException e) {
            // if the error message is "out of memory",
            // it probably means no database file is foundSystem.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null)
                connection.close();
            } catch(SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

    /**
     * UPDATE文
     */
    public static void updateData(int id, String name) {
        try {
            String sql = "UPDATE person SET name = ? WHERE id = ?";
            // create a database connection
            connection = DriverManager.getConnection(URL);
            connection.setAutoCommit(false);
            
            ps = connection.prepareStatement(sql);
            ps.setString(1, name);
            ps.setInt(2, id);
            ps.executeUpdate();
            connection.commit();
            ps.close();
        } catch(SQLException e) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null)
                connection.close();
            } catch(SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

    /**
     * DELETE文
     */
    public static void deleteData(int id) {
        try {
            String sql = "DELETE FROM person WHERE id = ?";
            // create a database connection
            connection = DriverManager.getConnection(URL);
            connection.setAutoCommit(false);

            ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            ps.executeUpdate();
            connection.commit();
            ps.close();
        } catch(SQLException e) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null)
                connection.close();
            } catch(SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

    /**
     * テーブル作成
     */
    public static void createTable() {
        try {
            // create a database connection
            connection = DriverManager.getConnection(URL);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            statement.executeUpdate("CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING)");
        } catch(SQLException e) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null)
                connection.close();
            } catch(SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

    /**
     * テーブル削除
     */
    public static void dropTable() {
        try {
            // create a database connection
            connection = DriverManager.getConnection(URL);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            statement.executeUpdate("DROP TABLE IF EXISTS person");
        } catch(SQLException e) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null)
                connection.close();
            } catch(SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }
}
javac TestDataBaseAccess.java && java -cp .:sqlite-jdbc-3.30.1.jar TestDataBaseAccess 
id = 1 | name = Satou
id = 2 | name = Tanaka
id = 3 | name = Suzuki
- --------
id = 1 | name = Takahashi
id = 2 | name = Tanaka
id = 3 | name = Suzuki
- --------
id = 1 | name = Takahashi
id = 2 | name = Tanaka

まとめ

今回の変更で再利用性が高まっていいコードに近づいてきたのではないでしょうか。 今後は実際にソフトウェアで使ってみて、より良いソースコードにしていきたいと思います。


本記事目次ページ

【データベース】SQLite3・JDBCまとめ


参考サイト