31 May 2015

(Java) MySQLとSQLiteのデータベースにアクセスする

JavaでMySQLとSQLiteのデータベースにアクセスするサンプル

JDBCライブラリ

SQLite用のJDBCライブラリは「SQLite JDBC Driver」からダウンロードする。2015年5月現在、sqlite-jdbc-3.8.6.jar が問題なく動作する安定バージョン。

MySQL用のJDBCライブラリは、Ubuntuの場合apt-get install libmysql-javaでシステムディレクトリ内(/usr/share/java)にmysql.jarが格納される。Windows等の場合は「Download Connector/J」からダウンロードすれば良いようだ。

JDBCによるSQL利用(公式マニュアル)

Oracleの公式ドキュメント「Processing SQL Statements with JDBC」に全て書かれている。

SQLサーバへの接続するには、次のメソッドを利用し

Connection conn = DriverManager.getConnection(...);

確立したコネクションを用いて、SQL命令を実行する。値が返るSQL命令はexecuteQuery()を、値が返らない命令はexecuteUpdate()を用いる。

直にSQL命令が入った文字列を実行する場合は次のようにし、

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL命令の文字列);

文字列が汚染された場合の脆弱性を排除するために、プレースホルダーを用いて

Statement stmt = conn.prepareStatement(String Query);
stmt.setString(1, 文字列);        // 1個目のプレースホルダーの置換
stmt.setInt(2, 数値);             // 2個目のプレースホルダーの置換
ResultSet rs = stmt.executeQuery();

とすることが推奨されていたりする。(IPAの「安全なSQLの呼び出し方」を参照)

MySQLの場合

赤の部分がSQLサーバとの接続関連、青がクエリ発行、緑がSQLiteとの主な違いの部分を示す。

// 開始時間を保存する
long timeStart = System.currentTimeMillis();
 
PreparedStatement stmt = null;
Connection conn = null;
 
try{
    // SQLサーバに接続する
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc:mysql://172.16.138.138:3306/test_db?useUnicode=true&characterEncoding=UTF-8",
            "test", "pass");
     
    // テーブル tbl_test が存在するかチェックする
    String strSql = "SHOW TABLES FROM test_db LIKE ?";
    stmt = conn.prepareStatement(strSql);
    stmt.setString(1, "tbl_test");
    ResultSet rs = stmt.executeQuery();
    boolean isTableExist = rs.next();
    stmt.close();
    
    // テーブル tbl_test が存在する場合は、削除(DROP TABLE)する
    if(isTableExist){
        strSql = "DROP TABLE ?";
        stmt = conn.prepareStatement(strSql);
        stmt.setString(1, "tbl_test");
        stmt.executeUpdate();
        stmt.close();
    }
    
    // テーブル tbl_test を作成(CREATE TABLE)する
    // (utf8のテキストは TEXT CHARACTER SET utf8 とするか LONGTEXT で定義する)
    strSql = "CREATE TABLE ? (" +
            "id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL," +
            "name TEXT CHARACTER SET utf8 NOT NULL," +
            "address LONGTEXT," +
            "tel LONGTEXT )";
    stmt = conn.prepareStatement(strSql);
    stmt.setString(1, "tbl_test");
    stmt.executeUpdate();
    stmt.close();
    
    // CSVファイルを読み込み、各行を順次SQLデータベースに追加(INSERT INTO)する
    FileInputStream fileInputStream = new FileInputStream("/home/user/test.csv");
    BufferedReader reader = new BufferedReader(new InputStreamReader(fileInputStream,"UTF-8"));
    String str;
    // 自動コミットを無効化する
    conn.setAutoCommit(false);
    String strSql = "INSERT INTO tbl_test (name, address, tel) values (?, ?, ?)";
    stmt = conn.prepareStatement(strSql);
    while ((str = reader.readLine() ) != null){
        String[] strElem = str.split(",");
        stmt.setString(1, strElem[0]);
        stmt.setString(2, strElem.length >= 2 ? strElem[1] : "空欄");
        stmt.setString(3, strElem.length >= 3 ? strElem[2] : "空欄");
        stmt.executeUpdate();
    }
    reader.close();
    fileInputStream.close();
    // 全データのINSERT INTO後にコミットする
    conn.commit();
    // 自動コミットを元に戻す(有効化する)
    conn.setAutoCommit(true);
    stmt.close();
    
    // SQLデータベースよりデータを読み出す
    stmt = conn.prepareStatement("SELECT * FROM tbl_test LIMIT 5");
    ResultSet rs = stmt.executeQuery();
    while(rs.next()){
        System.out.printf("name=%s, Addr=%s, tel=%s\n", rs.getString(2),
                rs.getString(3), rs.getString(4));
    }
    stmt.close();
    
    // データベースを閉じる
    conn.close();
} catch (IOException | ClassNotFoundException | SQLException e) {
    // TODO 自動生成された catch ブロック
    e.printStackTrace();
        try {
            if(stmt != null) stmt.close();
            if(conn != null) conn.close();
        } catch (SQLException e1) {
            // TODO 自動生成された catch ブロック
            e1.printStackTrace();
        }
}
// 実行に要した時間を表示する(autocommitあり、なしの比較のため)
System.out.printf("SQL実行にかかった時間 %d ミリ秒\n", System.currentTimeMillis()-timeStart);

MySQLデータベース側での準備

データベースの作成と、ユーザの作成、アクセス権限の付与

mysql > create database test_db;
mysql > grant all on test_db to test@"%";
mysql > set password for test@"%"=password('pass');

ユーザのアクセス元の制限解除と、パスワード付与を確認

mysql > select host,user,password from mysql.user;
+---------------+------------------+-------------------------------------------+
| host          | user             | password                                  |
+---------------+------------------+-------------------------------------------+
| localhost     | root             | *E8C709AA631DBC486ECABD29C40C6A920C3202DD |
| vm-ubuntu1204 | root             | *E8C709AA631DBC486ECABD29C40C6A920C3202DD |
| 127.0.0.1     | root             | *E8C709AA631DBC486ECABD29C40C6A920C3202DD |
| ::1           | root             | *E8C709AA631DBC486ECABD29C40C6A920C3202DD |
| localhost     | debian-sys-maint | *74F9808F6BFB56DB228F4D59E37E45F5BF4E5034 |
| %             | test             | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
+---------------+------------------+-------------------------------------------+
6 rows in set (0.00 sec)

データベースの言語をutf8に変更する

mysql > alter database test_db default character set=utf8;

SQLiteの場合

// 開始時間を保存する
long timeStart = System.currentTimeMillis();
 
PreparedStatement stmt = null;
Connection conn = null;
 
try{
    // SQLサーバに接続する
    Class.forName("org.sqlite.JDBC");
    conn = DriverManager.getConnection("jdbc:sqlite:/home/user/testdb.sqlite");
     
    // テーブル tbl_test が存在するかチェックする
    String strSql = "SELECT COUNT(*) FROM sqlite_master WHERE name=?";
    stmt = conn.prepareStatement(strSql);
    stmt.setString(1, "tbl_test");
    ResultSet rs = stmt.executeQuery();
    int tableExist = rs.getInt(1);
    stmt.close();
    
    // テーブル tbl_test が存在する場合は、削除(DROP TABLE)する
    if(tableExist != 0){
        strSql = "DROP TABLE ?";
        stmt = conn.prepareStatement(strSql);
        stmt.setString(1, "tbl_test");
        stmt.executeUpdate();
        stmt.close();
    }
    
    // テーブル tbl_test を作成(CREATE TABLE)する
    strSql = "CREATE TABLE tbl_test (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
            "name TEXT NOT NULL," +
            "address TEXT," +
            "tel TEXT )";
    stmt = conn.prepareStatement(strSql);
    stmt.setString(1, "tbl_test");
    stmt.executeUpdate();
    stmt.close();
    
    // CSVファイルを読み込み、各行を順次SQLデータベースに追加(INSERT INTO)する
    FileInputStream fileInputStream = new FileInputStream("/home/user/test.csv");
    BufferedReader reader = new BufferedReader(new InputStreamReader(fileInputStream,"UTF-8"));
    String str;
    // 自動コミットを無効化する
    conn.setAutoCommit(false);
    String strSql = "INSERT INTO tbl_test (name, address, tel) values (?, ?, ?)";
    stmt = conn.prepareStatement(strSql);
    while ((str = reader.readLine() ) != null){
        String[] strElem = str.split(",");
        stmt.setString(1, strElem[0]);
        stmt.setString(2, strElem.length >= 2 ? strElem[1] : "空欄");
        stmt.setString(3, strElem.length >= 3 ? strElem[2] : "空欄");
        stmt.executeUpdate();
    }
    reader.close();
    fileInputStream.close();
    // 全データのINSERT INTO後にコミットする
    conn.commit();
    // 自動コミットを元に戻す(有効化する)
    conn.setAutoCommit(true);
    stmt.close();
    
    // SQLデータベースよりデータを読み出す
    stmt = conn.prepareStatement("SELECT * FROM tbl_test LIMIT 5");
    ResultSet rs = stmt.executeQuery();
    while(rs.next()){
        System.out.printf("name=%s, Addr=%s, tel=%s\n", rs.getString(2),
                rs.getString(3), rs.getString(4));
    }
    stmt.close();
    
    // データベースを閉じる
    conn.close();
} catch (IOException | ClassNotFoundException | SQLException e) {
    // TODO 自動生成された catch ブロック
    e.printStackTrace();
        try {
            if(stmt != null) stmt.close();
            if(conn != null) conn.close();
        } catch (SQLException e1) {
            // TODO 自動生成された catch ブロック
            e1.printStackTrace();
        }
}
// 実行に要した時間を表示する(autocommitあり、なしの比較のため)
System.out.printf("SQL実行にかかった時間 %d ミリ秒\n", System.currentTimeMillis()-timeStart);