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

更多相关文章

随机推荐