动态SQL

精确查询:

引入 log4j.properties 文件,放到 resource 目录下

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=d:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
### set log levels - for more verbose logging change 'info' to 'debug' ###
log4j.rootLogger=debug, stdout

这里使用代理开发方式,创建dao层

package com.itheibai.dao;
import com.itheibai.entity.User;
import java.util.List;

public interface UserDao {
    List<User> findByCondition1(User user);
}

创建mapper文件

<?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.itheibai.dao.UserDao">
    <!--精确条件查询-->
    <select id="findByCondition1" parameterType="user" resultType="user">
        select * from user where id=#{id} and username=#{username} and password=#{password}
    </select>
</mapper>

创建测试类

package com.itheibai.test;
import com.itheibai.dao.UserDao;
import com.itheibai.entity.User;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MyBatisTest {
    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //模拟传入的user条件数据
        User condition = new User();
        condition.setId(1);
        condition.setUsername("张三");
        condition.setPassword("123456");
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.findByCondition1(condition);
        System.out.println(userList);
        sqlSession.close();
    }
}

精确查询结果

<if>条件查询

方式一:

<!--if条件查询方式一-->
<select id="findByCondition2" parameterType="user" resultType="user">
    select * from user where 1=1
    <if test="id!=0">
        and id=#{id}
    </if>
    <if test="username!=null">
        and username=#{username}
    </if>
    <if test="password!=null">
        and password=#{password}
    </if>
</select>

测试:

@Test
public void test2() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //模拟传入的user数据
    User condition = new User();
    condition.setId(1);
    condition.setUsername("张三");
    //这里没传password
    UserDao userDao = sqlSession.getMapper(UserDao.class);
    List<User> userList = userDao.findByCondition2(condition);
    System.out.println(userList);
    sqlSession.close();
}

查询结果:

查询语句后面 没有出现 password

方式二:

<!--if条件查询方式二，where标签相当于where 1=1-->
<select id="findByCondition3" parameterType="user" resultType="user">
    select * from user
    <where>
        <if test="id!=0">
            and id=#{id}
        </if>
        <if test="username!=null">
            and username=#{username}
        </if>
        <if test="password!=null">
            and password=#{password}
        </if>
    </where>
</select>

<foreach>标签

循环执行 sql 的拼接操作,例如:

select * from user where id in (1,2,5)

查询语句

<!--foreach标签拼接-->
<select id="findByCondition4" parameterType="list" resultType="user">
    select * from user 
    <where>
        <foreach collection="list" open="id in(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </where>
</select>

在dao层添加方法

package com.itheibai.dao;
import com.itheibai.entity.User;
import java.util.List;
public interface UserDao {
    List<User> findByCondition1(User user);
    List<User> findByCondition2(User user);
    List<User> findByCondition3(User user);
    List<User> findByCondition4(List<Integer> list);
}

测试代码

@Test
public void test4() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //模拟传入的list数据
    List<Integer> ids = new ArrayList<>();
    ids.add(1);
    ids.add(2);
    UserDao userDao = sqlSession.getMapper(UserDao.class);
    List<User> userList = userDao.findByCondition4(ids);
    System.out.println(userList);
    sqlSession.close();
}

注意:

foreach标签用于遍历集合,它的属性:
collection: 代表要遍历的集合元素,不要写成#{}
open: 代表语句的开始部分
close: 代表结束部分
item: 代表遍历集合的每个元素,生成的变量名
sperator: 代表分隔符