Android 提供了SQLiteDatabase类,使用该类可以完成对数据库的添加、查询、更新、删除等操作。对于SQliteDatabase的学习,重点掌握其execSQL()和rawQuery()。execSQL()方法可以进行insert、delete、update和CREATE TABLE等可以更改行为的SQL语句;而rawQuery()方法用于执行select语句。
SQLiteDatabase db = getWritableDatabase()/ getReadableDatabase();
db.execSQL(“insert into person(name, age) values(‘测试数据’, 4)”);
db.close();
执行上面SQL语句会往person表中添加进一条记录,在实际应用中, 语句中的“测试数据”这些参数值会由用户输入界面提供,如果把用户输入的内容原样组拼到上面的insert语句, 当用户输入的内容含有单引号时,组拼出来的SQL语句就会存在语法错误。要解决这个问题需要对单引号进行转义,也就是把单引号转换成两个单引号。有些时候用户往往还会输入像“ & ”这些特殊SQL符号,为保证组拼好的SQL语句语法正确,必须对SQL语句中的这些特殊SQL符号都进行转义,显然,对每条SQL语句都做这样的处理工作是比较烦琐的。 SQLiteDatabase类提供了一个重载后的execSQL(String sql, Object[] bindArgs)方法,使用这个方法可以解决前面提到的问题,因为这个方法支持使用占位符参数(?)。使用例子如下:
SQLiteDatabase db = ….;
db.execSQL(“insert into person(name, age) values(?,?)”, new Object[]{“测试数据”, 4});
db.close();
execSQL(String sql, Object[] bindArgs)方法的第一个参数为SQL语句,第二个参数为SQL语句中占位符参数的值,参数值在数组中的顺序要和占位符的位置对应。
public class DatabaseHelper extends SQLiteOpenHelper {
//类没有实例化,是不能用作父类构造器的参数,必须声明为静态
private static final String name = "itcast"; //数据库名称
private static final int version = 1; //数据库版本
public DatabaseHelper(Context context) {
//第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类,设置为null,代表使用系统默认的工厂类
super(context, name, null, version);
}
@Override public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS person (personid integer primary key autoincrement, name varchar(20), age INTEGER)");
}
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(" ALTER TABLE person ADD phone VARCHAR(12) NULL "); //往表中增加一列
// DROP TABLE IF EXISTS person 删除表
}
}
//在实际项目开发中,当数据库表结构发生更新时,应该避免用户存放于数//据库中的数据丢失。
SQLiteDatabase的rawQuery() 用于执行select语句,使用例子如下: SQLiteDatabase db = ….;
Cursor cursor = db.rawQuery(“select * from person”, null);
while (cursor.moveToNext()) {
int personid = cursor.getInt(0); //获取第一列的值,第一列的索引从0开始
String name = cursor.getString(1);//获取第二列的值
int age = cursor.getInt(2);//获取第三列的值
}
cursor.close();
db.close();
rawQuery()方法的第一个参数为select语句;第二个参数为select语句中占位符参数的值,如果select语句没有使用占位符,该参数可以设置为null。带占位符参数的select语句使用例子如下:
Cursor cursor = db.rawQuery(“select * from person where name like ? and age=?”, new String[]{“%传智%”, “4”});
Cursor是结果集游标,用于对结果集进行随机访问,如果大家熟悉jdbc, 其实Cursor与JDBC中的ResultSet作用很相似。使用moveToNext()方法可以将游标从当前行移动到下一行,如果已经移过了结果集的最后一行,返回结果为false,否则为true。另外Cursor 还有常用的moveToPrevious()方法(用于将游标从当前行移动到上一行,如果已经移过了结果集的第一行,返回值为false,否则为true )、moveToFirst()方法(用于将游标移动到结果集的第一行,如果结果集为空,返回值为false,否则为true )和moveToLast()方法(用于将游标移动到结果集的最后一行,如果结果集为空,返回值为false,否则为true ) 。
除了前面给大家介绍的execSQL()和rawQuery()方法, SQLiteDatabase还专门提供了对应于添加、删除、更新、查询的操作方法: insert()、delete()、update()和query() 。这些方法实际上是给那些不太了解SQL语法的菜鸟使用的,对于熟悉SQL语法的程序员而言,直接使用execSQL()和rawQuery()方法执行SQL语句就能完成数据的添加、删除、更新、查询操作。
Insert()方法用于添加数据,各个字段的数据使用ContentValues进行存放。 ContentValues类似于MAP,相对于MAP,它提供了存取数据对应的put(String key, Xxx value)和getAsXxx(String key)方法, key为字段名称,value为字段值,Xxx指的是各种常用的数据类型,如:String、Integer等。
SQLiteDatabase db = databaseHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(“name”, “测试数据”);
values.put(“age”, 4);
long rowid = db.insert(“person”, null, values);//返回新添记录的行号,与主键id无关
不管第三个参数是否包含数据,执行Insert()方法必然会添加一条记录,如果第三个参数为空,会添加一条除主键之外其他字段值为Null的记录。Insert()方法内部实际上通过构造insert SQL语句完成数据的添加,Insert()方法的第二个参数用于指定空值字段的名称,相信大家对该参数会感到疑惑,该参数的作用是什么?是这样的:如果第三个参数values 为Null或者元素个数为0, 由于Insert()方法要求必须添加一条除了主键之外其它字段为Null值的记录,为了满足SQL语法的需要, insert语句必须给定一个字段名,如:insert into person(name) values(NULL),倘若不给定字段名 , insert语句就成了这样: insert into person() values(),显然这不满足标准SQL的语法。对于字段名,建议使用主键之外的字段,如果使用了INTEGER类型的主键字段,执行类似insert into person(personid) values(NULL)的insert语句后,该主键字段值也不会为NULL。如果第三个参数values 不为Null并且元素的个数大于0 ,可以把第二个参数设置为null。
delete()方法的使用:
SQLiteDatabase db = databaseHelper.getWritableDatabase();
db.delete(“person”, “personid
public class PersonService
{
private DBOpenHelper helper;
public PersonService(Context context)
{
helper=new DBOpenHelper(context);
}
/**
* 新增一条记录
* @param person
*/
public void save(Person person)
{
SQLiteDatabase db=helper.getWritableDatabase();//Create and/or open a database that will be used for reading and writing
db.execSQL("INSERT INTO person(name,phone) values(?,?)",new Object[]{person.getName().trim(),person.getPhone().trim()});//使用占位符进行转译
// db.close(); 不关数据库连接 。可以提高性能 因为创建数据库的时候的操作模式是私有的。
// 代表此数据库,只能被本应用所访问 单用户的,可以维持长久的链接
}
/**
* 更新某一条记录
* @param person
*/
public void update(Person person)
{
SQLiteDatabase db=helper.getWritableDatabase();
db.execSQL("update person set phone=?,name=? where personid=?",
new Object[]{person.getPhone().trim(),person.getName().trim(),person.getId()});
}
/**
* 根据ID查询某条记录
* @param id
* @return
*/
public Person find(Integer id)
{
SQLiteDatabase db=helper.getReadableDatabase();
Cursor cursor=db.rawQuery("select * from person where personid=?", new String[]{id.toString()});//Cursor 游标和 ResultSet 很像
if(cursor.moveToFirst())//Move the cursor to the first row. This method will return false if the cursor is empty.
{
int personid=cursor.getInt(cursor.getColumnIndex("personid"));
String name=cursor.getString(cursor.getColumnIndex("name"));
String phone=cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid,name,phone);
}
return null;
}
/**
* 删除某一条记录
* @param id
*/
public void delete(Integer id)
{
SQLiteDatabase db=helper.getWritableDatabase();
db.execSQL("delete from person where personid=?",
new Object[]{id});
}
/**
* 得到记录数
* @return
*/
public long getCount()
{
SQLiteDatabase db=helper.getReadableDatabase();
Cursor cursor=db.rawQuery("select count(*) from person", null);
cursor.moveToFirst();
return cursor.getLong(0);
}
/**
* 分页查询方法 SQL语句跟MySQL的语法一样
* @return
*/
public List<Person> getScrollData(int offset,int maxResult)
{
List<Person> persons=new ArrayList<Person>();
SQLiteDatabase db=helper.getReadableDatabase();
Cursor cursor=db.rawQuery("select * from person limit ?,?",
new String[]{String.valueOf(offset),String.valueOf(maxResult)});
while (cursor.moveToNext())
{
int personid=cursor.getInt(cursor.getColumnIndex("personid"));
String name=cursor.getString(cursor.getColumnIndex("name"));
String phone=cursor.getString(cursor.getColumnIndex("phone"));
persons.add(new Person(personid,name,phone));
}
return persons;
}
}
android开发中离不开数据库的操作,直接上代码:
public class DBHelper extends SQLiteOpenHelper {
//singleton instance
private static DBHelper sInstance;
private static final int DATABASE_VERSION = 0x02;
private static final String DATABASE_NAME = "heart_rate";
//the table name
private static final String TABLE_NAME = "rate";
private static final String FILED_ID = "id";
private static final String FILED_RATE = "rate";
private static final String FILED_TIME = "time";
private static final String TAG = DBHelper.class.getSimpleName();
private static final long MAX_RECORD = 6000000;
/**
* get the instance of dbhelper
*
* @param context the context of db
* @return instance of dbhelper
*/
public static DBHelper getInstance(Context context) {
if (sInstance == null) {
synchronized (DBHelper.class) {
if (sInstance == null) {
sInstance = new DBHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
}
}
}
return sInstance;
}
private DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
createDB(db);
}
private void createDB(SQLiteDatabase db) {
db.execSQL("create table IF NOT EXISTS " + TABLE_NAME + " (" + FILED_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + FILED_RATE + " INTEGER DEFAULT 0, " + FILED_TIME + " BIGINT DEFAULT 0);");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
public void putHRValues(List<Integer> values, List<Long> times){
LogUtils.d(TAG,"begin to insert ");
if(values != null && times != null) {
LogUtils.d(TAG,"not null ");
if(values.size() == times.size() && values.size() > 0) {
LogUtils.d(TAG,"size = " + values.size());
for(int i = 0; i < values.size(); ++i){
LogUtils.d(TAG,"rate insert " + values.get(i));
putHRValue(values.get(i),times.get(i));
}
}
}
}
/**
* put value into db
*
* @param value heart rate value
* @param time the time of the heart rate
*/
public boolean putHRValue(int value, long time) {
limitRecords(1);
SQLiteDatabase db = getWritableDatabase();
if (db != null && db.isOpen()) {
ContentValues cv = new ContentValues(1);
cv.put(FILED_RATE, value);
LogUtils.d(TAG,"put time before is " + time);
cv.put(FILED_TIME, time);
long rowID = db.insert(TABLE_NAME, null, cv);
LogUtils.d(TAG,"row inserted id is " + rowID);
return rowID!= -1;
} else {
LogUtils.d(TAG, "db is null or db is not open");
return false;
}
}
/**
* get the max rate from db
* @return HRBean contains max heart rate
*/
public HRBean getMaxRateBean(){
HRBean bean = null;
SQLiteDatabase db = getReadableDatabase();
if(db != null && db.isOpen()){
Cursor cursor = db.rawQuery("SELECT " + FILED_ID + ",MAX(" + FILED_RATE + ")," + FILED_TIME + " FROM " + TABLE_NAME + " ", null);
if(cursor != null && cursor.getCount() > 0){
cursor.moveToFirst();
bean = new HRBean();
bean.setID(cursor.getInt(0));
bean.setRate(cursor.getInt(1));
bean.setTime(cursor.getLong(2));
LogUtils.d(TAG,"max rate is " + String.valueOf(cursor.getInt(1)) + "\nmax time is " + String.valueOf(cursor.getLong(2)));
}
if(cursor != null){
cursor.close();
}
}
return bean;
}
/**
* get the min rate from db
* @return HRBean contains min heart rate
*/
public HRBean getMinRateBean(){
HRBean bean = null;
SQLiteDatabase db = getReadableDatabase();
if(db != null && db.isOpen()){
Cursor cursor = db.rawQuery("SELECT " + FILED_ID + ",MIN(" + FILED_RATE + ")," + FILED_TIME + " FROM " + TABLE_NAME + " ", null);
if(cursor != null && cursor.getCount() > 0){
cursor.moveToFirst();
bean = new HRBean();
bean.setID(cursor.getInt(0));
bean.setRate(cursor.getInt(1));
bean.setTime(cursor.getLong(2));
LogUtils.d(TAG,"min rate is " + String.valueOf(cursor.getInt(1)) + "\nmax time is " + String.valueOf(cursor.getLong(2)));
}
if(cursor != null){
cursor.close();
}
}
return bean;
}
/**
* get all the hr rate values
*
* @return list of heart rate
*/
public List<HRBean> getHRBeans() {
List<HRBean> result = new ArrayList<>();
SQLiteDatabase db = getReadableDatabase();
if (db != null && db.isOpen()) {
Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
if (cursor != null && cursor.getCount() > 0) {
cursor.moveToNext();
int count = cursor.getCount();
HRBean bean = null;
for (int i = 0; i < count; ++i) {
bean = new HRBean();
bean.setID(cursor.getInt(0));
bean.setRate(cursor.getInt(1));
bean.setTime(cursor.getLong(2));
LogUtils.d(TAG,"rate " + i + " is " + String.valueOf(cursor.getInt(1)) + "\ntime is " + String.valueOf(cursor.getLong(2)));
result.add(bean);
cursor.moveToNext();
}
}
if(cursor != null){
cursor.close();
}
}
if(result != null){
LogUtils.d(TAG,"result count = " + result.size());
}
return result;
}
/**
* if the count of records is larger than the given limit(MAX_RECORD), than delete one record first
* @param willAddedCount the count of rate values which to be added
*/
private void limitRecords(int willAddedCount) {
SQLiteDatabase db = getWritableDatabase();
if (db != null && db.isOpen()) {
Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_NAME, null);
LogUtils.d(TAG,"before count");
if (cursor != null && cursor.getCount() > 0) {
cursor.moveToNext();
LogUtils.d(TAG,"count is " + cursor.getLong(0));
long count = cursor.getLong(0);
if (count > MAX_RECORD - willAddedCount) {
db.execSQL("DELETE FROM " + TABLE_NAME + " WHERE " + FILED_TIME + " IN (SELECT " + FILED_TIME + " FROM " + TABLE_NAME + " ORDER BY " + FILED_TIME + " LIMIT 0," + (count - MAX_RECORD + willAddedCount) + ")");
}
}
if(cursor != null){
cursor.close();
}
}
}
/**
* close the db
* when application quit, call this to close db
*/
public void closeDB() {
getWritableDatabase().close();
}
}
自己的另外一个练习:
public class DBHelper extends SQLiteOpenHelper {
private static final boolean D = true;
private static final String TAG = DBHelper.class.getSimpleName();
public static final String DATABASE_NAME = "person.db";
public static final int DATABASE_VERSION = 0x01;
public static final String TABLE_NAME = "person";
public static final String PERSON_ID = "_id";
public static final String PERSON_NAME = "name";
public static final String PERSON_SCORE = "score";
private static DBHelper mDBHelper;
public static DBHelper getInstance(Context context) {
if(mDBHelper==null)
mDBHelper = new DBHelper(context,DATABASE_NAME,null,DATABASE_VERSION);
return mDBHelper;
}
public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
super(context, name, factory, version, errorHandler);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS "+TABLE_NAME+"("
+PERSON_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"
+PERSON_NAME+" VARCHAR(20),"
+PERSON_SCORE+" INTEGER"+");");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
public void putPerson(Person person) {
if(person!=null) {
SQLiteDatabase db = getWritableDatabase();
if(db==null||!db.isOpen())
return;
String name = person.getName();
int score = person.getScore();
db.execSQL("INSERT INTO "+TABLE_NAME+"("+PERSON_NAME+","+PERSON_SCORE+")"
+" values(?,?)",new Object[]{name.trim(),score});
}
}
public void clearPerson() {
SQLiteDatabase db = getWritableDatabase();
if(db!=null&&db.isOpen()) {
db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
mDBHelper = null;
onCreate(db);
}
}
public List<Person> getPersons() {
List<Person> persons = new ArrayList<>();
SQLiteDatabase db = getReadableDatabase();
if(db!=null&&db.isOpen()) {
Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
while(cursor.moveToNext()) {
int id_index = cursor.getColumnIndex(PERSON_ID);
int name_index = cursor.getColumnIndex(PERSON_NAME);
int score_index = cursor.getColumnIndex(PERSON_SCORE);
persons.add(new Person(cursor.getInt(id_index),cursor.getString(name_index),
cursor.getInt(score_index)));
}
if(!cursor.isClosed())
cursor.close();
}
return persons;
}
public void delPerson(int id) {
SQLiteDatabase db = getWritableDatabase();
if(db!=null&&db.isOpen()) {
db.execSQL("DELETE FROM "+TABLE_NAME+" WHERE "+PERSON_ID+" = ?",new Object[]{id});
}
}
public void updatePerson(int id,Person person) {
SQLiteDatabase db = getWritableDatabase();
if(db!=null&&db.isOpen()) {
db.execSQL("UPDATE "+TABLE_NAME+" set "+PERSON_NAME+"=?,"+PERSON_SCORE+
"=?"+" WHERE "+PERSON_ID+"=?",new
Object[]{person.getName().trim(),person.getScore(),id});
}
}
public Person getMaxPerson() {
SQLiteDatabase db = getReadableDatabase();
if(db!=null&&db.isOpen()) {
/*
Cursor cursor = db.rawQuery("SELECT "+PERSON_ID+","+PERSON_NAME+",MAX("+PERSON_SCORE+")"
+" FROM "+TABLE_NAME,null);*/
Cursor cursor = db.rawQuery("SELECT * FROM "+TABLE_NAME+" WHERE " +
""+PERSON_SCORE+"=( select MAX" +
"(score) from "+TABLE_NAME+")",null);
if(D) Log.d(TAG,"cursor count:"+cursor.getCount());
if(cursor.getCount()>0) {
cursor.moveToFirst();
if(D) Log.d(TAG,"cursor position is:"+cursor.getPosition());
int id = cursor.getColumnIndex(PERSON_ID);
int name = cursor.getColumnIndex(PERSON_NAME);
int score = cursor.getColumnIndex(PERSON_SCORE);
if(D) Log.d(TAG,"id:"+id+"name:"+name+"score:"+score);
Person person = new Person(cursor.getInt(id),
cursor.getString(name),
cursor.getInt(score));
return person;
}
cursor.close();
}
return null;
}
}
public class DBDialog extends DialogFragment{
private Context mContext;
private View mView;
private List<Person> mPersons;
private ListView mListView;
private PersonAdapter mPersonAdapter;
public DBDialog(List<Person> persons) {
super();
mPersons = persons;
}
@Override
public void onAttach(Context context) {
super.onAttach(context);
mContext = context;
}
@Nullable
@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
mView = inflater.inflate(R.layout.persons_layout,container);
init();
return mView;
}
private void init() {
mListView = (ListView)mView.findViewById(R.id.person_list);
mPersonAdapter = new PersonAdapter();
mListView.setAdapter(mPersonAdapter);
}
private class PersonAdapter extends BaseAdapter {
public PersonAdapter() {
super();
}
@Override
public int getCount() {
return mPersons.size();
}
@Override
public Object getItem(int position) {
return mPersons.get(position);
}
@Override
public long getItemId(int position) {
return 0;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
ViewHolder viewHolder = null;
if(convertView==null) {
convertView = LayoutInflater.from(mContext).inflate(R.layout.person_item_layout,
null);
viewHolder = new ViewHolder();
viewHolder.person_id = (TextView)convertView.findViewById(R.id.person_id_tv);
viewHolder.person_name = (TextView)convertView.findViewById(R.id.person_name_tv);
viewHolder.person_score = (TextView)convertView.findViewById(R.id.person_score_tv);
convertView.setTag(viewHolder);
}else {
viewHolder = (ViewHolder)convertView.getTag();
}
viewHolder.person_id.setText(String.valueOf(mPersons.get(position).getId()));
viewHolder.person_name.setText(mPersons.get(position).getName());
viewHolder.person_score.setText(String.valueOf(mPersons.get(position).getScore()));
return convertView;
}
}
private class ViewHolder {
TextView person_id;
TextView person_name;
TextView person_score;
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if(oldVersion!=newVersion) {
db.execSQL("ALTER TABLE "+USER_INFO_SQL_TABLE_NAME+" RENAME TO "
+USER_INFO_SQL_TABLE_NAME_TMP+";");
db.execSQL("create table if not exists "+USER_INFO_SQL_TABLE_NAME+"(_id integer "+
"primary key autoincrement,"+SQL_TABLE_ACCOUNT+" text,"+SQL_TABLE_PASSWORD +
" text);");
db.execSQL("INSERT INTO "+USER_INFO_SQL_TABLE_NAME+"SELECT *,' ' FROM "+USER_INFO_SQL_TABLE_NAME_TMP);
db.execSQL("DROP TABLE IF EXITS "+USER_INFO_SQL_TABLE_NAME_TMP);
}
}