android sql前10条,android SQL数据库操作心得

最近由嵌入式转到Android开发，在学习Android SQL数据库的使用时遇到了一些问题，并分析Android源码明白了其中的原因，想记录下来与大家分享。

在《第一行代码》第六章：SQLite数据库存储，在没有删除数据库或者卸载程序的情况下，连续创建2次相同的数据库，只会执行一次onCreate()。但是只要在创建SQLiteOpenHelper对象实例时修改一下本版号就会执行onUpgrade()方法。下面我们将从源代码的角度分析为什么会这样。

书上的源代码：

`

public MyDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {

super(context, name, factory, version);

mContext = context;

}

`

其中MyDatabaseHelper继承于SQLiteOpenHelpe

public class MyDatabaseHelper extends SQLiteOpenHelpe

首先，我们看一下在创建SQLiteOpenHelper对象实例时会发生什么。

`

public SQLiteOpenHelper(@Nullable Context context, @Nullable String name,

@Nullable CursorFactory factory, int version) {

this(context, name, factory, version, null);

}

`

继续深究下去，下面就是不断的跟踪下去执行的代码：

`

public SQLiteOpenHelper(@Nullable Context context, @Nullable String name,

@Nullable CursorFactory factory, int version,

@Nullable DatabaseErrorHandler errorHandler) {

this(context, name, factory, version, 0, errorHandler);

}

public SQLiteOpenHelper(@Nullable Context context, @Nullable String name,

@Nullable CursorFactory factory, int version,

int minimumSupportedVersion, @Nullable DatabaseErrorHandler errorHandler) {

this(context, name, version, minimumSupportedVersion,

new SQLiteDatabase.OpenParams.Builder());

mOpenParamsBuilder.setCursorFactory(factory);

mOpenParamsBuilder.setErrorHandler(errorHandler);

}

private SQLiteOpenHelper(@Nullable Context context, @Nullable String name, int version,

int minimumSupportedVersion,

@NonNull SQLiteDatabase.OpenParams.Builder openParamsBuilder) {

Objects.requireNonNull(openParamsBuilder);

if (version < 1) throw new IllegalArgumentException("Version must be >= 1, was " + version);

mContext = context;

mName = name;

mNewVersion = version;

mMinimumSupportedVersion = Math.max(0, minimumSupportedVersion);

setOpenParamsBuilder(openParamsBuilder);

}

`

从上面就可以看出，在执行SQLiteOpenHelper构造方法时还没有创建数据库，只是保存一些参数值而已。

而创建和打开数据库主要通过2个方法：getWritableDatabase()和getWritableDatabase()，那么我们跟踪其中一个方法，以getWritableDatabase()为例。

`

public SQLiteDatabase getWritableDatabase() {

synchronized (this) {

return getDatabaseLocked(true);

}

}

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.createInMemory(mOpenParamsBuilder.build());

} else {

final File filePath = mContext.getDatabasePath(mName);

SQLiteDatabase.OpenParams params = mOpenParamsBuilder.build();

try {

db = SQLiteDatabase.openDatabase(filePath, params);

// Keep pre-O-MR1 behavior by resetting file permissions to 660

setFilePermissionsForDb(filePath.getPath());

} catch (SQLException ex) {

if (writable) {

throw ex;

}

Log.e(TAG, "Couldn't open " + mName

+ " for writing (will try read-only):", ex);

params = params.toBuilder().addOpenFlags(SQLiteDatabase.OPEN_READONLY).build();

db = SQLiteDatabase.openDatabase(filePath, params);

}

}

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);

}

if (version > 0 && version < mMinimumSupportedVersion) {

File databaseFile = new File(db.getPath());

onBeforeDelete(db);

db.close();

if (SQLiteDatabase.deleteDatabase(databaseFile)) {

mIsInitializing = false;

return getDatabaseLocked(writable);

} else {

throw new IllegalStateException("Unable to delete obsolete database "

+ mName + " with version " + version);

}

} else {

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();

}

}

}

`

代码比较长，我们只截取其中代码片段分析，可以参考上面的完整代码，好理解我讲的是什么。

`

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;

}

}

`

从这里我们可以看出，如果之前已经创建了数据库对象实例mDatabase且权限是可读可写则之间返回之前已经创建号的对象实例。

从之前跟踪SQLiteOpenHelper构造方法时我们就知道，version和mMinimumSupportedVersion已经被设置为0。那么根据以下代码片段：

`

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);

}

** if (version > 0 && version < mMinimumSupportedVersion) {File databaseFile = new File(db.getPath());

onBeforeDelete(db);

db.close();

if (SQLiteDatabase.deleteDatabase(databaseFile)) {

mIsInitializing = false;

return getDatabaseLocked(writable);

} else {

throw new IllegalStateException("Unable to delete obsolete database "

+ mName + " with version " + version);

}

} else {

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();

}

}**

}`

**此时version == 0条件成立，则说明在第一次创建SQLiteOpenHelper对象实例时会执行一次onCreate()方法，当下次不改变版本号时再次调用getWritableDatabase()方法时，则不会执行onCreate()了。**当下次改变版本号时再次调用getWritableDatabase()方法，此时version不再为0，且当新版本号增大时则会调用：onUpgrade()方法。否则调用onDowngrade()方法。

总结，在多次构造SQLiteOpenHelper对象实例时，如果版本号不变则只会执行一次onCreate()方法，当将新版本号增大后，则会调用onUpgrade()方法，否则调用onDowngrade()方法。