[Java] [JDBC] I tried converting SQLite3 database access from Java to a method for each SQL statement.

4 minute read

In the previous article, I succeeded in accessing the SQLite database from Java using JDBC. I prepared a database in advance and wrote a SELECT statement in a Java program to acquire data from the database, so I took a step further and incorporated an INSERT statement, UPDATE statement, DELETE statement, etc. in addition to the SELECT statement. I tried to.

Environment

The development environment this time is as follows.

  • Ubuntu 18.04.5LTS
  • OpenJDK 11.0.8
  • SQLite3 3.22.0

Referenced sample code

Since I wanted to use multiple SQL statements this time, I will make it a method based on the sample code in the README of the JDBC repository.

Reference source sample code


import java.sql.*;

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

    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30); // set timeout to 30 sec.

      statement.executeUpdate("drop table if exists person");
      statement.executeUpdate("create table person (id integer, name string)");
      statement.executeUpdate("insert into person values(1,'leo')");
      statement.executeUpdate("insert into person values(2,'yui')");
      ResultSet rs = statement.executeQuery("select * from person");
      while(rs.next())
      {
        // read the result set
        System.out.println("name = "+ rs.getString("name"));
        System.out.println("id = "+ rs.getInt("id"));
      }
    }
    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);
      }
    }
  }
}

Make a method for each SQL statement

In the above sample code, access to database -> clear table -> create table -> input data -> read data are all processed in the main method. In a real program, each operation is done separately, so I think it is more realistic to make them into methods and call them to the main method when necessary.

Below is the code that I converted into a method. The names in the database have been changed as appropriate for easy understanding.

Methodized code


import java.sql.*;

/**
 * TestDataBaseAccess
 */
public class TestDataBaseAccess {
    static Connection connection;
    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;
        
        dropTable();
        createTable();
        insertData();
        loadData();

        updateData();
        loadData();

        deleteData();
        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() {
        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);
            }
        }
    }

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

            statement.executeUpdate("UPDATE person SET name = 'Takahashi' WHERE id = 1");            
        } 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() {
        try {
            // create a database connection
            connection = DriverManager.getConnection(URL);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            statement.executeUpdate("DELETE FROM person WHERE id = 3");            
        } 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, 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);
            }
        }
    }
}

メソッド化した上で、処理の流れをmainメソッドに呼び出してみました。

実行結果


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

まとめ

各SQL文をメソッド化し、必要に応じてmainメソッドに呼び出すことになんとか成功しました。 SQLを扱う場合、各メソッドごとにtry-catchをしないとエラーになるようでした。 しかし現状では、データの入力内容・変更内容をメソッド内で定義しているため、再利用性が低く未だ未完成です。 今回のコードをベースに再利用性のあるコードにしていこうと思います。


参考サイト