创建sqlite数据库

实现SQLiteOpenHelper 创建数据库

---

package com.example.test;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Created by joy on 2015/12/26.
 */
public class MyDBHelper extends SQLiteOpenHelper {
    private final static String DB_NAME="person.db";
    public MyDBHelper(Context context) {
        //第一个参数上下文，第二个参数数据库名，第三个参数游标工厂，第四个参数数据库版本号
        //数据库文件保存在data/data/包名/databases/person.db
        super(context, DB_NAME, null, 2);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //数据库创建时调用
        db.execSQL("create table person(_id integer primary key autoincrement,name varchar(20),age varchar(2))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //数据库升级时调用
        Log.e("gjj","数据库更新了");
    }
}

---

单元测试中测试数据库

package com.example.test;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.test.InstrumentationTestCase;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Created by joy on 2015/12/26.
 */
public class DBTest extends InstrumentationTestCase {
    MyDBHelper helper;
    SQLiteDatabase db;


    //单元测试中设置全局变量 setup对应teardown
    @Override
    protected void setUp() throws Exception {
        super.setUp();
        helper = new MyDBHelper(getInstrumentation().getTargetContext());
        db = helper.getWritableDatabase();

    }

    @Override
    protected void tearDown() throws Exception {
        super.tearDown();
        db.close();
    }


    /**
     * 插入数据
     */
    public void testInsert() {
        for (int i = 0; i < 50; i++) {
            db.execSQL("insert into person values(null,?,?)", new Object[]{"顾建健", i + ""});
        }

    }

    public void testInsert2() {
        ContentValues values=new ContentValues();
        values.put("name","我是顾建健");
        values.put("age","今年27岁了");
        //insert("表名","一般设置为NULL",ContentValues对象)
        //第二个参数（如果values为null，指定第二个参数table中其中的列，让系统给其赋值NULL）;
        db.insert("person",null,values);

    }


    /**
     * 更新数据
     */
    public void testUpdate() {
        db.execSQL("update person set name=? where _id=?", new Object[]{"aaa", 23});
    }

    public void testUpdate2() {
        ContentValues values=new ContentValues();
        values.put("name","我是顾建健");
        values.put("age","今年27岁了");
        db.update("person", values, "_id=?", new String[]{1 + ""});
    }


    /**
     * 删除数据
     */
    public void testDel() {
        db.execSQL("delete from person where _id=?", new Object[]{23});
    }

    public void testDel2() {
        db.delete("person", "_id=?", new String[]{25 + ""});

    }


    /**
     * 查询数据
     */
    public void testQuery() {
        List<String> list=new ArrayList<>();
        Cursor cursor = db.rawQuery("select name,age from person", null);
        while (cursor.moveToNext()) {
            String name = cursor.getString(0);//对应name
            String age = cursor.getString(1);//对应pass
           list.add("name:"+name+",age"+age);

        }
        Iterator i=list.iterator();
        while(i.hasNext()){
            System.out.println(i.next().toString());
        }

    }

    /**
     * 事务..保证多条SQL语句同时成功同时失败
     */

    public void testTrans(){
        try{
            db.beginTransaction();
            ContentValues values=new ContentValues();
            values.put("name","顾大哥");
            values.put("age",25);
            db.update("person", values, "_id=?", new String[]{2 + ""});

            values.clear();
            values.put("name", "顾大哥");
            values.put("age",29);
            db.update("person", values, "_id=?", new String[]{3 + ""});
            db.setTransactionSuccessful();
        }finally {
            db.endTransaction();
        }

    }
}