Android 使用raw文件下的sqlite数据库以及分页查询

看别人的博客多了，自己也写点东西来分享下！

由于Assets下的单个文件大小最多只能是1M，所以我们不能把可能会偏大的数据库放到Assets文件夹下面。为此Android为我们提供了一个解决方案，将数据库放置到raw文件夹中，次文件夹中的文件在程序被发布时不会被编译成二进制。待我们在使用是，使用程序copy的方式将其拷贝到设备的SDCARD上。

下面是一个Sqlite数据库的帮助类

package com.philp.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import com.example.mytest.R;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class SqliteHelper {
	private final Context ctx;

	public SqliteHelper(Context context) {
		ctx = context;
	}

	// 初始化数据库
	private SQLiteDatabase getDatabase() {
		try {
			File path = new File("/sdcard/smarthome");// 创建目录
			File f = new File("/sdcard/smarthome/smarthome.db");// 创建文件
			if (!path.exists()) {// 目录存在返回false
				path.mkdirs();// 创建一个目录
			}
			if (!f.exists()) {// 文件存在返回false
				try {
					InputStream is = ctx.getResources().openRawResource(
							R.raw.smarthome);
					FileOutputStream fos = new FileOutputStream(f);
					byte[] buffer = new byte[8192];
					int count = 0;
					while ((count = is.read(buffer)) > 0) {
						fos.write(buffer, 0, count);
					}
					fos.close();
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			SQLiteDatabase mysql = SQLiteDatabase.openOrCreateDatabase(f, null);
			return mysql;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public int add(String tableName, ContentValues contentValues,
			String whereClause, String[] whereArgs) {
		SQLiteDatabase db = getDatabase();
		int returni = db.update(tableName, contentValues, whereClause,
				whereArgs);
		return returni;
	}

	public int add(String tableName, ContentValues contentValues) {
		SQLiteDatabase db = getDatabase();
		Long rowid = db.insert(tableName, null, contentValues);
		return rowid.intValue();
	}

	public void insertOrUpdateOrDelete(String sql) {
		SQLiteDatabase db = getDatabase();
		db.execSQL(sql);
		db.close();
	}

	public JSONArray commSelect(String tableName, String filedName,
			String whereClause) {
		SQLiteDatabase db = getDatabase();
		if (filedName.equals("")) {
			filedName = "*";
		}
		Cursor cur = db.rawQuery("Select " + filedName + " from " + tableName
				+ " where " + whereClause, null);
		JSONArray jsonArray = new JSONArray();
		if (cur != null) {
			while (cur.moveToNext()) {
				JSONObject jsonObject = new JSONObject();
				for (int i = 0; i < cur.getColumnCount(); i++) {
					try {
						jsonObject.put(cur.getColumnName(i), cur.getString(i));
					} catch (JSONException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				jsonArray.put(jsonObject);
			}
		}
		return jsonArray;
	}

	public JSONObject commSelectJSONObject(String tableName, String filedName,
			String whereClause) {
		SQLiteDatabase db = getDatabase();
		if (filedName.equals("")) {
			filedName = "*";
		}
		Cursor cur = db.rawQuery("Select " + filedName + " from " + tableName
				+ " where " + whereClause, null);
		JSONObject jsonObject = new JSONObject();
		if (cur != null) {
			while (cur.moveToNext()) {
				for (int i = 0; i < cur.getColumnCount(); i++) {
					try {
						jsonObject.put(cur.getColumnName(i), cur.getString(i));
					} catch (JSONException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
		}
		return jsonObject;
	}

	public Boolean isExist(String tableName, String whereClause) {
		SQLiteDatabase db = getDatabase();
		Cursor cursor = db.rawQuery("select count(*) from " + tableName
				+ " where " + whereClause, null);
		cursor.moveToFirst();
		long result = cursor.getLong(0);
		cursor.close();
		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}
}

使用方法：

SqliteHelper sqliteHelper = new SqliteHelper(MainActivity.this);

//参数一为表明，参数二为查询字段，参数三为条件
JSONArray jsonArray = sqliteHelper.commSelect("area_category"," * "," 1=1 ");

sqlite的操作，记下来不用每次都去找

select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果

运用：
sqlitecmd.CommandText = string.Format("select * from GuestInfo order by GuestId limit {0} offset {0}*{1}", size, index-1);//size:每页显示条数，index页码