Android SQLite数据库操作流程
本篇文章，我们就和大家一起来回顾下一个轻量级的数据库——SQLite数据库。Android系统集成了一个轻量级的数据库：SQLite，SQLite并不想成为像Oracle、MySQL那样的数据库。SQLite只是一个嵌入式的数据库引擎，专门适用于资源有限的设备上（入手机、PAD等）适量数据存储。
像SQLite一些原理性的东西，我在此就详细述说了，但是SQLiteOpenHelper类必须得说下，因为在实际的项目中，通常会继承SQLiteOpenHelper的子类，并通过子类的getReadableDatabase()、getWritableDatabase()方法开开数据库。
getReadableDatabase()：以读写方式打开数据库对应的SQLiteDatabase对象；
getWritableDatabase()：以写方式打开数据库对应的SQLiteDatabase对象；相信，读完下面的内容，会让你对Android SQLite数据的操作有很大的程度的提升。
我们要实现的应该增删查改的功能，像分页查询，排序查询的一些sql，在这里，我就不一一概述，请查看源码：点我查看源码
先来一张效果图
上代码：
首先，我们创建一个类，表示我们要创建的表：
/**
* @description:本地用户信息表
* @author：zzq
* @time: 2016-7-28 上午11:03:07
*/
public class LocalUserInfoTable extends AbstractTable {
public static LocalUserInfoTable instance;
public static LocalUserInfoTable getInstance() {
if (instance == null) {
instance = new LocalUserInfoTable();
}
return instance;
}
public static final class Fields implements BaseColumns {
public static final String TABLE_NAME = "_LocalUserInfoTable";
public static final String LocalUserId = "_LocalUserId";
public static final String LocalUserName = "_LocalUserName";
public static final String LocalUserSex = "_LocalUserSex";
public static final String LocalUserAge = "_LocalUserAge";
public static final String LocalUseBorthPlace = "_LocalUseBorthPlace";
public static final String LocalUseBorthTime = "_LocalUseBorthTime";
}
private static final String[] PROJECTION = new String[] { Fields._ID,
Fields.LocalUserId, Fields.LocalUserName, Fields.LocalUserSex,
Fields.LocalUserAge, Fields.LocalUseBorthPlace,
Fields.LocalUseBorthTime };
@Override
public void create(SQLiteDatabase db) {
String sql = "create table " + getTableName() + " (" + Fields._ID
+ " integer primary key," + Fields.LocalUserId + " text,"
+ Fields.LocalUserName + " text," + Fields.LocalUserSex
+ " text," + Fields.LocalUserAge + " text,"
+ Fields.LocalUseBorthPlace + " text,"
+ Fields.LocalUseBorthTime + " text);";
DataBaseManager.execSQL(db, sql);
}
@Override
protected String getTableName() {
return Fields.TABLE_NAME;
}
@Override
protected String[] getProjection() {
return PROJECTION;
}
}Activity中的实现主要在onclick点击中
@Override
public void onClick(View v) {
String id=edt_id.getText().toString();
String name=edt_name.getText().toString();
String time=edt_time.getText().toString();
switch (v.getId()) {
case R.id.btn_insert:
if(TextUtils.isEmpty(id)){
Toast.makeText(MainActivity.this, "请输入id", 0).show();
break;
}
if(TextUtils.isEmpty(name)){
Toast.makeText(MainActivity.this, "请输入姓名", 0).show();
break;
}
userInfoBean.setId(id);
userInfoBean.setName(name);
userInfoBean.setTime(time);
userInfoBean.setTime(DataFormat(System.currentTimeMillis() + ""));
LocalUserInfoTable.getInstance().save(userInfoBean);
list.clear();
list.addAll(LocalUserInfoTable.getInstance().getDataList()) ;
adapter.notifyDataSetChanged();
break;
case R.id.btn_query:
//根据id查询
if(TextUtils.isEmpty(id)){
Toast.makeText(MainActivity.this, "请输入id", 0).show();
break;
}
list.clear();
list.addAll(LocalUserInfoTable.getInstance().getDataList(id)) ;
adapter.notifyDataSetChanged();
break;
case R.id.btn_clear:
//清空表
LocalUserInfoTable.getInstance().clearData();
list.clear();
list.addAll(LocalUserInfoTable.getInstance().getDataList()) ;
adapter.notifyDataSetChanged();
break;
case R.id.btn_uodate:
//根据id修改name
LocalUserInfoTable.getInstance().updateData(id, name);
list.clear();
list.addAll(LocalUserInfoTable.getInstance().getDataList()) ;
adapter.notifyDataSetChanged();
break;
case R.id.btn_delete:
//根据id删除这条数据
LocalUserInfoTable.getInstance().deleteDataSelector(id);
list.clear();
list.addAll(LocalUserInfoTable.getInstance().getDataList();
adapter.notifyDataSetChanged();
break;
case R.id.btn_next:
// Intent intent = new Intent(MainActivity.this, SecondActivity.class);
// startActivity(intent);
break;
default:
break;
}
}adaper里面的操作相关代码，我就不贴出来了，这个也比较简单，接下来我贴出上面进行表的操作的一些方法：
/**
* 存数据
*
* @param msg
*/
public void save(UserInfoBean msg) {
SQLiteDatabase db = DataBaseManager.getInstance().getWritableDatabase();
ContentValues mVm = getContentValues(msg);
db.insert(getTableName(), null, mVm);
}
/**
* 查询
*
* @param userId
* @return
*/
public List<UserInfoBean> getDataList(String userId) {
List<UserInfoBean> list = new ArrayList<UserInfoBean>();
SQLiteDatabase db = DataBaseManager.getInstance().getReadableDatabase();
Cursor cursor = query(userId);
for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
UserInfoBean bean = toData(cursor);
list.add(bean);
}
cursor.close();
db.close();
return list;
}
public List<UserInfoBean> getDataList() {
List<UserInfoBean> list = new ArrayList<UserInfoBean>();
SQLiteDatabase db = DataBaseManager.getInstance().getReadableDatabase();
Cursor cursor = query();
for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
UserInfoBean bean = toData(cursor);
list.add(bean);
}
cursor.close();
db.close();
return list;
}
/**
* 根据id查询
* @param userId
* @return
*/
public final Cursor query(String userId) {
SQLiteDatabase db = DataBaseManager.getInstance().getReadableDatabase();
Cursor cursor = db.query(getTableName(), getProjection(),
Fields.LocalUserId+"=?", new String[] { userId }, null, null, null);
return cursor;
}
/**
* 查询所有数据
* @return
*/
public final Cursor query() {
SQLiteDatabase db = DataBaseManager.getInstance().getReadableDatabase();
Cursor cursor = db.query(getTableName(), getProjection(),
null, null, null, null, null);
return cursor;
}
/**
* 清空数据库
*/
public void clearData() {
SQLiteDatabase db = DataBaseManager.getInstance().getWritableDatabase();
db.delete(getTableName(), null, null);
db.close();
}
/**
* 根据name删除表中的数据
*
* @param name
*/
public void deleteDataSelector(String id) {
SQLiteDatabase db = DataBaseManager.getInstance().getWritableDatabase();
db.delete(getTableName(), Fields.LocalUserId + "=?",
new String[] { id });
db.close();
}
/**
* 更改数据 根据userId更改name
*
* @param userId
* @param name
*/
public void updateData(String userId, String name) {
SQLiteDatabase db = DataBaseManager.getInstance().getWritableDatabase();
ContentValues cvValues = new ContentValues();
cvValues.put(Fields.LocalUserName, name);
db.update(getTableName(), cvValues, Fields.LocalUserId + "=?",
new String[] { userId });
db.close();
}
private UserInfoBean toData(Cursor cursor) {
UserInfoBean data = new UserInfoBean();
data.setId(cursor.getString(cursor.getColumnIndex(Fields.LocalUserId)));
data.setName(cursor.getString(cursor
.getColumnIndex(Fields.LocalUserName)));
data.setSex(cursor.getString(cursor.getColumnIndex(Fields.LocalUserSex)));
data.setAge(cursor.getString(cursor.getColumnIndex(Fields.LocalUserAge)));
data.setBorthPlace(cursor.getString(cursor
.getColumnIndex(Fields.LocalUseBorthPlace)));
data.setTime(cursor.getString(cursor
.getColumnIndex(Fields.LocalUseBorthTime)));
return data;
}
public ContentValues getContentValues(UserInfoBean msg) {
ContentValues cValues = new ContentValues();
cValues.put(Fields.LocalUserId, msg.getId());
cValues.put(Fields.LocalUserName, msg.getName());
cValues.put(Fields.LocalUserSex, msg.getSex());
cValues.put(Fields.LocalUserAge, msg.getAge());
cValues.put(Fields.LocalUseBorthPlace, msg.getBorthPlace());
cValues.put(Fields.LocalUseBorthTime, msg.getTime());
Log.i("user", "----" + msg.getTime());
return cValues;
}重点：使用本数据库的封装还需要下面这三个类：
/**
* 抽象的表
* @author：zzq
*
*/
public abstract class AbstractTable implements DatabaseTable {
protected abstract String getTableName();
protected abstract String[] getProjection();
protected String getListOrder() {
return null;
}
@Override
public void migrate(SQLiteDatabase db, int toVersion) {
DataBaseManager.dropTable(db, getTableName());
}
/**
* Query table.
*
* @return Result set with defined projection and in defined order.
*/
public Cursor list() {
SQLiteDatabase db = DataBaseManager.getInstance().getWritableDatabase();
return db.query(getTableName(), getProjection(), null, null, null,
null, getListOrder());
}
public Cursor list(String select,String[]selectionArgs) {
SQLiteDatabase db = DataBaseManager.getInstance().getWritableDatabase();
return db.query(getTableName(), getProjection(), select, selectionArgs, null,
null, getListOrder());
}
@Override
public void clear() {
SQLiteDatabase db = DataBaseManager.getInstance().getWritableDatabase();
db.delete(getTableName(), null, null);
}
protected boolean hasData(Cursor c){
if(c!=null && c.getCount()>0){
return true;
}
return false;
}
protected void closeCurosr(Cursor c) {
if (c != null && !c.isClosed()) {
c.close();
c = null;
}
}
}
/**
* @description:
* @author：zzq
* @time: 2016-7-28 上午12:03:44
*/
public class DataBaseManager extends SQLiteOpenHelper {
private int tag = 1;
private static String DATABASE_NAME = "sqlite.db";
private static final int DATABASE_VERSION = 2;
private static final SQLiteException DOWNGRAD_EXCEPTION = new SQLiteException(
"Database file was deleted");
private final List<DatabaseTable> registeredTables;
private static DataBaseManager instance;
public static DataBaseManager getInstance() {
if (instance == null) {
instance = new DataBaseManager();
}
return instance;
}
public DataBaseManager() {
super(MyApplication.getInstance(), DATABASE_NAME, null,
DATABASE_VERSION);
registeredTables = new LinkedList<DatabaseTable>();
}
public static final void reInit(String name) {
if (instance != null) {
instance.close();
}
DATABASE_NAME = name;
instance = new DataBaseManager();
}
public void addTable(DatabaseTable table) {
registeredTables.add(table);
}
@Override
public void onCreate(SQLiteDatabase db) {
for (DatabaseTable table : registeredTables) {
table.create(db);// 重新创建表
}
// registeredTables.get(registeredTables.size() - 1).create(db);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.i("onUpgrade", "-oldVersion--" + oldVersion + "-newVersion---"
+ newVersion);
for (int i = 1; i <= 2; i++) {
switch (i) {
case 2:
Log.i("onUpgrade", "--大小-");
registeredTables.get(registeredTables.size() - 1).create(db);
break;
case 4:
tag = 2;
onCreate(db);
break;
default:
break;
}
}
// if (newVersion > oldVersion) {
// for (DatabaseTable table : registeredTables) {
// table.migrate(db, oldVersion);//删除表
// }
// onCreate(db);
// MyApplication.getInstance();
//
}
public static void execSQL(SQLiteDatabase db, String sql) {
db.execSQL(sql);
}
public static void dropTable(SQLiteDatabase db, String table) {
execSQL(db, "DROP TABLE IF EXISTS " + table + ";");
}
public static void renameTable(SQLiteDatabase db, String table,
String newTable) {
execSQL(db, "ALTER TABLE " + table + " RENAME TO " + newTable + ";");
}
public void onClear() {
for (DatabaseTable table : registeredTables)
table.clear();
}
public void onLoad() {
try {
getWritableDatabase(); // Force onCreate or onUpgrade
} catch (SQLiteException e) {
if (e == DOWNGRAD_EXCEPTION) {
// Downgrade occured
} else {
throw e;
}
}
}
}
public interface DatabaseTable {
/**
* 创建表
*
* @param db
*/
void create(SQLiteDatabase db);
/**
* Called on database migration.
* @param db
* @param toVersion
*/
void migrate(SQLiteDatabase db, int toVersion);
/**
* Called on clear database request.
*/
void clear();
}启动应用的时候，我们需要在application中做如下操作：
/**
* @description:
* @author：zzq
* @time: 2016-7-28 上午11:03:44
*/
public class MyApplication extends Application {
public static MyApplication instance;
@Override
public void onCreate() {
super.onCreate();
instance = this;
DataBaseManager.getInstance()
.addTable(LocalUserInfoTable.getInstance());
DataBaseManager.getInstance().addTable(LocalStudentInfoTable.getInstance());
DataBaseManager.getInstance().onLoad();
}
public static MyApplication getInstance() {
return instance;
}
}源码地址：点我查看源码
更多安卓相关信息，请扫码关注微信公众号：lifeAndroid
手机扫一扫
移动阅读更方便
你可能感兴趣的文章