Question

android 的SQLite数据库的SQL动态查询语句问题

private static final String DATABASE_TABLE = "Income";
private static final String DATABASE_CREATE =
"create table Income("
+"_id INTEGER PRIMARY KEY," //ID
+"item TEXT,"
+"project TEXT,"
+"amount NUMERIC "
+");";

public static final String KEY_ID = "_id";
public static final String KEY_ITEM = "item";
public static final String KEY_PROJECT = "project";
public static final String KEY_AMOUNT = "amount";

我现在有五个查询条件
item , project , dayu_amount , dengyu_amount , xiaoyu_amount
先判断为不为空，不为空，这作为where中的一条条件，为空则不作为条件。最后进行查询。

类似SQL的动态语句
select * from table
where column.a=case when isnull(a,'')!='' then a else column.a end
and column.b=case when isnull(b,'')!='' then a else column.b end
and column.c=case when isnull(c,'')!='' then a else column.c end
and column.d=case when isnull(d,'')!='' then a else column.d end

下面是我自己写的，请问对吗？错的话，能说下怎么写吗
return db.query(DATABASE_TABLE,
new String[] {KEY_ID, KEY_ITEM, KEY_PROJECT, KEY_AMOUNT},
"KEY_ITEM = case when isnull(Item,'')!='' then Item else KEY_ITEM end" +
"and KEY_PROJECT = case when isnull(Project,'')!='' then Project else KEY_PROJECT end " +
"and KEY_AMOUNT = case when isnull(Dengyu_amount,'')!='' then Dengyu_amount else KEY_AMOUNT end" +
"and KEY_AMOUNT > case when isnull(Dayu_amount,'')!='' then Dayu_amount else KEY_AMOUNT end" +
"and KEY_AMOUNT < case when isnull(Xiaoyu_amount,'')!='' then Xiaoyu_amount else KEY_AMOUNT end",
null,null,null,null);

Answer 1

顶下，没写过