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