SQLite数据库——分页搜索查询

1.常用SQl查询语句

1.1查询所有数据

 String sql = "Select * From history";

1.2倒序查询desc

String sql = "Select * From " + HistoryHelper.TABLE_NAME + " order by " + HistoryHelper.ID + " desc ";

1.3分页查询Limit、Offset

这里查询12条，从0到11

String sql = "Select * From " + HistoryHelper.TABLE_NAME + " Limit 11 Offset 0";

1.4倒序分页查询

String sql = "Select * From " + HistoryHelper.TABLE_NAME + " order by " + HistoryHelper.ID + " desc " + " Limit '" + pageCount + "' Offset '" + ((page - 1) * pageCount) + "'";

注意：order by和desc要写在Limit前面，同时非字符串的值需要加上单引号’ ‘，如" Limit ‘" + pageCount + “’”;pageCount这个值我们就需要用’ '。

1.5倒序分页查询-搜索匹配关键字where…like

搜索用where条件， LIKE 运算符是用来匹配 通配符 指定模式的文本值
下面的sql语句我们匹配了我们数据表的time字段是否包含s文本值

String sql = "Select * From " + HistoryHelper.TABLE_NAME +  " where " + HistoryHelper.Time + " like '%" + s + "%'"  + " order by " + HistoryHelper.ID + " desc "  + " Limit '" + pageCount + "' Offset '" + ((page - 1) * pageCount) + "'";

2.源码

2.1分页查询demo

查询上一页、下一页的方法：

• pageCount为你需要多少条数据
• page表示当前第几页，默认为1
• 当用户点击下一页，我们可以使用currentPage+=1，然后调用此方法
• 同理上一页我们可以使用currentPage-=1
• 当然还要判断是不是第一页和最后一页

//如果我要去11-20的Account表的数据 
//Select * From Account Limit 9 Offset 10; 
//以上语句表示从Account表获取数据，跳过10行，取9行

	/**
	 * context 上下文
	 * pageCount 查询多少条数据 
	 * page 第几页，默认为1
	 */
    public static List<CarResultMap> getHistory(Activity context, int pageCount, int page) {
        HistoryHelper mHelper = new HistoryHelper(context);
        SQLiteDatabase db = mHelper.getReadableDatabase();
        List<CarResultMap> carResultMap = new ArrayList<CarResultMap>();

        try {
            String sql = "Select * From " + HistoryHelper.TABLE_NAME + " order by " + HistoryHelper.ID + " desc " + " Limit '" + pageCount + "' Offset '" + ((page - 1) * pageCount) + "'";
            Cursor cursor = db.rawQuery(sql, new String[]{});
            if (cursor.moveToFirst()) {
                do {
                    CarResultMap carResult = new CarResultMap(cursor.getString(cursor.getColumnIndex(HistoryHelper.Time + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.CODE + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.CARTYPE + "")),
                            cursor.getInt(cursor.getColumnIndex(HistoryHelper.TYPE + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.USER + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.PRESSURE + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.LEAKAGE + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.UNIT + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.RESULT + "")));
                    carResultMap.add(carResult);
                }
                while (cursor.moveToNext());
            }
            cursor.close();
            db.close();
        } catch (Exception e) {
            db.close();
            e.printStackTrace();
            return carResultMap;
        }
        return carResultMap;
    }

2.2分页查询-搜索关键字demo

搜索框 输入想要查询的关键字
根据用户输入的文本s返回我们需要的数据

	/**
	 * context 上下文
	 * pageCount 查询多少条数据 
	 * page 第几页，默认为1
	 * s 想要匹配的关键字
	 */
public static List<CarResultMap> getSearchHistory(Activity context, int pageCount, int page, String s) {
        HistoryHelper mHelper = new HistoryHelper(context);
        SQLiteDatabase db = mHelper.getReadableDatabase();

        List<CarResultMap> carResultMap = new ArrayList<CarResultMap>();
        try {
            String sql = "Select * From " + HistoryHelper.TABLE_NAME +  " where " + HistoryHelper.USER
                    + " like '%" + s + "%'"
                    + " or " + HistoryHelper.Time
                    + " like '%" + s + "%'"
                    + " or " + HistoryHelper.USER
                    + " like '%" + s + "%'"
                    + " or " + HistoryHelper.TYPE
                    + " like '%" + s + "%'"
                    + " or " + HistoryHelper.CARTYPE
                    + " like '%" + s + "%'"
                    + " or " + HistoryHelper.CODE
                    + " like '%" + s + "%'"
                    + " or " + HistoryHelper.PRESSURE
                    + " like '%" + s + "%'"
                    + " or " + HistoryHelper.LEAKAGE
                    + " like '%" + s + "%'"
                    + " or " + HistoryHelper.RESULT
                    + " like '%" + s + "%'"
                    + " order by " + HistoryHelper.ID + " desc "  + " Limit '" + pageCount + "' Offset '" + ((page - 1) * pageCount) + "'";
            Cursor cursor = db.rawQuery(sql,new String[]{});
            if (cursor.moveToFirst()) {
                do {
                    CarResultMap carResult = new CarResultMap(cursor.getString(cursor.getColumnIndex(HistoryHelper.Time + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.CODE + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.CARTYPE + "")),
                            cursor.getInt(cursor.getColumnIndex(HistoryHelper.TYPE + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.USER + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.PRESSURE + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.LEAKAGE + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.UNIT + "")),
                            cursor.getString(cursor.getColumnIndex(HistoryHelper.RESULT + "")));
                    carResultMap.add(carResult);
                }
                while (cursor.moveToNext());
            }
            cursor.close();
            db.close();
        } catch (Exception e) {
            db.close();
            e.printStackTrace();
        }
        return carResultMap;
    }

3.参考文献

3.1文献路径