sqlite数据库操作工具类(含jsqlite.jar包)

package org.easydarwin.util; /** * Created by shankes on 2016/8/22. */ import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; import org.easydarwin.tab.tab.TabMainActivity; import java.io.File; import java.util.Map; /** * 数据库公共类，提供基本数据库操作 * * @author raymon */ public class DBManager { // 默认数据库 private static final String DB_NAME = "easyplayer.db"; private static final String PROPERTY_CREATE_TABLE_SQL_PATH_FULL = "/assets/createTableSql.property"; // 数据库版本 private static final int DB_VERSION = 1; // 执行open()打开数据库时，保存返回的数据库对象 private SQLiteDatabase mSQLiteDatabase = null; // 由SQLiteOpenHelper继承过来 private DatabaseHelper mDatabaseHelper = null; // 本地Context对象 private Context mContext = null; private static DBManager dbConn = null; // 查询游标对象 private Cursor cursor; /** * SQLiteOpenHelper内部类 */ private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { // 当调用getWritableDatabase()或 getReadableDatabase()方法时,创建一个数据库 super(context, DB_NAME, null, DB_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE ad_record(id PRIMARY KEY NOT NULL, adUrl TEXT, apMac TEXT, createDate DATETIME);"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS ad_record"); onCreate(db); } } /** * 构造函数 * * @param mContext */ private DBManager(Context mContext) { super(); this.mContext = mContext; } public static DBManager getInstance(Context mContext) { if (null == dbConn) { dbConn = new DBManager(mContext); } return dbConn; } /** * 打开数据库 */ public void open() { mDatabaseHelper = new DatabaseHelper(mContext); File pathDbFile = new File(ConfigPath.PATH_SQLITE); if (!pathDbFile.exists()) { pathDbFile.mkdirs(); } File dbFile = new File(ConfigPath.PATH_SQLITE + File.separator + DB_NAME); mSQLiteDatabase = SQLiteDatabase.openOrCreateDatabase(dbFile, null); // 初始化表 createTable(); } /** * 创建表 */ private void createTable() { // 从property文件中获取创建表的语句 Map<String, String> createTableSqlMap = PropertyUtil.getPropertiesMap(PROPERTY_CREATE_TABLE_SQL_PATH_FULL); for (Map.Entry<String, String> entry : createTableSqlMap.entrySet()) { String tableName = entry.getKey(); String createTableSql = entry.getValue(); if (!isTabbleExist(tableName)) { //执行SQL语句 mSQLiteDatabase.execSQL(createTableSql); } } } /** * 创建表 * * @param tableName */ private void createTable(String tableName, String createTableSql) { if (!isTabbleExist(tableName)) { //执行SQL语句 mSQLiteDatabase.execSQL(createTableSql); } } /** * 判断表是否存在 * * @param tableName * @return */ private boolean isTabbleExist(String tableName) { boolean result = false; if (tableName == null) { return false; } Cursor cursor = null; try { String sql = "select count(*) as c from sqlite_master where type ='table' and name ='" + tableName.trim() + "' "; cursor = mSQLiteDatabase.rawQuery(sql, null); if (cursor.moveToNext()) { int count = cursor.getInt(0); if (count > 0) { result = true; } } } catch (Exception e) { } return result; } /** * 关闭数据库 */ public void close() { if (null != mDatabaseHelper) { mDatabaseHelper.close(); } if (null != cursor) { cursor.close(); } } /** * 插入数据 * * @param tableName 表名 * @param nullColumn null * @param contentValues 名值对 * @return 新插入数据的ID，错误返回-1 * @throws Exception */ public long insert(String tableName, String nullColumn, ContentValues contentValues) throws Exception { if (find(tableName, new String[]{"_id"}, new String[]{contentValues.getAsString("_id")}, null, null, null).getCount() > 0) { Log.e(TabMainActivity.TAG, "该条数据已存在"); return -1; } else { try { return mSQLiteDatabase.insert(tableName, nullColumn, contentValues);// 返回新添记录的行号，与主键id无关 } catch (Exception e) { throw e; } } } /** * 通过主键ID删除数据 * * @param tableName 表名 * @param key 主键名 * @param id 主键值 * @return 受影响的记录数 * @throws Exception */ public long delete(String tableName, String key, int id) throws Exception { try { return mSQLiteDatabase.delete(tableName, key + " = " + id, null); } catch (Exception e) { throw e; } } /** * 查找表的所有数据 * * @param tableName 表名 * @param columns 如果返回所有列，则填null * @return * @throws Exception */ public Cursor findAll(String tableName, String[] columns) throws Exception { try { cursor = mSQLiteDatabase.query(tableName, columns, null, null, null, null, null); cursor.moveToFirst(); return cursor; } catch (Exception e) { throw e; } } /** * 根据主键查找数据 * * @param tableName 表名 * @param key 主键名 * @param id 主键值 * @param columns 如果返回所有列，则填null * @return Cursor游标 * @throws Exception */ public Cursor findById(String tableName, String key, int id, String[] columns) throws Exception { try { return mSQLiteDatabase.query(tableName, columns, key + " = " + id, null, null, null, null); } catch (Exception e) { throw e; } } /** * 根据条件查询数据 * * @param tableName 表名 * @param names 查询条件 * @param values 查询条件值 * @param columns 如果返回所有列，则填null * @param orderColumn 排序的列 * @param limit 限制返回数 * @return Cursor游标 * @throws Exception */ public Cursor find(String tableName, String[] names, String[] values, String[] columns, String orderColumn, String limit) throws Exception { try { StringBuffer selection = new StringBuffer(); for (int i = 0; i < names.length; i++) { selection.append(names[i]); selection.append(" = ?"); if (i != names.length - 1) { selection.append(","); } } cursor = mSQLiteDatabase.query(true, table