mybatis中sql标签的使用

mybatis中sql标签重要是为了避免在项目开发的过程中重复编写大量相同的sql语句，例如下面的查询语句：

    <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>
    <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>
    <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>

除了最后的查询条件或者返回的resultMap出现变化之外，需要查询的字段是完全相同的，通过使用sql标签，可以将所有需要查询的字段封装在一个sql语句中，语句如下：

    <sql id="all_columns">
        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
    </sql>

那么通过使用sql将所有需要查询的字段封装起来，在编写select语句的时候就不需要每次都重复编写需要查询的字段了，只需要在查询语句中包含定义的sql语句即可，改造后的查询语句如下：

    <select id="selectCountryAndCity" parameterType="map" resultMap="countryAndCity1">
        SELECT <include refid="all_columns" />
        From country LEFT JOIN city ON country.code=city.countrycode WHERE country.code=#{code} AND city.name=#{name}
    </select>
    <select id="selectAllCityByCountry" parameterType="map" resultMap="countryAndCity2">
        SELECT <include refid="all_columns" />
        From country LEFT JOIN city ON country.code=city.countrycode WHERE country.code=#{code}
    </select>
    <select id="selectResultByIndepYear" parameterType="map" resultMap="countryAndCity3">
        SELECT <include refid="all_columns" />
        From country LEFT JOIN city ON country.code=city.countrycode WHERE country.code=#{code}
    </select>

通过使用sql标签减少重复的sql语句，不仅可以减少我们的代码量，还更加方便我们查看！

测试中所使用的数据库可以通过点击world.sql下载