1. 输入参数之POJO包装类

1.1 内部嵌套有User的pojo

public class QueryVo implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    //
    private User user;
    
    List<Integer> idsList;
    
    Integer[] ids;
    
    
    public List<Integer> getIdsList() {
        return idsList;
    }
    public void setIdsList(List<Integer> idsList) {
        this.idsList = idsList;
    }
    public Integer[] getIds() {
        return ids;
    }
    public void setIds(Integer[] ids) {
        this.ids = ids;
    }
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
}

1.2 UserMapper的接口提供被动态代理的方法

public interface UserMapper {
    public List<User> findUserByQueryVo(QueryVo vo);
}

1.3 UserMapper.xml书写查询的sql语句

<!-- 传入参数类型是QueryVo， 返回的结果类型是User， sql语句中传入的user.username 使用了ognl语法-->
<!-- 根据用户名模糊查询 -->
    <select id="findUserByQueryVo" parameterType="QueryVo" resultType="pojo.User">
        select * from user where username like "%"#{user.username}"%"
    </select>

1.4 测试

@Test
    public void testMapperQueryVo() throws Exception {
        //加载核心配置文件
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        //SqlSEssion帮我生成一个实现类  （给接口）
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        QueryVo vo = new QueryVo();
        User user = new User();
        user.setUsername("张");
        vo.setUser(user);
        
        List<User> us = userMapper.findUserByQueryVo(vo);
        for (User u : us) {
            System.out.println(u);
            
        }
    }

2.输出参数之简单类型

2.1 准备User的pojo

public class User implements Serializable {
    private static final long serialVersionUID = 1L;
    private Integer id;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址
    
    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 getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", sex=" + sex
                + ", birthday=" + birthday + ", address=" + address + "]";
    }
}

2.2 UserMapper的接口提供被动态代理的方法

    //查询数据条数
    public Integer countUser();

2.3 UserMapper.xml书写查询的sql语句

    <select id="countUser" resultType="Integer">
        select count(1) from user
     </select>

2.4 测试

@Test
    public void testMapperQueryVoCOunt() throws Exception {
        //加载核心配置文件
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        //SqlSEssion帮我生成一个实现类  （给接口）
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        
        Integer i = userMapper.countUser();
        System.out.println(i);
    }

3. resultMap手动映射，解决表和pojo字段不同的情况

需求描述：有一张Order表，里面的一个字段是user_id和pojo中的字段userId不同，无法完成自动映射，使用resultMap 进行手动映射

在OrderMapper.xml中进行配置

<resultMap type="Order" id="order">
        <result column="user_id" property="userId"/>
    </resultMap>
    
    <select id="selectOrdersList" resultMap="order">
        SELECT id, user_id, number, createtime, note FROM orders 
    </select>

4. 动态sql的if和where标签

if标签是过滤掉比如sex=null || sex = ""的情况

where标签过滤掉如果有标签判断不成立之后，多余的and会导致sql错误的问题

<!--    根据性别和名字查询用户  where 可以去掉第一个前ANd   -->
     <select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
        select * from user 
        <where>
            <if test="sex != null and sex != ''">
                 and sex = #{sex} 
            </if>
            <if test="username != null and username != ''">
                 and username = #{username}
            </if>
        </where>
     </select>

5. 使用<include>进行sql重复代码提取

将select * from user这部分提取出来

    <sql id="selector">
        select * from user
    </sql>

<!--    根据性别和名字查询用户  where 可以去掉第一个前ANd   -->
     <select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
        <include refid="selector"/>
        <where>
            <if test="sex != null and sex != ''">
                 and sex = #{sex} 
            </if>
            <if test="username != null and username != ''">
                 and username = #{username}
            </if>
        </where>
     </select>

6. 动态Sql之Foreach1，根据多个id查询多个用户

6.1 包装类中的List是可以使用属性名称查找的

pojo中的属性名

public class QueryVo implements Serializable {
    List<Integer> idsList;
}

接口方法

  public List<User> selectUserByIds(QueryVo vo);

<!-- 多个ID (1,2,3)-->
     <select id="selectUserByIds" parameterType="QueryVo" resultType="User">
        select * from user 
        <where>
            <foreach collection="idList" item="id" separator="," open="id in (" close=")">
                #{id}
            </foreach>
        </where>
     </select>

测试

@Test
    public void testfindUserIDs() throws Exception {
        //加载核心配置文件
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<Integer> ids  = new ArrayList<>();
        ids.add(16);
        ids.add(22);
        ids.add(24);
        QueryVo vo = new QueryVo();
        vo.setIdsList(ids);
        
        List<User> users = userMapper.selectUserByIds(vo);

        for (User user2 : users) {
            System.out.println(user2);
        }
    }

6.2 非包装类，直接使用List和Array的时候

原理：

结论：如果传入的参数类型是List，那么collection="list"
如果传入的参数类型是Array，那么collection="array"
pojo

public class QueryVo implements Serializable {
    Integer[] ids;
}

接口方法

    public List<User> selectUserByIds(List<Integer> ids); 

<!-- 多个ID (1,2,3)-->
     <select id="selectUserByIds" parameterType="QueryVo" resultType="User">
        <include refid="selector"/>
        <where>
            <foreach collection="list" item="id" separator="," open="id in (" close=")">
                #{id}
            </foreach>
        </where>
     </select>

测试

//多个ID
    @Test
    public void testfindUserIDs() throws Exception {
        //加载核心配置文件
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<Integer> ids  = new ArrayList<>();
        ids.add(16);
        ids.add(22);
        ids.add(24);

// 使用数组的情况
/*      Integer[] ids = new Integer[3];
        ids[0] = 16;
        ids[2] = 22;
        ids[1] = 24;*/

        List<User> users = userMapper.selectUserByIds(ids);
        for (User user2 : users) {
            System.out.println(user2);
        }
    }

7. mybatis的一对一关联查询

查询订单对应的用户，一张订单只会有一个用户
注意：当多表查询的时候，所有想要进行查询的字段都需要进行手动映射（质疑）

1. pojo

public class Orders  implements Serializable{

    private static final long serialVersionUID = 1L;

    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;
    
    //附加对象  用户对象
    private User user;
}

public class User implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private Integer id;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址
}

2. OrderMapper.xml

     <!-- 
        //一对一关联 查询  以订单为中心 关联用户
     -->
     <resultMap type="Order" id="order">
        <result column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <!-- 一对一 -->
        <association property="user" javaType="User">
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
        </association>
     </resultMap>
     <select id="selectOrdersLeftJoin" resultMap="order">
        SELECT 
        o.id,
        o.user_id, 
        o.number,
        o.createtime,
        u.username 
        FROM orders o 
        left join user u 
        on o.user_id = u.id
     </select>

3. 测试

@Test
    public void testOrdersLeftJoin() throws Exception {
        //加载核心配置文件
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        //SqlSEssion帮我生成一个实现类  （给接口）
        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> selectOrderList = orderMapper.selectOrdersLeftJoin();
        
        for (Orders order : selectOrdersList) {
            System.out.println(orders);
        }
    }

8. 一对多，查询每一个用户的订单数

<!-- 
     
        //一对多关联
    public List<User> selectUserList(); -->
    <resultMap type="User" id="user">
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <!-- 一对多 -->
        <collection property="ordersList" ofType="Orders">
            <id column="id" property="id"/>
            <result column="number" property="number"/>
        </collection>
    </resultMap>
    <select id="selectUserList" resultMap="user">
        SELECT 
        o.id,
        o.user_id, 
        o.number,
        o.createtime,
        u.username 
        FROM user u
        left join orders o 
        on o.user_id = u.id
    </select>

@Test
    public void testUserList() throws Exception {
        //加载核心配置文件
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        //SqlSEssion帮我生成一个实现类  （给接口）
        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
        List<User> users = orderMapper.selectUserList();
        for (User user : users) {
            System.out.println(user);
        }
        
    }

9. Spring整合Mybatis之Mapper动态代理开发

1. 导包，包括整合包

2. 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>
    <!-- 设置别名 -->
    <typeAliases>
        <!-- 2. 指定扫描包，会把包内所有的类都设置别名，别名的名称就是类名，大小写不敏感 -->
        <package name="pojo" />
    </typeAliases>

    <mappers>
        <package name="mapper"/>
    </mappers>

</configuration>

3. applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
    http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd">


    <context:property-placeholder location="classpath:db.properties"/>
    
    <!-- 数据库连接池 -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="maxActive" value="10" />
        <property name="maxIdle" value="5" />
    </bean>
    
    <!-- Mybatis的工厂 -->
    <bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <!-- 核心配置文件的位置 -->
        <property name="configLocation" value="classpath:sqlMapConfig.xml"/>
    </bean>
    
    <!-- Mapper动态代理开发 -->
    <bean id="userMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="sqlSessionFactory" ref="sqlSessionFactoryBean"/>
        <property name="mapperInterface" value="mapper.UserMapper"/>
    </bean>
</beans>

4. mapper接口 UserMapper.java

public interface UserMapper {
    
    public User findUserById(Integer id);

}

5. UserMapper.xml

<mapper namespace="mapper.UserMapper">


    <!-- 通过ID查询一个用户 -->
    <select id="findUserById" parameterType="Integer" resultType="User">
        select * from user where id = #{v}
    </select>
</mapper>

6. 测试

public class JunitTest {

    
    @Test
    public void testMapper() throws Exception {
        ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserMapper mapper = (UserMapper) ac.getBean("userMapper");
        User user = mapper.findUserById(10);
        System.out.println(user);
    }
}

10. Spring整合Mybatis之Mapper动态代理扫描开发

1. applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
    http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd">


    <context:property-placeholder location="classpath:db.properties"/>
    
    <!-- 数据库连接池 -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="maxActive" value="10" />
        <property name="maxIdle" value="5" />
    </bean>
    
    <!-- Mybatis的工厂 -->
    <bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <!-- 核心配置文件的位置 -->
        <property name="configLocation" value="classpath:sqlMapConfig.xml"/>
    </bean>
    
    
    <!-- Mapper动态代理开发   扫描 -->
    <!-- 不需要再手动注入工厂，spring会自动获取上面配置好的工厂-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!-- 基本包 -->
        <property name="basePackage" value="mapper"/>
    </bean>
</beans>

测试

public class JunitTest {

    @Test
    public void testMapper() throws Exception {
        ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserMapper mapper = ac.getBean(UserMapper.class);
        User user = mapper.findUserById(10);
        System.out.println(user);
    }
}

11. 逆向工程生成pojo和mapper