Oracleのユーザー定義型(TYPE)のメモ。
いわば構造体のようなデータを定義し、テーブルの項目の型として使える。
|
|
create or replace type タイプ名 as object ( 属性名 型, … )
SQL> create or replace type TYPE0 as object ( 2 key numeric(4), 3 data varchar2(100) 4 ); 5 / 型が作成されました。 SQL> show err エラーはありません。
JavaからアクセスできるTYPEを作る場合は、以下のようにオプションを追加する。
create or replace type TYPE1 as object external name 'jp.hishidama.sample.jdbc.oracle.Type1' language java using SQLData( KEY numeric(4), DATA1 varchar2(100), DATA2 varchar2(200) ); /
「using SQLData」は、その実体となるクラス(上記の例ではjp.hishidama.sample.jdbc.oracle.Type1)がjava.sql.SQLDataをimplementsしている事を示す。
他に「using Serializable」や「using ORAData」等がある。
作ったTYPEは、create tableで使うことが出来る。
create table TYPESAMPLE ( DT1 type1 );
insert文は以下のように書く。
insert into TYPESAMPLE values( TYPE1(1234,'123ABCあいう','zzz') );
SQL*Plusでselectすると、以下の様に表示される。
SQL> select * from TYPESAMPLE; DT1(KEY, DATA1, DATA2) -------------------------------------------------------------------------------- TYPE1(1234, '123ABCあいう', 'zzz')
Java(JDBC)からユーザー定義型のデータを取得することが出来る。
この場合、java.sql.SQLDataを実装したクラスを普通に作っておき、実行時にそのクラスがCLASSPATHに入っていればよい。
import java.sql.SQLData; import java.sql.SQLException; import java.sql.SQLInput; import java.sql.SQLOutput;
public class Type1 implements SQLData { protected int key; protected String data1; protected String data2; @Override public String getSQLTypeName() throws SQLException { return "TYPE1"; } @Override public void readSQL(SQLInput stream, String typeName) throws SQLException { // TODO 自動生成されたメソッド・スタブ →実装例 } @Override public void writeSQL(SQLOutput stream) throws SQLException { // TODO 自動生成されたメソッド・スタブ } @Override public String toString() { return "key=" + key + ",data1=" + data1 + ",data2=" + data2; } }
これを使う側は、特に何かの定義をする必要は無い。(import文とかも不要)
ResultSet#getObject()を使えば、その中で自動的に該当クラスがロードされ、インスタンスが作られる。
(実行時のクラスパスにそのクラスが含まれている必要はある)
Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@ora92", "scott", "tiger"); Statement stat = conn.createStatement(); try { ResultSet rs = stat.executeQuery("select * from TYPESAMPLE"); try { //メタデータを表示してみる ResultSetMetaData md = rs.getMetaData(); for (int i = 1; i <= md.getColumnCount(); i++) { String tname = md.getColumnTypeName(i); String cname = md.getColumnClassName(i); System.out.printf("rmd[%d]\t%s\t%s%n", i, tname, cname); } //データを取得・表示 while (rs.next()) { for (int i = 1; i <= md.getColumnCount(); i++) { Object dt = rs.getObject(i); //データ取得 System.out.println(dt.toString()); } } } finally { rs.close(); } } finally { stat.close(); }
メタデータのTYPE_NAMEはOracle側のTYPE名、CLASS_NAMEは定義されているクラス名となる。
rmd[1] SCOTT.TYPE1 jp.hishidama.sample.jdbc.oracle.Type1
SQLDataにはreadSQL()やwriteSQL()といったメソッドがあり、Oracle(DB)からJavaのクラスのフィールドへの値の設定(およびその逆)は自分で実装する必要がある。
@Override public void readSQL(SQLInput stream, String typeName) throws SQLException { key = stream.readInt(); data1 = stream.readString(); data2 = stream.readString(); }
今回のサンプルで使ったデータ(key=1234、data1=「123ABCあいう」、data2=「zzz」)だと、以下のように表示された。(Oracle9iのojdbc14.jar)
key=1234,data1=0x31323341424382A082A282A4,data2=0x7A7A7A
どうも、“文字コードを十六進数で文字化した”文字列として返って来ているらしい(苦笑)
文字コードはShift_JISのようだが、これはDBがShift_JISだからだと思われる。
SQL> select dump(dt1) from TYPESAMPLE; DUMP(DT1) -------------------------------------------------------------------------------- Typ=121 Len=28: 132,1,254,0,0,0,28,3,194,13,35,12,49,50,51,65,66,67,130,160,130,162,130,164,3,122,122,122 ↑123 ↑ABC ↑あいう ↑zzz
ちなみにOracle11gのojdbc6.jarだと、文字化けした(爆)
key=1234,data1=???,data2=???
という訳で、(文字化けしない方なら、)無理矢理文字コードから戻してやれば、期待した文字列に変換することは出来る。
@Override public void readSQL(SQLInput stream, String typeName) throws SQLException { key = stream.readInt(); data1 = conv(stream.readString()); data2 = conv(stream.readString()); } protected String conv(String s) { int len = (s.length() - 2) / 2; byte[] r = new byte[len]; for (int i = 0; i < len; i++) { int j = i * 2 + 2; byte b = Integer.decode("0x" + s.substring(j, j + 2)).byteValue(); r[i] = b; } try { return new String(r, "MS932"); } catch (UnsupportedEncodingException e) { throw new RuntimeException(e); } }
…けど、なんだかなぁ(苦笑)
SQLInputにはreadNString()というメソッドもある。
@Override public void readSQL(SQLInput stream, String typeName) throws SQLException { key = stream.readInt(); data1 = stream.readNString(); data2 = stream.readNString(); }
が、これはJDK1.6で追加されたものなので、ojdbc14.jar(JDK1.4用)では実装されていない(実行時エラーになる)。
ojdbc6.jarだと以下のように文字化けした(苦笑)
key=1234,data1=???,data2=???
(そもそもnvarchar用だから、varchar2には使えないのかも?
もしくは、DB自体はOracle9iだから、Oracle11gのクライアントから接続するには何か設定が必要とか?)
SQLInputのreadCharacterStream()というメソッドは、Unicode文字を返してくれるらしい。
ただしこれの戻り値はReaderなので、Stringへの変換はコーディングする必要がある。
@Override public void readSQL(SQLInput stream, String typeName) throws SQLException { key = stream.readInt(); data1 = readReader(stream.readCharacterStream()); data2 = readReader(stream.readCharacterStream()); } protected String readReader(Reader r) throws SQLException { try { StringBuilder sb = new StringBuilder(); for (;;) { int c = r.read(); if (c < 0) break; sb.append((char) c); } return sb.toString(); } catch (IOException e) { throw new SQLException(e); } finally { try { r.close(); } catch (IOException e) { throw new SQLException(e); } } }
しかし実行してみたら、例外が発生した(Oracle9iのojdbc14.jarの場合)。
java.sql.SQLException: Non supported character set: oracle-character-set-832
ojdbc14.jarと同じ場所にあるnls_charset12.jarをクラスパスに追加してやると実行できるようになり、文字化けせずに表示された。
なお、Oracle11gのojdbc6.jarだと以下のような例外メッセージなので、追加すべきjarファイルが分かり易い。
java.sql.SQLException: サポートされないキャラクタ・セットです(orai18n.jarをクラスパスに追加してください): oracle-character-set-832
他に、SQLInputにはreadBinaryStream()というメソッドもある。
これはバイト列(InputStream)でデータを取得でき、自分で文字コード変換をすることになるので、Oracleの文字コード変換用jarファイルは不要になる。
@Override public void readSQL(SQLInput stream, String typeName) throws SQLException { key = stream.readInt(); data1 = readStream(stream.readBinaryStream()); data2 = readStream(stream.readBinaryStream()); } protected String readStream(InputStream is) throws SQLException { try { ByteArrayOutputStream bos = new ByteArrayOutputStream(); for (;;) { int d = is.read(); if (d < 0) break; bos.write(d); } return new String(bos.toByteArray(), "MS932"); } catch (IOException e) { throw new SQLException(e); } finally { try { is.close(); } catch (IOException e) { throw new SQLException(e); } } }