最近在项目中需要解决数据入库慢的问题,业务场景是:从服务器获取到数据后解析完成后,需要将数据入库,由于数据量比较大,并且在每次入库时需要判断当前库中是否存在此条数据,如果存在则不入库,否则入库。 其中一位开发人员将业务实现后,量小时勉强可以使用,当量大时,至少需要长达几分钟的时候。 经过分析后,发现在实现这个需求时发了过多的查询SQL(存在入库,不存在则不入库),同时没有使用SQLiteStatement预编译。 优化后,只需要2-3S钟数据就完成整个UI显示流程。
主要使用以下手段,请做为参考:
INSERT OR REPLACE 表示:插入或者替换。
当将要插入的数据主键或唯一键对应的值已经存在,则将进行替换。如果不存在,则进行插入操作。
INSERT OR IGNORE 表示:插入或者忽略。
当将要插入的数据主键或唯一键对应的值已经存在,则将进行忽略不执行插入操作,否则进行插入。
存在入库,不存在则不入库的逻辑,就可以使用 INSERT OR IGNORE来实现,减少了发出多余的SQL,减少了IO。 同时使用InsertHelper来完成入库。
InsertHelper代码如下, 代码也是从android源码中摘取(android.database.DatabaseUtils.InsertHelper) ,本人针对自己的业务做了一些修改,请参考。
不过在4.4版本上此类已经废弃,不过可以拿出来单独使用。
package dw.test;
import java.util.HashMap;
import java.util.Map;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;
public class InsertHelper {
private static final String TAG = "InsertHelper";
private final SQLiteDatabase mDb;
private final String mTableName;
private HashMap<String, Integer> mColumns;
private String mInsertSQL = null;
private SQLiteStatement mInsertStatement = null;
private SQLiteStatement mIgnoreStatement = null;
private SQLiteStatement mReplaceStatement = null;
private SQLiteStatement mPreparedStatement = null;
/**
* {@hide}
*
* These are the columns returned by sqlite's "PRAGMA
* table_info(...)" command that we depend on.
*/
public static final int TABLE_INFO_PRAGMA_COLUMNNAME_INDEX = 1;
/**
* This field was accidentally exposed in earlier versions of the platform
* so we can hide it but we can't remove it.
*
* @hide
*/
public static final int TABLE_INFO_PRAGMA_DEFAULT_INDEX = 4;
/**
* @param db the SQLiteDatabase to insert into
* @param tableName the name of the table to insert into
*/
public InsertHelper(SQLiteDatabase db, String tableName) {
mDb = db;
mTableName = tableName;
}
public void initColumns() {
getStatement(false);
}
private void buildSQL() throws SQLException {
StringBuilder sb = new StringBuilder(128);
sb.append("INSERT INTO ");
sb.append(mTableName);
sb.append(" (");
StringBuilder sbv = new StringBuilder(128);
sbv.append("VALUES (");
int i = 1;
Cursor cur = null;
try {
cur = mDb.rawQuery("PRAGMA table_info(" + mTableName + ")", null);
mColumns = new HashMap<String, Integer>(cur.getCount());
while (cur.moveToNext()) {
String columnName = cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX);
String defaultValue = cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX);
mColumns.put(columnName, i);
sb.append("'");
sb.append(columnName);
sb.append("'");
if (defaultValue == null) {
sbv.append("?");
} else {
sbv.append("COALESCE(?, ");
sbv.append(defaultValue);
sbv.append(")");
}
sb.append(i == cur.getCount() ? ") " : ", ");
sbv.append(i == cur.getCount() ? ");" : ", ");
++i;
}
} finally {
if (cur != null) cur.close();
}
sb.append(sbv);
mInsertSQL = sb.toString();
}
private SQLiteStatement getStatement(boolean allowReplace) throws SQLException {
if (allowReplace) {
if (mReplaceStatement == null) {
if (mInsertSQL == null) buildSQL();
// chop "INSERT" off the front and prepend "INSERT OR REPLACE" instead.
String replaceSQL = "INSERT OR REPLACE" + mInsertSQL.substring(6);
mReplaceStatement = mDb.compileStatement(replaceSQL);
}
return mReplaceStatement;
} else {
if (mInsertStatement == null) {
if (mInsertSQL == null) buildSQL();
mInsertStatement = mDb.compileStatement(mInsertSQL);
}
return mInsertStatement;
}
}
public SQLiteStatement getStatementIgnore() throws SQLException {
if (mIgnoreStatement == null) {
if (mInsertSQL == null) buildSQL();
// chop "INSERT" off the front and prepend "INSERT OR IGNORE" instead.
String replaceSQL = "INSERT OR IGNORE" + mInsertSQL.substring(6);
mIgnoreStatement = mDb.compileStatement(replaceSQL);
}
return mIgnoreStatement;
}
/**
* Performs an insert, adding a new row with the given values.
*
* @param values the set of values with which to populate the
* new row
* @param allowReplace if true, the statement does "INSERT OR
* REPLACE" instead of "INSERT", silently deleting any
* previously existing rows that would cause a conflict
*
* @return the row ID of the newly inserted row, or -1 if an
* error occurred
*/
private long insertInternal(ContentValues values, boolean allowReplace) {
// Start a transaction even though we don't really need one.
// This is to help maintain compatibility with applications that
// access InsertHelper from multiple threads even though they never should have.
// The original code used to lock the InsertHelper itself which was prone
// to deadlocks. Starting a transaction achieves the same mutual exclusion
// effect as grabbing a lock but without the potential for deadlocks.
mDb.beginTransactionNonExclusive();
try {
SQLiteStatement stmt = getStatement(allowReplace);
stmt.clearBindings();
for (Map.Entry<String, Object> e: values.valueSet()) {
final String key = e.getKey();
int i = getColumnIndex(key);
DatabaseUtils.bindObjectToProgram(stmt, i, e.getValue());
}
long result = stmt.executeInsert();
mDb.setTransactionSuccessful();
return result;
} catch (SQLException e) {
Log.e(TAG, "Error inserting " + values + " into table " + mTableName, e);
return -1;
} finally {
mDb.endTransaction();
}
}
private long insertInternalIgnore(ContentValues values, boolean allowIgnore) {
mDb.beginTransactionNonExclusive();
try {
SQLiteStatement stmt = getStatementIgnore();
stmt.clearBindings();
for (Map.Entry<String, Object> e: values.valueSet()) {
final String key = e.getKey();
int i = getColumnIndexForIngnore(key);
DatabaseUtils.bindObjectToProgram(stmt, i, e.getValue());
}
long result = stmt.executeInsert();
mDb.setTransactionSuccessful();
return result;
} catch (SQLException e) {
Log.e(TAG, "Error inserting " + values + " into table " + mTableName, e);
return -1;
} finally {
mDb.endTransaction();
}
}
public long insertIgnore() {
mDb.beginTransactionNonExclusive();
try {
SQLiteStatement stmt = getStatementIgnore();
stmt.clearBindings();
long result = stmt.executeInsert();
mDb.setTransactionSuccessful();
return result;
} catch (SQLException e) {
return -1;
} finally {
mDb.endTransaction();
}
}
public long insertIgnore(SQLiteStatement stmt) {
mDb.beginTransactionNonExclusive();
try {
long result = stmt.executeInsert();
mDb.setTransactionSuccessful();
return result;
} catch (SQLException e) {
return -1;
} finally {
mDb.endTransaction();
}
}
public long insertIgnoreNonTransaction(SQLiteStatement stmt) {
try {
long result = stmt.executeInsert();
return result;
} catch (SQLException e) {
return -1;
}
}
public void beginTransactionNonExclusive() {
mDb.beginTransactionNonExclusive();
}
public void setTransactionSuccessful() {
mDb.setTransactionSuccessful();
}
public void endTransaction(){
mDb.endTransaction();
}
/**
* Returns the index of the specified column. This is index is suitagble for use
* in calls to bind().
* @param key the column name
* @return the index of the column
*/
public int getColumnIndex(String key) {
getStatement(false);
final Integer index = mColumns.get(key);
if (index == null) {
throw new IllegalArgumentException("column '" + key + "' is invalid");
}
return index;
}
public int getColumnIndexForIngnore(String key) {
final Integer index = mColumns.get(key);
if (index == null) {
throw new IllegalArgumentException("column '" + key + "' is invalid");
}
return index;
}
/**
* Bind the value to an index. A prepareForInsert() or prepareForReplace()
* without a matching execute() must have already have been called.
* @param index the index of the slot to which to bind
* @param value the value to bind
*/
public void bind(int index, double value) {
mPreparedStatement.bindDouble(index, value);
}
/**
* Bind the value to an index. A prepareForInsert() or prepareForReplace()
* without a matching execute() must have already have been called.
* @param index the index of the slot to which to bind
* @param value the value to bind
*/
public void bind(int index, float value) {
mPreparedStatement.bindDouble(index, value);
}
/**
* Bind the value to an index. A prepareForInsert() or prepareForReplace()
* without a matching execute() must have already have been called.
* @param index the index of the slot to which to bind
* @param value the value to bind
*/
public void bind(int index, long value) {
mPreparedStatement.bindLong(index, value);
}
/**
* Bind the value to an index. A prepareForInsert() or prepareForReplace()
* without a matching execute() must have already have been called.
* @param index the index of the slot to which to bind
* @param value the value to bind
*/
public void bind(int index, int value) {
mPreparedStatement.bindLong(index, value);
}
/**
* Bind the value to an index. A prepareForInsert() or prepareForReplace()
* without a matching execute() must have already have been called.
* @param index the index of the slot to which to bind
* @param value the value to bind
*/
public void bind(int index, boolean value) {
mPreparedStatement.bindLong(index, value ? 1 : 0);
}
/**
* Bind null to an index. A prepareForInsert() or prepareForReplace()
* without a matching execute() must have already have been called.
* @param index the index of the slot to which to bind
*/
public void bindNull(int index) {
mPreparedStatement.bindNull(index);
}
/**
* Bind the value to an index. A prepareForInsert() or prepareForReplace()
* without a matching execute() must have already have been called.
* @param index the index of the slot to which to bind
* @param value the value to bind
*/
public void bind(int index, byte[] value) {
if (value == null) {
mPreparedStatement.bindNull(index);
} else {
mPreparedStatement.bindBlob(index, value);
}
}
/**
* Bind the value to an index. A prepareForInsert() or prepareForReplace()
* without a matching execute() must have already have been called.
* @param index the index of the slot to which to bind
* @param value the value to bind
*/
public void bind(int index, String value) {
if (value == null) {
mPreparedStatement.bindNull(index);
} else {
mPreparedStatement.bindString(index, value);
}
}
/**
* Performs an insert, adding a new row with the given values.
* If the table contains conflicting rows, an error is
* returned.
*
* @param values the set of values with which to populate the
* new row
*
* @return the row ID of the newly inserted row, or -1 if an
* error occurred
*/
public long insert(ContentValues values) {
return insertInternal(values, false);
}
public long insertIgnore(ContentValues values) {
return insertInternalIgnore(values, true);
}
/**
* Execute the previously prepared insert or replace using the bound values
* since the last call to prepareForInsert or prepareForReplace.
*
* <p>Note that calling bind() and then execute() is not thread-safe. The only thread-safe
* way to use this class is to call insert() or replace().
*
* @return the row ID of the newly inserted row, or -1 if an
* error occurred
*/
public long execute() {
if (mPreparedStatement == null) {
throw new IllegalStateException("you must prepare this inserter before calling "
+ "execute");
}
try {
return mPreparedStatement.executeInsert();
} catch (SQLException e) {
Log.e(TAG, "Error executing InsertHelper with table " + mTableName, e);
return -1;
} finally {
// you can only call this once per prepare
mPreparedStatement = null;
}
}
/**
* Prepare the InsertHelper for an insert. The pattern for this is:
* <ul>
* <li>prepareForInsert()
* <li>bind(index, value);
* <li>bind(index, value);
* <li>...
* <li>bind(index, value);
* <li>execute();
* </ul>
*/
public void prepareForInsert() {
mPreparedStatement = getStatement(false);
mPreparedStatement.clearBindings();
}
/**
* Prepare the InsertHelper for a replace. The pattern for this is:
* <ul>
* <li>prepareForReplace()
* <li>bind(index, value);
* <li>bind(index, value);
* <li>...
* <li>bind(index, value);
* <li>execute();
* </ul>
*/
public void prepareForReplace() {
mPreparedStatement = getStatement(true);
mPreparedStatement.clearBindings();
}
public void prepareForIgnore() {
mPreparedStatement = getStatementIgnore();
mPreparedStatement.clearBindings();
}
/**
* Performs an insert, adding a new row with the given values.
* If the table contains conflicting rows, they are deleted
* and replaced with the new row.
*
* @param values the set of values with which to populate the
* new row
*
* @return the row ID of the newly inserted row, or -1 if an
* error occurred
*/
public long replace(ContentValues values) {
return insertInternal(values, true);
}
/**
* Close this object and release any resources associated with
* it. The behavior of calling <code>insert()</code> after
* calling this method is undefined.
*/
public void close() {
if (mInsertStatement != null) {
mInsertStatement.close();
mInsertStatement = null;
}
if (mReplaceStatement != null) {
mReplaceStatement.close();
mReplaceStatement = null;
}
if (mIgnoreStatement != null) {
mIgnoreStatement.close();
mIgnoreStatement = null;
}
mInsertSQL = null;
mColumns = null;
}
}