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;    }}