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();
}
}
}