mybatis多对多查询
/mybatis-demo2/pom.xml
<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>com.jareny.it.mybatis</groupId> <artifactId>mybatis-demo</artifactId> <version>0.0.1-SNAPSHOT</version> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.9</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.2.8</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.7.5</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> </dependencies> </project>
/mybatis-demo2/src/main/resources/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>
<settings>
<setting name="LazyLoadingEnabled" value="true"/>
</settings>
<!-- 环境，可以配置多个，default：指定采用哪个环境 -->
<environments default="test">
<!-- id：唯一标识 -->
<environment id="test">
<!-- 事务管理器，JDBC类型的事务管理器 -->
<transactionManager type="JDBC" />
<!-- 数据源，池类型的数据源 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/test??useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mappers/IStudentMapper.xml" />
</mappers>
</configuration>
/mybatis-demo2/src/main/resources/log4j.properties
log4j.rootLogger=DEBUG,A1
log4j.logger.org.apache=DEBUG
log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%-d{yyyy-MM-dd HH:mm:ss,SSS} [%t] [%c]-[%p] %m%n
log4j.logger.com.jareny.it.mybatis.dao.ICountryDao=trace,console
#log4j.logger.com.jareny.it.mybatis.dao.IStudentMapper=trace,console
com.jareny.it.mybatis.entity.Student
package com.jareny.it.mybatis.entity;
import java.util.Set;
public class Student {
private Integer sid;
private String sname;
private Set<Course> courses;
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public Set<Course> getCourses() {
return courses;
}
public void setCourses(Set<Course> courses) {
this.courses = courses;
}
@Override
public String toString() {
return "Student [sid=" + sid + ", sname=" + sname + ", courses=" + courses + "]";
}
}
com.jareny.it.mybatis.entity.Course
package com.jareny.it.mybatis.entity;
import java.util.Set;
//多对多查询 ,一方的toString必须去掉多方的属性
public class Course {
private Integer cid;
private String cname;
private Set<Student> students;
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public Set<Student> getStudents() {
return students;
}
public void setStudents(Set<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "Course [cid=" + cid + ", cname=" + cname + "]";
}
}
com.jareny.it.mybatis.dao.IStudentMapper
package com.jareny.it.mybatis.dao;
import com.jareny.it.mybatis.entity.Student;
public interface IStudentMapper {
Student selectStudentById(int sid);
}
/mybatis-demo2/src/main/resources/mappers/IStudentMapper.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：命名空间，随便写，一般保证命名空间唯一 ，为了使用接口动态代理，这里必须是接口的全路径名-->
<mapper namespace="com.jareny.it.mybatis.dao.IStudentMapper">
<!-- 多对多查询 -->
<resultMap type="com.jareny.it.mybatis.entity.Student" id="studentMapper">
<id column="sid" property="sid"/>
<result column="sname" property="sname"/>
<collection property="courses" ofType="com.jareny.it.mybatis.entity.Course">
<id column="cid" property="cid"/>
<result column="cname" property="cname"/>
</collection>
</resultMap>
<!--
1.#{},预编译的方式preparedstatement，使用占位符替换，防止sql注入，一个参数的时候，任意参数名可以接收
2.${},普通的Statement，字符串直接拼接，不可以防止sql注入，一个参数的时候，必须使用${value}接收参数
-->
<select id="selectStudentById" resultMap="studentMapper">
select sid,sname,cid,cname
from student,stu_cou,course
where sid=studentId and cid=courseId and sid=#{sid}
</select>
</mapper>
com.jareny.it.test.MybatisTest
package com.jareny.it.test;
import java.io.InputStream;
import java.util.List;
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.Before;
import org.junit.Test;
import com.jareny.it.mybatis.dao.IStudentMapper;
import com.jareny.it.mybatis.entity.Student;
import com.jareny.it.mybatis.entity.User;
public class MybatisTest {
public IStudentMapper studentDao;
// public ICountryMapper2 countryMapper2;
// public INewsLabelMapper newsLabelMapper;
// public INewsLabelMapper2 newsLabelMapper2;
// public INewsLabelMapper3 newsLabelMapper3;
public SqlSession sqlSession;
@Before
public void setUp() throws Exception {
// mybatis-config.xml
String resource = "mybatis-config.xml";
// 读取配置文件
InputStream is = Resources.getResourceAsStream(resource);
// 构建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
// 获取sqlSession
sqlSession = sqlSessionFactory.openSession();
this.studentDao = sqlSession.getMapper(IStudentMapper.class);
// this.countryMapper2 = sqlSession.getMapper(ICountryMapper2.class);
// this.newsLabelMapper = sqlSession.getMapper(INewsLabelMapper.class);
// this.newsLabelMapper2 = sqlSession.getMapper(INewsLabelMapper2.class);
// this.newsLabelMapper3 = sqlSession.getMapper(INewsLabelMapper3.class);
}
/**
* 这是联表查询的，两张表一起去查询
* <p>Title: testSelectCountryById</p>
* <p>Description: </p>
* @throws Exception
*/
@Test
public void testSelectStudentById() throws Exception {
Student student= studentDao.selectStudentById(1);
System.out.println(student.toString());
}
}
运行sql
/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Version : 80015
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 80015
File Encoding : 65001
Date: 2019-08-05 00:17:55
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for country
-- ----------------------------
DROP TABLE IF EXISTS `country`;
CREATE TABLE `country` (
`cid` int(5) NOT NULL AUTO_INCREMENT,
`cname` varchar(20) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of country
-- ----------------------------
INSERT INTO `country` VALUES ('1', 'USA');
INSERT INTO `country` VALUES ('2', 'England');
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(5) NOT NULL AUTO_INCREMENT,
`cname` varchar(30) DEFAULT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'JAVASE');
INSERT INTO `course` VALUES ('2', 'JAVAEE');
INSERT INTO `course` VALUES ('3', 'Android');
-- ----------------------------
-- Table structure for minister
-- ----------------------------
DROP TABLE IF EXISTS `minister`;
CREATE TABLE `minister` (
`mid` int(5) NOT NULL AUTO_INCREMENT,
`mname` varchar(20) NOT NULL,
`countryId` int(5) DEFAULT NULL,
PRIMARY KEY (`mid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of minister
-- ----------------------------
INSERT INTO `minister` VALUES ('1', 'aaa', '1');
INSERT INTO `minister` VALUES ('2', 'bbb', '1');
INSERT INTO `minister` VALUES ('3', 'ccc', '1');
INSERT INTO `minister` VALUES ('4', 'ddd', '2');
INSERT INTO `minister` VALUES ('5', 'eee', '2');
-- ----------------------------
-- Table structure for newslabel
-- ----------------------------
DROP TABLE IF EXISTS `newslabel`;
CREATE TABLE `newslabel` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`pid` int(5) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of newslabel
-- ----------------------------
INSERT INTO `newslabel` VALUES ('1', '娱乐新闻', '0');
INSERT INTO `newslabel` VALUES ('2', '体育新闻', '0');
INSERT INTO `newslabel` VALUES ('3', 'NBA', '2');
INSERT INTO `newslabel` VALUES ('4', 'CBA', '2');
INSERT INTO `newslabel` VALUES ('5', '火箭', '3');
INSERT INTO `newslabel` VALUES ('6', '湖人', '3');
INSERT INTO `newslabel` VALUES ('7', '东莞银行', '4');
INSERT INTO `newslabel` VALUES ('8', '北京金隅', '4');
INSERT INTO `newslabel` VALUES ('9', '青岛双星', '4');
INSERT INTO `newslabel` VALUES ('10', '港台明星', '1');
INSERT INTO `newslabel` VALUES ('11', '内地明星', '1');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(30) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'zhangsan');
INSERT INTO `student` VALUES ('2', 'lisi');
INSERT INTO `student` VALUES ('3', 'wangwu');
-- ----------------------------
-- Table structure for stu_cou
-- ----------------------------
DROP TABLE IF EXISTS `stu_cou`;
CREATE TABLE `stu_cou` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`studentId` int(5) NOT NULL,
`courseId` int(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of stu_cou
-- ----------------------------
INSERT INTO `stu_cou` VALUES ('1', '1', '1');
INSERT INTO `stu_cou` VALUES ('2', '1', '2');
INSERT INTO `stu_cou` VALUES ('3', '2', '1');
INSERT INTO `stu_cou` VALUES ('4', '2', '3');
-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`id` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`user_name` varchar(32) DEFAULT NULL,
`password` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(10) DEFAULT NULL,
`sex` int(2) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`created` datetime DEFAULT NULL,
`updated` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1', 'aaa', '123456', 'zhangsan', '22', '1', '1990-01-04', '2019-08-04 17:44:22', '2019-08-04 17:44:24');
