前回の投稿でSQLiteにjdbcで接続したときにユーザー定義関数を使う方法について書きました。 今回はユーザー定義関数の簡単な例を投稿します。 ネタはorder byで使うことを念頭に置いた、乱数を返す関数です。
// UserFunctionRandomOrder.java
package sqliteuserfunctiontest;
import java.sql.SQLException;
import java.util.Random;
import org.sqlite.Function;
public class UserFunctionRandomOrder extends Function
{
public final int DEFAULT_TABLE_SIZE = 256;
public final int MAX_SIZE = 16384;
private Random _rand = null;
private int[] _randTable = null;
public UserFunctionRandomOrder()
{
_randTable = new int[DEFAULT_TABLE_SIZE];
_rand = new Random();
}
public String getName()
{
return "ufRandomOrder";
}
public void setSize(int size)
{
if(size < 2 || MAX_SIZE < size)
throw new IllegalArgumentException("サイズが不正です。2～" + MAX_SIZE + "にしてください。");
if(_randTable.length != size)
_randTable = new int[size];
}
public void setSeed(int seed)
{
_rand.setSeed(seed);
for(int i = 0; i < _randTable.length; i++)
_randTable[i] = _rand.nextInt();
}
@Override
protected void xFunc() throws SQLException
{
int id = value_int(0);
result(_randTable[Math.abs(id) % _randTable.length] ^ id);
}
}
元から用意されているrandom関数を並び替え用に使うとlimit offsetを使うことができません。 例えば、「1ページにつき10項目の情報をランダムにピックアップして載せるウェブページ」などは作れないのです。
このサンプルのランダム関数なら、シード値さえ取っておけば
(1ページ目) select * from テーブル order by ufRandomOrder(id) limit 10 (2ページ目) select * from テーブル order by ufRandomOrder(id) limit 10 offset 10 ...
というようにselectすることができます。
シード値には、最初のselectを実行するときの時間などを使えばいいでしょう。 乱数と言うには適当すぎる作りかもしれませんが、用途次第では十分かと。
試しに使ってみると、
package sqliteuserfunctiontest;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
import org.sqlite.Function;
public class SqliteUserFunctionTest
{
private static final String DB_NAME = "d:/test.db";
public static void main(String[] args)
{
PrintStream out = System.out;
Connection c = null;
Random r = new Random(System.currentTimeMillis() );
try
{
out.println("◆データベースの用意");
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:" + DB_NAME);
c.setAutoCommit(false);
String sqlDropTable = "drop table if exists tmain";
String sqlCreateTable = "create table tmain (col1 integer, col2 text)";
String[] col2s = {"家", "道", "店", "駅", "田"};
Statement s = c.createStatement();
s.executeUpdate(sqlDropTable);
s.executeUpdate(sqlCreateTable);
PreparedStatement ps = c.prepareStatement(
"insert into tmain values (?, ?)"
);
for(int i = 0; i < 10000; i++)
{
ps.setInt(1, r.nextInt() );
ps.setString(2, "" + Math.abs(r.nextInt() ) + "番目の" + col2s[i % col2s.length] );
ps.executeUpdate();
}
c.commit();
out.println("◆UserFunctionRandomOrderの登録");
UserFunctionRandomOrder ufRandomOrder = new UserFunctionRandomOrder();
ufRandomOrder.setSeed( (int)System.currentTimeMillis() );
Function.create(c, ufRandomOrder.getName(), ufRandomOrder);
out.println("◆order byでufRandomOrderを使用(limit 10)");
ResultSet res = s.executeQuery
(
"select col1, col2 from tmain " +
"order by ufRandomOrder(col1) " +
"limit 10"
);
int line = 1;
while(res.next() )
{
out.println(String.format(
"res8 %03d : %12d, %20s",
line,
res.getInt("col1"),
res.getString("col2")
));
line++;
}
out.println("◆同じseedなら同じ順番に(limit 5 offset 0)");
res = s.executeQuery
(
"select col1, col2 from tmain " +
"order by ufRandomOrder(col1) " +
"limit 5"
);
line = 1;
while(res.next() )
{
out.println(String.format(
"res9 %03d : %12d, %20s",
line,
res.getInt("col1"),
res.getString("col2")
));
line++;
}
out.println("◆同じseedなら同じ順番に(limit 5 offset 5)");
res = s.executeQuery
(
"select col1, col2 from tmain " +
"order by ufRandomOrder(col1) " +
"limit 5 offset 5"
);
while(res.next() )
{
out.println(String.format(
"res10 %03d : %12d, %20s",
line,
res.getInt("col1"),
res.getString("col2")
));
line++;
}
}
catch(ClassNotFoundException exc)
{
System.err.println("jdbcドライバの読み込みエラー。\n" + exceptionToString(exc) );
}
catch(SQLException exc)
{
System.err.println(
"SQL例外 : code = " + exc.getErrorCode() + '\n' +
exc.getMessage() + '\n' +
exceptionToString(exc)
);
}
finally
{
if(c != null)
{
try
{
c.close();
}
catch (SQLException exc)
{
System.err.println(
"SQLコネクションクローズ失敗 : code = " + exc.getErrorCode() + '\n' +
exc.getMessage() + '\n' +
exceptionToString(exc)
);
}
}
}
}
private static String exceptionToString(Exception exc)
{
StringBuilder res = new StringBuilder();
for(StackTraceElement elem : exc.getStackTrace() )
{
res.append('\t');
res.append(elem.toString() );
res.append('\n');
}
return res.toString();
}
}
結果は、
◆データベースの用意 ◆UserFunctionRandomOrderの登録 ◆order byでufRandomOrderを使用(limit 10) res8 001 : -2071494873, 457399573番目の道 res8 002 : 558256421, 1151252657番目の道 res8 003 : -946425784, 1241792479番目の店 res8 004 : 1953106339, 1538172827番目の田 res8 005 : 2097671560, 977070869番目の駅 res8 006 : -124948858, 855964781番目の家 res8 007 : 1950821907, 923013975番目の駅 res8 008 : 523221652, 21569394番目の駅 res8 009 : -1997337479, 680613490番目の店 res8 010 : 1560767072, 1890788397番目の店 ◆同じseedなら同じ順番に(limit 5 offset 0) res9 001 : -2071494873, 457399573番目の道 res9 002 : 558256421, 1151252657番目の道 res9 003 : -946425784, 1241792479番目の店 res9 004 : 1953106339, 1538172827番目の田 res9 005 : 2097671560, 977070869番目の駅 ◆同じseedなら同じ順番に(limit 5 offset 5) res10 006 : -124948858, 855964781番目の家 res10 007 : 1950821907, 923013975番目の駅 res10 008 : 523221652, 21569394番目の駅 res10 009 : -1997337479, 680613490番目の店 res10 010 : 1560767072, 1890788397番目の店
でお買い物
