效果:
清单文件:
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.sg31.sqlitedatabase"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="21" />
// 注意位置 和 targetPackage!!!!!!!
<instrumentation
android:name="android.test.InstrumentationTestRunner"
android:targetPackage="com.sg31.sqlitedatabase"
></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"/>
<activity
android:name=".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>
数据库帮助类:
package com.sg31.sqlitedatabase;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class SGDBHelper extends SQLiteOpenHelper {
public SGDBHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
// 数据库创建时,建表
System.out.println("数据库创建时调用");
db.execSQL("create table user(_id integer primary key autoincrement, name char(10), pwd char(10), phone integer(20))");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
System.out.println("数据库升级时调用");
}
}
测试代码:
package com.sg31.sqlitedatabase;
import android.R.bool;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;
public class sqlDatabaseTest extends AndroidTestCase {
private SGDBHelper dbHelper;
private SQLiteDatabase db;
boolean flag = false;
@Override
protected void setUp() throws Exception {
super.setUp();
// 初始化
// 建立数据库引用
dbHelper = new SGDBHelper(getContext(), "user.sqlite", null, 1);
db = dbHelper.getWritableDatabase();
}
@Override
protected void tearDown() throws Exception {
super.tearDown();
// 收尾工作
db.close();
}
//测试insert
public void testInsert() {
if (flag == false) {
return;
}
System.out.println("testInsert");
for (int i = 0; i < 5; i++) {
System.out.println("sg__i:"+i);
String sql = "insert into user (name, pwd, phone)values(?, ?, ?)";
Object[] bindArgs = new Object[]{"beyond"+i, 123456+i, "13876"+i};
db.execSQL(sql, bindArgs);
}
}
// 测试delete
public void testDelete(){
if (flag == false) {
return;
}
System.out.println("testDelete");
String sql = "delete from user where name = ?";
Object[] bindArgs = new Object[]{"beyond"};
db.execSQL(sql, bindArgs);
}
public void testDeleteAll(){
if (flag == false) {
return;
}
System.out.println("testDeleteAll");
String sql = "delete from user";
db.execSQL(sql);
}
// 测试update
public void testUpdate(){
if (flag == false) {
return;
}
System.out.println("testUpdate");
String sql = "update user set pwd = ? where name = ?";
Object[] bindArgs = new Object[]{"sg31.com","beyond3"};
db.execSQL(sql, bindArgs);
}
// 测试查询
public void testSelect(){
if (flag == false) {
return;
}
System.out.println("testSelect");
System.out.println("sg__begin");
Cursor cursor = db.rawQuery("select name,pwd,phone from user", null);
while(cursor.moveToNext()){
//通过列索引获取列的值
String name = cursor.getString(cursor.getColumnIndex("name"));
String pwd = cursor.getString(cursor.getColumnIndex("pwd"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
System.out.println(name + ";" +pwd + ";" +phone + ";");
}
System.out.println("sg__over");
}
// 使用封装好的API进行CRUD
public void testInsertApi(){
if (flag == false) {
return;
}
System.out.println("testInsertApi");
//把要插入的数据全部封装至ContentValues对象
ContentValues values = new ContentValues();
values.put("name", "shuiying");
values.put("pwd", "sg31.com");
values.put("phone", 1346943);
db.insert("user", null, values);
}
// 使用封装好的API进行CRUD
public void testDeleteApi(){
if (flag == false) {
return;
}
System.out.println("testDeleteApi");
int i = db.delete("user", "name = ?", new String[]{"shuiying"});
System.out.println(i);
}
// 使用封装好的API进行CRUD
public void testUpdateApi(){
if (flag == false) {
return;
}
System.out.println("testUpdateApi");
ContentValues values = new ContentValues();
values.put("pwd", 654321);
int i = db.update("user", values, "name = ?", new String[]{"shuiying"});
System.out.println(i);
}
// 使用封装好的API进行CRUD
public void testSelectApi(){
if (flag == false) {
return;
}
System.out.println("testSelectApi");
Cursor cursor = db.query("user", null, null, null, null, null, null, null);
while(cursor.moveToNext()){
String name = cursor.getString(cursor.getColumnIndex("name"));
String pwd = cursor.getString(cursor.getColumnIndex("pwd"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
System.out.println(name + ";" + pwd + ";" + phone);
}
}
// 使用API进行事务提交
public void transaction(){
try{
//开启事务
db.beginTransaction();
ContentValues values = new ContentValues();
// 减100
values.put("money", 900);
db.update("user", values, "name = ?", new String[]{"甲"});
// 非常关键的一步,不然数据就重合了
values.clear();
// 加100
values.put("money", 1100);
db.update("user", values, "name = ?", new String[]{"乙"});
// 特意放置一个exception
int i = 3/0;
//重要!!!必须设置 事务执行成功
db.setTransactionSuccessful();
}
finally{
//关闭事务,同时提交,如果已经设置事务执行成功,那么sql语句就生效了,反之,sql语句回滚
db.endTransaction();
}
}
}