【39】Android原生Sqlite数据库设计

Android原生Sqlite数据库设计

一.原生Sqlite数据库设计目标

1.支持新建多个数据库
2.支持数据库名称与各个数据库表名称的集中管理
3.支持数据库升级时对数据表数据的备份，数据表结构的修改，主外键的创建，索引的创建等
4.封装增、删、改操作开启事务，提交事务.
5.封装查询操作时游标cusor的关闭

二.实现思路

1.需要支持多个数据库，则可以通过创建多个SQLiteOpenHelper子类，分别指定数据库名实现。
2.数据库的建表、升级等操作统一在各个SQLiteOpenHelper子类中完成，而数据表的增、删、改、查在各个DAO中去完成。
3.数据库名称与数据库表名称统一在常量类中进行声明.
4.为各个DAO提供一个基类BaseDAO，用于封装执行增、删、改、查等操作需要用到的公共功能，如开启事务、提交事务、游标关闭等。

三.具体实现

package www.gdc.com.sqlitedemo.db.dbhelper;

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

import www.gdc.com.sqlitedemo.constant.DbConstant;


/**
 * @author XiongJie
 * @version appVer
 * @package www.gdc.com.sqlitedemo.dao
 * @file DBOpenHelper
 * @brief 数据库操作助手
 * @date 2017/9/15
 * @since appVer
 */
public class UserDbOpenHelper extends SQLiteOpenHelper {

    public UserDbOpenHelper(Context context) {
        super(context, DbConstant.Value.DB_NAME_USER, null, DbConstant.VERSION
                .DB_VERSION_USER);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        createTable(db);
    }

    /**
     * 数据库是有版本的，如果数据库版本更新了(在手机中的版本为1，新升级的版本为2时)，
     * 此方法将会被执行。
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < newVersion && DbConstant.VERSION.DB_VERSION_USER ==
                newVersion) {
            //1、备份与更新数据表
            updateTable(db);

            //2.其他的升级操作，如为表创建索引，添加列,删除列
        }
    }

    private void createTable(SQLiteDatabase db) {
        String userTable = String.format("create table %s(_id integer primary key " +
                "" + "autoincrement,userid text,username text,email text,mobile " +
                "text);", DbConstant.TABLE.TB_USER);
        // db没有被其他线程锁住
        if(!db.isDbLockedByOtherThreads() || !db.isDbLockedByCurrentThread()){
            db.beginTransaction();
            try {
                db.execSQL(userTable);
                db.setTransactionSuccessful();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                db.endTransaction();
            }
        }
    }

    /**
     * 备份与更新数据表 此例为t_user_info表新增一个字段
     *
     * @param db
     */
    private void updateTable(SQLiteDatabase db) {
        //1.创建一张临时表用于数据备份
        String tempTable = String.format("create table %s(_id integer primary key " +
                "autoincrement,userid text,username text,email text,mobile text,qq" +
                " text);",DbConstant.TABLE.TB_TEMP_USER);
        db.execSQL(tempTable);

        //2.将表中现有数据插入到临时表中
        String bakTable = String.format("insert into %s(_id,userid,username,email," +
                "mobile) select _id,userid,username,email,mobile from %s;",
                DbConstant.TABLE.TB_TEMP_USER,DbConstant.TABLE.TB_USER);

        db.execSQL(bakTable);

        //3.删除原数据表
        String deleteTable = String.format("drop table if exists %s;",DbConstant
                .TABLE.TB_USER);
        db.execSQL(deleteTable);

        //4.重命名临时表名称为原表名
        String alterTempTable = String.format("alter table %s rename to %s;",
                DbConstant.TABLE.TB_TEMP_USER,DbConstant.TABLE.TB_USER);

        db.execSQL(alterTempTable);
    }

}

package www.gdc.com.sqlitedemo.db.dao;

import android.content.ContentValues;
import android.database.sqlite.SQLiteOpenHelper;

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

import www.gdc.com.sqlitedemo.constant.DbConstant;
import www.gdc.com.sqlitedemo.entity.UserInfo;


/**
 * @author XiongJie
 * @version appVer
 * @package www.gdc.com.sqlitedemo.db.dao
 * @file UserInfoDao
 * @brief
 * @date 2017/9/15
 * @since appVer
 */
public class UserInfoDao extends BaseDao {

    public UserInfoDao(SQLiteOpenHelper helper) {
        super(helper);
    }

    public void insert(UserInfo userInfo){
        ContentValues cv = new ContentValues();
        if(null != userInfo){
            cv.put("userid",userInfo.getUserid());
            cv.put("username",userInfo.getUsername());
            cv.put("email",userInfo.getEmail());
            cv.put("mobile",userInfo.getMobile());
        }
        db.insert(DbConstant.TABLE.TB_USER,null,cv);
    }

    public void insertUserInfo(UserInfo userInfo) {
        //1.未升级前
        String sql = String.format("insert into %s(userid,username,email,mobile) " +
                "values (?,?,?,?)", DbConstant.TABLE.TB_USER);
        db.execSQL(sql, new Object[]{ userInfo.getUserid(), userInfo.getUsername()
                , userInfo.getEmail(), userInfo.getMobile() });

        //s2.升级后
        /*String sql = String.format("insert into %s(userid,username,email,mobile," +
                "qq) " +
                "values (?,?,?,?,?)", DbConstant.TABLE.TB_USER);
        db.execSQL(sql, new Object[]{ userInfo.getUserid(), userInfo.getUsername()
                , userInfo.getEmail(), userInfo.getMobile(),userInfo.getQq() });*/
    }

    public void updateUserInfo(UserInfo userInfo) {
        //s1:未升级前
        String sql = String.format("update %s set username=? where userid=?",
                DbConstant.TABLE.TB_USER);
        db.execSQL(sql, new Object[]{ userInfo.getUsername(),
                userInfo.getUserid() });

        //s2:升级后
        /*String sql = String.format("update %s set username=?,qq=? where userid=?",
                DbConstant.TABLE.TB_USER);
        db.execSQL(sql, new Object[]{ userInfo.getUsername(), userInfo.getQq(),
                userInfo.getUserid() });*/
    }

    public UserInfo getUserInfo(String userId) {
        UserInfo userInfo = null;
        try {
            db = helper.getReadableDatabase();
            //s1.升级之前
            String sql = String.format("select userid,username,email,mobile" +
                    "from %s where userid=?",DbConstant.TABLE.TB_USER);
            cursor = db.rawQuery(sql, new String[]{ userId });

            if (null != cursor && cursor.moveToNext()) {
                userInfo = new UserInfo(cursor.getString(cursor.getColumnIndex
                        ("userid")), cursor.getString(cursor.getColumnIndex
                        ("username")), cursor.getString(cursor.getColumnIndex
                        ("email")), cursor.getString(cursor.getColumnIndex
                        ("mobile")));
            }

            //s2.升级之后
            /*String sql = String.format("select userid,username,email,mobile,qq " +
                    "from %s where userid=?",DbConstant.TABLE.TB_USER);
            cursor = db.rawQuery(sql, new String[]{ userId });

            if (null != cursor && cursor.moveToNext()) {
                userInfo = new UserInfo(cursor.getString(cursor.getColumnIndex
                        ("userid")), cursor.getString(cursor.getColumnIndex
                        ("username")), cursor.getString(cursor.getColumnIndex
                        ("email")), cursor.getString(cursor.getColumnIndex
                        ("mobile")), cursor.getString(cursor.getColumnIndex("qq")));
            }*/
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeCursor();
            db.close();
        }
        return userInfo;
    }

    public List<UserInfo> getUserInfoList() {
        List<UserInfo> userInfoList = new ArrayList<UserInfo>(0);
        try {
            db = helper.getReadableDatabase();
            //s1.升级之前
            String sql = String.format("select userid,username,email,mobile " +
                    "from %s",DbConstant.TABLE.TB_USER);
            cursor = db.rawQuery(sql, new String[]{});
            while (cursor.moveToNext()) {
                UserInfo userInfo = new UserInfo(cursor.getString(cursor
                        .getColumnIndex("userid")), cursor.getString(cursor
                        .getColumnIndex("username")), cursor.getString(cursor
                        .getColumnIndex("email")), cursor.getString(cursor
                        .getColumnIndex("mobile")));
                userInfoList.add(userInfo);
            }

            //s2.升级之后
            /*String sql = String.format("select userid,username,email,mobile,qq " +
                    "from %s",DbConstant.TABLE.TB_USER);
            cursor = db.rawQuery(sql, new String[]{});
            while (cursor.moveToNext()) {
                UserInfo userInfo = new UserInfo(cursor.getString(cursor
                        .getColumnIndex("userid")), cursor.getString(cursor
                        .getColumnIndex("username")), cursor.getString(cursor
                        .getColumnIndex("email")), cursor.getString(cursor
                        .getColumnIndex("mobile")), cursor.getString(cursor
                        .getColumnIndex("qq")));
                userInfoList.add(userInfo);
            }*/
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeCursor();
            db.close();
        }
        return userInfoList;
    }

    public void deleteUserInfo(UserInfo userInfo) {
        String sql = String.format("delete from %s where userid=?",DbConstant
                .TABLE.TB_USER);
        db.execSQL(sql, new Object[]{ userInfo.getUserid() });
    }

}

package www.gdc.com.sqlitedemo.db.dao;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


/**
 * @author XiongJie
 * @version appVer
 * @package www.gdc.com.sqlitedemo.db.dao
 * @file BaseDao
 * @brief
 * @date 2017/9/18
 * @since appVer
 */
public class BaseDao {
    protected SQLiteOpenHelper helper;
    protected SQLiteDatabase db;
    protected Cursor         cursor;

    public BaseDao(SQLiteOpenHelper helper) {
        this.helper = helper;
    }

    /**
     * 手动启动事务 主要应用于新增、修改、删除
     */
    public void beginTrans() {
        db = helper.getWritableDatabase();
        db.beginTransaction();
    }

    /**
     * 手动提交事务 主要应用于新增、修改、删除
     */
    public void commit() {
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
    }

    /**
     * 关闭游标，主要应用于查询操作
     */
    public void closeCursor(){
        if (null != cursor) {
            cursor.close();
            cursor = null;
        }
    }

}

具体调用代码：

package www.gdc.com.sqlitedemo.activity;

import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.text.TextUtils;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;

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

import www.gdc.com.sqlitedemo.R;
import www.gdc.com.sqlitedemo.adapter.UserInfoAdapter;
import www.gdc.com.sqlitedemo.db.dao.MessageDao;
import www.gdc.com.sqlitedemo.db.dbhelper.MessageDbOpenHelper;
import www.gdc.com.sqlitedemo.db.dbhelper.UserDbOpenHelper;
import www.gdc.com.sqlitedemo.db.dao.UserInfoDao;
import www.gdc.com.sqlitedemo.entity.UserInfo;


public class MainActivity extends AppCompatActivity implements View.OnClickListener {

    private Button              bt_query;
    private Button              bt_save;
    private Button              bt_update;
    private RecyclerView        rv_list;
    private EditText            et_name;
    private UserDbOpenHelper    userHelper;
    private MessageDbOpenHelper messageHelper;
    private UserInfoDao         userInfoDao;
    private MessageDao          messageDao;

    private List<UserInfo>  userInfoList;
    private UserInfoAdapter adapter;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        initView();
        messageHelper = new MessageDbOpenHelper(this);
        userHelper = new UserDbOpenHelper(this);
        userInfoDao = new UserInfoDao(userHelper);
        messageDao = new MessageDao(messageHelper);
    }

    private void initView() {
        bt_query = (Button) findViewById(R.id.bt_query);
        bt_save = (Button) findViewById(R.id.bt_save);
        bt_update = (Button) findViewById(R.id.bt_update);
        rv_list = (RecyclerView) findViewById(R.id.rv_list);
        et_name = (EditText) findViewById(R.id.et_name);

        bt_query.setOnClickListener(this);
        bt_save.setOnClickListener(this);
        bt_update.setOnClickListener(this);
        adapter = new UserInfoAdapter(this);
        adapter.setUserInfoClickListener(new UserInfoItemClickListener());
        LinearLayoutManager layoutManager = new LinearLayoutManager(this,
                LinearLayoutManager.VERTICAL, false);
        rv_list.setLayoutManager(layoutManager);
        rv_list.setAdapter(adapter);
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
        case R.id.bt_query:
            showUserInfo();
            break;
        case R.id.bt_save:
            addUserInfo();
            showUserInfo();
            break;
        case R.id.bt_update:
//            editUserInfo();
            updateUserInfo();
            showUserInfo();
            break;
        }
    }

    private void showUserInfo() {
        userInfoList = userInfoDao.getUserInfoList();
        adapter.set(userInfoList);
    }

    private List<UserInfo> initUserInfo() {
        List<UserInfo> userInfoList = new ArrayList<UserInfo>();
        UserInfo userInfo1 = new UserInfo("1001", "成龙", "jacke@163.com",
                "15968754421");
        UserInfo userInfo2 = new UserInfo("1002", "李连杰", "li@163.com",
                "15968754422");
        UserInfo userInfo3 = new UserInfo("1003", "吴京", "wujin@163.com",
                "15968754423");
        UserInfo userInfo4 = new UserInfo("1004", "赵文卓", "zhaowenzhuo@163.com",
                "15968754424");
        UserInfo userInfo5 = new UserInfo("1005", "周星驰", "zhouxingchi@163.com",
                "15968754425");
        userInfoList.add(userInfo1);
        userInfoList.add(userInfo2);
        userInfoList.add(userInfo3);
        userInfoList.add(userInfo4);
        userInfoList.add(userInfo5);
        return userInfoList;
    }

    private List<UserInfo> initUpdateDbData(){
        List<UserInfo> userInfoList = new ArrayList<UserInfo>();
        UserInfo userInfo1 = new UserInfo("1001", "成龙", "jacke@163.com",
                "15968754421","413875661");
        UserInfo userInfo2 = new UserInfo("1002", "李连杰", "li@163.com",
                "15968754422","413875662");
        UserInfo userInfo3 = new UserInfo("1003", "吴京", "wujin@163.com",
                "15968754423","413875663");
        UserInfo userInfo4 = new UserInfo("1004", "赵文卓", "zhaowenzhuo@163.com",
                "15968754424","413875664");
        UserInfo userInfo5 = new UserInfo("1005", "周星驰", "zhouxingchi@163.com",
                "15968754425","413875665");
        userInfoList.add(userInfo1);
        userInfoList.add(userInfo2);
        userInfoList.add(userInfo3);
        userInfoList.add(userInfo4);
        userInfoList.add(userInfo5);
        return userInfoList;
    }

    private synchronized void updateUserInfo(){
        List<UserInfo> userInfoList = initUpdateDbData();
        userInfoDao.beginTrans();
        for(int i = 0 ; i < userInfoList.size(); i++){
            UserInfo userInfo = userInfoList.get(i);
            userInfoDao.updateUserInfo(userInfo);
        }
        userInfoDao.commit();
    }

    private synchronized void addUserInfo() {
        List<UserInfo> userInfoList = initUserInfo();
        userInfoDao.beginTrans();
        for (int i = 0; i < userInfoList.size(); i++) {
            userInfoDao.insertUserInfo(userInfoList.get(i));
        }
        userInfoDao.commit();
    }

    private synchronized void editUserInfo() {
        String userName = et_name
                .getText()
                .toString();
        if (!TextUtils.isEmpty(userName)) {
            UserInfo userInfo = new UserInfo("1002", userName, "li@163.com",
                    "15968754422");
            userInfoDao.beginTrans();
            userInfoDao.updateUserInfo(userInfo);
            userInfoDao.commit();
        }
    }

    class UserInfoItemClickListener implements UserInfoAdapter
            .UserInfoClickListener {

        @Override
        public void onUserInfoClick(UserInfo userInfo) {
            userInfoDao.beginTrans();
            userInfoDao.deleteUserInfo(userInfo);
            userInfoDao.commit();
            showUserInfo();
        }
    }
}