Android中数据库的基本操作

SQLite 是内嵌的数据库,创建数据库必须要继承 SQLiteOpenHelper 类:

 

public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {

 

    /**

     *数据库的结构方法 用来定义数据库的名称 数据库查询的结果集 数据库的版本

     *@param context

     */

    public PersonSQLiteOpenHelper(Context context) {

        super(context, "person.db", null, 1);

    }

 

    /**

     *数据库第一次被创建的时候调用的方法

     * @param db 被创建的数据库

     */

    @Override

    public void onCreate(SQLiteDatabase db) {

        //初始化数据库的表结构

        db.execSQL("create table person (id integer primary key autoincrement, name varchar(20), number varchar(20)) ");

    }

 

    @Override

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

               

    }

 

}

 

 

在onCreate方法中:

    //此时只是创建数据库对象,并未真正的创建数据库

    /*PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(this);

    //此时创建数据库,还有一个方法(getReadableDatabase())

    helper.getWritableDatabase();*/

 

 

创建测试类,要继承 AndroidTestCase 类:

public class TestPersonDB extends AndroidTestCase {

   

    private void testCreateDB() throws Exception{

        //此时只是创建数据库对象,并未真正的创建数据库

        PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext());

        //此时创建数据库,还有一个方法(getReadableDatabase())

        helper.getWritableDatabase();      

    }

   

}

 

注:测试时要配置相关的权限:

<instrumentation

        android:name="android.test.InstrumentationTestRunner"

        android:targetPackage="com.lcy.myfirstdb" />

 

在application内:

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

 

如不知道权限的配置可:File->Project->Android->Android Test Project ->选择要测试的项目->Finish再在清单文件拷贝过来即可

 

为可查看数据库,可安装 sqliteexpert软件

 

接下来创建一个DAO类,用来操作数据库的增删查改工作:

 

public class PersonDao {

    private PersonSQLiteOpenHelper helper;

    //在构造方法里面完成 helper的初始化

   

    public PersonDao(Context context){

        helper = new PersonSQLiteOpenHelper(context);

    }

   

    /**

     *添加一条记录到数据库

     *@param name 姓名

     *@param number 电话

     */

    public void add(String name, String number){

        SQLiteDatabase db = helper.getWritableDatabase();

        db.execSQL("insert into person (name, number) values (?,?)", new Object[]{name, number});

        db.close();

    }

   

    /**

     *查询记录是否存在

     *@param name 姓名

     *@param true 存在  false 不存在

     */

    public boolean find(String name){

        SQLiteDatabase db = helper.getReadableDatabase();

        Cursor cursor = db.rawQuery("select * from person where name=?", new String[]{ name });

        boolean result = cursor.moveToNext();

        cursor.close();

        db.close();

        return result;

    }

   

    /**

     *修改一条记录

     *@param name 要修改的人的姓名

     *@param newnumber 新的号码

     */

    public void update(String name, String newnumber){

        SQLiteDatabase db = helper.getWritableDatabase();

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

        db.close();

    }

   

    /**

     *删除一条记录

     *@param name

     */

    public void delete(String name){

        SQLiteDatabase db = helper.getWritableDatabase();

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

        db.close();

    }

   

    /**

     *返回全部的数据

     *@return

     */

    public List<Person> findAll(){

        SQLiteDatabase db = helper.getReadableDatabase();

        List<Person> persons = new ArrayList<Person>();

        Cursor cursor = db.rawQuery("select name,id,number from person", null);

        while(cursor.moveToNext()){

            int id = cursor.getInt(cursor.getColumnIndex("id"));

            String name = cursor.getString(cursor.getColumnIndex("name"));

            String number = cursor.getString(cursor.getColumnIndex("number"));

            Person p = new Person(id, name, number);

            persons.add(p);

        }

        cursor.close();

        db.close();    

        return persons;

    }

   

}

 

 

为方便数据的合理管理,在创建一个Person类:

package com.lcy.myfirstdb.domain;

 

public class Person {

    private int id;

    private String name;

    private String number;

   

    public Person() {

       

    }

   

    public Person(int id, String name, String number) {

        super();

        this.id = id;

        this.name = name;

        this.number = number;

    }

   

    public int getId() {

        return id;

    }

    public void setId(int id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public String getNumber() {

        return number;

    }

    public void setNumber(String number) {

        this.number = number;

    }

 

    @Override

    public String toString() {

        return "Person [id=" + id + ", name=" + name + ", number=" + number

                + "]";

    }

   

}

 

 

接下来在测试类可测试数据库了(测试时可直接选中对应的测试方法:然后Run As ->Android JUnit Test 即可):

public class TestPersonDB extends AndroidTestCase {

   

    private void testCreateDB() throws Exception{

        //此时只是创建数据库对象,并未真正的创建数据库

        PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext());

        //此时创建数据库,还有一个方法(getReadableDatabase())

        SQLiteDatabase db = helper.getWritableDatabase();      

    }

   

    public void textAdd() throws Exception{

        PersonDao dao = new PersonDao(getContext());

        dao.add("美女", "111");

    }

   

    public void testFind() throws Exception{

        PersonDao dao = new PersonDao(getContext());

        boolean result = dao.find("美女");

        assertEquals(true, result);

    }

   

    public void testUpdate() throws Exception{

        PersonDao dao = new PersonDao(getContext());

        dao.update("美女", "123");

    }

   

    public void testDelete() throws Exception{

        PersonDao dao = new PersonDao(getContext());

        dao.delete("美女");

    }

   

    public void FindAll() throws Exception{

        PersonDao dao = new PersonDao(getContext());

        List<Person> persons = dao.findAll();

        for(Person p : persons){

            System.out.println(p.toString());

        }

    }

   

}

 

 

API数据库,为了能避免错误(有中文空格,括号不对应等):

public class PersonDao2 {

    private PersonSQLiteOpenHelper helper;

    //在构造方法里面完成 helper的初始化

   

    public PersonDao2(Context context){

        helper = new PersonSQLiteOpenHelper(context);

    }

   

    /**

     *添加一条记录到数据库

     *@param name 姓名

     *@param number 电话

     */

    public long add(String name, String number, int money){

        SQLiteDatabase db = helper.getWritableDatabase();

        //db.execSQL("insert into person (name, number) values (?,?)", new Object[]{name, number});

        ContentValues values = new ContentValues();

        values.put("name", name);

        values.put("number", number);

        values.put("account", money);

        long id = db.insert("person", null, values);

        db.close();

        return id;

    }

   

    /**

     *查询记录是否存在

     *@param name 姓名

     *@param true 存在  false 不存在

     */

    public boolean find(String name){

        SQLiteDatabase db = helper.getReadableDatabase();

        //Cursor cursor = db.rawQuery("select * from person where name=?", new String[]{ name });

        Cursor cursor = db.query("person", null, "name=?", new String[]{ name }, null, null, null);

        boolean result = cursor.moveToNext();

        cursor.close();

        db.close();

        return result;

    }

   

    /**

     *修改一条记录

     *@param name 要修改的人的姓名

     *@param newnumber 新的号码

     */

    public int update(String name, String newnumber){

        SQLiteDatabase db = helper.getWritableDatabase();

        //db.execSQL("update person set number=? where name=?", new Object[]{newnumber,name});

        ContentValues values = new ContentValues();

        values.put("number", newnumber);

        int number = db.update("person", values, "name=?", new String[]{name});

        db.close();

        return number;

    }

   

    /**

     *删除一条记录

     *@param name

     */

    public int delete(String name){

        SQLiteDatabase db = helper.getWritableDatabase();

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

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

        db.close();

        return number;

    }

   

    /**

     *返回全部的数据

     *@return

     */

    public List<Person> findAll(){

        SQLiteDatabase db = helper.getReadableDatabase();

        List<Person> persons = new ArrayList<Person>();

        //Cursor cursor = db.rawQuery("select name,id,number from person", null);

        Cursor cursor = db.query("person", new String[]{"name","id","number"}, null, null, null, null, null);       

        while(cursor.moveToNext()){

            int id = cursor.getInt(cursor.getColumnIndex("id"));

            String name = cursor.getString(cursor.getColumnIndex("name"));

            String number = cursor.getString(cursor.getColumnIndex("number"));

            Person p = new Person(id, name, number);

            persons.add(p);

        }

        cursor.close();

        db.close();    

        return persons;

    }

   

}

 

 

当数据库表结构发生变化时(版本号),PersonSQLiteOpenHelper类调用onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)该方法:

public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {

 

    private static final String TAG = "PersonSQLiteOpenHelper";

 

    /**

     *数据库的结构方法 用来定义数据库的名称 数据库查询的结果集 数据库的版本

     *@param context

     */

    public PersonSQLiteOpenHelper(Context context) {

        super(context, "person.db", null, 2);

    }

 

    /**

     *数据库第一次被创建的时候调用的方法

     * @param db 被创建的数据库

     */

    @Override

    public void onCreate(SQLiteDatabase db) {

        //初始化数据库的表结构

        db.execSQL("create table person (id integer primary key autoincrement, name varchar(20), number varchar(20)) ");

    }

 

    /**

     *当数据表结构放生变化是调用

     */

    @Override

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        Log.i(TAG, "数据库的版本变化了...");

        db.execSQL("alter table person add account varchar(20)");

    }

 

}

 

数据库的事务:

 

在PersonDao2类中修改添加的方法:

public long add(String name, String number, int money){

        SQLiteDatabase db = helper.getWritableDatabase();

        //db.execSQL("insert into person (name, number) values (?,?)", new Object[]{name, number});

        ContentValues values = new ContentValues();

        values.put("name", name);

        values.put("number", number);

        values.put("account", money);

        long id = db.insert("person", null, values);

        db.close();

        return id;

    }

 

在类TestPersonDB中添加数据:

public void textAdd() throws Exception {

        PersonDao2 dao = new PersonDao2(getContext());

        dao.add("美女", "111", 5000);

        dao.add("帅哥", "222", 2000);

    }

 

再在该类中添加事务方法:

public void testTransaction() throws Exception {

        PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext());

        SQLiteDatabase db = helper.getWritableDatabase();

        // 开启数据库的事务

        db.beginTransaction();

        try {

            db.execSQL("update person set account=account-1000 where name =?",

                    new Object[] { "帅哥" });

            db.execSQL("update person set account=account+1000 where name =?",

                    new Object[] { "美女" });

            //标记数据库事务执行成功

            db.setTransactionSuccessful();

        }catch(Exception e){

           

        } finally {

            db.endTransaction();

            db.close();

        }

    }

 

 

为了安全,真实的手机是不能再DDMS中查看data里面的数据的,但可通过命令查看:

 

打开终端:

adb shell

cd data/data

cd com.lcy.myfirstdb

ls

cd databases

ls -1

sqlite3 person.db

>接下来可对数据库进行操作了,必须以分号结束语句

 

posted @   飞牛冲天  阅读(138)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示