Mybatis的基本使用

已于 2023-07-18 15:57:01 修改
阅读量107 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： mybatis
于 2023-07-01 17:07:43 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/csdnidofkdy2138/article/details/131487244

mybatis官网

mybatis – MyBatis 3 | 简介

JDBC缺点

1、硬编码注册驱动、获取连接和写SQL  ->mybatis配置文件

2、手动设置参数和封装结果比较繁琐      ->mybatis自动完成

快速入门 

导入依赖

org.mybatis的mybatis和msql的mysql-connector-java

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.13</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.8</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
    </dependencies>
项目结构

 

数据库表结构 
 com.kdy.domain中创建User类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String name;
    private String password;
    private String email;
    private Date birthday;
    private Integer infoId;
    private String englishTeacher;
}
resouce下创建jdbc.properties文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc
jdbc.username=root
jdbc.password=root123
  mybatis配置文件

在resource下创建mybatis-config.xml，粘贴入以下内容

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--通过properties标签机制外部的properties文件-->
    <properties resource="jdbc.properties"></properties>
    <!--起别名：为权限定名的类起一个比较短的别名-->
    <typeAliases>
        <package name="com.kdy.domain"/><!--扫描的为它的类名且不区分大小写-->
<!--        <typeAlias type="com.kdy.domain.User" alias="user"></typeAlias>-->
        <!--mybatis已经将String->string、Long->long、Integer->int、Double->double、Boolean->boolean转换设置好了别名-->
    </typeAliases>
    <environments default="development">
        <environment id="development"><!--环境development、test，可以配置多个数据库连接的环境信息，将来通过default属性切换-->
            <transactionManager type="JDBC"/><!--事务管理器,spring可接管，这里直接使用的JDBC的提交和回滚。依赖从数据源得到链接来管理事务作用域-->
            <dataSource type="POOLED">
                <!--数据源类型type有三种：
                UNPOOLED:这个数据源的实现只是每次被请求时打开和关闭连接。
                POOLED:这种数据源的实现利用“池” 的概念将JDBC连接对象组织起来。
                JNDI:这个数据源的实现是为了能在如EJB或应用服务器这类容器中使用，容器可以集中或在外部配置数据源,然后放置一个JNDI上下文的引用。-->
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <!--如果mysqlurl中有&符号需要进行转义为&amp;，如useSSL=false&amp;useServerPrepStmts=true
                127.0.0.1:3306本机默认端口可省略不写，直接为///-->
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--mappers加载映射，加载方式如下：
    使用相对于类路径的资源引用,例如:<mapper resource="org/mybatis/builder/AuthorMapper.xml/>
    使用完全限定资源定位符(URL) ，例如: <mapper url= "file:///var/mappers/ AuthorMapper.xml"/>
    使用映射器接口实现类的完全限定类名，例如: <mapper class=" org.mybatis.builder.AuthorMapper"/>
    将包内的映射器接口实现全部注册为映射器，例如: <package name="org.mybatis.builder"/>-->
    <mappers>
<!--        <package name="com.kdy.mapper"/>-->
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>

关于Mybatis配置文件的很多标签含义，见上配置文件中的注解介绍。 

sql映射文件

在resource中创建sql映射文件，命名规范：表名Mapper.xml如UserMapper.xml。粘贴入以下内容：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="test">
    <resultMap id="userResultMap" type="user">
        <result column="english_teacher" property="englishTeacher"/>
        <!--其他需要映射的字段接着上行格式往下写即可-->
    </resultMap>
    <select id="selectUsers" resultMap="userResultMap">
        select * from sys_user;
    </select>
    <insert id="save" parameterType="user">
        insert into sys_user(name,password,email,birthday,infoId,english_teacher)
        value(#{name},#{password},#{email},#{birthday},#{infoId},#{englishTeacher})
    </insert>
    <update id="update" parameterType="com.kdy.domain.User">
        update sys_user set name = #{name},password = #{password} where id = #{id}
    </update>
    <delete id="delete" parameterType="java.lang.Integer">
        delete from sys_user where id = #{id}
    </delete>
</mapper>
test目录下创建测试类写测试用例
public class UserTest {

    @Test
    public void selectAll() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3.执行sql
        List<User> userList = sqlSession.selectList("test.selectUsers");  //还是硬编码
        System.out.println(userList);
        //4.释放资源
        sqlSession.close();
    }
    @Test
    public void save() throws IOException, ParseException {
        String dateStr = "2021-02-22";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date birthday = sdf.parse(dateStr);
        User user = new User(null, "zhangsanMybatis", "hello666", "email666", birthday, 6, "english666");
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3.执行sql
        int count = sqlSession.insert("test.save", user);
        System.out.println(count);
        sqlSession.commit();
        //4.释放资源
        sqlSession.close();
    }
    @Test
    public void update() throws IOException {
        User user = new User(53, "zhangsanUpdate", "update666" ,null,null,null,null);
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3.执行sql
        int count = sqlSession.update("test.update", user);
        System.out.println(count);
        sqlSession.commit();
        //4.释放资源
        sqlSession.close();
    }
    @Test
    public void delete() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3.执行sql
        int count = sqlSession.delete("test.delete", 53);
        System.out.println(count);
        sqlSession.commit();
        //4.释放资源
        sqlSession.close();
    }
}
运行测试用例即可。 
注：Mybatis传统的Dao层开发可用上测试用例中的代码。

 sqlSessionFactory和SqlSession对象

注：SqlSession sqlSession = sqlSessionFactory.openSession();中

sqlSessionFactory.openSession();不带参数或参数为false，需手动提交事务。若带参数true，无需手动提交事务即可自动提交。

sqlSession执行语句的方法主要有：

<T> T selectOne(String statement,Object parameter)

<E> List<E> selectList(String statement,Object parameter)

int insert(String statement,Object parameter)

int update(String statement,Object parameter)

int delete(String statement,Object parameter)

sqlSession操作事务的方法主要有：

void commit();

void rollback();

Mapper代理开发

1.定义与sql映射文件同名的mapper接口，并和sql映射文件放在同一个目录下。

2.设置sql映射文件的namespace为mapper接口的全限定名。

3.mapper接口中定义方法，方法名为sql映射中的sql的id，并保持参数和返回值类型一致。

4.通过sqlSession的getMapper获取mapper接口的代理对象，并调用对应方法完成sql执行。

 目录结构

mybatis-config.xml中加载sql映射文件的地址修改
    <mappers>
        <mapper resource="com/kdy/mapper/userMapper.xml"/>
    </mappers>
 sql映射文件修改
<mapper namespace="com.kdy.mapper.userMapper">
    <select id="selectUsers" resultType="com.kdy.pojo.User">
        select * from users;
    </select>
</mapper>
userMapper接口
public interface userMapper {
    List<User> selectUsers();
}
 测试demo
    @Test
    public void testMybatis() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3.执行sql
        userMapper mapper = sqlSession.getMapper(userMapper.class);
        List<User> userList = mapper.selectUsers();

        System.out.println(userList);

        //4.释放资源
        sqlSession.close();
    }

包扫描

如果mapper接口默和sql映射文件名相同，且在同一目录下，在mybatis配置文件中可使用包扫描的方式简化sql映射文件的加载

mubatis-config.xml修改
    <mappers>
        <package name="com.kdy.mapper"/>
    </mappers>

 类型别名

简化sql映射文件中入参类型和返回值类型全限定名的写法，现可直接写类名且不区分大小写

<typeAliases>
        <package name="com.kdy.pojo"/>
    </typeAliases>

 注意：配置文件标签先后顺序需要遵循

mybatisX插件

在setting的plugins中安装，安装后通过点击图标即可完成接口和映射文件方法间的跳转

sql片段 

可在sql映射文件中定义sql片段

<mapper namespace="com.kdy.mapper.userMapper">
    <sql id="fields">
        id,name,password,email,birthday,infoId
    </sql>
    <select id="selectUsers" resultType="com.kdy.pojo.User">
        select <include refid="fields"/> from users;
    </select>
</mapper>

resultMap

可在sql映射文件中，定义resultMap解决数据表字段下划线命名和pojo类驼峰命名如何匹对的问题。

<mapper namespace="com.kdy.mapper.userMapper">
    <resultMap id="userResultMap" type="user">
        <result column="english_teacher" property="englishTeacher"/>
        <!--其他需要映射的字段接着上行格式往下写即可-->
    </resultMap>
    <select id="selectUsers" resultMap="userResultMap">
        select * from users;
    </select>
</mapper>

如果mapper接口中使用注解，没有用xml的statement。对应的mapper接口中要用@ResultMap注解声明，如下所示： 

    @Select("select * from users")
    @ResultMap("userResultMap")
    List<User> selectAll();

 parameterType可以省略

参数占位符

#{}类似?占位，传递参数使用。${}拼接sql存在sql注入问题，要对表名或列名进行动态设置只能用${}。        

sql语句传参三种方式 

散装参数传参、实体类封装参数传参、map集合传参 

demo如下：

mapper接口
public interface userMapper {
    //1.散装参数传参
    List<User> selectUsers(@Param("name") String name,@Param("password")String password,@Param("englishTeacher")String englishTeacher);
    //2.实体类封装参数传参
    List<User> selectUsersByPojo(User user);
    //3.map集合传参
    List<User> selectUsersByMap(Map map);
}
 sql映射
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kdy.mapper.userMapper">
    <resultMap id="userResultMap" type="user">
        <result column="english_teacher" property="englishTeacher"/>
        <!--其他需要映射的字段接着上行格式往下写即可-->
    </resultMap>
    <!--1.散装参数传参-->
    <select id="selectUsers"  resultMap="userResultMap">
        select * from users where name = #{name} and password = #{password} and english_teacher like #{englishTeacher};
    </select>
    <!--2.实体类封装参数传参-->
    <select id="selectUsersByPojo" parameterType="user"  resultMap="userResultMap">
        <!--如果接口入参传进来的是一个pojo对象，#{}中的内容要和对象的属性名一致-->
        select * from users where name = #{name} and password = #{password} and english_teacher like #{englishTeacher};
    </select>
    <!--3.map集合传参-->
    <select id="selectUsersByMap" parameterType="map"  resultMap="userResultMap">
        <!--如果接口入参传进来的是一个map，#{}中的内容要和map的key一致-->
        <!--如果map.put("user",user)放了一个对象，在这里需要使用#{user.name}之类取出属性-->
        select * from users where name = #{name} and password = #{password} and english_teacher like #{englishTeacher};
    </select>
</mapper>
 测试文件
    @Test
    public void testMybatis() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3.执行sql
        userMapper mapper = sqlSession.getMapper(userMapper.class);
        String englishTeacher = "all";
        englishTeacher = "%"+englishTeacher+"%";//对传入的数据模糊处理
        //1.散装参数传参
        List<User> userList = mapper.selectUsers("zhangsan","hello666",englishTeacher);
        System.out.println(userList);
        //2.实体类封装参数传参
        User user = new User();
        user.setName("zhangsan");
        user.setPassword("hello666");
        user.setEnglishTeacher(englishTeacher);
        List<User> userList2 = mapper.selectUsersByPojo(user);
        System.out.println(userList2);
        //3.map集合传参
        Map map = new HashMap();
        map.put("name","zhangsan");
        map.put("password","hello666");
        map.put("englishTeacher",englishTeacher);
        List<User> userList3 = mapper.selectUsersByMap(map);
        System.out.println(userList3);


        //4.释放资源
        sqlSession.close();
    }

 多条件动态查询

mybatis – MyBatis 3 | 动态 SQL官网介绍 : mybatis – MyBatis 3 | 动态 SQL

if

choose(when,otherwise)

trim(where,set)

foreach

if

int类型需判断是否为null，String类型需要判断是否为null或""

    <select id="selectUsers"  resultMap="userResultMap">
    <!--有条件根据条件过滤或多条件过滤，没有条件就查询全部-->
        select * from users where
        1=1
        <if test="name != null and name != ''">
            and name = #{name}
        </if>
        <if test="password != null and password != ''">
            and password = #{password}
        </if>
        <if test="englishTeacher != null and englishTeacher != '' ">
            and english_teacher like #{englishTeacher}
        </if>
    </select>
where
    <select id="selectUsersByPojo" parameterType="user"  resultMap="userResultMap">
    <!--有条件根据条件过滤或多条件过滤，没有条件就查询全部-->
        select * from users
        <where>
            <if test="name != null and name != ''">
                and name = #{name}
            </if>
            <if test="password != null and password != ''">
                and password = #{password}
            </if>
            <if test="englishTeacher != null and englishTeacher != '' ">
                and english_teacher like #{englishTeacher}
            </if>
        </where>
    </select>
foreach

public interface UserMapper {
    public List<User> selectByIds(@Param("ids") Integer []ids);
}
    <select id="selectByIds" parameterType="list" resultMap="userResultMap">
        select * from sys_user
        <where>
            <foreach collection="ids" open="id in(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>
    @Test
    public void selectByIds() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Integer [] ids =  {1,2,3,15,17,19};
        List<User> users = mapper.selectByIds(ids);
        System.out.println(users);
        //4.释放资源
        sqlSession.close();
    }

 单条件动态查询

choose(when,otherwise)

类似Java中的switch(值){case:值1 java语句;break;case 值2 java语句 break; case 值3 java语句;break;...default: java语句;}

    <select id="selectUsersByMap" parameterType="map"  resultMap="userResultMap">
        <!--条件不为空就根据多条条件过滤，条件按都为空就查询全部，类似where-if-->
        select * from users where
        <choose>
            <when test="name != null and name != ''">
                name = #{name}
            </when>
            <when test="password != null and password != ''">
                password = #{password}
            </when>
            <when test="englishTeacher != null and englishTeacher != '' ">
                english_teacher like #{englishTeacher}
            </when>
            <otherwise>
                1=1
            </otherwise>
        </choose>
    </select>
    <!--不写otherwise的1=1也可用<where>代替where，如下-->
    <select id="selectUsersByMap" parameterType="map" resultMap="userResultMap">
        <!--条件不为空就根据多条条件过滤，条件按都为空就查询全部，类似where-if-->
        select * from users
        <where>
            <choose>
                <when test="name != null and name != ''">
                    name = #{name}
                </when>
                <when test="password != null and password != ''">
                    password = #{password}
                </when>
                <when test="englishTeacher != null and englishTeacher != '' ">
                    english_teacher like #{englishTeacher}
                </when>
            </choose>
        </where>
    </select>

 Mybatis事务

openSession()：默认开启事务，进行增删改操作后需要使用sqlSession.commit();手动提交事务。

openSession(true)：可以设置为自动提交事务（关闭事务）

新增操作

mapper接口
public interface userMapper {
    int add(User user);
}
sql映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kdy.mapper.userMapper">
    <insert id="add">
        insert into users(name,password,email,birthday,infoId,english_teacher)
        values(#{name},#{password},#{email},#{birthday},#{infoId},#{englishTeacher})
    </insert>
</mapper>
测试demo
    @Test
    public void testMybatis() throws IOException, ParseException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3.执行sql
        userMapper mapper = sqlSession.getMapper(userMapper.class);
        Date birthday = new SimpleDateFormat("yyyy-MM-dd").parse("2001-02-22");
        //mybatis的java.util.Date类型无需先转为java.sql.Date可直接使用。
        User user = new User(null,"sunwukong","mima123456","email666",birthday,6,"hallo");
        int count = mapper.add(user);
        sqlSession.commit();   //默认openSession不加参数true的话为开启事务，需手动提交commit。
        System.out.println(count);
        //4.释放资源
        sqlSession.close();
    }

    /*或者openSession时加上参数true：
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        这样就会直接提交事务，不用写sqlSession.commit();了  */

主键返回

通过在sql映射中加上useGeneratedKeys为true和keyProperty="具体的主键字段名"即可。

<insert id="add" useGeneratedKeys="true" keyProperty="id">
        insert into users(name,password,email,birthday,infoId,english_teacher)
        values(#{name},#{password},#{email},#{birthday},#{infoId},#{englishTeacher})
    </insert>

然后回到testDemo中的user对象的id就有值了

    @Test
    public void testMybatis() throws IOException, ParseException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(true);//加上参数true，自动提交事务
        //3.执行sql
        userMapper mapper = sqlSession.getMapper(userMapper.class);
        Date birthday = new SimpleDateFormat("yyyy-MM-dd").parse("2001-02-22");
        //mybatis的java.util.Date类型无需先转为java.sql.Date可直接使用。
        User user = new User(null,"sunwukong","mima123456","email666",birthday,6,"hallo");
        int count = mapper.add(user);
        System.out.println("影响的行数"+count);
        System.out.println(user);//经过主键返回后，该user对象的id就有值了
        //4.释放资源
        sqlSession.close();
    }

修改操作

mapper接口
public interface userMapper {
    int update(User user);
}
sql映射
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kdy.mapper.userMapper">
    <update id="update">
        update users set name = #{name},password=#{password},email=#{email},birthday=#{birthday},
        infoId = #{infoId},english_teacher =#{englishTeacher} where id = #{id}
    </update>
</mapper>
测试文件
    @Test
    public void testMybatis() throws IOException, ParseException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(true);//加上参数true，自动提交事务
        //3.执行sql
        userMapper mapper = sqlSession.getMapper(userMapper.class);
        Date birthday = new SimpleDateFormat("yyyy-MM-dd").parse("2001-02-22");
        User user = new User(13,"sunwukong6","mima123456","email666",birthday,6,"hallo");
        int count = mapper.update(user);
        System.out.println("影响的行数"+count);
        //4.释放资源
        sqlSession.close();
    }

修改动态字段

需要修改sql映射文件的sql为
    <update id="update">
        update users
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="password != null and password != ''">
                password=#{password},
            </if>
            <if test="email != null and email != ''">
                email=#{email},
            </if>
            <if test="birthday != null">
                birthday=#{birthday},
            </if>
            <if test="infoId != null">
                infoId = #{infoId},
            </if>
            <if test="englishTeacher != null and englishTeacher != ''">
                english_teacher =#{englishTeacher}
            </if>
        </set>
        where id = #{id}
    </update>

删除操作

mapper接口
public interface userMapper {
    int deleteById(int id);
}
sql映射
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kdy.mapper.userMapper">
    <delete id="deleteById">
        delete from users where id = #{id};
    </delete>
</mapper>
测试demo
    @Test
    public void testMybatis() throws IOException, ParseException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(true);//加上参数true，自动提交事务
        //3.执行sql
        userMapper mapper = sqlSession.getMapper(userMapper.class);
        int count = mapper.deleteById(13);
        System.out.println("影响的行数"+count);
        //4.释放资源
        sqlSession.close();
    }

批量删除

mapper接口
public interface userMapper {
    int deleteByIdes(@Param("ids") int [] ids);
}
sql映射
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kdy.mapper.userMapper">
    <delete id="deleteByIdes">
        delete from users where id in 
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>
</mapper>
测试demo
    @Test
    public void testMybatis() throws IOException, ParseException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(true);//加上参数true，自动提交事务
        //3.执行sql
        userMapper mapper = sqlSession.getMapper(userMapper.class);
        int [] ids =new int[2];
        ids[0]=11;
        ids[1]=12;
        int count = mapper.deleteByIdes(ids);
        System.out.println("影响的行数"+count);
        //4.释放资源
        sqlSession.close();
    }

Mybatis配置文件深入

typeHandlers标签--类型处理器

无论是MyBatis在预处理语句(PreparedStatement) 中设置一个参数时, 还是从结果集中取出一一个值时，都会用类型处理器将获取的值以合适的方式转换成Java类型。如：

数据库BOOLEAN转java.lang.Boolean或boolean的BooleanTypeHandler

数据库MUNERIC或BYTE转java.lang.Byte或byte'的ByteTypeHandler

数据库MUNERIC或SHORT INTEGER转java.lang.Short或short的ShortTypeHandler

数据库MUNERIC或INTEGER转java.lang.Integer或int的IntegerTypeHandler

数据库MUNERIC或LONG INTEGER转java.lang.Long或long的LongTypeHandler

mybatis提供了很多的转换器，如果mybatis中转换器不满足要求或没有，我们就可以自定义类型转换器：

你可以重写类型处理器或创建你自己的类型处理器来处理不支持的或非标准的类型。具体做法为:实现org.apache.ibatis.type.TypeHandler接口，或继承一个很便利的类org.apache.ibatis.type .BaseTypeHandler, 然后可以选择性地将它映射到一个JDBC类型。

 快速入门
需求

Java中的Date类型的数据， 将之存入数据库的时候存成一个1970年至今的毫秒数，取出时转为java的Date。

创建mavenWeb项目
pom文件引入
    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.13</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.8</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
    </dependencies>
数据库数据表
 com.kdy.domain
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String name;
    private String password;
    private Date birthday;//java.util下的Date
}
resource下jdbc.properties文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc
jdbc.username=root
jdbc.password=root123
resource下log4j.properties文件 
### 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=c:/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=info, stdout
com.kdy.mapper下UserMapper接口
public interface UserMapper {
    public void save(User user);
    public User findById(@Param("id")int id);
}
resource下com.kdy.mapper下UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kdy.mapper.UserMapper">
    <insert id="save" parameterType="user">
        insert into user(name,password,birthday)
        value(#{name},#{password},#{birthday})
    </insert>
    <select id="findById" parameterType="int" resultType="user">
        select * from user where id = #{id}
    </select>
</mapper>
com.kdy.handler下创建日期转换器类，采用的继承BaseTypeHandler类的方式
public class DateTypeHandler extends BaseTypeHandler<Date> {
    /*将Java类 型转为 数据库所需要的类型*/
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
        long time = parameter.getTime();
        ps.setLong(i,time);
    }
    /*将数据库中类型 转换成 java类型
    * String参数 要转换的字段名称
    * ResultSet 查询出的结果集
    * */
    @Override
    public Date getNullableResult(ResultSet rs, String columnName) throws SQLException {
        //获取结果集中需要的数据(long) 转换成Date类型 返回
        long aLong = rs.getLong(columnName);
        Date date = new Date(aLong);
        return date;
    }
    /*将数据库中类型 转换成 java类型 */
    @Override
    public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        long aLong = rs.getLong(columnIndex);
        Date date = new Date(aLong);
        return date;
    }
    /*将数据库中的类型转换成Java类型*/
    @Override
    public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        long aLong = cs.getLong(columnIndex);
        Date date = new Date(aLong);
        return date;
    }
注册类型处理器：
resource下mybatis-config.xml中配置别名、注册类型处理器、数据源环境、mappers加载映射
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--通过properties标签机制外部的properties文件-->
    <properties resource="jdbc.properties"></properties>
    <!--起别名：为权限定名的类起一个比较短的别名-->
    <typeAliases>
        <package name="com.kdy.domain"/><!--扫描的为它的类名且不区分大小写-->
<!--        <typeAlias type="com.kdy.domain.User" alias="user"></typeAlias>-->
        <!--mybatis已经将String->string、Long->long、Integer->int、Double->double、Boolean->boolean转换设置好了别名-->
    </typeAliases>
    <!--注册类型处理器-->
    <typeHandlers>
        <typeHandler handler="com.kdy.handler.DateTypeHandler"></typeHandler>
    </typeHandlers>
    <environments default="development">
        <environment id="development"><!--环境development、test，可以配置多个数据库连接的环境信息，将来通过default属性切换-->
            <transactionManager type="JDBC"/><!--事务管理器,spring可接管，这里直接使用的JDBC的提交和回滚。依赖从数据源得到链接来管理事务作用域-->
            <dataSource type="POOLED">
                <!--数据源类型type有三种：
                UNPOOLED:这个数据源的实现只是每次被请求时打开和关闭连接。
                POOLED:这种数据源的实现利用“池” 的概念将JDBC连接对象组织起来。
                JNDI:这个数据源的实现是为了能在如EJB或应用服务器这类容器中使用，容器可以集中或在外部配置数据源,然后放置一个JNDI上下文的引用。-->
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <!--如果mysqlurl中有&符号需要进行转义为&amp;，如useSSL=false&amp;useServerPrepStmts=true
                127.0.0.1:3306本机默认端口可省略不写，直接为///-->
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--mappers加载映射，加载方式如下：
    使用相对于类路径的资源引用,例如:<mapper resource="org/mybatis/builder/AuthorMapper.xml/>
    使用完全限定资源定位符(URL) ，例如: <mapper url= "file:///var/mappers/ AuthorMapper.xml"/>
    使用映射器接口实现类的完全限定类名，例如: <mapper class=" org.mybatis.builder.AuthorMapper"/>
    将包内的映射器接口实现全部注册为映射器，例如: <package name="org.mybatis.builder"/>-->
    <mappers>
        <package name="com.kdy.mapper"/>
<!--        <mapper resource="UserMapper.xml"/>-->
    </mappers>
</configuration>
test目录下，com.kdy.test中创建测试用例，测试存数据和取数据的birthday字段的类型转换
public class UserTest {
    @Test
    public void test1() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User(null, "zhangsan", "hello666", new Date());
        //3.执行sql
        mapper.save(user);
        sqlSession.commit();
        //4.释放资源
        sqlSession.close();
    }

    @Test
    public void test2() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //3.执行sql
        User user = mapper.findById(5);
        System.out.println(user);
        //4.释放资源
        sqlSession.close();
    }
}

plugins标签--PageHelper分页助手

将分页的复杂操作进行封装，使用简单的方式就可获得分页的相关数据。

快速入门
还是上面的typeHandlers的项目案例
pom文件除了mybatis、mysql、log4j、junit、lombok还要引入依赖pagehelper、jsqlparser
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>4.0</version>
        </dependency>
<!--        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>3.7.5</version>
        </dependency>
        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>0.9.1</version>
        </dependency>-->

分别准备了两套版本的，如上代码中的依赖。

com.kdy.mapper中UserMapper中加上findAll方法
public interface UserMapper {
    public void save(User user);
    public User findById(@Param("id")int id);
    List<User> findAll();
}
其resource下的com.kdy.mapper中UserMapper.xml加上statement
    <select id="findAll" resultType="user">
        select * from user
    </select>
test目录下加上测试用例，先写不加分页助手代码的测试用例
    @Test
    public void test3() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //3.执行sql
        List<User> userList = mapper.findAll();
        for (User user:userList) {
            System.out.println(user);
        }
        //4.释放资源
        sqlSession.close();
    }

结果输出user表中所有的数据

在resource下mybatis-config.xml中配置分页助手的<plugin>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--通过properties标签机制外部的properties文件-->
    <properties resource="jdbc.properties"></properties>
    <!--起别名：为权限定名的类起一个比较短的别名-->
    <typeAliases>
        <package name="com.kdy.domain"/><!--扫描的为它的类名且不区分大小写-->
        <!--        <typeAlias type="com.kdy.domain.User" alias="user"></typeAlias>-->
        <!--mybatis已经将String->string、Long->long、Integer->int、Double->double、Boolean->boolean转换设置好了别名-->
    </typeAliases>
    <!--注册类型处理器-->
    <typeHandlers>
        <typeHandler handler="com.kdy.handler.DateTypeHandler"></typeHandler>
    </typeHandlers>
    <!--配置分页助手插件-->
    <plugins>
        <!--如果pom文件pagehelper版本3.7.5和jsqlparser版本0.9.1用下面这套属性注入-->
<!--        <plugin interceptor="com.github.pagehelper.PageHelper">
            <property name="dialect" value="mysql"></property>
        </plugin>-->
        <!--如果pom文件pagehelper版本5.2.0和jsqlparser版本4.0用下面这套属性注入-->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <property name="helperDialect" value="mysql"/>
            <property name="reasonable" value="true"/>
            <property name="supportMethodsArguments" value="true"/>
            <property name="params" value="count=countSql"/>
        </plugin>
    </plugins>
    <!--数据源环境-->
    <environments default="development">
        <environment id="development"><!--环境development、test，可以配置多个数据库连接的环境信息，将来通过default属性切换-->
            <transactionManager type="JDBC"/><!--事务管理器,spring可接管，这里直接使用的JDBC的提交和回滚。依赖从数据源得到链接来管理事务作用域-->
            <dataSource type="POOLED">
                <!--数据源类型type有三种：
                UNPOOLED:这个数据源的实现只是每次被请求时打开和关闭连接。
                POOLED:这种数据源的实现利用“池” 的概念将JDBC连接对象组织起来。
                JNDI:这个数据源的实现是为了能在如EJB或应用服务器这类容器中使用，容器可以集中或在外部配置数据源,然后放置一个JNDI上下文的引用。-->
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <!--如果mysqlurl中有&符号需要进行转义为&amp;，如useSSL=false&amp;useServerPrepStmts=true
                127.0.0.1:3306本机默认端口可省略不写，直接为///-->
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--mappers加载映射，加载方式如下：
    使用相对于类路径的资源引用,例如:<mapper resource="org/mybatis/builder/AuthorMapper.xml/>
    使用完全限定资源定位符(URL) ，例如: <mapper url= "file:///var/mappers/ AuthorMapper.xml"/>
    使用映射器接口实现类的完全限定类名，例如: <mapper class=" org.mybatis.builder.AuthorMapper"/>
    将包内的映射器接口实现全部注册为映射器，例如: <package name="org.mybatis.builder"/>-->
    <mappers>
        <package name="com.kdy.mapper"/>
        <!--        <mapper resource="UserMapper.xml"/>-->
    </mappers>
</configuration>
测试用例中加上分页助手的代码
    @Test
    public void test3() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //分页助手  当前页数，煤业显示的数据量
        PageHelper.startPage(1,3);
        //3.执行sql
        List<User> userList = mapper.findAll();
        for (User user:userList) {
            System.out.println(user);
        }
        //获得与分页相关的参数
        PageInfo<User> pageInfo = new PageInfo<>(userList);
        System.out.println("当前页"+pageInfo.getPageNum());
        System.out.println("每页展示的条数"+pageInfo.getPageSize());
        System.out.println("总条数"+pageInfo.getTotal());
        System.out.println("总页数"+pageInfo.getPages());
        System.out.println("上一页"+pageInfo.getPrePage());
        System.out.println("下一页"+pageInfo.getNextPage());
        System.out.println("是否是第一个"+pageInfo.isIsFirstPage());
        System.out.println("是否是最后一个"+pageInfo.isIsLastPage());
        System.out.println("是否有上一页"+pageInfo.isHasPreviousPage());
        System.out.println("是否有下一页"+pageInfo.isHasNextPage());
/*        pageInfo.setStartRow(1);
        pageInfo.setPageNum(5);     还有一些set方法*/
        //4.释放资源
        sqlSession.close();
    }

Mybatis多表查询

建表原则

一对一，主键一致或外键关系。一对多，多的那一方要有一个外键相关系。多对多，需要一张中间表维护双方的主键

一对一配置：使用<resultMap>做配置
一对多配置：使用<resultMap> +<collection>做配置

多对多配置：使用<resultMap> +<collection>做配置 

一对一查询

模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户。

需求

查询一个订单，与此同时查询出该订单所属的用户。

创建mavenWeb项目
pom文件引入依赖
    <dependencies>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>4.0</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.13</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.8</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
    </dependencies>

数据库表结构

com.kdy.domain
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String name;
    private String password;
    private Date birthday;//java.util下的Date
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Order {
    private Integer id;
    private Date orderTime;
    private double total;
    //当前订单隶属的用户
    private User user;
}
com.kdy.handler包中创建DateTypeHandler
public class DateTypeHandler extends BaseTypeHandler<Date> {
    /*将Java类 型转为 数据库所需要的类型*/
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
        long time = parameter.getTime();
        ps.setLong(i,time);
    }
    /*将数据库中类型 转换成 java类型
    * String参数 要转换的字段名称
    * ResultSet 查询出的结果集
    * */
    @Override
    public Date getNullableResult(ResultSet rs, String columnName) throws SQLException {
        //获取结果集中需要的数据(long) 转换成Date类型 返回
        long aLong = rs.getLong(columnName);
        Date date = new Date(aLong);
        return date;
    }
    /*将数据库中类型 转换成 java类型 */
    @Override
    public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        long aLong = rs.getLong(columnIndex);
        Date date = new Date(aLong);
        return date;
    }
    /*将数据库中的类型转换成Java类型*/
    @Override
    public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        long aLong = cs.getLong(columnIndex);
        Date date = new Date(aLong);
        return date;
    }
}
com.kdy.mapper包中创建OrderMapper接口
public interface OrderMapper {
    public List<Order> findAll();
}
resource下com.kdy.mapper包中创建OrderMapper.xml

若user表和order表主键字段都叫id，需要在sql查询中对结果起别名区分一个id

使用resultMap进行Order类和order表字段映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kdy.mapper.OrderMapper">
    <resultMap id="orderMap" type="order">
        <!--手动指定字段与实体类属性的映射关系
        column：数据表的字段名称；
        properties：实体的属性名称
        -->
        <id column="oid" property="id"></id>
        <result column="order_time" property="orderTime"></result>
        <result column="total" property="total"></result>
        <result column="uid" property="user.id"></result>
        <result column="name" property="user.name"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>
    </resultMap>
    <select id="findAll" resultMap="orderMap">
        <!--由于user表和order表的主键都叫id，我们给order表的主键起个别名oid，在借助resultMap对oid映射到order类的id上-->
        select * ,o.id oid from `order` o ,`user` u WHERE o.uid=u.id ;
    </select>
</mapper>
 OrderMapper中的resultMap映射也可以写成以下格式
    <resultMap id="orderMap" type="order">
        <!--手动指定字段与实体类属性的映射关系
        column：数据表的字段名称；
        properties：实体的属性名称
        -->
        <id column="oid" property="id"></id>
        <result column="order_time" property="orderTime"></result>
        <result column="total" property="total"></result>
        <!--<result column="uid" property="user.id"></result>
        <result column="name" property="user.name"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>-->
        <!--
        property：当前实体（order）中的属性名称（private User user）
        javaType：当前实体（order）中的属性类型（User）
        -->
        <association property="user" javaType="user">
            <id column="uid" property="id"></id>
            <result column="name" property="name"></result>
            <result column="password" property="password"></result>
            <result column="birthday" property="birthday"></result>
        </association>
    </resultMap>
resource下jdbc.properties文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc
jdbc.username=root
jdbc.password=root123
resource下log4j.properties文件
### 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=c:/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=info, stdout
resource下mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--通过properties标签机制外部的properties文件-->
    <properties resource="jdbc.properties"></properties>
    <!--起别名：为权限定名的类起一个比较短的别名-->
    <typeAliases>
        <package name="com.kdy.domain"/><!--扫描的为它的类名且不区分大小写-->
        <!--        <typeAlias type="com.kdy.domain.User" alias="user"></typeAlias>-->
        <!--mybatis已经将String->string、Long->long、Integer->int、Double->double、Boolean->boolean转换设置好了别名-->
    </typeAliases>
    <!--注册类型处理器 将java的date与数据库的varchar或bigint的转换-->
    <typeHandlers>
        <typeHandler handler="com.kdy.handler.DateTypeHandler"></typeHandler>
    </typeHandlers>
    <!--配置分页助手插件-->
    <plugins>
        <!--如果pom文件pagehelper版本3.7.5和jsqlparser版本0.9.1用下面这套属性注入-->
<!--        <plugin interceptor="com.github.pagehelper.PageHelper">
            <property name="dialect" value="mysql"></property>
        </plugin>-->
        <!--如果pom文件pagehelper版本5.2.0和jsqlparser版本4.0用下面这套属性注入-->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <property name="helperDialect" value="mysql"/>
            <property name="reasonable" value="true"/>
            <property name="supportMethodsArguments" value="true"/>
            <property name="params" value="count=countSql"/>
        </plugin>
    </plugins>
    <!--数据源环境-->
    <environments default="development">
        <environment id="development"><!--环境development、test，可以配置多个数据库连接的环境信息，将来通过default属性切换-->
            <transactionManager type="JDBC"/><!--事务管理器,spring可接管，这里直接使用的JDBC的提交和回滚。依赖从数据源得到链接来管理事务作用域-->
            <dataSource type="POOLED">
                <!--数据源类型type有三种：
                UNPOOLED:这个数据源的实现只是每次被请求时打开和关闭连接。
                POOLED:这种数据源的实现利用“池” 的概念将JDBC连接对象组织起来。
                JNDI:这个数据源的实现是为了能在如EJB或应用服务器这类容器中使用，容器可以集中或在外部配置数据源,然后放置一个JNDI上下文的引用。-->
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <!--如果mysqlurl中有&符号需要进行转义为&amp;，如useSSL=false&amp;useServerPrepStmts=true
                127.0.0.1:3306本机默认端口可省略不写，直接为///-->
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--mappers加载映射，加载方式如下：
    使用相对于类路径的资源引用,例如:<mapper resource="org/mybatis/builder/AuthorMapper.xml/>
    使用完全限定资源定位符(URL) ，例如: <mapper url= "file:///var/mappers/ AuthorMapper.xml"/>
    使用映射器接口实现类的完全限定类名，例如: <mapper class=" org.mybatis.builder.AuthorMapper"/>
    将包内的映射器接口实现全部注册为映射器，例如: <package name="org.mybatis.builder"/>-->
    <mappers>
        <package name="com.kdy.mapper"/>
        <!--        <mapper resource="UserMapper.xml"/>-->
    </mappers>
</configuration>
test目录下的测试用例
    @Test
    public void test5() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> orderList = mapper.findAll();
        for (Order order: orderList) {
            System.out.println(order);
        }
        sqlSession.close();
    }

一对多查询

模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

需求

查询一个用户，与此同时查询出该用户具有的订单

还是上面一对一的案例
com.kdy.domain中User中加上一个orderList的属性
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String name;
    private String password;
    private Date birthday;//java.util下的Date

    //描述的是当前用户存在哪些订单
    private List<Order> orderList;
}
com.kdy.mapper的UserMapper接口
public interface UserMapper {
    public List<User> findAll();
}
resource下com.kdy.mapper的UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kdy.mapper.UserMapper">
    <resultMap id="userMap" type="user">
        <id column="uid" property="id"></id>
        <result column="name" property="name"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <!--配置集合信息
        property：集合名称
        ofType：当前集合中的数据类型
        -->
        <collection property="orderList" ofType="order">
            <!--封装order数据-->
            <id column="oid" property="id"></id>
            <result column="order_time" property="orderTime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userMap">
        select * ,o.id oid from `user` u ,`order` o WHERE o.uid=u.id ;
        <!--orderMapper里的findAll是
        select * ,o.id oid from `order` o ,`user` u WHERE o.uid=u.id ;
        这里from后第一个表换成了user表，这时查询的结果也是user表排左边，id也是user表的id，id1是order表的id了
        不过效果一样都是21条数据(应为是内连接)。
        且user的id还是用uid即可，
        -->
    </select>
</mapper>
test目录下的测试用例
    @Test
    public void test6() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.findAll();
        for (User user: userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }

多对多查询

模型

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用

需求

查询所有用户同时查询出该用户的所有角色

还是上面项目的延续，增加以下内容 
数据库表结构

com.kdy.domain中Role
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Role {
    private Integer id;
    private String roleName;
    private String roleDesc;
}
 com.kdy.domain中User加上roleList字段
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String name;
    private String password;
    private Date birthday;//java.util下的Date

    //描述的是当前用户存在哪些订单
    private List<Order> orderList;

    //描述当前用户具备哪些角色
    private List<Role> roleList;
}
com.kdy.mapper中UserMapper接口中增加findUserAndRoleAll方法
public interface UserMapper {
    public List<User> findAll();
    public  List<User> findUserAndRoleAll();
}
resource下com.kdy.mapper中UserMapper.xml中加上findUserAndRoleAll的statement
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kdy.mapper.UserMapper">
    <resultMap id="userMap" type="user">
        <id column="uid" property="id"></id>
        <result column="name" property="name"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <!--配置集合信息
        property：集合名称
        ofType：当前集合中的数据类型
        -->
        <collection property="orderList" ofType="order">
            <!--封装order数据-->
            <id column="oid" property="id"></id>
            <result column="order_time" property="orderTime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>
    <resultMap id="userRoleMap" type="user">
        <id column="uid" property="id"></id>
        <result column="name" property="name"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <!--user内部的roleList-->
        <collection property="roleList" ofType="role">
            <id column="rid" property="id"></id>
            <result column="role_name" property="roleName"></result>
            <result column="role_desc" property="roleDesc"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="userMap">
        select * ,o.id oid from `user` u ,`order` o WHERE o.uid=u.id ;
        <!--orderMapper里的findAll是
        select * ,o.id oid from `order` o ,`user` u WHERE o.uid=u.id ;
        这里from后第一个表换成了user表，这时查询的结果也是user表排左边，id也是user表的id，id1是order表的id了
        不过效果一样都是21条数据(应为是内连接)。
        且user的id还是用uid即可，
        -->
    </select>
    <select id="findUserAndRoleAll" resultMap="userRoleMap">
        select * from `user` u, `user_role` ur ,`role` r where u.id = ur.uid and ur.rid = r.id
    </select>
</mapper>
test目录下的测试用例
    @Test
    public void test6() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.findAll();
        for (User user: userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }

Mybatis注解开发

常用的mybatis注解

@lnsert:实现新增   @Update:实现更新   @Delete:实现删除   @Select:实现查询
@Result:实现结果集封装    @Results:可以与@Result一起使用，封装多个结果集   

@One:实现一对一结果集封装   @Many:实现一对多结果集封装

基础CRUD的mybatis注解开发

创建mavenWeb项目，pom文件引入
    <dependencies>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>4.0</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.13</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.8</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
    </dependencies>
表结构 

com.kdy.domain
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String name;
    private String password;
    private Date birthday;//java.util下的Date
}
resource下jdbc.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc
jdbc.username=root
jdbc.password=root123
resource下log4j.properties
### 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=c:/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=info, stdout
com.kdy.handler中自定义的日期的TypeHandler
public class DateTypeHandler extends BaseTypeHandler<Date> {
    /*将Java类 型转为 数据库所需要的类型*/
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
        long time = parameter.getTime();
        ps.setLong(i,time);
    }
    /*将数据库中类型 转换成 java类型
    * String参数 要转换的字段名称
    * ResultSet 查询出的结果集
    * */
    @Override
    public Date getNullableResult(ResultSet rs, String columnName) throws SQLException {
        //获取结果集中需要的数据(long) 转换成Date类型 返回
        long aLong = rs.getLong(columnName);
        Date date = new Date(aLong);
        return date;
    }
    /*将数据库中类型 转换成 java类型 */
    @Override
    public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        long aLong = rs.getLong(columnIndex);
        Date date = new Date(aLong);
        return date;
    }
    /*将数据库中的类型转换成Java类型*/
    @Override
    public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        long aLong = cs.getLong(columnIndex);
        Date date = new Date(aLong);
        return date;
    }
}
resource下mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--通过properties标签机制外部的properties文件-->
    <properties resource="jdbc.properties"></properties>
    <!--起别名：为权限定名的类起一个比较短的别名-->
    <typeAliases>
        <package name="com.kdy.domain"/><!--扫描的为它的类名且不区分大小写-->
        <!--        <typeAlias type="com.kdy.domain.User" alias="user"></typeAlias>-->
        <!--mybatis已经将String->string、Long->long、Integer->int、Double->double、Boolean->boolean转换设置好了别名-->
    </typeAliases>
    <!--注册类型处理器 将java的date与数据库的varchar或bigint的转换-->
    <typeHandlers>
        <typeHandler handler="com.kdy.handler.DateTypeHandler"></typeHandler>
    </typeHandlers>
    <!--配置分页助手插件-->
    <plugins>
        <!--如果pom文件pagehelper版本3.7.5和jsqlparser版本0.9.1用下面这套属性注入-->
<!--        <plugin interceptor="com.github.pagehelper.PageHelper">
            <property name="dialect" value="mysql"></property>
        </plugin>-->
        <!--如果pom文件pagehelper版本5.2.0和jsqlparser版本4.0用下面这套属性注入-->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <property name="helperDialect" value="mysql"/>
            <property name="reasonable" value="true"/>
            <property name="supportMethodsArguments" value="true"/>
            <property name="params" value="count=countSql"/>
        </plugin>
    </plugins>
    <!--数据源环境-->
    <environments default="development">
        <environment id="development"><!--环境development、test，可以配置多个数据库连接的环境信息，将来通过default属性切换-->
            <transactionManager type="JDBC"/><!--事务管理器,spring可接管，这里直接使用的JDBC的提交和回滚。依赖从数据源得到链接来管理事务作用域-->
            <dataSource type="POOLED">
                <!--数据源类型type有三种：
                UNPOOLED:这个数据源的实现只是每次被请求时打开和关闭连接。
                POOLED:这种数据源的实现利用“池” 的概念将JDBC连接对象组织起来。
                JNDI:这个数据源的实现是为了能在如EJB或应用服务器这类容器中使用，容器可以集中或在外部配置数据源,然后放置一个JNDI上下文的引用。-->
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <!--如果mysqlurl中有&符号需要进行转义为&amp;，如useSSL=false&amp;useServerPrepStmts=true
                127.0.0.1:3306本机默认端口可省略不写，直接为///-->
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--mappers加载映射，加载方式如下：
    使用相对于类路径的资源引用,例如:<mapper resource="org/mybatis/builder/AuthorMapper.xml/>
    使用完全限定资源定位符(URL) ，例如: <mapper url= "file:///var/mappers/ AuthorMapper.xml"/>
    使用映射器接口实现类的完全限定类名，例如: <mapper class=" org.mybatis.builder.AuthorMapper"/>
    将包内的映射器接口实现全部注册为映射器，例如: <package name="org.mybatis.builder"/>-->
    <mappers>
        <package name="com.kdy.mapper"/>
        <!--        <mapper resource="UserMapper.xml"/>-->
    </mappers>
</configuration>
com.kdy.mapper中UserMapper接口
public interface UserMapper {
    @Insert("insert into user values(#{id},#{name},#{password},#{birthday})")
    public void save(User user);
    @Update("update user set name = #{name} ,password = #{password},birthday = #{birthday} where id=#{id}")
    public void update(User user);
    @Delete("delete from user where id = #{id}")
    public void delete(int id);
    @Select("select * from user where id = #{id}")
    public User findById(int id);
    @Select("select * from user")
    public List<User> findAll();
}
resource下com.kdy.mapper中UserMappe.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kdy.mapper.UserMapper"><!--即使使用mybatis的注解开发也需要加上命名空间的mapper标签-->
<!--    <insert id="save" parameterType="user">
        insert into user values(#{id},#{name},#{password},#{birthday})
    </insert>
    <update id="update" parameterType="user">
        update user set name = #{name} ,password = #{password},birthday = #{birthday} where id=#{id}
    </update>
    <delete id="delete" parameterType="int">
        delete from user where id = #{id}
    </delete>
    <select id="findById" resultType="user" parameterType="int">
        select * from user where id = #{id}
    </select>
    <select id="findAll" resultType="user">
        select * from user
    </select>-->
</mapper>
test目录下测试用例
public class UserTest {

    private UserMapper mapper;
    private SqlSession sqlSession;
    @Before
    public void before() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        sqlSession = sqlSessionFactory.openSession();
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @After
    public void after(){
        sqlSession.close();
    }

    @Test
    public void testSave(){
        User user = new User(null, "tom666", "hello666", new Date());
        mapper.save(user);
        sqlSession.commit();
    }

    @Test
    public void testUpdate(){
        User user = new User(38, "tom777", "hello777", new Date());
        mapper.update(user);
        sqlSession.commit();
    }

    @Test
    public void testDelete(){
        mapper.delete(35);
        sqlSession.commit();
    }

    @Test
    public void testFindById(){
        User user = mapper.findById(3);
        System.out.println(user);
    }

    @Test
    public void testFindAll(){
        List<User> userList = mapper.findAll();
        System.out.println(userList);
    }
}

MyBatis的注解实现复杂映射开发

实现复杂关系映射之前我们可以在映射文件中通过配置<resultMap>来实现，使用注解开发后，我们可以使用@Results注解@Result注解，@One注解，@Many注解组合完成复杂关系的配置

@Results：代替的是标签<resultMap>该注解中可以使用单个@Result注解也可以使用@Result集合。使用格式:@Results ({@Result () ,@Result () })或@Results (@Result () )

@Result：代替了<id>标签和<result>标签，@Result中属性介绍:column:数据车的列名property:需要装配的属性名   one:需要使用的(One注解( @Result (one=@One) () ))
many:需要使用的@Many注解(@Result (many=@many)() ) )

@One(一对一)：代替了<assocation>标签，是多表查询的关键，在注解中用来指定子查询返回单一对象。@One注解属性介绍:select:指定用来多表查询的sqlmapper  使用格式: @Result(column=" ",property="",one=@One(select=""))

@Many (多对一)：代替了<collection>标签,是是多表查询的关键，在注解中用来指定子查询返回对象集合。使用格式:@Result(property="",column="",many=@Many(select=""))

一对一查询注解开发

模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户。

需求

查询一个订单，与此同时查询出该订单所属的用户。

还是使用上面注解开发CRUD的案例
数据表加上order表

com.kdy.domain中Order
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Order {
    private int id;
    private Date orderTime;
    private double total;

    //当前订单属于哪个用户
    private User user;
}
 com.kdy.mapper中OrderMapper com.kdy.mapper中OrderMapper接口

 查询方式一：

select * ,o.id oid from `order` o,`user` u where o.uid = u.id

public interface OrderMapper {
    @Select("select * ,o.id oid from `order` o,`user` u where o.uid = u.id")
    @Results({
            @Result(column = "oid", property = "id"),
            @Result(column = "order_time", property = "orderTime"),
            @Result(column = "total", property = "total"),
            @Result(column = "uid", property = "user.id"),
            @Result(column = "name", property = "user.name"),
            @Result(column = "password", property = "user.password"),
            @Result(column = "birthday", property = "user.birthday")
    })
    public List<Order> findAll();
}
resource下com.kdy.mapper中OrderMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kdy.mapper.OrderMapper"><!--即使使用mybatis的注解开发也需要加上命名空间的mapper标签-->
</mapper>
test目录下OrderTest测试用例
public class OrderTest {
    private OrderMapper mapper;
    private SqlSession sqlSession;

    @Before
    public void before() throws IOException {
        //1.加载mybatis核心配置，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2.获取SqlSession对象，用它来执行sql
        sqlSession = sqlSessionFactory.openSession();
        mapper = sqlSession.getMapper(OrderMapper.class);
    }

    @After
    public void after(){
        sqlSession.close();
    }

    @Test
    public void test(){
        List<Order> orderList = mapper.findAll();
        for (Order order:orderList) {
            System.out.println(order);
        }
    }
}

一对一关系的另一种查询方式：

查询方式二：

select * from order;

select * from user where id = order表中的uid;

上面的CRUD案例中UserMapper中有一个finById根据id查user表中数据的方法
我们的OrderMapper接口中先查order表，然后根据uid查user表封装结果集，改写为下：
public interface OrderMapper {
    @Select("select * from `order`")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "order_time", property = "orderTime"),
            @Result(column = "total", property = "total"),
            @Result(
                    property = "user",//要封装的属性名称
                    column = "uid",//根据哪个字段去查询user表中的数据
                    javaType = User.class,//要封装的实体类型
                    //select属性 代表查询哪个接口的方法获得数据
                    one = @One(select = "com.kdy.mapper.UserMapper.findById")
            )
    })
    public List<Order> findAll();
}
运行一对一的测试用例即可。

一对多查询

模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

需求

一对多查询的需求:查询一个用户，与此同时查询出该用户具有的订单

com.kdy.domain中User中加上orderList属性
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String name;
    private String password;
    private Date birthday;//java.util下的Date

    //描述的是当前用户具有的订单
    private List<Order> orderList;
}
使用下面这种sql的方式查询且封装结果集

select * from user;  查询的id作为下面的参数

select * from order where uid = 用户id;

OrderMapper接口中加上根据uid查询的方法
    @Select("select * from `order` where uid = #{uid}")
    public List<Order> findByUid(int uid);
UserMapper接口中加上查询user表所有数据和根据uid查order表对应数据的方法
    @Select("select * from user")
    @Results({
            @Result(id = true,column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "password",property = "password"),
            @Result(column = "birthday",property = "birthday"),
            @Result(
                    property = "orderList",
                    column = "id",//上面查询user的哪个字段作为这个查询的参数
                    javaType = List.class,
                    many = @Many(select = "com.kdy.mapper.OrderMapper.findByUid")
            )
    })
    public List<User> findUserAndOrderList();
test目录中UserTest中加上测试用例
    @Test
    public void testFindUserAndOrderList(){
        List<User> userAndOrderList = mapper.findUserAndOrderList();
        for (User user:userAndOrderList) {
            System.out.println(user);
        }
    }

多对多查询

模型

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用

需求

查询用户同时查询出该用户的所有角色

还是继续上面案例
数据库中除了user表外加上role表和user_role表的表结构

 domain中创建Role
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Role {
    private Integer id;
    private String roleName;
    private String roleDesc;
}
 domain中User加上roleList
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String name;
    private String password;
    private Date birthday;//java.util下的Date

    //描述当前用户具备的角色
    private List<Role> roleList;

    //描述的是当前用户具有的订单
    private List<Order> orderList;
}
使用下面这种sql的方式查询且封装结果集

select * from user; id作为下面的参数

select * from user_role ur, role r where ur.rid = r.id and ur.uid = 用户的id;

com.kdy.mapper中创建RoleMapper接口
public interface RoleMapper {
    //根据用户id查询该用户的所有角色
    @Select("select * from `user_role` ur,`role` r where ur.rid = r.id and ur.uid = #{uid}")
    public List<Role> findByUid(int uid);
}
resource下的com.kdy.mapper中创建RoleMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kdy.mapper.RoleMapper"><!--即使使用mybatis的注解开发也需要加上命名空间的mapper标签-->
</mapper>
UserMapper接口中加上findUserAndRoleAll
    @Select("select * from user")
    @Results({
            @Result(id = true,column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "password",property = "password"),
            @Result(column = "birthday",property = "birthday"),
            @Result(
                    property = "roleList",
                    column = "id",//上面查询user的哪个字段作为这个查询的参数
                    javaType = List.class,
                    many = @Many(select = "com.kdy.mapper.RoleMapper.findByUid")
            )
    })
    public List<User> findUserAndRoleAll();
test目录UserTest中加上测试用例
    @Test
    public void testFindUserAndRoleAll(){
        List<User> userList = mapper.findUserAndRoleAll();
        for (User user:userList) {
            System.out.println(user);
        }
    }
运行测试用例即可。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值