android sqlite数据库详解例子

1.建立数据库，继承SQLiteOpenHelper

package com.yz.demo.fieldmanagement.db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;public class DBHelper extends SQLiteOpenHelper{/** * 数据库名字和版本 */final static String DB_NAME = "ydkq.db";final static int DB_VERSION = 1; private static DBHelper mInstance;//签到数据 sql语句  设想把打卡记录和移动轨迹关联起来private String sign_sql = "create table sign (userid text,date text,week text,signtext text,signtime text," +"isbelate text,signexplain text,signoutext text,signoutime text,isleavearly text,signoutexplain text)";//请假申请 sql语句private String leave_sql = "create table leave (userid text,name text,numdays text,startime text,endtime text,reasons text,state text,type text)" ;//外出申请 sql语句private String gout_sql = "create table gout (userid text,name text,date text,startime text,endtime text,reasons text,state text,type text)" ;//加班申请 sql语句private String overtime_sql = "create table overtime (userid text,name text,date text,startime text,endtime text,reasons text,state text,type text)" ;//移动轨迹 sql语句private String trackroute_sql = "create table trackroute (userid text,name text,date text,time text,lat text,lng text,thing text,place text)" ;//日报 sql语句private String daily_sql = "create table daily (userid text,name text,date text,time text,summary text)" ;//通讯录private String addbooks_sql = "create table addbooks (userid text,name text,phone text)";public DBHelper(Context context) {super(context, DB_NAME, null, DB_VERSION);// TODO Auto-generated constructor stub}public synchronized static DBHelper getInstance(Context context) {          if (mInstance == null) {              mInstance = new DBHelper(context);          }          return mInstance;      }; @Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stubLog.i("创建表", "打卡");db.execSQL(sign_sql);Log.i("创建表", "请假申请");db.execSQL(leave_sql);Log.i("创建表", "外出申请");db.execSQL(gout_sql);Log.i("创建表", "加班申请");db.execSQL(overtime_sql);Log.i("创建表", "轨迹申请");db.execSQL(trackroute_sql);Log.i("创建表", "日报");db.execSQL(daily_sql);db.execSQL(addbooks_sql);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stubswitch (oldVersion) {        case 0:            if (newVersion <= 1) {                return;            }            db.beginTransaction();            try {//                upgradeDatabaseToVersion1(db);                db.setTransactionSuccessful();            } catch (Throwable ex) {//                Log.e(TAG, ex.getMessage(), ex);                break;            } finally {                db.endTransaction();            }        return;    }    onCreate(db);}}

然后写增删改查方法类

package com.yz.demo.fieldmanagement.db;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;import com.yz.demo.fieldmanagement.entity.AddBooks;import com.yz.demo.fieldmanagement.entity.Daily;import com.yz.demo.fieldmanagement.entity.Gout;import com.yz.demo.fieldmanagement.entity.Leave;import com.yz.demo.fieldmanagement.entity.OverTime;import com.yz.demo.fieldmanagement.entity.Sign;import com.yz.demo.fieldmanagement.entity.Track;public class DBWrapper {/** *  数据库执行语句 *  杨钊 2017-4-12 */private static DBWrapper sInstance;DBHelper dbHelper;SQLiteDatabase mDb;public DBWrapper(Context context) {// TODO Auto-generated constructor stubDBHelper dbHelper = new DBHelper(context);dbHelper = DBHelper.getInstance(context);mDb = dbHelper.getWritableDatabase();}/** * 获得DBWrapper类对象 */public static DBWrapper getInstance(Context context) {if (sInstance == null) {synchronized (DBWrapper.class) {if (sInstance == null) {sInstance = new DBWrapper(context);}}}return sInstance;}/** * 删除表 * @param table */public void deleteTable(String table){mDb.delete(table, null, null);Log.i("DBWrapper", "删除了     " + table + "    表");}public void deleteTableBySpid(String table,String spid) {mDb.delete(table, "spid=?", new String[]{spid});Log.i("DBWrapper", "删除  "+table+"表");}///**// * 查询表// * @param table// * @param spid// * @return// */public Cursor selectTableById(String table,String id) {Cursor cursor = mDb.query(table, null, "id=?", new String[]{id}, null, null, null);cursor.moveToFirst();return cursor;}public Cursor selectTableByDate(String table,String date) {Cursor cursor = mDb.query(table, null, "date=?", new String[]{date}, null, null, null);cursor.moveToFirst();return cursor;}/** * 查询表 * @param table * @return */public Cursor selectTableBySpid(String table) {Cursor cursor = mDb.query(table, null, null, null, null, null, null);cursor.moveToFirst();return cursor;}/** * 打卡签到 * create table sign (userid text,date text,week text,signtext text,time text,isbelate text,explain text) */public void insertSign(Sign sign) {ContentValues values = new ContentValues();values.put("userid",sign.getId());values.put("date",sign.getDate());values.put("week",sign.getWeek());values.put("signtime",sign.getSigntime());values.put("signtext",sign.getSigntext());values.put("isbelate",sign.getIsbelate());values.put("signexplain",sign.getSignexplain());long rowid = mDb.insert("sign", null, values);Log.i("DBWrapper", "添加入数据库第几行          "+rowid);}/** * 打卡签退 */public void updateSign(Sign sign,String date){ContentValues values = new ContentValues();values.put("signoutime",sign.getSignoutime());values.put("signoutext",sign.getSignoutext());values.put("isleavearly",sign.getIsleavearly());values.put("signoutexplain",sign.getSignoutexplain());long rowid = mDb.update("sign",values,"date=?",new String[]{date});Log.i("DBWrapper", "更新数据库Sign表第几行          "+rowid);}/** * 请假申请 * create table leave (userid text,name text,numdays text,startime text,endtime text,reasons text,state text,type text */public void insertRequest(Leave leave) {ContentValues values = new ContentValues();values.put("userid", leave.getUserid());values.put("name", leave.getName());values.put("numdays", leave.getNumdays());values.put("startime", leave.getStartime());values.put("endtime", leave.getEndtime());values.put("reasons", leave.getReasons());values.put("state", leave.getState());values.put("type", leave.getType());long rowid = mDb.insert("leave", null, values);Log.i("DBWrapper", "添加入数据库第几行          "+rowid);}/** * 外出登记 * create table gout (userid text,name text,date text,startime text,endtime text,reasons text,state text,type text)" */public void insertGout(Gout gout) {ContentValues values = new ContentValues();values.put("userid", gout.getUserid());values.put("name", gout.getName());values.put("date", gout.getDate());values.put("startime", gout.getStartime());values.put("endtime", gout.getEndtime());values.put("reasons", gout.getReasons());values.put("state", gout.getState());values.put("type", gout.getType());long rowid = mDb.insert("gout", null, values);Log.i("DBWrapper", "添加入数据库第几行          "+rowid);}/** * 加班申请 * "create table overtime (userid text,name text,date text,startime text,endtime text,reasons text,state text,type text) */public void insertOverTime(OverTime overTime) {ContentValues values = new ContentValues();values.put("userid", overTime.getUserid());values.put("name", overTime.getName());values.put("date", overTime.getDate());values.put("startime", overTime.getStartime());values.put("endtime", overTime.getEndtime());values.put("reasons", overTime.getReasons());values.put("state", overTime.getState());values.put("type", overTime.getType());long rowid = mDb.insert("overtime", null, values);Log.i("DBWrapper", "添加入数据库第几行          "+rowid);}/** * 移动轨迹 * "create table trackroute (userid text,name text,date text,time text,lat text,lng text,thing text,place text */public void insertTrackRoute(Track track) {ContentValues values = new ContentValues();values.put("userid", track.getUserid());values.put("name", track.getName());values.put("date", track.getDate());values.put("time", track.getTime());values.put("lat", track.getLat());values.put("lng", track.getLng());values.put("thing", track.getThing());values.put("place", track.getPlace());long rowid = mDb.insert("trackroute", null, values);Log.i("DBWrapper", "添加入数据库第几行          "+rowid);}/** * 移动轨迹 * "create table daily (userid text,name text,date text,time text,summary text */public void insertDaily(Daily daily) {ContentValues values = new ContentValues();values.put("userid", daily.getUserid());values.put("name", daily.getName());values.put("date", daily.getDate());values.put("time", daily.getTime());values.put("summary", daily.getSummary());long rowid = mDb.insert("daily", null, values);Log.i("DBWrapper", "添加入数据库第几行          "+rowid);}/** * 移动轨迹 * "create table addbooks (userid text,name text,phone text */public void insertAddBooks(AddBooks addBooks) {ContentValues values = new ContentValues();values.put("userid", addBooks.getUserid());values.put("name", addBooks.getName());values.put("phone", addBooks.getPhone());long rowid = mDb.insert("addbooks", null, values);Log.i("DBWrapper", "添加入数据库第几行          "+rowid);}}

查找数据库数据进行解析

Cursor cursor = dbWrapper.selectTableBySpid("addbooks");if (cursor.getCount()!=0){    cursor.moveToFirst();    AddBooks addBooks ;    for (int i = 0;i<cursor.getCount();i++){        addBooks = new AddBooks();        addBooks.setUserid(cursor.getString(cursor.getColumnIndex("userid")));        addBooks.setName(cursor.getString(cursor.getColumnIndex("name")));        addBooks.setPhone(cursor.getString(cursor.getColumnIndex("phone")));        addBooks.setSortLetters(getAlpha(cursor.getString(cursor.getColumnIndex("name"))));        listAddBooks.add(addBooks);        cursor.moveToNext();    }}