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