一、问题现象以及结论
1、问题现象
做数据库查询操作时，报了异常 IllegalArgumentException：the bind value at index 2 is null。
2、异常代码片段
cursor = db.query("table_account", null,“account_name” + "=? and "+"account_tel"+ "=? and "+"account_sex"+"=?",new String[]{entity.getName(),entity.getTel(),String.valueOf(entity.getSex())}, null, null, null);3、问题原因
传入的entity.getTel() 为null了，SQLite内部抛出 IllegalArgumentException ，查询任务终止。
二、详细分析
1、异常出处代码片段
发现直接从db.query方法往下追有点麻烦，分支比较多，所以直接找到异常出处的代码，然后倒追回来。如果不习惯，也可以从后往前看。
IllegalArgumentException("the bind value at index " + index + " is null")。
SQLiteProgram.java[1.1]
/**
* Bind a String value to this statement. The value remains bound until
* {@link #clearBindings} is called.
*
* @param index The 1-based index to the parameter to bind
* @param value The value to bind, must not be null
*/
public void bindString(int index, String value) {
if (value == null) {
throw new IllegalArgumentException("the bind value at index " + index + " is null");
}
bind(index, value);
}2、代码追踪
### SQLiteProgram.java[1.2]
/**
* Given an array of String bindArgs, this method binds all of them in one single call.
*
* @param bindArgs the String array of bind args, none of which must be null.
*/
public void bindAllArgsAsStrings(String[] bindArgs) {
if (bindArgs != null) {
for (int i = bindArgs.length; i != 0; i--) {
bindString(i, bindArgs[i - 1]);//调用[1.1]
}
}
}SQLiteDirectCursorDriver.java[2.1]
public Cursor query(CursorFactory factory, String[] selectionArgs) {
final SQLiteQuery query = new SQLiteQuery(mDatabase, mSql, mCancellationSignal);
final Cursor cursor;
try {
query.bindAllArgsAsStrings(selectionArgs);//调用[1.2]
if (factory == null) {
cursor = new SQLiteCursor(this, mEditTable, query);// 这里应该是用了 default factory
} else {
cursor = factory.newCursor(mDatabase, this, mEditTable, query);
}
} catch (RuntimeException ex) {
query.close();
throw ex;
}
mQuery = query;
return cursor;
}SQLiteDatabase.java[3.1]
/**
* Runs the provided SQL and returns a cursor over the result set.
*
* @param cursorFactory the cursor factory to use, or null for the default factory
*/
public Cursor rawQueryWithFactory(
CursorFactory cursorFactory, String sql, String[] selectionArgs,
String editTable, CancellationSignal cancellationSignal) {
acquireReference();
try {
SQLiteCursorDriver driver = new SQLiteDirectCursorDriver(this, sql, editTable,
cancellationSignal);
//cursorFactory传入为null则用mCursorFactory
return driver.query(cursorFactory != null ? cursorFactory : mCursorFactory,
selectionArgs);//调用[2.1]
} finally {
releaseReference();
}
}SQLiteDatabase.java[3.2]
/**
* Query the given URL, returning a {@link Cursor} over the result set.
*
* @param cursorFactory the cursor factory to use, or null for the default factory
* @param distinct true if you want each row to be unique, false otherwise.
*/
public Cursor queryWithFactory(CursorFactory cursorFactory,
boolean distinct, String table, String[] columns,
String selection, String[] selectionArgs, String groupBy,
String having, String orderBy, String limit, CancellationSignal cancellationSignal) {
acquireReference();
try {
String sql = SQLiteQueryBuilder.buildQueryString(
distinct, table, columns, selection, groupBy, having, orderBy, limit);//调用[6.1]
return rawQueryWithFactory(cursorFactory, sql, selectionArgs,
findEditTable(table), cancellationSignal);//调用[3.1]
} finally {
releaseReference();
}
}SQLiteDatabase.java[3.3]
/**
* Query the given URL, returning a {@link Cursor} over the result set.
*
* @param distinct true if you want each row to be unique, false otherwise.
*/
public Cursor query(boolean distinct, String table, String[] columns,
String selection, String[] selectionArgs, String groupBy,
String having, String orderBy, String limit) {
return queryWithFactory(null, distinct, table, columns, selection, selectionArgs,
groupBy, having, orderBy, limit, null);//调用[3.2]
}SQLiteDatabase.java[3.4]
/**
* Query the given table, returning a {@link Cursor} over the result set.
*
* @return A {@link Cursor} object, which is positioned before the first entry. Note that
* {@link Cursor}s are not synchronized, see the documentation for more details.
* @see Cursor
*/
public Cursor query(String table, String[] columns, String selection,
String[] selectionArgs, String groupBy, String having,
String orderBy) {
return query(false, table, columns, selection, selectionArgs, groupBy,
having, orderBy, null /* limit */);//调用[3.3]
}三、扩展问题
1、distinct 这个布尔参数在控制什么
看到上述代码块中的[3.4]调用[3.3]中直接给 distinct 传了false，看代码注释：distinct true if you want each row to be unique, false otherwise.大致意思是我true的时候有一个唯一性的要求。搜索代码如下：
BordeauxSessionStorage[4.1]
BordeauxSessionManager.Session getSession(String key) {
//调用[3.3]
Cursor cursor = mDbSessions.query(true, SESSION_TABLE,
new String[]{COLUMN_KEY, COLUMN_CLASS, COLUMN_MODEL, COLUMN_TIME},
COLUMN_KEY + "=\"" + key + "\"", null, null, null, null, null);
if ((cursor == null) | (cursor.getCount() == 0)) {
cursor.close();
return null;
}
if (cursor.getCount() > 1) {
cursor.close();
throw new RuntimeException("Unexpected duplication in session table for key:" + key);
}
cursor.moveToFirst();
BordeauxSessionManager.Session s = getSessionFromCursor(cursor);
cursor.close();
return s;
}BordeauxSessionManager[5.1]
这里，我可以看到这个BordeauxSessionManager的类注释：
// This class manages the learning sessions from multiple applications.
// The learning sessions are automatically backed up to the storage.
这里应该是和Application的管理有关。
public IBinder getSessionBinder(Class learnerClass, SessionKey key) {
if (mSessions.containsKey(key.value)) {
return mSessions.get(key.value).learner.getBinder();
}
// not in memory cache
try {
// try to find it in the database
Session stored = mSessionStorage.getSession(key.value);//调用[4.1]
if (stored != null) {
// set the callback, so that we can save the state
stored.learner.setModelChangeCallback(new LearningUpdateCallback(key.value));
// found session in the storage, put in the cache
mSessions.put(key.value, stored);
return stored.learner.getBinder();
}
// if session is not already stored, create a new one.
Log.i(TAG, "create a new learning session: " + key.value);
IBordeauxLearner learner =
(IBordeauxLearner) learnerClass.getConstructor().newInstance();
// set the callback, so that we can save the state
learner.setModelChangeCallback(new LearningUpdateCallback(key.value));
Session session = new Session();
session.learnerClass = learnerClass;
session.learner = learner;
mSessions.put(key.value, session);
return learner.getBinder();
} catch (Exception e) {
throw new RuntimeException("Can't instantiate class: " +
learnerClass.getName());
}
}但是不懂是干什么的，接着看到这里。
BordeauxSessionManager[5.2]
// internal unique key that identifies the learning instance.
// Composed by the package id of the calling process, learning class name
// and user specified name.
public SessionKey getSessionKey(String callingUid, Class learnerClass, String name) {
SessionKey key = new SessionKey();
key.value = callingUid + "#" + "_" + name + "_" + learnerClass.getName();
return key;
}还是不懂，先放弃了。//TODO
还是回头去去看下这个参的true或者false实现上有啥区别。
SQLiteQueryBuilder.java[6.1]
// SQLiteDatabase.java[3.2]调用了该方法。
public static String buildQueryString(
boolean distinct, String tables, String[] columns, String where,
String groupBy, String having, String orderBy, String limit) {
//这里做的不错，当两个参数为一对都存在才可用的时候要进行校验。
if (TextUtils.isEmpty(groupBy) && !TextUtils.isEmpty(having)) {
throw new IllegalArgumentException(
"HAVING clauses are only permitted when using a groupBy clause");
}
if (!TextUtils.isEmpty(limit) && !sLimitPattern.matcher(limit).matches()) {
throw new IllegalArgumentException("invalid LIMIT clauses:" + limit);
}
StringBuilder query = new StringBuilder(120);
query.append("SELECT ");
if (distinct) {
query.append("DISTINCT ");//区别就是在这里，拼接SQL语句的时候，加了个 DISTINCT
}
if (columns != null && columns.length != 0) {
appendColumns(query, columns);
} else {
query.append("* ");
}
query.append("FROM ");
query.append(tables);
appendClause(query, " WHERE ", where);
appendClause(query, " GROUP BY ", groupBy);
appendClause(query, " HAVING ", having);
appendClause(query, " ORDER BY ", orderBy);
appendClause(query, " LIMIT ", limit);
return query.toString();
}最后发现 distinct 为 true 的时候，给查询的 SQL 语句拼接了一个参数 DISTINCT。搜索下来段英文的解释装个逼。
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
The SELECT DISTINCT statement is used to return only distinct (different) values.
2、cursorFactory 传null了，注释说用 default factory 怎么玩的
首先观察SQLiteDatabase.java[3.2]这个类rawQueryWithFactory方法中cursorFactory传入为null则用 mCursorFactory，这样说明这个mCursorFactory，也就是所说的 default factory。
SQLiteDatabase.java[3.5]
private SQLiteDatabase(String path, int openFlags, CursorFactory cursorFactory,
DatabaseErrorHandler errorHandler) {
mCursorFactory = cursorFactory;//mCursorFactory
mErrorHandler = errorHandler != null ? errorHandler : new DefaultDatabaseErrorHandler();
mConfigurationLocked = new SQLiteDatabaseConfiguration(path, openFlags);
}发现mCursorFactory是构造中继续传入。
SQLiteDatabase.java[3.6]
/**
* Open the database according to the flags {@link #OPEN_READWRITE}
* @param factory an optional factory class that is called to instantiate a
* cursor when query is called, or null for default
* @param flags to control database access mode
* @param errorHandler the {@link DatabaseErrorHandler} obj to be used to handle corruption
* when sqlite reports database corruption
* @return the newly opened database
* @throws SQLiteException if the database cannot be opened
*/
public static SQLiteDatabase openDatabase(String path, CursorFactory factory, int flags,
DatabaseErrorHandler errorHandler) {
SQLiteDatabase db = new SQLiteDatabase(path, flags, factory, errorHandler);
db.open();
return db;
}SQLiteOpenHelper.java[7.1]
private SQLiteDatabase getDatabaseLocked(boolean writable) {
if (mDatabase != null) {
if (!mDatabase.isOpen()) {
// Darn! The user closed the database by calling mDatabase.close().
mDatabase = null;
} else if (!writable || !mDatabase.isReadOnly()) {
// The database is already open for business.
return mDatabase;
}
}
if (mIsInitializing) {
throw new IllegalStateException("getDatabase called recursively");
}
SQLiteDatabase db = mDatabase;
try {
mIsInitializing = true;
if (db != null) {
if (writable && db.isReadOnly()) {
db.reopenReadWrite();
}
} else if (mName == null) {
db = SQLiteDatabase.create(null);
} else {
try {
if (DEBUG_STRICT_READONLY && !writable) {
final String path = mContext.getDatabasePath(mName).getPath();
//调用了[3.6]
db = SQLiteDatabase.openDatabase(path, mFactory,
SQLiteDatabase.OPEN_READONLY, mErrorHandler);
} else {
db = mContext.openOrCreateDatabase(mName, mEnableWriteAheadLogging ?
Context.MODE_ENABLE_WRITE_AHEAD_LOGGING : 0,
mFactory, mErrorHandler);
}
} catch (SQLiteException ex) {
if (writable) {
throw ex;
}
Log.e(TAG, "Couldn't open " + mName
+ " for writing (will try read-only):", ex);
final String path = mContext.getDatabasePath(mName).getPath();
//调用了[3.6]
db = SQLiteDatabase.openDatabase(path, mFactory,
SQLiteDatabase.OPEN_READONLY, mErrorHandler);
}
}
onConfigure(db);
final int version = db.getVersion();
if (version != mNewVersion) {
if (db.isReadOnly()) {
throw new SQLiteException("Can't upgrade read-only database from version " +
db.getVersion() + " to " + mNewVersion + ": " + mName);
}
db.beginTransaction();
try {
if (version == 0) {
onCreate(db);
} else {
if (version > mNewVersion) {
onDowngrade(db, version, mNewVersion);
} else {
onUpgrade(db, version, mNewVersion);
}
}
db.setVersion(mNewVersion);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
}
onOpen(db);
if (db.isReadOnly()) {
Log.w(TAG, "Opened " + mName + " in read-only mode");
}
mDatabase = db;
return db;
} finally {
mIsInitializing = false;
if (db != null && db != mDatabase) {
db.close();
}
}
}SQLiteOpenHelper.java[7.2]
public SQLiteDatabase getReadableDatabase() {
synchronized (this) {
return getDatabaseLocked(false);
}
}SQLiteOpenHelper.java[7.3]
public SQLiteDatabase getWritableDatabase() {
synchronized (this) {
return getDatabaseLocked(true);
}
}SQLiteOpenHelper.java[7.4]
public SQLiteOpenHelper(Context context, String name, CursorFactory factory, int version) {
this(context, name, factory, version, null);
}最后追踪到这里，又回到开头了。我们通常要继承SQLiteOpenHelper，但是发现我们一般在这里传入的就是null，那这个并没有任何实现。
MyDBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}DatabaseCursorTest[8.1]
@MediumTest
public void testRequeryWithAlteredSelectionArgs() throws Exception {
/**
* Test the ability of a subclass of SQLiteCursor to change its query arguments.
*/
populateDefaultTable();
SQLiteDatabase.CursorFactory factory = new SQLiteDatabase.CursorFactory() {
public Cursor newCursor(
SQLiteDatabase db, SQLiteCursorDriver masterQuery, String editTable,
SQLiteQuery query) {
return new SQLiteCursor(db, masterQuery, editTable, query) {
@Override
public boolean requery() {
setSelectionArguments(new String[]{"2"});
return super.requery();
}
};
}
};
// 省略 xxx
}
}根据SQLiteDirectCursorDriver.java[2.1] ，看到一个测试代码里边有这么个写法，这时候应该大概明白了这个CursorFactory的作用了，实际可以在外部自定义newCursor的实现。
观察到SQLiteDirectCursorDriver.java[2.1] 的代码，发现传入的CursorFactory,new的方式不一样。
SQLiteCursor[9.1]
/**
* Execute a query and provide access to its result set through a Cursor
* interface. For a query such as: {@code SELECT name, birth, phone FROM
* myTable WHERE ... LIMIT 1,20 ORDER BY...} the column names (name, birth,
* phone) would be in the projection argument and everything from
* {@code FROM} onward would be in the params argument.
*
* @param editTable the name of the table used for this query
* @param query the {@link SQLiteQuery} object associated with this cursor object.
*/
public SQLiteCursor(SQLiteCursorDriver driver, String editTable, SQLiteQuery query) {
if (query == null) {
throw new IllegalArgumentException("query object cannot be null");
}
if (StrictMode.vmSqliteObjectLeaksEnabled()) {
mStackTrace = new DatabaseObjectNotClosedException().fillInStackTrace();
} else {
mStackTrace = null;
}
mDriver = driver;
mEditTable = editTable;
mColumnNameMap = null;
mQuery = query;
mColumns = query.getColumnNames();
mRowIdColumnIndex = DatabaseUtils.findRowIdColumnIndex(mColumns);
}SQLiteDirectCursorDriver.java[2.2]
/**
* Used to allow returning sub-classes of {@link Cursor} when calling query.
*/
public interface CursorFactory {
/**
* See {@link SQLiteCursor#SQLiteCursor(SQLiteCursorDriver, String, SQLiteQuery)}.
*/
public Cursor newCursor(SQLiteDatabase db,
SQLiteCursorDriver masterQuery, String editTable,
SQLiteQuery query);
}根据上述接口，我们是不是考虑在进行查询操作时假如有部分特殊的要求，可以自定义实现自己想要的Cursor。
3、最后如何得到我们想要的返回值Cursor？
根据SQLiteCursor[9.1]实际已经知道看到了Cursor实际是我们new出来的。不过在构造中有几个操作。
mQuery = query;
mColumns = query.getColumnNames();
mRowIdColumnIndex = DatabaseUtils.findRowIdColumnIndex(mColumns);五、参考资料
SQL SELECT DISTINCT Statement
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent