最近项目的部分要用到做数据库方面的开发,虽然功能要求很简单,但是由于要在系统内编译,所以要求最好采用自身的数据库操作。由于用惯了第三方的框架。这里做个记录,算是捡起来以前刚学的数据库那部分吧。
为了验证就先写了一个数据库的demo。
需求1:基本的增、删、查。更新 这里的话先放着 2:支持大批量的存储
第一步继承SQLiteOpenHelper类BtDBHelper ,并在其中重写一些方法,这个很重要,数据库的初始化,表的创建和删除,以及相对应的数据库的更新操作都在这里完成。 需要注意的是这里主键是自动生成的interger primary key autoincrement ,同时有一个字段是用unique修饰的唯一索引。
在这里要定义好自己的SQL拼接字段。这里为了测试方便多了两个方法,一个创建表,一个删除表。
上代码:
public class BtDBHelper extends SQLiteOpenHelper {
private static final String TAG = Logutil.makeTagLog(BtDBHelper.class);
public BtDBHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
//数据库建表语句
private static final String CREATE_SEARCH_CONTACT_TABLE = "create table "
+ BtDbConfiguration.TABLE_CUSTOMER + "("
+ BtDbConfiguration.ContactsEntityfig.Contacts_ID
+ " integer primary key autoincrement ,"
+ BtDbConfiguration.ContactsEntityfig.FIRSTNAME + " varchar(20),"
+ BtDbConfiguration.ContactsEntityfig.MIDDLENAME + " varchar(20) ,"
+ BtDbConfiguration.ContactsEntityfig.LASTNAME + " varchar(20) ,"
+ BtDbConfiguration.ContactsEntityfig.FULLNAME + " varchar(20) ,"
+ BtDbConfiguration.ContactsEntityfig.NUMBER + " varchar(20) ,"
+ BtDbConfiguration.ContactsEntityfig.ORDER + " integer ,"
+ BtDbConfiguration.ContactsEntityfig.UNINUNAME + " varchar(20) unique"
+ ")";
@Override
public void onCreate(SQLiteDatabase db) {
Logutil.i(TAG, "=======onCreate()");
db.execSQL(CREATE_SEARCH_CONTACT_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
Logutil.i(TAG, "=======onUpgrade()");
db.execSQL("DROP TABLE IF EXISTS " + BtDbConfiguration.TABLE_CUSTOMER); //删除表
}
public void dropDbtable(SQLiteDatabase db,String table){
db.execSQL("DROP TABLE IF EXISTS " + table); //删除表
}
public void createDbtable(SQLiteDatabase db,String table){
if (table.equals(BtDbConfiguration.TABLE_CUSTOMER)){
db.execSQL(CREATE_SEARCH_CONTACT_TABLE);//创建表
}
}
}
第二部分则是数据库常量辅助的类BtDbConfiguration ,这里面定义一些常量,比如我们需要用到的数据库名,表名,版本号,以及表字段的常量。需要其他常量可以再次定制。
public class BtDbConfiguration {
public static final int DB_VERSION = 2; // 版本号
public static final String DB_NAME = "autoblue.db";//数据库名
// public static final String TABLE_NAME = "AutoBluetooth";
public static final String TABLE_CUSTOMER = "contactsentity"; //表名
/**
* contactsEntity数据表的字段
*/
public static class ContactsEntityfig {
//contactsEntity
public static final String Contacts_ID="id";
public static final String FIRSTNAME="firstName";
public static final String MIDDLENAME="middleName";
public static final String LASTNAME="lastName";
public static final String FULLNAME="fullName";
public static final String NUMBER="number";
public static final String ORDER="btorder";
public static final String UNINUNAME="uninuname";
}
}
第三部分则是定义一个需要用到的数据类ContactsEntity 。这里需要注意的是,我们的id主键都是设置的自增长。同时也要设置一个字段作为唯一标识,至于为什么就是因为我们在给一个对象存储的时候,如果都是一样的,那么就新添加一条记录,如果是有唯一标识则认为是存在的就会去更新一个。
节约代码这里get,set就不写了,自动生成的。
public class ContactsEntity {
/**
* 数据库表ID
*/
private int id ;
private String firstName;
private String middleName;
private String lastName;
private String fullName;
private String number;
private int btorder;
private String uninuname; //唯一索引
}
第四部分就是实现一个数据库的操作类,这里为了方便代码结构分析就写了一个方法接口。
public interface BtManagerInterface<T> {
long save(T obj); //保存数据
long saveAll(Collection<T> collection); //保存所有数据
List<T> queryAll(T table); //根据类名(表名)查找所有的数据
List<T> queryAll(String order); //通过查找人名的方式查找所有的数据
T queryById(Class<T> table, Object id);//通过id查找对应的数据
int delete(Class table); //清空对应表名中的所有数据
}
第五部分就是实现这个操作接口的类,同时暴露出给外部操作。这里有初始化init以及对应的数据库的操作。其中save实现采用了replace操作,而不是insert.这是因为这个操作可以是对象存在就是更新,而不存在就是新插入。这里我们由于整个项目都需要用到所以采用单例的形式提供。实现形式采用了静态内部类实现。
同时这里由于查询的都是Cursor中,我们这里封装一个方法parseContact,把查询的数据封装成对象。
这样实现操作类之后,基本上就可以测试了。
package com.example.administrator.db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.administrator.aidl.ContactsEntity;
import com.example.administrator.util.Logutil;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
/**
* Created by Yongzhou Tang on 2019/2/22 0022 11:03
* E-Mail Address:00000000@qq.com
*/
public class BtDbContactManager implements BtManagerInterface<ContactsEntity> {
private static final String TAG = Logutil.makeTagLog(BtDbContactManager.class);
private static Context mCtx;
public BtDBHelper mDBHelper;
public SQLiteDatabase mWritableDatabase;
private String tableName = BtDbConfiguration.TABLE_CUSTOMER;
// 列定义
private final String[] ORDER_COLUMNS = new String[]{BtDbConfiguration.ContactsEntityfig.Contacts_ID,
BtDbConfiguration.ContactsEntityfig.FIRSTNAME,
BtDbConfiguration.ContactsEntityfig.MIDDLENAME,
BtDbConfiguration.ContactsEntityfig.LASTNAME,
BtDbConfiguration.ContactsEntityfig.FULLNAME,
BtDbConfiguration.ContactsEntityfig.NUMBER,
BtDbConfiguration.ContactsEntityfig.ORDER,
BtDbConfiguration.ContactsEntityfig.UNINUNAME
};
private BtDbContactManager() {
}
public void init(Context context){
this.mCtx = context;
this.mDBHelper = new BtDBHelper(mCtx, BtDbConfiguration.DB_NAME, null, BtDbConfiguration.DB_VERSION);
this.mWritableDatabase = mDBHelper.getWritableDatabase();
}
//判断表是否存在
private boolean IsTableExist( ) {
boolean isTableExist = false;
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = mWritableDatabase;
String sql = "select count(*) as c from Sqlite_master where type ='table' and name ='"+tableName.trim()+"' ";
cursor = db.rawQuery(sql, null);
if(cursor.moveToNext()){
int count = cursor.getInt(0);
if(count>0){
isTableExist = true;
}
}
} catch (Exception e) {
}
return isTableExist;
}
private static class Holder {
private static BtDbContactManager INSTANCE = new BtDbContactManager();
}
public static BtDbContactManager getInstance() { // 静态内部类实现单例
return Holder.INSTANCE;
}
private SQLiteDatabase getDB() {
if (null == mWritableDatabase) {
this.mWritableDatabase = mDBHelper.getWritableDatabase();
}
return this.mWritableDatabase;
}
@Override
public long save(ContactsEntity contactenty) {
long result = -1;
if (mWritableDatabase != null){
if (!IsTableExist()){
mDBHelper.createDbtable(mWritableDatabase,tableName);
}
}
if (null == contactenty) {
Logutil.e(TAG, "contactenty is Empty !!!");
return -1;
}
try {
ContentValues values = new ContentValues();
values.put(BtDbConfiguration.ContactsEntityfig.FIRSTNAME, contactenty.getFirstName());
values.put(BtDbConfiguration.ContactsEntityfig.MIDDLENAME, contactenty.getMiddleName());
values.put(BtDbConfiguration.ContactsEntityfig.LASTNAME, contactenty.getLastName());
values.put(BtDbConfiguration.ContactsEntityfig.FULLNAME, contactenty.getFullName());
values.put(BtDbConfiguration.ContactsEntityfig.NUMBER, contactenty.getNumber());
values.put(BtDbConfiguration.ContactsEntityfig.ORDER, contactenty.getBtorder());
values.put(BtDbConfiguration.ContactsEntityfig.UNINUNAME, contactenty.getUninuname());
result = getDB().replace(BtDbConfiguration.TABLE_CUSTOMER, null, values); //使用replace 实现没有就添加有则更新
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
@Override
public long saveAll(Collection<ContactsEntity> collection) {
synchronized (mWritableDatabase) {
long result = -1;
if (null == collection || collection.isEmpty()) {
Logutil.e(TAG, "contactentys is Empty !!!");
return result;
}
try {
getDB().beginTransaction();
for (ContactsEntity contactenty : collection) {
ContentValues values = new ContentValues();
values.put(BtDbConfiguration.ContactsEntityfig.FIRSTNAME, contactenty.getFirstName());
values.put(BtDbConfiguration.ContactsEntityfig.MIDDLENAME, contactenty.getMiddleName());
values.put(BtDbConfiguration.ContactsEntityfig.LASTNAME, contactenty.getLastName());
values.put(BtDbConfiguration.ContactsEntityfig.FULLNAME, contactenty.getFullName());
values.put(BtDbConfiguration.ContactsEntityfig.NUMBER, contactenty.getNumber());
values.put(BtDbConfiguration.ContactsEntityfig.ORDER, contactenty.getBtorder());
values.put(BtDbConfiguration.ContactsEntityfig.UNINUNAME, contactenty.getUninuname());
result = getDB().insert(BtDbConfiguration.TABLE_CUSTOMER, null, values);
}
getDB().setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally {
getDB().endTransaction();
}
return result;
}
}
@Override
public List<ContactsEntity> queryAll(ContactsEntity entity) {
Cursor cursor = null;
try {
List<ContactsEntity> list = new ArrayList<>();
cursor = getDB().query(tableName, null, null, null, null, null, null);
if(cursor.getCount() > 0)
{
while (cursor.moveToNext()) {
list.add(parseContact(cursor));
}
}
return list;
} catch (Exception e) {
Logutil.e(TAG, "" + e);
} finally {
if (cursor != null) {
cursor.close();
}
}
return null;
}
@Override
public List<ContactsEntity> queryAll( String fullname) { //查询单个人名
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = getDB();
//1.使用这种query方法%号前不能加' ;
// Cursor c_test = mDatabase.query(tab_name, new String[]{tab_field02}, tab_field02+" LIKE ? ",
// new String[] { "%" + str[0] + "%" }, null, null, null);
//2.使用这种query方法%号前必须加' ; 注意空格
// Cursor c_test=mDatabase.query(tab_name, new String[]{tab_field02},tab_field02+" like '%" + str[0] + "%'", null, null, null, null);
//多条件查询 BtDbConfiguration.ContactsEntityfig.FULLNAME + " LIKE" + " '%" + fullname + "%' "
// + " OR " + BtDbConfiguration.ContactsEntityfig.NUMBER+ " LIKE" + " '%" + number + "%'";/
cursor = db.query(tableName,
ORDER_COLUMNS,
BtDbConfiguration.ContactsEntityfig.FULLNAME + " LIKE ? ",
new String[]{"%" + fullname + "%"},
null, null, null);
if (cursor.getCount() > 0) {
List<ContactsEntity> orderList = new ArrayList<ContactsEntity>(cursor.getCount());
while (cursor.moveToNext()) {
ContactsEntity entity = parseContact(cursor);
orderList.add(entity);
}
return orderList;
}
} catch (Exception e) {
Logutil.e(TAG, "" + e);
} finally {
if (cursor != null) {
cursor.close();
}
}
return null;
}
@Override
public ContactsEntity queryById(Class<ContactsEntity> table, Object id) {
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = getDB();
//1.使用这种query方法%号前不能加' ;
// Cursor c_test = mDatabase.query(tab_name, new String[]{tab_field02}, tab_field02+" LIKE ? ",
// new String[] { "%" + str[0] + "%" }, null, null, null);
//2.使用这种query方法%号前必须加' ; 注意空格
// Cursor c_test=mDatabase.query(tab_name, new String[]{tab_field02},tab_field02+" like '%" + str[0] + "%'", null, null, null, null);
cursor = db.query(tableName,
ORDER_COLUMNS,
BtDbConfiguration.ContactsEntityfig.Contacts_ID + " LIKE ? ",
new String[]{"%" + id + "%"},
null, null, null);
if (cursor.getCount() > 0) {
if (cursor.moveToFirst()) {
return parseContact(cursor);
}
}
} catch (Exception e) {
Logutil.e(TAG, "" + e);
} finally {
if (cursor != null) {
cursor.close();
}
}
return null;
}
删除 id = 1的数据
// sqliteDatabase.delete("user", "id=?", new String[]{"1"});
// // 参数1:表名(String)
// // 参数2:WHERE表达式(String),需删除数据的行; 若该参数为 null, 就会删除所有行;?号是占位符
// // 参数3:WHERE选择语句的参数(String[]), 逐个替换 WHERE表达式中 的“?”占位符;
// db.delete(tableName, fieldName + "=?", new String[]{value});
// // 注:也可采用SQL语句修改
// String sql = "delete from user where id="1";
// db.execSQL(sql);
@Override
public int delete(Class table) {
SQLiteDatabase db = null;
int result = 0;
try {
db = getDB();
result = db.delete(tableName, "btorder=?", new String[]{"1"});
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
// /**
// * 操作:修改数据 = update()
// */
// // a. 创建一个ContentValues对象
// ContentValues values = new ContentValues();
// values.put("name", "zhangsan");
//
// // b. 调用update方法修改数据库:将id=1 修改成 name = zhangsan
// sqliteDatabase.update("user", values, "id=?", new String[] { "1" });
// // 参数1:表名(String)
// // 参数2:需修改的ContentValues对象
// // 参数3:WHERE表达式(String),需数据更新的行; 若该参数为 null, 就会修改所有行;?号是占位符
// // 参数4:WHERE选择语句的参数(String[]), 逐个替换 WHERE表达式中 的“?”占位符;
//
// // 注:调用完upgrate()后,则会回调 数据库子类的onUpgrade()
//
// // 注:也可采用SQL语句修改
// String sql = "update [user] set name = 'zhangsan' where id="1";
// db.execSQL(sql);
/**
* 将查找到的数据转换成ContactsEntity类
*/
private ContactsEntity parseContact(Cursor cursor) {
ContactsEntity entity = new ContactsEntity();
entity.setId(cursor.getInt(cursor.getColumnIndex(BtDbConfiguration.ContactsEntityfig.Contacts_ID)));
entity.setFirstName(cursor.getString(cursor.getColumnIndex(BtDbConfiguration.ContactsEntityfig.FIRSTNAME)));
entity.setMiddleName(cursor.getString(cursor.getColumnIndex(BtDbConfiguration.ContactsEntityfig.MIDDLENAME)));
entity.setLastName(cursor.getString(cursor.getColumnIndex(BtDbConfiguration.ContactsEntityfig.LASTNAME)));
entity.setFullName(cursor.getString(cursor.getColumnIndex(BtDbConfiguration.ContactsEntityfig.FULLNAME)));
entity.setNumber(cursor.getString(cursor.getColumnIndex(BtDbConfiguration.ContactsEntityfig.NUMBER)));
entity.setBtorder(cursor.getInt(cursor.getColumnIndex(BtDbConfiguration.ContactsEntityfig.ORDER)));
entity.setUninuname(cursor.getString(cursor.getColumnIndex(BtDbConfiguration.ContactsEntityfig.UNINUNAME)));
return entity;
}
}
至于测试的actity.很简单就不写了,这里就一个页面,每个操作一个butoon.然后toast弹出结果。