S-JIS[2003-07-19]
JavaからDBへのアクセスには、JDBCを使う。
同一DBでも、コネクションが異なればトランザクションが異なる(別々にコミットする必要があるし、一方がコミットしなければ他方がその変更を見ることは出来ない)。
PostgreSQLのJDBCにはちょっと不便なところ(バグ?)があるので注意。
StatementでもPreparedStatementでも、SQLの実行に使うメソッド名は同じ。そもそもPreparedStatementはStatementから派生したインターフェースだし。
SQL | 実行(結果取得)メソッド | 結果の型 | |
---|---|---|---|
Statement | PreparedStatement | ||
SELECT | st = con.createStatement() rs = st.executeQuery(SQL) |
st = con.prepareStatement(SQL) st.setString(n,val) rs = st.executeQuery() |
ResultSet |
st = con.createStatement() st.execute(SQL) rs = st.getResultSet() |
st = con.prepareStatement(SQL) st.setString(n,val) st.execute() rs = st.getResultSet() |
||
INSERT UPDATE DELETE |
st = con.createStatement() n = executeUpdate(SQL) |
st = con.prepareStatement(SQL) st.setString(n,val) n = st.executeUpdate() |
int |
st = con.createStatement() st.execute(SQL) n = st.getUpdateCount() |
st = con.prepareStatement(SQL) st.setString(n,val) st.execute() n = st.getUpdateCount() |
SQLをそのまま実行する。
外部から入力された値を使う場合等では、SQLインジェクションに注意。
例えば当サンプルで
引数に「abc' and hoge='a」という(シングルクォーテーションが混ざった)文字列をdataに指定した場合、SQLは
"select * from test where data=\'" + data + "\'" ↓ "select * from test where data=\'" + "abc' and hoge='a" + "\'" ↓ select * from test where data='abc' and hoge='a'
すなわち、「hoge」などという無関係な項目が入ってしまい、SQLエラーになるか、又は意図しないSQLが実行されることになる!
この辺りは、PreparedStatementの方が安全。
SQLを実行する。
SQL文の中にパラメータ「?」を書いておくことにより、(データ型に応じて)setString()等で値を後から自由に変えられる。
何番目のパラメータを置き換えるかをsetString()の第一引数で指定する。置き換えられなかったパラメータは、nullが指定されたものとして実行される。
パラメータ1個につき1個の値として扱われて変換されるので、Statementに比べると安全。
select * from test where data=? ↓ select * from test where data='abc\' and hoge=\'a' ←1つの文字列
したがって、複数の値には置き換えられない。
パラメータに「'1000','2000'」を指定することによって「in('1000','2000')」としたいが… select * from test where data in(?) ↓ select * from test where data in('\'1000\',\'2000\'') ←1つの文字列
SELECTを実行した結果は、ResultSetで受け取る。
next()を使って結果の各レコードを処理する。レコード内の項目の値は、(データ型に応じて)getString()等を使って取得する。
PostgreSQLのJDBCが返すResultSetでは、複数レコードを取得するSQLの場合は
全結果を一度に取り込もうとするらしい。
レコード数が無茶苦茶多い(何十万件とか何百万件とかだ)と、OutOfMemoryExceptionが発生する。
PostgreSQLのPreparedStatementでは、toString()でSQL文を表示できる。ORACLEのPreparedStatement#toString()では、SQL文は表示されない。
setString()等で値をセットした後は、セットされた状態で表示される(表示されたSQLをコピペすれば、そのままpsql等から実行できる)。
PreparedStatement stmt = con.prepareStatement("select * from tbl_hoge where foo = ?"); System.out.println(stmt.toString()); stmt.setString(1,"hogehoge"); System.out.println(stmt.toString());
複数のSQL文を書くことが出来る。
PreparedStatement stmt = con.prepareStatement( "SET enable_seqscan TO false;" + "SELECT * from tbl_hoge;" + "SET enable_seqscan TO DEFAULT;" );
psqlのデフォルトの表示コードを指定する環境変数PGCLIENTENCODINGだが、postmaster起動時にも読み込まれるらしい。このため、エンコードが日本語になっているDBのテーブルをJDBC経由でアクセスすると、余計な変換がかかって文字化けするケースがある。
Javaソース SJIS |
コンパイル →→→→→→→→ SJIS→UNICODE変換 |
JavaVM UNICODE |
JDBC →→→→ 無変換 |
postmaster →→→→→→→→ EUC→UNICODE変換 |
DB内 UNICODE (のつもりで文字化け) |
画面等 SJIS |
JavaVM ←←←←←←←← UNICODE→SJIS変換 |
JDBC ←←←← 無変換 |
postmaster ←←←←←←←← UNICODE→EUC変換 |
||
psql EUC_JP |
→→→→→→→→ EUC→UNICODE変換 |
||||
←←←←←←←← UNICODE→EUC変換 |
Javaは内部ではUNICODEで処理しており、DBもこの例ではUNICODEなので、JDBC経由では本来文字コードの変換は不要なはずである。にも関わらずPGCLIENTENCODINGがpostmasterに影響し、EUC⇔UNICODE変換を行ってしまう。その結果、DB内ではUNICODEをEUCのつもりでUNICODEに変換した変な文字コードで格納されることになる。
厄介なことに、逆変換を行えば表面上は正しく取得できる。上の例では、JDBC経由でDBに入れた文字は画面では正しく表示される。しかしそのDBの値をpsqlで直接見ると、読める文字にはなっていない。また、psqlから正しく入力した文字は
この画面では文字化けしてしまう。
この現象の回避方法は、postmaster起動時はPGCLIENTENCODINGを指定するのをやめ、psql起動時にだけ指定することである。
JDBCの「SJISやEUCとUNICODEとの変換のマッピング」に不具合があって、全角ハイフン等の文字が正しく扱えない。
三協運輸サービスさんの『PostgreSQL Bank』でパッチが公開されています。