Android数据库开发简单封装回顾

     最近项目的部分要用到做数据库方面的开发，虽然功能要求很简单，但是由于要在系统内编译，所以要求最好采用自身的数据库操作。由于用惯了第三方的框架。这里做个记录，算是捡起来以前刚学的数据库那部分吧。

      为了验证就先写了一个数据库的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弹出结果。