android mysql sqlite_Android 创建SQLite数据库（一）

Android内置了轻量级的数据库SQLite，这里将自己理解作个记录，方便自己复习。

一.首先，创建SQLite数据库比较常见的方式是通过Android提供的SQLiteOpenHelper来实现，先贴一段代码：

importandroid.Manifest;importandroid.content.Context;importandroid.content.pm.PackageManager;importandroid.database.sqlite.SQLiteDatabase;importandroid.database.sqlite.SQLiteOpenHelper;importandroid.support.v7.app.AppCompatActivity;importandroid.os.Bundle;importandroid.util.Log;importandroid.view.View;importandroid.widget.Button;

importandroid.database.sqlite.SQLiteDatabase.CursorFactory;importjava.io.File;public class MainActivity extends AppCompatActivity implementsView.OnClickListener{private static final String TAG = "CustomSQLiteOpenHelper";privateButton mButton;privateCustomSQLiteOpenHelper customSQLiteOpenHelper;

@Overrideprotected voidonCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

mButton=(Button)findViewById(R.id.btn1);

mButton.setOnClickListener(this);customSQLiteOpenHelper= new CustomSQLiteOpenHelper(this);//创建SQLIteOpenHelper对象(1)

}

@Overridepublic voidonClick(View v) {switch(v.getId()) {caseR.id.btn1:

customSQLiteOpenHelper.getWritableDatabase();//通过getWritableDatabase()方式来新建SQLite数据库(2)break;default:break;

}

}class CustomSQLiteOpenHelper extendsSQLiteOpenHelper {private static final String DATABASE_NAME = "book_store.db";//数据库名字private static final int DATABASE_VERSION = 1;//数据库版本号private static final String CREATE_TABLE = "create table bookStore ("

+ "id integer primary key autoincrement,"

+ "book_name text, "

+ "author text, "

+ "price real)";//数据库里的表publicCustomSQLiteOpenHelper(Context context) {this(context, DATABASE_NAME, null, DATABASE_VERSION);

}private CustomSQLiteOpenHelper(Context context, String name, CursorFactory factory, intversion) {super(context, name, factory, version);//调用到SQLiteOpenHelper中

Log.d(TAG,"New CustomSQLiteOpenHelper");

}

@Overridepublic voidonCreate(SQLiteDatabase db) {

Log.d(TAG,"onCreate");

db.execSQL(CREATE_TABLE);

}

@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, intnewVersion) {

}

}

}

在MainActivity布局文件activity_main.xml里，只有一个Button控件，点击该Button，就会通过调用SQLiteOpenHelper.getWritableDatabase()的方式来创建名为DATABASE_NAME的数据库。数据库是否新建成功，可以在/data/data//databse/目录下确认。

用于创建SQLite数据库比较重要的代码：(1)建立SQLiteOpenHelper对象；(2)调用getWritableDatabase()来建立SQLite数据库。下面来看看这两句代码主要做了什么。

二.代码分析

(1)建立SQLiteOpenHelper对象

/*** Create a helper object to create, open, and/or manage a database.

* The database is not actually created or opened until one of

* {@link#getWritableDatabase} or {@link#getReadableDatabase} is called.*/

public SQLiteOpenHelper(Context context, String name, CursorFactory factory, intversion,

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

mContext=context;

mName=name;

mFactory=factory;

mNewVersion=version;

mErrorHandler=errorHandler;

}

从注释中就可以看出，该构造函数只是建立SQLiteOpenHelper对象，以及进行了一些变量的初始化动作，所以正真创建SQLite数据库地方不是在这里，而是在getWritableDatabase() 或者 getReadableDatabase()中实现的。接下来看这两个函数是如何创建SQLite数据库的。

(2)调用getWritableDatabase()函数

/*** Create and/or open a database that will be used for reading and writing.

* The first time this is called, the database will be opened and

* {@link#onCreate}, {@link#onUpgrade} and/or {@link#onOpen} will be

* called.

*

*

Once opened successfully, the database is cached, so you can

* call this method every time you need to write to the database.

* (Make sure to call {@link#close} when you no longer need the database.)

* Errors such as bad permissions or a full disk may cause this method

* to fail, but future attempts may succeed if the problem is fixed.

*

*

Database upgrade may take a long time, you

* should not call this method from the application main thread, including

* from {@linkandroid.content.ContentProvider#onCreate ContentProvider.onCreate()}.

**/

publicSQLiteDatabase getWritableDatabase() {synchronized (this) {return getDatabaseLocked(true);

}

}/*** Create and/or open a database. This will be the same object returned by

* {@link#getWritableDatabase} unless some problem, such as a full disk,

* requires the database to be opened read-only. In that case, a read-only

* database object will be returned. If the problem is fixed, a future call

* to {@link#getWritableDatabase} may succeed, in which case the read-only

* database object will be closed and the read/write object will be returned

* in the future.

*

*

Like {@link#getWritableDatabase}, this method may

* take a long time to return, so you should not call it from the

* application main thread, including from

* {@linkandroid.content.ContentProvider#onCreate ContentProvider.onCreate()}.

**/

publicSQLiteDatabase getReadableDatabase() {synchronized (this) {return getDatabaseLocked(false);

}

}

getWritableDatabase() & getReadableDatabase()函数中都是调用getDatabaseLocked()函数，仅仅是传的参数不同，这两个函数的功能基本上是相同的。注释中也举例说明了当磁盘满了是两者间的区别。下面接着看getDatabaseLocked()中的内容。

private SQLiteDatabase getDatabaseLocked(booleanwritable) {if (mDatabase != null) {//第一次进来时，mDatabase为空if (!mDatabase.isOpen()) {//Darn! The user closed the database by calling mDatabase.close().

mDatabase = null;//当mDatabase被close后，再次打开时需要重新创建

}else if (!writable || !mDatabase.isReadOnly()) {//The database is already open for business.

returnmDatabase;//直接返回当前mDatabase

}

}if(mIsInitializing) {throw new IllegalStateException("getDatabase called recursively");

}

SQLiteDatabase db=mDatabase;try{

mIsInitializing= true;if (db != null) {if (writable &&db.isReadOnly()) {

//数据库从ReadOnly变为ReadWrite时调用

db.reopenReadWrite();

}

}else if (mName == null) {

db= SQLiteDatabase.create(null);

}else{try{

//DEBUG_STRICT_READONLY 默认为FALSE，因为实际应用过程中，只读数据库不实用，自己Debug可以设置为TRUE测试。if (DEBUG_STRICT_READONLY && !writable) {final String path =mContext.getDatabasePath(mName).getPath();

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

}

Log.e(TAG,"Couldn't open " +mName+ " for writing (will try read-only):", ex);final String path =mContext.getDatabasePath(mName).getPath();

db=SQLiteDatabase.openDatabase(path, mFactory,

SQLiteDatabase.OPEN_READONLY, mErrorHandler);

}

}

onConfigure(db);

//下面数据库版本信息发生变化时调用final int version =db.getVersion();//第一次创建，version值为0if (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) {

//版本降低时调用，但是默认不支持该操作，会报“SQLiteException：Can't downgrade database from version”

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

}finally{

mIsInitializing= false;if (db != null && db !=mDatabase) {

db.close();

}

}

}

其中DEBUG_STRICT_READONLY变量默认为False，所以正常建立的数据库都是Readable & Writable。所以上面新建数据库代码主要可以分为三个部分：

1.如果mDataBase不为空，并且处于打开状态时，直接返回，所以多次调用getWritableDatabase()或getReadableDatabase()和只调用一次的效果是一样的。

2.如果mDataBase为空，则调用mContext.openOrCreateDatabase()来创建数据库。

3.当数据库版本信息发生变化时，做相应的升/降版本处理。

那么mContext.openOrCreateDatabase()函数里又做了哪些事情呢？进入到ContextImpl.java文件中一探究竟。

@Overridepublic SQLiteDatabase openOrCreateDatabase(String name, intmode, CursorFactory factory,

DatabaseErrorHandler errorHandler) {

File f= validateFilePath(name, true);//判断数据库name是否有效int flags =SQLiteDatabase.CREATE_IF_NECESSARY;if ((mode & MODE_ENABLE_WRITE_AHEAD_LOGGING) != 0) {

flags|=SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING;

}

//openDatabase

SQLiteDatabase db=SQLiteDatabase.openDatabase(f.getPath(), factory, flags, errorHandler);

setFilePermissionsFromMode(f.getPath(), mode,0);//设置文件读写权限Modereturndb;

}

private File validateFilePath(String name, boolean createDirectory) {

File dir;

File f;

if (name.charAt(0) == File.separatorChar) {

//可以自己定义数据库所在目录，但是要注意目录权限，比如以User身份往System所属的目录里添加文件

String dirPath = name.substring(0, name.lastIndexOf(File.separatorChar));

dir = new File(dirPath);

name = name.substring(name.lastIndexOf(File.separatorChar));

f = new File(dir, name);

} else {

dir = getDatabasesDir();

f = makeFilename(dir, name);

}

if (createDirectory && !dir.isDirectory() && dir.mkdir()) {

FileUtils.setPermissions(dir.getPath(),

FileUtils.S_IRWXU|FileUtils.S_IRWXG|FileUtils.S_IXOTH,

-1, -1);

}

return f;

}

private File getDatabasesDir() {

synchronized (mSync) {

if (mDatabasesDir == null) {

//这个得到的结果就是/data/data//database

mDatabasesDir = new File(getDataDirFile(), "databases");

}

if (mDatabasesDir.getPath().equals("databases")) {

mDatabasesDir = new File("/data/system");

}

return mDatabasesDir;

}

}

@SuppressWarnings("deprecation")

static void setFilePermissionsFromMode(String name, int mode,

int extraPermissions) {

int perms = FileUtils.S_IRUSR|FileUtils.S_IWUSR

|FileUtils.S_IRGRP|FileUtils.S_IWGRP

|extraPermissions;//默认权限为-rx-rx---

if ((mode&MODE_WORLD_READABLE) != 0) {

perms |= FileUtils.S_IROTH;

}

if ((mode&MODE_WORLD_WRITEABLE) != 0) {

perms |= FileUtils.S_IWOTH;

}

if (DEBUG) {

Log.i(TAG, "File " + name + ": mode=0x" + Integer.toHexString(mode)

+ ", perms=0x" + Integer.toHexString(perms));

}

FileUtils.setPermissions(name, perms, -1, -1);

}

从上面代码可以知道为何新建的数据库的目录为/data/data//database/，并且权限为-rx-rx--，再贴下之前新建数据库得到的结果：

其中SQLiteDatabase db =SQLiteDatabase.openDatabase(f.getPath(), factory, flags, errorHandler);应该是正真建立数据库的地方。

public static SQLiteDatabase openDatabase(String path, CursorFactory factory, intflags,

DatabaseErrorHandler errorHandler) {

SQLiteDatabase db= newSQLiteDatabase(path, flags, factory, errorHandler);

db.open();returndb;

}

这样最终就可以得到SQLite数据库了。