【重拾Android】(2)SQLite数据库与数据持久化

SQLite是非常流行的嵌入式关系型数据库，轻载，速度快，而且是开源。在Android中，runtime提供SQLite，所以我们可以使用SQLite，而且是全集的SQLite。SQLite提供SQL接口，和一般的数据库一样。但是Android的API不采用JDBC，JDBC消耗太多的资源。SQLite 是一个开源的嵌入式关系数据库，它可以减少应用程序管理数据的开销， SQLite 可移植性好、很容易使用、很小、高效而且可靠。目前在Android 系统中集成的是 SQLite3 版本，SQLite 不支持静态数据类型，而是使用列关系。这意味着它的数据类型不具有表列属性，而具有数据本身的属性。当某个值插入数据库时， SQLite 将检查它的类型。如果该类型与关联的列不匹配，则 SQLite 会尝试将该值转换成列类型。如果不能转换，则该值将作为其本身具有的类型存储。SQLite 支持 NULL 、INTEGER 、 REAL 、 TEXT 和 BLOB 数据类型。例如：可以在 Integer 字段中存放字符串，或者在布尔型字段中存放浮点数，或者在字符型字段中存放日期型值。但是有一种例外，如果你的主键是 INTEGER ，那么只能存储 6 4位整数，当向这种字段中保存除整数以外的数据时，将会产生错误。另外， SQLite 在解析REATE TABLE语句时，会忽略 CREATE TABLE 语句中跟在字段名后面的数据类型信息。

SQLite 的特点

SQlite数据库总结起来有五大特点:

1. 零配置

SQlite3不用安装、不用配置、不用启动、关闭或者配置数据库实例。当系统崩溃后不用做任何恢复操作，在下次使用数据库的时候自动恢复。

2. 可移植

它是运行在 Windows 、 Linux 、BSD 、 Mac OS X 和一些商用 Unix 系统，比如 Sun 的 Solaris 、IBM 的 AIX ，同样，它也可以工作在许多嵌入式操作系统下，比如 Android 、 QNX 、VxWorks、 Palm OS 、 Symbin 和 Windows CE 。

3. 紧凑

SQLite是被设计成轻量级、自包含的。一个头文件、一个 lib 库，你就可以使用关系数据库了，不用任何启动任何系统进程。

4. 简单

SQLite有着简单易用的 API 接口。

5. 可靠

SQLite的源码达到 100% 分支测试覆盖率。

好了不废话，现在我们将使用工具类SQLiteOpenHelper实现基于SQL数据库的数据持久化工作

先定义SQLiteOpenHelper类进行设置

public class DBOpenHelper extends SQLiteOpenHelper {

private static final String DATABASE_NAME = "ask.db";//数据库名称

private static final String TABLE_NAME = "ask"; //数据表名称

private static final int DATABASE_VERSION = 1;//数据库版本

//单例模式实体类

static DBOpenHelper mInstance = null;

public DBOpenHelper(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION);

// TODO Auto-generated constructor stub

}

@Override

public void onCreate(SQLiteDatabase db) {

//建表语句,这样写修改起来方便直观

String sql = "CREATE TABLE " + TABLE_NAME + " ("

+ "_id INTEGER primary key autoincrement," //主键,自增,无需赋值

+ "name varchar(20),"

+ "group1 varchar(20)," //这里group是数据库的默认保留字,所以必须设为group1

+ "detail varchar(20),"

+ "starttime varchar(20),"

+ "endtime varchar(20),"

+ "type varchar(20)"

+ ")";

db.execSQL(sql);

}

/**

* 当数据库版本升级时重新建表

@Override

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

// TODO Auto-generated method stub

String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;

db.execSQL(sql);

onCreate(db);

}

/**

* 删除数据库

* @param context

* @return

public boolean deleteDatabase(Context context) {

return context.deleteDatabase(DATABASE_NAME);

}

/**单例模式

* 使用synchronized关键字加了一个异步锁

* 该模式在使用时无需使用new方法，直接使用getInstance方法即可获取对象

* **/

public static synchronized DBOpenHelper getInstance(Context context) {

if (mInstance == null) {

mInstance = new DBOpenHelper(context);

}

return mInstance;

}

数据实体类

public class Ask4Leave {

private Integer _id;

private String name;

private String group;

private String starttime;

private String endtime;

private String type;

private String detail;

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getGroup() {

return group;

}

public void setGroup(String group) {

this.group = group;

}

public String getStarttime() {

return starttime;

}

public void setStarttime(String starttime) {

this.starttime = starttime;

}

public String getEndtime() {

return endtime;

}

public void setEndtime(String endtime) {

this.endtime = endtime;

}

public String getType() {

return type;

}

public void setType(String type) {

this.type = type;

}

public String getDetail() {

return detail;

}

public void setDetail(String detail) {

this.detail = detail;

}

public Integer get_id() {

return _id;

}

public void set_id(Integer _id) {

this._id = _id;

}

数据服务类，实现对数据实体的增删改查，以及模糊查询等等

public class Ask4LeaveService {

private DBOpenHelper dbOpenHelper;

private SQLiteDatabase db;

private static Ask4LeaveService mInstance = null;

public Ask4LeaveService(Context context) {

// TODO Auto-generated constructor stub

dbOpenHelper = DBOpenHelper.getInstance(context);

//Log.e("DatabaseName",dbOpenHelper.getDatabaseName());

}

//连接数据库

public void openDB(){

db = dbOpenHelper.getWritableDatabase();

}

//关闭数据库

public void closeDB(){

closeDB();

}

//增数据

public void save(Ask4Leave a){

openDB();

ContentValues cv = new ContentValues();

cv.put("name",a.getName());

cv.put("group1",a.getGroup());

cv.put("detail",a.getDetail());

db.insert("ask", null, cv);

closeDB();

}

//按照对象删除数据

public void delete(Ask4Leave a){

openDB();

db.execSQL("delete from ask where _id=?",new Object[]{a.get_id()});

closeDB();

}

//按照_id删除数据

public void delete(Integer _id){

openDB();

db.execSQL("delete from ask where _id=?",new Object[]{_id});

closeDB();

}

//删除所有数据

public void deleteAll(){

openDB();

db.execSQL("delete from ask");

closeDB();

}

//按照_id查询数据

public Ask4Leave find(Integer _id){

openDB();

Cursor cursor = db.rawQuery("select * from ask where _id=?", new String[]{_id.toString()});

if(cursor.moveToFirst()){

int id = cursor.getInt(cursor.getColumnIndex("_id"));

String name = cursor.getString(cursor.getColumnIndex("name"));

String group = cursor.getString(cursor.getColumnIndex("group1"));

String detail = cursor.getString(cursor.getColumnIndex("detail"));

Ask4Leave a = new Ask4Leave();

a.set_id(id);

a.setName(name);

a.setGroup(group);

a.setDetail(detail);

return a;

}

closeDB();

return null;

}

//查询所有数据

public List<Ask4Leave> findAll(){

openDB();

List<Ask4Leave> ask4Leaves = new ArrayList<Ask4Leave>();

Cursor cursor = db.rawQuery("select * from ask order by _id DESC", null);

while(cursor.moveToNext()){

Ask4Leave ask4Leave = new Ask4Leave();

int id = cursor.getInt(cursor.getColumnIndex("_id"));

String name = cursor.getString(cursor.getColumnIndex("name"));

String group = cursor.getString(cursor.getColumnIndex("group1"));

String detail = cursor.getString(cursor.getColumnIndex("detail"));

ask4Leave.set_id(id);

ask4Leave.setName(name);

ask4Leave.setGroup(group);

ask4Leave.setDetail(detail);

ask4Leaves.add(ask4Leave);

}

closeDB();

return ask4Leaves;

}

/**单例模式

* 思考这里能否使用单例模式,使用单例模式的好处是什么

* 另外要是需要并发访问数据库这里需要如何修改?

* **/

public static synchronized Ask4LeaveService getInstance(Context context) {

if (mInstance == null) {

mInstance = new Ask4LeaveService(context);

}else{

}

return mInstance;

}

需要使用时只需要调用PersonService数据服务类即可实现基于数据库的数据持久化。

PersonService personService = new PersonService(this);

List<Person> ps = personService.findAll();

ps =personService.findByName("测试");

参考链接:

http://blog.csdn.net/wangkuifeng0118/article/details/7028953

http://blog.csdn.net/rhljiayou/article/details/7085347

http://blog.csdn.net/rhljiayou/article/details/7100840

拓展阅读 SQL数据库的用法介绍：

可以看看如何索引以及实现分页查找的SQL语句：

http://baoyy198612.blog.163.com/blog/static/1621399012011114103214223/

另附DB类的一些自带的方法,可以方便数据的增删改而不用自行拼接SQL语句

http://blog.csdn.net/i_lovefish/article/details/12389009

http://www.cnblogs.com/maxinliang/archive/2013/01/22/2871474.html

http://lqzit.iteye.com/blog/1042652

1、SQLiteDataBase对象的query()接口： public Cursorquery( Stringtable, String[]columns, Stringselection, String[]selectionArgs, 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 StringgroupBy, Stringhaving, StringorderBy, Stringlimit)

Query the given table, returning aCursorover the result set.

Parameters

table	The table name to compile the query against.(要查询的表名.)
columns	A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.(想要显示的列，若为空则返回所有列，不建议设置为空，如果不是返回所有列)
selection	A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.(where子句，声明要返回的行的要求，如果为空则返回表的所有行。)
selectionArgs	You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.(where子句对应的条件值)
groupBy	A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.(分组方式，若为空则不分组.)
having	A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.(having条件，若为空则返回全部（不建议）)
orderBy	How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.(排序方式，为空则为默认排序方式)
limit	Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.(限制返回的记录的条数，为空则不限制)

Returns

ACursorobject, which is positioned before the first entry. Note thatCursors are not synchronized, see the documentation for more details.

示例： ContentValues cv =newContentValues(); String[] args = {String.valueOf("a")};

query("user", new String[] {"username","password"},"username=?",args, null,null, null, null);

2、SQLiteDataBase对象的insert()接口：

public longinsert(Stringtable,StringnullColumnHack,ContentValuesvalues)

Convenience method for inserting a row into the database.

Parameters

table	the table to insert the row into(要插入数据的表的名称)
nullColumnHack	optional; may be`null`. SQL doesn't allow inserting a completely empty row without naming at least one column name. If your provided`values`is empty, no column names are known and an empty row can't be inserted. If not set to null, the`nullColumnHack`parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your`values`is empty.(当values参数为空或者里面没有内容的时候，我们insert是会失败的（底层数据库不允许插入一个空行），为了防止这种情况，我们要在这里指定一个列名，到时候如果发现将要插入的行为空行时，就会将你指定的这个列名的值设为null，然后再向数据库中插入。)
values	this map contains the initial column values for the row. The keys should be the column names and the values the column values(一个ContentValues对象，类似一个map.通过键值对的形式存储值。)

Returns

the row ID of the newly inserted row, or -1 if an error occurred

示例： ContentValues cv =newContentValues(); cv.put( "username" , "a" ); cv.put( "password" , "b" ); insert("user", null , cv); 3、SQLiteDataBase对象的update()接口：

public intupdate(Stringtable,ContentValuesvalues,StringwhereClause,String[]whereArgs)

Convenience method for updating rows in the database.

Parameters

table	the table to update in(要更新的表名)
values	a map from column names to new column values. null is a valid value that will be translated to NULL.(一个ContentValues对象，类似一个map.通过键值对的形式存储值。)
whereClause whereArgs	the optional WHERE clause to apply when updating. Passing null will update all rows.(可选的where语句) the group of args to deal with(whereClause语句中表达式的？占位参数列表)

Returns

the number of rows affected

ContentValues cv =newContentValues(); cv.put( "username" , "c" ); cv.put( "password" , "d" ); String[] args = {String.valueOf( "a" )}; update("user", cv, "username=?" ,args) 4、SQLiteDataBase对象的delete()接口：

public intdelete(Stringtable,StringwhereClause,String[]whereArgs)

Convenience method for deleting rows in the database.

Parameters

table	the table to delete from
whereClause whereArgs	the optional WHERE clause to apply when deleting. Passing null will delete all rows.(可选的where语句) the optional WHERE clause to apply when updating. Passing null will update all rows.(whereClause语句中表达式的？占位参数列表)

Returns

the number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass "1" as the whereClause.

示例： ContentValues cv =newContentValues(); String[] args = {String.valueOf( "c")}; delete("user", "username=?" , args);

//举例说明

//按照对象更新数据

public void update(Person p){

SQLiteDatabase db=dbOpenHelper.getWritableDatabase();

ContentValues cv = new ContentValues();

cv.put("name",p.getName());

cv.put("age",p.getAge());

db.update("person", cv, "_id=?",new String[]{p.get_id().toString()});

db.close();

}

//增数据

public void save(Ask4Leave a){

openDB();

ContentValues cv = new ContentValues();

cv.put("name",a.getName());

cv.put("group1",a.getGroup());

cv.put("detail",a.getDetail());

db.insert("ask", null, cv);

db.close();

}

//复杂的查询方式只能通过SQL语句实现

//按照subname进行模糊查询数据

public List<Person> findByName(String subname){

SQLiteDatabase db=dbOpenHelper.getReadableDatabase();

List<Person> persons = new ArrayList<Person>();

//这里使用了like语句,进行模糊查询,并需要注意参数的格式

Cursor cursor=db.rawQuery("select * from person1 where name like ?", new String[]{"%"+subname+"%"});

while(cursor.moveToNext()){

Person person=new Person();

int id=cursor.getInt(cursor.getColumnIndex("_id"));

String name=cursor.getString(cursor.getColumnIndex("name"));

String birthday = cursor.getString(cursor.getColumnIndex("birthday"));

String email = cursor.getString(cursor.getColumnIndex("email"));

int age=cursor.getInt(cursor.getColumnIndex("age"));

person.set_id(id);

person.setName(name);

person.setAge(age);

person.setBirsthday(birthday);

person.setEmail(email);

persons.add(person);

}

db.close();

return persons;

}

需要后续思考一下要是需要并发访问数据库Service类是否还能正常使用?

若不能的话,需要如何修改???

另外Android还有开源框架greenDAO能够供有需要时调用

Parameters

Returns

public longinsert(Stringtable,StringnullColumnHack,ContentValuesvalues)

Parameters

Returns

public intupdate(Stringtable,ContentValuesvalues,StringwhereClause,String[]whereArgs)

Parameters

Returns

public intdelete(Stringtable,StringwhereClause,String[]whereArgs)

Parameters

Returns

更多相关文章

随机推荐