【Java】JDBCを利用し、データベースに接続、操作する
JavaにはJDBCというデータベース（以下DB）へ接続するための仕組みがあります。
DBを利用すると、より大規模なデータ蓄積をSQLを使って行うことができます。
要はすっごいセーブデータですねｗ
データベースを準備する
Javaで利用可能なDBはたくさんあります。少なくとも下記のようなDBには対応しているでしょう。
・SQLite
・PostgreSQL
・MySQL
・Oracle Database
・Db2
・SQLServer
この中でお手軽に始めるのならSQLiteです。おススメは無料で使えて現場でも使われているPostgreSQL、MySQLです。
Oracle DatabaseやSQLServerなどは有料なので個人で使うことはあまりないですね。私も使ったことはありません。
自分が使用したいDBを使ってもらうと良いかと思います。
私がテストで使ったものはPostgreSQLです。
DBが決まったら環境にインストールしておきましょう。
JDBCライブラリを準備する
Javaで各データベースを利用するためのJDBCライブラリを準備します。
Oracle Database JDBC
SQLite JDBC
PostgreSQL JDBC
MySQL JDBC
Db2 JDBC
SQLServer JDBC
テストで利用するのはPostgreSQLなのでPostgreSQLのJDBCドライバーのjarをダウンロードしておきました。
接続
PostgreSQLのJDBC接続は下記のようなコードを記述します。
Connection connection = DriverManager.getConnection("jdbc:postgresql://接続先ホスト:接続先ポート/DB名",ユーザー名,パスワード);
DBをインストールしたときのユーザー名、パスワード、ポートなどの情報が必要です。
別サーバーに接続する場合は接続ホストの部分はIPアドレスにするなり修正してください。
DB名がない場合はユーザー名のDBに対してアクセスされます。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) throws SQLException {
//接続先の情報
String url="jdbc:postgresql://localhost:5432/";
String user="postgres";
String pass="postgres";
//接続処理
try(Connection connection = DriverManager.getConnection(url,user,pass)){
//接続情報を表示
System.out.println(connection);
}
}
}
org.postgresql.jdbc4.Jdbc4Connection@769c9116
接続に失敗した場合は例外が発生します。
SQL文を実行してみる
それでは早速インサート文を実行してデータを追加し、表示してみましょう。
まずはデータベースを作成します。
今回はこんな感じの簡単なテーブルを用意しました。
create table test( id integer primary key ,cd varchar(10) ,value text );
SQLを実行する場合はStatementオブジェクトを取得する必要があります。
StatementオブジェクトのexecuteUpdateメソッドでinsert文を実行できます。
それでは実際にデータを追加してみましょう。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
public static void main(String[] args) throws SQLException {
//接続先の情報
String url="jdbc:postgresql://localhost:5432/";
String user="postgres";
String pass="postgres";
//接続処理
try(Connection connection = DriverManager.getConnection(url,user,pass);
Statement statement = connection.createStatement();){
//insert文
StringBuilder sql = new StringBuilder();
sql.append("insert into test values");
sql.append("(1,'test','テスト')");
sql.append(",(2,'anime','アニメ')");
sql.append(",(3,'game','ゲーム');");
//SQL実行
statement.executeUpdate(sql.toString());
}
}
}
続いて追加したデータを表示してみましょう。
StatementオブジェクトのexecuteQueryメソッドでselect文を実行できます。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
public static void main(String[] args) throws SQLException {
//接続先の情報
String url="jdbc:postgresql://localhost:5432/";
String user="postgres";
String pass="postgres";
//接続処理
try(Connection connection = DriverManager.getConnection(url,user,pass);
Statement statement = connection.createStatement();){
//select文
StringBuilder sql = new StringBuilder();
sql.append("select * from test;");
//SQL実行
try(ResultSet result = statement.executeQuery(sql.toString())){
while(result.next()) {
int id = result.getInt("id");
String cd = result.getString("cd");
String value = result.getString("value");
System.out.printf("id=%d,",id);
System.out.printf("cd=%s,",cd);
System.out.printf("value=%s%n",value);
}
}
}
}
}
id=1,cd=test,value=テスト id=2,cd=anime,value=アニメ id=3,cd=game,value=ゲーム
PreparedStatementを利用してSQLインジェクションを防ぐ
SQLインジェクションはSQL文の構文の一部を改変することによって開発者が意図しないSQL文を不正実行してしまう攻撃手法のことです。
例えば次のような標準入力から値を取得し、cdの値で検索ができる仕組みを実装したとしましょうか。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class Test {
public static void main(String[] args) throws SQLException {
//接続先の情報
String url="jdbc:postgresql://localhost:5432/";
String user="postgres";
String pass="postgres";
//接続処理
try(Connection connection = DriverManager.getConnection(url,user,pass);
Statement statement = connection.createStatement();
Scanner sc = new Scanner(System.in)){
//検索したい文字列を入力
System.out.println("cdカラムで検索したい値を入力してね！");
String findTx = sc.nextLine();
//select文
StringBuilder sql = new StringBuilder();
sql.append("select * from test where cd='"+findTx+"';");
//SQL実行
try(ResultSet result = statement.executeQuery(sql.toString())){
while(result.next()) {
int id = result.getInt("id");
String cd = result.getString("cd");
String value = result.getString("value");
System.out.printf("id=%d,",id);
System.out.printf("cd=%s,",cd);
System.out.printf("value=%s%n",value);
}
}
}
}
}
cdカラムで検索したい値を入力してね！ game id=3,cd=game,value=ゲーム
普通の検索プログラムに見えるかもしれませんが、このプログラムはSQLインジェクションの脆弱性があります。
検索値に次のような文字列を指定してみましょう。
';delete from test where true or cd='
cdカラムで検索したい値を入力してね！ ';delete from test where true or cd=' Exception in thread "main" org.postgresql.util.PSQLException: クエリの実行により、複数のResultSetが返されました。 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:290) at Test.main(Test.java:32)
エラーが発生しました。
テーブルがどうなっているか見てみましょうか。
データがありません・・・
delete文が実行され、testテーブルのデータが削除されてしまったようです。
これがSQLインジェクション攻撃の一例です。
このようなパターンの脆弱性を回避するためにJDBCにはあらかじめ、SQLインジェクションの対策を行うPreparedStatementがありますので是非使用方法を覚えるようにしましょう。
PreparedStatement statement = connection.prepareStatement(SQL文)
でインスタンスを取得できます。
引数に指定するSQL文字列はエスケープしたい箇所を?にしておきます。
statement.setString(?の順番「1～」, ?に置き換えられる値);
などで指定できます。
setStringの部分はsetIntなどもありますので、必要であれば、それぞれ引数を変更してみましょう。
それでは試しにPreparedStatementを使った検索プログラムに変更し、試してみましょうか。
テーブルが消えている場合は先に下記のSQLを流しておきましょう。
insert into test values (1,'test','テスト') ,(2,'anime','アニメ') ,(3,'game','ゲーム');
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class Test {
public static void main(String[] args) throws SQLException {
//接続先の情報
String url="jdbc:postgresql://localhost:5432/";
String user="postgres";
String pass="postgres";
//接続処理
try(Connection connection = DriverManager.getConnection(url,user,pass);
Scanner sc = new Scanner(System.in)){
//検索したい文字列を入力
System.out.println("cdカラムで検索したい値を入力してね！");
String findTx = sc.nextLine();
//select文
StringBuilder sql = new StringBuilder();
sql.append("select * from test where cd=?;");
try(PreparedStatement statement = connection.prepareStatement(sql.toString())){
statement.setString(1, findTx);
//SQL実行
try(ResultSet result = statement.executeQuery()){
while(result.next()) {
int id = result.getInt("id");
String cd = result.getString("cd");
String value = result.getString("value");
System.out.printf("id=%d,",id);
System.out.printf("cd=%s,",cd);
System.out.printf("value=%s%n",value);
}
}
}
}
}
}
cdカラムで検索したい値を入力してね！ ';delete from test where true or cd='
これでSQLインジェクションは発生せず、検索処理も問題なく動きます。
トランザクション
トランザクションとは、一連の処理を一つのまとまった処理として扱ったものです。
DBを扱うプログラムでは一連の処理が正常に終了したらDBの更新内容を確定させ、異常が発生した場合は元に戻すということを行います。
これらの制御を行うにはconnection.setAutoCommit(false)を指定し、手動でcommit（確定）、rollback（キャンセル）メソッドを実行することで実現します。
次のサンプルはcd=animeのデータ行のvalueを更新するプログラムです。更新後に正常終了させるか異常発生させるかを選択させ、正常終了ならcommit、異常発生したらrollbackします。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class Test {
public static void main(String[] args) throws SQLException {
//接続先の情報
String url="jdbc:postgresql://localhost:5432/";
String user="postgres";
String pass="postgres";
//接続処理
Connection connection = DriverManager.getConnection(url,user,pass);
try(Scanner sc = new Scanner(System.in)){
//自動コミットの無効化
connection.setAutoCommit(false);
//更新したい値を入力
System.out.println("cd=animeのvalueを何に更新する？");
String findTx = sc.nextLine();
//update文
StringBuilder sql = new StringBuilder();
sql.append("update test set value=? where cd='anime';");
try(PreparedStatement statement = connection.prepareStatement(sql.toString())){
statement.setString(1, findTx);
//SQL実行
statement.executeUpdate();
}
System.out.println("更新実行！次の操作を選択してね。");
System.out.println("0:正常終了 1:異常発生");
String ctrl = sc.nextLine();
switch(ctrl) {
case "0":break;
default:throw new SQLException();
}
//更新内容の確定
connection.commit();
} catch ( SQLException e ) {
//更新内容のキャンセル
connection.rollback();
throw e;
} finally {
//切断
connection.close();
}
}
}
まずは異常発生の動作を見てみましょう。
cd=animeのvalueを何に更新する？ falskjdf 更新実行！次の操作を選択してね。 0:正常終了 1:異常発生 1 Exception in thread "main" java.sql.SQLException at Test.main(Test.java:45)
テーブルの内容が更新されていませんので想定通りの動作です。
では正常終了の動作も見ていきましょう。
cd=animeのvalueを何に更新する？ fjlkasdf 更新実行！次の操作を選択してね。 0:正常終了 1:異常発生 0
想定通り、更新されました。
このようにエラー時は更新処理をなかったことにできるので非常に便利です。
CallableStatementによるストアドプロシージャの実行
PostgreSQLのストアドプロシージャを実行する方法を見ていきます。
ストアドプロシージャとはデータベースに定義できる関数です。
Javaでは、このストアドプロシージャを呼び出す方法があります。
connection.prepareCall(sql)で実行できます。
それでは実際にストアドプロシージャを定義してJavaから実行してみましょう。
CREATE OR REPLACE PROCEDURE public.proc(
IN val text, OUT res text)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
res = val || val;
END;
$BODY$;
この関数は第一引数valを二回繰り返した文字列を第二引数resに代入するだけの関数となります。
次はJavaの呼び出しコードです。
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Scanner;
public class Test {
public static void main(String[] args) throws SQLException {
//接続先の情報
String url="jdbc:postgresql://localhost:5432/";
String user="postgres";
String pass="postgres";
//接続処理
try(Connection connection = DriverManager.getConnection(url,user,pass);
Scanner sc = new Scanner(System.in)){
//call文作成
StringBuilder sql = new StringBuilder();
sql.append("call proc(?, ?)");
try(CallableStatement statement = connection.prepareCall(sql.toString())){
//第一引数INパラメータ
statement.setString(1, "3分間待ってやる。");
//第二引数OUTパラメータ設定
statement.registerOutParameter(2, Types.VARCHAR);
//実行
statement.execute();
//第二引数の値を取得
String val = statement.getString(2);
//表示
System.out.printf("%s",val);
}
}
}
}
3分間待ってやる。3分間待ってやる。
うまくストアドプロシージャを呼び出せました。