Mybatis实现一对一，一对多，多对多查询，所用到的mysql建表语句，Java项目里面的配置文件

员不是圆

于 2024-07-25 20:48:03 发布

阅读量334

点赞数 10

文章标签： mybatis mysql java

本文链接：https://blog.csdn.net/2302_77031506/article/details/140698868

版权

一、这次所用表有四个，分别为user用户表，order订单表，role角色表，user_role中间表；其中user与order是一对多的关系，一个用户可以有多个订单，一个订单只属于一个用户；user与role是多对多的关系，一个用户可以有多个角色，一个角色也可以被多个用户拥有。

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='用户表单';

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');

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='角色表';

INSERT INTO `role` VALUES ('1', '商家', '负责卖商品的商家');
INSERT INTO `role` VALUES ('2', '买家', '负责买商品的顾客');
INSERT INTO `role` VALUES ('3', '管理员', '负责商品的管理问题');

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='订单表';


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');

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='中间表';


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

二、java中时间类型和数据库数据类型之间的转换

java类型转和数据库数据类型之间的转换

------------------------------------------------------**/
package com.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;
}
}

三、核心配置文件 mybatis-config1.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>
<!--   注册类型处理器，将实现后端和MySQL数据库转换时间类型的方法所在类添加过来，会自动执行转换功能 -->
    <typeHandlers>
        <typeHandler handler="com.handle.DateTypeHandler"></typeHandler>
    </typeHandlers>
    <!-- 配置链接数据库的环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_test?serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
<!--      这些映射文件不能同时存在  -->
        <!--        1对1-->
        <!--        <mapper resource="mapper/MoreTableMapper.xml"/>-->
        <!--        1对多-->
        <!--        <mapper resource="mapper/MoreTableUserMapper.xml"/>-->
        <!--        多对多-->
        <mapper resource="mapper/MoreTableUserToRoleMapper.xml"/>
    </mappers>
</configuration>

四、Mapper映射文件

1.一对一的映射文件 MoreTableMapper.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="com.Dao.moretableDao.OrderMapper">
<!--    运用键值对的形式把sql语句一一对应，准确的添加到所对应的JavaBean中，type中所书写的是对应的JavaBean的目录-->
    <resultMap id="orderMap" type="com.JavaBean.moretable.Order">
        <!--手动去指定字段与实体属性的映射关系-->
        <!--
        column: 数据表的字段名称
        property: 实体属性的名称
        1对1查询
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>

        <!--
        property: 当前实体(order)中的属性名称(private User user)
        javaType: 当前实体(order)中的属性类型(User)
           一对一查询使用到的标签类为association，javaType
        -->
        <association property="user" javaType="com.JavaBean.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>
<!--    该处id里面应填入与之映射的接口里面对应的方法名，resultMap是因为需要返回值类型为集合，该处命名无关紧要
但是需注意命名须于第6行中标签中的id值一致-->
    <select id="findAll" resultMap="orderMap">
        select *, o.id oid
        from orders o,
             user
        where o.uuid = user.id;
    </select>
</mapper>

2.一对多 MoreTableUserMapper.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="com.Dao.moretableDao.OrderMapper">
    <resultMap id="userMap" type="com.JavaBean.moretable.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，ofType-->
        <collection property="orderList" ofType="com.JavaBean.moretable.Order">
            <id column=" oid" property="id"></id>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>
    <select id="oneTouser" resultMap="userMap">
        select *, o.id oid
        from user,
             orders o
        where user.id = o.uuid;
    </select>
</mapper>

3.多对多 MoreTableUserToRoleMapper.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="com.Dao.moretableDao.OrderMapper">
    <resultMap id="userMap" type="com.JavaBean.moretable.User">
        <id column="userId" 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.JavaBean.moretable.Role">
           <id column="roleId" property="id" ></id>
            <result column="roleName" property="roleName"></result>
            <result column="roleDesc" property="roleDesc"></result>
        </collection>
    </resultMap>
    <select id="findUserToRole" resultMap="userMap">
        select * from user u,user_role ur ,role r where u.id=ur.userId and r.id=ur.roleId;
    </select>
</mapper>

五、pom.xml加载依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xmlns="http://maven.apache.org/POM/4.0.0"
         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_Study</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <!--数据库相关必要依赖-->
        <!-- jdbc-mysql驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>
                mysql-connector-java
            </artifactId>
            <version>
                8.0.22
            </version>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>
                1.2.12
            </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.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.example</groupId>
            <artifactId>Mybatis_Study</artifactId>
            <version>1.0-SNAPSHOT</version>
            <scope>test</scope>
        </dependency>


    </dependencies>

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

</project>

建表的格式为

OrderMapper文件中数据

package com.Dao.moretableDao;

import com.JavaBean.moretable.Order;
import com.JavaBean.moretable.User;

import java.util.List;

public interface OrderMapper {
    //order表与user表之间实现一对一查询，一个订单只能属于一个用户
    public List<Order> findAll();

    //user表与order表实现一对多查询，一个用户有多个订单
    public List<User> oneTouser();

    //user表与role表之间实现多对多查询，一个用户可以有多个角色，一个角色也可以被多个用户拥有；
    public List<User> findUserToRole();
}

DateTypeHandler文件中数据为上述第二点（java中时间类型和数据库数据类型之间的转换）中的内容。

moretable文件夹中的数据是数据库中各表里面的内容，建表时要一致。如下：

test测试类数据

import com.Dao.StuDao;
import com.Dao.moretableDao.OrderMapper;
import com.JavaBean.moretable.Order;
import com.JavaBean.moretable.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.apache.log4j.PropertyConfigurator;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Properties;

public class testMoreTable {
    @Test
    public void findAll() 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-config1.xml");
        SqlSessionFactory fac = new SqlSessionFactoryBuilder().build(in);
        SqlSession sqlSession = fac.openSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> all = mapper.findAll();
        for (int i = 0; i < all.size(); i++) {
            Order order = all.get(i);
            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-config1.xml");
        SqlSessionFactory fac = new SqlSessionFactoryBuilder().build(in);
        SqlSession sqlSession = fac.openSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List<User> users = mapper.oneTouser();
        for (User user : users) {
            System.out.println("user = " + user);
        }
    }
    @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-config1.xml");
        SqlSessionFactory fac = new SqlSessionFactoryBuilder().build(in);
        SqlSession sqlSession = fac.openSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List<User> userToRole = mapper.findUserToRole();
        for (User user : userToRole) {
            System.out.println("user = " + user);
        }
    }


}

员不是圆

关注

10
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Mybatis实现一对一，一对多，多对多查询，所用到的mysql建表语句，Java项目里面的配置文件

一、这次所用表有四个，分别为user用户表，order订单表，role角色表，user_role中间表；其中user与order是一对多的关系，一个用户可以有多个订单，一个订单只属于一个用户；user与role是多对多的关系，一个用户可以有多个角色，一个角色也可以被多个用户拥有。DateTypeHandler文件中数据为上述第二点（java中时间类型和数据库数据类型之间的转换）中的内容。moretable文件夹中的数据是数据库中各表里面的内容，建表时要一致。五、pom.xml加载依赖。
复制链接

扫一扫