android mysql sqlite_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

*/

public List findPersonList(Integer start, Integer length)

{

List persons = new ArrayList();

// 只对读的操作的方法

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

}

}

分享到：

2011-12-22 21:48

浏览 10909

评论