SQliteOpenHelper是一个抽象类,来管理数据库的创建和版本的管理。这个辅助类继承自SQLiteOpenHelper类,在该类的构造器中,调用Context中的方法创建并打开一个指定名称的数据库对象。继承和扩展SQLiteOpenHelper类主要做的工作就是重写以下两个方法。
onCreate(SQLiteDatabase db) : 当数据库被首次创建时执行该方法,一般将创建表等初始化操作在该方法中执行。
onUpgrade(SQLiteDatabse dv, int oldVersion,int new Version):当打开数据库时传入的版本号与当前的版本号不同时会调用该方法。
SQLiteOpenHelper 类的基本用法是:当需要创建或打开一个数据库并获得数据库对象时,首先根据指定的文件名创建一个辅助对象,然后调用该对象的getWritableDatabase 或 getReadableDatabase方法 获得SQLiteDatabase 对象。
android可以不需要启动整个系统,单独执行某一个方法,在方法的右边,如test()选择右键test-android run junit test执行就可以单独测试某一个方法了(虚拟机需要打开)。
首先需要在清单文件Manifist中配置一下才行,配置的内容和方法如下
配置AndroidManifist.xml文件
<instrumentation
android:name="android.test.InstrumentationTestRunner"
android:targetPackage="com.ldw.sqlite"
></instrumentation>
<uses-library android:name="android.test.runner"></uses-library>
清单文件
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.ldw.sqlite"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="17" />
<instrumentation
android:name="android.test.InstrumentationTestRunner"
android:targetPackage="com.ldw.sqlite"
></instrumentation>
<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/AppTheme" >
<uses-library android:name="android.test.runner"></uses-library>"
<activity
android:name="com.ldw.sqlite.MainActivity"
android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>
MainActivity.java这里什么都没有做,都是再test case中运行的
package com.ldw.sqlite;
import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;
public class MainActivity extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
}
SQLiteOpenHelper.java数据库的接口
package com.ldw.sqlite;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class MyOpenHelper extends SQLiteOpenHelper {
public MyOpenHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}
//数据库创建时调用
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table person(_id integer primary key autoincrement, name char(10), salary char(20), phone integer(20))");
System.out.println("创建数据库");
}
//数据库升级时调用
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
System.out.println("数据库升级了");
}
}
TestCase.java测试数据库的增删改查
package com.ldw.sqlite.test;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;
import com.ldw.sqlite.MyOpenHelper;
public class TestCase extends AndroidTestCase{
private MyOpenHelper oh;
private SQLiteDatabase db;
public void test(){
//getContext()是获取虚拟的上下文
oh = new MyOpenHelper(getContext(), "people.db", null, 1);
//获取可写可读数据库对象,如果数据库存在,直接打开,如果数据库不存在,先创建
//数据库再获取科协的数据库对象
db = oh.getWritableDatabase();
}
//测试框架初始化完毕后,在测试方法执行之前,此方法调用
@Override
protected void setUp() throws Exception{
super.setUp();
oh = new MyOpenHelper(getContext(), "people.db", null, 1);
db = oh.getWritableDatabase();
}
//测试方法执行完毕以后此方法调用
@Override
protected void tearDown() throws Exception{
super.tearDown();
db.close();
}
public void insert(){
db.execSQL("insert into person (name, salary, phone)values(?, ?, ?)",new Object[]{"fhgfhf", "14000", 1302549110});
db.execSQL("insert into person (name, salary, phone)values(?, ?, ?)",new Object[]{"ghfhfh", "14000", 1302549110});
db.execSQL("insert into person (name, salary, phone)values(?, ?, ?)",new Object[]{"74gfhfhhfhf9", "14000", 1302549110});
}
public void delete(){
db.execSQL("delete from person where name = ?",new Object[]{"aaaaa"});
}
public void update(){
db.execSQL("update person set phone = ? where name = ?", new Object[]{321312, "aaaaa"});
}
public void select(){
Cursor cursor = db.rawQuery("select name, salary from person ", null);
while(cursor.moveToNext()){
//通过列索引获取到列的值
String name = cursor.getString(cursor.getColumnIndex("name"));
String salary = cursor.getString(1);
System.out.println("name=" + name + "salary" + salary);
}
}
///API方法实现增删该查
public void insertApi(){
//把要插入的数据全部封装到ContentValues对象
ContentValues values = new ContentValues();
values.put("name", "xxx");
values.put("phone", "234324");
values.put("salary",13423);
db.insert("person", null, values);
}
public void deleteApi(){
//返回删除的行数
int i = db.delete("person", "name = ? and _id = ?", new String[]{"aaaaa" , "4"});
}
public void updateApi(){
ContentValues values = new ContentValues();
values.put("salary", 17000);
//返回修改的行数
int i = db.update("person", values, "name = ?", new String[]{"xxx"});
System.out.println("i"+i);
}
public void selectApi(){
Cursor cursor = db.query("person", null, null, null, null, null, null, null);
while(cursor.moveToNext()){
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
String salary = cursor.getString(cursor.getColumnIndex("salary"));
System.out.println("name = " + name + "phone = " + phone + "salary = " + salary);
}
}
public void transation(){
try{
//开启事务
db.beginTransaction();
ContentValues values = new ContentValues();
values.put("salary", 40000);
db.update("person", values, "name = ?", new String[]{"luodewu"});
//上下都使用一个values防止数据干扰
values.clear();
values.put("salary", 20000);
db.update("person", values, "name = ?", new String[]{"luodewuluodewu"});
//设置事物执行成功
db.setTransactionSuccessful();
}
finally{
//关闭事物,同时提交,如果已经设置事务执行成功,sql语句就生效了,反之语句回滚
db.endTransaction();
}
}
}
activity_main.xml布局文件。因为是测试case,这个文件没有什么用
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context=".MainActivity" >
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/hello_world" />
</RelativeLayout>