使用mybatis中的接口代理扫描进行多表查询

最新推荐文章于 2024-07-27 13:31:57 发布

unoliiskam

最新推荐文章于 2024-07-27 13:31:57 发布

阅读量1k

点赞数 36

文章标签： mybatis java 数据库

本文链接：https://blog.csdn.net/2301_76908023/article/details/140698029

版权

一、建表

-- ----------------------------

-- Table structure for orders

-- ----------------------------

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (

`id` int NOT NULL AUTO_INCREMENT COMMENT '订单ID',

`ordertime` bigint NOT NULL,

`total` double NOT NULL COMMENT '总共订单数量',

`uuid` int NOT NULL COMMENT '用户的ID',

PRIMARY KEY (`id`),

KEY `user_orders` (`uuid`),

CONSTRAINT `user_orders` FOREIGN KEY (`uuid`) REFERENCES `user` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 COMMENT='订单表';

-- ----------------------------

-- Records of orders

-- ----------------------------

INSERT INTO `orders` VALUES ('1', '20211112', '12.5', '1');

INSERT INTO `orders` VALUES ('2', '20211115', '12.5', '3');

INSERT INTO `orders` VALUES ('3', '20311115', '12.5', '2');

INSERT INTO `orders` VALUES ('4', '20221213', '15', '2');

INSERT INTO `orders` VALUES ('5', '20221212', '15', '1');

-- ----------------------------

-- Table structure for role

-- ----------------------------

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (

`id` int NOT NULL AUTO_INCREMENT COMMENT '角色ID',

`roleName` varchar(50) NOT NULL COMMENT '角色名称',

`roleDesc` varchar(100) NOT NULL COMMENT '角色的描述',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COMMENT='角色表';

-- ----------------------------

-- Records of role

-- ----------------------------

INSERT INTO `role` VALUES ('1', '商家', '负责卖商品的商家');

INSERT INTO `role` VALUES ('2', '买家', '负责买商品的顾客');

INSERT INTO `role` VALUES ('3', '管理员', '负责商品的管理问题');

-- ----------------------------

-- Table structure for user

-- ----------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`id` int NOT NULL AUTO_INCREMENT COMMENT '用户ID',

`username` varchar(50) NOT NULL COMMENT '用户名',

`password` varchar(50) NOT NULL COMMENT '密码',

`birthday` bigint NOT NULL COMMENT '生日',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 COMMENT='用户表单';

-- ----------------------------

-- Records of user

-- ----------------------------

INSERT INTO `user` VALUES ('1', 'aa', '1233', '20221213');

INSERT INTO `user` VALUES ('2', 'bb', '2133', '20211212');

INSERT INTO `user` VALUES ('3', 'cc', '2133', '10211212');

INSERT INTO `user` VALUES ('4', 'xx', '12343', '20111213');

INSERT INTO `user` VALUES ('5', 'yy', '12343', '20111213');

-- ----------------------------

-- Table structure for user_role

-- ----------------------------

DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role` (

`userId` int NOT NULL COMMENT '用户id',

`roleId` int NOT NULL COMMENT '角色id',

KEY `user_userId` (`userId`),

KEY `role_roleId` (`roleId`),

CONSTRAINT `role_roleId` FOREIGN KEY (`roleId`) REFERENCES `role` (`id`),

CONSTRAINT `user_userId` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='中间表';

-- ----------------------------

-- Records of user_role

-- ----------------------------

INSERT INTO `user_role` VALUES ('1', '2');

INSERT INTO `user_role` VALUES ('2', '1');

INSERT INTO `user_role` VALUES ('3', '1');

INSERT INTO `user_role` VALUES ('3', '3');

INSERT INTO `user_role` VALUES ('3', '2');

二、java类型中关于Date类型转换和数据库数据类型之间的转换

//可以直接使用

package com.jiazhong.handle;
//将日期类型转换的工具类
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>
    <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/mybatis?serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--映射文件匹配-->
 <!--映射文件匹配    一个匹配一个-->
<!--        <mapper resource="mapper/UserMapper.xml"/>-->
<!--        <mapper resource="mapper/OrderMapper.xml"/>-->
        <mapper resource="mapper/UserMapperAndRole.xml"/>
    </mappers>
</configuration>

四、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">
<!--接口代理映射，所以OrderMapper接口-->

一对一：

mapper namespace="com.jiazhong.Dao.moretableDao.OrderMapper">
    <resultMap id="orderMap" type="com.jiazhong.pojo.moreTable.Order">
        <!--手动去指定字段与实体属性的映射关系-->
        <!--column:字段名称  property:实体属性的名称-->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <!--User表集合--> <!--对应的JavaBean地址-->
        <association property="userList" 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>
    <!--sql语句-->  <!--id指的接口-->  <!--resultMap保持一致-->
    <select id="findAll" resultMap="orderMap">
        select *,o.id oid from orders o,user u where o.uuid=u.id
    </select>

</mapper>

一对多：

mapper namespace="com.jiazhong.Dao.moretableDao.OrderMapper">
    <resultMap id="userMap" type="com.jiazhong.pojo.moreTable.User">
        <!--手动去指定字段与实体属性的映射关系-->
        <!--column:字段名称  property:实体属性的名称-->
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <!--Order表集合--> <!--对应的JavaBean地址-->
        <collection property="orderList" ofType="com.jiazhong.pojo.moreTable.Order">
            <!--User表-->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        </collection>
    </resultMap>
    <!--sql语句-->  <!--id指的接口-->  <!--resultMap保持一致-->
    <select id="findAllUser" resultMap="userMap">
        select *,o.id oid from user u,orders o where o.uuid=u.id
    </select>

</mapper>

多对多：

<!--接口代理映射，所以OrderMapper接口-->
<mapper namespace="com.jiazhong.Dao.moretableDao.OrderMapper">
    <resultMap id="userRoleMap" type="com.jiazhong.pojo.moreTable.User">
        <!--手动去指定字段与实体属性的映射关系-->
        <!--column:字段名称  property:实体属性的名称-->
        <!--User表-->
        <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">
            <!--Role表-->
        <id column="id" property="id"></id>
        <result column="roleName" property="roleName"></result>
        <result column="roleDesc" property="roleDesc"></result>
        </collection>
    </resultMap>
    <!--sql语句-->  <!--id指的接口-->  <!--resultMap保持一致-->
    <select id="findUserAndRoleAll" resultMap="userRoleMap">
        select * from user u,role r,user_role ur
        where ur.userId=u.id
        and r.id=ur.roleId
    </select>

</mapper>

五、接口代理

public interface OrderMapper {
    //一对一
    public List<Order> findAll();
    //一对多
    public List<User> findAllUser();
    //多对多
    public List<User> findUserAndRoleAll();
}

六、测试文件

@Test
//一对一查询
public void select() throws IOException {
    //配置日志
    Properties properties = new Properties();
    FileInputStream fileInputStream = new FileInputStream("src/main/resources/config/log4j.properties");
    properties.load(fileInputStream);
    PropertyConfigurator.configure(properties);
    //连接核心配置文件
    InputStream resourceAsStream = Resources.getResourceAsStream("config/mybatis_config1.xml");
    //获取一个工厂对象
    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
    //通过工厂获取一个对象
    SqlSession sqlSession = build.openSession();
    //获取接口
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    //调用接口中的方法
    List<Order> all = orderMapper.findAll();
    for (Order stu:all){
        System.out.println("信息：" + stu);
    }
}
@Test
//一对多查询
public void select1() throws IOException {
    //配置日志
    Properties properties = new Properties();
    FileInputStream fileInputStream = new FileInputStream("src/main/resources/config/log4j.properties");
    properties.load(fileInputStream);
    PropertyConfigurator.configure(properties);
    //连接核心配置文件
    InputStream resourceAsStream = Resources.getResourceAsStream("config/mybatis_config1.xml");

    //获取一个工厂对象
    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
    //通过工厂获取一个对象
    SqlSession sqlSession = build.openSession();
    //获取接口
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    //调用接口中的方法
    List<User> all = orderMapper.findAllUser();
    for (User user:all){
        System.out.println("信息：" + user);
    }
}
@Test
//多对多查询
public void select2() throws IOException {
    //配置日志
    Properties properties = new Properties();
    FileInputStream fileInputStream = new FileInputStream("src/main/resources/config/log4j.properties");
    properties.load(fileInputStream);
    PropertyConfigurator.configure(properties);
    //连接核心配置文件
    InputStream resourceAsStream = Resources.getResourceAsStream("config/mybatis_config1.xml");

    //获取一个工厂对象
    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
    //通过工厂获取一个对象
    SqlSession sqlSession = build.openSession();
    //获取接口
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    //调用接口中的方法
    List<User> userAndRoleAll = orderMapper.findUserAndRoleAll();
    for (User user:userAndRoleAll){
        System.out.println("信息：" + user);
    }
}

七、相关javaBean

一对一：

public class Order {
    private Integer id;
    private Date ordertime;
    private double total;
    private User user;

public Order() {
}

public Order(Integer id, Date ordertime, double total, User user) {
    this.id = id;
    this.ordertime = ordertime;
    this.total = total;
    this.user = user;
}

/**
 * 获取
 * @return id
 */
public Integer getId() {
    return id;
}

/**
 * 设置
 * @param id
 */
public void setId(Integer id) {
    this.id = id;
}

/**
 * 获取
 * @return ordertime
 */
public Date getOrdertime() {
    return ordertime;
}

/**
 * 设置
 * @param ordertime
 */
public void setOrdertime(Date ordertime) {
    this.ordertime = ordertime;
}

/**
 * 获取
 * @return total
 */
public double getTotal() {
    return total;
}

/**
 * 设置
 * @param total
 */
public void setTotal(double total) {
    this.total = total;
}

/**
 * 获取
 * @return user
 */
public User getUser() {
    return user;
}

/**
 * 设置
 * @param user
 */
public void setUser(User user) {
    this.user = user;
}

public String toString() {
    return "Order{id = " + id + ", ordertime = " + ordertime + ", total = " + total + ", user = " + user + "}";
}}

public class User {
   private Integer id;
   private String username;
   private String password;
   private Date birthday;

  public User() {
    }

    public User(Integer id, String username, String password, Date birthday) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.birthday = birthday;
    }

    /**
     * 获取
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * 设置
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * 获取
     * @return username
     */
    public String getUsername() {
        return username;
    }

    /**
     * 设置
     * @param username
     */
    public void setUsername(String username) {
        this.username = username;
    }

    /**
     * 获取
     * @return password
     */
    public String getPassword() {
        return password;
    }

    /**
     * 设置
     * @param password
     */
    public void setPassword(String password) {
        this.password = password;
    }

    /**
     * 获取
     * @return birthday
     */
    public Date getBirthday() {
        return birthday;
    }

    /**
     * 设置
     * @param birthday
     */
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String toString() {
        return "User{id = " + id + ", username = " + username + ", password = " + password + ", birthday = " + birthday + "}";
    }
}

一对多：

上述不变

添加

private List<Order> orderList;

并且重新生成javaBean

多对多：

建立role类

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

    public Role() {
    }

    public Role(Integer id, String roleName, String roleDesc) {
        this.id = id;
        this.roleName = roleName;
        this.roleDesc = roleDesc;
    }

    /**
     * 获取
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * 设置
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * 获取
     * @return roleName
     */
    public String getRoleName() {
        return roleName;
    }

    /**
     * 设置
     * @param roleName
     */
    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    /**
     * 获取
     * @return roleDesc
     */
    public String getRoleDesc() {
        return roleDesc;
    }

    /**
     * 设置
     * @param roleDesc
     */
    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    public String toString() {
        return "Role{id = " + id + ", roleName = " + roleName + ", roleDesc = " + roleDesc + "}";
    }
}
并且添加一个给User类