MyBatis中动态SQL语句的书写

官网的动态SQL语句参考
动态SQL语句的书写
这里面的Dao.xml比如和java包中的对应的Dao路径一致：

主要的步骤是：
1、在SQLWorkbench中创建SQL语句，并复制到SpringBoot的项目resource目录中

DROP TABLE IF EXISTS question;
CREATE TABLE `wenda2`.`question` ( `id` INT NOT NULL AUTO_INCREMENT, `title` VARCHAR(256) NOT NULL, `content` TEXT NULL, `user_id` INT NOT NULL, `create_date` DATETIME NOT NULL, `comment_count` INT NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `id_UNIQUE` (`id` ASC), INDEX `create_date` (`create_date` ASC));

2、在model层中建立对应的model层，注意：SQL的下划线–》对象的驼峰的命名法

3、在DAO层建立SQL的执行

@Mapper
public interface QuestionDao {
String TABLE_NAME =” question “;
String INSERT_FIELDS=” title,content,user_id,create_date,comment_count “;
String SELECT_FIELDS =” id, “+INSERT_FIELDS;

//使用mapper写法
@Insert({"insert into " +TABLE_NAME+" ( " +INSERT_FIELDS +" ) "+
        "values( #{title} , #{content} , #{userId} , #{createDate}, #{commentCount} )"})
int addQuestion(Question question);
List<Question> selectLatestQuestions(@Param("userId") int userId,
                           @Param("offset") int offset,
                           @Param("limit") int limit);

}

4、在与Dao相同的包相同的resouce中写入动态的SQL语句

<?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="com.zju.dao.QuestionDao">
    <!--//写入sql的引用-->
    <sql id="table">question</sql>
    <sql id="selectFields">id,title,content,user_id,create_date,comment_count</sql>

    <!--//下面是选择的语句：类似于函数体-->
    <select id="selectLatestQuestions" resultType="com.zju.model.Question">

--         #下面写SQL语句
        SELECT <include refid="selectFields"/>
        FROM <include refid="table"/>

--         #下面是if的分支语句if标签内部是条件控制的语句
        <if test="userId != 0">
            WHERE user_id=#{userId}
        </if>

        ORDER BY id DESC
        LIMIT #{offset} ,#{limit}
    </select>
</mapper>

5、写QuestionDao的测试案例

//测试QuestionDao
            Question question=new Question();
            question.setTitle(String.format("谈论你幸福吗===%d",i));
            question.setUserId(i+1);
            question.setCommentCount(i*5);

            Date date =new Date();
            date.setTime(date.getTime()+1000*3600*24*i);
            question.setCreateDate(date);
            question.setContent(String.format("XXXXXXXXX--%d",i*i+3));

            questionDao.addQuestion(question);