Android之SQlite存储
来源:互联网 发布:java字符数组赋值 编辑:程序博客网 时间:2024/05/17 01:19
使用嵌入式关系型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;}@Overridepublic 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}@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.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());}}}启用测试功能,不要忘记在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打开。
6.分页显示数据
我们在ContactsService类中,提供了一个获取分页数据的方法。我们将调用它获取的数据,使用ListView组件显示出来。
编辑mail.xml:
<?xml version="1.0" encoding="utf-8"?><LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent" > <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="fill_parent" android:layout_height="wrap_content"> <TextView android:layout_width="40px" android:layout_height="wrap_content" android:textSize="20px" android:id="@+id/personidtitle" android:text="编号" /> <TextView android:layout_width="200px" android:layout_height="wrap_content" android:textSize="20px" android:layout_toRightOf="@id/personidtitle" android:layout_alignTop="@id/personidtitle" android:gravity="center_horizontal" android:id="@+id/nametitle" android:text="姓名" /> <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:textSize="20px" android:layout_toRightOf="@id/nametitle" android:layout_alignTop="@id/nametitle" android:id="@+id/agetitle" android:text="年龄" /></RelativeLayout><ListView android:layout_width="fill_parent" android:layout_height="wrap_content" android:id="@+id/listView" /></LinearLayout>
在mail.xml所在目录里添加一个personitem.xml:
<?xml version="1.0" encoding="utf-8"?><RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="fill_parent" android:layout_height="wrap_content"> <TextView android:layout_width="40px" android:layout_height="wrap_content" android:textSize="20px" android:id="@+id/personid" /> <TextView android:layout_width="200px" android:layout_height="wrap_content" android:textSize="20px" android:layout_toRightOf="@id/personid" android:layout_alignTop="@id/personid" android:gravity="center_horizontal" android:id="@+id/name" /> <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:textSize="20px" android:layout_toRightOf="@id/name" android:layout_alignTop="@id/name" android:id="@+id/age" /></RelativeLayout>
编辑 DBActivity 类:
package com.jbridge.db;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import com.jbridge.domain.Person;import com.jbridge.service.PersonService;import android.R.string;import android.app.Activity;import android.database.Cursor;import android.os.Bundle;import android.provider.LiveFolders;import android.util.Log;import android.view.View;import android.widget.AdapterView;import android.widget.ArrayAdapter;import android.widget.ListView;import android.widget.SimpleAdapter;import android.widget.SimpleCursorAdapter;import android.widget.Toast;public class DBActivity extends Activity {/** Called when the activity is first created. */private static final String TAG = "DBActivity";/*实现方法一 @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); PersonService personService=new PersonService(this); ListView listView = (ListView) this.findViewById(R.id.listView); List<HashMap<String, String>> data = new ArrayList<HashMap<String, String>>(); // HashMap<String, String> title = new HashMap<String, String>(); // title.put("personid", "编号"); // title.put("name", "姓名"); // title.put("age", "年龄"); // data.add(title); List<Person> persons= personService.getScrollData(0, 10); for (Person person : persons) { HashMap<String, String> p = new HashMap<String, String>(); p.put("personid", String.valueOf(person.getId())); p.put("name", person.getName()); p.put("age",String.valueOf(person.getAge())); data.add(p); } // 适配器有: // ArrayAdapter<T> // simpAdapter // SimpleCursorAdapter SimpleAdapter adapter = new SimpleAdapter(DBActivity.this, data, R.layout.personitem, new String[] { "personid", "name", "age" }, new int[] {R.id.personid, R.id.name, R.id.age }); listView.setAdapter(adapter); listView.setOnItemClickListener(new AdapterView.OnItemClickListener(){ @Override // parent即为你点击的listView // view为listview的外面布局 public void onItemClick(AdapterView<?> parent, View view, int position, long id) { ListView listView= (ListView) parent; HashMap<String, String> itemdata= (HashMap<String, String>) listView.getItemAtPosition(position); String personid=itemdata.get("personid"); String name=itemdata.get("name"); String age=itemdata.get("age"); Log.i(TAG,view.getClass().getName()); Log.i(TAG, "personid: "+personid+ " name: "+name+" age: "+age); Log.i(TAG," position==id:"+ (position==id));Toast.makeText(DBActivity.this, name, Toast.LENGTH_LONG).show(); } }); }*///实现方法二(游标)@Overridepublic void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.main);PersonService personService = new PersonService(this);ListView listView = (ListView) this.findViewById(R.id.listView);List<HashMap<String, String>> data = new ArrayList<HashMap<String, String>>();// HashMap<String, String> title = new HashMap<String, String>();// title.put("personid", "编号");// title.put("name", "姓名");// title.put("age", "年龄");// data.add(title);// 适配器有:// ArrayAdapter<T>// simpAdapter// SimpleCursorAdapterCursor cursor = personService.getRawScrollData(0, 10);SimpleCursorAdapter adapter = new SimpleCursorAdapter(DBActivity.this,R.layout.personitem, cursor, new String[] { "_id", "name","age" },new int[] { R.id.personid, R.id.name, R.id.age });listView.setAdapter(adapter);listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {@Override// parent即为你点击的listView// view为listview的外面布局public void onItemClick(AdapterView<?> parent, View view,int position, long id) {ListView listView = (ListView) parent;Cursor cursor = (Cursor) listView.getItemAtPosition(position);String personid = String.valueOf(cursor.getInt(0));String name = String.valueOf(cursor.getString(1));String age = String.valueOf(cursor.getShort(2));Log.i(TAG, view.getClass().getName());Log.i(TAG, "personid: " + personid + " name: " + name+ " age: " + age);Log.i(TAG, " position==id:" + (position == id));Toast.makeText(DBActivity.this, name, Toast.LENGTH_LONG).show();}});}}
- Android之SQlite存储
- Android之SQlite存储
- Android之SQlite存储
- Android之SQlite存储
- Android之Sqlite存储
- Android之SQlite存储
- Android之SQlite存储
- Android存储之SQLite
- Android之SQlite存储
- 【Android】数据存储之SQLite
- android数据存储之SQLite
- Android数据存储之SQLite
- Android数据存储之SQLite
- Android数据存储之SQLite
- Android之SQLite 数据存储
- Android之SQLite 数据存储
- Android 数据存储之SQLite
- Android数据存储之SQLite
- nova 冷迁移配置nova用户互信
- TCP、UDP、IP 协议分析
- 读写SharedPreferences中的数据
- 【Linux】中TCP的握手与挥手
- [DP优化] POJ 1160 Post Office
- Android之SQlite存储
- Android内容提供者(Content provider)
- TST运行报错解决
- java udp简单例子
- java tcp简单例子
- Linux_博客-端口分类调研
- defy手机 小技能 问题
- spring mvc maven
- java关于多线程的面试题(三道)--参考答案