Mybatis篇一

时间：2021-03-31 16:27:00 阅读：37 评论：0 收藏：0 [点我收藏+]

Mybatis

简介

原始jdbc操作

原始jdbc开发存在的问题

数据库连接创建，释放频繁造成系统资源浪费从而影响系统性能
sql语句在代码中硬编码，造成代码不易维护，实际应用sql变化的可能较大，sql变动需要改变java代码
查询操作时，需要手动将结果集中的数据手动封装到实体中，插入操作时，需要手动将实体的数据设置到sql语句的占位符位置

解决方案(mybatis)

使用数据库连接池初始化连接资源
将sql语句抽取到xml配置文件中
使用反射、内省等底层技术，自动将实体与表进行属性与字段的自动映射

Mybatis

基于Java的持久层框架
内部封装类jdbc
解耦合，开发者本身只需要关注sql语句的编写
通过xml或注解方式将需要执行的statement配置起来
通过java对象和statement中的sql动态映射生成最终的sql

开发步骤

添加依赖
创建user表
编写User实体类
编写映射文件UserMapper.xml
编写核心文件SqlMapConfig.xml
编写测试类

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
?
    <groupId>org.example</groupId>
    <artifactId>mybatis_quick</artifactId>
    <version>1.0-SNAPSHOT</version>
?
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
?
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
?
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>
?
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>
?
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>
?
</project>

package com.cyz.domain;
?
import javax.management.relation.Role;
import java.util.List;
?
public class User {
    private Long id;
    private String username;
    private String email;
    private String password;
    private String phoneNum;
?
    public Long getId() {
        return id;
    }
?
    public void setId(Long id) {
        this.id = id;
    }
?
    public String getUsername() {
        return username;
    }
?
    public void setUsername(String username) {
        this.username = username;
    }
?
    public String getEmail() {
        return email;
    }
?
    public void setEmail(String email) {
        this.email = email;
    }
?
    public String getPassword() {
        return password;
    }
?
    public void setPassword(String password) {
        this.password = password;
    }
?
    public String getPhoneNum() {
        return phoneNum;
    }
?
    public void setPhoneNum(String phoneNum) {
        this.phoneNum = phoneNum;
    }
?
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username=‘" + username + ‘\‘‘ +
                ", email=‘" + email + ‘\‘‘ +
                ", password=‘" + password + ‘\‘‘ +
                ", phoneNum=‘" + phoneNum + ‘\‘‘ +
                ‘}‘;
    }
}

userMapper.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="userMapper">
?
    <select id="findAll" resultType="com.cyz.domain.User">
        select * from sys_user
    </select>
</mapper>

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>
<!--    数据源环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/jdbc_test"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
?
<!--    加载映射文件-->
    <mappers>
        <mapper resource="com/cyz/mapper/UserMapper.xml"></mapper>
    </mappers>
</configuration>

log4j.properties

#console
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
?
#file
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=e:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
?
log4j.rootLogger=debug,stdout

测试代码

package com.cyz.test;
?
import com.cyz.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.Test;
?
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
?
public class MyBatisTest {
?
    @Test
    public void test() throws IOException {
//        获取核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//        获取session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//        获取session会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
//        执行操作  参数：namespace+id
        List<User> userList = sqlSession.selectList("userMapper.findAll");
//      打印数据
        System.out.println(userList);
//        释放资源
        sqlSession.close();
    }
}

映射文件

增删改查

查询和插入
- 注意食物默认不提交

<?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="userMapper">
<!--插入操作-->
    <insert id="save" parameterType="com.cyz.domain.User">
        insert into sys_user values(#{id},#{username},#{email},#{password},#{phoneNum})
    </insert>
<!--    查询操作-->
    <select id="findAll" resultType="com.cyz.domain.User">
        select * from sys_user
    </select>
</mapper>

package com.cyz.test;
?
import com.cyz.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.Test;
?
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
?
public class MyBatisTest {
?
    @Test
    public void test() throws IOException {
//        获取核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//        获取session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//        获取session会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
//        执行操作  参数：namespace+id
        List<User> userList = sqlSession.selectList("userMapper.findAll");
//      打印数据
        System.out.println(userList);
//        释放资源
        sqlSession.close();
    }
?
    @Test
    public void test2() throws IOException {
?
//        模拟User
        User user = new User();
        user.setUsername("xxxx");
        user.setEmail("163@qq.com");
        user.setPassword("123123");
        user.setPhoneNum("17896556552");
?
//        获取核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//        获取session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//        获取session会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
//        执行操作  参数：namespace+id,实体对象
?
        sqlSession.insert("userMapper.save",user);
?
//        默认事务不提交
        sqlSession.commit();
?
?
//        释放资源
        sqlSession.close();
    }
}

修改

    <!--    修改操作-->
    <update id="update" parameterType="com.cyz.domain.User">
        update sys_user
        set username = #{username},
            email    = #{email},
            password=#{password},
            phoneNum=#{phoneNum}
        where id = #{id}
    </update>

    @Test
    public void test3() throws IOException {

//        模拟User
        User user = new User();
        user.setId(8L);
        user.setUsername("eeeee");
        user.setEmail("163e@qq.com");
        user.setPassword("123123e");
        user.setPhoneNum("178965565ee");

//        获取核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//        获取session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//        获取session会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
//        执行操作  参数：namespace+id,实体对象

        sqlSession.update("userMapper.update",user);

//        默认事务不提交
        sqlSession.commit();


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

删除

<!--    删除操作-->
    <delete id="delete" parameterType="java.lang.Long">
        delete from sys_user where id = #{id}
    </delete>

    @Test
    public void test4() throws IOException {
//        获取核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//        获取session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//        获取session会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
//        执行操作  参数：namespace+id,实体对象

        sqlSession.delete("userMapper.delete",8L);

//        默认事务不提交
        sqlSession.commit();

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

核心配置文件

<?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-->
    <properties resource="jdbc.properties"/>
?
    <!--    别名配置            注意配置位置是有顺序的-->
    <typeAliases>
        <typeAlias type="com.cyz.domain.User" alias="user"/>
    </typeAliases>
?
<!--    数据源环境-->
<!--    指定默认环境-->
    <environments default="development">
<!--    当前环境    -->
        <environment id="development">
<!--            当前事务管理器类型 两种
                JDBC:这个配置就是直接使用了JDBC的提交和回滚设置，它依赖于从数据源得到的连接来管理事务作用域
                MANAGED：这个配置几乎没做什么 它从来不提交和回滚一个连接，而是让容器来管理事务的整个生命周期
                        （比如：JEE应用服务器的上下文），默认情况下它会关闭连接，然而一些容器并不希望这样，因此需要
                        将closeConnection属性设置为false来阻止它默认的关闭行为
 -->
            <transactionManager type="JDBC"/>
<!--            当前数据源类型 三种
                UNPOOLED：这个数据源的实现只是每次被请求时打开和关闭
                POOLED：这种数据源的实现利用“池”的概念将JDBC连接对象组织起来
                JNDI:这个数据源的实现是为了能在如EJB或应用服务器这类容器中使用，容器可以
                    集中或在外部配置数据源，让后放置一个JNDI上下文的引用
-->
            <dataSource type="POOLED">
<!--                当前数据源配置基本参数-->
                <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>
?
<!--    加载映射文件
    使用相对于类路径的资源引用：<mapper resource="com/cyz/mapper/UserMapper.xml"/>
    使用完全限定资源定位符（URL）：<mapper url="file:///var/mappers/UserMapper.xml"/>
    使用映射器接口实现类的完全限定类名 <mapper class="com.cyz.mapper.UserMapper"/>
    将包内的映射器接口实现全部注册为映射器：<package name="com.cyz.mapper"/>
-->
    <mappers>
        <mapper resource="com/cyz/mapper/UserMapper.xml"/>
    </mappers>
?
?
</configuration>

<?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="userMapper">

<!--    删除操作-->
    <delete id="delete" parameterType="long">
        delete from sys_user where id = #{id}
    </delete>
    <!--    修改操作-->
    <update id="update" parameterType="user">
        update sys_user
        set username = #{username},
            email    = #{email},
            password=#{password},
            phoneNum=#{phoneNum}
        where id = #{id}
    </update>

    <!--插入操作-->
    <insert id="save" parameterType="user">
        insert into sys_user
        values (#{id}, #{username}, #{email}, #{password}, #{phoneNum})
    </insert>
    <!--    查询操作-->
    <select id="findAll" resultType="user">
        select *
        from sys_user
    </select>
</mapper>

相应API

SqlSessionFactory build(InputStream inputStream)

//        获取核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//        获取session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

Resources：工具类，帮助我们获取从类路径下，文件系统或一个web URL中加载资源文件

Mybatis的Dao层实现

传统开发模式

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
?
    <groupId>org.example</groupId>
    <artifactId>mybatis_dao</artifactId>
    <version>1.0-SNAPSHOT</version>
?
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
?
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
?
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>
?
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>
?
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>
?
</project>

package com.cyz.domain;
?
public class User {
    private Long id;
    private String username;
    private String email;
    private String password;
    private String phoneNum;
?
    public Long getId() {
        return id;
    }
?
    public void setId(Long id) {
        this.id = id;
    }
?
    public String getUsername() {
        return username;
    }
?
    public void setUsername(String username) {
        this.username = username;
    }
?
    public String getEmail() {
        return email;
    }
?
    public void setEmail(String email) {
        this.email = email;
    }
?
    public String getPassword() {
        return password;
    }
?
    public void setPassword(String password) {
        this.password = password;
    }
?
    public String getPhoneNum() {
        return phoneNum;
    }
?
    public void setPhoneNum(String phoneNum) {
        this.phoneNum = phoneNum;
    }
?
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username=‘" + username + ‘\‘‘ +
                ", email=‘" + email + ‘\‘‘ +
                ", password=‘" + password + ‘\‘‘ +
                ", phoneNum=‘" + phoneNum + ‘\‘‘ +
                ‘}‘;
    }
}

#console
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

#file
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=e:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

log4j.rootLogger=info,stdout

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc_test
jdbc.username=root
jdbc.password=123456

<?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 resource="jdbc.properties"/>

    <typeAliases>
        <typeAlias type="com.cyz.domain.User" alias="user"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <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>

    <mappers>
        <mapper resource="com/cyz/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

<?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="userMapper">

    <select id="findAll" resultType="user">
        select *
        from sys_user
    </select>

</mapper>

package com.cyz.dao;

import com.cyz.domain.User;

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

public interface UserMapper {
    public List<User> findAll() throws IOException;
}

package com.cyz.dao.impl;

import com.cyz.dao.UserMapper;
import com.cyz.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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class UserMapperImpl implements UserMapper {
    @Override
    public List<User> findAll() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> userList = sqlSession.selectList("userMapper.findAll");
        return userList;
    }
}

模拟service层

package com.cyz.service;

import com.cyz.dao.UserMapper;
import com.cyz.dao.impl.UserMapperImpl;
import com.cyz.domain.User;
import java.io.IOException;
import java.util.List;

public class UserDemo {

    public static void main(String[] args) throws IOException {
//        创建dao层对象 当前dao层实现手动编写的
        UserMapper userMapper = new UserMapperImpl();
        List<User> all = userMapper.findAll();
        System.out.println(all);
    }
}

接口代理方式

采用Mybatis的代理开发方式实现DAO层的开发
开发者只需要编写Mapper接口（相当于Dao接口），由Mybatis框架根据接口自定义创建接口的动态代理对象，代理对象的方法同上边Dao接口实现类方法

Mapper接口开发需要遵循以下规范：

Mapper.xml文件的namespace与mapper接口的全限定名相同
Mapper接口方法名和Mapper.xml中定义的每一个statement的id相同
Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql的parameterType的类型相同
Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同

package com.cyz.dao;

import com.cyz.domain.User;

import java.util.List;

public interface UserMapper {
    public List<User> findAll();

    public User findById(Long id);
}

<?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.cyz.dao.UserMapper">

    <select id="findAll" resultType="user">
        select *
        from sys_user
    </select>

<!--    根据ID进行查询-->
    <select id="findById" parameterType="long" resultType="user">
        select * from sys_user where id = #{id}
    </select>
</mapper>

package com.cyz.service;

import com.cyz.dao.UserMapper;
import com.cyz.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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class UserDemo {

    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> all = mapper.findAll();
        System.out.println(all);

        User user = mapper.findById(1L);
        System.out.println(user);
        
        sqlSession.close();
    }
}

映射文件的深入

动态sql

复杂业务中的sql是动态变化的

where/if

package com.cyz.mapper;
?
import com.cyz.domain.User;
?
import java.util.List;
?
public interface UserMapper {
    public List<User> findByCondition(User user);
}

<?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.cyz.mapper.UserMapper">
?
    <select id="findByCondition" parameterType="user" resultType="user">
        select * from sys_user
        <where>
            <if test="id != null">
                and id=#{id}
            </if>
            <if test="username != null and username != ‘‘">
                and username=#{username}
            </if>
            <if test="password != null and password != ‘‘">
                and password=#{password}
            </if>
        </where>
    </select>
</mapper>

package com.cyz.test;
?
import com.cyz.domain.User;
import com.cyz.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
?
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
?
public class MapperTest {
?
    @Test
    public void test() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
?
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
?
//       模拟条件
        User condition = new User();
//        condition.setId(1L);
//        condition.setUsername("张三");
//        condition.setPassword("");
//        condition.setPassword("123");
?
        List<User> userList = mapper.findByCondition(condition);
?
        System.out.println(userList);
        
        sqlSession.close();
    }
}

foreach

package com.cyz.mapper;

import com.cyz.domain.User;

import java.util.List;

public interface UserMapper {
    public List<User> findByCondition(User user);

    public List<User> findByIds(List<Long> ids);
}

    <select id="findByIds" parameterType="list" resultType="user">
        select * from sys_user
        <where>
<!--            <foreach collection="array" item="id" separator="," open="id in(" close=")">-->
            <foreach collection="list" item="id" separator="," open="id in(" close=")">
                #{id}
            </foreach>
        </where>
    </select>

  @Test
    public void test2() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

       List<Long> ids = new ArrayList<>();
       ids.add(1L);
       ids.add(2L);

        List<User> userList = mapper.findByIds(ids);

        System.out.println(userList);
        
        sqlSession.close();
    }

sql片断抽取

<?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.cyz.mapper.UserMapper">

<!--    sql语句抽取-->
    <sql id="selectUser">
        select * from sys_user
    </sql>

    <select id="findByCondition" parameterType="user" resultType="user">
        <include refid="selectUser"/>
        <where>
            <if test="id != null">
                and id=#{id}
            </if>
            <if test="username != null and username != ‘‘">
                and username=#{username}
            </if>
            <if test="password != null and password != ‘‘">
                and password=#{password}
            </if>
        </where>
    </select>

    <select id="findByIds" parameterType="list" resultType="user">
        <include refid="selectUser"/>
        <where>
<!--            <foreach collection="array" item="id" separator="," open="id in(" close=")">-->
            <foreach collection="list" item="id" separator="," open="id in(" close=")">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>

配置文件深入

typeHanders

你可以重写类型处理器或创建自己的类型处理器来处理不支持的或非标准的类型，具体做法：实现org.apache.ibatis.type.TypeHandler接口，或继承一个很便利的类org.apache.ibatis.type.BaseTypeHandler，然后可以选择的将他映射到一个JDBC类型，例如需求：一个Java中的Date数据类型，我想将之存到数据库的时候存成一个1970年至今的毫秒数，取出来时转换成java的Date，即java的Date与数据库的varchar毫秒值之间转换
开发步骤
- 定义转换类继承类BaseTypeHandler<T>
- 覆盖4个未实现的方法，其中setNonNullParameter为java程序设置数据到数据库的回调方法，getNullableResult为查询时mysql的字符串类型转换成java的Type类型的方法
- 在Mybatis核心配置文件中进行注册
- 测试转换是否正确

package com.cyz.domain;

import java.util.Date;

public class User {
    private Long id;
    private String username;
    private String email;
    private String password;
    private String phoneNum;

    private Date birthday;

    public Long getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getPhoneNum() {
        return phoneNum;
    }

    public void setPhoneNum(String phoneNum) {
        this.phoneNum = phoneNum;
    }

    public Date getBirthday() {
        return birthday;
    }

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

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username=‘" + username + ‘\‘‘ +
                ", email=‘" + email + ‘\‘‘ +
                ", password=‘" + password + ‘\‘‘ +
                ", phoneNum=‘" + phoneNum + ‘\‘‘ +
                ", birthday=" + birthday +
                ‘}‘;
    }
}

package com.cyz.mapper;

import com.cyz.domain.User;

public interface UserMapper {

    public void save(User user);
    
    public User findById(Long id);
    
    public List<User> findAll();
}

<?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.cyz.mapper.UserMapper">
    <select id="save" parameterType="user">
        insert into sys_user
        values (#{id}, #{username}, #{email}, #{password}, #{phoneNum}, #{birthday})
    </select>
    
    <select id="findById" parameterType="long" resultType="user">
       select * from sys_user where id = #{id}
    </select>
    
    
    <select id="findAll" resultType="user">
        select * from sys_user
    </select>
</mapper>

package com.cyz;

import com.cyz.domain.User;
import com.cyz.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

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

public class MyBatisTest {

    @Test
    public void test() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

//        模拟对象
        User user = new User();
        user.setUsername("asda");
        user.setEmail("145@qq.com");
        user.setPassword("12312");
        user.setPhoneNum("12312566544");
        
        user.setBirthday(new Date());
//        执行保存操作
        mapper.save(user);

        sqlSession.commit();
        sqlSession.close();
    }
}

实现

package com.cyz.handler;
?
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
?
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
?
public class DateTypeHandler extends BaseTypeHandler<Date> {
   //    将java类型 转换成 数据库需要的类型
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        long time = date.getTime();
        preparedStatement.setLong(i,time);
    }
?
    //    将数据库中类型 转换成java类型
//    String参数 表的字段名称
//    ResultSet 查询出的结果集
    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
//        获取结果集中需要的数据（long）转换为Date类型 返回
        long aLong = resultSet.getLong(s);
        Date date = new Date(aLong);
        return date;
    }
?
    //    将数据库中类型 转换成java类型
    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        long aLong = resultSet.getLong(i);
        Date date = new Date(aLong);
        return date;
    }
?
    //    将数据库中类型 转换成java类型
    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        long aLong = callableStatement.getLong(i);
        Date date = new Date(aLong);
        return date;
    }
}

<?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 resource="jdbc.properties"/>

    <typeAliases>
        <typeAlias type="com.cyz.domain.User" alias="user"/>
    </typeAliases>

<!--    注册类型处理器-->
    <typeHandlers>
        <typeHandler handler="com.cyz.handler.DateTypeHandler"/>
    </typeHandlers>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <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>

    <mappers>
        <mapper resource="com/cyz/mapper/UserMapper.xml"/>
    </mappers>

</configuration>

再次测试

package com.cyz;

import com.cyz.domain.User;
import com.cyz.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

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

public class MyBatisTest {

    @Test
    public void test() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

//        模拟对象
        User user = new User();
        user.setUsername("asda");
        user.setEmail("145@qq.com");
        user.setPassword("12312");
        user.setPhoneNum("12312566544");
        user.setBirthday(new Date());
//        执行保存操作
        mapper.save(user);

        sqlSession.commit();
        sqlSession.close();
    }


    @Test
    public void test2() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = mapper.findById(11L);
        System.out.println(user);

        sqlSession.close();
    }
}

plugins标签

扩展第三方插件
- 分页助手PageHelper
开发步骤
- 导入依赖
- 在mybatis核心配置文件中配置PageHelper插件
- 测试分页数据获取

<!--        pagehelper-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.4</version>
        </dependency>
<!--        5.x版本后不需要-->
<!--        <dependency>-->
<!--            <groupId>com.github.jsqlparser</groupId>-->
<!--            <artifactId>jsqlparser</artifactId>-->
<!--            <version>3.2</version>-->
<!--        </dependency>-->
        <!--        pagehelper-->

<?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 resource="jdbc.properties"/>
?
    <typeAliases>
        <typeAlias type="com.cyz.domain.User" alias="user"/>
    </typeAliases>
?
<!--    注册类型处理器-->
    <typeHandlers>
        <typeHandler handler="com.cyz.handler.DateTypeHandler"/>
    </typeHandlers>
?
<!--    配置分页助手插件  4.0.x-->
<!--    <plugins>-->
<!--        <plugin interceptor="com.github.pagehelper.PageHelper">-->
<!--&lt;!&ndash;            指定方言&ndash;&gt;-->
<!--            <property name="dialect" value="mysql"/>-->
<!--        </plugin>-->
<!--    </plugins>-->
    <!--    配置分页助手插件  5.0.x-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"/>
    </plugins>
?
?
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <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>
?
    <mappers>
        <mapper resource="com/cyz/mapper/UserMapper.xml"/>
    </mappers>
?
</configuration>

测试

    @Test
    public void test3() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
?
//        设置分页相关参数      当前页 + 每页条数      超过页数显示最后一页
        PageHelper.startPage(1,3);
?
        List<User> userList = mapper.findAll();
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }

 @Test
    public void test3() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
?
//        设置分页相关参数      当前页 + 每页条数          超过页数显示最后一页
        PageHelper.startPage(1,3);
?
        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());
?
        sqlSession.close();
    }

多表操作

一对一查询

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>mybatis_multi</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

</project>

package com.cyz.domain;

import java.math.BigDecimal;
import java.util.Date;

public class Orders {

    private Long id;
    private Date ordertime;
    private BigDecimal total;

    //    当前订单属于哪一个用户
    private User user;

    public Long getId() {
        return id;
    }

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

    public Date getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(Date ordertime) {
        this.ordertime = ordertime;
    }

    public BigDecimal getTotal() {
        return total;
    }

    public void setTotal(BigDecimal total) {
        this.total = total;
    }

    public User getUser() {
        return user;
    }

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

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", ordertime=" + ordertime +
                ", total=" + total +
                ", user=" + user +
                ‘}‘;
    }
}

package com.cyz.domain;

import java.util.Date;

public class User {
    private Long id;
    private String username;
    private String email;
    private String password;
    private String phoneNum;

    public Long getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getPhoneNum() {
        return phoneNum;
    }

    public void setPhoneNum(String phoneNum) {
        this.phoneNum = phoneNum;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username=‘" + username + ‘\‘‘ +
                ", email=‘" + email + ‘\‘‘ +
                ", password=‘" + password + ‘\‘‘ +
                ", phoneNum=‘" + phoneNum + ‘\‘‘ +
                ‘}‘;
    }
}

<?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 resource="jdbc.properties"/>

    <typeAliases>
        <typeAlias type="com.cyz.domain.User" alias="user"/>
        <typeAlias type="com.cyz.domain.Orders" alias="orders"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <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>

    <mappers>
        <mapper resource="com/cyz/mapper/UserMapper.xml"/>
        <mapper resource="com/cyz/mapper/OrderMapper.xml"/>
    </mappers>

</configuration>

<?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.cyz.mapper.OrderMapper">

    <resultMap id="orderMap" type="orders">
<!--        手动指定字段与实体之间的关系-->
        <id column="oid" property="id"/>
        <result column="ordertime" property="ordertime"/>
        <result column="total" property="total"/>
        <result column="uid" property="user.id"/>
        <result column="username" property="user.username"/>
        <result column="email" property="user.email"/>
        <result column="password" property="user.password"/>
        <result column="phoneNum" property="user.phoneNum"/>
    </resultMap>

    <select id="findAll" resultMap="orderMap">
        select *,o.id oid from orders o,sys_user u where o.uid=u.id
    </select>
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cyz.mapper.OrderMapper">

    <resultMap id="orderMap" type="orders">
        <!--        手动指定字段与实体之间的关系
                column:数据库表的字段名称
                property：实体的属性名称
        -->
        <id column="oid" property="id"/>
        <result column="ordertime" property="ordertime"/>
        <result column="total" property="total"/>
        <!--        <result column="uid" property="user.id"/>-->
        <!--        <result column="username" property="user.username"/>-->
        <!--        <result column="email" property="user.email"/>-->
        <!--        <result column="password" property="user.password"/>-->
        <!--        <result column="phoneNum" property="user.phoneNum"/>-->
        <!--
            property:当前实体（order）中的属性名称（private User user）
            javaType:当前实体（order）中的属性的类型（User）
        -->
        <association property="user" javaType="user">
            <id column="uid" property="id"/>
            <result column="username" property="username"/>
            <result column="email" property="email"/>
            <result column="password" property="password"/>
            <result column="phoneNum" property="phoneNum"/>
        </association>
    </resultMap>

    <select id="findAll" resultMap="orderMap">
        select *, o.id oid
        from orders o,
             sys_user u
        where o.uid = u.id
    </select>
</mapper>

package com.cyz;
?
import com.cyz.domain.Orders;
import com.cyz.mapper.OrderMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
?
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
?
public class MybatisTest {
?
    @Test
    public void test() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
?
        List<Orders> ordersList = mapper.findAll();
        System.out.println(ordersList);
?
        sqlSession.close();
    }
}

一对多查询

用户表和订单表的关系为：一个用户有多个订单，一个订单只从属于一个用户
一对多查询需求：查询一个用户，与此同时查询出该用户具有的订单

package com.cyz.domain;
?
import java.math.BigDecimal;
import java.util.Date;
?
public class Orders {
?
    private Long id;
    private Date ordertime;
    private BigDecimal total;
?
    //    当前订单属于哪一个用户
    private User user;
?
    public Long getId() {
        return id;
    }
?
    public void setId(Long id) {
        this.id = id;
    }
?
    public Date getOrdertime() {
        return ordertime;
    }
?
    public void setOrdertime(Date ordertime) {
        this.ordertime = ordertime;
    }
?
    public BigDecimal getTotal() {
        return total;
    }
?
    public void setTotal(BigDecimal total) {
        this.total = total;
    }
?
    public User getUser() {
        return user;
    }
?
    public void setUser(User user) {
        this.user = user;
    }
?
    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", ordertime=" + ordertime +
                ", total=" + total +
                ", user=" + user +
                ‘}‘;
    }
}

package com.cyz.mapper;

import com.cyz.domain.User;

import java.util.List;

public interface UserMapper {
    public List<User> findAll();
}

<?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.cyz.mapper.UserMapper">
    <resultMap id="userMap" type="user">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="email" property="email"/>
        <result column="password" property="password"/>
        <result column="phoneNum" property="phoneNum"/>
<!--        配置集合信息-->
        <collection property="ordersList" ofType="orders">
            <id column="oid" property="id"/>
            <result column="ordertime" property="ordertime"/>
            <result column="total" property="total"/>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="userMap">
        select *,o.id oid from sys_user u,orders o where u.id = o.uid
    </select>
</mapper>

  @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = mapper.findAll();
        System.out.println(userList);

        sqlSession.close();
    }

多对多查询

用户表和角色表关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询用户同时查询出该用户的所有角色

package com.cyz.domain;

public class Role {
    private Long id;
    private String roleName;
    private String roleDesc;

    public Long getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleName=‘" + roleName + ‘\‘‘ +
                ", roleDesc=‘" + roleDesc + ‘\‘‘ +
                ‘}‘;
    }
}

package com.cyz.domain;
?
import java.util.List;
?
public class User {
    private Long id;
    private String username;
    private String email;
    private String password;
    private String phoneNum;
?
//  描述的是当前用户存在哪些订单
    private List<Orders> ordersList;
?
//    用户角色列表
    private List<Role> roleList;
?
    public List<Orders> getOrdersList() {
        return ordersList;
    }
?
    public void setOrdersList(List<Orders> ordersList) {
        this.ordersList = ordersList;
    }
?
    public List<Role> getRoleList() {
        return roleList;
    }
?
    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }
?
    public Long getId() {
        return id;
    }
?
    public void setId(Long id) {
        this.id = id;
    }
?
    public String getUsername() {
        return username;
    }
?
    public void setUsername(String username) {
        this.username = username;
    }
?
    public String getEmail() {
        return email;
    }
?
    public void setEmail(String email) {
        this.email = email;
    }
?
    public String getPassword() {
        return password;
    }
?
    public void setPassword(String password) {
        this.password = password;
    }
?
    public String getPhoneNum() {
        return phoneNum;
    }
?
    public void setPhoneNum(String phoneNum) {
        this.phoneNum = phoneNum;
    }
?
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username=‘" + username + ‘\‘‘ +
                ", email=‘" + email + ‘\‘‘ +
                ", password=‘" + password + ‘\‘‘ +
                ", phoneNum=‘" + phoneNum + ‘\‘‘ +
                ", ordersList=" + ordersList +
                ", roleList=" + roleList +
                ‘}‘;
    }
}

package com.cyz.mapper;
?
import com.cyz.domain.User;
?
import java.util.List;
?
public interface UserMapper {
    public List<User> findAll();
?
    public List<User> findUserAndRoleAll();
}

<?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.cyz.mapper.UserMapper">
    <resultMap id="userMap" type="user">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="email" property="email"/>
        <result column="password" property="password"/>
        <result column="phoneNum" property="phoneNum"/>
        <!--        配置集合信息-->
        <collection property="ordersList" ofType="orders">
            <id column="oid" property="id"/>
            <result column="ordertime" property="ordertime"/>
            <result column="total" property="total"/>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="userMap">
        select *, o.id oid
        from sys_user u,
             orders o
        where u.id = o.uid
    </select>
?
    <resultMap id="userRoleMap" type="user">
        <id column="userId" property="id"/>
        <result column="username" property="username"/>
        <result column="email" property="email"/>
        <result column="password" property="password"/>
        <result column="phoneNum" property="phoneNum"/>
        <collection property="roleList" ofType="role">
            <id column="roleId" property="id"/>
            <result column="roleName" property="roleName"/>
            <result column="roleDesc" property="roleDesc"/>
        </collection>
    </resultMap>
    <select id="findUserAndRoleAll" resultMap="userRoleMap">
        select *
        from sys_user u,
             sys_user_role ur,
             sys_role r
        where u.id = ur.userId
          and ur.roleId = r.id
    </select>
</mapper>

package com.cyz;
?
import com.cyz.domain.Orders;
import com.cyz.domain.User;
import com.cyz.mapper.OrderMapper;
import com.cyz.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
?
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
?
public class MybatisTest {
?
    @Test
    public void test() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
?
        List<Orders> ordersList = mapper.findAll();
        System.out.println(ordersList);
?
        sqlSession.close();
    }
?
    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
?
        List<User> userList = mapper.findAll();
        System.out.println(userList);
?
        sqlSession.close();
    }
?
    @Test
    public void test2() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
?
        List<User> userAndRoleAll = mapper.findUserAndRoleAll();
        for (User user : userAndRoleAll) {
            System.out.println(user);
        }
        sqlSession.close();
    }
}

知识要点

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

Mybatis篇一

原文：https://www.cnblogs.com/liang-chen-fly/p/14601066.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)