Masa's blog(2009-07-10)

2009年07月10日 Java儂(わし)的解釈によるメモ(JDBC + MySQL編) [長年日記]

_ Java儂(わし)的解釈によるメモ(JDBC + MySQL編)

JavaからDBMS(MySQL)にJDBC経由で接続してみる。例によって用語、用法等は思い込みである部分が見受けられると思うが、御容赦願う。

準備

Slackware-12.xに標準でインストールされるMySQL-5.xをそのまま利用する。

JDBCドライバの取得

mysql-connector-java-5.0.8.tar.gzを取得(http://dev.mysql.com/downloads/connector/j/)

$ tar xvzf mysql-connector-java-5.0.8.tar.gz
$ cp mysql-connector-java-5.0.8/mysql-connector-java-5.0.8-bin.jar .

mysql-connector-java-5.0.8-bin.jarをどこかグローバルな場所(例えば/usr/local/jdk/みたいな)に置いて、

CLASSPATH=${CLASSPATH}:/usr/local/jdk/mysql-connector-java-5.0.8-bin.jar; export CLASSPATH

しておくのも良いかも。

データベース領域の初期化

mysqldを起動するに当たって、一度だけ実行する必要がある。

# mysql_install_db --user=mysql

mysqld起動スクリプトの修正

JDBC経由でアクセスするために起動オプションを修正する(/etc/rc.d/rc.mysqld)。

#SKIP="--skip-networking"  <-- コメントアウト

MySQLコンフィグファイル設定

デフォルト文字コード、デフォルトストレージエンジンの設定。

標準のストレージエンジンであるMyISAMは速度は早いが、トランザクションがサポートされていない。非標準のストレージエンジンであるInnoDBはMyISAMに比べると速度が劣るがトランザクションをサポートする。

汎用機畑で育った私としてはトランザクションが無い(すなわちcommit、rollbackが無い)のは信じられないので、デフォルトのストレージエンジンをInnoDBに変更する。

# cp /etc/my-small.conf /etc/my.cnf
# vi /etc/my.cnf

[client]
##default-character-set = utf8
default-character-set = ujis

[mysqld]
##default-character-set = utf8
default-character-set = ujis
default-storage-engine=innodb

mysqld起動

# chmod +x /etc/rc.d/rc.mysqld; reboot

MySQL管理者(root)のパスワード設定

# mysqladmin -u root password ROOT-PASSWORD

データベース(jdbctestdb)の作成

# mysql -u root -p
Enter password: XXXXXXXXXXXXX <-- ROOT-PASSWORDを入力する
mysql> create database jdbctestdb character set ujis;
mysql> quit

一般ユーザ(m-ito)にデータベースへのアクセス権を設定

# mysql -u root -p jdbctestdb
Enter password: XXXXXXXXXXXXX <-- ROOT-PASSWORDを入力する
mysql> grant all on jdbctestdb.* to 'm-ito'@'localhost' identified by 'USER-PASSWORD';
mysql> quit

一般ユーザ用MySQLコンフィグファイル設定

パスワードの設定とデフォルト文字コードの設定。このあたりは必要に応じて...。

$ cp /etc/my-small.conf ~m-ito/.my.cnf
$ chmod 600 ~m-ito/.my.cnf
$ vi ~m-ito/.my.cnf

[client]
password = USER-PASSWORD
##default-character-set = utf8
default-character-set = ujis

サンプルプログラム

//
// 「jdbc mysql」テスト
//
// compile : javac -encoding utf-8 jdbc_mysql.java
// run     : java -cp .:mysql-connector-java-5.0.8-bin.jar jdbc_mysql
//
//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_mysql {

	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 MySQLのロード
//
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			System.out.println("JDBCドライバをロードしました。");
//
// データベースへの接続
//
//			String url = "jdbc:mysql://localhost/jdbctestdb?useUnicode=true&characterEncoding=SJIS";
			String url = "jdbc:mysql://localhost/jdbctestdb?useUnicode=true&characterEncoding=EUC_JP";
			conn = DriverManager.getConnection(url, "m-ito", "USER-PASSWORD");
			System.out.println("jdbctestdbに接続しました。");
//
// 自動コミット停止(トランザクションの開始)
//
			conn.setAutoCommit(false);
			System.out.println("自動コミットをOFFにしました。");
//
// 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)" +
				") engine = innodb"
			);
			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("インデックスを作成しました。");
//
// タプルの追加
//
			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("jdbctestdbから切断しました。");

	}
}

実行結果

JDBCドライバをロードしました。
jdbctestdbに接続しました。
自動コミットをOFFにしました。
テーブル member を削除しました。
テーブル member を作成しました。
テーブル member を作成できませんでした(2)(Table 'member' already exists)
インデックスを作成しました。
インサートしました。
インサートしました。
インサートしました。
インサートできませんでした(Duplicate entry '3' for key 1)
セレクトしました(id=1)(name=伊藤 太郎)(job=サラリーマン)。
セレクトしました(id=2)(name=山田 太郎)(job=野球選手)。
セレクトしました(id=3)(name=山田 花子)(job=タレント)。
セレクトできませんでした(After end of result set)
アップデートしました。
アップデート結果(id=2)(name=山田 太郎)(job=裁判官)。
デリートしました(2件)。
デリート結果(id=1)(name=伊藤 太郎)(job=サラリーマン)。
メタデータを取得しました(id     name    job     )。
コミットしました。
jdbctestdbから切断しました。

追記

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

about Microsoft SQL Server(情報のみ)

Microsoft SQL Server 2005 JDBC Driver

JDBCドライバ(msbase.jar、mssqlserver.jar)を使用した場合(SQLServer2000以前)

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://サーバ名:1433","ユーザID","パスワード");

JDBCドライバ(sqljdbc.jar)を使用した場合(SQLServer2005以降)

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://サーバ名:1433","ユーザID","パスワード");

about Oracle(情報のみ)

Oracle JDBC Driver ダウンロード

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@サーバ名:1521:OracleSID","ユーザID","パスワード");

OracleSID : tnsnames.ora に記述がある。

[ツッコミを入れる]


		2009年 7月
日	月	火	水	木	金	土
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

Masa's blog