android学习笔记之SQLite数据库存储
来源:互联网 发布:js offsetleft 菜鸟 编辑:程序博客网 时间:2024/04/25 09:17
在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
SQLiteDatabase db = ....;db.execSQL("insert into person(name, age) values('传智播客', 4)");db.close();
SQLiteDatabase db = ....;db.execSQL("insert into person(name, age) values(?,?)", new Object[]{"传智播客", 4}); db.close();execSQL(String sql, Object[] bindArgs)方法的第一个参数为SQL语句,第二个参数为SQL语句中占位符参数的值,参数值在数组中的顺序要和占位符的位置对应。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();
SQLiteDatabase db = databaseHelper.getWritableDatabase();ContentValues values = new ContentValues();values.put("name", "传智播客");values.put("age", 4);long rowid = db.insert(“person”, null, values);//返回新添记录的行号,与主键id无关
SQLiteDatabase db = databaseHelper.getWritableDatabase();db.delete("person", "personid<?", new String[]{"2"});db.close();
SQLiteDatabase db = databaseHelper.getWritableDatabase();ContentValues values = new ContentValues();values.put(“name”, “传智播客”);//key为字段名,value为值db.update("person", values, "personid=?", new String[]{"1"}); db.close();
SQLiteDatabase db = databaseHelper.getWritableDatabase();Cursor cursor = db.query("person", new String[]{"personid,name,age"}, "name like ?", new String[]{"%溧阳%"}, null, null, "personid desc", "1,2");while (cursor.moveToNext()) { int personid = cursor.getInt(0); //获取第一列的值,第一列的索引从0开始 String name = cursor.getString(1);//获取第二列的值 int age = cursor.getInt(2);//获取第三列的值}cursor.close();db.close();
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("DROP TABLE IF EXISTS person"); onCreate(db); }}
public class DatabaseHelper extends SQLiteOpenHelper { private static final String name = "itcast"; //数据库名称 private static final int version = 1; //数据库版本 ......略}public class HelloActivity extends Activity { @Override public void onCreate(Bundle savedInstanceState) { ...... Button button =(Button) this.findViewById(R.id.button); button.setOnClickListener(new View.OnClickListener(){public void onClick(View v) {DatabaseHelper databaseHelper = new DatabaseHelper(HelloActivity.this);SQLiteDatabase db = databaseHelper.getWritableDatabase();db.execSQL("insert into person(name, age) values(?,?)", new Object[]{"传智播客", 4}); db.close(); }}); }}
SQLiteDatabase db = ....;db.beginTransaction();//开始事务try { db.execSQL("insert into person(name, age) values(?,?)", new Object[]{"传智播客", 4}); db.execSQL("update person set name=? where personid=?", new Object[]{"传智", 1}); db.setTransactionSuccessful();//调用此方法会在执行到endTransaction() 时提交当前事务,如果不调用此方法会回滚事务} finally { db.endTransaction();//由事务的标志决定是提交事务,还是回滚事务} db.close();
Project name: DataStore
BuildTarget:Android4.0.3
Application name: 数据库应用
Package name: com.android.datastore
Create Activity: DBActivity
Min SDK Version:15
2、Person实体:
package com.android.domain;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;}@Overridepublic String toString() {return "Person [id=" + id + ", name=" + name + ", age=" + age + "]";}}
3.编写DataBaseOpenHelper类
DataBaseOpenHelper继承自SQLiteOpenHelper类。我们需要创建数据表,必须重写onCreate(更新时重写onUpgrade方法)方法,在这个方法中创建数据表。
package com.android.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}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL("CREATE TABLE IF NOT EXISTS person (personid integer primary key autoincrement, name varchar(20), age INTEGER)");}// onUpgrade()方法在数据库版本每次发生变化时都会把用户手机上的数据库表删除,然后再重新创建。// 一般在实际项目中是不能这样做的,正确的做法是在更新数据库表结构时,还要考虑用户存放于数据库中的数据不会丢失,从版本几更新到版本几。@Overridepublic void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {db.execSQL("DROP TABLE IF EXISTS person");onCreate(db);}}
4.编写PersonService类
PersonService类主要实现对业务逻辑和数据库的操作。
package com.android.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.android.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) {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.android.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.android.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.android.db;//import com.android.service.OtherPersonService;import com.android.domain.Person;import com.android.service.PersonService;import java.util.List;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());}}}启用测试功能,不要忘记在AndroidManifest.xml文件中加入测试环境。为application元素添加一个子元素:<uses-library android:name="android.test.runner"/>,为application元素添加一个兄弟元素:<instrumentation android:name="android.test.InstrumentationTestRunner" android:targetPackage="com.jbridge.db" android:label="Tests for My App" />。
SQLite数据库以单个文件存储,就像微软的Access数据库。有一个查看SQLite数据库文件的工具——SQLite Developer,我们可以使用它来查看数据库。Android将创建的数据库存放在”/data/data/ com.jbridge.db/databases/person”,我们将它导出然后使用SQLite Developer打开。
- android学习笔记之SQLite数据库存储
- Android学习笔记034之数据存储—SQLite数据库
- Android学习笔记三十四之数据存储—SQLite数据库
- 【Android学习笔记】SQLite数据库存储
- Android笔记之存储(SQLite数据库)
- 数据存储之SQLite 数据库存储——第一行代码Android学习笔记
- 学习笔记 android数据库之 Sqlite
- android学习笔记之SQLite数据库
- android学习---SQLite数据库存储
- Android存储之SQLite数据库
- android存储之SQLite数据库
- Android 存储学习之SQLite数据库的基本操作
- Android数据存储之SQLite数据库存储
- Android数据存储之SQLite数据库存储
- Android数据存储之SQLite数据库存储
- Android数据存储之:SQLite数据库存储
- Android 数据存储之SQLite数据库存储
- Android数据存储之SQLite数据库存储
- OpenStack架构图
- jsp回车事件
- 参数FAST_START_MTTR_TARGET的理解
- net中如何获取浏览器类型
- Looper源码解析
- android学习笔记之SQLite数据库存储
- UIPageControl的详细使用 (与uiscrollview配合使用)
- apache
- JS调用后台方法
- UIScrollView 原理详解
- win7无法共享无线网络的一种解决方式
- Oracle数据库逻辑增量备份恢复之exp/imp
- SQL语句
- MessageQueue源码解析