はじめに
まだまだレガシーなJDBC接続を実現せざるを得ないJavaソースは世に数多く存在し
プログラマの悩みの種の一つであったりします。
保守だけならまだしも、新規に作らなくてはいけないことも。
レガシーという意味ですぐに思いつくケースとしては
「DBからSelectで引いた情報をBeanとしてマッピングする」という形ですが
その処理プロセスのまだるっこしさたるや…
「クエリの結果をResultSetで一回預かる」
「ResultSetのカーソルを回してBeanに詰める」
「Beanをループさせてビジネスロジックを実装」
みたいな感じです。
SQLとBeanの設計・定義は正しいのに、そのまだるっこしい過程の中で
ケアレスミスをしたばっかりに全体が動かなくなってしまう、という顛末も往々にしてあります。
もっと直感的に、SQLとBeanだけで完結しないの？という事で本稿です。
実現したいこと
掲題通りです。
厳密には「返ってくるBeanの型」はあらかじめ指定する必要があるので
スタバのマイボトルに近いでしょうか。
注文（SQL）して、マイボトル（Bean）を店員に渡すと
マイボトルがコーヒーで満たされて返ってくる、的な。
実現に当たって捨てたもの
型安全性
この後出てくるコードをご覧になって頂ければわかりますが
Javaの大きな特徴の一つである「型安全性」を完全に無視している部分があります。
個人的には「別に」ですが、引っかかる人は引っかかるかもしれません。
何卒、ご容赦ください。
既存ORM技術の深堀り
ORMは全然詳しくありません。
なので、既存のミドルウェアやライブラリでもっと優れた機能性を以て
私がやりたい事を実現しているリソースもありそうではありますが
ORM自体、賛否両論どころか否定的な意見が多い、という見切りもあり
早計も早計ではありますが、無理に首を突っ込むのはやめておきました。
（個人的に、ORMは広義な言葉と認識していますので、
全部が全部ダメという事ではないとは思うのですが
言葉狩りのきらいがあるのも事実ですのであえて避けました）
参考にさせて頂いた記事
やりたい事に一番近い、と感じたのが以下の記事です。
基盤部分のソースはほぼそのまま転用させて頂いております。
【Java】DBアクセスライブラリを作ってみよう！
Javaのバージョン
Java6以降
※Java5でも動かない事はないと思いますが、未検証です。
登場人物
JDBCManagerクラス
DB接続基盤クラス兼マッピングを司るクラスです。
SqlParameterクラス
SQLへのパラメータとなるプレースホルダを管理するクラスです。
型安全ならぬ型危険なのがこいつです。
SQL（クエリ文字列）
実行したいSQL文です。
CRUDを問わず、特にファイルやDBレコードとして永続化する必要はありません。
Selectに応じたBeanクラス
Selectの抽出カラムに対応したBeanクラスです。
1つのSelect文に対して1つ作成します。
I/O
最低限の動作に必要なI/Oは以下の通りです。
インプット
・JDBCドライバクラスのフルパス（oracle.jdbc.driver.OracleDriverなど）
・JDBC接続子（jdbc:oracle:thin:@//localhost:1521/xeなど）
・JDBC接続ユーザ
・JDBC接続パスワード
・SQL
・SQLのパラメータ（ただしプレースホルダ未使用の場合は不要）
アウトプット
Bean
※ただしInsert、Update、Deleteの場合は更新件数のみをintで返却。
基盤ソース
JDBCManagerクラスとSqlParameterクラスが基盤となります。
JDBCManagerクラス
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class JDBCManager {
private Connection conn;
@SuppressWarnings("unused")
private JDBCManager() throws Throwable{}
public JDBCManager(String driver,String url,String user,String password) throws ClassNotFoundException,SQLException{
Class.forName(driver);
this.conn = DriverManager.getConnection(url, user, password);
this.conn.setAutoCommit(false);
}
private void setParams(PreparedStatement statement, SqlParameter sqlParameter) throws SQLException {
if(sqlParameter == null || !sqlParameter.hasParameters()) {
return;
}
int paramNo = 1;
for( Object param : sqlParameter.toParameter() ) {
statement.setObject(paramNo++, param);
}
}
public void commit() throws SQLException{
this.conn.commit();
}
public void rollback() throws SQLException{
this.conn.rollback();
}
public void close() throws SQLException{
this.conn.close();
}
private <E> E toObject(ResultSet rs, Class<E> clazz) throws InstantiationException, IllegalAccessException, SQLException {
E bean = null;
if( rs.next() ) {
Field[] fields = clazz.getDeclaredFields();
bean = clazz.newInstance();
for (Field f : fields) {
f.setAccessible(true);
Object val = rs.getObject(f.getName());
f.set(bean, val);
}
}
return bean;
}
private <E> List<E> toObjectList(ResultSet rs, Class<E> clazz) throws SQLException, InstantiationException, IllegalAccessException {
List<E> rsList = new ArrayList<E>();
while (rs.next()) {
Field[] fields = clazz.getDeclaredFields();
E bean = clazz.newInstance();
for( Field f: fields ) {
f.setAccessible(true);
Object val = rs.getObject( f.getName() );
f.set(bean, val);
}
rsList.add(bean);
}
return rsList;
}
private SQLException sqlErr(Throwable e, CharSequence sql) {
return new SQLException("sql error!\nerror occurred sql="+sql, e);
}
public int insert(String sql, SqlParameter sqlParameter) throws SQLException {
return exert(sql,sqlParameter);
}
public int update(String sql, SqlParameter sqlParameter) throws SQLException {
return exert(sql,sqlParameter);
}
public int delete(String sql, SqlParameter sqlParameter) throws SQLException {
return exert(sql,sqlParameter);
}
private int exert(String sql, SqlParameter sqlParameter) throws SQLException {
PreparedStatement statement = null;
try {
statement = conn.prepareStatement(sql.toString());
setParams(statement,sqlParameter);
int ret = statement.executeUpdate();
return ret;
}catch (SQLException e){
throw sqlErr(e, sql);
}finally {
try {
if(statement != null){
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public <E> E selectOne(String sql, Class<E> clazz) throws SQLException {
return selectOne(sql,clazz,null);
}
public <E> E selectOne(String sql, Class<E> clazz, SqlParameter sqlParameter) throws SQLException {
ResultSet rs = null;
PreparedStatement statement = null;
try{
statement = conn.prepareStatement(sql.toString());
setParams(statement,sqlParameter);
rs = statement.executeQuery();
E val = toObject(rs, clazz);
return val;
}catch(Exception e) {
throw sqlErr(e, sql);
}finally {
try {
if( rs != null ) {
rs.close();
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
try {
if(statement != null){
statement.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
public <E> List<E> selectList(String sql, Class<E> clazz) throws SQLException {
return selectList(sql,clazz,null);
}
public <E> List<E> selectList(String sql, Class<E> clazz, SqlParameter sqlParameter) throws SQLException {
ResultSet rs = null;
PreparedStatement statement = null;
try {
statement = conn.prepareStatement(sql.toString());
setParams(statement,sqlParameter);
rs = statement.executeQuery();
List<E> rsList = toObjectList(rs, clazz);
return rsList;
}catch(Exception e) {
throw sqlErr(e, sql);
}finally {
try {
if( rs != null ) {
rs.close();
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
try {
if(statement != null){
statement.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
}
SqlParameterクラス
import java.util.ArrayList;
import java.util.List;
public class SqlParameter {
@SuppressWarnings("rawtypes")
private List list = null;
@SuppressWarnings("rawtypes")
public SqlParameter(int paramNumber){
list = new ArrayList(paramNumber);
}
@SuppressWarnings("rawtypes")
public SqlParameter(){
list = new ArrayList();
}
@SuppressWarnings("unchecked")
public <E> void addParameter(E e){
list.add(e);
}
public boolean hasParameters() {
return list.size()==0 ? false : true;
}
public Object[] toParameter(){
return list.toArray();
}
}
動作サンプル
4つのケースで動かしていきます。
対象のDBは「Oracle（ExpressEdition）」とします。
スキーマにデフォルトで存在する「EMP」表と「DEPT」表を操作対象としています。
①Select:WHERE句なし（プレースホルダ不要）の複数件取得SQL
②Select:WHERE句あり（プレースホルダあり）の複数件取得SQL
③Select:単一取得SQL
④更新（insert,update,delete）
それぞれSQLに応じたBeanを作成していきます。
ポイントはSelectのエイリアス（AS）とBeanのフィールド名を同じにする事です。
また、Beanのフィールド型はJDBC仕様に応じたクラスで宣言しておきます。
本サンプルのSelectでは、単一のテーブルに対しての操作をしておりますが
結合を使用した複数テーブルを対象としたSelectでも同様の処理が可能です。
要は「SQLとBeanが合っていればいい」のです。
① Select:WHERE句なし（プレースホルダ不要）の複数件取得SQL
SELECT
--VARCHAR2
DNAME AS dname
FROM
DEPT
public class DeptBean {
private String dname = null;
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
}
② Select:WHERE句あり（プレースホルダあり）の複数件取得SQL
SELECT
--NUMBER
EMPNO AS empNo,
--VARCHAR2
ENAME AS ename,
--DATE
HIREDATE AS hireDate
FROM
EMP
WHERE
DEPTNO = ?
AND
JOB = ?
import java.math.BigDecimal;
import java.sql.Timestamp;
public class EmpBean {
private BigDecimal empNo = null;
private String ename = null;
private Timestamp hireDate = null;
public BigDecimal getEmpNo() {
return empNo;
}
public void setEmpNo(BigDecimal empNo) {
this.empNo = empNo;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Timestamp getHireDate() {
return hireDate;
}
public void setHireDate(Timestamp hireDate) {
this.hireDate = hireDate;
}
}
③ Select:単一取得SQL
SELECT
--NUMBER
EMPNO AS empNo,
--VARCHAR2
ENAME AS ename,
--DATE
HIREDATE AS hireDate
FROM
EMP
WHERE
//PK
EMPNO = ?
（上と同じなので割愛）
④ 更新（insert,update,delete）
insertをサンプルとします。
結果はintで受け取る為、更新用のBeanは作らなくて大丈夫です。
INSERT INTO DEPT
--NUMBER,VARCHAR2,VARCHAR2
(DEPTNO, DNAME, LOC)
values
(?, ?, ?)
テストクラス
上記４つのケースを全て動かしていきます。
import java.math.BigDecimal;
import java.util.List;
public class JDBCTest {
public static void main(String[] args) throws Throwable{
JDBCTest t = new JDBCTest();
t.execute();
}
public void execute() throws Throwable{
//JDBC接続情報
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@//localhost:1521/xe";
String user = "ouser";
String password = "ouser";
//JDBCManagerを初期化
JDBCManager jdbc = new JDBCManager(driver,url,user,password);
//パターン１．WHERE句なし（プレースホルダ不要）の複数件取得SQL
String sql = "SELECT "
+ "DNAME AS dname "
+ "FROM "
+ "DEPT ";
//Listを取得
List<DeptBean> dlist = jdbc.selectList(sql, DeptBean.class);
System.out.println("―――パターン１―――");
for(DeptBean d : dlist) {
System.out.println(d.getDname());
}
//パターン２．WHERE句あり（プレースホルダあり）の複数件取得SQL
//別名でBeanのフィールドに合わせる
sql = "SELECT "
+ "EMPNO AS empNo, "
+ "ENAME AS ename, "
+ "HIREDATE AS hireDate "
+ "FROM "
+ "EMP "
+ "WHERE "
+ "DEPTNO = ? "
+ "AND "
+ "JOB = ? ";
//プレースホルダは２つ
SqlParameter p = new SqlParameter(2);
//より先頭から順番にバインド
p.addParameter(new BigDecimal(30));
p.addParameter("SALESMAN");
//Listを取得
List<EmpBean> elist = jdbc.selectList(sql, EmpBean.class, p);
System.out.println("―――パターン２―――");
for(EmpBean e : elist) {
System.out.println(e.getEmpNo() + "\t" + e.getEname() + "\t" + e.getHireDate());
}
//パターン３．単一取得SQL
sql = "SELECT "
+ "EMPNO AS empNo, "
+ "ENAME AS ename, "
+ "HIREDATE AS hireDate "
+ "FROM "
+ "EMP "
+ "WHERE "
//PK
+ "EMPNO = ? ";
p = new SqlParameter(1);
p.addParameter(new BigDecimal(7369));
//Beanを取得
EmpBean emp = jdbc.selectOne(sql, EmpBean.class, p);
System.out.println("―――パターン３―――");
System.out.println(emp.getEname());
//パターン４．更新（insert,update,delete）
sql = "INSERT INTO DEPT "
+ "(DEPTNO, DNAME, LOC) "
+ "values "
+ "(?, ?, ?) ";
p = new SqlParameter(3);
p.addParameter(new BigDecimal(99));
p.addParameter("SPECIAL");
p.addParameter("JAPAN");
int i = jdbc.insert(sql, p);
System.out.println("―――パターン４―――");
System.out.println("Insert件数は"+i+"件です");
jdbc.commit();
//実際はエラートラップして、catchでjdbc.rollback()を実行
jdbc.close();
//実際はエラートラップして、finallyでjdbc.close()を実行
}
}
実行結果は以下の通りです。
―――パターン１―――
ACCOUNTING
RESEARCH
SALES
OPERATIONS
―――パターン２―――
7499 ALLEN 1981-02-20 00:00:00.0
7521 WARD 1981-02-22 00:00:00.0
7654 MARTIN 1981-09-28 00:00:00.0
7844 TURNER 1981-09-08 00:00:00.0
―――パターン３―――
SMITH
―――パターン４―――
Insert件数は1件です
まとめ
参考にさせて頂いた記事から比較しても
目新しい内容はほぼ皆無ですし、
型安全でないばかりに実行時エラーを引き起こすリスクは上がっているのですが
個人的には、扱う対象がSQLとパラメータとBeanの多くても３つなので
Resultsetとかでごちゃごちゃやって時間を食うくらいであれば
型安全をトレードオフにする価値はあるだろうと思っている次第です。
また、諸事情でJavaをバージョンアップできない環境であったり、
ORMを始めとしたDB基盤系のミドルウェアやライブラリを導入できない環境であったり、
と環境面・運用面での制限があったとしても、
古いJavaVMのみで動作可能という所がお気に入りです。
今回はデータベースとしてOracleをターゲットとしましたが
JDBCで接続可能であれば、多少の方言的な挙動差異はあれど
どのデータベースでもいけるんではないかなぁと楽観的に思ってたりします。