我正在使用Ormlite,使用Table RecentSearch：
@DatabaseTable(tableName = LocalStorageConfig.sql_RECENTS_TABLE_NAME)
public class RecentSearch {
@DatabaseField
public String search_text;
public static String sql_SEARCH_FIELD = "search_text";
@DatabaseField
public String location_text;
public static String sql_LOCATION_FIELD = "location_text";
@DatabaseField
public Date creation_date = new Date();
public static String sql_CREATION_DATE_FIELD = "creation_date";
它几乎一直在工作,但当我发现包含’的字符串的情况时,它似乎是一个问题.你知道怎么解决这个问题吗？我找不到我要找的东西.
这是我删除最近的功能
public boolean deleteRecent(RecentSearch search) {
try {
Dao<RecentSearch,Integer> recentsDao = recentssqlManager.getRecentsDao();
DeleteBuilder<RecentSearch,Integer> deleteBuilder = recentsDao.deleteBuilder();
deleteBuilder.where().eq(RecentSearch.sql_SEARCH_FIELD,search.getSearch_text()).and().eq(RecentSearch.sql_LOCATION_FIELD,search.location_text);
recentsDao.delete(deleteBuilder.prepare());
return true;
} catch (Exception e) {
Log.e(TAG,"Database exception",e);
return false;
}
}
这是我得到的例外情况：
java.sql.sqlException: Problems executing Android statement: DELETE FROM `recent_searches` WHERE (`search_text` = '' AND `location_text` = 'Villefranche-d'Allier,Allier' )
at com.j256.ormlite.misc.sqlExceptionUtil.create(sqlExceptionUtil.java:22)
at com.j256.ormlite.android.AndroidCompiledStatement.runUpdate(AndroidCompiledStatement.java:66)
at com.j256.ormlite.stmt.StatementExecutor.delete(StatementExecutor.java:425)
at com.j256.ormlite.dao.BaseDaoImpl.delete(BaseDaoImpl.java:347)
...
Caused by: android.database.sqlite.sqliteException: near "Allier": Syntax error:,while compiling: DELETE FROM `recent_searches` WHERE (`search_text` = '' AND `location_text` = 'Villefranche-d'Allier,Allier' )
at android.database.sqlite.sqliteCompiledsql.native_compile(Native Method)
at android.database.sqlite.sqliteCompiledsql.compile(sqliteCompiledsql.java:92)
at android.database.sqlite.sqliteCompiledsql.<init>(sqliteCompiledsql.java:65)
at android.database.sqlite.sqliteProgram.<init>(sqliteProgram.java:83)
at android.database.sqlite.sqliteStatement.<init>(sqliteStatement.java:41)
at android.database.sqlite.sqliteDatabase.compileStatement(sqliteDatabase.java:1149)
解决方法
在
ORMLite中,当您尝试使用引号构建查询时,应该使用SelectArg功能,该功能将使用sql生成查询？参数然后直接将字符串传递给预准备语句.这解决了转义特殊字符的任何问题,并保护您免受sql注入安全问题的影响.见
documentation on
SelectArg.
使用SelectArg,您可以执行以下操作：
DeleteBuilder<RecentSearch,Integer> deleteBuilder = recentsDao.deleteBuilder();
// create our argument which uses a sql ?
SelectArg locationArg = new SelectArg(search.location_text);
deleteBuilder.where().eq(RecentSearch.sql_SEARCH_FIELD,search.getSearch_text())
.and().eq(RecentSearch.sql_LOCATION_FIELD,locationArg);
recentsDao.delete(deleteBuilder.prepare());
...