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;  
    }; 

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		Log.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);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		switch (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 stub
		DBHelper 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();
    }
}