ここを参考に、
Asciiの場合
1 2 3 | cursor.execute("SELECT * FROM posts WHERE tags LIKE ?", ('%{}%'.format(tag),)) |
Unicodeの場合
tagはunicode string
1 2 3 | cursor.execute("SELECT * FROM posts WHERE tags LIKE ?", (u"%{}%".format(tag),)) |
ここを参考に、
1 2 3 | cursor.execute("SELECT * FROM posts WHERE tags LIKE ?", ('%{}%'.format(tag),)) |
tagはunicode string
1 2 3 | cursor.execute("SELECT * FROM posts WHERE tags LIKE ?", (u"%{}%".format(tag),)) |
SQLiteにはストアドプロシージャはなく、その代わりにユーザ定義関数を追加し、同様の処理を行うようにできます。
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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でバインドしてやります
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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で使うサンプルです
1 2 3 4 5 6 7 8 | <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" /> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | 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のコネクションを取得する際に実際のコネクションを取得してやる必要があります。
1 2 3 4 5 6 7 8 | 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でユーザ定義関数が追加できます。参考になれば幸いです
DelegateingConnectionを取得した際にコネクションを閉じ忘れるとTOMCAT起動時にエラーというか無限ループに陥ります。注意
1 2 3 4 5 6 7 8 | <Context docBase="test" path="/test" reloadable="true" source="org.eclipse.jst.jee.server:test"> <Resource name="jdbc/sqlite" auth="Container" type="javax.sql.DataSource" driverClassName="org.sqlite.JDBC" url="jdbc:sqlite:/tmp/test.db" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"/> </Context> |
1 2 3 4 5 6 7 8 9 | <?xml version="1.0" encoding="UTF-8"?> <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5"> <display-name>test</display-name> <resource-ref> <res-ref-name>jdbc/sqlite</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref> </web-app> |
1 2 3 4 5 6 7 8 9 10 11 12 | <%@ page language="java" contentType="text/html; charset=Shift_JIS" pageEncoding="Shift_JIS"%><%@ page import="java.sql.*,javax.naming.*,javax.sql.*,java.util.*,java.text.*,java.net.*" %> <% InitialContext ic = new InitialContext(); DataSource ds=(DataSource)ic.lookup("java:comp/env/jdbc/sqlite"); Connection conn=ds.getConnection(); PreparedStatement ps=conn.prepareStatement("select datetime('now','localtime')"); ps.execute(); ResultSet rs=ps.getResultSet(); while(rs.next()){ out.println(rs.getString(1)); } %> |
1 | 2013-04-18 17:01:20 |