Mybatis动态sql

步骤1：创建实体类

package beans;


import java.sql.Date;

/**
 * Created by NewObject on 2017/8/14.
 */
public class ConferenceInfo {
    private Long id;
    private String cnname;
    private String enname;
    private String tag;
    private String location;
    private String sponsor;
    private Date startdate;
    private Date enddate;
    private Date deadline;
    private Date acceptance;
    private String website;


    @Override
    public String toString() {
        StringBuilder builder = new StringBuilder();
        builder.append("id:\t").append(this.id)
                .append("\ncnname:\t").append(this.cnname)
                .append("\nenname:\t").append(this.enname)
                .append("\nlocation:\t").append(this.location)
                .append("\nsponsor:\t").append(this.sponsor)
                .append("\nwebsite:\t").append(this.website)
                .append("\nstartdate:\t").append(this.startdate.toString())
                .append("\nenddate:\t").append(this.enddate.toString())
                .append("\n");
        return builder.toString();
    }

    public ConferenceInfo() {
        this.id = 0L;
    }
    //此处省略getter方法和setter方法
}

步骤2：在mysql中创建实体类对应的表

create table `conferenceinfo` 
(
  `id` bigint(20) unsigned not null auto_increment,
  `cnname` varchar(128),
  `enname` varchar(128),
  `tag` varchar(64),
  `location` varchar(64),
  `sponsor` varchar(64),
  `startdate` date,
  `enddate` date,
  `deadline` date,
  `acceptance` date,
  `website` varchar(128) not null,
  primary key (`id`),

  unique key `cnname_website` (`cnname`,`website`),

  KEY `conference_tag_index` (`id`,`tag`) using BTree,
  KEY `conference_startdate_index` (`startdate`) using BTree

) engine=InnoDB  default charset=utf8

步骤3：采用Mapper动态代理方式创建所需的接口

dao.IConferenceInfoDao.java

package dao;

import beans.ConferenceInfo;
import java.util.List;
import java.util.Map;

public interface IConferenceInfoDao {
    boolean addConferenceInfo(ConferenceInfo conference);
    List<ConferenceInfo> queryConferenceInfoByIf(ConferenceInfo conference);
    List<ConferenceInfo> queryConferenceInfoByIf(Map<String, Object>map);
    List<ConferenceInfo> queryConferenceInfoByForEach(String[] cities);
    List<ConferenceInfo> queryConferenceInfoByForEachList(List<String> cities);

}

步骤4：在resources下创建Mapper.xml

conferenceInfoMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.IConferenceInfoDao">
    <insert id="addConferenceInfo" parameterType="ConferenceInfo"
            useGeneratedKeys="true" keyProperty="id">
        insert into conferenceinfo
        <choose>

            <when test="
            cnname != null and location != null and
            website != null and startdate != null and
            tag != null and enddate != null and
            enname != null">
                (cnname, location,startdate,website,enddate,tag,enname)
                values(#{cnname},#{location},#{startdate},#{website},#{enddate},#{tag},#{enname})
            </when>

            <when test="
            cnname != null and location != null and
            website != null and startdate != null and
            tag != null and enddate != null and
            sponsor != null">
                (cnname, location,startdate,website,enddate,tag,sponsor)
                values(#{cnname},#{location},#{startdate},#{website},#{enddate},#{tag},#{sponsor})
            </when>

            <when test="
            cnname != null and location != null and
            website != null and startdate != null and
            tag != null and sponsor != null">
                (cnname, location,startdate,website,tag,sponsor)
                values(#{cnname},#{location},#{startdate},#{website},#{tag},#{sponsor})
            </when>

            <when test="
            cnname != null and location != null and
            website != null and startdate != null and
            tag != null and enddate != null">
                (cnname, location,startdate,website,enddate,tag)
                values(#{cnname},#{location},#{startdate},#{website},#{enddate},#{tag})
            </when>

            <when test="
            cnname != null and location != null and
            website != null and startdate != null">
                (cnname, location,startdate,website)
                values(#{cnname},#{location},#{startdate},#{website})
            </when>
        </choose>

    </insert>

    <select id="queryConferenceInfoByIf" resultType="ConferenceInfo">
        select id,cnname,tag,location,sponsor,startdate,enddate,website
        from conferenceinfo
        <where>

            <if test="location != null">
                location = #{location}
            </if>

            <if test="id != null">
                and id > #{id}
            </if>
        </where>
    </select>

    <select id="queryConferenceInfoByForEach" resultType="ConferenceInfo">
        select id,cnname,tag,location,sponsor,startdate,enddate,website
        from conferenceinfo
        <if test="array.length > 0">
            where location in
            <foreach collection="array" open="(" separator="," close=")" item="city">
                #{city}
            </foreach>
        </if>

    </select>

    <select id="queryConferenceInfoByForEachList" resultType="ConferenceInfo">
        select id,cnname,tag,location,sponsor,startdate,enddate,website
        from conferenceinfo
        <if test="list.size > 0 ">
            where location in
            <foreach collection="list" open="(" separator="," close=")" item="city">
                #{city}
            </foreach>
        </if>
    </select>
</mapper>

---

解析

1. 条件插入语句
映射配置文件mapper.xml下的insert 语句如下

<insert id="addConferenceInfo" parameterType="ConferenceInfo"
            useGeneratedKeys="true" keyProperty="id">
        insert into conferenceinfo
        <choose>

            <when test="
            cnname != null and location != null and
            website != null and startdate != null and
            tag != null and enddate != null and
            enname != null">
                (cnname, location,startdate,website,enddate,tag,enname)
                values(#{cnname},#{location},#{startdate},#{website},#{enddate},#{tag},#{enname})
            </when>

            <when test="
            cnname != null and location != null and
            website != null and startdate != null and
            tag != null and enddate != null and
            sponsor != null">
                (cnname, location,startdate,website,enddate,tag,sponsor)
                values(#{cnname},#{location},#{startdate},#{website},#{enddate},#{tag},#{sponsor})
            </when>

            <when test="
            cnname != null and location != null and
            website != null and startdate != null and
            tag != null and sponsor != null">
                (cnname, location,startdate,website,tag,sponsor)
                values(#{cnname},#{location},#{startdate},#{website},#{tag},#{sponsor})
            </when>

            <when test="
            cnname != null and location != null and
            website != null and startdate != null and
            tag != null and enddate != null">
                (cnname, location,startdate,website,enddate,tag)
                values(#{cnname},#{location},#{startdate},#{website},#{enddate},#{tag})
            </when>

            <when test="
            cnname != null and location != null and
            website != null and startdate != null">
                (cnname, location,startdate,website)
                values(#{cnname},#{location},#{startdate},#{website})
            </when>
        </choose>

    </insert>

choose标签和when标签相当于 switch 和 case 关键字，when标签相当于case语句，当遇到1个when标签测试结果为true时就选择该标签内语句执行，执行完跳出choose标签，后面when标签的测试结果即使是true也不会执行

2. if标签动态条件查询



3. 使用foreach标签的in集合(array)查询

4. 使用foreach标签的in集合(list)查询