android小功能实现之SQLite数据库的基本操作
栏目：综合技术时间：2015-03-25 11:47:49
创建1个Android工程
1 新建数据库
1 新建1个类DBOpenHelper
内容以下：
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBOpenHelper extends SQLiteOpenHelper {
public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
// 数据库保持位置<包>/databases
super(context, name, factory, version);
}
@Override
// 数据库第1次被创建时调用
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE person(id integer primary key autoincrement, name varchar(20))");
}
@Override
// 数据库文件的版本号产生变更时调用
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL");
}
}
onCreate函数在数据库创建时调用；
onUpgrade在数据库版本号version产生变化时调用。
2 测试
修改MainActivity.java代码以下：
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
int version = 1;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
}
此时在data文件夹中可以看到test.db文件如图：
打开可以看到：
打开person:
修改version的值：
int version = 2;
再次查看test.db的内容：
2 数据库的基本操作
1 新建1个类Person类
用于保存对象数据，内容以下：
public class Person {
private Integer id;
private String name;
private String phone;
public Person(){
}
public Person(String name, String phone) {
this.name = name;
this.phone = phone;
}
public Person(int id, String name, String phone) {
this.id = id;
this.name = name;
this.phone = phone;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Person{" +
"id=" + id +
", name='" + name + ''' +
", phone='" + phone + ''' +
'}';
}
}
2 新建1个类PersonService类
用于对数据进行操作，内容以下：
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
import java.util.List;
public class PersonService {
private DBOpenHelper dbOpenHelper;
public PersonService(Context context) {
int version = 2;
this.dbOpenHelper = new DBOpenHelper(context, "test.db", null, version);
}
// 增
public void save(Person person){
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
db.execSQL("insert into person(name, phone) values(?, ?)", new Object[]{person.getName(), person.getPhone()});
}
// 删
public void delete(Integer id){
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
db.execSQL("delete from person where id=?", new Object[]{id});
}
// 改
public void update(Person person){
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
db.execSQL("update person set name=?,phone=? where id=?", new Object[]{person.getName(), person.getPhone(), person.getId()});
}
// 查
public Person find(Integer id){
SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from person where id=?", new String[]{id.toString()});
if (cursor.moveToFirst()){
int personId = cursor.getInt(cursor.getColumnIndex("id"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personId, name, phone);
}
cursor.close();
return null;
}
// 分页获得记录，跳过前offset条记录，查找maxResult条记录
public List<Person> getScrollData(int offset, int maxResult){
SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from person order by id asc limit ?,?",
new String[]{String.valueOf(offset), String.valueOf(maxResult)});
List<Person> persons = new ArrayList<Person>();
while (cursor.moveToNext()){
int personId = cursor.getInt(cursor.getColumnIndex("id"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
persons.add(new Person(personId, name, phone));
}
cursor.close();
return persons;
}
// 获得记录条数
public long getCount(){
SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*) from person",null);
cursor.moveToFirst();
long res = cursor.getLong(0);
cursor.close();
return res;
}
}
3 测试添加数据
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 创建数据库
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
// 数据库基本操作
PersonService service = new PersonService(getApplicationContext());
Person person = new Person("寒风", "1596262XXXX");
service.save(person);
}
再次查看test.db的内容：
4 测试查找数据
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 创建数据库
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
// 数据库基本操作
PersonService service = new PersonService(getApplicationContext());
/*
Person person = new Person("寒风", "1596262XXXX");
service.save(person);
*/
Person p = service.find(1);
Log.i("PersonService", p.toString());
}
注释掉之前添加数据的代码，讲查询出的数据打印到logcat，结果以下：
5 测试更新数据
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 创建数据库
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
// 数据库基本操作
PersonService service = new PersonService(getApplicationContext());
/*
Person person = new Person("寒风", "1596262XXXX");
service.save(person);
*/
Person p = service.find(1);
p.setName("丿寒灬风丨");
service.update(p);
Person p1 = service.find(1);
Log.i("PersonService", p1.toString());
}
修改了名称，并更新到数据库，再次履行查询结果如图：
注意，前1条是上1次输出的结果。
5 测试获得记录条数
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 创建数据库
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
// 数据库基本操作
PersonService service = new PersonService(getApplicationContext());
/*
Person person = new Person("寒风", "1596262XXXX");
service.save(person);
*/
/*
Person p = service.find(1);
p.setName("丿寒灬风丨");
service.update(p);
Log.i("PersonService", p.toString());
*/
Log.i("PersonService","count:"+ String.valueOf(service.getCount()));
}
结果如图：
5 测试分页方法
5.1 添加多条测试数据
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 创建数据库
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
// 数据库基本操作
PersonService service = new PersonService(getApplicationContext());
for (int i=0; i<20;++i) {
Person person = new Person("寒风", "1596262XXX"+i);
service.save(person);
}
/*
Person p = service.find(1);
p.setName("丿寒灬风丨");
service.update(p);
Log.i("PersonService", p.toString());
*/
// Log.i("PersonService","count:"+ String.valueOf(service.getCount()));
}
查看test.db的内容：
5.2 测试删除方法
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 创建数据库
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
// 数据库基本操作
PersonService service = new PersonService(getApplicationContext());
/*
for (int i=0; i<20;++i) {
Person person = new Person("寒风", "1596262XXX"+i);
service.save(person);
}
*/
/*
Person p = service.find(1);
p.setName("丿寒灬风丨");
service.update(p);
Log.i("PersonService", p.toString());
*/
// Log.i("PersonService","count:"+ String.valueOf(service.getCount()));
List<Person> persons = service.getScrollData(0, 5);
for(Person person:persons){
Log.i("PersonService", person.toString());
}
Log.i("PersonService","-------------------------");
List<Person> persons1 = service.getScrollData(5, 5);
for(Person person:persons1){
Log.i("PersonService", person.toString());
}
}
结果如图：
6 测试删除方法
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 创建数据库
int version = 2;
DBOpenHelper dbOpenHelper = new DBOpenHelper(getApplicationContext(), "test.db", null, version);
dbOpenHelper.getWritableDatabase();
// 数据库基本操作
PersonService service = new PersonService(getApplicationContext());
/*
for (int i=0; i<20;++i) {
Person person = new Person("寒风", "1596262XXX"+i);
service.save(person);
}
*/
/*
Person p = service.find(1);
p.setName("丿寒灬风丨");
service.update(p);
Log.i("PersonService", p.toString());
*/
// Log.i("PersonService","count:"+ String.valueOf(service.getCount()));
/*
List<Person> persons = service.getScrollData(0, 5);
for(Person person:persons){
Log.i("PersonService", person.toString());
}
Log.i("PersonService","-------------------------");
List<Person> persons1 = service.getScrollData(5, 5);
for(Person person:persons1){
Log.i("PersonService", person.toString());
}
*/
service.delete(1);
}
查看test.db的内容：
3 使用封装方法实现
SQLiteDatabase类中对SQL语句做了简单的封装，每一个方法对以下：
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
import java.util.List;
public class PersonService {
private DBOpenHelper dbOpenHelper;
public PersonService(Context context) {
int version = 2;
this.dbOpenHelper = new DBOpenHelper(context, "test.db", null, version);
}
// 增
public void save(Person person){
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
db.execSQL("insert into person(name, phone) values(?, ?)", new Object[]{person.getName(), person.getPhone()});
}
public void save1(Person person){
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", person.getName());
values.put("phone", person.getPhone());
db.insert("person",null, values);
}
// 删
public void delete(Integer id){
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
db.execSQL("delete from person where id=?", new Object[]{id});
}
public void delete1(Integer id){
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
db.delete("person", "id=?",new String[]{id.toString()});
}
// 改
public void update(Person person){
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
db.execSQL("update person set name=?,phone=? where id=?", new Object[]{person.getName(), person.getPhone(), person.getId()});
}
public void update1(Person person) {
SQLiteDatabase db = this.dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", person.getName());
values.put("phone", person.getPhone());
db.update("person",values,"id=?", new String[]{person.getId().toString()});
}
// 查
public Person find(Integer id){
SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from person where id=?", new String[]{id.toString()});
if (cursor.moveToFirst()){
int personId = cursor.getInt(cursor.getColumnIndex("id"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personId, name, phone);
}
cursor.close();
return null;
}
public Person find1(Integer id) {
SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();
//db.query("person", null, "id=?", new String[]{id.toString()},null, null,null);
Cursor cursor = db.query("person", new String[]{"id","name","phone"}, "id=?", new String[]{id.toString()},null, null,null);
if (cursor.moveToFirst()){
int personId = cursor.getInt(cursor.getColumnIndex("id"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personId, name, phone);
}
cursor.close();
return null;
}
// 分页获得记录
public List<Person> getScrollData(int offset, int maxResult){
SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from person order by id asc limit ?,?",
new String[]{String.valueOf(offset), String.valueOf(maxResult)});
List<Person> persons = new ArrayList<Person>();
while (cursor.moveToNext()){
int personId = cursor.getInt(cursor.getColumnIndex("id"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
persons.add(new Person(personId, name, phone));
}
cursor.close();
return persons;
}
public List<Person> getScrollData1(int offset, int maxResult){
SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();
Cursor cursor = db.query("person", null, null,null,null,null,"id asc", offset+","+maxResult);
new String[]{String.valueOf(offset), String.valueOf(maxResult)});
List<Person> persons = new ArrayList<Person>();
while (cursor.moveToNext()){
int personId = cursor.getInt(cursor.getColumnIndex("id"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
persons.add(new Person(personId, name, phone));
}
cursor.close();
return persons;
}
// 获得记录条数
public long getCount(){
SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*) from person",null);
cursor.moveToFirst();
long res = cursor.getLong(0);
cursor.close();
return res;
}
public long getCount1(){
SQLiteDatabase db = this.dbOpenHelper.getReadableDatabase();
Cursor cursor = db.query("person", new String[]{"count(*)"},null,null,null,null,null);
cursor.moveToFirst();
long res = cursor.getLong(0);
cursor.close();
return res;
}
}
------分隔线----------------------------
------分隔线----------------------------