mybatis实现一对多的操作

2年前 (2022) 程序员胖胖胖虎阿

251 0 0

@DuYingJie

1.添加依赖包打坐标 pom

    <!--定义打包格式-->
    <packaging>jar</packaging>
    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

2、向数据库导入文件注意刷新查看结果

此数据库是第三单元的作为演示

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50731
 Source Host           : localhost:3306
 Source Schema         : chapter03

 Target Server Type    : MySQL
 Target Server Version : 50731
 File Encoding         : 65001

 Date: 07/03/2022 08:55:37
*/


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

Drop Database If Exists chapter03;
Create Database chapter03;
use chapter03;

-- ----------------------------
-- Table structure for class_info
-- ----------------------------
DROP TABLE IF EXISTS `class_info`;
CREATE TABLE `class_info`  (
  `cid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班级名称',
  `sum` int(11) NULL DEFAULT NULL COMMENT '总人数',
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of class_info
-- ----------------------------
INSERT INTO `class_info` VALUES (1, 'Java1801', 50);
INSERT INTO `class_info` VALUES (2, 'Java1802', 46);

-- ----------------------------
-- Table structure for class_teach
-- ----------------------------
DROP TABLE IF EXISTS `class_teach`;
CREATE TABLE `class_teach`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_id` int(11) NULL DEFAULT NULL,
  `teach_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `class_id`(`class_id`) USING BTREE,
  INDEX `teach_id`(`teach_id`) USING BTREE,
  CONSTRAINT `class_teach_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class_info` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `class_teach_ibfk_2` FOREIGN KEY (`teach_id`) REFERENCES `teach_info` (`tid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of class_teach
-- ----------------------------

-- ----------------------------
-- Table structure for stu
-- ----------------------------
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu`  (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `course` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `cardid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE,
  UNIQUE INDEX `cardid`(`cardid`) USING BTREE,
  CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`cardid`) REFERENCES `stu_card` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of stu
-- ----------------------------
INSERT INTO `stu` VALUES (1, 'ZhangSan', '20', 'Java', 1);
INSERT INTO `stu` VALUES (2, 'LiSi', '21', 'Java', 2);

-- ----------------------------
-- Table structure for stu_card
-- ----------------------------
DROP TABLE IF EXISTS `stu_card`;
CREATE TABLE `stu_card`  (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `balance` double NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of stu_card
-- ----------------------------
INSERT INTO `stu_card` VALUES (1, 1000.5);
INSERT INTO `stu_card` VALUES (2, 5000.5);

-- ----------------------------
-- Table structure for stu_class
-- ----------------------------
DROP TABLE IF EXISTS `stu_class`;
CREATE TABLE `stu_class`  (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班级名称',
  `sum` int(11) NULL DEFAULT NULL COMMENT '总人数',
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of stu_class
-- ----------------------------
INSERT INTO `stu_class` VALUES (1, 'Java1801', 50);
INSERT INTO `stu_class` VALUES (2, 'Java1802', 46);

-- ----------------------------
-- Table structure for stu_info
-- ----------------------------
DROP TABLE IF EXISTS `stu_info`;
CREATE TABLE `stu_info`  (
  `sid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '学生年龄',
  `course` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '科目',
  `classid` int(11) NOT NULL COMMENT '班级id',
  PRIMARY KEY (`sid`) USING BTREE,
  INDEX `classid`(`classid`) USING BTREE,
  CONSTRAINT `stu_info_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `stu_class` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of stu_info
-- ----------------------------
INSERT INTO `stu_info` VALUES (1, 'ZhangSan', 20, 'Java', 1);
INSERT INTO `stu_info` VALUES (2, 'LiSi', 21, 'Java', 2);
INSERT INTO `stu_info` VALUES (3, 'WangWu', 20, 'Java', 1);
INSERT INTO `stu_info` VALUES (4, 'ZhaoLiu', 19, 'Java', 1);

-- ----------------------------
-- Table structure for teach_info
-- ----------------------------
DROP TABLE IF EXISTS `teach_info`;
CREATE TABLE `teach_info`  (
  `tid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `tname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '教师年龄',
  `course` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程',
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of teach_info
-- ----------------------------
INSERT INTO `teach_info` VALUES (1, 'ZhangSan', 31, 'JavaSE');
INSERT INTO `teach_info` VALUES (2, 'LiSi', 33, 'JavaWeb');

SET FOREIGN_KEY_CHECKS = 1;

3、配置mybatis-config.xml和db.properties

注意位置是在resources下 扫描包的位置

密码和账号改为自己的即可

db.properties

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/chapter03
mysql.username=root
mysql.password=010624

mybatis-config.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>
    <properties resource="db.properties">
    </properties>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${mysql.driver}"/>
                <property name="url" value="${mysql.url}"/>
                <property name="username" value="${mysql.username}"/>
                <property name="password" value="${mysql.password}"/>
            </dataSource>
        </environment>
    </environments>
<!--    指定映射包-->
    <mappers>
        <package name="cn.edu.hncj.dao"/>
    </mappers>
</configuration>

4、目录结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-87BSGazr-1653520571753)(C:\Users\dyj\AppData\Roaming\Typora\typora-user-images\1653396119025.png)]

5、根据数据库创建pojo

这个自己创建即可注意在stuclass中添加private List stuInfoList;

6、创建maper

StuInfoMapper

package cn.edu.hncj.dao;

import cn.edu.hncj.pojo.StuInfo;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

public interface StuInfoMapper {
    @Select("select * from  stu_info where sid =#{sid}")
    @Results({
            @Result(id = true,property = "sid",column = "sid"),
            @Result(property = "sname",column = "sname"),
            @Result(property = "age",column = "age"),
            @Result(property ="course",column = "course"),
            @Result(property = "cid",column = "classid")
})
    StuInfo selectStuInfoById(int sid);
    @Update("UPDATE stu_info set sname=#{sname} , age=#{age},course=#{course} where sid =#{sid}")
    int updateStudent(StuInfo stuInfo);
    @Select("select *from stu_info where classid=#{classid}")
    @Results({
            @Result(id = true,property = "sid",column = "sid"),
            @Result(property = "sname",column = "sname"),
            @Result(property = "age",column = "age"),
            @Result(property ="course",column = "course"),
            @Result(property = "cid",column = "classid")
    })
    List<StuInfo> selectStuByCid(int classid);

//    @Select("select *from stu_info")
//    @Results(
//            id = "studentInfoMap",value = {
//            @Result(id = true,property = "sid",column = "sid"),
//            @Result(property = "sname",column = "sname"),
//            @Result(property = "age",column = "age"),
//            @Result(property ="course",column = "course"),
//            @Result(property = "classid",column = "classid",
//            one = @One(select="com.dyj.demo.mapper.StuInfoMapper.findByClassid",fetchType = FetchType.EAGER))
//
//    })
//    List<StuInfo> findALl();
//    @Select("select *from stu_info where classid={classid}")
//    StuInfo findByClassid(int classid);

}

7、创建工具类

MybatisUtils

package cn.edu.hncj.utils;
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;

//从 SqlSessionFactory 中获取 SqlSession
public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //获取SqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //从 SqlSessionFactory 中获取 SqlSession
    public static SqlSession getSqlSession() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        return sqlSession;
    }
}

8、编写测试类

import cn.edu.hncj.dao.StuInfoMapper;
import cn.edu.hncj.pojo.StuInfo;
import cn.edu.hncj.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MybatisTest {
    @Test
    public void findStuInfoBySidTest(){
        SqlSession session= MybatisUtils.getSqlSession();
        StuInfoMapper mapper=session.getMapper(StuInfoMapper.class);
        StuInfo stuInfo = mapper.selectStuInfoById(2);
        System.out.println(stuInfo.toString());
        session.close();
    }
    @Test
    public void updateStuClassTest(){
        SqlSession session= MybatisUtils.getSqlSession();
        StuInfoMapper mapper=session.getMapper(StuInfoMapper.class);
        StuInfo stuInfo=new StuInfo();
        stuInfo.setAge("22");
        stuInfo.setSid(4);
        stuInfo.setCourse("javaee");
        stuInfo.setSname("张朝龙");
        mapper.updateStudent(stuInfo);
        session.commit();
        session.close();

    }
    @Test
    public void findStuInfoByCidTest(){
        SqlSession session= MybatisUtils.getSqlSession();
        StuInfoMapper mapper=session.getMapper(StuInfoMapper.class);
        List<StuInfo> stuInfos = mapper.selectStuByCid(2);
        for (StuInfo stuInfo:stuInfos){
            System.out.println(stuInfo);
        }
        session.close();
    }
}

9、结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TEVkC1Re-1653520571754)(C:\Users\dyj\AppData\Roaming\Typora\typora-user-images\1653396512041.png)]