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),serif; font-size:18px; background-color:inherit"> + "starttime varchar(20),serif; font-size:18px; background-color:inherit"> + "endtime varchar(20),serif; font-size:18px; background-color:inherit"> + "type varchar(20)"
+ ")";
/**
*/
public void onUpgrade(sqliteDatabase db,int oldVersion,int newVersion) {
// TODO Auto-generated method stub
String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
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",cv);
//按照对象删除数据
public void delete(Ask4Leave a){ db.execsql("delete from ask where _id=?",new Object[]{a.get_id()});
//按照_id删除数据
public void delete(Integer _id){
//删除所有数据
public void deleteAll(){
db.execsql("delete from ask");
//按照_id查询数据
public Ask4Leave find(Integer _id){
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;
}
return null;
//查询所有数据
public List<Ask4Leave> findAll(){
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();
ask4Leave.set_id(id);
ask4Leave.setName(name);
ask4Leave.setGroup(group);
ask4Leave.setDetail(detail);
ask4Leaves.add(ask4Leave);
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://www.jb51.cc/article/p-hibebhde-qk.html
http://www.jb51.cc/article/p-mhpqicnw-bd.html
http://www.jb51.cc/article/p-huzatsjz-bd.html
http://baoyy198612.blog.163.com/blog/static/1621399012011114103214223/
另附DB类的一些自带的方法,可以方便数据的增删改而不用自行拼接sql语句
http://www.jb51.cc/article/p-rjktuygp-bak.html
http://www.cnblogs.com/maxinliang/archive/2013/01/22/2871474.html
http://lqzit.iteye.com/blog/1042652
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.(限制返回的记录的条数，为空则不限制) |
示例：ContentValues cv =newContentValues();
String[] args = {String.valueOf("a")};
query("user",new String[] {"username","password"},255)">"username=?"argsnull,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 benull. sql doesn't allow inserting a completely empty row without naming at least one column name. If your providedvaluesis empty,no column names are known and an empty row can't be inserted. If not set to null,thenullColumnHackparameter provides the name of nullable column name to explicitly insert a NULL into in the case where yourvaluesis 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
cv.put(
"username"
,
"a"
);
"password"
"b"
insert("user",
null
3、sqliteDataBase对象的update()接口：
public intupdate(Stringtable,ContentValuesvalues,StringwhereClause,String[]whereArgs)Convenience method for updating rows in the database. Parameters
|
|---|
