Mybatis之动态SQL语句查询（三）

一、创建数据库

1. user.table

CREATE TABLE user ( 
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` datetime default NULL COMMENT '生日',
  `sex` char(1) default NULL COMMENT '性别',
  `address` varchar(256) default NULL COMMENT '地址',
  PRIMARY KEY  (`id`)
) ;

二、编写实体类

1. user.class

package com.hzxy.domain;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

/**
 * 实现序列化接口
 */

public class User implements Serializable {

    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;


    private List<Account> accounts;

    public void setAccounts(List<Account> accounts) {
        this.accounts = accounts;
    }

    public List<Account> getAccounts() {
        return accounts;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", address='" + address + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                '}';
    }
}

2. QueryVo.java

package com.hzxy.domain;
import java.util.List;

public class QueryVo {

    private User user;

    private List<Integer> ids;

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

三、主配置文件

1. SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>  <!--头部约束-->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <!-- 配置properties resourse属性：用于指定配置文件的位置-->
    <properties resource="jdbcConfig.properties"></properties>

    <!-- 使用typeAliases配置别名，它只能配置domain中类的别名-->
    <typeAliases>
        <!-- typeAliases用于配置别名；type属性指定实体类全限定别名；alias属性指定别名，当指定了别名就不再区分大小写。-->
        <!--<typeAlias type="com.hzxy.domain.User" alias="user"></typeAlias>-->

        <!-- 用于指定别名的包，当指定之后，该包下的实体类都会注册别名，并且类名就是别名，不再区分大小写-->
        <package name="com.hzxy.domain"></package>
    </typeAliases>


    <!-- 配置环境 -->
    <environments default="mysql"> <!--默认环境-->
        <!-- 配置mysql的环境-->
        <environment id="mysql">
            <!-- 配置事务的类型 -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 配置连接池(数据源) -->
            <dataSource type="POOLED">
                <!-- 配置连接数据库的4个基本信息 -->
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 指定映射配置文件的位置，映射配置文件指的是每个dao独立的配置文件 -->
    <mappers>
        <!--<mapper resource="com/hzxy/dao/UserDao.xml"/>-->
        <!-- package标签是用于指定dao接口所在的包,指定之后不需要再写mapper\resourse\class-->
        <package name="com.hzxy.dao"></package>
    </mappers>
</configuration>

2. jdbcConfig.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybastis?serverTimezone=UTC
jdbc.username=root
jdbc.password=123456

四、业务层接口

实现方法
1.根据传入参数条件查询
2.根据queryvo中提供的id集合，查询用户信息

1. UserDao

package com.hzxy.dao;
import com.hzxy.domain.QueryVo;
import com.hzxy.domain.User;
import java.util.List;

/**
 * 用户的持久层接口
 */
public interface UserDao {


    /**
     * 根据传入参数条件查询
     * @param user 条件多种多样
     * @return
     */
    List<User> findUserByCondition(User user);

    /**
     * 根据queryvo中提供的id集合，查询用户信息
     * @param vo
     * @return
     */
    List<User> findUserInIds(QueryVo vo);
}

五、编写映射文件

UserDao.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="com.hzxy.dao.UserDao">   <!-- 注明哪个dao接口-->

    <!-- 抽取重复的sql语句-->
    <sql id="defaultUser">
        SELECT * FROM  user
    </sql>
    
    <!-- 根据条件查询 -->
    <!-- 方法一 -->
    <select id="findUserByCondition" resultType="user" parameterType="user">
        SELECT * FROM user where 1 = 1
        <if test="username != null">
         and username = #{username}
        </if>
        <if test="sex != null">
            and sex = #{sex}
        </if>
    </select>

	<!-- 方法二 -->
    <select id="findUserByCondition" resultType="user" parameterType="user">
        SELECT * FROM user
        <where>
            <if test="username != null">
                and username = #{username}
            </if>
            <if test="sex != null">
                and sex = #{sex}
            </if>
        </where>
    </select>

    
    <!-- 根据queryvo中的Id集合实现查询用户列表 -->
    <!-- 方法一 -->
    <select id="findUserInIds" resultType="user" parameterType="queryvo">
        <!-- <include refid="defaultUser"></include>-->
        <include refid="defaultUser"></include>  <!--调用抽取sql语句-->
        <where>
            <if test="ids != null and ids.size()>0">
                <foreach collection="ids" open="and id in (" close=")" item="uid" separator=",">
                    #{uid}
                </foreach>
            </if>
        </where>
    </select>
    
</mapper>

六、测试类

1. UserTest.class

package com.hzxy.text;

import com.hzxy.dao.UserDao;
import com.hzxy.domain.QueryVo;
import com.hzxy.domain.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.After;
import org.junit.Before;
import org.junit.Test;

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

/**
 * 测试mybatis的crud操作
 */

public class MybatisTest {
    private InputStream in ;
    private SqlSession sqlSession ;
    private UserDao userDao ;

    @Before //用于在测试方法执行前执行
    public void init() throws IOException {
        //使用类加载器读取类路径的配置文件，生成字节输入流，不使用绝对路径和相对路径
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory工厂,mybatis使用构建者模式
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //使用工厂生产SqlSession对象
        sqlSession = factory.openSession();
        //使用SqlSession创建Dao接口的代理对象
        userDao = sqlSession.getMapper(UserDao.class);
    }

    @After //用于在测试方法执行之后执行
    public void destroy() throws IOException {
        //提交事务
        sqlSession.commit();
        //释放资源
        sqlSession.close();
        in.close();
    }

   
    /**
     * if和where标签的使用
     */
    @Test
    public void testFindByCondition(){
        User u = new User();
        u.setUsername("老王");

        List<User> users = userDao.findUserByCondition(u);
        for (User user : users) {
            System.out.println(user);
        }
    }
    /**
     * foreach标签的使用
     */
    @Test
    public void testFindInIds(){
        QueryVo vo = new QueryVo();
        List<Integer> list = new ArrayList<Integer>();
        list.add(41);
        list.add(42);
        list.add(46);
        vo.setIds(list);

        List<User> users = userDao.findUserInIds(vo);
        for (User user : users) {
            System.out.println(user);
        }
    }
}

2. 执行结果

根据传入参数条件查询

根据queryvo中提供的id集合，查询用户信息

七、具体标签

1.if标签

<!-- 根据条件查询 -->
    <!-- 方法一 -->
    <select id="findUserByCondition" resultType="user" parameterType="user">
        SELECT * FROM user where 1 = 1
        <if test="username != null">
         and username = #{username}
        </if>
        <if test="sex != null">
            and sex = #{sex}
        </if>
    </select>

2.if和where标签

<!-- 根据条件查询 -->
    <!-- 方法二 -->
<select id="findUserByCondition" resultType="user" parameterType="user">
        SELECT * FROM user
        <where>
            <if test="username != null">
                and username = #{username}
            </if>
            <if test="sex != null">
                and sex = #{sex}
            </if>
        </where>
    </select>

3.if和foreach标签

<!-- 根据queryvo中的id集合实现查询用户列表-->   <!-- 由item决定-->
    <select id="findUserInIds" resultType="user" parameterType="queryvo">
        SELECT * FROM USER
        <where>
            <if test="ids != null and ids.size()>0">
                <foreach collection="ids" open="and id in ("close=")" item="id" separator=",">
                    #{id}
                </foreach>
          </if>
        </where>
    </select>