Android的room数据库表数据的多表查询示范:不使用实体类之间的@ForeignKey外键关联

这里示范市省市区的三张表,
功能是模糊查询省市区
1.这是json结构：

把json分成省市区三快，三个表(直辖市和省一级,区县和区县一下为一级)
2.建立三个实体对象:ProvinceBean，ShiBean，QuBean
大家会发现没有像网上教程一样使用实体类之间的@ForeignKey外键关联,确实没有(原因是我参考着效果没出来报错),但效果是实现了的,这里实现三个表的联合模糊查询,只在dao层放了一条长长的sql语句：
省实体

@Entity(tableName = "province")
public class ProvinceBean implements Serializable {
    /**
     * cityLevel : 1
     * province : 吉林省
     * entry : [{"cityLevel":"2","province":"吉林省","secondaryCity":"长春市","cityId":"182","entry":[{"cityLevel":"3","cityId":"183","cityName":"双阳区","province":"吉林省","secondaryCity":"长春市"},{"cityLevel":"3","cityId":"184","cityName":"九台区","province":"吉林省","secondaryCity":"长春市"}]}]
     */

    @PrimaryKey()
    @ColumnInfo(name = "provinceId")
    private int provinceId;//省id

    @ColumnInfo(name = "provinceName")
    private String province;//省份名字

    @Ignore
    private String cityLevel;//城市等级

    private String secondaryCity;//地级市名字
    private String cityName;//县区域名字
    private String shiCityId;//地级市区域id
    private String quCityId;//县区域名字

    public String getShiCityId() {
        return shiCityId;
    }

    public void setShiCityId(String shiCityId) {
        this.shiCityId = shiCityId;
    }

    public String getQuCityId() {
        return quCityId;
    }

    public void setQuCityId(String quCityId) {
        this.quCityId = quCityId;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public String getSecondaryCity() {
        return secondaryCity;
    }

    public void setSecondaryCity(String secondaryCity) {
        this.secondaryCity = secondaryCity;
    }

    public Integer getProvinceId() {
        return provinceId;
    }

    public void setProvinceId(Integer provinceId) {
        this.provinceId = provinceId;
    }

    /**
     * cityLevel : 2
     * province : 吉林省
     * secondaryCity : 长春市
     * cityId : 182
     * entry : [{"cityLevel":"3","cityId":"183","cityName":"双阳区","province":"吉林省","secondaryCity":"长春市"},{"cityLevel":"3","cityId":"184","cityName":"九台区","province":"吉林省","secondaryCity":"长春市"}]
     */

    public String getCityLevel() {
        return cityLevel;
    }

    public void setCityLevel(String cityLevel) {
        this.cityLevel = cityLevel;
    }

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }


}

市实体:

/**
 * 市的实体
 * , foreignKeys = @ForeignKey(entity = ProvinceBean.class,
 * parentColumns = "provinceId", childColumns = "shi_parentId")
 * , indices =@Index(value = {"shi_parentId"}, unique = true)
 */
@Entity(tableName = "shi")
public class ShiBean {
    // 主键
    @PrimaryKey(autoGenerate = true)
    private Integer shiId;
    private String cityLevel;
    private String province;
    private String secondaryCity;
    private String shiCityId;
    @ColumnInfo(name = "shi_parentId")
    private String parentId;//假设这是 我们要的第一层id

    /**
     * cityLevel : 3
     * cityId : 183
     * cityName : 双阳区
     * province : 吉林省
     * secondaryCity : 长春市
     */

//    private List<QuBean> entry;
    public String getCityLevel() {
        return cityLevel;
    }

    public void setCityLevel(String cityLevel) {
        this.cityLevel = cityLevel;
    }

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }

    public String getSecondaryCity() {
        return secondaryCity;
    }

    public void setSecondaryCity(String secondaryCity) {
        this.secondaryCity = secondaryCity;
    }

    public String getShiCityId() {
        return shiCityId;
    }

    public void setShiCityId(String shiCityId) {
        this.shiCityId = shiCityId;
    }

//    public List<QuBean> getEntry() {
//        return entry;
//    }
//
//    public void setEntry(List<QuBean> entry) {
//        this.entry = entry;
//    }

    public String getParentId() {
        return parentId;
    }

    public void setParentId(String parentId) {
        this.parentId = parentId;
    }

    public Integer getShiId() {
        return shiId;
    }

    public void setShiId(Integer shiId) {
        this.shiId = shiId;
    }
}

区的实体:

/**
 * 区的实体
 */
@Entity(tableName = "qu")
public class QuBean {
    // 主键
    @PrimaryKey(autoGenerate = true)
    private Integer quId;
    private String cityLevel;
    private String quCityId;
    private String cityName;
    private String province;
    private String secondaryCity;
    private String parentId;//假设这是 我们要的第二层id

    public String getCityLevel() {
        return cityLevel;
    }

    public void setCityLevel(String cityLevel) {
        this.cityLevel = cityLevel;
    }

    public String getQuCityId() {
        return quCityId;
    }

    public void setQuCityId(String quCityId) {
        this.quCityId = quCityId;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }

    public String getSecondaryCity() {
        return secondaryCity;
    }

    public void setSecondaryCity(String secondaryCity) {
        this.secondaryCity = secondaryCity;
    }

    public String getParentId() {
        return parentId;
    }

    public void setParentId(String parentId) {
        this.parentId = parentId;
    }

    public Integer getQuId() {
        return quId;
    }

    public void setQuId(Integer quId) {
        this.quId = quId;
    }
}

3.省表的dao(接口类)

/**
 * DO Dao 必须是接口或者抽象类，Room 使用注解自动生成访问数据库的代码
 * 对省份类的映射
 */
@Dao
public interface ProvinceDao {

    @Insert
    void insertProvince(ProvinceBean provinceBean);

    // TODO 如果插入的数据在数据库表中已经存在，就会抛出异常
    //TODO 关联关系 Province表的provinceId  是SHI表的parentId   //区县表QU的parentId是SHI表的cityId
    //模糊查询省份、province 省份名字
//    @Query("SELECT * FROM province WHERE provinceName LIKE '%' || :provinceName || '%'")
//    List<ProvinceBean> loadLikeProvince(String provinceName);//加一个占位符strName 表示%last_name%


//    @Query("select *  from  province  left join shi on shi.shi_parentId = province.provinceId " +
//            "left join qu on qu.parentId = shi.cityId WHERE provinceName LIKE '%' || :provinceName || '%' or shi.secondaryCity LIKE '%' || :provinceName || '%' or qu.cityName LIKE '%' || :provinceName || '%'")
//    List<CityInnerJoinResult> loadLikeProvince(String provinceName);//加一个占位符strName 表示%last_name%
    //select *  from  province  left join shi on shi.shi_parentId = province.provinceId left join qu on qu.parentId = shi.shiCityId WHERE provinceName LIKE '%' || '石家庄' || '%' or shi.secondaryCity LIKE '%' || "石家庄" || '%' or qu.cityName LIKE '%' || '石家庄' || '%'

    @Query("select province.provinceId,province.provinceName,shi.secondaryCity,qu.cityName, shi.shiCityId,qu.quCityId from  province  left join shi on shi.shi_parentId = province.provinceId " +
            "left join qu on qu.parentId = shi.shiCityId WHERE provinceName LIKE '%' || :provinceName || '%' or shi.secondaryCity LIKE '%' || " +
            ":provinceName || '%' or qu.cityName LIKE '%' || :provinceName || '%'")
    List<ProvinceBean> loadLikeProvince(String provinceName);//加一个占位符strName 表示%last_name%

    //left 加2名字，on,跟同属性，leftjoin,
    //select * from  province  left join shi on shi.parentId = province.provinceId left join qu on qu.parentId = shi.cityId
    //表名相同，属性给as，区name和市name
//    @Query("SELECT SHI.parentId FROM  PROVINCE INNER JOIN SHI ON PROVINCE.provinceId=SHI.parentId")
//    List<ProvinceBean> loadLikeProvince(String provinceName);
}

4.数据库的RoomDatabase类

**
 * 省份的映射
 */
@Database(entities = {ProvinceBean.class, ShiBean.class, QuBean.class}, version = 1, exportSchema = false)
public abstract class ProvinceDatabase extends RoomDatabase {
    private static volatile ProvinceDatabase mAppDatabase;

    //省
    public abstract ProvinceDao provinceDao();

    //市
    public abstract ShiDao shiDao();

    //区
    public abstract QuDao quDao();

}

省表结构

市表结构;

区表结构: