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