关于数据库的三层模式一般见地

数据库的三层模式

关于数据库的封装一般分为三层，即：业务逻辑访问DBManager（顶层业务逻辑直接访问的操作类）→→DBService（操作底层实体类数据，对其进行增删查改（调用DBHelper里面的方法））→→DBHelper（其中DBHelper用于书写增删查改的基本SQL操作方法，DBOpenHelper用于建立相关数据库表、规定相关字段）。
这样分明的层次，能够保证底层数据不易被更改或破坏（数据安全得以保证），且便于后台维护和管理，是各个公司及企业常用的分层模式。

• DBManager类
• DBService类
• DBHelper类

---

DBManager类

public class HisDbManager {

    private static HisDbManager mInstance;
    private static Object mSyncLock = new Object();
    private final Context mContext;
    private static DbServices mDbServices;
    private static final String TABLE_HISTORY = DbOpenHelper.TABLE__HISTORY;

    public static HisDbManager getInstance(Context context) {
        synchronized (mSyncLock) {
            if (mInstance == null) {
                mInstance = new HisDbManager(context);
            }
        }
        return mInstance;
    }

    private HisDbManager(Context context) {
        mContext = context;
        mDbServices = DbServices.getInstance(context);
    }


    /**
     *  删除记录表全部记录
     * @return
     */
    synchronized public boolean deleteAll(){
        return mDbServices.deleteAllHisRec();
    }

    /**
     *  通过Id删除记录表全部记录
     *
     * @param id 记录表Id
     * @return
     */
    synchronized public boolean deleteById(int id){
        return mDbServices.deleteHisRecById(id);
    }

    /**
     * 批量删除记录表
     *
     * @param ids
     * @return 返回受影响的行数
     */
    synchronized public int deleteById(List<Integer> ids) {
        return mDbServices.deleteHisRecById(ids,TABLE_HISTORY);
    }


    /**
     * 查询全部记录表记录
     */
    public List<HisRecInfo> queryAll(){
        return mDbServices.queryAllHisRec();
    }


    /**
     * 查询单条记录表资料
     *
     * @param id 自增长id
     */
    public HisRecInfo queryById(int id){
        return mDbServices.queryHisRecById(id);
    }


    /**
     * 模糊查询详历史记录
     */
    public List<Info> queryFuzzy(String key){
        return mDbServices.queryFuzzyHistory(key);
    }

注意：以下这个方法的格式，可套用

public List<Info> queryAllDetails() {
    String sql = "SELECT * FROM " + TABLE_DETAILS
            + " WHERE " + DbOpenHelper.DetailsInfo.idCode.name() + " IS NOT NULL"
            + ";";//执行sql语句(注意空格)
    Cursor cursor = executeSql(sql, 0);//执行sql语句
    List<Info> list = null;
    if (cursor != null && cursor.getCount() > 0) {//判断不为空且不为""
        list = new ArrayList<Info>();//创建容器
        while (cursor.moveToNext()) {
            list.add(getInfo(cursor));
        }
    }
    mOpenHelper.closeCursor(cursor);//必须关闭游标，否则内存泄漏
    return list;
}

DBService类

public class DbServices {

    private static DbServices mInstance;
    private static final Object mSyncLock = new Object();
    private static DbHelper mDbHelper;
    private static final String TABLE__DETAILS = DbOpenHelper.TABLE_DETAILS;
    private static final String TABLE_HISTORY = DbOpenHelper.TABLE_HISTORY;

    public static DbServices getInstance(Context context) {
        synchronized (mSyncLock) {
            if (mInstance != null) {
                return mInstance;
            }
            if (context != null) {
                mInstance = new DbServices(context);
                return mInstance;
            }
        }

        return null;
    }

    private DbServices(Context context) {
        mDbHelper = DbHelper.getInstance(context);
    }

    public boolean insertDetails(DetailsInfo info) {
        if (info == null || TextUtils.isEmpty(info.getInvCode())) {
            return false;
        }
        return mDbHelper.insertDetails(info.getInvCode(), info.getUnitId(),
                info.getInvName());
    }

    public long insertHisRec(HisRecInfo info) {
        ContentValues contentValues = getHisContentValues(info);
        return mDbHelper.insert(TABLE__HISTORY, contentValues);
    }
/**
 * 将字段信息的model类转为ContentValues（序列化数据）
 *
 * @param info
 * @return
 */
private ContentValues getDetailsContentValues(DetailsInfo info) {
    ContentValues values = new ContentValues();
    values.put(DbOpenHelper.Details.invCode.name(), info.getInvCode());
    values.put(DbOpenHelper.Details.invName.name(), info.getInvName());
    values.put(DbOpenHelper.Details.unitId.name(), info.getUnitId());
    return values;
}


public boolean deleteHisRecById(String Id) {
    if (TextUtils.isEmpty(Id)) {
        return false;
    }
    return mDbHelper.deleteByCondition(TABLE_HISTORY,
            DbOpenHelper.History.invCode.name(),
            Id);
}

public boolean deleteHisRecByWorkerId(String workerId) {
    if (TextUtils.isEmpty(workerId)) {
        return false;
    }
    return mDbHelper.deleteByCondition(TABLE_HISTORY,
            DbOpenHelper.History.userCode.name(),
            workerId);
}

public boolean deleteHisRecById(int id) {
    return mDbHelper.deleteByCondition(TABLE_HISTORY,
            DbOpenHelper.History.id.name(),
            id + "");
}

public boolean deleteHisRecByDevice(String deviceName) {
    if (TextUtils.isEmpty(deviceName)) {
        return false;
    }
    return mDbHelper.deleteByCondition(TABLE_HISTORY,
            DbOpenHelper.History.scaleName.name(),
            deviceName + "");
}

public List<DetailsInfo> queryAllDetails() {
    return mDbHelper.queryAllDetails();
}

public List<HisRecInfo> queryAllHisRec() {
    return mDbHelper.queryAllHisRec();
}

public DetailsInfo queryDetailsById(String Id) {
    if (TextUtils.isEmpty(Id)) {
        return null;
    }
    return mDbHelper.queryDetailsByCondition(DbOpenHelper.Details.invCode.name(), Id);
}

public List<HisRecInfo> queryHisRecById(String Id) {
    if (TextUtils.isEmpty(Id)) {
        return null;
    }
    return mDbHelper.queryHisRecByCondition(DbOpenHelper.History.invCode.name(), Id);
}

public List<HisRecInfo> queryHisRecByWorkerId(String workerId) {
    if (TextUtils.isEmpty(workerId)) {
        return null;
    }
    return mDbHelper.queryHisRecByCondition(DbOpenHelper.History.userCode.name(), workerId);
}

public HisRecInfo queryHisRecById(int id) {
    return mDbHelper.queryHisRecById(id);
}

DBHelper类

public boolean insertDetails(String invCode, int unitId, String invName) {
    String sql = "REPLACE INTO " + TABLE_DETAILS
            + " ("
            + DbOpenHelper.Details.invCode.name() + ","
            + DbOpenHelper.Details.unitId.name() + ","
            + DbOpenHelper.Details.invName.name()
            + " )"
            + " VALUES ('" + invCode + "','" + unitId + "','" + invName  + "');";
    return executeSql(sql);
}

/**
 * 批量删除记录表
 *
 * @param ids
 * @return 返回受影响的行数
 */
public int deleteHisRecById(List<Integer> ids, String table) {
    String idColName =DbOpenHelper.History.id.name();
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < ids.size(); i++) {
        sb.append(idColName + "=" + ids.get(i));
        if (i + 1 < ids.size()) {
            sb.append(" or ");
        } else {
            sb.append(";");
        }
    }

    String sqlString = "DELETE FROM " + table + " WHERE " + sb.toString();
    if (executeSql(sqlString)) {
        return ids.size();
    }
    return -1;
}

/**
 * 执行删除操作
 */
public boolean deleteAll(String table) {
    String sql = "DELETE FROM " + table;
    return executeSql(sql);
}

/**
 * 执行单条删除操作
 */
public boolean deleteByCondition(String table, String column, String values) {
    String sql = "DELETE FROM " + table + " WHERE " + column + " ='" + values + "';";
    return executeSql(sql);
}

/**
 * 重置指定表当前id为0
 */
public void revertSeq(String table) {
    String sql = "UPDATE sqlite_sequence set seq=0 WHERE name='" + table
            + "'" + ";";
    executeSql(sql);
}
/**
 * 执行简单SQL语句
 *
 * @param sqlString
 * @return 执行成功返回true，否则返回false
 */
private boolean executeSql(final String sqlString) {
    synchronized (mSyncLock) {
        Globals.log("数据库测试 sqlString:" + sqlString);
        SQLiteDatabase db = null;
        try {
            db = mOpenHelper.getReadableDatabase();
            db.execSQL(sqlString);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }
}

/**
 * 执行插入操作
 *
 * @param table
 * @param values
 * @return
 */
public long insert(String table, ContentValues values) {
    try {
        return executeInsertOrThrow(table, null, values);
    } catch (Exception e) {
        e.printStackTrace();
        return -1;
    }
}

/**
 * 执行插入操作
 *
 * @param table
 * @param nullColumnHack
 * @param values
 * @return 返回最后一条插入的rowId，若发生错误则返回-1
 */
private long executeInsertOrThrow(String table, String nullColumnHack, ContentValues values) {
    synchronized (mSyncLock) {
        SQLiteDatabase db = null;
        long rowId = -1;
        try {
            db = mOpenHelper.getReadableDatabase();
            rowId = db.insertOrThrow(table, nullColumnHack, values);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rowId;
    }
}

/**
 * 执行简单查询SQL语句
 * 不可用于执行delete update
 *
 * @param sqlString
 * @param flag      任意数值,无实际作用
 * @return
 */
private Cursor executeSql(final String sqlString, int flag) {
    synchronized (mSyncLock) {
        Globals.log("数据库测试 sqlString:" + sqlString);
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = mOpenHelper.getReadableDatabase();
            cursor = db.rawQuery(sqlString, null);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return cursor;
    }
}

public long replace(final String table, final String nullColumnhack, final List<ContentValues> valuesList) {
    synchronized (mSyncLock) {
        long affected = 0;
        SQLiteDatabase db = null;
        try {
            db = mOpenHelper.getWritableDatabase();
            db.beginTransaction();
            for (ContentValues values : valuesList) {
                for (String key : values.keySet()) {
                    Globals.log("", "replace order pCode List:"
                            + key + ":" + values.get(key));
                }
                db.replace(table, nullColumnhack, values);
                affected++;
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (db != null) {
                db.endTransaction();
            }
        }
        return affected;
    }
}


/**
 * 模糊查询的一般书写步骤
 * 筛选累计记录
 * 若不筛选传入null
 * @param groupByFlag  标记哪个列需要groupBy ( 1：Id, 2：Name 3: area 4:worker)
 * @return
 */
public List<HisRecInfo> queryHistoryRecAll(String id,String name, String area, String worker,
                                                String scaleName, String startTime, String endTime,
                                                int groupByFlag) {
    String idArg = " AND " + DbOpenHelper.History.invCode.name() + "='" + Id + "'";
    String nameArg = " AND " + DbOpenHelper.History.invName.name() + "='" + Name + "'";
    String areaArg = " AND " + DbOpenHelper.History.area.name() + "='" + area + "'";
    String workerNameArg = " AND " + DbOpenHelper.History.userName.name() + "='" + worker + "'";
    String scaleNameArg = " AND " + DbOpenHelper.History.scaleName.name() + "='" + scaleName + "'";
    String dateTimeArg = " AND " + DbOpenHelper.History.date.name() + " >= '" + startTime + "'"
            + " AND " + DbOpenHelper.History.date.name() + " <= '" + endTime + "'";

    String idGroupByArg = " GROUP BY " + DbOpenHelper.History.invCode.name();
    String nameGroupByArg = " GROUP BY " + DbOpenHelper.History.invName.name();
    String areaGroupByArg = " GROUP BY " + DbOpenHelper.History.area.name();
    String workerNameGroupByArg = " GROUP BY " + DbOpenHelper.History.userName.name();

    StringBuffer sb = new StringBuffer();
    if (!TextUtils.isEmpty(Id)) {
        sb.append(idArg);
    }
    if (!TextUtils.isEmpty(name)) {
        sb.append(nameArg);
    }
    if (!TextUtils.isEmpty(area)) {
        sb.append(areaArg);
    }
    if (!TextUtils.isEmpty(worker)) {
        sb.append(workerNameArg);
    }
    if (!TextUtils.isEmpty(scaleName)) {
        sb.append(scaleNameArg);
    }
    if (!TextUtils.isEmpty(startTime) && !TextUtils.isEmpty(endTime)) {
        sb.append(dateTimeArg);
    }

    if (1 == groupByFlag) {
        sb.append(idGroupByArg);
    } else if (2 == groupByFlag) {
        sb.append(nameGroupByArg);
    } else if (3 == groupByFlag) {
        sb.append(areaGroupByArg);
    } else if (4 == groupByFlag) {
        sb.append(workerNameGroupByArg);
    }


    String sql = "SELECT * FROM " + TABLE_HISTORY
            + " WHERE " + DbOpenHelper.History.invCode.name() + " IS NOT NULL "
            + sb.toString() + " "
            + "ORDER BY " + DbOpenHelper.History.id.name() + " DESC"
            + ";";
    Cursor cursor = executeSql(sql, 0);
    List<Info> list = null;
    if (cursor != null && cursor.getCount() > 0) {
        list = new ArrayList<Info>();
        while (cursor.moveToNext()) {
            list.add(getHisRecInfo(cursor));
        }
    }
    mOpenHelper.closeCursor(cursor);
    return list;
}

private DetailsInfo getDetailsInfo(Cursor cursor) {
    DetailsInfo info = new DetailsInfo();
    info.setInvCode(cursor.getString(cursor.getColumnIndex(DbOpenHelper.Details.invCode.name())));
    info.setInvName(cursor.getString(cursor.getColumnIndex(DbOpenHelper.Details.invName.name())));
    info.setUnitId(cursor.getInt(cursor.getColumnIndex(DbOpenHelper.Details.unitId.name())));
    return info;
}

先介绍到这了，如有需要改进的，还望各位大神指导，谢谢！

附上一些学习教程的地址

http://www.runoob.com/sqlite/sqlite-tutorial.html“SQLite学习”

http://download.csdn.net/download/disanxiaoyao/9744470“sql语句学习pdf文件下载，免费的”