SQLite查询操作源码分析：从 IllegalArgumentException：the bind value at index 2  is null 说起

# 一、问题现象以及结论
### 1、问题现象

做数据库查询操作时，报了异常 IllegalArgumentException：the bind value at index 2  is null。


### 2、异常代码片段
```java
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]
```java

    /**
     * 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]
```java

    /**
     * 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] 

```java

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]
```java
    /**
     * 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]
```java
 /**
     * 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]
```java
 /**
     * 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]
```java
  /**
     * 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]
```java
    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的类注释：
```java
// This class manages the learning sessions from multiple applications.
// The learning sessions are automatically backed up to the storage.
```
这里应该是和Application的管理有关。

```java
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]
```java
    // 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]
```java
 // 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]
```java
    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]
```java
    /**
     * 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]
```java
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]
```java
    public SQLiteDatabase getReadableDatabase() {
        synchronized (this) {
            return getDatabaseLocked(false);
        }
    }

```
### SQLiteOpenHelper.java[7.3]
```java
    public SQLiteDatabase getWritableDatabase() {
        synchronized (this) {
            return getDatabaseLocked(true);
        }
    }
```
### SQLiteOpenHelper.java[7.4]
```java
    public SQLiteOpenHelper(Context context, String name, CursorFactory factory, int version) {
        this(context, name, factory, version, null);
    }
```
最后追踪到这里，又回到开头了。我们通常要继承SQLiteOpenHelper，但是发现我们一般在这里传入的就是null，那这个并没有任何实现。
```java
   MyDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
```

### DatabaseCursorTest[8.1]
```java
  @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]
```java
    /**
     * 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] 
```java
    /**
     * 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出来的。不过在构造中有几个操作。
```java
        mQuery = query;

        mColumns = query.getColumnNames();
        mRowIdColumnIndex = DatabaseUtils.findRowIdColumnIndex(mColumns);
```


# 五、参考资料
[SQL SELECT DISTINCT Statement](https://www.w3schools.com/sql/sql_distinct.asp)

SQLite查询操作源码分析：从 IllegalArgumentException：the bind value at index 2 is null 说起 #48

Description

一、问题现象以及结论

1、问题现象

2、异常代码片段

3、问题原因

二、详细分析

1、异常出处代码片段

SQLiteProgram.java[1.1]

2、代码追踪

SQLiteDirectCursorDriver.java[2.1]

SQLiteDatabase.java[3.1]

SQLiteDatabase.java[3.2]

SQLiteDatabase.java[3.3]

SQLiteDatabase.java[3.4]

三、扩展问题

1、distinct 这个布尔参数在控制什么

BordeauxSessionStorage[4.1]

BordeauxSessionManager[5.1]

BordeauxSessionManager[5.2]

SQLiteQueryBuilder.java[6.1]

2、cursorFactory 传null了，注释说用 default factory 怎么玩的

SQLiteDatabase.java[3.5]

SQLiteDatabase.java[3.6]

SQLiteOpenHelper.java[7.1]

SQLiteOpenHelper.java[7.2]

SQLiteOpenHelper.java[7.3]

SQLiteOpenHelper.java[7.4]

DatabaseCursorTest[8.1]

SQLiteCursor[9.1]

SQLiteDirectCursorDriver.java[2.2]

3、最后如何得到我们想要的返回值Cursor？

五、参考资料

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions