Android将sqlite数据库保存到SD卡以及数据库增删改查操作,模糊查询like or

于 2018-01-11 14:59:18 发布
阅读量1.4k 收藏 5
点赞数
文章标签： sqlite 数据库路径 sdcard 增删改查 模糊查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/legend12300/article/details/79034010
版权

        Android通过SQLiteOpenHelper创建数据库时默认是将数据库保存在'/data/data/应用程序名/databases'目录下的，只需要在继承SQLiteOpenHelper类的构造函数中传入数据库名称就可以了，但如果将数据库保存到指定的路径下面，都需要通过重写继承SQLiteOpenHelper类的构造函数中的context.因为：在阅读SQLiteOpenHelper.java的源码时会发现：创建数据库都是通过Context的openOrCreateDatabase方法实现的，如果我们需要在指定的路径下创建数据库，就需要写一个类继承Context，并复写其openOrCreateDatabase方法，在openOrCreateDatabase方法中指定数据库存储的路径即可，下面为类SQLiteOpenHelper中getWritableDatabase和getReadableDatabase方法的源码，SQLiteOpenHelper就是通过这两个方法来创建数据库的。


由于Context中有除了openOrCreateDatabase方法以外的其它抽象函数，所以建议使用非抽象类ContextWrapper，该类继承自Context，自定义的DatabaseContext类源码如下：

 public SQLiteDatabase getReadableDatabase() {
        synchronized (this) {
            return getDatabaseLocked(false);
        }
    }

    private SQLiteDatabase getDatabaseLocked(boolean writable) {
        if (mDatabase != null) {
            if (!mDatabase.isOpen()) {
                // Darn!  The user closed the database by calling mDatabase.close().
                mDatabase = null;
            } else if (!writable || !mDatabase.isReadOnly()) {
                // The database is already open for business.
                return mDatabase;
            }
        }

        if (mIsInitializing) {
            throw new IllegalStateException("getDatabase called recursively");
        }

        SQLiteDatabase db = mDatabase;
        try {
            mIsInitializing = true;

            if (db != null) {
                if (writable && db.isReadOnly()) {
                    db.reopenReadWrite();
                }
            } else if (mName == null) {
                db = SQLiteDatabase.create(null);
            } else {
                try {
                    if (DEBUG_STRICT_READONLY && !writable) {
                        final String path = mContext.getDatabasePath(mName).getPath();
                        db = SQLiteDatabase.openDatabase(path, mFactory,
                                SQLiteDatabase.OPEN_READONLY, mErrorHandler);
                    } else {
                        db = mContext.openOrCreateDatabase(mName, mEnableWriteAheadLogging ?
                                Context.MODE_ENABLE_WRITE_AHEAD_LOGGING : 0,
                                mFactory, mErrorHandler);
                    }
                } catch (SQLiteException ex) {
                    if (writable) {
                        throw ex;
                    }
                    Log.e(TAG, "Couldn't open " + mName
                            + " for writing (will try read-only):", ex);
                    final String path = mContext.getDatabasePath(mName).getPath();
                    db = SQLiteDatabase.openDatabase(path, mFactory,
                            SQLiteDatabase.OPEN_READONLY, mErrorHandler);
                }
            }

            onConfigure(db);

            final int version = db.getVersion();
            if (version != mNewVersion) {
                if (db.isReadOnly()) {
                    throw new SQLiteException("Can't upgrade read-only database from version " +
                            db.getVersion() + " to " + mNewVersion + ": " + mName);
                }

                if (version > 0 && version < mMinimumSupportedVersion) {
                    File databaseFile = new File(db.getPath());
                    onBeforeDelete(db);
                    db.close();
                    if (SQLiteDatabase.deleteDatabase(databaseFile)) {
                        mIsInitializing = false;
                        return getDatabaseLocked(writable);
                    } else {
                        throw new IllegalStateException("Unable to delete obsolete database "
                                + mName + " with version " + version);
                    }
                } else {
                    db.beginTransaction();
                    try {
                        if (version == 0) {
                            onCreate(db);
                        } else {
                            if (version > mNewVersion) {
                                onDowngrade(db, version, mNewVersion);
                            } else {
                                onUpgrade(db, version, mNewVersion);
                            }
                        }
                        db.setVersion(mNewVersion);
                        db.setTransactionSuccessful();
                    } finally {
                        db.endTransaction();
                    }
                }
            }

            onOpen(db);

            if (db.isReadOnly()) {
                Log.w(TAG, "Opened " + mName + " in read-only mode");
            }

            mDatabase = db;
            return db;
        } finally {
            mIsInitializing = false;
            if (db != null && db != mDatabase) {
                db.close();
            }
        }
    }
所以自定义Context类, 重写 getDatabasePath   openOrCreateDatabase  openOrCreateDatabase 由于Context中有除了openOrCreateDatabase方法以外的其它抽象函数，所以建议使用非抽象类ContextWrapper，该类继承自Context，自定义的DatabaseContext类源码如下： 由于Context中有除了openOrCreateDatabase方法以外的其它抽象函数，所以建议使用非抽象类ContextWrapper，该类继承自Context，自定义的DatabaseContext类源码如下： 

package com.tan.searchhistory.db;

import android.content.Context;
import android.content.ContextWrapper;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.tan.searchhistory.constants.Config;

import java.io.File;
import java.io.IOException;


/**
 * 用于支持对存储在SD卡上的数据库的访问
 **/
public class DatabaseContext extends ContextWrapper {

    /**
     * 构造函数
     * @param    base 上下文环境
     */
    public DatabaseContext(Context base){
        super(base);
    }

    /**
     * 获得数据库路径，如果不存在，则创建对象对象
     * @param    name
     */ 
    @Override
    public File getDatabasePath(String name) {
        //判断是否存在sd卡
        boolean sdExist = android.os.Environment.MEDIA_MOUNTED.equals(android.os.Environment.getExternalStorageState());
        if(!sdExist){//如果不存在,
            Log.e("SD卡管理：", "SD卡不存在，请加载SD卡");
            return null;
        } else{//如果存在
            //获取sd卡路径
            String dbDir=android.os.Environment.getExternalStorageDirectory().toString();
            dbDir += Config.DB_PATH;//数据库所在目录
            String dbPath = dbDir+"/"+name;//数据库路径
            //判断目录是否存在，不存在则创建该目录
            File dirFile = new File(dbDir);
            if(!dirFile.exists())
                dirFile.mkdirs();

            //数据库文件是否创建成功
            boolean isFileCreateSuccess = false;
            //判断文件是否存在，不存在则创建该文件
            File dbFile = new File(dbPath);
            if(!dbFile.exists()){
                try {
                    isFileCreateSuccess = dbFile.createNewFile();//创建文件
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            } else
                isFileCreateSuccess = true;

            //返回数据库文件对象
            if(isFileCreateSuccess)
                return dbFile;
            else
                return null;
        }
    }

    /**
     * 重载这个方法，是用来打开SD卡上的数据库的，android 2.3及以下会调用这个方法。
     *
     * @param    name
     * @param    mode
     * @param    factory
     */
    @Override
    public SQLiteDatabase openOrCreateDatabase(String name, int mode,
                                               SQLiteDatabase.CursorFactory factory) {
        SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), null);
        return result;
    }

    /**
     * Android 4.0会调用此方法获取数据库。
     *
     * @see ContextWrapper#openOrCreateDatabase(String, int,
     *              SQLiteDatabase.CursorFactory,
     *              DatabaseErrorHandler)
     * @param    name
     * @param    mode
     * @param    factory
     * @param     errorHandler
     */
    @Override
    public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory,
                                               DatabaseErrorHandler errorHandler) {
        SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), null);
        return result;
    }
}

Config 定义的变量

package com.tan.searchhistory.constants;

public class Config {

    //数据库
    public static final int DATABASE_VERSION = 1;//如果数据库升级此处需+1
    public static final String DATABASE_NAME = "search.db";
    public static final String DB_PATH = "/search_db";

    //搜索历史记录个数
    public static final int DEFAULT_HISTORY_SEARCH_LIST_NUM = 5;
    
}
DatabaseContext  代码在上面


DBHelper

package com.tan.searchhistory.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.tan.searchhistory.constants.Config;
import com.tan.searchhistory.utils.Dog;


public class DBHelper extends SQLiteOpenHelper {

    private static final String TAG = DBHelper.class.getSimpleName();

    private static DatabaseContext mDatabaseContext;

    private static DBHelper mInstance;

    private Context mContext;
    /**
     *
     * @param context
     */
    private DBHelper(Context context) {
        super(context, Config.DATABASE_NAME, null, Config.DATABASE_VERSION);
        this.mContext = context;
    }

    public static DBHelper getInstance (Context context){
        if (null == mInstance){
            synchronized (DBHelper.class){
                if (null == mInstance){
                    //实例自定义Context
                    mDatabaseContext = new DatabaseContext(context);
                    //使用自定义Context 创建SQLiteOpenHelper
                    mInstance = new DBHelper(mDatabaseContext);
                }
            }
        }
        return mInstance;
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        Dog.d(TAG,"--onOpen---");
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Dog.d(TAG , "---onCreate start ---");
        db.execSQL(DBTable.CREATE_HISTORY);
        Dog.d(TAG , "---onCreate finish---");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Dog.d(TAG , "--onUpgrade ---oldVersion=" + oldVersion + "----newVersion=" + newVersion);

    }
}
DBManager 
package com.tan.searchhistory.db;

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

import com.tan.searchhistory.models.HistoryItem;
import com.tan.searchhistory.utils.Dog;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by pateo on 17-12-27.
 */

public class DBManager {

    protected static final String TAG = DBManager.class.getSimpleName();

    private static DBManager mInstance;

    private static DBHelper mDBHelper;

    private static SQLiteDatabase mDB;

    private DBManager(Context context){
        mDBHelper = DBHelper.getInstance(context);
        mDB = mDBHelper.getReadableDatabase();
    }

    public static DBManager getInstance(Context context){
        if (null == mInstance){
            synchronized (DBManager.class){
                if (null == mInstance){
                    mInstance = new DBManager(context);
                }
            }
        }
        return mInstance;
    }

    //获取所有的记录列表
    public List<HistoryItem> queryAllHistoryList(){
        mDB = mDBHelper.getReadableDatabase();
        List<HistoryItem> historyItemList = new ArrayList<HistoryItem>();
        Cursor cursor = null ;

        try{
            cursor = mDB.query(DBTable.HISTORY_TABLE_NAME , null ,null ,null , null ,null , null);
            Dog.d(TAG , "--queryAllHistoryList---cursor=" + cursor.getCount());
            if (null != cursor && cursor.getCount() > 0){
                cursor.moveToFirst();
                do {
                    int id = cursor.getInt(cursor.getColumnIndex(DBTable.ID));
                    String name = cursor.getString(cursor.getColumnIndex(DBTable.NAME));
                    String createTime = cursor.getString(cursor.getColumnIndex(DBTable.CREATE_TIME));

                    HistoryItem historyItem = new HistoryItem(id ,name , createTime);
                    Dog.d(TAG , " ---queryAllHistoryList----historyItem=" + historyItem);
                    historyItemList.add(historyItem);

                }while (cursor.moveToNext());

            }
        }catch ( Exception e ){
            Dog.d(TAG , "Exception=" + e.toString());
        }finally {
            if (null != cursor){
                cursor.close();
            }
            closeDatabase();
        }
        return historyItemList;
    }

    //orderby  查询最新的几条数据
    //查询所有数据
    public static List<HistoryItem> queryLatestHistoryListByNum(int num){
        mDB = mDBHelper.getReadableDatabase();

        List<HistoryItem> historyItemList = new ArrayList<HistoryItem>();
        Cursor cursor = null ;

        try{

            String sql = "select * from " + DBTable.HISTORY_TABLE_NAME+ " ORDER BY " + DBTable.ID + " DESC" + " limit " + num;
            Dog.d(TAG, "queryLatestHistoryListByNum-->sql:" + sql);
            cursor = mDB.rawQuery(sql, null);
            //这种查询没法限制条数
//            cursor = mDB.query(DBTable.HISTORY_TABLE_NAME , null ,null ,null,null ,null , DBTable.ID + " desc" ); //按照id 降序排序(倒序)
            Dog.d(TAG, "cursor.size ======" + cursor.getCount());
            if (cursor != null && cursor.getCount() > 0) {
                cursor.moveToFirst();
                do {
                    Dog.d(TAG , "----queryLatestHistoryListByNum----num=" + num + "----historyItemList.size()=" + historyItemList.size());
                    int id = cursor.getInt(cursor.getColumnIndex(DBTable.ID));
                    String name = cursor.getString(cursor.getColumnIndex(DBTable.NAME));
                    String createTime = cursor.getString(cursor.getColumnIndex(DBTable.CREATE_TIME));

                    HistoryItem historyItem = new HistoryItem(id, name , createTime);
                    Dog.d(TAG , " ---queryLatestHistoryListByNum----historyItem=" + historyItem);
                    historyItemList.add(historyItem);
                } while (cursor.moveToNext());
            }

        }catch ( Exception  e ){

            Dog.d(TAG , "Exception=" + e.toString());

        }finally {
            if (null != cursor){
                cursor.close();
            }
            closeDatabase();

        }

        return historyItemList;
    }


    //查询最新的一条数据  last:最后   latest:最新
    public HistoryItem queryLatestHistoryItem (){
        HistoryItem historyItem = null ;

        mDB = mDBHelper.getReadableDatabase();
        Cursor cursor = null ;
        try{
            cursor = mDB.query(DBTable.HISTORY_TABLE_NAME , null ,null ,null , null ,null , null);
            Dog.d(TAG , "--queryLatestHistoryItem---cursor=" + cursor.getCount());
            if (null != cursor && cursor.getCount() > 0){
                //指针指向最后一个.最后一个插入的数据是最新的数据
                cursor.moveToLast();

                int id = cursor.getInt(cursor.getColumnIndex(DBTable.ID));
                String name = cursor.getString(cursor.getColumnIndex(DBTable.NAME));
                String createTime = cursor.getString(cursor.getColumnIndex(DBTable.CREATE_TIME));

                historyItem = new HistoryItem( name,  createTime);
                Dog.d(TAG , " ---queryLatestHistoryItem----historyItem=" + historyItem);
            }
        }catch ( Exception e ){
            Dog.d(TAG , "Exception=" + e.toString());
        }finally {
            if (null != cursor){
                cursor.close();
            }
            closeDatabase();
        }

        return historyItem;
    }


    //更新数据
    public boolean updateHistoryItem(HistoryItem historyItem){
        boolean result = false;

        mDB = mDBHelper.getWritableDatabase();
        if (null != historyItem){
            int id = historyItem.getId();
            String name = historyItem.getName();
            ContentValues cValue = new ContentValues();
            cValue.put(DBTable.NAME , historyItem.getName());
            mDB.update(DBTable.HISTORY_TABLE_NAME , cValue , DBTable.ID + "=?" , new String[]{id + ""});  //id 为int 转换成string
            result = true;
        }
        return result;
    }


    //插入数据
    public boolean insertHistoryItem (HistoryItem historyItem){
        mDB = mDBHelper.getReadableDatabase();
        if (null != historyItem){
            //插入前应该先判断数据库是否存在
            ContentValues cValue = new ContentValues();
            cValue.put(DBTable.NAME , historyItem.getName());
            cValue.put(DBTable.CREATE_TIME , historyItem.getCreateTime());

            mDB.insert(DBTable.HISTORY_TABLE_NAME , null ,cValue);
            closeDatabase();
            return true;
        }
        closeDatabase();
        return false;
    }

    //删除快速查询
    public static boolean deleteHistory (HistoryItem historyItem){
        mDB = mDBHelper.getReadableDatabase();
        int result = 0;

        //删除条件
        String whereClause = ""+DBTable.ID + "=?";
        //删除条件参数
        String[] whereArgs = { historyItem.getId() + ""};
        //执行删除
        try {
            result = mDB.delete(DBTable.HISTORY_TABLE_NAME ,whereClause,whereArgs);
            Dog.d(TAG , "deleteHistory- ---result=" + result);

        }catch (Exception e){
            Dog.d(TAG , "deleteHistory- ---Exception=" + e.toString());
        }finally {
            closeDatabase();
        }

        if (result > 0){
            return  true;
        }
        return false;
    }

    //删除多个快速查询
    public static boolean deleteQuickQuery( List<HistoryItem> historyItemList){
        mDB = mDBHelper.getReadableDatabase();
        try{

            for (HistoryItem item : historyItemList){
                //删除条件
                String whereClause = ""+DBTable.ID+"=?";
                //删除条件参数
                String[] whereArgs = { item.getId() + ""};
                //执行删除
                mDB.delete(DBTable.HISTORY_TABLE_NAME ,whereClause,whereArgs);
            }
            return  true;

        }catch (Exception e ){
            Dog.d(TAG , "deleteQuickQuery--list ---Exception=" + e.toString());
        }finally {
            closeDatabase();
        }
        return false;
    }

    //删除所有的数据
    public static boolean deleteAllHistory (){
        mDB = mDBHelper.getReadableDatabase();
        try{
            //执行删除
            mDB.delete(DBTable.HISTORY_TABLE_NAME ,null,null);
            return  true;

        }catch (Exception e ){
            Dog.d(TAG , "deleteAllHistory--list ---Exception=" + e.toString());
        }finally {
            closeDatabase();
        }
        return false;
    }

    // 关闭数据库对象
    public static void closeDatabase() {
        if (mDB != null && mDB.isOpen()) {
            mDB.close();
        }
    }

}


模糊查询 or

查询某几列 是否包含key

//搜索本地资源
    public ArrayList<SkinBean> querySKinByKey(String key){

        mDB = mDBHelper.getReadableDatabase();
        ArrayList<SkinBean> mResultSkinList = new ArrayList<>();
        Cursor cursor = null ;

        try{
            cursor = mDB.rawQuery("select * from "+ DBTable.SYSTEM_SKIN_TABLE_NAME +" where "+ DBTable.RESOURCE_NAME + " like ? " + " or " + DBTable.RESOURCE_CLASSIFY + " like ? ", new String[]{ "%" + key + "%" ,  "%" + key + "%"});
            if (null != cursor && cursor.getCount() > 0){
                cursor.moveToFirst();

                do{
                    String sid = cursor.getString(cursor.getColumnIndex(DBTable.SID));
                    String typeId = cursor.getString(cursor.getColumnIndex(DBTable.TYPE_ID));
                    String resourceName = cursor.getString(cursor.getColumnIndex(DBTable.RESOURCE_NAME));
                    String resourceDesc = cursor.getString(cursor.getColumnIndex(DBTable.RESOURCE_DESC));
                    String resourceProvider = cursor.getString(cursor.getColumnIndex(DBTable.RESOURCE_PROVIDER));
                    double resourcePrice = cursor.getDouble(cursor.getColumnIndex(DBTable.RESOURCE_PRICE));
                    String resourceClassify = cursor.getString(cursor.getColumnIndex(DBTable.RESOURCE_CLASSIFY));
                    long resourceClassifyHeaderId = cursor.getLong(cursor.getColumnIndex(DBTable.RESOURCE_CLASSIFY_HEADER_ID));

                    List<String> resourceThumbnail = new ArrayList<>();
                    String strResourceThumbnail = cursor.getString(cursor.getColumnIndex(DBTable.THUMBNAIL_LIST));
                    String[] all =strResourceThumbnail.split("\\|");
                    for (int i=0 ; i < all.length ; i++){
                        resourceThumbnail.add(all[i]);
                    }

                    String resourceUrl = cursor.getString(cursor.getColumnIndex(DBTable.RESOURCE_URL));
                    double resourceSize = cursor.getDouble(cursor.getColumnIndex(DBTable.RESOURCE_SIZE));
                    String createDate = cursor.getString(cursor.getColumnIndex(DBTable.CREATE_DATE));
                    String updateDate = cursor.getString(cursor.getColumnIndex(DBTable.UPDATE_DATE));
                    String screenWidth = cursor.getString(cursor.getColumnIndex(DBTable.SCREEN_WIDTH));
                    String screenHeight = cursor.getString(cursor.getColumnIndex(DBTable.SCREEN_HEIGHT));
                    String systemVersion = cursor.getString(cursor.getColumnIndex(DBTable.SYSTEM_VERSION));
                    int isRecommend = cursor.getInt(cursor.getColumnIndex(DBTable.IS_RECOMMEND));
                    int isHoliday = cursor.getInt(cursor.getColumnIndex(DBTable.IS_HOLIDAY));
                    String showStartDate = cursor.getString(cursor.getColumnIndex(DBTable.SHOW_START_DATE));
                    String showEndDate = cursor.getString(cursor.getColumnIndex(DBTable.SHOW_END_DATE));
                    int hasDownload = cursor.getInt(cursor.getColumnIndex(DBTable.HAS_DOWNLOAD));
                    int hasUpdate = cursor.getInt(cursor.getColumnIndex(DBTable.HAS_UPDATE));

                    SkinBean bean = new SkinBean( sid,  typeId,  resourceName,  resourceDesc,  resourceProvider, resourcePrice,  resourceClassify,  resourceClassifyHeaderId,  resourceThumbnail,  resourceUrl,  resourceSize,  createDate,  updateDate,  screenWidth,  screenHeight,  systemVersion,  isRecommend,  isHoliday,  showStartDate,  showEndDate, hasDownload , hasUpdate);
                    Dog.d(TAG , " ---querySKinByKey----bean=" + bean);
                    mResultSkinList.add(bean);
                }while (cursor.moveToNext());
            }
        }catch (Exception e ){
            e.printStackTrace();
            Dog.d(TAG , "---querySKinByKey--e=" + e);
        }finally {

            if (null != cursor){
                cursor.close();
            }
        }
        return mResultSkinList;
    }

 cursor = mDB.rawQuery("select * from "+ DBTable.SYSTEM_SKIN_TABLE_NAME +" where "+ DBTable.RESOURCE_NAME + " like ? " + " or " + DBTable.RESOURCE_CLASSIFY + " like ? ", new String[]{ "%" + key + "%" ,  "%" + key + "%"});
查询 name 和 classify  两列 , 是否含有key 值

注意

new String[]{ "%" + key + "%" ,  "%" + key + "%"}
必须带% .


查询数据 

            String sql = "select * from " + AddedCitySqliteArgs.TABLE_NAME + " WHERE "
                    + AddedCitySqliteArgs._ID + "=" + id + " GROUP BY " + AddedCitySqliteArgs.CITY_LAT + ", " +
                    AddedCitySqliteArgs.CITY_LON + " ORDER BY " + AddedCitySqliteArgs._ID + " ASC" + " limit " + num;

            Log.d(TAG, "queryAddedCityById-->sql:" + sql);
            cursor = db.rawQuery(sql, null);


String sql = "select * from " + DBTable.HISTORY_TABLE_NAME+ " ORDER BY " + DBTable.ID + " DESC" + " limit " + num;
Dog.d(TAG, "queryLatestHistoryListByNum-->sql:" + sql);
cursor = mDB.rawQuery(sql, null);

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值