十八、封装SQLite

绝地灬酷狼 2022-09-25 09:21 182阅读 0赞

把一些数据放在本地数据库，当离线时，展示缓存的数据给用户，不至于什么内容都没有。

首先创建数据库类，每次新登录的用户创建不同的数据库：

public class KengOpenHelper extends SQLiteOpenHelper{
    
        private static String DB_NAME_ = "user_";
        private static int version = 1;
    
        public KengOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
            super(context, name, factory, version, errorHandler);
        }
    
        public KengOpenHelper(Context context, int id) {
            this(context, getDbName(id), null, version, mErrorHandler);
        }
    
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(UserData.CREATE_USER_TABLE);
        }
    
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    
        }
    
        /**
         * 获取数据库名字
         * @return
         */
        private static String getDbName(int id) {
            return DB_NAME_ + id;
        }
    
        private static DatabaseErrorHandler mErrorHandler = new DatabaseErrorHandler() {
            @Override
            public void onCorruption(SQLiteDatabase dbObj) {
                // 数据库异常提示
                KengToast.show(R.string.db_corruption);
            }
        };
    }

上述创建表的语句UserData.CREATE\_USER\_TABLE：

public static String CREATE_USER_TABLE = "create table "+ TABLE_NAME + "(" +
            ID + " integer primary key," +
            NICKNAME + " text," +
            HEADIMG + " text," +
            CITY + " text," +
            SIGNATURE + " text," +
            GENDER + " integer," +
            LEVEL + " integer," +
            COIN + " integer" +
            " );";

在application中获取数据库单例：

private static KengApplication mKengApplication;
    private KengOpenHelper mKengOpenHelper;
    
    public static KengApplication getInstance() {
        return mKengApplication;
    }
    
    public static KengOpenHelper getOpenHelper() {
        if (mKengApplication != null) {
            return mKengApplication.mKengOpenHelper;
        }
        return null;
    }

application中更新数据库方法：

public void updateOpenHelper() {
        if (UserUtil.isLogin()) {
            mKengOpenHelper = new KengOpenHelper(mKengApplication.getApplicationContext(), UserUtil.getUserId());
        } else {
            mKengOpenHelper = null;
        }
    }

登录成功后调用更新数据库和保存用户信息到本地数据库：

KengApplication.getInstance().updateOpenHelper();
    UserData.saveUser(userInfo);

在其他地方都可以使用本地保存的信息了：

BaseUserInfo userInfo = UserData.getUser(UserUtil.getUserId());
    if (userInfo == null)
        return;
    headerImage.setImageURI(UriUtil.parseUriOrNull(userInfo.getHeadimg()));
    nameTextView.setText(userInfo.getNickname());

下面是用户操作类的完整代码：

public class UserData {
    
        private static String TABLE_NAME = "user";    // 表名
    
        private static String ID = "id";               // 用户id
        private static String NICKNAME = "nickname";   // 用户昵称
        private static String HEADIMG = "headimg";     // 用户头像地址
        private static String CITY = "city";           // 用户城市
        private static String SIGNATURE = "signature"; // 用户签名
        private static String GENDER = "gender";       // 用户性别
        private static String LEVEL = "level";         // 用户等级
        private static String COIN = "coin";           // 用户金币
    
        /**
         * 创建user表
         */
        public static String CREATE_USER_TABLE = "create table "+ TABLE_NAME + "(" +
                ID + " integer primary key," +
                NICKNAME + " text," +
                HEADIMG + " text," +
                CITY + " text," +
                SIGNATURE + " text," +
                GENDER + " integer," +
                LEVEL + " integer," +
                COIN + " integer" +
                " );";
    
        /**
         * 获取用户信息
         * @param id 用户id
         * @return BaseUserInfo
         */
        public static BaseUserInfo getUser(int id) {
            KengOpenHelper openHelper = KengApplication.getOpenHelper();
            if (openHelper == null)
                return null;
            SQLiteDatabase db = openHelper.getReadableDatabase();
            if (db == null)
                return null;
            StringBuffer sql = new StringBuffer();
            sql.append("select * from ");
            sql.append(TABLE_NAME);
            sql.append(" where ");
            sql.append(ID);
            sql.append("=?");
            Cursor cursor = db.rawQuery(sql.toString(), new String[]{String.valueOf(id)});
            if (cursor == null)
                return null;
            if (cursor.moveToLast()) {
                BaseUserInfo userInfo = new BaseUserInfo();
                userInfo.setId(cursor.getInt(cursor.getColumnIndex(ID)));
                userInfo.setNickname(cursor.getString(cursor.getColumnIndex(NICKNAME)));
                userInfo.setCity(cursor.getString(cursor.getColumnIndex(CITY)));
                userInfo.setCoin(cursor.getLong(cursor.getColumnIndex(COIN)));
                userInfo.setGender(cursor.getInt(cursor.getColumnIndex(GENDER)));
                userInfo.setHeadimg(cursor.getString(cursor.getColumnIndex(HEADIMG)));
                userInfo.setLevel(cursor.getInt(cursor.getColumnIndex(LEVEL)));
                userInfo.setSignature(cursor.getString(cursor.getColumnIndex(SIGNATURE)));
                cursor.close();
                return userInfo;
            }
            return null;
        }
    
        /**
         * 保存用户信息
         * @param userInfo
         */
        public static void saveUser(BaseUserInfo userInfo) {
            KengOpenHelper openHelper = KengApplication.getOpenHelper();
            if (openHelper == null)
                return;
            SQLiteDatabase db = openHelper.getWritableDatabase();
            if (db == null)
                return;
            ContentValues cv = new ContentValues();
            cv.put(ID, userInfo.getId());
            cv.put(NICKNAME, userInfo.getNickname());
            cv.put(CITY, userInfo.getCity());
            cv.put(COIN, userInfo.getCoin());
            cv.put(GENDER, userInfo.getGender());
            cv.put(HEADIMG, userInfo.getHeadimg());
            cv.put(LEVEL, userInfo.getLevel());
            cv.put(SIGNATURE, userInfo.getSignature());
            db.replace(TABLE_NAME, null, cv); // 插入或更新
        }
    }

异常：

attempt to re-open an already-closed object: SQLiteDatabase

![Center][]

出问题代码：

KengOpenHelper openHelper = KengApplication.getOpenHelper();
    SQLiteDatabase db = openHelper.getReadableDatabase();
    Cursor cursor;
    /**
    	逻辑代码
    **/
    cursor.close();
    db.close();

异常原因就是我最后一句，把SQLiteDatabase关闭掉了，虽然它是我们创建的局部变量，但是不能关闭，如果关闭，再在其他地方创建SQLiteDatabase就会报错，所以我们不能close，除非不能使用数据库。

参考：

[http://blog.csdn.net/harvic880925/article/details/24516545][http_blog.csdn.net_harvic880925_article_details_24516545]

[http://www.cnblogs.com/Excellent/archive/2011/11/19/2254888.html][http_www.cnblogs.com_Excellent_archive_2011_11_19_2254888.html]

[http://blog.csdn.net/flowingflying/article/details/6841070][http_blog.csdn.net_flowingflying_article_details_6841070]

[http://blog.csdn.net/zhufuing/article/details/14455823][http_blog.csdn.net_zhufuing_article_details_14455823]

[Center]: /images/20220718/77ff999dd37a44be88a0bc325adcaa30.png
[http_blog.csdn.net_harvic880925_article_details_24516545]: http://blog.csdn.net/harvic880925/article/details/24516545
[http_www.cnblogs.com_Excellent_archive_2011_11_19_2254888.html]: http://www.cnblogs.com/Excellent/archive/2011/11/19/2254888.html
[http_blog.csdn.net_flowingflying_article_details_6841070]: http://blog.csdn.net/flowingflying/article/details/6841070
[http_blog.csdn.net_zhufuing_article_details_14455823]: http://blog.csdn.net/zhufuing/article/details/14455823