安卓访问mysql的源码_Android数据库一些源码分析

对于批量数据插入这种最常见的情况来说，我们来看两种实现方式(两种都用了事务)。

下面这种应该是最多人使用的插入数据的方法：

1 public long addByExec(Listpersons) {2

3 long start =System.currentTimeMillis();4 db.beginTransaction();5

6 for(Person person : persons) {7 db.execSQL(" INSERT INTO person(name,age,info) VALUES(?, ?, ?) ",8 newObject[] { person.name, person.age, person.info });9 }10

11 db.setTransactionSuccessful();12 long end =System.currentTimeMillis();13 db.endTransaction();14 return end -start;15

16 }

再看一种比较少用的插入方法

1 public long addByStatement(Listpersons) {2 long start =System.currentTimeMillis();3 db.beginTransaction();4 SQLiteStatement sqLiteStatement =db.compileStatement(sql);5

6 for(Person person : persons) {7 sqLiteStatement.bindString(1, person.name);8 sqLiteStatement.bindString(2, person.age);9 sqLiteStatement.bindString(3, person.info);10 sqLiteStatement.executeInsert();11 }12 db.setTransactionSuccessful();13 long end =System.currentTimeMillis();14 db.endTransaction();15 return end -start;16 }

然后我们分别用这两个方法 来向数据库里面插入一万条数据 看看耗时多少。为了演示效果更加突出一点，我录制了一个GIF，同时，

这2个方法我也没有用子线程来操作他，直接在ui线程上操作 所以看起来效果会比较突出一些(但是自己写代码的时候千万别这么写小心ANR)。

可以看出来后者耗时几乎只有前者的 一半(所以以后大家在做大批量数据插入的时候可以考虑后者的实现方式)。我们来看看源代码为啥会这样。

首先看前者的实现方法源码

1 public void execSQL(String sql, Object[] bindArgs) throwsSQLException {2 if (bindArgs == null) {3 throw new IllegalArgumentException("Empty bindArgs");4 }5 executeSql(sql, bindArgs);6 }7

8 private int executeSql(String sql, Object[] bindArgs) throwsSQLException {9 if (DatabaseUtils.getSqlStatementType(sql) ==DatabaseUtils.STATEMENT_ATTACH) {10 disableWriteAheadLogging();11 mHasAttachedDbs = true;12 }13 SQLiteStatement statement = new SQLiteStatement(this, sql, bindArgs);14 try{15 returnstatement.executeUpdateDelete();16 } catch(SQLiteDatabaseCorruptException e) {17 onCorruption();18 throwe;19 } finally{20 statement.close();21 }22 }

我们发现 前者的实现 实际上最后也是通过SQLiteStatement 这个类是操作的。

而后者不过是

1 public SQLiteStatement compileStatement(String sql) throwsSQLException {2 verifyDbIsOpen();3 return new SQLiteStatement(this, sql, null);4 }

所以实际上前者之所以比后者耗时 应该是下面这段代码的原因：

1 if (DatabaseUtils.getSqlStatementType(sql) ==DatabaseUtils.STATEMENT_ATTACH) {2 disableWriteAheadLogging();3 mHasAttachedDbs = true;4 }

1 public static intgetSqlStatementType(String sql) {2 sql =sql.trim();3 if (sql.length() < 3) {4 returnSTATEMENT_OTHER;5 }6 String prefixSql = sql.substring(0, 3).toUpperCase();7 if (prefixSql.equals("SEL")) {8 returnSTATEMENT_SELECT;9 } else if (prefixSql.equals("INS") ||

10 prefixSql.equals("UPD") ||

11 prefixSql.equals("REP") ||

12 prefixSql.equals("DEL")) {13 returnSTATEMENT_UPDATE;14 } else if (prefixSql.equals("ATT")) {15 returnSTATEMENT_ATTACH;16 } else if (prefixSql.equals("COM")) {17 returnSTATEMENT_COMMIT;18 } else if (prefixSql.equals("END")) {19 returnSTATEMENT_COMMIT;20 } else if (prefixSql.equals("ROL")) {21 returnSTATEMENT_ABORT;22 } else if (prefixSql.equals("BEG")) {23 returnSTATEMENT_BEGIN;24 } else if (prefixSql.equals("PRA")) {25 returnSTATEMENT_PRAGMA;26 } else if (prefixSql.equals("CRE") || prefixSql.equals("DRO") ||

27 prefixSql.equals("ALT")) {28 returnSTATEMENT_DDL;29 } else if (prefixSql.equals("ANA") || prefixSql.equals("DET")) {30 returnSTATEMENT_UNPREPARED;31 }32 returnSTATEMENT_OTHER;33 }

实际上就是多了一个字符串处理的函数。这就是为什么前者耗时要比后者多。因为实际上直接调用executeSql的时候

他里面是先做字符串处理然后再调用SQLiteStatement来执行，这个过程当然是比我们直接调用SQLiteStatement

来执行速度慢的。

我们首先来看一下下面这个函数

1 publicCursor queryTest1() {2 long start1 =System.currentTimeMillis();3 Cursor c = db.rawQuery("select * from t1,t3 where t1.num>t3.num", null);4 long end1 =System.currentTimeMillis();5 Log.v("DBManager", "time1 need " + (end1 -start1));6 long start2 =System.currentTimeMillis();7 c.moveToNext();8 long end2 =System.currentTimeMillis();9 Log.v("DBManager", "time2 need" + (end2 -start2));10 long start3 =System.currentTimeMillis();11 c.moveToNext();12 long end3 =System.currentTimeMillis();13 Log.v("DBManager", "time3 need" + (end3 -start3));14 returnc;15 }

一个很常见的，多表查询的函数，有些人可能会奇怪为啥在这个地方我要加那么多日志。实际上如果你t1和t3的数据都很多的话，这个查询是可以预料到的会非常耗时。

很多人都会以为这个耗时是在下面这条语句做的：

1 Cursor c = db.rawQuery("select * from t1,t3 where t1.num>t3.num", null);

但是实际上这个查询耗时是在你第一调用

1 c.moveToNext();

来做的，有兴趣的同学可以自己试一下，我们这里就不帮大家来演示这个效果了，但是可以帮助大家分析一下源代码为什么会是这样奇怪的结果？

我们首先来分析一下rawQuery 这个函数

1 publicCursor rawQuery(String sql, String[] selectionArgs) {2 return rawQueryWithFactory(null, sql, selectionArgs, null);3 }4

5 /**

6 * Runs the provided SQL and returns a cursor over the result set.7 *8 *@paramcursorFactory the cursor factory to use, or null for the default factory9 *@paramsql the SQL query. The SQL string must not be ; terminated10 *@paramselectionArgs You may include ?s in where clause in the query,11 * which will be replaced by the values from selectionArgs. The12 * values will be bound as Strings.13 *@parameditTable the name of the first table, which is editable14 *@returnA {@linkCursor} object, which is positioned before the first entry. Note that15 * {@linkCursor}s are not synchronized, see the documentation for more details.16 */

17 publicCursor rawQueryWithFactory(18 CursorFactory cursorFactory, String sql, String[] selectionArgs,19 String editTable) {20 verifyDbIsOpen();21 BlockGuard.getThreadPolicy().onReadFromDisk();22

23 SQLiteDatabase db =getDbConnection(sql);24 SQLiteCursorDriver driver = newSQLiteDirectCursorDriver(db, sql, editTable);25

26 Cursor cursor = null;27 try{28 cursor =driver.query(29 cursorFactory != null ?cursorFactory : mFactory,30 selectionArgs);31 } finally{32 releaseDbConnection(db);33 }34 returncursor;35 }

看一下24行，发现是构造了一个driver对象 然后调用这个driver对象的query方法

我们继续跟踪源代码

1 publicSQLiteDirectCursorDriver(SQLiteDatabase db, String sql, String editTable,2 CancellationSignal cancellationSignal) {3 mDatabase =db;4 mEditTable =editTable;5 mSql =sql;6 mCancellationSignal =cancellationSignal;7 }8

9 publicCursor query(CursorFactory factory, String[] selectionArgs) {10 final SQLiteQuery query = newSQLiteQuery(mDatabase, mSql, mCancellationSignal);11 finalCursor cursor;12 try{13 query.bindAllArgsAsStrings(selectionArgs);14

15 if (factory == null) {16 cursor = new SQLiteCursor(this, mEditTable, query);17 } else{18 cursor = factory.newCursor(mDatabase, this, mEditTable, query);19 }20 } catch(RuntimeException ex) {21 query.close();22 throwex;23 }24

25 mQuery =query;26 returncursor;27 }

发现这个返回的cursor实际上就是直接new出来的一个对象

1 publicSQLiteCursor(SQLiteCursorDriver driver, String editTable, SQLiteQuery query) {2 if (query == null) {3 throw new IllegalArgumentException("query object cannot be null");4 }5 if (query.mDatabase == null) {6 throw new IllegalArgumentException("query.mDatabase cannot be null");7 }8 mStackTrace = newDatabaseObjectNotClosedException().fillInStackTrace();9 mDriver =driver;10 mEditTable =editTable;11 mColumnNameMap = null;12 mQuery =query;13

14 query.mDatabase.lock(query.mSql);15 try{16 //Setup the list of columns

17 int columnCount =mQuery.columnCountLocked();18 mColumns = newString[columnCount];19

20 //Read in all column names

21 for (int i = 0; i < columnCount; i++) {22 String columnName =mQuery.columnNameLocked(i);23 mColumns[i] =columnName;24 if (false) {25 Log.v("DatabaseWindow", "mColumns[" + i + "] is "

26 +mColumns[i]);27 }28

29 //Make note of the row ID column index for quick access to it

30 if ("_id".equals(columnName)) {31 mRowIdColumnIndex =i;32 }33 }34 } finally{35 query.mDatabase.unlock();36 }37 }

所以看到这里我们就能确定的是rawquery这个方法 返回的cursor实际上就是一个对象，并没有任何真正调用sql的地方。

然后我们来看看我们怀疑的moveToNext这个方法因为从日志上看耗时的地方在第一次调用他的时候，所以我们怀疑真正调用查询sql的地方

在这个函数里面被触发。

1 public final booleanmoveToNext() {2 return moveToPosition(mPos + 1);3 }

1 public final boolean moveToPosition(intposition) {2 //Make sure position isn't past the end of the cursor

3 final int count =getCount();4 if (position >=count) {5 mPos =count;6 return false;7 }8

9 //Make sure position isn't before the beginning of the cursor

10 if (position < 0) {11 mPos = -1;12 return false;13 }14

15 //Check for no-op moves, and skip the rest of the work for them

16 if (position ==mPos) {17 return true;18 }19

20 boolean result =onMove(mPos, position);21 if (result == false) {22 mPos = -1;23 } else{24 mPos =position;25 if (mRowIdColumnIndex != -1) {26 mCurrentRowID =Long.valueOf(getLong(mRowIdColumnIndex));27 }28 }29

30 returnresult;31 }

看一下那个getcount方法

1 @Override2 public intgetCount() {3 if (mCount ==NO_COUNT) {4 fillWindow(0);5 }6 returnmCount;7 }8

9 private void fillWindow(intstartPos) {10 clearOrCreateLocalWindow(getDatabase().getPath());11 mWindow.setStartPosition(startPos);12 int count =getQuery().fillWindow(mWindow);13 if (startPos == 0) { //fillWindow returns count(*) only for startPos = 0

14 if(Log.isLoggable(TAG, Log.DEBUG)) {15 Log.d(TAG, "received count(*) from native_fill_window: " +count);16 }17 mCount =count;18 } else if (mCount <= 0) {19 throw new IllegalStateException("Row count should never be zero or negative "

20 + "when the start position is non-zero");21 }22 }

发现如果满足某个条件的话 就调用fillwindow这个方法，我们来看看是什么条件

1 /**The number of rows in the cursor*/

2 private volatile int mCount =NO_COUNT;3 static final int NO_COUNT = -1;

看到这就明白了，如果你默认的mCount为-1的话就代表你这个cursor里面还没有查过吗，所以必须要调用fillwindow方法

1 private synchronizedSQLiteQuery getQuery() {2 returnmQuery;3 }

我们来看看这个query是什么

1 /**The query object for the cursor*/

2 private SQLiteQuery mQuery;

看看他的fillwindow方法

1 /**

2 * Reads rows into a buffer. This method acquires the database lock.3 *4 *@paramwindow The window to fill into5 *@returnnumber of total rows in the query6 */

7 /*package*/ intfillWindow(CursorWindow window) {8 mDatabase.lock(mSql);9 long timeStart =SystemClock.uptimeMillis();10 try{11 acquireReference();12 try{13 window.acquireReference();14 int startPos =window.getStartPosition();15 int numRows =nativeFillWindow(nHandle, nStatement, window.mWindowPtr,16 startPos, mOffsetIndex);17 if(SQLiteDebug.DEBUG_LOG_SLOW_QUERIES) {18 long elapsed = SystemClock.uptimeMillis() -timeStart;19 if(SQLiteDebug.shouldLogSlowQuery(elapsed)) {20 Log.d(TAG, "fillWindow took " +elapsed21 + " ms: window=\"" +window22 + "\", startPos=" +startPos23 + ", offset=" +mOffsetIndex24 + ", filledRows=" +window.getNumRows()25 + ", countedRows=" +numRows26 + ", query=\"" + mSql + "\""

27 + ", args=[" + (mBindArgs != null ?

28 TextUtils.join(", ", mBindArgs.values()) : "")29 + "]");30 }31 }32 mDatabase.logTimeStat(mSql, timeStart);33 returnnumRows;34 } catch(IllegalStateException e){35 //simply ignore it

36 return 0;37 } catch(SQLiteDatabaseCorruptException e) {38 mDatabase.onCorruption();39 throwe;40 } catch(SQLiteException e) {41 Log.e(TAG, "exception: " + e.getMessage() + "; query: " +mSql);42 throwe;43 } finally{44 window.releaseReference();45 }46 } finally{47 releaseReference();48 mDatabase.unlock();49 }50 }

一目了然，其实就是rawquery返回的是一个没有意义的cursor对象里面什么都没有，当你调用movetonext之类的方法的时候，

会判断是否里面没有数据 如果有数据就返回你要的数据，如果没有的话，实际上最终调用的就是SQLiteQuery这个类的fillwindow方法

来最终执行你写的sql语句~~耗时也就是在这里耗时！！！！！切记！不是在rawquery里耗时的！