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.
The environment this time is as follows.
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.
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.
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
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.
[Database] SQLite3 / JDBC Summary
--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