如何将嵌套的JSON数据存储在Room Database中?[房间]

2022-03-23 00:00:00 sqlite android android-room java retrofit

有如下JSON结构。我还想在Retrofit的帮助下解析后将此数据存储在Room中。当使用相同的实体类时,它会给出错误。如果您能分享一个嵌套数据的示例,我将不胜感激。(请共享用Java编写的代码)

此处提供了所有必需的结构:https://github.com/theoyuncu8/roomdb

JSON数据

{
"MyData": [
  {
    "food_id": "1",
    "food_name": "Food 1",
    "food_image": "imageurl",
    "food_kcal": "32",
    "food_url": "url",
    "food_description": "desc",
    "carb_percent": "72",
    "protein_percent": "23",
    "fat_percent": "4",
    "units": [
      {
        "unit": "Unit A",
        "amount": "735.00",
        "calory": "75.757",
        "calcium": "8.580",
        "carbohydrt": "63.363",
        "cholestrl": "63.0",
        "fiber_td": "56.12",
        "iron": "13.0474",
        "lipid_tot": "13.01",
        "potassium": "11.852",
        "protein": "717.1925",
        "sodium": "112.02",
        "vit_a_iu": "110.7692",
        "vit_c": "110.744"
      },
      {
        "unit": "Unit C",
        "amount": "32.00",
        "calory": "23.757",
        "calcium": "53.580",
        "carbohydrt": "39.363",
        "cholestrl": "39.0",
        "fiber_td": "93.12",
        "iron": "93.0474",
        "lipid_tot": "93.01",
        "potassium": "9.852",
        "protein": "72.1925",
        "sodium": "10.0882",
        "vit_a_iu": "80.7692",
        "vit_c": "80.744"
      }
    ]
  },
  {
    "food_id": "2",
    "food_name": "Food 2",
    "food_image": "imageurl",
    "food_kcal": "50",
    "food_url": "url",
    "food_description": "desc",
    "carb_percent": "25",
    "protein_percent": "14",
    "fat_percent": "8",
    "units": [
      {
        "unit": "Unit A",
        "amount": "25.00",
        "calory": "25.757",
        "calcium": "55.580",
        "carbohydrt": "53.363",
        "cholestrl": "53.0",
        "fiber_td": "53.12",
        "iron": "53.0474",
        "lipid_tot": "53.01",
        "potassium": "17.852",
        "protein": "757.1925",
        "sodium": "122.02",
        "vit_a_iu": "10.7692",
        "vit_c": "10.744"
      },
      {
        "unit": "Unit C",
        "amount": "2.00",
        "calory": "2.757",
        "calcium": "5.580",
        "carbohydrt": "3.363",
        "cholestrl": "3.0",
        "fiber_td": "3.12",
        "iron": "3.0474",
        "lipid_tot": "3.01",
        "potassium": "77.852",
        "protein": "77.1925",
        "sodium": "12.02",
        "vit_a_iu": "0.7692",
        "vit_c": "0.744"
      },
      {
        "unit": "Unit G",
        "amount": "1.00",
        "calory": "2.1",
        "calcium": "0.580",
        "carbohydrt": "0.363",
        "cholestrl": "0.0",
        "fiber_td": "0.12",
        "iron": "0.0474",
        "lipid_tot": "0.01",
        "potassium": "5.852",
        "protein": "0.1925",
        "sodium": "1.02",
        "vit_a_iu": "0.7692",
        "vit_c": "0.744"
      }
    ]
  }
]
}


解决方案

不能将列表/数组作为列。

private List<FoodUnitsData> units = null;

这与您上一个问题How can I store data with nested object data in Android?中讨论的问题相同。

所以您需要将数据转换为可处理的列或具有相关的表。

要使用转换后的数据以便可以将其存储在列中,您可以有一个具有列表的类,例如

class FoodUnitsDataListHolder {
    List<FoodUnitsData> unitsList;
}

并将该列设置为(不确定这将如何与Retrofit一起使用):-

private FoodUnitsDataListHolder units = null;

然后,您需要TypeConverters将FoodUnitsDataListHolder转换为可以存储的类型(可能是字符串),或者从可以存储的类型(可能是字符串)进行转换。通常,这将指向对象的JSON字符串表示形式。

例如

@TypeConverter
String fromFoodUnitsDataListHolder(FoodUnitsDataListHolder fudlh): String {
    return Gson().toJson(fudlh)
}
@TypeConverter
FoodUnitsDataListHolder toFoodUnitsDataListHolder(String json) {
    return Gson().fromJson(json,FoodUnitsDataListHolderclass.java)
}

您需要使用具有合适作用域的[@TypeConverters][1]批注(我建议使用@Database级别)。

How can I store data with nested object data in Android?的答案解释了如何拥有相关的表,您只需要使用合适的实体(每个表使用@Entity注释的类)通过空间来实现这一点,而不是使用SQL来创建表以及@Dao注释的类中用于访问数据的合适的方法。

添加备注

用sqlite做这类工作既复杂又不够。所以我必须为房间找一个例子。

调整代码并不那么困难。以下内容将带您一览无余。

首先考虑Foods类。

正如最初建议的那样,您遇到的问题是成员变量private List<FoodUnitsData> units = null;。列不能是列表/数组。

如前所述,有两个选项将列表存储为单个对象,该对象将转换为Room可以管理的类型。这通常是一个JSON字符串。但是,JSON字符串不容易通过SQL操作/访问,并且会额外膨胀。

您可能很想做一些事情,例如查找低/高卡路里(卡路里)值的食物。那么,从数据库的角度来看,试图从JSON字符串确定这一点将是困难和低效的。因此,强烈建议将单位(嵌入/嵌套数据)存储在表中。

记住这一点,那么第一件事就是从Foods中排除该单元,但是为了JSON,允许它存在于Foods类中。这可以通过使用Room的@Ignore批注来实现。

下一步是添加所需的@Entity@PrimaryKey注释。foodId成员变量对于每个Foods对象似乎是唯一的,因此可能是此对象的候选变量。

由于foodId是字符串,并且Room将坚持它不能为空,因此应该另外使用@NonNull批注对其进行批注。

因此食品可以是:-

@Entity(tableName = "food_data") // ADDED to make it usable as a Room table
public class Foods {
    @SerializedName("food_id")
    @Expose
    @PrimaryKey // ADDED as MUST have a primary key
    @NonNull // ADDED Room does not accept NULLABLE PRIMARY KEY
    private String foodId;
    @SerializedName("food_name")
    @Expose
    private String foodName;
    @SerializedName("food_image")
    @Expose
    private String foodImage;
    @SerializedName("food_kcal")
    @Expose
    private String foodKcal;
    @SerializedName("food_url")
    @Expose
    private String foodUrl;
    @SerializedName("food_description")
    @Expose
    private String foodDescription;
    @SerializedName("carb_percent")
    @Expose
    private String carbPercent;
    @SerializedName("protein_percent")
    @Expose
    private String proteinPercent;
    @SerializedName("fat_percent")
    @Expose
    private String fatPercent;
    @SerializedName("units")
    @Expose
    @Ignore // ADDED AS going to be a table
    private List<FoodUnitsData> units = null;

    @NonNull // ADDED (not reqd)
    public String getFoodId() {
        return foodId;
    }


    public void setFoodId(@NonNull /* ADDED @NonNull (not reqd)*/ String foodId) {
        this.foodId = foodId;
    }

    public String getFoodName() {
        return foodName;
    }

    public void setFoodName(String foodName) {
        this.foodName = foodName;
    }

    public String getFoodImage() {
        return foodImage;
    }

    public void setFoodImage(String foodImage) {
        this.foodImage = foodImage;
    }

    public String getFoodKcal() {
        return foodKcal;
    }

    public void setFoodKcal(String foodKcal) {
        this.foodKcal = foodKcal;
    }

    public String getFoodUrl() {
        return foodUrl;
    }

    public void setFoodUrl(String foodUrl) {
        this.foodUrl = foodUrl;
    }

    public String getFoodDescription() {
        return foodDescription;
    }

    public void setFoodDescription(String foodDescription) {
        this.foodDescription = foodDescription;
    }

    public String getCarbPercent() {
        return carbPercent;
    }

    public void setCarbPercent(String carbPercent) {
        this.carbPercent = carbPercent;
    }

    public String getProteinPercent() {
        return proteinPercent;
    }

    public void setProteinPercent(String proteinPercent) {
        this.proteinPercent = proteinPercent;
    }

    public String getFatPercent() {
        return fatPercent;
    }

    public void setFatPercent(String fatPercent) {
        this.fatPercent = fatPercent;
    }

    public List<FoodUnitsData> getUnits() {
        return units;
    }

    public void setUnits(List<FoodUnitsData> units) {
        this.units = units;
    }

}
  • 查看评论
  • Room将@Entity批注类(如果在@Database批注的Entities参数中定义)视为表。这就是我们介绍的Foods类,它既可以从JSON字符串中提取,也可以作为表提取。
    • 请注意,如果仅从表中提取Foods,则不会填充FoodUnitsData列表。

将FoodUnitsData作为表进行第二次

FoodUnitsData类在没有:-

的情况下不会使其自身成为表(@Entity批注)
  • a)主键,
  • b)没有将其关联(映射/引用)到拥有它的食品(其父项)的方式。

这样的类将被创建嵌入FoodUnitsData类,但包含a)和b)的附加成员变量。

由于b)将是所谓的外键,建议添加一个外键约束(规则)来实施引用完整性(这样您就不能让孤立的(无用的)FoodUnitsData使数据库膨胀,甚至可能导致崩溃)。

为进一步简化引用完整性的维护,外键约束将包括在删除或更新父Foods以影响关系(foodId更改)时删除或更新FoodUnitsData的特殊操作。

出于演示目的,该类将命名为FoodUnitsDataEntity。

为便于从FoodUnitsData对象构造FoodUnitsDataEntity,将添加一个具有2个参数的构造函数:-

  1. FoodUnitsData对象和
  2. 与父Foods的关系的foodId。

将自动生成主键(也称为id)(但不使用低效的AUTOINCREMENT(autogenerate = true在Room中的PrimaryKey注释中)。因此,使用@PrimaryKey注释的Long the_column/member_name = null;(因此,SQLite行ID的别名Room将把列定义为整数主键,这是允许自动生成ID的特殊情况)。

映射到foodId的成员变量必须是相同类型(在数据库方面,因此在中您可以逃脱,例如,将int映射到long,只要两者都存储为整数类型)。因此,foodId应该是String foodId;,因为房间希望索引外键(如果没有,则发出警告),则使用@ColumnInfo(index = true)注释。

若要嵌入FoodUnitsData并获取所有成员变量作为列,则使用@Embedded对FoodUnitsData对象进行编码和批注。

应用上述内容FoodUnitsDataEntity可以是:-

/*
    NEW CLASS that:-
        Has a Unique ID (Long most efficient) as the primary Key
        Has a column to reference/map to the parent FoodUnitsData of the food that owns this
        Embeds the FoodUnitsData class
        Enforces referential integrity be defining a Foreign Key constraint (optional)
            If parent is delete then children are deleted (CASCADE)
            If the parent's foodId column is changed then the foodIdMap is updated in the children (CASCADE)
 */
@Entity(
        tableName = "food_units",
        foreignKeys = {
                @ForeignKey(
                        entity = Foods.class, /* The class (annotated with @ Entity) of the owner/parent */
                        parentColumns = {"foodId"}, /* respective column referenced in the parent (Foods) */
                        childColumns = {"foodIdMap"}, /* Column in the table that references the parent */
                        onDelete = CASCADE, /* optional within Foreign key */
                        onUpdate = CASCADE /* optional with foreign key */
                )
        }
)
class FoodUnitsDataEntity {
    @PrimaryKey             //<<<<< a)
    Long foodUnitId = null; //<<<<< a) 
    @ColumnInfo(index = true) //<<<<< b) not essential but room will warn if not indexed
    String foodIdMap; //<<<<< b)
    @Embedded    // Tells Room to include the following object with it's member variables as columns in the table
    FoodUnitsData foodUnitsData;

    FoodUnitsDataEntity(){} // Empty Constructor for Rooms creation of FoodUnitsEntity objects
    // constructor for converting a FoodUnitsData to a FoodUnitsDataEntity 
    FoodUnitsDataEntity(FoodUnitsData fud, String foodId) {
        this.foodUnitsData = fud;
        this.foodIdMap = foodId;
        this.foodUnitId = null;
    }
}

@Database

在此阶段,如果您使用定义的两个实体创建了合适的@Database带注释的类,则可以编译(生成)项目(而不是运行应用程序),而Room将生成底层Java代码,同时还会生成突出显示问题的日志。

所以您可以:-

@Database(entities = {Foods.class, FoodUnitsDataEntity.class /*<<<<<<<<<< ADDED*/}, version = 1)
public abstract class FoodDatabase extends RoomDatabase {
    public abstract DaoAccess daoAccess(); //* do not inlcude this line until the DaoAccess class has been created
}
  • 请注意DAoAccess的注释(因为在将此添加到答案之前已进行了全面测试)。
  • 编译后可以访问生成的Java。这将位于生成的java的FoodDatabase_Impl类中(使用Android Studio Android View查看生成的java)。createAllTables方法是最有可能感兴趣的方法,因为它包含将用于创建表、索引等的SQL。

DAoAccess

由于可能需要使用建议的模式(外键约束)从JSON填充数据库,因此需要a)将父级插入到Foods表中,然后b)插入FoodUnitsData的相应行。将需要具有主体的方法。因此,DaoAccess应该是一个抽象类,而不是一个接口,例如,它可以是:-

@Dao
public /* CHANGED TO abstract class from interface */ abstract class DaoAccess {
    @Query("SELECT * FROM food_data")
    abstract List<Foods> getAll();

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(Foods task);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(FoodUnitsDataEntity foodUnitsDataEntity);

    @Delete
    abstract int delete(Foods task);
    @Delete
    abstract int delete(FoodUnitsDataEntity foodUnitsDataEntity);

    @Update
    abstract int update(Foods task);
    @Update
    abstract int update(FoodUnitsDataEntity foodUnitsDataEntity);

    @Query("")
    @Transaction
    long insertFoodsWithAllTheFoodUnitsDataEntityChildren(Foods foods) {
        long rv = -1;
        long fudInsertCount = 0;
        if (insert(foods) > 0) {
          for(FoodUnitsData fud: foods.getUnits()) {
              if (insert(new FoodUnitsDataEntity(fud,foods.getFoodId())) > 0) {
                  fudInsertCount++;
              }
          }
          if (fudInsertCount != foods.getUnits().size()) {
              rv = -(foods.getUnits().size() - fudInsertCount);
          } else {
              rv = 0;
          }
        }
        return rv;
    }
}
  • 请注意,这些方法返回的不是void,而是long(插入)或int(删除/更新)。

    • 对于INSERTS,LONG将是插入行的行ID(rowid是所有房间表(尽管不是FTS的虚拟表)将具有的隐藏列),或者如果该行没有插入但没有导致错误,则为-1(@INSERT使用INSERT或IGNORE,因此忽略约束冲突,如UNIQUE,但不插入该行)。但是,违反外键将导致失败。

    • 对于更新和删除,int返回的是受影响的行数(已删除或已更新)。

  • 没有正文的方法需要是抽象的,有正文的方法不应该是抽象的。

  • 了解insertFoodsWithAllTheFoodUnitsDataEntityChildren方法如何调用其他方法,以及它如何遵循上面的a)和b)。这是从JSON添加的关键,JSON在Foods对象中构建嵌入式FoodUnitsData对象。

  • 使用了@query(";";),它显然不执行任何操作,以允许使用@Transaction。因此,所有插入都将在内存/临时文件中完成,并且只有在最后才会将更改应用到磁盘。这降低了I/O开销。

重新访问FoodDatabase

添加方法以检索DaoAccess实例,并再次编译并检查日志(警告可以注意或忽略,但至少在使用Room 2.4.1版时不应该有警告)。

向FoodUnitsData添加方便的构造函数

这不是必需的,但此答案的后续部分会使用它,并建议使用。

不必编写代码来构造空的FoodUnitsData对象,然后使用setter来设置值,如果添加构造函数以允许传递值,则可以简化编码。

因此,可以添加以下构造函数(我还建议使用空构造函数)。以下是两者的代码:-

/* ADDED Constructors */
FoodUnitsData(){}
FoodUnitsData(String unit,
              String amount,
              String calory,
              String calcium,
              String cholestrl,
              String carbohydrt,
              String fiberTd,
              String iron,
              String lipidTot,
              String potassium,
              String protein,
              String sodium,
              String vitAIu,
              String vitC
){
    this.unit = unit;
    this.amount = amount;
    this.calory = calory;
    this.calcium = calcium;
    this.cholestrl = cholestrl;
    this.carbohydrt = carbohydrt;
    this.fiberTd = fiberTd;
    this.iron = iron;
    this.lipidTot = lipidTot;
    this.potassium = potassium;
    this.sodium = sodium;
    this.protein = protein;
    this.vitAIu = vitAIu;
    this.vitC = vitC;

}

测试/演示

以下MainActivity代码用于测试/演示:-

public class MainActivity extends AppCompatActivity {

    FoodDatabase fooddb;
    DaoAccess foodDao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        /* Build data to test */
        Foods foods = new Foods();
        foods.setFoodId("MyFood");
        foods.setCarbPercent("10.345");
        foods.setFoodDescription("The Food");
        foods.setFatPercent("15.234");
        foods.setFoodImage("The Food Image");
        foods.setFoodKcal("120");
        foods.setFoodName("The Food");
        foods.setFoodUrl("URL for the Food");
        foods.setProteinPercent("16.234");
        foods.setUnits(Arrays.asList(
                new FoodUnitsData("100","15","1200","11","12","13","14","15","16","17","18","19","20","21"),
                new FoodUnitsData("1001","151","12001","11","12","13","14","15","16","17","18","19","20","21"),
                new FoodUnitsData("1002","152","12002","11","12","13","14","15","16","17","18","19","20","21")
        ));

        String json = new Gson().toJson(foods);
        Log.d("JSONINFO",json);
        Foods foodsFromJSON = new Gson().fromJson(json,Foods.class);

        fooddb = Room.databaseBuilder(this,FoodDatabase.class,"food.db")
                .allowMainThreadQueries()
                .build();
        foodDao = fooddb.daoAccess();
        foodDao.insertFoodsWithAllTheFoodUnitsDataEntityChildren(foodsFromJSON);
    }
}

测试/演示结果

使用应用检查(数据库检查器)可以看到数据库是:-

和Food_unit表:-

相关文章