[JAVA] [JDBC ③] I tried to input from the main method using placeholders and arguments.

In Previous article, divide the SQL INSERT statement, UPDATE statement, DELETE statement, and SELECT statement by method, and execute the SQL statement with the main method. I succeeded in doing so. It was not practical because the data I wanted to add, change, or delete was fixed in the SQL statement.

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

environment

The environment this time is as follows.


Looking back on the last time

First, let's take a 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 can't add arbitrary data just by calling it from the main method.


What you need to do 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 two items.

--Use placeholders --Use PreparedStatement instead ofStatement

You can use placeholders to pass arbitrary strings to SQL statements. Also, when using placeholders, it is necessary to define SQL statements in advance, so it is now possible to pass data using PreparedStatement instead ofStatement as follows.

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 the id to make the source code as easy to understand as possible.


Increase method reusability

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

The source code and execution result are described 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 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(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 statement
     */
    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);
            }
        }
    }

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

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

Summary

I think this change has brought us closer to code that is more reusable. In the future, I would like to actually use it in software and make it a better source code.


This article table of contents page

[Database] SQLite3 / JDBC Summary


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 /) -Sample to insert / update / delete with Java SQLite

Recommended Posts

[JDBC ③] I tried to input from the main method using placeholders and arguments.
I want to call the main method using reflection
I tried to explain the method
[JDBC] I tried to access the SQLite3 database from Java.
[Rails] I tried to raise the Rails version from 5.0 to 5.2
[Rails] I tried using the button_to method for the first time
I tried to summarize the basics of kotlin and java
I want to call a method and count the number
I tried to build the environment little by little using docker
[Rails] I tried to implement "Like function" using rails and js
I tried upgrading from CentOS 6.5 to CentOS 7 with the upgrade tool
I tried to integrate Docker and Maven / Netbean nicely using Jib
I tried to understand how the rails method "redirect_to" is defined
I tried to understand how the rails method "link_to" is defined
I tried to summarize the methods of Java String and StringBuilder
[Java] I tried to make a maze by the digging method ♪
I tried to solve the paiza campaign problem "Challenge from Kaito 813"
I tried to display the calendar on the Eclipse console using Java.
I tried to summarize the methods used
I summarized the points to note when using resources and resources in combination
I introduced WSL2 + Ubuntu to Window10 and tried using GDC, DMD, LDC
I tried using UICollectionViewListCell added from Xcode12.
[Ruby] From the basics to the inject method
I tried to summarize the key points of gRPC design and development
I tried to make my own transfer guide using OpenTripPlanner and GTFS
I wanted to add @VisibleForTesting to the method
I was addicted to the roll method
I tried to implement the Iterator pattern
I want to pass the argument of Annotation and the argument of the calling method to aspect
[JDBC] I tried to make SQLite3 database access from Java into a method for each SQL statement.
Iterative processing of Ruby using each method (find the sum from 1 to 10)
I tried to summarize the Stream API
I tried to build Ruby 3.0.0 from source
I translated the grammar of R and Java [Updated from time to time]
Rails API mode I tried to implement the keyword multiple search function using arrays and iterative processing.
I tried to measure and compare the speed of GraalVM with JMH
[Ruby] I want to make an array from a character string with the split method. And vice versa.
[Java] I installed JDBC and tried to connect with servlet + MySQL. (There is a version using DAO / Bean)
I tried to get the distance from the address string to the nearest station with ruby
[Ruby] How to calculate the total amount using the initialize method and class variables
I tried using Docker for the first time
I tried to organize the session in Rails
I tried to link grafana and postgres [docker-compose]
[Android] I quit SQLite and tried using Realm
I tried hitting a Java method from ABCL
[API] I tried using the zip code search API
I tried to link JavaFX and Spring Framework.
I tried to set tomcat to run the Servlet.
I tried to implement a server using Netty
I tried using the profiler of IntelliJ IDEA
I called YouTube video from DB with haml and tried to embed and display it
I tried using Wercker to create and publish a Docker image that launches GlassFish 5.
Pass arguments to the method and receive the result of the operation as a return value
I tried to investigate the mechanism of Emscripten by using it with the Sudoku solver
I tried to organize the cases used in programming
I tried using the Server Push function of Servlet 4.0
Tokoro I rewrote in the migration from Wicket 7 to 8
I tried to read and output CSV with Outsystems
I tried to summarize the state transition of docker
I tried to operate SQS using AWS Java SDK
05. I tried to stub the source of Spring Boot