トップ «前の日記(2009年07月30日) 最新 次の日記(2009年08月04日)» 編集

Masa's blog

検索キーワード:

2009年07月31日 Java儂(わし)的解釈によるメモ(JDBC + Microsoft SQL Server 2005 Express Edition編) [長年日記]

_ Java儂(わし)的解釈によるメモ(JDBC + Microsoft SQL Server 2005 Express Edition編)

Windowsの事も知らんぷりできない状況になりつつある中、Microsoft SQL Server 2005 Express Editionという無償で使える MS SQL Serverが有る事を知り、早速試してみた。

基本的にはJava儂(わし)的解釈によるメモ(JDBC + MySQL編)の内容と大差無いが、やはり多少の方言と言うか違いは存在していた。

Windows機(IP=192.168.0.1)側にインストールする物

以上でサーバが起動する。

  • sqlcmd.exe(Windows側ツール)

sqlcmd.exeはWindows側でデータベースにアクセスするためのクライアント(コマンドライン版)。

インスタンス名はSQLExpressに固定。

ところでインスタンスてなんじゃという話だが、1個のインスタンスが1個のリスナーポートに対応していて、つまりこれが1個のデータベース(この中に多数のテーブルを抱える)サーバプロセスということらしい。(データベースはCREATE DATABASE testdb で作成する...)

sqlcmd -?
  ヘルプ表示
sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLExpress
  1> SQL命令文
  2> GO
  3> EXIT
sqlcmd -U sa -P SA-PASSWORD -S localhost\SQLExpress -s "," -W -i Hoge.sql

クライアント側(Linux or Windows)にインストールする物

Microsoft SQL Server JDBC Driver 2.0内のsqljdbc4.jarCLASSPATHに追加するか、実行時に

java -cp /somewhere/sqljdbc4.jar HogeHoge

の様に指定する。

サンプルプログラム

//
// 「jdbc mssql」テスト
//
// compile : javac -encoding utf-8 jdbc_mssql.java
// run     : java -cp .:sqljdbc4.jar jdbc_mssql
//
//import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

public class jdbc_mssql {

	static Connection conn = null;
	static Statement stmt = null;
	static PreparedStatement pstmt = null;
	static ResultSet rs = null;
	static ResultSetMetaData rsmd = null;

	public static void main(String[] args) {
		try {
//
// JDBC for MS SQL Serverのロード
//
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
			System.out.println("JDBCドライバをロードしました。");
//
// データベースへの接続
//
//			String url = "jdbc:sqlserver://192.168.0.1:1433";
//			conn = DriverManager.getConnection(url, "sa", "SA-PASSWORD");
			String url = "jdbc:sqlserver://192.168.0.1:1433;databaseName=testdb;user=sa;password=SA-PASSWORD;";
			conn = DriverManager.getConnection(url);
			System.out.println("jdbc:sqlserver://192.168.0.1:1433に接続しました。");
//
// 自動コミット有効
//
//   自動コミットを有効にしないと create index で、テーブル member を見つけられずに
//   例外が発生する。MS SQLServer特有の現象か?。
//
			conn.setAutoCommit(true);
			System.out.println("自動コミットをONにしました。");
//
// SQLステートメントオブジェクト生成
//
			stmt = conn.createStatement();
//
// テーブルの削除
//
			try {
				stmt.executeUpdate("drop table member");
				System.out.println("テーブル member を削除しました。");
			} catch(Exception e){
				System.out.println("テーブル member を削除できませんでした(" + e.getMessage() + ")");
			}
//
// テーブルの作成
//
			stmt.executeUpdate(
				"create table member (" +
					"id INT PRIMARY KEY," +
					"name VARCHAR(50)," +
					"job VARCHAR(50)" +
				")"
			);
			System.out.println("テーブル member を作成しました。");
//
// わざと2重にテーブル作成してみる
//
			try {
				stmt.executeUpdate(
					"create table member (" +
						"id INT PRIMARY KEY," +
						"name VARCHAR(50)," +
						"job VARCHAR(50)" +
					")"
				);
				System.out.println("テーブル member を作成しました(2)。");
			} catch(Exception e){
				System.out.println("テーブル member を作成できませんでした(2)(" + e.getMessage() + ")");
			}
//
// 2次インデックス作成
//
			stmt.executeUpdate("create index i_member_name on member(name)");
			System.out.println("インデックスを作成しました。");
//
// 自動コミット無効
//
			conn.setAutoCommit(false);
			System.out.println("自動コミットをOFFにしました(トランザクション開始)。");
//
// タプルの追加
//
			try {
				pstmt = conn.prepareStatement("insert into member (id, name, job) values (?, ?, ?)");
//
// 1件目
//
				pstmt.setInt(1, 1);
				pstmt.setString(2, "伊藤 太郎");
				pstmt.setString(3, "サラリーマン");
				pstmt.executeUpdate();
				System.out.println("インサートしました。");
//
// 2件目
//
				pstmt.setInt(1, 2);
				pstmt.setString(2, "山田 太郎");
				pstmt.setString(3, "野球選手");
				pstmt.executeUpdate();
				System.out.println("インサートしました。");
//
// 3件目
//
				pstmt.setInt(1, 3);
				pstmt.setString(2, "山田 花子");
				pstmt.setString(3, "タレント");
				pstmt.executeUpdate();
				System.out.println("インサートしました。");
//
// わざと3件目をもう一度追加してみる
//
				pstmt.setInt(1, 3);
				pstmt.setString(2, "山田 花子");
				pstmt.setString(3, "タレント");
				pstmt.executeUpdate();
				System.out.println("インサートしました。");
			} catch (Exception e) {
				System.out.println("インサートできませんでした(" + e.getMessage() + ")");
			}
//
// タプルの検索
//
			try {
//
// クエリの実行
//
				pstmt = conn.prepareStatement("select * from member where id >= ?");
				pstmt.setInt(1, 1);
				rs = pstmt.executeQuery();
//
// 1件目の取り出し
//
				rs.next();
				System.out.println("セレクトしました(id=" +
					rs.getInt("id") + ")(name=" +
					rs.getString("name") + ")(job=" +
					rs.getString("job") + ")。");
//
// 2件目の取り出し
//
				rs.next();
				System.out.println("セレクトしました(id=" +
					rs.getInt("id") + ")(name=" +
					rs.getString("name") + ")(job=" +
					rs.getString("job") + ")。");
//
// 3件目の取り出し
//
				rs.next();
				System.out.println("セレクトしました(id=" +
					rs.getInt("id") + ")(name=" +
					rs.getString("name") + ")(job=" +
					rs.getString("job") + ")。");
//
// わざと存在しない4件目を取り出してみる
//
				rs.next();
				System.out.println("セレクトしました(id=" +
					rs.getInt("id") + ")(name=" +
					rs.getString("name") + ")(job=" +
					rs.getString("job") + ")。");
			} catch (Exception e) {
				System.out.println("セレクトできませんでした(" + e.getMessage() + ")");
			}
//
// タプルの更新
//
			try {
//
// 更新ステートメント実行
//
				pstmt = conn.prepareStatement("update member set job = ? where name = ?");
				pstmt.setString(1, "裁判官");
				pstmt.setString(2, "山田 太郎");
				pstmt.executeUpdate();
				System.out.println("アップデートしました。");
//
// 更新結果を取り出すクエリの実行
//
				pstmt = conn.prepareStatement("select * from member where name = ?");
				pstmt.setString(1, "山田 太郎");
				rs = pstmt.executeQuery();
//
// クエリ結果の取り出し
//
				rs.next();
				System.out.println("アップデート結果(id=" +
					rs.getInt("id") + ")(name=" +
					rs.getString("name") + ")(job=" +
					rs.getString("job") + ")。");
			} catch (Exception e) {
				System.out.println("アップデートできませんでした(" + e.getMessage() + ")");
			}
//
// タプルの削除
//
			try {
//
// 削除ステートメント実行
//
				pstmt = conn.prepareStatement("delete from member where name like ?");
				pstmt.setString(1, "山田%");
				pstmt.executeUpdate();
				System.out.println("デリートしました(" + pstmt.getUpdateCount() + "件)。");
//
// 削除後の状態の検索クエリ実行
//
				pstmt = conn.prepareStatement("select * from member");
				rs = pstmt.executeQuery();
//
// クエリ結果の取り出し
//
				while (rs.next()){
					System.out.println("デリート結果(id=" +
						rs.getInt("id") + ")(name=" +
						rs.getString("name") + ")(job=" +
						rs.getString("job") + ")。");
				}
			} catch (Exception e) {
				System.out.println("デリートできませんでした(" + e.getMessage() + ")");
			}
//
// メタデータの取得
//
			try {
//
// メタデータの取得
//
				rsmd = rs.getMetaData();
//
// カラム数の取得
//
				int numberOfColumns = rsmd.getColumnCount();
				System.out.print("メタデータを取得しました(");
				for (int i = 1; i <= numberOfColumns; i++) {
//
// カラム名称の取得
//
					String name = rsmd.getColumnName (i);
					System.out.print(name + "\t");
				}
				System.out.println(")。");
			} catch (Exception e) {
				System.out.println("メタデータを取得できませんでした(" + e.getMessage() + ")");
			}
//
// コミット(トランザクションの反映)
//
			try {
				conn.commit();
				System.out.println("コミットしました。");
			} catch (Exception e) {
				System.out.println("コミットできませんでした(" + e.getMessage() + ")");
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
//
// ロールバック(トランザクションの破棄)
//
			try {
				conn.rollback();
			} catch(Exception e2){
				System.out.println(e2.getMessage());
			}
		} catch(Exception e){
			System.out.println(e.getMessage());
//
// ロールバック(トランザクションの破棄)
//
			try {
				conn.rollback();
			} catch(Exception e2){
				System.out.println(e2.getMessage());
			}
		}

		try {
//
// 検索クエリの結果セットオブジェクトを破棄
//
			rs.close();
//
// SQLステートメントオブジェクトを破棄
//
			stmt.close();
			pstmt.close();
//
// データベースからの切断
//
			conn.close();
		} catch(Exception e){
			System.out.println(e.getMessage());
		}
		System.out.println("mssqlserverから切断しました。");

	}
}

実行結果

JDBCドライバをロードしました。
jdbc:sqlserver://192.168.0.1:1433に接続しました。
自動コミットをONにしました。
テーブル member を削除しました。
テーブル member を作成しました。
テーブル member を作成できませんでした(2)(データベースに 'member' という名前のオブジェクトが既に存在します。)
インデックスを作成しました。
自動コミットをOFFにしました(トランザクション開始)。
インサートしました。
インサートしました。
インサートしました。
インサートできませんでした(制約 'PK__member__2E3BD7D3' の PRIMARY KEY 違反。オブジェクト 'dbo.member' には重複したキーを挿入できません。)
セレクトしました(id=1)(name=伊藤 太郎)(job=サラリーマン)。
セレクトしました(id=2)(name=山田 太郎)(job=野球選手)。
セレクトしました(id=3)(name=山田 花子)(job=タレント)。
セレクトできませんでした(ResultSet に現在の行がありません。)
アップデートしました。
アップデート結果(id=2)(name=山田 太郎)(job=裁判官)。
デリートしました(2件)。
デリート結果(id=1)(name=伊藤 太郎)(job=サラリーマン)。
メタデータを取得しました(id     name    job     )。
コミットしました。
mssqlserverから切断しました。

追記

  • stmt.executeUpdate(INSERT|DELETE|UPDATE) の戻り値は更新されたタプル数
  • stmt.executeUpdate(上記以外の更新系SQL) の戻り値は 0
  • エラー発生時は SQLExceptin 発生。getSQLState()にてエラーコードを取得できる。

MySQLとの違い

最初からAutoCommit=falseにしていると、create indexでテーブルが見つけられずに例外が発生した。create tableの直後に明示的にcommitを発行してもダメで、結局create indexまではAutoCommit=trueで実行し、それ以降をAutoCommit=falseで実行することで(無理矢理)解決した。

ショック

一応、サーバをSQL Server 2008 Express with Advanced Servicesに変えても、上記のサンプルは動いた。