mysql查询⼀对多_Mybatis⼀对⼀、⼀对多、多对多查询。
+MYSQL
场景：使⽤三张数据表：student学⽣表、teacher教师表、position职位表
⼀个学⽣可以有多为⽼师、⼀位⽼师可以有多个学⽣、但是⼀个⽼师只能有⼀个职位：教授、副教授、讲师；但是⼀个职位可以有多个⽼师：例如教授可以多⼈
这⾥则产⽣了：
⼀对⼀关系，从⽼师⾓度：⽼师对职位⼀对⼀
⼀对多关系，从职位⾓度：职位对⽼师⼀对多
多对多关系：查被教授教导的所有学⽣(⾸先职位对⽼师⼀对多，⽼师再对学⽣再对多、这⾥便有了⼀对多对多)
数据表：
⽼师表
CREATE TABLE `tb_teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t_no` varchar(20) DEFAULT NULL,
`t_name` varchar(20) DEFAULT NULL,
`position_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*Data for the table `tb_teacher` */
insert  into `tb_teacher`(`id`,`t_no`,`t_name`,`position_id`) values
(1,'163314001','张⽂远',1),
(2,'163314002','赵传智',1),
(3,'163314003','风清扬',2),
(4,'163314004','王汇智',2),
(5,'163314005','汪思远',3);
学⽣表
CREATE TABLE `tb_student` (
`id` int(11) NOT NULL AUTO_INCREMENT,position职位
`t_stu_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*Data for the table `tb_student` */
insert into `tb_student`(`id`,`t_stu_name`) values
(1,'赵依'),
(5,'王武'),
(6,'马柳');
职位表
CREATE TABLE `tb_position` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t_pos_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; /*Data for the table `tb_position` */
insert into `tb_position`(`id`,`t_pos_name`) values
(1,'教授'),
(2,'副教授'),
(3,'讲师');
最后是教师学⽣关系表
CREATE TABLE `tb_stu_teach` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t_stu_id` int(11) DEFAULT NULL,
`t_teach_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8; /*Data for the table `tb_stu_teach` */
insert into `tb_stu_teach`(`id`,`t_stu_id`,`t_teach_id`) values
(1,1,1),
(2,1,2),
(3,1,3),
(4,2,2),
(5,2,3),
(6,2,4),
(7,3,3),
(8,3,4),
(9,3,5),
最后在eclipse中的⽬录结构如下：
希望您明⽩sqlMapConfig该如何配置，以及jdbc.properties和log4j的作⽤。
贴出POJO中的三个实体(注意：以下的POJO都⽤了lombok来快速⽣成setter和getter等，lomok具体使⽤，请见此⽂)：Position.java
package com.pojo;
import java.io.Serializable;
import lombok.Data;
@Data
public class Position implements Serializable {
private int id;
private String name;
private Teacher teacher;
}
Student.java
package com.pojo;
import java.io.Serializable;
import java.util.List;
import lombok.Data;
@Data
public class Student implements Serializable {
private String id;
private String name;
private List list;
}
Teacher.java
package com.pojo;
import java.io.Serializable;
import java.util.List;
import lombok.Data;
@Data
public class Teacher implements Serializable {
private int id;
private String no;
private String name;
private List studentList;
private Position pos;
}
注意：关系表不⽤以实体表⽰出来，表⽰外键关系的ID也不⽤写在实体中(⼀般我们也不使⽤外键)再贴另⼀个MybatisUtil.java⼯具类
package com.util;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MyBatisUtil {
private static SqlSessionFactory sqlSessionFactory = null;
static {
String resource = "l";
/
/ ⾸先要加载核⼼配置⽂件：从classpath下开始。
InputStream in;
try {
in = ResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); } catch (IOException e) {
throw new Message());
}
}
public static SqlSession getSqlSession() {
return sqlSessionFactory.openSession();
}
public static SqlSessionFactory getSqlSessionFactory() {
return sqlSessionFactory;
}
}
⼀对⼀：⽼师对职位
SELECT *
FROM tb_teacher t
LEFT JOIN tb_position p
ON t.position_id = p.id
where t.id = #{id}
SELECT *
FROM tb_teacher t
LEFT JOIN tb_position p
ON t.`position_id` = p.id
TeacherMapper.java接⼝
package com.mapper;
import java.util.List;
import com.pojo.Teacher;
public interface TeacherMapper {
public List queryTeacherPositionResultMap();
public Teacher queryTeacherPositionResultMapById(Integer id); }
测试⼀对⼀：
st;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。