我曾经Sqlite
通过将string
值与我的参数连接起来,并使用最终的可执行query
返回此string
,如下所示:
public static String getExampleQuery(String id) {
String query = "SELECT * " + "FROM " + Constants.TABLE_NAME
+ " as table " + "INNER JOIN " + Constants.TABLE_NAME_TWO
+ " as table2 " + "ON table." + Constants.COL_ID
+ " = table2." + Constants.COL_ID
+ " and table2." + Constants.COL_ID + " = '"
+ para1 + "'"
+ " order by table." + Constants.COL_ID + " desc ";
return query;
}
然后,我传递要执行的query
字符串。但是,大多数时候,我通过忘记spaces
或在外部参数前后添加额外的单引号'
而面临很多问题。总是很难从logcat
中找出问题。此外,查询根本不可读。
构建Sqlite
查询的最佳方法是什么,这些查询使查询可读且不易出错,并避免前面列出的问题!提前致谢<</p>
查看:SQLiteQueryBuilder,它使编写查询的级别降低。您将在此处找到一些如何开始使用它的示例:
http://www.vogella.com/tutorials/AndroidSQLite/article.html
我无法一目了然地阅读SQLiteQueryBuilder
代码,而且我非常习惯于阅读 SQL,所以我按照您的方式编写查询。我尝试将我的回报放在一个合乎逻辑的位置以提高可读性。我还使用 rawQuery
方法,这样我就可以分离出任何变量,而不必担心引用文本或不引用整数。 我在查询中添加了额外的空格。它只是让我避免犯常见的错误:
String[] whereVars = new String[]{id};
String query = " SELECT * "
+ " FROM " + Constants.TABLE_NAME + " as t "
+ " INNER JOIN " + Constants.TABLE_NAME_TWO + " as t2 "
+ " ON t." + Constants.COL_ID + " = t2." + Constants.COL_ID
+ " WHERE t2." + Constants.COL_ID + " = ? "
+ " ORDER BY t." + Constants.COL_ID + " DESC ";
db.rawQuery(query, whereVars)