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

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

environment

The development environment this time is as follows.


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

The above sample code accesses the database → deletes the table → creates the table → inputs the data → reads the data all in the main method. In an actual program, each operation is performed separately, so I think it is more realistic to make them into methods and call them to the main method when necessary.

The code that I made into a method is as follows. The names in the database have been changed as appropriate to make them easier to understand.

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 statement
     */
    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);
            }
        }
    }

    /**
     *Create table
     */
    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);
            }
        }
    }

    /**
     *Delete table
     */
    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);
            }
        }
    }
}

After making it a method, I called the processing flow to the main method.

Execution result


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

Summary

I managed to make each SQL statement a method and call it to the main method as needed. When dealing with SQL, it seemed that an error would occur if you did not try-catch each method. However, at present, the data input contents and changes are defined in the method, so the reusability is low and it is still incomplete. I will make the code reusable based on this code.


Reference site

--JDBC repository for SQLite -[TECHSCORE BROG / 3 minutes to build! Try running SQLite in various environments](https://www.techscore.com/blog/2015/03/27/3%E5%88%86%E3%81%A7%E6%A7%8B%E7 % AF% 89% EF% BC% 81sqlite% E3% 82% 92% E8% 89% B2% E3% 80% 85% E3% 81% AA% E7% 92% B0% E5% A2% 83% E3% 81 % A7% E5% 8B% 95% E3% 81% 8B% E3% 81% 97% E3% 81% A6% E3% 81% BF% E3% 82% 8B /)

Recommended Posts

[JDBC] I tried to make SQLite3 database access from Java into a method for each SQL statement.
[Java] I tried to make a maze by the digging method ♪
I tried hitting a Java method from ABCL
I tried to make a login function in Java
I tried to make a program that searches for the target class from the process that is overloaded with Java
I tried to make a client of RESAS-API in Java
I made a method to ask for Premium Friday (Java 8 version)
How to make a groundbreaking diamond using Java for statement wwww
I tried to make a talk application in Java using AI "A3RT"
I tried to make Basic authentication with Java
I did Java to make (a == 1 && a == 2 && a == 3) always true
I wanted to make (a == 1 && a == 2 && a == 3) true in Java
I want to write quickly from java to sqlite
[Azure] I tried to create a Java application for free-Web App creation- [Beginner]
[Small story] I tried to make the java ArrayList a little more convenient
I tried to break a block with java (1)
[JDBC ③] I tried to input from the main method using placeholders and arguments.
[Azure] I tried to create a Java application for free ~ Connect with FTP ~ [Beginner]
How to jump from Eclipse Java to a SQL file
I made a method to ask for Premium Friday
[Java basics] Let's make a triangle with a for statement
I tried to create a Clova skill in Java
Using the database (SQL Server 2014) from a Java program 2018/01/04
I tried using an extended for statement in Java
How to loop Java Map (for Each / extended for statement)
I tried to make the sample application into a microservice according to the idea of the book "Microservice Architecture".
[Java] I tried to make a rock-paper-scissors game that beginners can run on the console.
I tried to make an application in 3 months from inexperienced
[Java] Make variables in extended for statement and for Each statement immutable
I tried to modernize a Java EE application with OpenShift.
I want to make a list with kotlin and java!
I just wanted to make a Reactive Property in Java
I want to make a function with kotlin and java!
I tried using Hotwire to make Rails 6.1 scaffold a SPA
I tried to make Java Optional and guard clause coexist
I tried to convert a string to a LocalDate type in Java
[Unity] I tried to make a native plug-in UniNWPathMonitor using NWPathMonitor
I tried connecting to Oracle Autonomous Database 21c with JDBC Thin
Connecting to a database with Java (Part 1) Maybe the basic method
I tried to make an Android application with MVC now (Java)
I tried to generate a C language program source from cURL
A story that I finally understood Java for statement as a non-engineer
I tried to make a group function (bulletin board) with Rails
I tried to make a sample program using the problem of database specialist in Domain Driven Design
How to make a Java container
How to make a JDBC driver
I tried to interact with Java
Access Teradata from a Java application
I tried to explain the method
I tried to summarize Java learning (1)
I tried to summarize Java 8 now
How to make a Java array
I tried to make a parent class of a value object in Ruby
I tried to make a simple face recognition Android application using OpenCV
[Java] How to turn a two-dimensional array with an extended for statement
[iOS] I tried to make a processing application like Instagram with Swift
I tried to make a Web API that connects to DB with Quarkus
Java beginner tried to make a simple web application using Spring Boot
I made a virtual currency arbitrage bot and tried to make money
I tried to create a method to apply multiple filters at once with Java Stream API. Is this okay?
When I wanted to create a method for Premium Friday, it was already in the Java 8 standard API