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.
The development environment this time is as follows.
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);
}
}
}
}
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
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.
--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