创建数据库与完成数据增删改查(二)
数据增删改查
SQLite完成数据增删改查
A.使用SQLiteOpenHelper里的.getReadableDatabase()方法或.getWriteableDatabase()方法完成数据的增删改查区别: 当数据库的磁盘空间满了,getWriteableDatabase()就不能用了,不能写数据,但可以读,就用getReadableDatabase()来读数据
用一个实例来说明SQLite的增删改查:
<span style="white-space:pre"> </span>package com.example.dao;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.bean.Person;
public class PersonDao {
//DBOpenHelper这个类是创建数据库的类,在《创建数据库与完成数据添删改查(一)》中有
private DBOpenHelper dbOpenHelper;
public PersonDao(Context context) {
super();
this.dbOpenHelper = new DBOpenHelper(context);
}
/**
* 添加记录
* @param person
*/
public void save(Person person){
//有一个缓存的特点
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("insert into person(name, phone, amount) values(?,?,?)",
new Object[]{person.getName(),person.getPhone(),person.getAmount()});
}
/**
* 删除记录
* @param id 记录id
*/
public void delete(Integer id){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("delete from person where personid=?",
new Object[]{id});
}
/**
* 更新记录
* @param person
*/
public void update(Person person){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("update person set name=?,phone=?,amount=? where personid=?",
new Object[]{person.getName(),person.getPhone(), person.getAmount(),person.getId()});
}
/**
* 查询记录
* @param id 记录Id
* @return
*/
public Person find(Integer id){
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
//Cursor游标对象,专门对返回的结果集进行随机访问
Cursor cursor = db.rawQuery("select * from person where personid=?",
new String[]{id.toString()});
if(cursor.moveToFirst()){ //若有数据,就返回true,否则false
//cursor.getInt(字段在结果集中的索引号)
//索引号 cursor.getColumnIndex(columnName)根据字段名得到字段索引
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
int amount = cursor.getInt(cursor.getColumnIndex("amount"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid,name,phone,amount);
}
//
cursor.close();
return null;
}
/**
* 进行分页 (分页获取记录)
* @param offset 跳过前面多少条记录
* @param maxResult 每页获取多少条记录
* @return
*/
public List<Person> getScrollData(int offset,int maxResult){
List<Person> personList = new ArrayList<Person>();
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from person order by personid asc limit ?,?",
new String[]{String.valueOf(offset),String.valueOf(maxResult)});
while(cursor.moveToNext()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
int amount = cursor.getInt(cursor.getColumnIndex("amount"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
personList.add(new Person(personid, name, phone, amount));
}
cursor.close();
return personList;
}
/**
* 取得记录总数
* @return
*/
public long getCount(){
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*) from person", null);
cursor.moveToFirst();
long result = cursor.getLong(0);
cursor.close();
return result;
}
/**
* 实现事务 :转账
*/
public void payment(){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
//要创建一个事务
db.beginTransaction(); //开启事务
//确保endTransaction会执行
try{
db.execSQL("update person set amount=amount-10 where personid=29");
db.execSQL("update person set amount=amount+10 where personid=30");
db.setTransactionSuccessful(); //设置事务的标志为true
}finally{
db.endTransaction(); //结束事务,有两种情况:commit,rollback,
}
//事务的提交或回滚是由事务的标志决定的,
//true:事务就会提交,false:回滚(默认)
}
}
除了前面介绍的execSQL()和rawQuery方法,SQLiteDatabase还专门提供了对应添加,删除,更新,查询的操作方法:insert(),delete(),update(),query()这些方法是给菜鸟使用的,对熟悉sql的程序员来说,就只要用execSQL()和rawQuery()方法就行了,当然还是用一下看看:
B.使用insert(),delete(),update(),query()方法完成数据的增删改查
<span style="white-space:pre"> </span>package com.example.dao;
import java.util.ArrayList;
import java.util.List;
import android.R.id;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.bean.Person;
public class OtherPersonDao {
private DBOpenHelper dbOpenHelper;
public OtherPersonDao(Context context) {
super();
this.dbOpenHelper = new DBOpenHelper(context);
}
/**
* 使用insert添加记录
* @param person
*/
public void save(Person person){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
//insert(String table, String nullColumnHack, ContentValues values)
//insert(表的名称,空值字段,值集合)
//如何用户传入的值为null或者为空集合,就会用到第二个参数了,填入字段(也可以是主键)
ContentValues values = new ContentValues();
values.put("name", person.getName());
values.put("phone", person.getPhone());
values.put("amount", person.getAmount());
db.insert("person", null, values);
}
/**
* 使用delete删除记录
* @param id 记录id
*/
public void delete(Integer id){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
//delete(表的名称, where关键字后的内容, ?参数)
db.delete("person", "personid=?", new String[]{id.toString()});
}
/**
* 使用update更新记录
* @param person
*/
public void update(Person person){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
//update(String table, ContentValues values, String whereClause, String[] whereArgs)
//update(表的名称, ContentValues值集合, where关键字后的内容, ?参数)
ContentValues values = new ContentValues();
values.put("name", person.getName());
values.put("phone", person.getPhone());
values.put("amount", person.getAmount());
db.update("person", values, "personid=?", new String[]{person.getId().toString()});
}
/**
* 使用select查询记录
* @param id 记录Id
* @return
*/
public Person find(Integer id){
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
//db.query(表的名称, 要查询的字段列表 如果为null就相当于*,
// where关键字后的内容, ?参数, groupBy后的语句, having后的语句, orderBy后的语句)
Cursor cursor = db.query("person", new String[]{"personid","name","phone"},
"personid=?", new String[]{id.toString()}, null, null, null);
if(cursor.moveToFirst()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
int amount = cursor.getInt(cursor.getColumnIndex("amount"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid,name,phone,amount);
}
cursor.close();
return null;
}
/**
* 进行分页 (分页获取记录)
* @param offset 跳过前面多少条记录
* @param maxResult 每页获取多少条记录
* @return
*/
public List<Person> getScrollData(int offset,int maxResult){
List<Person> personList = new ArrayList<Person>();
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.query("person", null, null, null, null, null, "personid", offset+","+maxResult);
while(cursor.moveToNext()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
int amount = cursor.getInt(cursor.getColumnIndex("amount"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
personList.add(new Person(personid, name, phone,amount));
}
cursor.close();
return personList;
}
/**
* 取得记录总数
* @return
*/
public long getCount(){
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.query("person", new String[]{"count(*)"}, null, null, null, null, null);
cursor.moveToFirst();
long result = cursor.getLong(0);
cursor.close();
return result;
}
}
以上两个方法都可以实现数据的增删改查