SQLiteOpenHelper 使用方法

public abstract class SQLiteOpenHelper extends Object


Class Overview
A helper class to manage database creation and version management.
You create a subclass implementing onCreate(SQLiteDatabase), onUpgrade(SQLiteDatabase, int, int) and optionally onOpen(SQLiteDatabase), and this class takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary. Transactions are used to make sure the database is always in a sensible state.
This class makes it easy for ContentProvider implementations to defer opening and upgrading the database until first use, to avoid blocking application startup with long-running database upgrades.
For an example, see the NotePadProvider class in the NotePad sample application, in the samples/ directory of the SDK.

Note: this class assumes monotonically increasing version numbers for upgrades.

getReadableDatabase()
getWritableDatabase()
onCreate(SQLiteDatabase db)
onOpen(SQLiteDatabase db)
onUpgrade(SQLiteDatabase db , int oldVersion, int newVersion)
close()

主要函数有:


SQLiteDatabase getReadableDatabase()
Create and/or open a database.


SQLiteDatabase getWritableDatabase()
Create and/or open a database that will be used for reading and writing.


这两个函数返回的都是SQLiteDatabase对象
SQLiteDatabase  --- Exposes methods to manage a SQLite database.
SQLiteDatabase 对象用来实际对数据库增删改查


synchronized void close()
Close any open database object.


回调函数

void onConfigure(SQLiteDatabase db)
Called when the database connection is being configured, to enable features such as write-ahead logging or foreign key support.


abstract void onCreate(SQLiteDatabase db)
Called when the database is created for the first time.


void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)
Called when the database needs to be downgraded.


void onOpen(SQLiteDatabase db)
Called when the database has been opened.


abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
Called when the database needs to be upgraded.


void setWriteAheadLoggingEnabled(boolean enabled)
Enables or disables the use of write-ahead logging for the database.

关于version字段 


Note: this class assumes monotonically increasing version numbers for upgrades.


public abstract void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)
Called when the database needs to be upgraded. 

The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.


public void onDowngrade (SQLiteDatabase db, int oldVersion, int newVersion)
Called when the database needs to be downgraded. This is strictly similar to onUpgrade(SQLiteDatabase, int, int) method,
but is called whenever current version is newer than requested one. However, this method is not abstract, so it is not mandatory for a customer to implement it. 
If not overridden, default implementation will reject downgrade and throws SQLiteException
This method executes within a transaction. If an exception is thrown, all changes will automatically be rolled back.


version 是单调递增的


当数据库需要更新操作时调用onUpgrade函数, 只要是更改了数据库结构, 比如删除表, 添加表, 或其他任何更新数据库scheme的操作系统都会自动调用调用这个函数.   


可以进控制台adb shell ,  进入到程序目录, 用sqlite3 databaseName 来查看数据库, .schema 命令可以查看数据库结构.


onDowngrade当需要数据库降级操作的时候调用这个函数, 比如请求的数据库版本比实际的数据库版本要低的话, 就会调用这个onDowngrade, 如果没有重写这个函数则默认会抛出异常.
Downgrade操作是在数据库处理的过程中调用, 如果发生异常,会自动撤销对数据库的更改


加入这个version可能是想实现rollback的操作??  但是具体没有试验过, 但这两个函数即使都不进行什么操作,并不会有什么问题...


onUpgrade 和 onDowngrade 必须复写不然会抛出异常

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	// TODO Auto-generated method stub
	System.out.println("update a Database");
}
/* (non-Javadoc)
 * @see android.database.sqlite.SQLiteOpenHelper#onDowngrade(android.database.sqlite.SQLiteDatabase, int, int)
 */
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	// TODO Auto-generated method stub
	System.out.println("downGrade a Database");
	//super.onDowngrade(db, oldVersion, newVersion);
}

创建一个DatabaseHelper对象


Android系统会判断如果数据库已经存在则不会创建, 否则会调用 DatabaseHelper extends SQLiteOpenHelper  这个类里面的回调函数onCreate来创建数据库
这样挺方便的, 不需要写代码去判断数据库是否存在,是否需要创建,  只需要直接说需要访问哪个数据库, 第一次访问时,会自动去调用回调函数, 只要在回调函数里写上创建表的操作即可
以后每次访问就可以直接去进行表的操作了.


DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this,"test_mars_db");

//只有调用了DatabaseHelper对象的getReadableDatabase()方法，或者是getWritableDatabase()方法之后，才会创建，或打开一个数据库

SQLiteDatabase db = dbHelper.getReadableDatabase();

	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		System.out.println("create a Database");
		//execSQL函数用于执行SQL语句
		db.execSQL("create table user(id int,name varchar(20))");
	}

Activity 里面 插入, 更新, 查询操作:

<strong>  </strong>  class InsertListener implements OnClickListener{

		@Override
		public void onClick(View v) {
			//生成ContentValues对象
			ContentValues values = new ContentValues();
			//想该对象当中插入键值对，其中键是列名，值是希望插入到这一列的值，值必须和数据库当中的数据类型一致
			values.put("id", 1);
			values.put("name","zhangsan");
			DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this,"test_mars_db",2);
			SQLiteDatabase db = dbHelper.getWritableDatabase();
			//调用insert方法，就可以将数据插入到数据库当中
			db.insert("user", null, values);
		}
    }
    //更新操作就相当于执行SQL语句当中的update语句
    //UPDATE table_name SET XXCOL=XXX WHERE XXCOL=XX...
    class UpdateRecordListener implements OnClickListener{

		@Override
		public void onClick(View arg0) {
			// TODO Auto-generated method stub
			//得到一个可写的SQLiteDatabase对象
			DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this,"test_mars_db");
			SQLiteDatabase db = dbHelper.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put("name", "zhangsanfeng");
			//第一个参数是要更新的表名
			//第二个参数是一个ContentValeus对象
			//第三个参数是where子句
			db.update("user", values, "id=?", new String[]{"1"});
		}
    }
    class QueryListener implements OnClickListener{

		@Override
		public void onClick(View v) {
			System.out.println("aaa------------------");
			Log.d("myDebug", "myFirstDebugMsg");
			
			DatabaseHelper dbHelper = new DatabaseHelper(SQLiteActivity.this,"test_mars_db");
			SQLiteDatabase db = dbHelper.getReadableDatabase();
			Cursor cursor = db.query("user", new String[]{"id","name"}, "id=?", new String[]{"1"}, null, null, null);
			while(cursor.moveToNext()){
				String name = cursor.getString(cursor.getColumnIndex("name"));
				System.out.println("query--->" + name);
			}
		}
    }