android sqlite数据库

1)sqlite是android内自带的数据库,是一款轻量级数据库,

2)继承SQLiteOpenHelper定义一个子类,定义构造方法,和onCreate,onUpgrade方法

public class MySqliteOpenHelper extends SQLiteOpenHelper {

    public MySqliteOpenHelper(Context context, String name,
            CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
    }

}

3)创建数据库

在主流程代码里,写

        MySqliteOpenHelper oh = new MySqliteOpenHelper(getContext(), "abc.db", null, 2);
        SQLiteDatabase db = oh.getWritableDatabase();

getContext()获取上下文,
"abc.db"是数据库的名称,
CursorFactory factory:是结果集合,一般输入null;
最后一个参数是版本号,要>=1

4)创建表
在onCreate里面写:

        db.execSQL("create table person ("
                + "_id integer primary key autoincrement,"//primary key主键, autoincrement为自增长
                + "name char(10),"//字段名字 类型(位数)
                + "salary char(20),"
                + "phone char(20)"
                + ")");

5)插入一条

insert into tablename (字段1,字段2,字段3)values(值1,值2,值3)

db.execSQL("insert into person ( name, salary, phone)values(?, ?, ?)",new Object[]{"tom", "130000", "1222222"} );

?是占位符号

或者用db.insert();

        ContentValues values = new ContentValues();
        values.put("name", "Dick");
        values.put("salary", "10080");
        values.put("phone", "987653");
        db.insert("person", null, values);

6)删除一条

delete from tablename where 字段=值

db.execSQL("delete from person where name=?",new Object[]{"tom"});

或者用db.delete();

db.delete("person", "name = ?", new String[]{"Dick"});

 

7)修改

update tablename set 字段1=值 where 字段2=值

db.execSQL("update person set phone=? where name=?",new Object[]{"9999888","tom2"});

where后面如果有多个条件的化,要用and连接

where 字段1=值1 and 字段2=值2

 

或者用db.update

        ContentValues values = new ContentValues();
        values.put("phone", "abcdefg");
        db.update("person", values , "name=?", new String[]{"tom1"});

 

8)查询

select 字段1,字段2 from tablename

        Cursor c = db.rawQuery("select * from person ", null);//这里和前三个不一样
        
        while(c.moveToNext()){//Cursor的初始位置为-1,moveToNext后变为0
            String name = c.getString(c.getColumnIndex("name"));//获得name的列号.
            String salary = c.getString(c.getColumnIndex("salary"));
            String phone = c.getString(c.getColumnIndex("phone"));
            System.out.println("name="+name+";salary="+salary+";phone="+phone);
        }

或者用db.query()

Cursor cursor = db.query("person", null, null, null, null, null, null, null);

 query的定义

query(String table, String[] columns,
 String selection,
String[] selectionArgs, 
String groupBy, 
String having,
String orderBy, 
String limit)//limit表示从哪条开始查询,和要查询的个数 例如  "2,5"  表示从第二条开始查,一共查询5条

http://blog.csdn.net/anladeyatou/article/details/7683679

9)事物transaction

事物是保证一组操作全部成功的;如果有一小部分失败,则事物以内的操作不会生效.

        try{
            db.beginTransaction();
    　　　　　　//事物操作代码
            db.setTransactionSuccessful();
        }
        finally{
            db.endTransaction();
        }        

10)测试环境

 

1.在AndroidManifest.xml下,添加instrumentation

 

    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="21" />
    <instrumentation 
        android:name="android.test.InstrumentationTestRunner"
        android:targetPackage="com.sqlite"
        />

 

 

2.在Androidmanifest.xml的<activity外面写

<uses-library android:name="android.test.runner"/>

3.测试类要继承AndroidTestCase

public class TestCase extends AndroidTestCase {
    @Override
    protected void setUp() throws Exception {//这是测试环境的初始化函数
        super.setUp();
        oh = new MySqliteOpenHelper(getContext(), "abc.db", null, 2);
        db = oh.getWritableDatabase();
    }
    @Override
    protected void tearDown() throws Exception {//这是测试环境的收尾函数
        // TODO Auto-generated method stub
        super.tearDown();
        db.close();
    }
}