rubbish-db / チュートリアル / SQLインターフェース

[ rubbish-db ]

インターフェースの実装

/**
 * 書籍を検索するSQL
 */
public class SearchBookSQL implements SQL {

    private StringBuffer sql = new StringBuffer();

    public CharSequence getSQL() {
        return sql;
    }

    private List params = new ArrayList();

    public Object[] getParams() {
        return params.toArray();
    }

    public SearchBookSQL(String publisher, String id, String title, String author) {
        sql.append("SELECT * FROM BOOK");
        List where = new ArrayList();

        if (publisher != null) {
            where.add("PUBLISHER=?");
            params.add(publisher);
        }

        if (id != null) {
            where.add("ID=?");
            params.add(id);
        }

        if (title != null) {
            where.add("TITLE=?");
            params.add(title);
        }

        if (author != null) {
            where.add("AUTHOR=?");
            params.add(author);
        }

        if (where.size() > 0) {
            sql.append(" WHERE ");
            sql.append(StringUtils.join(where.toArray(), " AND "));
        }
    }

}

ソース

RubbishDatabase dbh = new RubbishDatabase();
dbh.setLogging(true);

dbh.connect("jdbc:hsqldb:hsql://localhost", "sa", "");
SQL sql01 = new SearchBookSQL("05", null, null, null);

Map[] books01 = dbh.query(sql01);

for (int i = 0; i < books01.length; i++)
    println(books01[i]);

SQL sql02 = new SearchBookSQL("05", null, null, "盛田慎之介");
Map[] books02 = dbh.query(sql02);

for (int i = 0; i < books02.length; i++)
    println(books02[i]);

dbh.disconnect();

結果

connect database 'url=jdbc:hsqldb:hsql://localhost, user=sa, password='.
'SELECT * FROM BOOK WHERE PUBLISHER=? [05]'
{PUBLISHER=05, ID=000, TITLE=降龍天臨霹, AUTHOR=竹乃元秀路, ISSUE_DATE=null, UPDATE_DATE=2005-06-10 17:10:00.000, CREATE_DATE=1999-06-10 17:10:00.000}
{PUBLISHER=05, ID=001, TITLE=天釐蜘巣, AUTHOR=盛田慎之介, ISSUE_DATE=null, UPDATE_DATE=2005-06-10 17:10:00.000, CREATE_DATE=1999-06-10 17:10:00.000}
{PUBLISHER=05, ID=002, TITLE=爆ショウ繋飯, AUTHOR=盛田慎之介, ISSUE_DATE=null, UPDATE_DATE=2005-06-10 17:10:00.000, CREATE_DATE=1999-06-10 17:10:00.000}
{PUBLISHER=05, ID=003, TITLE=夢キョウ楼覚, AUTHOR=盛田慎之介, ISSUE_DATE=null, UPDATE_DATE=2005-06-10 17:10:00.000, CREATE_DATE=1999-06-10 17:10:00.000}
{PUBLISHER=05, ID=004, TITLE=體透奇, AUTHOR=盛田慎之介, ISSUE_DATE=null, UPDATE_DATE=2005-06-10 17:10:00.000, CREATE_DATE=1999-06-10 17:10:00.000}
'SELECT * FROM BOOK WHERE PUBLISHER=? AND AUTHOR=? [05, 盛田慎之介]'
{PUBLISHER=05, ID=001, TITLE=天釐蜘巣, AUTHOR=盛田慎之介, ISSUE_DATE=null, UPDATE_DATE=2005-06-10 17:10:00.000, CREATE_DATE=1999-06-10 17:10:00.000}
{PUBLISHER=05, ID=002, TITLE=爆ショウ繋飯, AUTHOR=盛田慎之介, ISSUE_DATE=null, UPDATE_DATE=2005-06-10 17:10:00.000, CREATE_DATE=1999-06-10 17:10:00.000}
{PUBLISHER=05, ID=003, TITLE=夢キョウ楼覚, AUTHOR=盛田慎之介, ISSUE_DATE=null, UPDATE_DATE=2005-06-10 17:10:00.000, CREATE_DATE=1999-06-10 17:10:00.000}
{PUBLISHER=05, ID=004, TITLE=體透奇, AUTHOR=盛田慎之介, ISSUE_DATE=null, UPDATE_DATE=2005-06-10 17:10:00.000, CREATE_DATE=1999-06-10 17:10:00.000}
disconnect database.