[JDBC] I tried to access the SQLite3 database from Java.

This time, I was studying database access from Java in the process of software development, and I succeeded in reading the database, so I wrote an article. Since this is almost my first experience with database access using JDBC, I would like to do it step by step as a review of SQLite3.

For how to use SQLite3, please refer to the article Qiita: SQLite3 operation command related summary.

environment

The development environment this time is as follows.

• Ubuntu 18.04.5LTS
• OpenJDK 11.0.8
• SQLite3 3.22.0

Create a database

Create a database in advance to read elements from an existing database.

Create database

sqlite3 test.db

Define table1

CREATE TABLE table1(id INTEGER PRIMARY KEY, name TEXT NOT NULL);

At this point, the database files will be created in the directory.

I put some data in the INSERT statement, so let's take a look at it in the SELECT statement.

SELECT * FROM table1;
id          name      
----------  ----------
1           satou     
2           suzuki    
3           tanaka    
4           katou     
5           takahashi 

I was able to confirm the database.

JDBC download

A JDBC driver is required to access SQLite3 from Java. Download JDBC from the JDBC Repository (https://bitbucket.org/xerial/sqlite-jdbc/downloads/). I think the version can be any one, but this time I will download the latest sqlite-jdbc-3.30.1.jar at the time of writing.

Once you've downloaded JDBC, copy it to the directory that contains the database you just created.

Java programming creation

This time, I created a java file by making appropriate changes based on the code of the site that I referred to.

TestDAtabaseDriver.java

import java.sql.*;

public class TestDatabaseDriver {
    public static void main(String[] args) {
 
        Connection connection = null;
        Statement statement = null;
 
        try {
            Class.forName("org.sqlite.JDBC");

            //Specify the database PATH. It seems that you can go with either a relative path or an absolute path
            connection = DriverManager.getConnection("jdbc:sqlite:test.db");
            statement = connection.createStatement();
            String sql = "select * from table1";
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
        } catch (ClassNotFoundException e) {
          e.printStackTrace();
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Compile and run

javac *.java && java -classpath .:sqlite-jdbc-3.30.1.jar TestDatabaseDriver

Execution result

1
2
3
4
5

From this result, it seems that the part to be read is determined by the System.out.println (rs.getString (1)); part of TestDAtabaseDriver.java, so let's make some changes.

Change before

System.out.println(rs.getString(1));

After change

System.out.println(rs.getString(1) + "|" + rs.getString(2));

I will try it.

Compile and run

javac *.java && java -classpath .:sqlite-jdbc-3.30.1.jar TestDatabaseDriver

Execution result

1|satou
2|suzuki
3|tanaka
4|katou
5|takahashi

Summary

I was able to read the database safely. This time I tried JDBC for the first time, so most of it was copied and pasted, but I would like to expand the range based on this and make it highly reusable.

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 /)