sqlite的使用二

}

数据库操作的实体类Person

private int id;
private String name;
private int age;

数据库的操作dao类,第一种方式使用sql语句进行操作

public class PersonDao {


private PersonSQLiteOpenHelper mOpenHelper; // 数据库的帮助类对象


public PersonDao(Context context) {
mOpenHelper = new PersonSQLiteOpenHelper(context);
}

/**
* 添加到person表一条数据
* @param person
*/
public void insert(Person person) {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
if(db.isOpen()) { // 如果数据库打开,执行添加的操作

// 执行添加到数据库的操作
db.execSQL("insert into person(name,age) values(?,?);",new Object[]{person.getName(),person.getAge()});

db.close(); // 数据库关闭
}
}

/**
* 更据id删除记录
* @param id
*/
public void delete(int id) {
SQLiteDatabase db = mOpenHelper.getWritableDatabase(); // 获得可写的数据库对象
if(db.isOpen()) { // 如果数据库打开,执行添加的操作

db.execSQL("delete from person where _id = ?;",new Integer[]{id});

db.close(); // 数据库关闭
}
}



/**
* 根据id找到记录,并且修改姓名
* @param id
* @param name
*/
public void update(int id,String name) {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
if(db.isOpen()) { // 如果数据库打开,执行添加的操作

db.execSQL("update person set name = ? where _id = ?;",new Object[]{name,id});

db.close(); // 数据库关闭
}
}

public List<Person> queryAll() {
SQLiteDatabase db = mOpenHelper.getReadableDatabase(); // 获得一个只读的数据库对象
if(db.isOpen()) {

Cursor cursor = db.rawQuery("select _id,name,age from person;",null);

if(cursor != null && cursor.getCount() > 0) {
List<Person> personList = new ArrayList<Person>();
int id;
String name;
int age;
while(cursor.moveToNext()) {
id = cursor.getInt(0); // 取第0列的数据 id
name = cursor.getString(1); // 取姓名
age = cursor.getInt(2); // 取年龄
personList.add(new Person(id,age));
}


db.close();
return personList;
}
db.close();
}
return null;
}

/**
* 根据id查询人
* @param id
* @return
*/
public Person queryItem(int id) {
SQLiteDatabase db = mOpenHelper.getReadableDatabase(); // 获得一个只读的数据库对象
if(db.isOpen()) {
Cursor cursor = db.rawQuery("select _id,age from person where _id = ?;",new String[]{id + ""});
if(cursor != null && cursor.moveToFirst()) {
int _id = cursor.getInt(0);
String name = cursor.getString(1);
int age = cursor.getInt(2);
db.close();
return new Person(_id,age);
}
db.close();
}
return null;
}
}

第一种数据库操作方式的测试类

public class TestCase extends AndroidTestCase {


private static final String TAG = "TestCase";


public void test() {
// 数据库什么时候创建
PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());

// 第一次连接数据库时创建数据库文件. onCreate会被调用
openHelper.getReadableDatabase();
}

public void testInsert() {
PersonDao dao = new PersonDao(getContext());

dao.insert(new Person(0,"冠希",28));
}

public void testDelete() {
PersonDao dao = new PersonDao(getContext());
dao.delete(1);
}


public void testUpdate() {
PersonDao dao = new PersonDao(getContext());
dao.update(3,"凤姐");
}


public void testQueryAll() {
PersonDao dao = new PersonDao(getContext());
List<Person> personList = dao.queryAll();

for (Person person : personList) {
Log.i(TAG,person.toString());
}
}


public void testQueryItem() {
PersonDao dao = new PersonDao(getContext());
Person person = dao.queryItem(4);
Log.i(TAG,person.toString());
}

public void testTransaction() {
PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());
SQLiteDatabase db = openHelper.getWritableDatabase();

if(db.isOpen()) {

try {
// 开启事务
db.beginTransaction();

// 1. 从张三账户中扣1000块钱
db.execSQL("update person set balance = balance - 1000 where name = 'zhangsan';");

// ATM机,挂掉了.
// int result = 10 / 0;

// 2. 向李四账户中加1000块钱
db.execSQL("update person set balance = balance + 1000 where name = 'lisi';");

// 标记事务成功
db.setTransactionSuccessful();
} finally {
// 停止事务
db.endTransaction();
}
db.close();
}
}

public void testTransactionInsert() {
PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());
SQLiteDatabase db = openHelper.getWritableDatabase();

if(db.isOpen()) {

// 1. 记住当前的时间
long start = System.currentTimeMillis();

// 2. 开始添加数据
try {
db.beginTransaction();
for (int i = 0; i < 10000; i++) {
db.execSQL("insert into person(name,age,balance) values('wang" + i + "'," + (10 + i) + "," + (10000 + i) + ")");
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}

// 3. 记住结束时间,计算耗时时间
long end = System.currentTimeMillis();

long diff = end - start;
Log.i(TAG,"耗时: " + diff + "毫秒");

db.close();
}
}
}

第二种操作数据库的方式使用android自带的系统的api来操作数据库

public class PersonDao2 {


private static final String TAG = "PersonDao2";
private PersonSQLiteOpenHelper mOpenHelper; // 数据库的帮助类对象


public PersonDao2(Context context) {
mOpenHelper = new PersonSQLiteOpenHelper(context);
}

/**
* 添加到person表一条数据
* @param person
*/
public void insert(Person person) {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
if(db.isOpen()) { // 如果数据库打开,执行添加的操作

ContentValues values = new ContentValues();
values.put("name",person.getName()); // key作为要存储的列名,value对象列的值
values.put("age",person.getAge());
long id = db.insert("person","name",values);
Log.i(TAG,"id: " + id);

db.close(); // 数据库关闭
}
}

/**
* 更据id删除记录
* @param id
*/
public void delete(int id) {
SQLiteDatabase db = mOpenHelper.getWritableDatabase(); // 获得可写的数据库对象
if(db.isOpen()) { // 如果数据库打开,执行添加的操作

String whereClause = "_id = ?";
String[] whereArgs = {id + ""};
int count = db.delete("person",whereClause,whereArgs);
Log.i(TAG,"删除了: " + count + "行");
db.close(); // 数据库关闭
}
}



/**
* 根据id找到记录,执行添加的操作
ContentValues values = new ContentValues();
values.put("name",name);

int count = db.update("person",values,"_id = ?",new String[]{id + ""});


Log.i(TAG,"修改了: " + count + "行");

db.close(); // 数据库关闭
}
}

public List<Person> queryAll() {
SQLiteDatabase db = mOpenHelper.getReadableDatabase(); // 获得一个只读的数据库对象
if(db.isOpen()) {
String[] columns = {"_id","age"}; // 需要的列
String selection = null; // 选择条件,给null查询所有
String[] selectionArgs = null; // 选择条件的参数,会把选择条件中的? 替换成数据中的值
String groupBy = null; // 分组语句 group by name
String having = null; // 过滤语句
String orderBy = null; // 排序

Cursor cursor = db.query("person",columns,selection,selectionArgs,groupBy,having,orderBy);

int id;
String name;
int age;
if(cursor != null && cursor.getCount() > 0) {
List<Person> personList = new ArrayList<Person>();

while(cursor.moveToNext()) { // 向下移一位,知道最后一位,不可以往下移动了,停止.
id = cursor.getInt(0);
name = cursor.getString(1);
age = cursor.getInt(2);

personList.add(new Person(id,age));
}

db.close();
return personList;
}
db.close();
}
return null;
}

/**
* 根据id查询人
* @param id
* @return
*/
public Person queryItem(int id) {
SQLiteDatabase db = mOpenHelper.getReadableDatabase(); // 获得一个只读的数据库对象
if(db.isOpen()) {
String[] columns = {"_id","age"}; // 需要的列
String selection = "_id = ?"; // 选择条件,给null查询所有
String[] selectionArgs = {id + ""}; // 选择条件的参数,orderBy);

if(cursor != null && cursor.moveToFirst()) { // cursor对象不为null,并且可以移动到第一行
int _id = cursor.getInt(0);
String name = cursor.getString(1);
int age = cursor.getInt(2);

db.close();
return new Person(_id,age);
}
db.close();
}
return null;
}
}

第二种操作方式的测试类

public class TestCase2 extends AndroidTestCase { private static final String TAG = "TestCase"; public void test() { // 数据库什么时候创建 PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext()); // 第一次连接数据库时创建数据库文件. onCreate会被调用 openHelper.getReadableDatabase(); } public void testInsert() { PersonDao2 dao = new PersonDao2(getContext()); dao.insert(new Person(0,"zhouqi",88)); } public void testDelete() { PersonDao2 dao = new PersonDao2(getContext()); dao.delete(8); } public void testUpdate() { PersonDao2 dao = new PersonDao2(getContext()); dao.update(3,"fengjie"); } public void testQueryAll() { PersonDao2 dao = new PersonDao2(getContext()); List<Person> personList = dao.queryAll(); for (Person person : personList) { Log.i(TAG,person.toString()); } } public void testQueryItem() { PersonDao2 dao = new PersonDao2(getContext()); Person person = dao.queryItem(4); Log.i(TAG,person.toString()); } }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!