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)查询