天天看點
傳回

mybatis中association、collection和discriminator的使用

在使用Mybatis中定義resultMap的時候，經常會遇到這三個比較常用的标簽association、collection和discriminator，本篇部落格就在上兩遍部落格的基礎上，講解如何使用這個三個标簽。

下面依次進行介紹，首先看一下association：

先看一下兩個不同的resultMap定義：

<resultMap id="city" type="com.entities.City">
        <id column="city_id" jdbcType="INTEGER" property="id"/>
        <result column="city_name" jdbcType="CHAR" property="name"/>
        <result column="city_countryCode" jdbcType="CHAR" property="countrycode"/>
        <result column="city_district" jdbcType="CHAR" property="district"/>
        <result column="city_population" jdbcType="INTEGER" property="population"/>
    </resultMap>
    <resultMap id="countryAndCity" type="com.entities.AssociationBean">
        <id column="country_code" jdbcType="CHAR" property="code" />
        <result column="country_name" jdbcType="CHAR" property="name" />
        <result column="country_continent" jdbcType="CHAR" property="continent" />
        <result column="country_region" jdbcType="CHAR" property="region" />
        <result column="country_surfaceArea" jdbcType="REAL" property="surfacearea" />
        <result column="country_indepYear" jdbcType="SMALLINT" property="indepyear" />
        <result column="country_population" jdbcType="INTEGER" property="population" />
        <result column="country_lifeExpectancy" jdbcType="REAL" property="lifeexpectancy" />
        <result column="country_gnp" jdbcType="REAL" property="gnp" />
        <result column="country_gnpOld" jdbcType="REAL" property="gnpold" />
        <result column="country_localName" jdbcType="CHAR" property="localname" />
        <result column="country_governmentForm" jdbcType="CHAR" property="governmentform" />
        <result column="country_headOfState" jdbcType="CHAR" property="headofstate" />
        <result column="country_capital" jdbcType="INTEGER" property="capital" />
        <result column="country_code2" jdbcType="CHAR" property="code2" />
        <result column="city_id" jdbcType="INTEGER" property="city_id"/>
        <result column="city_name" jdbcType="CHAR" property="city_name"/>
        <result column="city_countryCode" jdbcType="CHAR" property="city_countryCode"/>
        <result column="city_district" jdbcType="CHAR" property="city_district"/>
        <result column="city_population" jdbcType="INTEGER" property="city_population"/>
    </resultMap>
    <resultMap id="countryAndCity1" type="com.entities.AssociationBean1">
        <id column="country_code" jdbcType="CHAR" property="code" />
        <result column="country_name" jdbcType="CHAR" property="name" />
        <result column="country_continent" jdbcType="CHAR" property="continent" />
        <result column="country_region" jdbcType="CHAR" property="region" />
        <result column="country_surfaceArea" jdbcType="REAL" property="surfacearea" />
        <result column="country_indepYear" jdbcType="SMALLINT" property="indepyear" />
        <result column="country_population" jdbcType="INTEGER" property="population" />
        <result column="country_lifeExpectancy" jdbcType="REAL" property="lifeexpectancy" />
        <result column="country_gnp" jdbcType="REAL" property="gnp" />
        <result column="country_gnpOld" jdbcType="REAL" property="gnpold" />
        <result column="country_localName" jdbcType="CHAR" property="localname" />
        <result column="country_governmentForm" jdbcType="CHAR" property="governmentform" />
        <result column="country_headOfState" jdbcType="CHAR" property="headofstate" />
        <result column="country_capital" jdbcType="INTEGER" property="capital" />
        <result column="country_code2" jdbcType="CHAR" property="code2" />
        <association property="city" resultMap="city"/>
    </resultMap>

其實countryAndCity和countryAndCity1在實際對傳回結果集的表示上是一緻，都是對結果的封裝，隻是countryAndCity1将City表中的字段抽取出來進行封裝成city對象，并将其作為一個屬性存放在AssociationBean1對象中。

而countryAndCity隻是将查詢語句傳回的所有字段映射為一個對象的内部屬性，并沒有對部分屬性進行二次封裝。類似的這種封裝在聯表查詢中經常會遇到這種情況，下載下傳看一下代碼，以及完成的mapper語句

<resultMap id="city" type="com.entities.City">
        <id column="city_id" jdbcType="INTEGER" property="id"/>
        <result column="city_name" jdbcType="CHAR" property="name"/>
        <result column="city_countryCode" jdbcType="CHAR" property="countrycode"/>
        <result column="city_district" jdbcType="CHAR" property="district"/>
        <result column="city_population" jdbcType="INTEGER" property="population"/>
    </resultMap>
    <resultMap id="countryAndCity1" type="com.entities.AssociationBean1">
        <id column="country_code" jdbcType="CHAR" property="code" />
        <result column="country_name" jdbcType="CHAR" property="name" />
        <result column="country_continent" jdbcType="CHAR" property="continent" />
        <result column="country_region" jdbcType="CHAR" property="region" />
        <result column="country_surfaceArea" jdbcType="REAL" property="surfacearea" />
        <result column="country_indepYear" jdbcType="SMALLINT" property="indepyear" />
        <result column="country_population" jdbcType="INTEGER" property="population" />
        <result column="country_lifeExpectancy" jdbcType="REAL" property="lifeexpectancy" />
        <result column="country_gnp" jdbcType="REAL" property="gnp" />
        <result column="country_gnpOld" jdbcType="REAL" property="gnpold" />
        <result column="country_localName" jdbcType="CHAR" property="localname" />
        <result column="country_governmentForm" jdbcType="CHAR" property="governmentform" />
        <result column="country_headOfState" jdbcType="CHAR" property="headofstate" />
        <result column="country_capital" jdbcType="INTEGER" property="capital" />
        <result column="country_code2" jdbcType="CHAR" property="code2" />
        <association property="city" resultMap="city"/>
    </resultMap>
    <select id="selectCountryAndCity" parameterType="map" resultMap="countryAndCity1">
        SELECT country.Code as country_code,
        country.Name AS country_name,
        country.Continent AS country_contient,
        country.Region as country_region,
        country.SurfaceArea as country_surfaceArea,
        country.IndepYear as country_indepYear,
        country.Population as country_population,
        country.LifeExpectancy as country_lifeExpectancy,
        country.GNP as country_gnp,
        country.GNPOld as country_gnpOld,
        country.LocalName as country_localName,
        country.GovernmentForm as country_governmentForm,
        country.HeadOfState as country_headOfState,
        country.Capital as country_capital,
        country.Code2 as country_code2,
        city.ID as city_id,
        city.Name as city_name,
        city.CountryCode as city_countryCode,
        city.District as city_district,
        city.Population as city_population
        From country LEFT JOIN city ON country.code=city.countrycode WHERE country.code=#{code} AND city.name=#{name}

    </select>

相關的service和controller代碼：

//service:
public AssociationBean1 selectCountryAndCity(String code, String name){
        Map<String,String> param = new HashMap<String, String>();
        param.put("code",code);
        param.put("name",name);
        return countryMapper.selectCountryAndCity(param);
    }
//controller:
@ApiOperation(value = "selectCountryAndCity")
    @RequestMapping(value = "/countryAndCity",method = RequestMethod.GET,
    produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseDto selectCountryAndCity(@RequestParam(value = "code") @ApiParam(value = "國家代碼") String code,
                                            @RequestParam(value = "name") @ApiParam(value = "城市名稱") String name){
        AssociationBean1 bean = countryService.selectCountryAndCity(code,name);
        ResponseDto dto = new ResponseDto();
        dto.setData(bean);
        dto.setTimestamp(String.valueOf(System.currentTimeMillis()));
        dto.setStatus("200");
        return dto;
    }

基本上，通過以上代碼，直接啟動springboot應用，然後通過使用swagger提供的本地調試接口就可以測試項目中實作的接口是否正确了！

實際的運作結果如下：

{
  "status": "200",
  "timestamp": "1525593649226",
  "data": {
    "code": "NLD",
    "name": "Netherlands",
    "continent": null,
    "region": "Western Europe",
    "surfacearea": ,
    "indepyear": ,
    "population": ,
    "lifeexpectancy": ,
    "gnp": ,
    "gnpold": ,
    "localname": "Nederland",
    "governmentform": "Constitutional Monarchy",
    "headofstate": "Beatrix",
    "capital": ,
    "code2": "NL",
    "city": {
      "id": ,
      "name": "Amsterdam",
      "countrycode": "NLD",
      "district": "Noord-Holland",
      "population": 
    }
  }
}

從結果中可以看到，内部有五個字段被封裝成了一個city對象。association的用法大概就是這樣，在看一下collection的用法。

collection與association的用法比較類似，association适用于一對一的關聯，而collection更多用于一對多的關聯，例如在資料庫中查詢一個國家的所有被記錄的城市，這個時候适合使用collection标簽，具體用法如下：

<resultMap id="city" type="com.entities.City">
        <id column="city_id" jdbcType="INTEGER" property="id"/>
        <result column="city_name" jdbcType="CHAR" property="name"/>
        <result column="city_countryCode" jdbcType="CHAR" property="countrycode"/>
        <result column="city_district" jdbcType="CHAR" property="district"/>
        <result column="city_population" jdbcType="INTEGER" property="population"/>
    </resultMap>
    <resultMap id="countryAndCity2" type="com.entities.CollectionBean">
        <id column="country_code" jdbcType="CHAR" property="code" />
        <result column="country_name" jdbcType="CHAR" property="name" />
        <result column="country_continent" jdbcType="CHAR" property="continent" />
        <result column="country_region" jdbcType="CHAR" property="region" />
        <result column="country_surfaceArea" jdbcType="REAL" property="surfacearea" />
        <result column="country_indepYear" jdbcType="SMALLINT" property="indepyear" />
        <result column="country_population" jdbcType="INTEGER" property="population" />
        <result column="country_lifeExpectancy" jdbcType="REAL" property="lifeexpectancy" />
        <result column="country_gnp" jdbcType="REAL" property="gnp" />
        <result column="country_gnpOld" jdbcType="REAL" property="gnpold" />
        <result column="country_localName" jdbcType="CHAR" property="localname" />
        <result column="country_governmentForm" jdbcType="CHAR" property="governmentform" />
        <result column="country_headOfState" jdbcType="CHAR" property="headofstate" />
        <result column="country_capital" jdbcType="INTEGER" property="capital" />
        <result column="country_code2" jdbcType="CHAR" property="code2" />
        <collection javaType="list" resultMap="city" property="cities"/>
    </resultMap>

使用property指明在java對象中對應的屬性名，使用javaType表明屬性類型為一個list集合。

在select中使用時，隻需要直接指定resultMap值為countryAndCity2即可，select語句如下：

<select id="selectAllCityByCountry" parameterType="map" resultMap="countryAndCity2">
        SELECT country.Code as country_code,
        country.Name AS country_name,
        country.Continent AS country_contient,
        country.Region as country_region,
        country.SurfaceArea as country_surfaceArea,
        country.IndepYear as country_indepYear,
        country.Population as country_population,
        country.LifeExpectancy as country_lifeExpectancy,
        country.GNP as country_gnp,
        country.GNPOld as country_gnpOld,
        country.LocalName as country_localName,
        country.GovernmentForm as country_governmentForm,
        country.HeadOfState as country_headOfState,
        country.Capital as country_capital,
        country.Code2 as country_code2,
        city.ID as city_id,
        city.Name as city_name,
        city.CountryCode as city_countryCode,
        city.District as city_district,
        city.Population as city_population
        From country LEFT JOIN city ON country.code=city.countrycode WHERE country.code=#{code}
    </select>

相對應的service和controller代碼如下：

//service:
    public CollectionBean selectAllCityByCountry(String code){
        Map<String,String> param = new HashMap<String, String>();
        param.put("code",code);
        return countryMapper.selectAllCityByCountry(param);
    }
    //controller:
    @ApiOperation(value = "selectAllCityByCountry")
    @RequestMapping(value = "/allCity",method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseDto selectAllCityByCountry(@RequestParam(value = "code") @ApiParam(value = "國家代碼") String code){
        CollectionBean bean = countryService.selectAllCityByCountry(code);
        ResponseDto dto = new ResponseDto();
        dto.setData(bean);
        dto.setTimestamp(String.valueOf(System.currentTimeMillis()));
        dto.setStatus("200");
        return dto;
    }

代碼測試結果如下：

{
  "status": "200",
  "timestamp": "1525594129868",
  "data": {
    "code": "AFG",
    "name": "Afghanistan",
    "continent": null,
    "region": "Southern and Central Asia",
    "surfacearea": ,
    "indepyear": ,
    "population": ,
    "lifeexpectancy": ,
    "gnp": ,
    "gnpold": null,
    "localname": "Afganistan/Afqanestan",
    "governmentform": "Islamic Emirate",
    "headofstate": "Mohammad Omar",
    "capital": ,
    "code2": "AF",
    "cities": [
      {
        "id": ,
        "name": "Kabul",
        "countrycode": "AFG",
        "district": "Kabol",
        "population": 
      },
      {
        "id": ,
        "name": "Qandahar",
        "countrycode": "AFG",
        "district": "Qandahar",
        "population": 
      },
      {
        "id": ,
        "name": "Herat",
        "countrycode": "AFG",
        "district": "Herat",
        "population": 
      },
      {
        "id": ,
        "name": "Mazar-e-Sharif",
        "countrycode": "AFG",
        "district": "Balkh",
        "population": 
      }
    ]
  }
}

從實際的測試結果來看，mybatis已經自動的将所有的city自動封裝成一個list！

關于最後一個discriminator用來根據查詢傳回的結果集不同而采用不同的ResultMap，具體的使用如下：

<!--判斷傳回的獨立時間，如果獨立時間為-1512，則使用countryAndCity2，如果為1776則使用BaseResultMap-->
    <resultMap id="countryAndCity3" type="com.entities.Country">
        <discriminator column="country_indepYear" jdbcType="INTEGER" javaType="int">
            <case value="1949" resultMap="countryAndCity2"/>
            <case value="1776" resultMap="BaseResultMap"/>
        </discriminator>
    </resultMap>

兩個resultMap之間的差別如下：

<resultMap id="BaseResultMap" type="com.entities.Country">
    <id column="country_code" jdbcType="CHAR" property="code" />
    <result column="country_name" jdbcType="CHAR" property="name" />
    <result column="country_continent" jdbcType="CHAR" property="continent" />
    <result column="country_region" jdbcType="CHAR" property="region" />
    <result column="country_surfaceArea" jdbcType="REAL" property="surfacearea" />
    <result column="country_indepYear" jdbcType="SMALLINT" property="indepyear" />
    <result column="country_population" jdbcType="INTEGER" property="population" />
    <result column="country_lifeExpectancy" jdbcType="REAL" property="lifeexpectancy" />
    <result column="country_gnp" jdbcType="REAL" property="gnp" />
    <result column="country_gnpOld" jdbcType="REAL" property="gnpold" />
    <result column="country_localName" jdbcType="CHAR" property="localname" />
    <result column="country_governmentForm" jdbcType="CHAR" property="governmentform" />
    <result column="country_headOfState" jdbcType="CHAR" property="headofstate" />
    <result column="country_capital" jdbcType="INTEGER" property="capital" />
    <result column="country_code2" jdbcType="CHAR" property="code2" />
  </resultMap>
    <resultMap id="city" type="com.entities.City">
        <id column="city_id" jdbcType="INTEGER" property="id"/>
        <result column="city_name" jdbcType="CHAR" property="name"/>
        <result column="city_countryCode" jdbcType="CHAR" property="countrycode"/>
        <result column="city_district" jdbcType="CHAR" property="district"/>
        <result column="city_population" jdbcType="INTEGER" property="population"/>
    </resultMap>
    <resultMap id="countryAndCity2" type="com.entities.CollectionBean">
        <id column="country_code" jdbcType="CHAR" property="code" />
        <result column="country_name" jdbcType="CHAR" property="name" />
        <result column="country_continent" jdbcType="CHAR" property="continent" />
        <result column="country_region" jdbcType="CHAR" property="region" />
        <result column="country_surfaceArea" jdbcType="REAL" property="surfacearea" />
        <result column="country_indepYear" jdbcType="SMALLINT" property="indepyear" />
        <result column="country_population" jdbcType="INTEGER" property="population" />
        <result column="country_lifeExpectancy" jdbcType="REAL" property="lifeexpectancy" />
        <result column="country_gnp" jdbcType="REAL" property="gnp" />
        <result column="country_gnpOld" jdbcType="REAL" property="gnpold" />
        <result column="country_localName" jdbcType="CHAR" property="localname" />
        <result column="country_governmentForm" jdbcType="CHAR" property="governmentform" />
        <result column="country_headOfState" jdbcType="CHAR" property="headofstate" />
        <result column="country_capital" jdbcType="INTEGER" property="capital" />
        <result column="country_code2" jdbcType="CHAR" property="code2" />
        <collection javaType="list" resultMap="city" property="cities"/>
    </resultMap>

BaseResultMap隻是包含了國家的相關資訊，并不包含城市資訊，而countryAndCity2不僅包含了國家資訊，還包含了一個國家中的城市資訊。具體的查詢如下：

<select id="selectResultByIndepYear" parameterType="map" resultMap="countryAndCity3">
        SELECT country.Code as country_code,
        country.Name AS country_name,
        country.Continent AS country_contient,
        country.Region as country_region,
        country.SurfaceArea as country_surfaceArea,
        country.IndepYear as country_indepYear,
        country.Population as country_population,
        country.LifeExpectancy as country_lifeExpectancy,
        country.GNP as country_gnp,
        country.GNPOld as country_gnpOld,
        country.LocalName as country_localName,
        country.GovernmentForm as country_governmentForm,
        country.HeadOfState as country_headOfState,
        country.Capital as country_capital,
        country.Code2 as country_code2,
        city.ID as city_id,
        city.Name as city_name,
        city.CountryCode as city_countryCode,
        city.District as city_district,
        city.Population as city_population
        From country LEFT JOIN city ON country.code=city.countrycode WHERE country.code=#{code}
    </select>

記住這裡的BaseResultMap和countryAndCity2對應的javaBean存在一定的關系，也就是countryAndCity3對應的java類一定為BaseResultMap和countryAndCity2對應的java類的父類，如果不這樣做的話，沒有辦法定義mapper接口的傳回類型，在測試項目中CollectionBean繼承Country類。

相關的service和controller代碼如下：

//service:
    public Country selectResultByIndepYear(String code){
        Map<String,String> param = new HashMap<String, String>();
        param.put("code",code);
        return countryMapper.selectResultByIndepYear(param);
    }
    //controller:
    @ApiOperation(value = "selectAllCityByCountry")
    @RequestMapping(value = "/allResult",method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseDto selectResultByIndepYear(@RequestParam(value = "code") @ApiParam(value = "國家代碼") String code){
        Country bean = countryService.selectResultByIndepYear(code);
        ResponseDto dto = new ResponseDto();
        dto.setData(bean);
        dto.setTimestamp(String.valueOf(System.currentTimeMillis()));
        dto.setStatus("200");
        return dto;
    }

實際測試中，傳回的結果如下：CHN的獨立時間為-1512，USA的獨立時間為1776，

指定code為CHN：

{
  "status": "200",
  "timestamp": "1525594968864",
  "data": {
    "code": "CHN",
    "name": "China",
    "continent": null,
    "region": "Eastern Asia",
    "surfacearea": ,
    "indepyear": ,
    "population": ,
    "lifeexpectancy": ,
    "gnp": ,
    "gnpold": ,
    "localname": "Zhongquo",
    "governmentform": "People'sRepublic",
    "headofstate": "Jiang Zemin",
    "capital": ,
    "code2": "CN",
    "cities": [
      {
        "id": ,
        "name": "Shanghai",
        "countrycode": "CHN",
        "district": "Shanghai",
        "population": 
      },
      {
        "id": ,
        "name": "Peking",
        "countrycode": "CHN",
        "district": "Peking",
        "population": 
      }
     ......
  }

傳回結果隻是展示了部分，可以看到實際傳回的對象就是通過countryAndCity2指定的CollectionBean封裝的，再來測試code=USA：

{
  "status": "200",
  "timestamp": "1525595092000",
  "data": {
    "code": "USA",
    "name": "United States",
    "continent": null,
    "region": "North America",
    "surfacearea": ,
    "indepyear": ,
    "population": ,
    "lifeexpectancy": ,
    "gnp": ,
    "gnpold": ,
    "localname": "United States",
    "governmentform": "Federal Republic",
    "headofstate": "George W. Bush",
    "capital": ,
    "code2": "US"
  }
}

實際傳回的結果是通過BaseResultMap所指定的Country類進行封裝的。

關于association、collection和discriminator的使用就示範到這裡，測試中所使用的資料庫可以通過點選world.sql下載下傳

Mybaits mybatis 聯表查詢 結果封裝 assocation collection
上一篇: 統計連續列重複個數
下一篇: auto html 裡面table,來自HTML表的jsPDF AutoTable rowspan / colspan

繼續閱讀