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)側にインストールする物
- Microsoft .NET Framework Version 2.0 再頒布可能パッケージ (x86)
- MS SQL Server 2005 Express edition インストールのポイント
- 「機能の選択」において、とりあえず全コンポーネントを選択する。
- 「認証モード」において、混合モードを選択する。また管理者(sa)のパスワードも設定する。
- インストール完了後、スタートメニューより
- 「プログラム」->「Microsoft SQL Server 2005」->「構成ツール」->「SQL Server Configuration Manager」を起動し
- 「SQL Server 2005 ネットワークの構成」->「SQLEXPRESSのプロトコル」->「TCP/IP」->「プロパティ」->「IPアドレス」->「TCPポート」に「1433」を設定する。その後、上記の「TCP/IP」を「有効化」する。
- 「SQL Server 2005 のサービス」->「SQL Server(SQLEXPRESS)」->「再起動」を行う。
以上でサーバが起動する。
- 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.jarをCLASSPATHに追加するか、実行時に
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で実行することで(無理矢理)解決した。
ショック
- Microsoft SQL Server 2005 Express Edition with Advanced Services(Express Edition + Management Studio Express)というのが有ったんだね。
- さらに新しいMicrosoft SQL Server 2008 Expressというのも有ったんだね...。
一応、サーバをSQL Server 2008 Express with Advanced Servicesに変えても、上記のサンプルは動いた。
[ツッコミを入れる]