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

2 minute read

This time, I was studying database access from Java in the process of software development and succeeded in reading the database, so I made an article. Since I’m almost new to database access using JDBC, I would like to follow along with a review of SQLite3.

For details on how to use SQLite3, please refer to the article Qiita:Summary of SQLite3 operation commands.

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

table1

``` define sql

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

At this point, the database files will be created in the directory.
<img width = "400" alt = "testJDBC_Qiita.png" src = "https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/150278/a090f761-988e-d6c0-585a -d861ce676f1e.png">

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

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

The database can be confirmed.


Download JDBC

A JDBC driver is required to access SQLite3 from Java. Download JDBC from JDBC repository. Any version can be used, but this time download the latest sqlite-jdbc-3.30.1.jar at the time of writing.

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


Java programming creation

This time, I made 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 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
Four
Five

From this result, 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.

before change


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 it was the first time I tried JDBC, so most of it started with copy and paste, but I would like to broaden the range and make it highly reusable.


This article table of contents

[Database] SQLite3/JDBC Summary


Reference site