动态SQL

何为动态SQL？？回顾一下我们之前写的SSH项目中，有多条件查询的情况，如下图

我们当时刚开始做的时候，是需要在Controller中判断SQL是否已经有条件了，因为SQL语句需要拼接起来….这样干的话，就非常容易出错的。

如下的代码，如果有多个条件的话，那么拼接起来很容易出错！

  public String listUI() {

        //查询语句

        String hql = "FROM Info i ";

        List<Object> objectList  = new ArrayList<>();

        //根据info是否为null来判断是否是条件查询。如果info为空，那么是查询所有。

        if (info != null) {

            if (StringUtils.isNotBlank(info.getTitle())) {

                hql += "where i.title like ?";

                objectList.add("%" + info.getTitle() + "%");

            }

        }

        infoList = infoServiceImpl.findObjects(hql,objectList);

        ActionContext.getContext().getContextMap().put("infoTypeMap", Info.INFO_TYPE_MAP);

        return "listUI";

    }

后来，我们觉得这样不好，于是就专门写了一个查询助手类：

package zhongfucheng.core.utils;

import java.util.ArrayList;

import java.util.List;

/**

 * Created by ozc on 2017/6/7.

 */

public class QueryHelper {

    private String fromClause = "";

    private String whereClause = "";

    private String orderbyClause = "";

    private List<Object> objectList;

    public static String ORDER_BY_ASC = "asc";

    public static String ORDER_BY_DESC = "desc";

    //FROM子句只出现一次

    /**

     * 构建FROM字句，并设置查询哪张表

     * @param aClass 用户想要操作的类型

     * @param alias  别名

     */

    public QueryHelper(Class aClass, String alias) {

        fromClause = "  FROM " + aClass.getSimpleName() + "  " + alias;

    }

    //WHERE字句可以添加多个条件，但WHERE关键字只出现一次

    /**

     * 构建WHERE字句

     * @param condition

     * @param objects

     * @return

     */

    public QueryHelper addCondition(String condition, Object... objects) {

        //如果已经有字符了，那么就说明已经有WHERE关键字了

        if (whereClause.length() > 0) {

            whereClause += " AND  " + condition;

        } else {

            whereClause += " WHERE" + condition;

        }

        //在添加查询条件的时候，?对应的查询条件值

        if (objects == null) {

            objectList = new ArrayList<>();

        }

        for (Object object : objects) {

            objectList.add(object);

        }

        return this;

    }

    /**

     *

     * @param property 要排序的属性

     * @param order 是升序还是降序

     * @return

     */

    public QueryHelper orderBy(String property, String order) {

        //如果已经有字符了，那么就说明已经有ORDER关键字了

        if (orderbyClause.length() > 0) {

            orderbyClause += " ,  " + property +"   " + order;

        } else {

            orderbyClause += "  ORDER BY " + property+"   " + order;

        }

        return this;

    }

    /**

     * 返回HQL语句

     */

    public String returnHQL() {

        return fromClause + whereClause + orderbyClause;

    }

    /**

     * 得到参数列表

     * @return

     */

    public List<Object> getObjectList() {

        return objectList;

    }

}

这样一来的话，我们就不用自己手动拼接了，给我们的查询助手类去拼接就好了。

而如果我们使用Mybatis的话，就可以免去查询助手类了。因为Mybatis内部就有动态SQL的功能【动态SQL就是自动拼接SQL语句】！

动态查询

    <!--多条件查询【动态SQL】-->

    <!--会自动组合成一个正常的WHERE字句-->

    <!--name值会从map中寻找-->

    <select id="findByCondition" resultMap="studentMap" parameterType="map">

        select * from students

        <where>

            <if test="name!=null">

                and name=#{name}

            </if>

            <if test="sal!=null">

                and sal &lt; #{sal}

            </if>

        </where>

    </select>

查询出来小于9000块的人

   public List<Student> findByCondition(String name,Double sal) throws Exception {

        //得到连接对象

        SqlSession sqlSession = MybatisUtil.getSqlSession();

        try{

            //映射文件的命名空间.SQL片段的ID，就可以调用对应的映射文件中的SQL

            /**

             * 由于我们的参数超过了两个，而方法中只有一个Object参数收集

             * 因此我们使用Map集合来装载我们的参数

             */

            Map<String, Object> map = new HashMap();

            map.put("name", name);

            map.put("sal", sal);

            return sqlSession.selectList("StudentID.findByCondition", map);

        }catch(Exception e){

            e.printStackTrace();

            sqlSession.rollback();

            throw e;

        }finally{

            MybatisUtil.closeSqlSession();

        }

    }

    public static void main(String[] args) throws Exception {

        StudentDao studentDao = new StudentDao();

        List<Student> students = studentDao.findByCondition(null,9000D);

        for (Student student : students) {

            System.out.println(student.getId() + "---" + student.getName() + "----" + student.getSal());

        }

    }

动态更新

    <!--动态更新-->

    <!--不要忘了逗号-->

    <update id="updateByConditions" parameterType="map">

        update students

        <set>

            <if test="name!=null">

                 name = #{name},

            </if>

            <if test="sal!=null">

                 sal = #{sal},

            </if>

        </set>

        where id = #{id}

    </update>

给出三个更新的字段

    public void updateByConditions(int id,String name,Double sal) throws Exception {

        //得到连接对象

        SqlSession sqlSession = MybatisUtil.getSqlSession();

        try{

            //映射文件的命名空间.SQL片段的ID，就可以调用对应的映射文件中的SQL

            /**

             * 由于我们的参数超过了两个，而方法中只有一个Object参数收集

             * 因此我们使用Map集合来装载我们的参数

             */

            Map<String, Object> map = new HashMap();

            map.put("id", id);

            map.put("name", name);

            map.put("sal", sal);

            sqlSession.update("StudentID.updateByConditions", map);

            sqlSession.commit();

        }catch(Exception e){

            e.printStackTrace();

            sqlSession.rollback();

            throw e;

        }finally{

            MybatisUtil.closeSqlSession();

        }

    }

    public static void main(String[] args) throws Exception {

        StudentDao studentDao = new StudentDao();

        studentDao.updateByConditions(2,"haha",500D);

    }

动态删除

以前我们使用JDBC也好，Hibernate也好，想要批量删除的时候，总是使用的是循环删除。而我们现在使用的是Mybatis，SQL语句是自己写的。所以我们可以写下如下的SQL来进行删除

delete from students where id in (?,?,?,?);

而我们的Mybatis又支持动态SQL,所以删除起来就非常方便了！

    <delete id="deleteByConditions" parameterType="int">

        <!-- foreach用于迭代数组元素

             open表示开始符号

             close表示结束符合

             separator表示元素间的分隔符

             item表示迭代的数组，属性值可以任意，但提倡与方法的数组名相同

             #{ids}表示数组中的每个元素值

         -->

        delete from students where id in

         <foreach collection="array" open="(" close=")" separator="," item="ids">

             #{ids}

         </foreach>

    </delete>

删除编号为2，3，4的记录

    public void deleteByConditions(int... ids) throws Exception {

        //得到连接对象

        SqlSession sqlSession = MybatisUtil.getSqlSession();

        try{

            //映射文件的命名空间.SQL片段的ID，就可以调用对应的映射文件中的SQL

            /**

             * 由于我们的参数超过了两个，而方法中只有一个Object参数收集

             * 因此我们使用Map集合来装载我们的参数

             */

            sqlSession.delete("StudentID.deleteByConditions", ids);

            sqlSession.commit();

        }catch(Exception e){

            e.printStackTrace();

            sqlSession.rollback();

            throw e;

        }finally{

            MybatisUtil.closeSqlSession();

        }

    }

    public static void main(String[] args) throws Exception {

        StudentDao studentDao = new StudentDao();

        studentDao.deleteByConditions(2,3,4);

    }

动态插入

我们要想动态插入的话，就比其他的DML语句稍微复杂一点，因为它有两部分是不确定的，平常的SQL语句是这样的：

insert into student(id,name,sal) values(?,?,?)

SQL代码块是不能像之前那样帮我们自动去除多余的逗号的，因此我们需要使用trim标签来自己手动去除…

编写insertSQL语句的时候，不要忘了写()括号。

    <!--SQL片段默认是不帮我们自动生成合适的SQL，因此需要我们自己手动除去逗号-->

    <sql id="key">

        <trim suffixOverrides=",">

            <if test="id!=null">

                id,

            </if>

            <if test="id!=null">

                name,

            </if>

            <if test="id!=null">

                sal,

            </if>

        </trim>

    </sql>

    <sql id="value">

        <trim suffixOverrides=",">

            <if test="id!=null">

                #{id},

            </if>

            <if test="id!=null">

                #{name},

            </if>

            <if test="id!=null">

                #{sal},

            </if>

        </trim>

    </sql>

    <!--动态插入-->

    <insert id="insertByConditions" parameterType="zhongfucheng.Student">

        insert into students (<include refid="key"/>) values

        (<include refid="value"/>)

    </insert>

测试三个不同内容的数据

    public void insertByConditions(Student student) throws Exception {

        //得到连接对象

        SqlSession sqlSession = MybatisUtil.getSqlSession();

        try{

            //映射文件的命名空间.SQL片段的ID，就可以调用对应的映射文件中的SQL

            sqlSession.insert("StudentID.insertByConditions", student);

            sqlSession.commit();

        }catch(Exception e){

            e.printStackTrace();

            sqlSession.rollback();

            throw e;

        }finally{

            MybatisUtil.closeSqlSession();

        }

    }

    public static void main(String[] args) throws Exception {

        StudentDao studentDao = new StudentDao();

        studentDao.insertByConditions(new Student(55, null, null));//name和sal为空

        studentDao.insertByConditions(new Student(66, "haxi", null));//sal为空

        studentDao.insertByConditions(new Student(77, null, 3999d));//name为空

    }