Reached MAX size for compiled-sql statement cache for database
My code is
ContentValues values;
values = new ContentValues();
values.put(SQLHelper.EMPLOYEE_LPN, jsObj.getString("lpn"));
db.update(SQLHelper.EMPLOYEE_TABLE, values,
"EMPLOYEE_LPN ='" + jsObj.getString("lpn") + "'",
null);
a warning is shown in the Log Cat
08-31 15:19:45.297: WARN/Database(2868): Reached MAX size for compiled-sql statement cache for database /data/data/org.sipdroid.sipua/databases/test.db; i.e.,
NO space for开发者_如何转开发 this sql statement in cache:
SELECT EMPLOYEE_NAME FROM eyemployee WHERE EMPLOYEE_LPN ='1169162'.
Please change your sql statements to use '?' for bindargs, instead of using actual values
How to resolve it?
Look at examples 8-3 and 8-4 here.
Example 8-3. Using the update method
/**
* Update a job in the database.
* @param job_id The job id of the existing job
* @param employer_id The employer offering the job
* @param title The job title
* @param description The job description
*/
public void editJob(long job_id, long employer_id, String title, String description) {
ContentValues map = new ContentValues();
map.put("employer_id", employer_id);
map.put("title", title);
map.put("description", description);
String[] whereArgs = new String[]{Long.toString(job_id)};
try{
getWritableDatabase().update("jobs", map, "_id=?", whereArgs);
} catch (SQLException e) {
Log.e("Error writing new job", e.toString());
}
}
Here are some of the highlights of the code in Example 8-3:
Example 8-4 shows you how to use the execSQL method.
Example 8-4. Using the execSQL method
/**
* Update a job in the database.
* @param job_id The job id of the existing job
* @param employer_id The employer offering the job
* @param title The job title
* @param description The job description
*/
public void editJob(long job_id, long employer_id, String title, String description) {
String sql =
"UPDATE jobs " +
"SET employer_id = ?, "+
" title = ?, "+
" description = ? "+
"WHERE _id = ? ";
Object[] bindArgs = new Object[]{employer_id, title, description, job_id};
try{
getWritableDatabase().execSQL(sql, bindArgs);
} catch (SQLException e) {
Log.e("Error writing new job", e.toString());
}
}
The message is asking you to make parameters use sql variables instead of sql literals.
Each sql query is parsed, plans are generated, and stored in a sql statement cache.
Queries which have the same text are fetched from the cache.
--One query
SELECT * FROM Customers WHERE Id = @1 (@1 = 3)
SELECT * FROM Customers WHERE Id = @1 (@1 = 4)
SELECT * FROM Customers WHERE Id = @1 (@1 = 5)
Queries which have different text (including literals) cannot be found in the cache and are (uselessly) added to it.
--Three Queries.
SELECT * FROM Customers WHERE Id = 3
SELECT * FROM Customers WHERE Id = 4
SELECT * FROM Customers WHERE Id = 5
I was searching for this today, and came across this doc.
http://ormlite.com/docs/query-builder
This solved my issue. This is the code from the link above
QueryBuilder<Account, String> queryBuilder =
accountDao.queryBuilder();
Where<Account, String> where = queryBuilder.where();
SelectArg selectArg = new SelectArg();
// define our query as 'name = ?'
where.eq(Account.NAME_FIELD_NAME, selectArg);
// prepare it so it is ready for later query or iterator calls
PreparedQuery<Account> preparedQuery = queryBuilder.prepare();
// later we can set the select argument and issue the query
selectArg.setValue("foo");
List<Account> accounts = accountDao.query(preparedQuery);
// then we can set the select argument to another
// value and re-run the query
selectArg.setValue("bar");
accounts = accountDao.query(preparedQuery);
精彩评论