Mybatis实现多表查询

m0_74212034

于 2024-07-25 20:25:05 发布

阅读量678

点赞数 7

文章标签： mybatis 数据库

本文链接：https://blog.csdn.net/m0_74212034/article/details/140698610

版权

1.建表如图所示

2.java类型和数据库类型之间的转化

 // 将java类型转换成数据库需要的类型
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException, 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;
    }

3.核心配置文件

<?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>
<!--    多表查询-->
<typeHandlers>
    <typeHandler handler="com.jiazhong.handle.DateTypeHandler"></typeHandler>
</typeHandlers>
<environments default="develop">
    <environment id="develop">
        <transactionManager type="JDBC"></transactionManager>
        <dataSource type="POOLED">
            <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://localhost:3306/duobiaochaxun?characterEncoding=utf-8"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
        </dataSource>
    </environment>
</environments>
<mappers>
<!--&lt;!&ndash;    一对一映射&ndash;&gt;-->
<!--    <mapper resource="mapper/MoreTableMapper.xml"/>-->
<!--&lt;!&ndash;    一对多映射&ndash;&gt;-->
<!--    <mapper resource="mapper/MoreTableUserMapper.xml"/>-->
<!--&lt;!&ndash;    多对多映射&ndash;&gt;-->
    <mapper resource="mapper/MoreTableUserAndRollMapper.xml"/>
</mappers>
</configuration>

4.三种查询及测试过程

(1)一对一查询

<?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">
<!--接口代理映射，所以OrderMapper接口-->
<mapper namespace="com.jiazhong.dao.moretableDao.OrderMapper">
    <resultMap id="orderMap" type="com.jiazhong.pojo.moretable.Order">
        <!--手动去指定字段与实体属性的映射关系-->
        <!--
            column: 数据表的字段名称
            property: 实体属性的名称
            1对1查询
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <!--这里是resultMap的第一种配置方法-->
        <!--<result column="uuid" property="user.id"></result>
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>-->
        <!--
         property: 当前实体(order)中的属性名称(private User user)
         javaType: 当前实体(order)中的属性类型(User)javabean中
     -->
        <association property="user" javaType="com.jiazhong.pojo.moretable.User">
            <id column="uuid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
            <result column="birthday" property="birthday"></result>
        </association>
    </resultMap>
    <!--数据库的一对一查询-->
    <select id="findAll" resultMap="orderMap">
        select *,o.id oid  from orders o, user u where o.uuid = u.id
    </select>

</mapper>

（2）一对多查询

<?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">
<!--接口代理映射，所以OrderMapper接口-->
<mapper namespace="com.jiazhong.dao.moretableDao.OrderMapper">
    <resultMap id="userMap" type="com.jiazhong.pojo.moretable.User">
        <!--手动去指定字段与实体属性的映射关系-->
        <!--
            column: 数据表的字段名称
            property: 实体属性的名称javabean中
            1对多查询
        -->
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>

        <!--这里是resultMap的第一种配置方法-->
        <!--<result column="uuid" property="user.id"></result>
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>-->
        <!--
         property: 当前实体(order)中的属性名称(private User user)
         javaType: 当前实体(order)中的属性类型(User)
     -->
        <collection property="orderList" ofType="com.jiazhong.pojo.moretable.Order">
            <id column="oid" property="id"></id>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>
    <!--数据库的一对多查询-->
    <select id="findAllUser" resultMap="userMap">
        select *,o.id oid  from  user u ,orders o where o.uuid = u.id
    </select>
</mapper>
（3）多对多查询

<?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">
<!--接口代理映射，所以OrderMapper接口-->
<mapper namespace="com.jiazhong.dao.moretableDao.OrderMapper">
    <resultMap id="userRoleMap" type="com.jiazhong.pojo.moretable.User">
        <!--手动去指定字段与实体属性的映射关系-->
        <!--
            column: 数据表的字段名称
            property: 实体属性的名称javabean中
            1对多查询
        -->
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <collection property="roleList" ofType="com.jiazhong.pojo.moretable.Role">
            <id column="roleId" property="id"></id>
            <result column="rolename" property="roleName"></result>
            <result column="roleDesc" property="roleDesc"></result>
        </collection>
    </resultMap>
    <!--数据库的多对多查询-->
    <select id="findUserAndRoleAll" resultMap="userRoleMap">
        select * from  user u ,user_role ur,role r where u.id=ur.userId and r.id=ur.roleId
    </select>
</mapper>

（4）测试源码

public class testMoreTable {
    @Test
    public void oneToOne() throws IOException {//一对一
        //配置日志信息
        Properties properties = new Properties();
        FileInputStream fileInputStream = new FileInputStream("src/main/resources/config/log4j.properties");
        properties.load(fileInputStream);
        PropertyConfigurator.configure(properties);
        InputStream in = Resources.getResourceAsStream("config/mybatis-config2.xml");
        //从配置文件中获取一个工厂对象
        SqlSessionFactory fac = new SqlSessionFactoryBuilder().build(in);
        //通过工厂获取sqlsession对象
        SqlSession sqlSession = fac.openSession();
        //sqlsession对象执行映射mapper文件中的sql语句
        OrderMapper  ordermapper= sqlSession.getMapper(OrderMapper.class);
        List<Order> all = ordermapper.findAll();
        for(Order order:all){
            System.out.println("order = " + order);
        }
    }

    @Test
    public void oneToMore() throws IOException {//一对多
        //配置日志信息
        Properties properties = new Properties();
        FileInputStream fileInputStream = new FileInputStream("src/main/resources/config/log4j.properties");
        properties.load(fileInputStream);
        PropertyConfigurator.configure(properties);
        InputStream in = Resources.getResourceAsStream("config/mybatis-config2.xml");
        //从配置文件中获取一个工厂对象
        SqlSessionFactory fac = new SqlSessionFactoryBuilder().build(in);
        //通过工厂获取sqlsession对象
        SqlSession sqlSession = fac.openSession();
        //sqlsession对象执行映射mapper文件中的sql语句
        OrderMapper  ordermapper= sqlSession.getMapper(OrderMapper.class);
        List<User> allUser = ordermapper.findAllUser();
        for(User order:allUser){
            System.out.println("user = " + order);
        }
    }
    @Test
    public void MoreToMore() throws IOException {//多对多
        //配置日志信息
        Properties properties = new Properties();
        FileInputStream fileInputStream = new FileInputStream("src/main/resources/config/log4j.properties");
        properties.load(fileInputStream);
        PropertyConfigurator.configure(properties);
        InputStream in = Resources.getResourceAsStream("config/mybatis-config2.xml");
        //从配置文件中获取一个工厂对象
        SqlSessionFactory fac = new SqlSessionFactoryBuilder().build(in);
        //通过工厂获取sqlsession对象
        SqlSession sqlSession = fac.openSession();
        //sqlsession对象执行映射mapper文件中的sql语句
        OrderMapper  ordermapper= sqlSession.getMapper(OrderMapper.class);
        List<User> userAndRoleAll = ordermapper.findUserAndRoleAll();
        for(User order:userAndRoleAll){
            System.out.println("user = " + order);
        }
    }

}