从零开始学android<数据存储（4）Sqlite数据库存储.三十八.>

从前几章我们分别学习了属性文件存储输数据，内储存存储数据，和外部储存存储数据，今天我们来学习一下android 轻量级数据库Sqlite数据库的数据存储

首先必须了解SQLiteOpenHelper

SQLiteDatabase类本身只是一个数据库的操作类，但是如果要想进行数据库的操作，还需要一个android.database.sqlite.SQLiteOpenHelper类帮助下才可以取得进行，但是，SQLiteOpenHelper类是一个抽象类，所以要使用的时候需要定义其子类，并且在子类中要覆写相应的抽象方法

让一个类继承SQLiteOpenHelper 并=并且复写SQLiteOpenHelper的方法实现

SQLiteOpenHelper常用的方法

1

public SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)

构造

通过此构造方法指明要操作的数据库名称以及数据库的版本编号

2

public synchronized void close()

普通

关闭数据库

3

public synchronized SQLiteDatabase getReadableDatabase()

普通

以只读的方式创建或者打开数据库

4

public synchronized SQLiteDatabase getWritableDatabase()

普通

以修改的方式创建或者打开数据库

5

public abstract void onCreate(SQLiteDatabase db)

普通

创建数据表

6

public void onOpen(SQLiteDatabase db)

普通

打开数据表

7

public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

普通

更新数据表

三个回调方法

1onCreate()：在第一次使用数据库的时候会调用此方法生成相应的数据库表，但是此方法并不是说是在实例化SQLiteOpenHelper类的对象时调用，而是通过对象调用了getReadableDatabase()或getWritableDatabase()方法时才会调用；

2onUpgrade()：当数据库需要进行升级的时候，会调用此方法，一般可以在此方法之中将数据表删除，并且在删除表之后往往会调用onCreate()方法以重新创建新的数据表；

3open()：当数据库打开的时候会调用此方法，但是一般情况下用户不需要覆写此方法。

接下来我们几个列子来学习下sqlite数据库

---

首先是数据库的创建

html文件

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"    xmlns:tools="http://schemas.android.com/tools"    android:layout_width="match_parent"    android:layout_height="match_parent"    android:paddingBottom="@dimen/activity_vertical_margin"    android:paddingLeft="@dimen/activity_horizontal_margin"    android:paddingRight="@dimen/activity_horizontal_margin"    android:paddingTop="@dimen/activity_vertical_margin"    tools:context=".MainActivity" >    <Button        android:id="@+id/button1"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignParentTop="true"        android:layout_centerHorizontal="true"        android:layout_marginTop="108dp"        android:text="建立数据库" /></RelativeLayout>

JAVA文件
1让一个类继承SQLiteOpenHelper

2主类

package com.example.sqllitedatabase1;import java.sql.Date;import android.content.Context;import android.database.DatabaseErrorHandler;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class SqlIteOpenHelper extends SQLiteOpenHelper {public static final String DBNAME = "flyou";//数据库名称public static final int VERSION = 2;//数据库版本public static final String TABLENAME = "user_info";//数据表名称public SqlIteOpenHelper(Context context) {super(context, DBNAME, null, VERSION);// TODO Auto-generated constructor stub}@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stub

//sql语句创建数据表String sql = "CREATE TABLE " + TABLENAME + " ("+ "idINTEGER PRIMARY KEY ,"+ "nameVARCHAR(50)NOT NULL ," + "birthdayDATENOT NULL)";// SQL语句db.execSQL(sql);}@Override//更新回掉操作public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stubString sql = "DROP TABLE IF EXISTS " + TABLENAME;db.execSQL(sql);onCreate(db);}}

运行后可以在data/data/包名/databases中查看数据库文件

2.使用helper类数据库的增加，删除，更新操作

同样需要一个类继承SQLiteOpenHelper

另外可以定义一个操作数据库的类来实现操作

HTML文件

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"    xmlns:tools="http://schemas.android.com/tools"    android:layout_width="match_parent"    android:layout_height="match_parent"    android:paddingBottom="@dimen/activity_vertical_margin"    android:paddingLeft="@dimen/activity_horizontal_margin"    android:paddingRight="@dimen/activity_horizontal_margin"    android:paddingTop="@dimen/activity_vertical_margin"    tools:context=".MainActivity" >    <TextView        android:id="@+id/textView1"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignBottom="@+id/editText1"        android:layout_alignLeft="@+id/textView2"        android:text="姓        名" />    <Button        android:id="@+id/button1"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignParentLeft="true"        android:layout_alignParentRight="true"        android:layout_below="@+id/editText2"        android:text="插入数据" />    <EditText        android:id="@+id/editText3"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignRight="@+id/button1"        android:layout_below="@+id/button1"        android:ems="10" >        <requestFocus />    </EditText>    <EditText        android:id="@+id/editText5"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignRight="@+id/editText4"        android:layout_below="@+id/editText4"        android:ems="10" />    <TextView        android:id="@+id/textView3"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignBottom="@+id/editText3"        android:layout_alignParentLeft="true"        android:text="I            D" />    <TextView        android:id="@+id/textView4"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_above="@+id/editText5"        android:layout_alignParentLeft="true"        android:text="姓         名" />    <TextView        android:id="@+id/textView5"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignBottom="@+id/editText5"        android:layout_alignParentLeft="true"        android:text="出生日期" />    <Button        android:id="@+id/button2"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignParentLeft="true"        android:layout_alignParentRight="true"        android:layout_below="@+id/editText5"        android:text="更新数据" />    <EditText        android:id="@+id/editText1"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:layout_alignLeft="@+id/editText2"        android:layout_alignParentTop="true"        android:ems="10" />    <EditText        android:id="@+id/editText2"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignRight="@+id/button1"        android:layout_below="@+id/editText1"        android:ems="10" />    <TextView        android:id="@+id/textView2"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignBottom="@+id/editText2"        android:layout_alignRight="@+id/textView4"        android:text="出生日期" />    <EditText        android:id="@+id/editText4"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignLeft="@+id/editText3"        android:layout_below="@+id/editText3"        android:ems="10" />    <Button        android:id="@+id/button3"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignLeft="@+id/textView2"        android:layout_alignParentRight="true"        android:layout_below="@+id/button2"        android:layout_marginTop="22dp"        android:text="删除数据" /></RelativeLayout>

JAVA文件

创建数据表

package com.example.sqllitedatabase2;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class SqlIteOpenHelper extends SQLiteOpenHelper {public static final String DBNAME = "flyou";public static final int VERSION = 2;public static final String TABLENAME = "user_info";public SqlIteOpenHelper(Context context) {super(context, DBNAME, null, VERSION);// TODO Auto-generated constructor stub}@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stubString sql = "CREATE TABLE " + TABLENAME + " ("+ "idINTEGER PRIMARY KEY ,"+ "nameVARCHAR(50)NOT NULL ," + "birthdayDATENOT NULL)";// SQL语句db.execSQL(sql);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stubString sql = "DROP TABLE IF EXISTS " + TABLENAME;db.execSQL(sql);onCreate(db);}}

操作数据表

package com.example.sqllitedatabase2;import android.database.sqlite.SQLiteDatabase;public class Operator {private SQLiteDatabase db = null;public Operator(SQLiteDatabase db) {this.db = db;}// 数据插入public void insert(String name, String birthday) {String sql = "insert into  " + SqlIteOpenHelper.TABLENAME+ "  (name,birthday) Values(?,?)";Object args[] = { name, birthday };db.execSQL(sql, args);}// 数据更新public void upDate(int id, String name, String birthday) {String sql = "update " + SqlIteOpenHelper.TABLENAME+ " set name=?,birthday=? where id=?";Object args[] = { id, name, birthday };db.execSQL(sql, args);}// 数据删除public void delete(int id) {String sql = "delete from " + SqlIteOpenHelper.TABLENAME + " where id=?";Object args[] = { id };db.execSQL(sql, args);}}

+

将数据导出后打开

使用sqliteDatabase自带的方法方法进行数据的CRUD操作

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"    xmlns:tools="http://schemas.android.com/tools"    android:layout_width="match_parent"    android:layout_height="match_parent"    android:paddingBottom="@dimen/activity_vertical_margin"    android:paddingLeft="@dimen/activity_horizontal_margin"    android:paddingRight="@dimen/activity_horizontal_margin"    android:paddingTop="@dimen/activity_vertical_margin"    tools:context=".MainActivity" >    <Button        android:id="@+id/button1"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignParentTop="true"        android:layout_centerHorizontal="true"        android:layout_marginTop="19dp"        android:text="插入数据" />    <Button        android:id="@+id/button2"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignLeft="@+id/button1"        android:layout_below="@+id/button1"        android:layout_marginTop="31dp"        android:text="更新数据" />    <Button        android:id="@+id/button3"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignRight="@+id/button2"        android:layout_below="@+id/button2"        android:layout_marginTop="26dp"        android:text="删除数据" /></RelativeLayout>

JAVA文件

数据库建立

package com.example.sqlitedatabase3;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class Helper extends SQLiteOpenHelper {public static final String DBNAME = "flyou.db";public static final String TABLENAME = "user_info";public Helper(Context context) {super(context, DBNAME, null, 2);// TODO Auto-generated constructor stub}@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stubString sql = "CREATE TABLE " + TABLENAME + " ("+ "idINTEGER PRIMARY KEY ,"+ "nameVARCHAR(50)NOT NULL ," + "birthdayDATENOT NULL)";// SQL语句db.execSQL(sql);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stubString sql = "DROP TABLE IF EXISTS " + TABLENAME;db.execSQL(sql);onCreate(db);}}

数据库操作

package com.example.sqlitedatabase3;import android.content.ContentValues;import android.database.sqlite.SQLiteDatabase;import android.provider.MediaStore.Video;public class Operator {private SQLiteDatabase db=null;public Operator(SQLiteDatabase db) {this.db = db;}//插入数据public void insert(String name,String birthday){Boolean flag=false;ContentValues cv=new ContentValues();cv.put("name", name);cv.put("birthday", birthday);db.insert(Helper.TABLENAME, null,cv );}//更新数据public void upDate(String name,String birthday,int id){ContentValues cv=new ContentValues();cv.put("name", name);cv.put("birthday", birthday);String whereClause="id=?";String whereArgs[]={String.valueOf(id)};db.update(Helper.TABLENAME, cv, whereClause, whereArgs);}//删除数据public void delete(int id){String whereClause="id=?";String whereArgs[]={String.valueOf(id)};db.delete(Helper.TABLENAME, whereClause, whereArgs);}}

主文件

package com.example.sqlitedatabase3;import android.os.Bundle;import android.app.Activity;import android.database.sqlite.SQLiteDatabase;import android.view.Menu;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;public class MainActivity extends Activity {private Button insert,upDate,delete;Helper helper;SQLiteDatabase database;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);    insert=(Button)this.findViewById(R.id.button1);    upDate=(Button)this.findViewById(R.id.button2);    delete=(Button)this.findViewById(R.id.button3);//    插入事件的监听    insert.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubhelper=new Helper(MainActivity.this);database=helper.getWritableDatabase();new Operator(database).insert("房泽龙", "1992-03-18");database.close();}});//    更新事件的监听    upDate.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubhelper=new Helper(MainActivity.this);database=helper.getWritableDatabase();new Operator(database).upDate("flyou", "1992-03-18", 1);database.close();}});//    数据的删除    delete.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubhelper=new Helper(MainActivity.this);database=helper.getWritableDatabase();new Operator(database).delete(3);}});    }    }

导出后打开

由于点击了多次插入，可以看见里面已经存在了多条数据

数据的查询操作

数据库查询需要使用到 cursor类

一下为常用的方法

1

public abstract void close()

普通

关闭查询

2

public abstract int getCount()

普通

返回查询的数据量

3

public abstract int getColumnCount()

普通

返回查询结果之中列的总数

4

public abstract String[] getColumnNames()

普通

得到查询结果之中全部列的名称

5

public abstract String getColumnName(int columnIndex)

普通

得到指定索引位置列的名称

6

public abstract boolean isAfterLast()

普通

判断结果集指针是否在最后一行数据之后

7

public abstract boolean isBeforeFirst()

普通

判断结果集指针是否在第一行记录之前

8

public abstract boolean isClosed()

普通

判断结果集是否已关闭

9

public abstract boolean isFirst()

普通

判断结果集指针是否指在第一行

10

public abstract boolean isLast()

普通

判断结果集指针是否指在最后一行

11

public abstract boolean moveToFirst()

普通

将结果集指针移到第一行

12

public abstract boolean moveToLast()

普通

将结果集指针移动到最后一行

13

public abstract boolean moveToNext()

普通

将结果集指针向下移动一行

14

public abstract boolean moveToPrevious()

普通

将结果集指针向前移动一行

15

public abstract boolean requery()

普通

更新数据后刷新结果集中的内容

16

public abstract int getXxx(int columnIndex)

普通

根据指定列的索引取得指定的数据

一般使用for循环对cursor进行结果的遍历

for (result.moveToFirst(); !result.isAfterLast(); result.moveToNext()) {

循环体 ;

}

下面看例子

HTML文件

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"    xmlns:tools="http://schemas.android.com/tools"    android:layout_width="match_parent"    android:layout_height="match_parent"    android:paddingBottom="@dimen/activity_vertical_margin"    android:paddingLeft="@dimen/activity_horizontal_margin"    android:paddingRight="@dimen/activity_horizontal_margin"    android:paddingTop="@dimen/activity_vertical_margin"    tools:context=".MainActivity" >    <Button        android:id="@+id/button1"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignParentLeft="true"        android:layout_alignParentTop="true"        android:text="插入数据" />    <Button        android:id="@+id/button2"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignBaseline="@+id/button1"        android:layout_alignBottom="@+id/button1"        android:layout_centerHorizontal="true"        android:text="更新数据" />    <Button        android:id="@+id/button3"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignBaseline="@+id/button2"        android:layout_alignBottom="@+id/button2"        android:layout_toRightOf="@+id/button2"        android:text="删除数据" />    <Button        android:id="@+id/button4"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignLeft="@+id/button1"        android:layout_alignRight="@+id/button3"        android:layout_below="@+id/button1"        android:text="全部查询" />    <Button        android:id="@+id/button5"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignRight="@+id/button4"        android:layout_below="@+id/button4"        android:text="模糊查询" />    <EditText        android:id="@+id/editText1"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignParentLeft="true"        android:layout_alignRight="@+id/button2"        android:layout_below="@+id/button4"        android:ems="10" >        <requestFocus />    </EditText>    <ListView        android:id="@+id/listView1"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:layout_below="@+id/button5"        android:layout_centerHorizontal="true" >    </ListView></RelativeLayout>

创建数据库

package com.example.sqlitedatabase4;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class Helper extends SQLiteOpenHelper {public static final String DBNAME = "flyou.db";public static final String TABLENAME = "user_info";public Helper(Context context) {super(context, DBNAME, null, 2);// TODO Auto-generated constructor stub}@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stubString sql = "CREATE TABLE " + TABLENAME + " ("+ "idINTEGER PRIMARY KEY ,"+ "nameVARCHAR(50)NOT NULL ," + "birthdayDATENOT NULL)";// SQL语句db.execSQL(sql);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stubString sql = "DROP TABLE IF EXISTS " + TABLENAME;db.execSQL(sql);onCreate(db);}}

数据的CURD操作

package com.example.sqlitedatabase4;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import android.R.integer;import android.content.ContentValues;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.provider.MediaStore.Video;import android.widget.ListView;public class Operator {private SQLiteDatabase db = null;public Operator(SQLiteDatabase db) {this.db = db;}// 插入数据public void insert(String name, String birthday) {Boolean flag = false;ContentValues cv = new ContentValues();cv.put("name", name);cv.put("birthday", birthday);db.insert(Helper.TABLENAME, null, cv);}// 更新数据public void upDate(String name, String birthday, int id) {ContentValues cv = new ContentValues();cv.put("name", name);cv.put("birthday", birthday);String whereClause = "id=?";String whereArgs[] = { String.valueOf(id) };db.update(Helper.TABLENAME, cv, whereClause, whereArgs);}// 删除数据public void delete(int id) {String whereClause = "id=?";String whereArgs[] = { String.valueOf(id) };db.delete(Helper.TABLENAME, whereClause, whereArgs);}// 查找全部数据public List<String> findAll() {List<String> all = new ArrayList<String>();//String sql = "select * from " + Helper.TABLENAME;//Cursor result = db.rawQuery(sql, null);String columns[]={"id","name","birthday"};Cursor result=this.db.query(Helper.TABLENAME, columns, null, null, null, null, null);for (result.moveToFirst(); !result.isAfterLast(); result.moveToNext()) {all.add("[" + result.getInt(0) + "]--" + result.getString(1)+ "--" + result.getString(2));}return all;}//模糊查询public List<String>findBy(String keyword){List<String> all = new ArrayList<String>();  String sql="select *from "+Helper.TABLENAME+" where name like ? or birthday like ? or id like ?";   String contexstr[]={"%"+keyword+"%","%"+keyword+"%","%"+keyword+"%"}; Cursor result=this.db.rawQuery(sql,contexstr); //-----sqlite自带查询------//String columns[]={"id","name","birthday"};//设置lie的名字//Cursor result=this.db.query(TABLE, columns, null, null, null, null, null);//-----------------------for (result.moveToFirst(); !result.isAfterLast(); result.moveToNext()) {all.add("[" + result.getInt(0) + "]--" + result.getString(1)+ "--" + result.getString(2));}result.close();db.close();return all;}}

UI线程

package com.example.sqlitedatabase4;import java.util.List;import android.app.Activity;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.view.View;import android.view.View.OnClickListener;import android.widget.ArrayAdapter;import android.widget.Button;import android.widget.EditText;import android.widget.ListView;public class MainActivity extends Activity {private Button insert, upDate, delete, findAll, findBy;private EditText keyword;private ListView list;List<String> data;Helper helper;SQLiteDatabase database;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);insert = (Button) this.findViewById(R.id.button1);upDate = (Button) this.findViewById(R.id.button2);delete = (Button) this.findViewById(R.id.button3);findAll = (Button) this.findViewById(R.id.button4);findBy = (Button) this.findViewById(R.id.button5);keyword=(EditText)this.findViewById(R.id.editText1);list = (ListView) this.findViewById(R.id.listView1);// 插入事件的监听insert.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubhelper = new Helper(MainActivity.this);database = helper.getWritableDatabase();new Operator(database).insert("房泽龙", "1992-03-18");database.close();}});// 更新事件的监听upDate.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubhelper = new Helper(MainActivity.this);database = helper.getWritableDatabase();new Operator(database).upDate("flyou", "1992-03-18", 1);database.close();}});// 数据的删除delete.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubhelper = new Helper(MainActivity.this);database = helper.getWritableDatabase();new Operator(database).delete(3);}});// 查询全部数据findAll.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubhelper = new Helper(MainActivity.this);database = helper.getWritableDatabase();data = new Operator(database).findAll();System.out.println(data);list.setAdapter(new ArrayAdapter<String>(MainActivity.this,android.R.layout.simple_expandable_list_item_1, data));}});// 模糊查询findBy.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubhelper = new Helper(MainActivity.this);database = helper.getWritableDatabase();data = new Operator(database).findBy(keyword.getText().toString());list.setAdapter(new ArrayAdapter<String>(MainActivity.this,android.R.layout.simple_expandable_list_item_1, data));}});}}

限于篇幅问题，分页查询，事务处理就不再赘述，相信学习过JAVA的童鞋都会操作

下节预报：Activity的生命周期