ユーザ定義関数の追加
SQLiteにはストアドプロシージャはなく、その代わりにユーザ定義関数を追加し、同様の処理を行うようにできます。
public class TestFunc extends Function{
@Override
protected void xFunc() throws SQLException
{
try{
String a = value_text(0);
String b = value_text(1);
result(Integer.parseInt(a)+Integer.parseInt(b));
}catch(Exception e){
throw new SQLException(e.getMessage());
}
}
}
org.sqlite.Functionを継承したクラスを作成し、その中のxFuncをオーバラードしたメソッで実装する関数を定義しresultで戻します。
今回の場合には文字列で定義した数字を足しあわせて返すという関数を定義しました。
これを利用する際にはJDBCのコネクションに対しFunction.createでバインドしてやります
InitialContext ic = new InitialContext();
DataSource ds = (DataSource)ic.lookup("java:comp/env/test");
Connection conn=ds.getConnection();
conn.setAutoCommit(false);
Function.create(conn,"my_func",new TestFunc());
PreparedStatement stmt=conn.prepareStatement("select my_func(?,?)");
stmt.setString(1,"1");
stmt.setString(2,"3");
ResultSet rs=stmt.executeQuery();
while(rs.next()){
out.println(rs.getString(1));
}
バインドする際にSQL文内で呼ぶ関数名を同時に定義します。その後は通常のJDBCの処理と同じです。
DBCPで使う
DBCPで使うサンプルです
- context.xml
- TestServlet.java
public class TestServlet extends HttpServlet{
private static final long serialVersionUID = 1L;
@Override
public void init(ServletConfig sc) throws ServletException {
super.init(sc);
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
doPost(req, res);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html;charset=utf-8");
PrintWriter out=res.getWriter();
//http://stackoverflow.com/questions/6489514/apache-commons-dbcp-connection-object-problem-thread-classcastexception-in-org
//http://www.ksky.ne.jp/~snbhsmt/commons-dbcp.html
//http://grokbase.com/t/tomcat/users/052spdat5s/tomcat-5-5-7-using-builtin-jdbc-connection-pool-cant-access-real-connection-accesstounderlyingconnectionallowed-true
try{
InitialContext ic = new InitialContext();
BasicDataSource ds = (BasicDataSource)ic.lookup("java:comp/env/test");
//ds.setAccessToUnderlyingConnectionAllowed(true);
DelegatingConnection dcon=(DelegatingConnection)ds.getConnection();
DelegatingStatement dstmt = (DelegatingStatement)dcon.createStatement();
PoolableConnection pconn=(PoolableConnection)dstmt.getDelegate().getConnection();
Connection conn=pconn.getInnermostDelegate();
conn.setAutoCommit(false);
Function.create(conn,"my_func",new TestFunc());
PreparedStatement stmt=conn.prepareStatement("select my_func(?,?)");
stmt.setString(1,"1");
stmt.setString(2,"3");
ResultSet rs=stmt.executeQuery();
while(rs.next()){
out.println(rs.getString(1));
}
dcon.close();// 140916 閉じ忘れるとTOMCAT起動時に止まる
}catch(Exception e){
e.printStackTrace();
}
}
public class TestFunc extends Function{
@Override
protected void xFunc() throws SQLException
{
try{
String a = value_text(0);
String b = value_text(1);
result(Integer.parseInt(a)+Integer.parseInt(b));
}catch(Exception e){
throw new SQLException(e.getMessage());
}
}
}
}
ここでのポイントはDBCPのコネクションプールからJDBCのコネクションを取得する際に実際のコネクションを取得してやる必要があります。
BasicDataSource ds = (BasicDataSource)ic.lookup("java:comp/env/test");
//ds.setAccessToUnderlyingConnectionAllowed(true);
DelegatingConnection dcon=(DelegatingConnection)ds.getConnection();
DelegatingStatement dstmt = (DelegatingStatement)dcon.createStatement();
PoolableConnection pconn=(PoolableConnection)dstmt.getDelegate().getConnection();
Connection conn=pconn.getInnermostDelegate();
この部分です。詳細は↓あたりを参考にしてください。
http://www.ksky.ne.jp/~snbhsmt/commons-dbcp.html
なおJDBCはsqlite-jdbc-3.7.2.jarではなくsqlite-jdbc-3.8.5-pre1.jarを使用してください。Function.create内のinstanceofでconnectionが実コネクションかどうか判定しているのですが、JDBCが古いほうだと何故かここでエラーになってしまいます。
こんな感じでSQLite＋DBCPでユーザ定義関数が追加できます。参考になれば幸いです
20140916追記
DelegateingConnectionを取得した際にコネクションを閉じ忘れるとTOMCAT起動時にエラーというか無限ループに陥ります。注意