GreenDAO数据库的使用 - 多表关联
GreenDAO的使用:
1.在project的build.gradle里边添加:
buildscript { repositories { maven { url 'https://maven.aliyun.com/repository/gradle-plugin' } maven { url 'https://jitpack.io' } // greenDao数据库 mavenCentral() google() jcenter() } dependencies { //Gradle编译版本 classpath "com.android.tools.build:gradle:4.1.2" // greenDao数据库 classpath 'org.greenrobot:greendao-gradle-plugin:3.3.0' }}allprojects { repositories { // 阿里云云效仓库,提升编译速度 maven { url 'https://maven.aliyun.com/repository/public' } maven { url 'https://maven.aliyun.com/repository/jcenter' } maven { url 'https://maven.aliyun.com/repository/central' } maven { url 'https://maven.aliyun.com/repository/google' } maven { url 'https://jitpack.io' } jcenter() google() }}task clean(type: Delete) { delete rootProject.buildDir}
在app模块的build.gradle里的android{}节点里添加:
greendao { schemaVersion 1 // 数据库版本号 daoPackage 'com.my.dao.greendao' // 设置DaoMaster、DaoSession、Dao包名,也就是要放置这些类的包的全路径。 targetGenDir 'src/main/java' // 设置 DaoMaster、DaoSession、Dao 目录 //generateTests false //设置为true以自动生成单元测试。 //targetGenDirTests 'src/main/java' //应存储生成的单元测试的基本目录。默认为 src / androidTest / java。 }
dependencies{}节点里边添加
// greenDao数据库 implementation 'org.greenrobot:greendao:3.3.0'
3.创建实体对象类
@Entitypublic class TestIdCard { @Id private Long cardId; private String cardNo;}
@Entitypublic class TestPerson { @Id(autoincrement = true) private Long personId; private String personName; @ToOne(joinProperty = "personId") private TestIdCard testIdCard;}
这个类用不着为了看他的注解方法 忽略这个
//@Entity//public class Student {// //@Id:主键,通过这个注解标记的字段必须是Long类型的,这个字段在数据库中表示它就是主键,并且它默认就是自增的// @Id(autoincrement = true)// private long id;//// //姓名// @NotNull // @NotNull 设置数据库表当前列不能为空// @Unique //唯一// private String name;//// //年龄// //@Property:设置一个非默认关系映射所对应的列名,默认是使用字段名,例如:@Property(nameInDb = "name")// @Property(nameInDb = "userage")// private int age;//}
4.点击Android Studio窗口上的Build , Make Project会自动生成数据库相应的表和实体类中的方法,注意实体类都必须用@Entity注解修饰,主键用@Id
此处只记录下GreenDAO的多表关联,其他的介绍可自行前往了解
必要的一步,在Application中初始化数据库:
import android.app.Application;import android.database.sqlite.SQLiteDatabase;import com.my.dao.greendao.DaoMaster;import com.my.dao.greendao.DaoSession;/** * Author : i小灰 * time : 2021/3/11 14:52 * desc : 程序入口 */public class App extends Application { //数据库名称 private static final String DB_NAME = "hui_dao.db"; private static DaoSession mDaoSession; //单例模式-懒汉式 private static App app; public App() { App.app = this; } public static App getInstance() { if (app == null) { //双重校验锁 synchronized (App.class) { if (app == null) { app = new App(); } } } return app; } @Override public void onCreate() { super.onCreate(); //初始化GreenDao数据库 initGreenDao(); } private void initGreenDao() { //生成数据库文件,名为 students-db DaoMaster.DevOpenHelper helper = new DaoMaster.DevOpenHelper(this, DB_NAME, null); //获取可写数据库 SQLiteDatabase db = helper.getWritableDatabase(); //建立特定模式下的所有的 DAO 对象和数据 db 对象的映射 DaoMaster daoMaster = new DaoMaster(db); //管理特定模式下的所有 DAO 对象,并提供一些通用的 CRUD 持久化方法 mDaoSession = daoMaster.newSession(); } //供外部调用操作数据库 public static DaoSession getmDaoSession() { return mDaoSession; }}
这里补充插入一点:关联表查询,在不设置的前提下,在主动端关联查询,可以查询到被动端的数据。而从被动端查询是不知道自己属于谁和谁有关联的。
一对一 例如:一个人对应一张身份证,关联时会将这个人的ID设置给这张身份证,那么这个人是主动端,身份证是被动端。这个人是知道哪张身份证属于自己,而被动端的身份证是不知道自己属于哪个人。
一对多 例如:一个老师有多个学生,如果我们不给学生设置他对应哪个老师的情况下。外键是设置在学生这个多 的一方(被动的一方),从老师端查询,可以查询到他拥有的学生数据,而从学生端查询是无法知道他对应的老师的信息的,只有给学生设置了对应的老师的信息,那么这个学生才知道自己数据哪个老师。
结论:在不给拥有外键这个多的一方设置的情况下,从拥有外键的一方关联查询只能查询到自己的信息,而从多的一方是可以查询到拥有外键这方的信息。(主动方将自己的主键发给被动方持有,双方由此产生关联)
一对一
一对一实际上是表A中一项元素的主键等于表B中一项元素的主键,那么这两个元素有一对一的关联。一对一要用到@ToOne注解,例如一个人只有一张身份证
用到的实体类就是上面的两个
测试:
private void oneToOne() { //存储数据 TestPerson testPerson = new TestPerson(); testPerson.setPersonName("i小灰"); App.getmDaoSession().insert(testPerson); TestIdCard testIdCard = new TestIdCard(); testIdCard.setCardId(testPerson.getPersonId()); testIdCard.setCardNo("12334555"); App.getmDaoSession().insert(testIdCard); //查询数据 List<TestPerson> testPeople = App.getmDaoSession().queryAll(TestPerson.class); for (TestPerson person : testPeople) { LogUtils.d("person :" + person.toString()); TestIdCard testIdCard1 = person.getTestIdCard(); LogUtils.d("card: " + testIdCard1.toString()); } }
一对多
一对多实际上是在多的一方的表中创建一个外键,如果多的一方表中的某个元素的外键等于一的一方表中的主键,那么多的一方表中的这个元素就跟一的一方表中的这个元素产生了一对多的关联。一对多要用到@ToMany注解,例如一个用户可以拥有多张信用卡
新建实体类:
TestUser
@Entitypublic class TestUser { @Id(autoincrement = true) private Long userId; private String userName; private String age; //一对多 @ToMany(referencedJoinProperty = "credit_user_id") private List<TestCreditCard> creditCards;}
TestCreditCard
@Entitypublic class TestCreditCard { //主键必须是唯一的 @Id private Long cId; private String creditCardNo; private String creditCardName; //外键必须是唯一的 @Unique private Long credit_user_id;}
同样记得build Make Project
测试
/** * 一对多 * user and creditCard */ private void oneToMany() { //存储数据 TestUser testUser = new TestUser(); testUser.setUserName("狗哥"); testUser.setAge("23"); App.getmDaoSession().insert(testUser); for (int i = 3; i < 6; i++) { TestCreditCard testCreditCard = new TestCreditCard(); testCreditCard.setCId((i + 1L)); testCreditCard.setCredit_user_id((i + 1L)); testCreditCard.setCreditCardName("招商信用卡_"+i); testCreditCard.setCreditCardNo("12345678"); App.getmDaoSession().insert(testCreditCard); } //查询数据 List<TestUser> testUsers = App.getmDaoSession().queryAll(TestUser.class); for (TestUser user : testUsers) { LogUtils.d("用户:" + user.toString()); List<TestCreditCard> creditCards = user.getCreditCards(); for (TestCreditCard creditCard : creditCards) { LogUtils.d("信用卡:" + creditCard.toString()); } } }
多对多
多对多创建中间表,在中间表中分别有两个外键指向一的一方。如果中间表中的某个元素的外键等于一的一方中某个元素的主键,那么这两项元素就产生了一对多的关联。中间表分别指向两边产生一对多的关联,那么两边就产生了多对多的关联。多对多的关系其实也就类似于MySQL中的多对多关系,通过建立中间表来分解为一对多的关系。我们以student和teacher为例,下面创建两个实体类
@Entitypublic class Student { @Id(autoincrement = true) Long studentId; String studentName; @ToMany @JoinEntity(entity = StudentAndTeacherBean.class, sourceProperty = "sId", targetProperty = "tId") List<Teacher> teacherList;...setter and getter}
@Entitypublic class Teacher { @Id(autoincrement = true) Long teacherId; String teacherName; @ToMany @JoinEntity(entity = StudentAndTeacherBean.class, sourceProperty = "tId", targetProperty = "sId") List<Student> studentList;...setter and getter}
build Make Project后,我们来进行测试。分为两个方法:insertDataToMany是创建并保存数据;
queryDataToMany是分别从学生的角度查询关联的老师和从老师的角度查询关联的学生
测试
/** * 多对多 */ public void insertDataToMany(View view) { for (int i = 0; i < 2; i++) { if (i == 0) { Teacher teacher = new Teacher(); teacher.setTeacherName("李老师"); long insert = App.getmDaoSession().insert(teacher); Log.i(TAG, "老师存储的结果:" + insert); for (int i1 = 0; i1 < 2; i1++) { if (i1 == 0) { Student student = new Student(); student.setStudentName("大同学1号"); long insertStudent = App.getmDaoSession().insert(student); Log.i(TAG, "学生存储的结果:" + insertStudent); StudentAndTeacherBean studentAndTeacherBean = new StudentAndTeacherBean(); studentAndTeacherBean.setTId(teacher.getTeacherId()); studentAndTeacherBean.setSId(student.getStudentId()); long insertStudentTeacher = App.getmDaoSession().insert(studentAndTeacherBean); Log.i(TAG, "存储中间表的结果:" + insertStudentTeacher); }else if (i1==1){} if (i1 == 0) { Student student = new Student(); student.setStudentName("大同学2号"); long insertStudent = App.getmDaoSession().insert(student); Log.i(TAG, "学生存储的结果:" + insertStudent); StudentAndTeacherBean studentAndTeacherBean = new StudentAndTeacherBean(); studentAndTeacherBean.setTId(teacher.getTeacherId()); studentAndTeacherBean.setSId(student.getStudentId()); long insertStudentTeacher = App.getmDaoSession().insert(studentAndTeacherBean); Log.i(TAG, "存储中间表的结果:" + insertStudentTeacher); } } } else if (i == 1) { Teacher teacher = new Teacher(); teacher.setTeacherName("刘老师"); long insert = App.getmDaoSession().insert(teacher); Log.i(TAG, "老师存储的结果:" + insert); for (int i1 = 0; i1 < 2; i1++) { if (i1 == 0) { Student student = new Student(); student.setStudentName("小同学1号"); long insertStudent = App.getmDaoSession().insert(student); Log.i(TAG, "学生存储的结果:" + insertStudent); StudentAndTeacherBean studentAndTeacherBean = new StudentAndTeacherBean(); studentAndTeacherBean.setTId(teacher.getTeacherId()); studentAndTeacherBean.setSId(student.getStudentId()); long insertStudentTeacher = App.getmDaoSession().insert(studentAndTeacherBean); Log.i(TAG, "存储中间表的结果:" + insertStudentTeacher); }else if (i1==1){ Student student = new Student(); student.setStudentName("小同学2号"); long insertStudent = App.getmDaoSession().insert(student); Log.i(TAG, "学生存储的结果:" + insertStudent); StudentAndTeacherBean studentAndTeacherBean = new StudentAndTeacherBean(); studentAndTeacherBean.setTId(teacher.getTeacherId()); studentAndTeacherBean.setSId(student.getStudentId()); long insertStudentTeacher = App.getmDaoSession().insert(studentAndTeacherBean); Log.i(TAG, "存储中间表的结果:" + insertStudentTeacher); } } } } }/** * 查询数据 */ public void queryDataToMany(View view) { List<Teacher> teachers = App.getmDaoSession().loadAll(Teacher.class); Log.i(TAG, "所有老师的数量:" + teachers.size()); for (Teacher teacher : teachers) { Log.i(TAG, "老师:" + teacher.toString()); List<Student> studentList = teacher.getStudentList(); Log.i(TAG, "学生的数量:" + studentList.size()); for (Student student : studentList) { Log.i(TAG,"学生的信息:"+student.toString()); } } List<Student> students = App.getmDaoSession().loadAll(Student.class); Log.i(TAG, "所有学生的数量:" + students.size()); for (Student student : students) { Log.i(TAG, "学生:" + student.toString()); List<Teacher> teacherList = student.getTeacherList(); Log.i(TAG, "老师的数量:" + teacherList.size()); for (Teacher teacher : teacherList) { Log.i(TAG,"老师的信息:"+teacher.toString()); } } }
数据库的升级更新:文章参考https://www.jianshu.com/p/53083f782ea2,在此感谢分享!
我们自定义一个类继承自OpenHelper
public class MyDaoMaster extends DaoMaster.OpenHelper { private static final String TAG = "MyDaoMaster"; public MyDaoMaster(Context context, String name) { super(context, name); } public MyDaoMaster(Context context, String name, SQLiteDatabase.CursorFactory factory) { super(context, name, factory); } @Override public void onUpgrade(Database db, int oldVersion, int newVersion) { super.onUpgrade(db, oldVersion, newVersion); MigrationHelper.migrate(db, new MigrationHelper.ReCreateAllTableListener() { @Override public void onCreateAllTables(Database db, boolean ifNotExists) { DaoMaster.createAllTables(db, ifNotExists); } @Override public void onDropAllTables(Database db, boolean ifExists) { DaoMaster.dropAllTables(db, ifExists); } },YouThingDao.class); Log.e(TAG, "onUpgrade: " + oldVersion + " newVersion = " + newVersion); }}
在MyMasterDao中YouThingDao.class是你项目中的Dao数据类,这里可以传一个Dao类的数组
/** * 数据库升级的思路: * 1.先创建当前已存在的所有表,对应临时的表 * 2.然后删除当前已存在的所有的表 * 3.创建数据库升级后的所有新的表 * */public final class MigrationHelper { public static boolean DEBUG = false; private static String TAG = "MigrationHelper"; private static final String SQLITE_MASTER = "sqlite_master"; private static final String SQLITE_TEMP_MASTER = "sqlite_temp_master"; /** * 重新创建所有表格监听的弱引用 */ private static WeakReference<ReCreateAllTableListener> weakListener; /** * 重新创建所有表格的监听 */ public interface ReCreateAllTableListener{ void onCreateAllTables(Database db, boolean ifNotExists); void onDropAllTables(Database db, boolean ifExists); } /** * 迁移 * @param db * @param daoClasses */ public static void migrate(SQLiteDatabase db, Class<? extends AbstractDao<?, ?>>... daoClasses) { //旧的数据库版本 printLog("【The Old Database Version】" + db.getVersion()); Database database = new StandardDatabase(db); migrate(database, daoClasses); } public static void migrate(SQLiteDatabase db, ReCreateAllTableListener listener, Class<? extends AbstractDao<?, ?>>... daoClasses) { weakListener = new WeakReference<>(listener); migrate(db, daoClasses); } public static void migrate(Database database, ReCreateAllTableListener listener, Class<? extends AbstractDao<?, ?>>... daoClasses) { weakListener = new WeakReference<>(listener); migrate(database, daoClasses); } public static void migrate(Database database, Class<? extends AbstractDao<?, ?>>... daoClasses) { printLog("【Generate temp table】start"); generateTempTables(database, daoClasses);//生成临时的表 printLog("【Generate temp table】complete"); ReCreateAllTableListener listener = null; if (weakListener != null) { listener = weakListener.get(); } if (listener != null) { listener.onDropAllTables(database, true); printLog("【Drop all table by listener】"); listener.onCreateAllTables(database, false); printLog("【Create all table by listener】"); } else { dropAllTables(database, true, daoClasses); createAllTables(database, false, daoClasses); } //回复原来表的数据 printLog("【Restore data】start"); restoreData(database, daoClasses); printLog("【Restore data】complete"); } /** * 声场新的表 */ private static void generateTempTables(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) { for (int i = 0; i < daoClasses.length; i++) { String tempTableName = null; DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]); String tableName = daoConfig.tablename; if (!isTableExists(db, false, tableName)) { printLog("【New Table】" + tableName); continue; } try { tempTableName = daoConfig.tablename.concat("_TEMP"); StringBuilder dropTableStringBuilder = new StringBuilder(); dropTableStringBuilder.append("DROP TABLE IF EXISTS ").append(tempTableName).append(";"); db.execSQL(dropTableStringBuilder.toString()); StringBuilder insertTableStringBuilder = new StringBuilder(); insertTableStringBuilder.append("CREATE TEMPORARY TABLE ").append(tempTableName); insertTableStringBuilder.append(" AS SELECT * FROM ").append(tableName).append(";"); db.execSQL(insertTableStringBuilder.toString()); printLog("【Table】" + tableName +"\n ---Columns-->"+getColumnsStr(daoConfig)); printLog("【Generate temp table】" + tempTableName); } catch (SQLException e) { Log.e(TAG, "【Failed to generate temp table】" + tempTableName, e); } } } /** * * @param db * @param isTemp * @param tableName * @return */ private static boolean isTableExists(Database db, boolean isTemp, String tableName) { if (db == null || TextUtils.isEmpty(tableName)) { return false; } String dbName = isTemp ? SQLITE_TEMP_MASTER : SQLITE_MASTER; String sql = "SELECT COUNT(*) FROM " + dbName + " WHERE type = ? AND name = ?"; Cursor cursor=null; int count = 0; try { cursor = db.rawQuery(sql, new String[]{"table", tableName}); if (cursor == null || !cursor.moveToFirst()) { return false; } count = cursor.getInt(0); } catch (Exception e) { e.printStackTrace(); } finally { if (cursor != null) cursor.close(); } return count > 0; } private static String getColumnsStr(DaoConfig daoConfig) { if (daoConfig == null) { return "no columns"; } StringBuilder builder = new StringBuilder(); for (int i = 0; i < daoConfig.allColumns.length; i++) { builder.append(daoConfig.allColumns[i]); builder.append(","); } if (builder.length() > 0) { builder.deleteCharAt(builder.length() - 1); } return builder.toString(); } private static void dropAllTables(Database db, boolean ifExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) { reflectMethod(db, "dropTable", ifExists, daoClasses); printLog("【Drop all table by reflect】"); } private static void createAllTables(Database db, boolean ifNotExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) { reflectMethod(db, "createTable", ifNotExists, daoClasses); printLog("【Create all table by reflect】"); } /** * dao class already define the sql exec method, so just invoke it */ private static void reflectMethod(Database db, String methodName, boolean isExists, @NonNull Class<? extends AbstractDao<?, ?>>... daoClasses) { if (daoClasses.length < 1) { return; } try { for (Class cls : daoClasses) { Method method = cls.getDeclaredMethod(methodName, Database.class, boolean.class); method.invoke(null, db, isExists); } } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } } /** * 恢复数据 * @param db * @param daoClasses */ private static void restoreData(Database db, Class<? extends AbstractDao<?, ?>>... daoClasses) { for (int i = 0; i < daoClasses.length; i++) { DaoConfig daoConfig = new DaoConfig(db, daoClasses[i]); String tableName = daoConfig.tablename; String tempTableName = daoConfig.tablename.concat("_TEMP"); if (!isTableExists(db, true, tempTableName)) { continue; } try { // get all columns from tempTable, take careful to use the columns list List<TableInfo> newTableInfos = TableInfo.getTableInfo(db, tableName); List<TableInfo> tempTableInfos = TableInfo.getTableInfo(db, tempTableName); ArrayList<String> selectColumns = new ArrayList<>(newTableInfos.size()); ArrayList<String> intoColumns = new ArrayList<>(newTableInfos.size()); for (TableInfo tableInfo : tempTableInfos) { if (newTableInfos.contains(tableInfo)) { String column = '`' + tableInfo.name + '`'; intoColumns.add(column); selectColumns.add(column); } } // NOT NULL columns list for (TableInfo tableInfo : newTableInfos) { if (tableInfo.notnull && !tempTableInfos.contains(tableInfo)) { String column = '`' + tableInfo.name + '`'; intoColumns.add(column); String value; if (tableInfo.dfltValue != null) { value = "'" + tableInfo.dfltValue + "' AS "; } else { value = "'' AS "; } selectColumns.add(value + column); } } if (intoColumns.size() != 0) { StringBuilder insertTableStringBuilder = new StringBuilder(); insertTableStringBuilder.append("REPLACE INTO ").append(tableName).append(" ("); insertTableStringBuilder.append(TextUtils.join(",", intoColumns)); insertTableStringBuilder.append(") SELECT "); insertTableStringBuilder.append(TextUtils.join(",", selectColumns)); insertTableStringBuilder.append(" FROM ").append(tempTableName).append(";"); db.execSQL(insertTableStringBuilder.toString()); printLog("【Restore data】 to " + tableName); } StringBuilder dropTableStringBuilder = new StringBuilder(); dropTableStringBuilder.append("DROP TABLE ").append(tempTableName); db.execSQL(dropTableStringBuilder.toString()); printLog("【Drop temp table】" + tempTableName); } catch (SQLException e) { Log.e(TAG, "【Failed to restore data from temp table 】" + tempTableName, e); } } } private static List<String> getColumns(Database db, String tableName) { List<String> columns = null; Cursor cursor = null; try { cursor = db.rawQuery("SELECT * FROM " + tableName + " limit 0", null); if (null != cursor && cursor.getColumnCount() > 0) { columns = Arrays.asList(cursor.getColumnNames()); } } catch (Exception e) { e.printStackTrace(); } finally { if (cursor != null) cursor.close(); if (null == columns) columns = new ArrayList<>(); } return columns; } private static void printLog(String info){ if(DEBUG){ Log.d(TAG, info); } } private static class TableInfo { int cid; String name; String type; boolean notnull; String dfltValue; boolean pk; @Override public boolean equals(Object o) { return this == o || o != null && getClass() == o.getClass() && name.equals(((TableInfo) o).name); } @Override public String toString() { return "TableInfo{" + "cid=" + cid + ", name='" + name + '\'' + ", type='" + type + '\'' + ", notnull=" + notnull + ", dfltValue='" + dfltValue + '\'' + ", pk=" + pk + '}'; } private static List<TableInfo> getTableInfo(Database db, String tableName) { String sql = "PRAGMA table_info(" + tableName + ")"; printLog(sql); Cursor cursor = db.rawQuery(sql, null); if (cursor == null) return new ArrayList<>(); TableInfo tableInfo; List<TableInfo> tableInfos = new ArrayList<>(); while (cursor.moveToNext()) { tableInfo = new TableInfo(); tableInfo.cid = cursor.getInt(0); tableInfo.name = cursor.getString(1); tableInfo.type = cursor.getString(2); tableInfo.notnull = cursor.getInt(3) == 1; tableInfo.dfltValue = cursor.getString(4); tableInfo.pk = cursor.getInt(5) == 1; tableInfos.add(tableInfo); // printLog(tableName + ":" + tableInfo); } cursor.close(); return tableInfos; } } }
数据库的文件有变动,记得修改build.gradle中的数据库版本号,当新的数据库版本号大于旧的版本号时才会进行数据库的更新升级!
多表关联:一对多和多对多时,把一的一方的外键给多的一方存储;一对一中,把被动一方的外键给主动一方存储
作者:i小灰
链接:https://www.jianshu.com/p/8bef1f7ce1b5