Android 原生SQLite数据库操作实战

创建DB工具类

MyDBHelper.java(创建数据库的操作)

package com.amos.android_db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/** * Created by amosli on 14-6-12. */public class MyDBHelper extends SQLiteOpenHelper{    /**     *     * @param context     */    public MyDBHelper(Context context) {        super(context, "sqlitedb", null, 1);    }    /**     * 数据库第一次创建的时候调用此方法     * @param db     */    @Override    public void onCreate(SQLiteDatabase db) {        db.execSQL("create table if not exists person (personid integer primary key autoincrement ,name varchar(30) ,age integer(3) )");    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {    }}

PersonDao.java (实现增删改查)

package com.amos.android_db.dao;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;import com.amos.android_db.MyDBHelper;import java.util.ArrayList;import java.util.List;/** * Created by amosli on 14-6-12. */public class PersonDao {    private Context context;    MyDBHelper dbHelper;    public PersonDao(Context context) {        this.context = context;        dbHelper = new MyDBHelper(context);    }    /**     * 添加一条记录     */    public void add(String name, int age) {        SQLiteDatabase db = dbHelper.getWritableDatabase();        if (db.isOpen()) {            ContentValues values = new ContentValues();            values.put("age", age);            values.put("name", name);            //不允许插入一个空值,如果contentvalue,一般第二个参            db.insert("person", null, values);//通过组拼完成的添加的操作        }        db.close();    }}

测试添加方法

public void testAdd() throws Exception{        PersonDao personDao = new PersonDao(this.getContext());        personDao.add("amosli",10);        personDao.add("amosli",10);        for(int i=0;i<10;i++){            personDao.add("amos"+i,10+i);        }    }

删除数据
delete方法,主要是调用了SQLiteDatabase的delete方法.其实质上也是在拼sql语句.

public void delete(String name) {        SQLiteDatabase db = dbHelper.getWritableDatabase();        if (db.isOpen()) {            db.delete("person", "name=?", new String[]{name});            db.close();        }    }    //测试 delete方法：    public void testDelete() throws Exception{            PersonDao personDao = new PersonDao(this.getContext());            personDao.delete("amosli");        }

更新数据

public void update(String name, String newname, int newage) {        SQLiteDatabase db = dbHelper.getWritableDatabase();        if (db.isOpen()) {            ContentValues contentValues = new ContentValues();            contentValues.put("name", newname);            contentValues.put("age", newage);            db.update("person", contentValues, "name=?", new String[]{name});            db.close();        }    }    //测试update    public void testUpdate() throws Exception{            PersonDao personDao = new PersonDao(this.getContext());            personDao.update("amos0","0amos",35);     }

查询数据

public boolean find(String name) {        boolean status_result = false;        SQLiteDatabase db = dbHelper.getReadableDatabase();//        public android.database.Cursor query(//                String table,//                String[] columns,//                String selection,//                String[] selectionArgs,//                String groupBy,//                String having,//                String orderBy)        if (db.isOpen()) {            Cursor cursor = db.query("person", null, "name=?", new String[]{name}, null, null, null);            if (cursor.moveToFirst()) {                status_result = true;            }            cursor.close();            db.close();        }            return status_result;        }        //测试select public void testFind() throws Exception{            PersonDao personDao = new PersonDao(this.getContext());            assertEquals(true,personDao.find("amos1"));        }

查询所有数据

public List findAll(){         List persons = null;         SQLiteDatabase db = dbHelper.getReadableDatabase();         if(db.isOpen()){             persons = new ArrayList();             Cursor cursor = db.query("person", null, null, null, null, null, null);             while(cursor.moveToNext()){                 Person person = new Person();                 person.setName(cursor.getString(cursor.getColumnIndex("name")));                 person.setAge(cursor.getInt(cursor.getColumnIndex("age")));                 persons.add(person);              }             cursor.close();             db.close();         }         return persons;     } // 测试public void testFindAll() throws Exception{            PersonDao personDao = new PersonDao(getContext());            List personList = personDao.findAll();            for(Person person:personList){                Log.d("person:",person.toString());            }        }

扩展（事务）
这里以amos1向amos2转钱200元为例:

1),amos1账户初始1000元,amos2账户初始0元.

2),从amos1中减去200元,amos2中加上200元,这两个步骤要么同时成功,要么同时失败,不能一方成功,另一主失败,这就是事务.

代码实现：升级数据库

package com.amos.android_db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/** * Created by amosli on 14-6-12. */public class MyDBHelper extends SQLiteOpenHelper{       public MyDBHelper(Context context) {        super(context, "sqlitedb", null, 2);    }    /**     * 数据库第一次创建的时候调用此方法     * @param db     */    @Override    public void onCreate(SQLiteDatabase db) {        db.execSQL("create table if not exists person (personid integer primary key autoincrement ,name varchar(30) ,age integer(3) )");    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        db.execSQL("alter table person add account integer null");    }}

在初始化时更新表的结构,添加account一列,用来表示账户余额.

在PersonDao中添加如下方法:

public void transferMoney() {        SQLiteDatabase db = dbHelper.getWritableDatabase();        if(db.isOpen()){            try{                db.beginTransaction();                //给amos1账户里设置1000元,amost account=0;                db.execSQL("update person  set account=?  where name = ?",new Object[]{1000,"amos1"});                db.execSQL("update person  set account=?  where name = ?",new Object[]{0,"amos2"});                //从amos1账户里扣除200元                db.execSQL("update person  set account=account-?  where name = ?",new Object[]{200,"amos1"});                //把amos1的钱转给amos2                db.execSQL("update person set account=account+? where name=?",new Object[]{200,"amos2"});            }catch(Exception e){                e.printStackTrace();            }finally{                //显示的设置数据事务是否成功                db.setTransactionSuccessful();                db.endTransaction();                db.close();            }        }    }

这里先beginTransaction,然后要注意的是setTransactionSuccessful和endTransaction.

public void testTransaction() throws Exception{            PersonDao personDao = new PersonDao(getContext());            personDao.transferMoney();        }

更多相关文章

随机推荐