这里跟练习八差不多,也是先得到SQLiteOpenHelper的实现类的对象mOpenHelper,由这个对象得到数据库SQLiteDatabase对象db,但是之后不是调用execSQL之类的方法来直接SQL语句,而是使用Android提供的查询API方法:db.insert,db.delete,db.update,db.query四个方法
话不多说 直接上代码:
PersonDao类:
package com.alexchen.sqliteAPI.dao;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.alexchen.sqliteAPI.db.PersonSQLiteOpenHelper;
import com.alexchen.sqliteAPI.entity.Person;
public class PersonDao {
private static final String TAG = "PersonDao";
private PersonSQLiteOpenHelper mOpenHelper;// 数据库的帮助类
public PersonDao(Context context) {
mOpenHelper = new PersonSQLiteOpenHelper(context);
}
// 添加到person表一条数据
public void insert(Person person) {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
if (db.isOpen()) {
// ContentValues内部维护了一个HashMap对象
ContentValues values = new ContentValues();
values.put("name", person.getName());// key:作为要存储的列名,value作为对象列的值
values.put("age", person.getAge());
// 直接执行的SQLiteDatabase.insert的API方法
// 第二个参数 String nullColumnHack 是 对应的这个参数表示的列 在values为空时设置的默认值
// 比如说第二个参数设为"name",那么如果values为null时,name这一列的值就会被设为"null"
long id = db.insert("person", null, values);// 返回值是long类型的影响的行的行号
Log.i(TAG, "插入了" + id + "行");
db.close();
} else {
System.out.println("数据库打开失败");
return;
}
}
/*
* delete from person where _id=1;
*
* update person set name='李四' where _id=1;
*
* select * from person;
*/
/**
* 根据_id删除记录
*
* @param _id
*/
public void delete(int _id) {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
if (db.isOpen()) {
String[] whereArgs = new String[] { String.valueOf(_id) };
String whereClause = "_id=?";
long id = db.delete("person", whereClause, whereArgs);
Log.i(TAG, "删除了" + id + "行");
db.close();
}
}
/**
* 根据_id更新数据
*
* @param _id
*/
public void update(int _id, String name) {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
if (db.isOpen()) {
ContentValues values = new ContentValues();
values.put("name", name);
String[] whereArgs = new String[] { String.valueOf(_id) };
String whereClause = "_id=?";
long id = db.update("person", values, whereClause, whereArgs);
Log.i(TAG, "修改了" + id + "行");
db.close();
}
}
/**
* 查询所有
*
* @return
*/
public List<Person> queryAll() {
List<Person> personList;
int _id;
String name;
int age;
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
personList = null;
if (db.isOpen()) {
String[] columns = new String[] { "_id", "name", "age" };
String selection = null;// 选择条件为空,查询所有
String[] selectionArgs = null;// 选择条件的参数,会替换选择条件中的问好,这里没有,所以为空
String groupBy = null;// SQL中的分组语句,group by name,这如果要写要去掉group by
// ,直接写name即可
String having = null;// 过滤语句
String orderBy = null;// SQL排序语句
Cursor cursor = db.query("person", columns, selection,
selectionArgs, groupBy, having, orderBy);
if (cursor != null && cursor.getCount() > 0) {
personList = new ArrayList<Person>();
while (cursor.moveToNext()) {
_id = cursor.getInt(0);
name = cursor.getString(1);
age = cursor.getInt(2);
personList.add(new Person(_id, name, age));
}
}
db.close();
cursor.close();
}
db.close();
return personList;
}
/**
* 根据_id查询Item
*
* @param _id
* @return
*/
public Person queryItem(int _id) {
Person person = null;
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
if (db.isOpen()) {
String[] columns = new String[] { "_id", "name", "age" };
String selection = "_id=?";
String[] selectionArgs = new String[] { String.valueOf(_id) };
Cursor cursor = db.query("person", columns, selection,
selectionArgs, null, null, null);
if (cursor != null & cursor.moveToFirst()) {
String name = cursor.getString(1);
int age = cursor.getInt(2);
person = new Person(_id, name, age);
}
db.close();
cursor.close();
}
db.close();
return person;
}
}