android对sqlite数据库操作(创建 增 删 改 查)
操作sqlite数据库第一种方法execSql()? rawQuery () 第二种方法:insert() delete() update()? query()
/** * 通过继承SqliteOpenHelper来创建一个数据库 * @author Administrator * */public class DbOpenhelper extends SQLiteOpenHelper{private static String DATABASENAME = "secn.db";private static int DATABASEVERSION = 2;/** * (Context context, String name, CursorFactory factory,int version) * @param context 上下文对象 * @param name 数据库名称 secb.db * @param factory 游标工厂 * @param version 数据库版本 */public DbOpenhelper(Context context){super(context, DATABASENAME, null, DATABASEVERSION);}/**数据库第一次被使用时创建数据库 * @param db 操作数据库的 */public void onCreate(SQLiteDatabase db){//执行有更新行为的sql语句db.execSQL("CREATE Table person (personid integer primary key autoincrement, name varchar(20), amount integer,age integer)");}/**数据库版本发生改变时才会被调用,数据库在升级时才会被调用; * @param db 操作数据库 * @param oldVersion 旧版本 * @param newVersion 新版本 */public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){db.execSQL("drop table if exists person");onCreate(db);}}?
?
?
/** * 对Person对象的sql操作(增删改查) * * @author Administrator * */public class PersonService{private DbOpenhelper dbOpenHelper;public PersonService(Context context){dbOpenHelper = new DbOpenhelper(context);}/** * 添加Person * * @param person */public void addPerson(Person person){// 对读和写操作的方法// 如果当我们二次调用这个数据库方法,他们调用的是同一个数据库对象,在这里的方法创建的数据调用对象是用的同一个对象SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("insert into Person(name,amount) values(?,?)", new Object[]{ person.getName(), person.getAmount() });}/** * 修改Person * * @param person */public void modifyPerson(Person person){SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("update Person set name=? where personid=?", new Object[]{ person.getName(), person.getId() });}/** * 删除Person * * @param person */public void deletePerson(Integer id){SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("delete from Person where personid=?", new Object[]{ id.toString() });}/** * 根据person的Id查询Person对象 * * @param id * Person的ID * @return Person */public Person findPerson(Integer id){// 只对读的操作的方法SQLiteDatabase db = dbOpenHelper.getReadableDatabase();// Cursor游标的位置,默认是0,所有在操作时一定要先cursor.moveToFirst()一下,定位到第一条记录// Cursor cursor =// db.rawQuery("select * from person Where personid=?",new// String[]{id.toString()});Cursor cursor = db.query("Person", new String[]{ "personid", "name", "amount" }, "personid=?", new String[]{ id.toString() }, null, null, null);if (cursor.moveToFirst()){int personId = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("name"));int amount = cursor.getInt(cursor.getColumnIndex("amount"));return new Person(personId, name, amount);}return null;}/** * 返回Person对象的集合 * * @return List<Person> */public List<Person> findPersonList(Integer start, Integer length){List<Person> persons = new ArrayList<Person>();// 只对读的操作的方法SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select * from Person limit ?,?",new String[]{ start.toString(), length.toString() });cursor = db.query("Person", null, null, null, null, null, null, start+ "," + length);while (cursor.moveToNext()){int personId = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("name"));int amount = cursor.getInt(cursor.getColumnIndex("amount"));persons.add(new Person(personId, name, amount));}return persons;}/** * 返回Person的记录总个数 * * @return */public Long getCount(){SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select count(0) from Person ", null);// 这里必定有一条记录.所有不用判断,直接移到第一条.cursor.moveToFirst();// 这里只有一个字段时候 返回return cursor.getLong(0);}/** * 操作一个事务 * * @return */public String getTransaction(){SQLiteDatabase db = dbOpenHelper.getReadableDatabase();String success = "";db.beginTransaction();try{db.execSQL("update person set amount = amount+10 where personId=?",new Object[]{ 1 });db.execSQL("update person set amount = amount-10 where personId=?",new Object[]{ 2 });success = "success";} catch (Exception e){success = "input";} finally{// db.setTransactionSuccessful();//设置事务标志为成功,当结束事务时就会提交事务db.endTransaction();}return success;}}?
?
?
public class SqliteTest extends AndroidTestCase{/** * 创建数据库及新建表 */public void testCreateSqllite(){DbOpenhelper db = new DbOpenhelper(this.getContext());// 第一次调用该方法会调用数据库db.getWritableDatabase();}/** * 添加Person */public void testSavePerson(){PersonService db = new PersonService(this.getContext());Person person = new Person();person.setName("LiMing");person.setAmount(888);db.addPerson(person);}/** * 更新Person */public void testUpdatePerson(){PersonService db = new PersonService(this.getContext());Person person = new Person();person.setId(1);person.setName("LiMingRen");person.setAmount(101);db.modifyPerson(person);}/** * 删除Person */public void testDeletePerson(){PersonService db = new PersonService(this.getContext());db.deletePerson(1);}/** * 查询一条Person记录 */public void testPerson(){PersonService db = new PersonService(this.getContext());Person person = db.findPerson(2);Log.i("SqliteTest", person.toString());}/** * 查询Person集合对象 */public void testPersonList(){PersonService db = new PersonService(this.getContext());List<Person> persons = db.findPersonList(0, 5);for (Person parson : persons){Log.i("SqliteTest", parson.toString());}}/** * 测试一个事务 */public void testgetTransaction(){PersonService db = new PersonService(this.getContext());String str = db.getTransaction();Log.i("SqliteTest", str);}}?
?
public class Person{private int id;private String name;private int amount;public Person(){}public Person(int id, String name, int amount){super();this.id = id;this.name = name;this.amount = amount;}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 int getAmount(){return amount;}public void setAmount(int amount){this.amount = amount;}public String toString(){return "ID:"+id+" Name:" + name + " Amount:" + amount;}}?