android 数据存储之SQLite
使用嵌入式关系型SQLite数据库存储数据
除了可以使用文件或SharedPreferences存储数据,还可以选择使用SQLite数据库存储数据。
在Android平台上,集成了一个嵌入式关系型数据库—SQLite,
1、SQLite3支持 NULL、INTEGER、REAL(浮点数字)、TEXT(字符串文本)和BLOB(二进制对象)数据类型,虽然它支持的类型虽然只有五种,但实际上sqlite3也接受varchar(n)、char(n)、decimal(p,s) 等数据类型,只不过在运算或保存时会转成对应的五种数据类型。
2、SQLite最大的特点是你可以保存任何类型的数据到任何字段中,无论这列声明的数据类型是什么。例如:可以在Integer字段中存放字符串,或者在布尔型字段中存放浮点数,或者在字符型字段中存放日期型值。
3、但有一种情况例外:定义为INTEGER PRIMARY KEY的字段只能存储64位整数, 当向这种字段中保存除整数以外的数据时,将会产生错误。
4、另外, SQLite 在解析CREATE TABLE 语句时,会忽略 CREATE TABLE 语句中跟在字段名后面的数据类型信息,如下面语句会忽略 name字段的类型信息:
CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))
SQLite可以解析大部分标准SQL语句,如:
查询语句:select * from 表名 where 条件子句 group by 分组字句 having ... order by 排序子句
如:select * from person
select * from person order by id desc
select name from person group by name having count(*)>1
分页SQL与mysql类似,下面SQL语句获取5条记录,跳过前面3条记录
select * from Account limit 5 offset 3 或者 select * from Account limit 3,5
插入语句:insert into 表名(字段列表) values(值列表)。如: insert into person(name, age) values(‘传智’,3)
更新语句:update 表名 set 字段名=值 where 条件子句。如:update person set name=‘传智‘ where id=10
删除语句:delete from 表名 where 条件子句。如:delete from person where id=10
1.创建Android工程
Project name: db
BuildTarget:Android2.2
Application name: 数据库应用
Package name: com.jbridge.db
Create Activity: DBActivity
Min SDK Version:8、
2. Person实体
package com.jbridge.domain; import android.R.string; public class Person {
private Integer id;
private String name;
private Short age; public Person(String name, Short age) {
this.name = name;
this.age = age;
} public Person(Integer id, String name, Short age) {
super();
this.id = id;
this.name = name;
this.age = age;
} 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 Short getAge() {
return age;
} public void setAge(Short age) {
this.age = age;
} @Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", age=" + age + "]";
} }
3.编写DataBaseOpenHelper类
DataBaseOpenHelper继承自SQLiteOpenHelper类。我们需要创建数据表,必须重写onCreate(更新时重写onUpgrade方法)方法,在这个方法中创建数据表。
package com.jbridge.service; import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper; public class DataBaseOpenHelper extends SQLiteOpenHelper {
// 类没有实例化,是不能用作父类构造器的参数,必须声明为静态
private static String dbname = "zyj";
private static int version = 1; public DataBaseOpenHelper(Context context) {
// 第一个参数是应用的上下文
// 第二个参数是应用的数据库名字
// 第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类,设置为null,代表使用系统默认的工厂类
// 第四个参数是数据库版本,必须是大于0的int(即非负数)
super(context, dbname, null, version);
// TODO Auto-generated constructor stub
} public DataBaseOpenHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
} @Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS person (personid integer primary key autoincrement, name varchar(20), age INTEGER)");
} // onUpgrade()方法在数据库版本每次发生变化时都会把用户手机上的数据库表删除,然后再重新创建。
// 一般在实际项目中是不能这样做的,正确的做法是在更新数据库表结构时,还要考虑用户存放于数据库中的数据不会丢失,从版本几更新到版本几。
@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
db.execSQL("DROP TABLE IF EXISTS person");
onCreate(db);
} }
4.编写PersonService类
PersonService类主要实现对业务逻辑和数据库的操作
package com.jbridge.service; import java.util.ArrayList;
import java.util.Currency;
import java.util.List; import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase; import com.jbridge.domain.Person; public class PersonService { private DataBaseOpenHelper dbOpenHelper; // private Context context; public PersonService(Context context) {
// this.context = context;
dbOpenHelper = new DataBaseOpenHelper(context);
} public void save(Person person) {
SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
database.beginTransaction();
database.execSQL("insert into person(name,age)values(?,?)",
new Object[] { person.getName(), person.getAge() });
// database.close();可以不关闭数据库,他里面会缓存一个数据库对象,如果以后还要用就直接用这个缓存的数据库对象。但通过
// context.openOrCreateDatabase(arg0, arg1, arg2)打开的数据库必须得关闭
database.setTransactionSuccessful();
database.endTransaction(); } public void update(Person person) {
SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
database.execSQL(
"update person set name=?,age=? where personid=?",
new Object[] { person.getName(), person.getAge(),
person.getId() });
} public Person find(Integer id) {
SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
Cursor cursor = database.rawQuery(
"select * from person where personid=?",
new String[] { String.valueOf(id) });
if (cursor.moveToNext()) {
return new Person(cursor.getInt(0), cursor.getString(1),
cursor.getShort(2));
}
return null;
} public void delete(Integer... ids) {
if (ids.length > 0) {
StringBuffer sb = new StringBuffer();
for (Integer id : ids) {
sb.append('?').append(',');
}
sb.deleteCharAt(sb.length() - 1);
SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
database.execSQL(
"delete from person where personid in(" + sb.toString()
+ ")", ids);
}
} public List<Person> getScrollData(int startResult, int maxResult) {
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
Cursor cursor = database.rawQuery(
"select * from person limit ?,?",
new String[] { String.valueOf(startResult),
String.valueOf(maxResult) });
while (cursor.moveToNext()) {
persons.add(new Person(cursor.getInt(0), cursor.getString(1),
cursor.getShort(2)));
}
return persons;
} // 获取分页数据,提供给SimpleCursorAdapter使用。
public Cursor getRawScrollData(int startResult, int maxResult) {
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
return database.rawQuery(
"select personid as _id ,name,age from person limit ?,?",
new String[] { String.valueOf(startResult),
String.valueOf(maxResult) }); } public long getCount() {
SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
Cursor cursor = database.rawQuery("select count(*) from person", null);
if (cursor.moveToNext()) {
return cursor.getLong(0);
}
return 0;
} }
下面是使用 insert()、delete()、update()和query()方法实现的业务类
package com.jbridge.service; import java.util.ArrayList;
import java.util.Currency;
import java.util.List; import android.R.string;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase; import com.jbridge.domain.Person; public class OtherPersonService { private DataBaseOpenHelper dbOpenHelper; // private Context context; public OtherPersonService(Context context) {
// this.context = context;
dbOpenHelper = new DataBaseOpenHelper(context);
} public void save(Person person) {
SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("name", person.getName());
contentValues.put("age", person.getAge());
database.insert("person", null, contentValues);
} public void update(Person person) {
SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("name", person.getName());
contentValues.put("age", person.getAge());
database.update("person", null, "personid=?",
new String[] { String.valueOf(person.getId()) });
} public Person find(Integer id) {
SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
Cursor cursor = database.query("person", new String[] { "personid",
"name", "age" }, "personid=?",
new String[] { String.valueOf(id) }, null, null, null);
if (cursor.moveToNext()) {
return new Person(cursor.getInt(0), cursor.getString(1),
cursor.getShort(2));
}
return null;
} public void delete(Integer... ids) {
if (ids.length > 0) {
StringBuffer sb = new StringBuffer();
String[] strIds = new String[ids.length];
// for (Integer id : ids) {
// sb.append('?').append(',');
// }
for (int i = 0; i < strIds.length; i++) {
sb.append('?').append(',');
strIds[i] = String.valueOf(ids[i]);
}
sb.deleteCharAt(sb.length() - 1);
SQLiteDatabase database = dbOpenHelper.getWritableDatabase();
database.delete("person", "personid in(" + sb.toString() + ")",
strIds);
}
} public List<Person> getScrollData(int startResult, int maxResult) {
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
Cursor cursor = database.query("person", new String[] { "personid",
"name", "age" }, null, null, null, null, "personid desc",
startResult + "," + maxResult);
while (cursor.moveToNext()) {
persons.add(new Person(cursor.getInt(0), cursor.getString(1),
cursor.getShort(2)));
}
return persons;
} public long getCount() {
SQLiteDatabase database = dbOpenHelper.getReadableDatabase();
Cursor cursor = database.query("person", new String[] { "count(*)" },
null, null, null, null, null);
if (cursor.moveToNext()) {
return cursor.getLong(0);
}
return 0;
} }
5.编写测试类
编写一个针对PersonService的测试类,测试PersonService类中的各个方法是否正确
package com.jbridge.db; import java.util.List; import com.jbridge.domain.Person;
import com.jbridge.service.OtherPersonService;
import com.jbridge.service.PersonService; import android.test.AndroidTestCase;
import android.util.Log; public class PersonServiceTest extends AndroidTestCase {
private static String TAG = "PersonServiceTest"; // OtherPersonService personService = new
// OtherPersonService(this.getContext());
// //不可以这么写,因为Android把context环境变量是在PersonServiceTest实例化后给他的 public void testSave() throws Exception {
PersonService personService = new PersonService(this.getContext());
// personService.save(new Person("老猪", (short) 11));
for (int i = 0; i < 10; i++) {
personService.save(new Person("你" + i, (short) (i + 10)));
} } public void testFind() throws Exception {
PersonService personService = new PersonService(this.getContext());
Person person = personService.find(1);
Log.i(TAG, person.toString());
} public void testUpdate() throws Exception {
PersonService personService = new PersonService(this.getContext());
Person person = personService.find(1);
person.setName("lv");
personService.update(person);
} public void testDelete() throws Exception {
PersonService personService = new PersonService(this.getContext());
personService.delete(1, 2, 3);
} public void testGetCount() throws Exception {
PersonService personService = new PersonService(this.getContext());
Log.i(TAG, String.valueOf(personService.getCount()));
} public void testGetScrollData() throws Exception {
PersonService personService = new PersonService(this.getContext());
List<Person> persons = personService.getScrollData(0, 3);
for (Person person : persons) {
Log.i(TAG, person.toString());
}
}
}
最新文章
- SSISDB2:使用TSQL执行Package
- [WPF系列]-数据邦定之DataTemplate 对 ItemsControl 进行样式和模板处理
- oracle 执行计划查看
- WCF服务创建与使用(双工模式)
- Bug避障算法简介
- 【疯狂Java学习笔记】【理解面向对象】
- HDU 3507 Print Article(DP+斜率优化)
- 1169 二叉树遍历(XCOJ DFS)
- yum添加源。
- MQTT协议详解一
- 动动手,写个knockout的分页模板
- OAuth2.0学习(1-7)授权方式4-客户端模式(Client Credentials Grant)
- 记录一次有意思的XSS过滤绕过2
- [译文]Domain Driven Design Reference(七)—— 大型战略设计结构
- Python内置函数(26)——globals
- ubuntu16.04下 搭建 lnmp 环境
- 在py文件中设置文件头
- 【2】Kali之情报搜集技术
- SkylineGlobe 如何二次开发获取三维模型的BBOX和设置Tint属性
- C#_基础题1-10套答案
热门文章
- [BZOJ 3132] 上帝造题的七分钟
- Coursera Algorithms week4 基础标签表 练习测验:Inorder traversal with constant extra space
- P4280 [AHOI2008]逆序对
- call( )、apply( )
- NOIP真题汇总
- Application、Activity Stack 和 Task的区别
- mysql使用出现错误:mysqld dead but subsys locked
- Java系列学习(零)-写在前面的话
- iOS CoreData 开发之数据模型关系
- JS高级——函数的调用模式