SQLiteにユーザ定義関数を追加しTOMCAT＋DBCPで利用する

ユーザ定義関数の追加

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());
			}
		}
	}

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));
			}

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

				 <Resource name="test"
							auth="Container"
							type="javax.sql.DataSource"
							driverClassName="org.sqlite.JDBC"
							url="jdbc:sqlite:C:\\temp\\test.db"
							accessToUnderlyingConnectionAllowed = "true"
							factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" 
							/>

<Resource name="test"

auth="Container"

type="javax.sql.DataSource"

driverClassName="org.sqlite.JDBC"

url="jdbc:sqlite:C:\\temp\\test.db"

accessToUnderlyingConnectionAllowed = "true"

factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"

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());
			}
		}
	}
}

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();

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://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

なおJDBCはsqlite-jdbc-3.7.2.jarではなくsqlite-jdbc-3.8.5-pre1.jarを使用してください。Function.create内のinstanceofでconnectionが実コネクションかどうか判定しているのですが、JDBCが古いほうだと何故かここでエラーになってしまいます。

こんな感じでSQLite＋DBCPでユーザ定義関数が追加できます。参考になれば幸いです

20140916追記

DelegateingConnectionを取得した際にコネクションを閉じ忘れるとTOMCAT起動時にエラーというか無限ループに陥ります。注意