【Java】JDBCを利用し、データベースに接続、操作する

JavaにはJDBCというデータベース(以下DB)へ接続するための仕組みがあります。

DBを利用すると、より大規模なデータ蓄積をSQLを使って行うことができます。

要はすっごいセーブデータですねw

データベースを準備する

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);
				}
			}
		}
		
	}
}
入力をgameとした場合の結果
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)

エラーが発生しました。

テーブルがどうなっているか見てみましょうか。

testテーブル

データがありません・・・

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分間待ってやる。

うまくストアドプロシージャを呼び出せました。

Java

Posted by nompor