動(dòng)態(tài)sql語句概述

Mybatis 的映射?件中，前?我們的 SQL 都是?較簡單的，有些時(shí)候業(yè)務(wù)邏輯復(fù)雜時(shí)，我們的 SQL是動(dòng)態(tài)變化的，此時(shí)在前?的學(xué)習(xí)中我們的 SQL 就不能滿?要求了。

參考的官??檔，描述如下：

Dynamic SQL
One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities. If you have any experience with JDBC or any similar framework, you understand how painful it is to conditionally concatenate strings of SQL together, making sure not to forget spaces or to omit a comma at the end of a list of columns. Dynamic SQL can be downright painful to deal with.

While working with Dynamic SQL will never be a party, MyBatis certainly improves the situation with a powerful Dynamic SQL language that can be used within any mapped SQL statement.

The Dynamic SQL elements should be familiar to anyone who has used JSTL or any similar XML based text processors. In previous versions of MyBatis, there were a lot of elements to know and understand. MyBatis 3 greatly improves upon this, and now there are less than half of those elements to work with. MyBatis employs powerful OGNL based expressions to eliminate most of the other elements:

· if
· choose (when, otherwise)
· trim (where, set)
· foreach

我們根據(jù)實(shí)體類的不同取值，使?不同的 SQL語句來進(jìn)?查詢。?如在 id如果不為空時(shí)可以根據(jù)id查詢，如果username 不同空時(shí)還要加??戶名作為條件。這種情況在我們的多條件組合查詢中經(jīng)常會碰到。

接下來我們來看具體的代碼實(shí)現(xiàn)
在IUserProxyDao接口中增加findByCondition方法

package study.lagou.com.dao;

import study.lagou.com.pojo.User;

import java.util.List;

public interface IUserProxyDao {

    List<User> findAll();

    List<User> findByCondition(User user);
}

在UserProxyMapper.xml配置文件中編寫對應(yīng)的查詢方法，這里resultType和parameterType都基于別名來進(jìn)行使用

<?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="study.lagou.com.dao.IUserProxyDao">

    <!--查詢方法-->
    <select id="findAll" resultType="study.lagou.com.pojo.User">
        SELECT * FROM user
    </select>

    <select id="findByCondition" resultType="user" parameterType="user">
        SELECT * FROM user
        <where>
            <if test="id != null">
                AND id = #{id}
            </if>
            <if test="username != null and username != ''">
                AND username = #{username}
            </if>
        </where>
    </select>
</mapper>

在MyBatisProxyTest測試類編寫測試方法 testFindByCondition()，然后通過設(shè)置不同的查詢參數(shù)進(jìn)行查詢測試

package study.lagou.com.test;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import study.lagou.com.dao.IUserProxyDao;
import study.lagou.com.pojo.User;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @Description: MyBatis傳統(tǒng)開發(fā)方式測試
 * @Author houjh
 * @Email: happyxiaohou@gmail.com
 * @Date: 2021-3-22 19:48
 */
public class MyBatisProxyTest {

    @Test
    public void testFindAll() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        IUserProxyDao mapper = sqlSession.getMapper(IUserProxyDao.class);
        List<User> all = mapper.findAll();
        for (User user : all) {
            System.out.println(user);
        }
    }

    @Test
    public void testFindByCondition() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        IUserProxyDao mapper = sqlSession.getMapper(IUserProxyDao.class);
        User param = new User();
        param.setId(2);
        param.setUsername("zhangsan");
        List<User> all = mapper.findByCondition(param);
        for (User user : all) {
            System.out.println(user);
        }
    }

}

上一篇筆記地址:http://www.itdecent.cn/p/f377dd1073ef

下一篇筆記地址:http://www.itdecent.cn/p/7855d845fd80

具體代碼對應(yīng)下載地址:https://gitee.com/happymima/mybatis.git