Android SQLite数据库解析并使用两种方法实现增删改查

一、创建实体类

public class Person {

    public int id;
    public String name;
    public String number;
}

第二步：创建数据库

public class PersonDBHelper extends SQLiteOpenHelper {
    private static final Uri PERSONS_DB_URI = Uri.parse("content://person");
    private Context mContext;

    public PersonDBHelper(Context context) {
        super(context, "erp.db", null, 1);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "create table person(_id integer primary key autoincrement, name varchar(20), number varchar(20))";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i("tag", "oldVersion=" + oldVersion);
        Log.i("tag", "newVersion=" + newVersion);
        //db.execSQL("alter table person add age integer");
        db.execSQL("DROP TABLE IF EXISTS person");
        mContext.getContentResolver().notifyChange(PERSONS_DB_URI, null);
        this.onCreate(db);
    }

}

第三步：实现增删改查
1）使用原始方法

public class PersonService {

    private PersonDBHelper mHelper;

    public PersonService(Context context){
        mHelper = new PersonDBHelper(context);
    }

    public void add(String name, String number){
        SQLiteDatabase db = mHelper.getWritableDatabase();
        db.execSQL("insert into person(name,number)values(?,?)", new String[]{name,number});
        db.close();
    }

    public void update(String name, String number){
        SQLiteDatabase db = mHelper.getWritableDatabase();
        db.execSQL("update person set number = ? where name = ?", new String[]{number,name});
        db.close();
    }

    public void delete(String name){
        SQLiteDatabase db = mHelper.getWritableDatabase();
        db.execSQL("delete from person where name = ?", new String[]{name});
        db.close();
    }

    public boolean find(String name){
        boolean result = false;
        SQLiteDatabase db = mHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from person where name = ?", new String[]{name});
        if (cursor.moveToNext()) {
            result = true;
        }
        cursor.close();
        db.close();
        return result;
    }

    public List<Person> findAll(){
        List<Person> persons = new ArrayList<Person>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        Cursor cursor = db.rawQuery("select * from person", null);
        while(cursor.moveToNext()){
            Person person = new Person();
            int id = cursor.getInt(cursor.getColumnIndex("_id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String number = cursor.getString(cursor.getColumnIndex("number"));
            person.id = id;
            person.name = name;
            person.number = number;
            persons.add(person);
        }
        cursor.close();
        db.close();
        return persons;
    }
}

2）使用Android提供的方法

public class PersonUtil {

    private PersonDBHelper mHelper;

    public PersonUtil(Context context){
        mHelper = new PersonDBHelper(context);
    }

    public long add(String name, String number){
        SQLiteDatabase db = mHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("number", number);
        long insert = db.insert("person", null, values);
        db.close();
        return insert;
    }

    public int update(String name, String number){
        SQLiteDatabase db = mHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("number", number);
        String whereClause = " name = ?";
        int update = db.update("person", values, whereClause, new String[]{name});
        db.close();
        return update;
    }

    public void delete(int id){
        SQLiteDatabase db = mHelper.getWritableDatabase();
        db.delete("person", " _id = ? ", new String[]{id+""});
        db.close();
    }

    public boolean find(String name){
        boolean result = false;
        SQLiteDatabase db = mHelper.getReadableDatabase();
        String selection = " number = ? ";
        Cursor cursor = db.query("person", null, selection, new String[]{name}, null, null, null);
        if (cursor.moveToNext()) {
            result = true;
        }
        cursor.close();
        db.close();
        return result;
    }

    public List<Person> findAll(){
        List<Person> persons = new ArrayList<Person>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        Cursor cursor = db.query("peron", null, null, null, null, null, null);
        while(cursor.moveToNext()){
            Person person = new Person();
            int id = cursor.getInt(cursor.getColumnIndex("_id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String number = cursor.getString(cursor.getColumnIndex("number"));
            person.id = id;
            person.name = name;
            person.number = number;
            persons.add(person);
        }
        cursor.close();
        db.close();
        return persons;
    }
}

第四步：使用事物控制

public class PersonTransation extends AndroidTestCase{

    public void testAge(){
        PersonDBHelper helper = new PersonDBHelper(getContext());
        SQLiteDatabase db = helper.getWritableDatabase();
        db.beginTransaction();
        try {
            db.execSQL("update person set age = age - 100 where _id = ?", new Object[]{"1"});
            db.execSQL("update person set age = age + 100 where _id = ?", new Object[]{"2"});
            db.setTransactionSuccessful();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            db.endTransaction();
            db.close();
        }
    }
}

五、contentprovide的使用

package com.view.loaders.provider;

import com.view.loaders.db.DbHelper;

import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.util.Log;
import android.widget.SeekBar;

public class PersonContentProvider extends ContentProvider {
    // 公开的 内容uri
    // uri 通常只有两种格式，即请求全数据的 还有 withAppendId (/#)形式的

    public static final Uri Content_URI = Uri
            .parse("content://com.view.loaders.provider.PersonContentProvider/person");
    // 列名很重要 ，公开查询的ID 和其他列的列名
    public static final String KEY_ID = "id";
    public static final String COLUMN_1_NAME = "name";
    public static final String COLUMN_2_NAME = "password";

    // 数据库帮助类
    private DbHelper helper;
    // 单行操作 还是多行操作
    private static final int SINGLE_ROW = 2;
    private static final int ALLROWS = 1;

    private static final UriMatcher URI_MATCHER = new UriMatcher(
            UriMatcher.NO_MATCH);

    static {
        // 匹配
        // 单行操作 多行操作授权相同，主要是路径不同

        URI_MATCHER.addURI("com.view.loaders.provider.PersonContentProvider",
                "person/#", SINGLE_ROW);
        URI_MATCHER.addURI("com.view.loaders.provider.PersonContentProvider",
                "person", ALLROWS);
    }

    public PersonContentProvider() {
        // TODO Auto-generated constructor stub
    }

    /**
     * 初始化 在此实例化数据库帮助类
     */
    @Override
    public boolean onCreate() {
        helper = new DbHelper(getContext());
        return true;
    }

    /**
     * 返回MIME信息
     */
    @Override
    public String getType(Uri uri) {
        int flag = URI_MATCHER.match(uri);
        Log.i("nikan", "11111------" + flag);
        switch (flag) {
        case SINGLE_ROW:
            return "vnd.android.cursor.item/person";

        case ALLROWS:
            return " vnd.android.cursor.dir/person";

        }
        return null;
    }

    /**
     * 插入
     */
    @Override
    public Uri insert(Uri uri, ContentValues values) {
        // TODO Auto-generated method stub
        SQLiteDatabase db = helper.getWritableDatabase();
        Uri newUri = null;

        int flag = URI_MATCHER.match(uri);
        Log.i("nikan", "insert   ......." + flag);
        // 插入的Uri不带id，id为自动增长
        // 所以插入的Uri会匹配为ALLROWS，或者不需要匹配验证
        switch (flag) {
        case ALLROWS:
            Log.i("nikan", "insert   0002  .......");
            long id = db.insert("person", null, values);
            newUri = ContentUris.withAppendedId(uri, id);
            Log.i("nikan", newUri.toString());
            return newUri;

        default:
            break;
        }
        return null;
    }

    /**
     * 删除
     */
    @Override
    public int delete(Uri uri, String selection, String[] selectionArgs) {
        int flag = URI_MATCHER.match(uri);
        SQLiteDatabase db = helper.getWritableDatabase();
        switch (flag) {
        case SINGLE_ROW:
            // String rowID=uri.getPathSegments().get(1);
            // 获取Uri里面的ID
            long id = ContentUris.parseId(uri);
            String whereValues = "id=" + id;
            Log.i("nikan", "delete   ......." + id);
            // 如果selection不为空，添加where条件
            if (selection != null && "".equals(selection.trim())) {
                whereValues += "AND" + selection;
            }
            int count = db.delete("person", whereValues, selectionArgs);
            // count 删除操作影响的行数
            if (count > 0) {
                Log.i("nikan", "delete success   .......");
            }

            return count;

        case ALLROWS:
            int counts = db.delete("person", selection, selectionArgs);
            return counts;

        default:
            return 0;
        }

    }

    /**
     * 更新数据
     */
    @Override
    public int update(Uri uri, ContentValues values, String selection,
            String[] selectionArgs) {
        int flag = URI_MATCHER.match(uri);
        SQLiteDatabase db = helper.getWritableDatabase();
        switch (flag) {
        case SINGLE_ROW:
            long id = ContentUris.parseId(uri);
            String whereValues = "id=" + id;
            if (selection != null && "".equals(selection.trim())) {
                whereValues += "AND" + selection;
            }
            int count = db.update("person", values, whereValues, selectionArgs);
            if (count > 0) {
                Log.i("nikan", "update success....." + id);
                return count;
            }

        default:
            break;
        }
        return 0;
    }

    /**
     * 查询
     */
    @Override
    public Cursor query(Uri uri, String[] projection, String selection,
            String[] selectionArgs, String sortOrder) {
        SQLiteDatabase db = helper.getWritableDatabase();
        // SQLiteQueryBuilder is a helper class that creates the
        // proper SQL syntax for us.
        SQLiteQueryBuilder qBuilder = new SQLiteQueryBuilder();

        // Set the table we're querying.
        qBuilder.setTables(DbHelper.DATABASE_PERSON_TABLE);

        // If the query ends in a specific record number, we're
        // being asked for a specific record, so set the
        // WHERE clause in our query.
        if ((URI_MATCHER.match(uri)) == SINGLE_ROW) {
            qBuilder.appendWhere("id=" + ContentUris.parseId(uri));
        }

        // Make the query.
        Cursor c = qBuilder.query(db, projection, selection, selectionArgs,
                null, null, sortOrder);
        // 通知所有觀察者 ，數據集以改變
        c.setNotificationUri(getContext().getContentResolver(), uri);
        return c;

    }

}