2009年08月15日 Java儂(わし)的解釈によるメモ(JDBC + Oracle Database 10g Express Edition編) [長年日記]
_ Java儂(わし)的解釈によるメモ(JDBC + Oracle Database 10g Express Edition編)
さて、Oracleにも無償版があるとの事で、早速試してみた(Oracle Database 10g Express Edition)。
サーバはWindows版とLinux版が有る。とりあえずインストールの簡単そうだったWindows版で試してみる。
クライアントはLinuxで。
こちらも基本的にはJava儂(わし)的解釈によるメモ(JDBC + MySQL編)と同様だが、MS SQL Serverの時の様な方言的な動きは無く、ロードするドライバの部分だけをOracle用に書き換えれば動作した。
サーバ側(IP=192.168.0.1)にインストールする物
クライアント側にインストールする物
- ojdbc14.jar (Oracle JDBC Driver 10.2.0.1.0) (thin type4 ドライバ)
サンプルプログラム
//
// 「jdbc oracle」テスト
//
// compile : javac -encoding uutf-8 jdbc_oracle.java
// run : java -cp .:ojdbc14.jar jdbc_oracle
//
//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_oracle {
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 ORACLEのロード
//
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
System.out.println("JDBCドライバをロードしました。");
//
// データベースへの接続
//
String url = "jdbc:oracle:thin:@192.168.0.1:1521:XE";
conn = DriverManager.getConnection(url, "system", "SYSTEM-PASSWORD");
System.out.println("XE(データベース)に接続しました。");
//
// 自動コミット停止(トランザクションの開始)
//
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 NVARCHAR2(50)," +
"job NVARCHAR2(50)" +
")"
);
System.out.println("テーブル member を作成しました。");
//
// わざと2重にテーブル作成してみる
//
try {
stmt.executeUpdate(
"create table member (" +
"id INT PRIMARY KEY," +
"name NVARCHAR2(50)," +
"job NVARCHAR2(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("XE(データベース)から切断しました。");
}
}
_ 実行結果
JDBCドライバをロードしました。 XE(データベース)に接続しました。 自動コミットをOFFにしました。 テーブル member を削除しました。 テーブル member を作成しました。 テーブル member を作成できませんでした(2)(ORA-00955: すでに使用されているオブジェクト名です。 ) インデックスを作成しました。 インサートしました。 インサートしました。 インサートしました。 インサートできませんでした(ORA-00001: 一意制約(TESTUSER.SYS_C004065)に反しています ) セレクトしました(id=1)(name=伊藤 太郎)(job=サラリーマン)。 セレクトしました(id=2)(name=山田 太郎)(job=野球選手)。 セレクトしました(id=3)(name=山田 花子)(job=タレント)。 セレクトできませんでした(空の結果セットです。) アップデートしました。 アップデート結果(id=2)(name=山田 太郎)(job=裁判官)。 デリートしました(2件)。 デリート結果(id=1)(name=伊藤 太郎)(job=サラリーマン)。 メタデータを取得しました(ID NAME JOB )。 コミットしました。 XE(データベース)から切断しました。
追記
- stmt.executeUpdate(INSERT|DELETE|UPDATE) の戻り値は更新されたタプル数
- stmt.executeUpdate(上記以外の更新系SQL) の戻り値は 0
- エラー発生時は SQLExceptin 発生。getSQLState()にてエラーコードを取得できる。
_ Oracle 儂(わし)的解釈によるメモ(sqlplus編)
階層構造
データベース(XE)/スキーマ(ユーザ名と同じ)/テーブル
- データベース : インスタンスと同義(?)。SIDという識別子で区別される。Express Editionの場合は XE に固定。データベース毎に異なる接続ポートを開く。慣例的に1521を使用する事が多い。
- スキーマ : テーブル等をグループ分けする為の名前空間。データベースに接続するユーザ毎に自分のスキーマを所有する。その際のスキーマ名はユーザ名と同じになる。ユーザの作成したテーブル等のオブジェクトはデフォルトでユーザの所有するスキーマに(論理的に)格納される。
- テーブル : 同左。
テーブルスペース
実際にデータが格納されるファイル(又はファイル群)に付けられる名称(表領域)。デフォルトで以下のテーブルスペースが有る。
- USERS : 非 SYSTEM ユーザーにより作成される全てのデータベース・オブジェクトのデフォルト表領域
- SYSAUX : SYSTEM 表領域の補助表領域
- UNDO : ロールバックに必要なデータ領域
- SYSTEM : データ・ディクショナリ表およびその他の管理データの領域
データベース作成
Express Editionの場合、同時に起動できるインスタンスが1個なので、実質あまり意味が無い。それでも新たに作った場合は既存のインスタンスをshutdownして、別のインスタンスをstartupすることになる。
ここでは説明しない。
sqlplusのヘルプ
C:\Documents and Settings\m-ito>sqlplus -?
SQL*Plus: Release 10.2.0.1.0 - Production
Copyright (c) 1982, 2005, Oracle. All rights reserved.
使用方法1: sqlplus -H | -V
-H SQL*Plusのバージョンおよび
使用方法のヘルプを表示します。
-V SQL*Plusのバージョンを表示します。
使用方法2: sqlplus [ [<option>] [<logon>] [<start>] ]
<option>は次のとおりです: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]
-C <version> 影響されるコマンドの互換性を、
<version>で指定されたバージョンに設定します。バージョンのフォームは
"x.y[.z]"です。たとえば、-C 10.2.0となります
-L エラー時に再プロンプト表示するかわりに、
1回のみログオンを試行します。
-M "<options>" 出力の自動HTMLマークアップを設定します。オプション
のフォームは次のとおりです:
HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
[ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
-R <level> 制限モードを設定し、ファイルシステムと対話する
SQL*Plusコマンドを無効にします。レベルは
1、2または3にできます。最も限定的なのは-R 3であり、
ファイルシステムと対話するすべてのユーザー・コマンドを
無効にします。
-S コマンドのSQL*Plusバナー、プロンプトおよび
エコーの表示を抑制するサイレント・モードを
設定します。
<logon>は次のとおりです: (<username>[/<password>][@<connect_identifier>] | /)
[AS SYSDBA | AS SYSOPER] | /NOLOG
データベース接続用のデータベース・アカウント・ユーザー名、
パスワードおよび接続識別子を指定します。接続識別子がないと、
SQL*Plusはデフォルトのデータベースに接続します。
AS SYSDBAおよびAS SYSOPERオプションはデータベース管理
権限です。
/NOLOGオプションでは、データベースに接続せずに
SQL*Plusを起動します。
<start>は次のとおりです: @<URL>|<filename>[.<ext>] [<parameter> ...]
スクリプト内の置換変数に割り当てられる指定パラメータを使用して、
Webサーバー(URL)またはローカル・ファイルシステム(filename.ext)から、
指定のSQL*Plusスクリプトを実行します。
SQL*Plusの起動時でCONNECTコマンドの後に、サイト・プロファイル
($ORACLE_HOME/sqlplus/admin/glogin.sqlなど)およびユーザー・プロファイル
(作業ディレクトリ内のlogin.sqlなど)が実行されます。ファイルには
SQL*Plusコマンドが含まれる場合があります。
詳細は、SQL*Plusユーザーズ・ガイドおよびリファレンスを参照してください。
ユーザー作成
sqlplus sys/SYS-PASSWORD as sysdba SQL> CREATE USER testuser IDENTIFIED BY USER-PASSWORD 2> DEFAULT TABLESPACE users 3> TEMPORARY TABLESPACE temp 3> QUOTA UNLIMITED ON users;
ユーザ名はアルファベット、数字以外は避けた方が良い(ハイフン、アンダーバー等の記号を入れるとハマルかも...)。
テーブルスペースを指定しないと、デフォルトでSYSTEM表領域にデータを作りにいくので、明示的にusers表領域を指定しておく。またこの例では容量制限(quota)を外している。
ユーザー削除
sqlplus sys/SYS-PASSWORD as sysdba SQL> DROP USER testuser;
ORA-01922のエラーメッセージが出る場合は、当該ユーザで既にデータを作成している場合。データごと消して良い場合は、
sqlplus sys/SYS-PASSWORD as sysdba SQL> DROP USER testuser CASCADE;
アクセス権の付与(管理者)
sqlplus sys/SYS-PASSWORD as sysdba SQL> GRANT connect,resource,dba TO testuser;
- connect : データベースに接続する権限
- resource : データを操作する権限
- dba : 管理者としての権限(これだけ有れば十分？、sysdbaなら最強！)
アクセス権の付与(一般ユーザ)
データベースに接続する権限(connect)、テーブルを作成する権限(resource)と既存のテーブル(testtable)に対する操作権限(select,insert,update,delete)を与える。
sqlplus sys/SYS-PASSWORD as sysdba SQL> GRANT connect,resource to testuser; SQL> GRANT select,insert,update,delete ON testtable TO testuser;
select,insert,update,deleteはallで代用可能。
アクセス権の削除
sqlplus sys/SYS-PASSWORD as sysdba SQL> REVOKE connect FROM testuser; SQL> REVOKE select,insert,update,delete ON testtable FROM testuser;
ヘルプを表示
sqlplus sys/SYS-PASSWORD as sysdba SQL> HELP;
ユーザ一覧
sqlplus sys/SYS-PASSWORD as sysdba SQL> select * from all_users;
ユーザ名は全て大文字に変換されて登録されている。
テーブル一覧
sqlplus sys/SYS-PASSWORD as sysdba SQL> select * from all_tables; SQL> select * from user_tables;
テーブル名は全て大文字に変換されて登録されている。
テーブル構造を見る
sqlplus sys/SYS-PASSWORD as sysdba SQL> DESC testtable;
バックアップ(フル)
まずバックアップ対称のファイルを調べる。
sqlplus sys/SYS-PASSWORD as sysdba SQL> select name from v$datafile; SQL> select name from v$controlfile; SQL> select name from v$tempfile;
ORACLEデータベースを停止する。
sqlplus sys/SYS-PASSWORD as sysdba SQL> shutdown immediate;
上記で表示されたファイルを外部媒体等にコピーする。Oracleのインストールディレクトリ以下をまるごとコピーしても良いかも(?)。
ORACLEデータベースを再起動する。
sqlplus sys/SYS-PASSWORD as sysdba SQL> startup;
テーブルをCSVファイルに書き出す
sqlplus sys/SYS-PASSWORD as sysdba SQL> set linesize 32767 SQL> set pagesize 0 SQL> set trimspool on SQL> set colsep ',' SQL> set head off SQL> spool Hoge.csv SQL> select * from testtable; SQL> spool off
CSVファイルからテーブルに読み込む
これと言った方法は無い...しいて言えば、CSVファイルに適当なスクリプトをかまして、insert文の形式に変換してsqlplusに食わせるぐらい。
_ Oracle 儂(わし)的解釈によるメモ(sqlldr編)
CSVファイルからテーブルに読み込む
sqlldrというコマンドで出来る。
制御ファイル作成
ファイル名を sqlldr.ctrl とする。
load data
characterset AL32UTF8 <- infileの文字セット(UTF8を指定)
infile 'INPUT.CSV' <- 入力ファイルの指定
badfile 'BAD.CSV' <- インポートに失敗したレコードファイル
discardfile 'DISCARD.CSV' <- 破棄されたレコードファイル
append into table member <- インポート先のテーブル
fields terminated by X'09' <- 項目の区切り文字(UTF16でのTABを指定)
trailing nullcols <- 項目が無い時、NULLを設定する
(
id, <- テーブル項目の列挙
name, <- テーブル項目の列挙
job <- テーブル項目の列挙
)
実行
sqlldr userid=USER/PASSWORD control=sqlldr.ctl log=sqlldr.log
USER/PASSWORDで認証し、sqlldr.ctlの内容に従ってデータをインポートする。実行ログがsqlldr.logに記録される。
_ Oracle 儂(わし)的解釈によるメモ(もろもろ追記編)
How to handle UTF-8 data by sqlplus on windows
Put next lines into operation.sql and save it with encoding UTF-8. If you use notepad, operation.sql will be saved with BOM.
set echo off;
set feedback off;
set termout off;
set colsep ',';
set trimspool on;
set linesize 32767;
set pagesize 0;
drop table tb_sample;
create table tb_sample(
id char(8),
name nvarchar2(20)
);
insert into tb_sample names(id,name) values('12345678','オラクル 太郎');
commit;
spool result.txt
select * from tb_sample where id='12345678';
spool off
quit;
rmbom.c
#include <stdio.h>
main()
{
int ch;
int i = 0;
while ((ch = getchar()) != EOF){
i++;
if (i > 3){
putchar(ch);
}
}
}
Remove BOM from operation.sql. Operate it in Cygwin.
$ ./rmbom.exe <operation.sql >/tmp/junk $ mv /tmp/junk operation.sql
Just do it in command prompt!
> SET NLS_LANG=JAPANESE_JAPAN.AL32UTF8 > sqlplus -s _user_/_password_ @operation.sql > SET NLS_LANG= > notepad result.txt
How to make sure about encoding of database
sqlplus sys/sys-password as sysdba SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET'; SQL> select value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';
How to change ARCHIVELOG mode
sqlplus sys/sys-password as sysdba SQL> archive log list; SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open; SQL> archive log list;
How to switch current logfile
sqlplus sys/sys-password as sysdba SQL> alter system switch logfile; SQL> select name from v$archived_log; SQL> select * from v$log; SQL> select * from v$logfile;
How to archive current log
sqlplus sys/sys-password as sysdba SQL> alter system archive log current; SQL> select name from v$archived_log;
How to see current logfile
sqlplus sys/sys-password as sysdba SQL> select * from v$log; SQL> select * from v$logfile;
How to delete unused archivelog safely
rman target=sys/sys-password RMAN> list archivelog all; RMAN> delete noprompt archivelog until time 'sysdate - 3'; RMAN> delete noprompt archivelog all; RMAN> quit;
[ツッコミを入れる]