サムネがコーヒーの記事は書きかけです。

JavaからSQLiteに接続してデータベースの操作を行う方法

SHARE

Javaを使用してSQLiteを操作する方法をまとめます。

なかなかシンプルな記事がなかったので、コピペで実行できるようにします。

環境構築

SQLite3をダウンロード済みの前提で話を進めていきます。

以下のサイトからJava JDBCのダウンロードをする必要があります。

https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc

データベース接続クラス

存在しないデータベースを指定すると、新たにデータベースが作成されます。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Database {

    private final String DB_NAME = "database.db";

    private Connection c = null;

    private Statement stmt = null;

    public Database(){
        try{
            Class.forName("org.sqlite.JDBC");
            this.c = DriverManager.getConnection("jdbc:sqlite:" + this.DB_NAME);

            this.stmt = c.createStatement();  
        }catch (Exception e){
            System.out.println(e);
        }
    }
}

CRUD処理

テーブルの作成

id INT, value INT, description STRINGのカラムを持つテーブルを作成します。

public void createTable(String tablename){
        try{
            this.stmt.executeQuery(
                "CREATE TABLE " + tablename +
                """
                (
                id INTEGER,
                value INTEGER,
                description STRING
                )
                """);
            this.c.commit();
        }catch (SQLException e){
           System.out.println("------------------------------------------------");
           System.out.println(tablename+ " already exists.");
           System.out.println("------------------------------------------------");
        }
    }

データの追加(INSERT)

 public void insertData(String tablename,int id, int value, String description){
        try{
            this.stmt.executeUpdate(
                "INSERT INTO " + tablename +
                """
                (
                \"id\",
                \"value\",
                \"description\"
                )
                """ 
                + 
                "VALUES(" 
                + id + ","
                + value + ","
                + "\"" +  description + "\")"
                );
                
            System.out.println("id"  + "->" + id + ", value"  + "->" + value +  ", description"  + "->" + description + " for " + tablename );
            
        }catch (SQLException e){
          System.out.println(e);
        }
    }

データの更新

public void updateTable(String tablename,int id){
        try{
            this.stmt.executeUpdate(
                "UPDATE " + tablename + " SET " + 
                """
                description = \" updated description.\" WHERE id = 
                """ 
                + 
                id
                );
        }catch (SQLException e){
          System.out.println(e);
        }
    }

データの取得


    public void readTable(String tablename){
        try{
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + tablename);
            while (rs.next()) {
                int id = rs.getInt("id"); // get data of id col.
                String value = rs.getString("value"); // get data of value col.
                String description = rs.getString("description");// get data of description col.
                System.out.println(id + "," + value + ","+ description); // print out the data.
            }
            rs.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

テーブルの削除

public void dropTable(String tablename){
        try{
            this.stmt.executeUpdate(
                "DROP TABLE " + tablename
                );
        }catch (SQLException e){
          System.out.println(e);
        }
    }

接続の遮断

一連の操作が終わった後にデータベースとの接続を切れるようにするために、別途メソッドを用意しました。


    public void closeConnection(){
        try {
            if (this.c != null) {
                this.c.close();
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

実行結果

試しに上記のコードを実行してみます。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Database {

    private final String DB_NAME = "database.db";

    private Connection c = null;

    private Statement stmt = null;

    public Database(){
        try{
            Class.forName("org.sqlite.JDBC");
            this.c = DriverManager.getConnection("jdbc:sqlite:" + this.DB_NAME);

            this.stmt = c.createStatement();  
        }catch (Exception e){
            System.out.println(e);
        }
    }

    public Database(String dbname){
        try{
            Class.forName("org.sqlite.JDBC");
            this.c = DriverManager.getConnection("jdbc:sqlite:" + dbname);
            this.stmt = c.createStatement();
        }catch (Exception e){
            System.out.println(e);
        }
    }

    public void createTable(String tablename){
        try{
            this.stmt.executeQuery(
                "CREATE TABLE " + tablename +
                """
                (
                id INTEGER,
                value INTEGER,
                description STRING
                )
                """);
            this.c.commit();
        }catch (SQLException e){
           System.out.println("------------------------------------------------");
           System.out.println(tablename+ " already exists.");
           System.out.println("------------------------------------------------");
        }
    }

    public void readTable(String tablename){
        try{
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + tablename);
            while (rs.next()) {
                int id = rs.getInt("id"); // get data of id col.
                String value = rs.getString("value"); // get data of value col.
                String description = rs.getString("description");// get data of description col.
                System.out.println(id + "," + value + ","+ description); // print out the data.
            }
            rs.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    public void insertData(String tablename,int id, int value, String description){
        try{
            this.stmt.executeUpdate(
                "INSERT INTO " + tablename +
                """
                (
                \"id\",
                \"value\",
                \"description\"
                )
                """ 
                + 
                "VALUES(" 
                + id + ","
                + value + ","
                + "\"" +  description + "\")"
                );
                
            System.out.println("id"  + "->" + id + ", value"  + "->" + value +  ", description"  + "->" + description + " for " + tablename );
            
        }catch (SQLException e){
          System.out.println(e);
        }
    }

    public void updateTable(String tablename,int id){
        try{
            this.stmt.executeUpdate(
                "UPDATE " + tablename + " SET " + 
                """
                description = \" updated description.\" WHERE id = 
                """ 
                + 
                id
                );
        }catch (SQLException e){
          System.out.println(e);
        }
    }


    
    public void dropTable(String tablename){
        try{
            this.stmt.executeUpdate(
                "DROP TABLE " + tablename
                );
        }catch (SQLException e){
          System.out.println(e);
        }
    }


    public void closeConnection(){
        try {
            if (this.c != null) {
                this.c.close();
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public static void main(String[] args) {
        Database db = new Database();

        String tablename = "table1";
        db.createTable(tablename);
        
        for (int i = 1; i < 11; i++) {
            db.insertData(tablename, i,i*100 , "description " + i);
        }
        
        db.updateTable(tablename, 1);
        
        db.readTable(tablename);

        db.dropTable(tablename);
        
        db.closeConnection();
    }
  
}
>>>
------------------------------------------------
table1 already exists.
------------------------------------------------
id->1, value->100, description->description 1 for table1
id->2, value->200, description->description 2 for table1
id->3, value->300, description->description 3 for table1
id->4, value->400, description->description 4 for table1
id->5, value->500, description->description 5 for table1
id->6, value->600, description->description 6 for table1
id->7, value->700, description->description 7 for table1
id->8, value->800, description->description 8 for table1
id->9, value->900, description->description 9 for table1
id->10, value->1000, description->description 10 for table1
1,100, updated description.
2,200,description 2
3,300,description 3
4,400,description 4
5,500,description 5
6,600,description 6
7,700,description 7
8,800,description 8
9,900,description 9
10,1000,description 10

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

前の記事

グラムシュミットの直交化によるQR分解と固有値問題

次の記事

Pandasを使用したSQLiteへのデータ登録のテンプレート