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,
  `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,'赵依'),

(2,'钱迩'),

(3,'张山'),

(4,'李石'),

(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),

(10,4,4),

(11,4,5),

(12,4,1);

最后在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<Teacher> 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<Student> 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 = "sqlMapConfig.xml";
		// 首先要加载核心配置文件:从classpath下开始找。
		InputStream in;
		try {
			in = Resources.getResourceAsStream(resource);
			
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		} catch (IOException e) {
			throw new RuntimeException(e.getMessage());
		}	
	}
	
	public static SqlSession getSqlSession() {
		
		return sqlSessionFactory.openSession();
	}
	
	public static SqlSessionFactory getSqlSessionFactory() {
		return sqlSessionFactory;
	}
}

一对一:老师对职位

TeacherMapper.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.mapper.TeacherMapper">
	<resultMap type="Teacher" id="teacherPositionResultMap">
		<id property="id" column="id"/>
		<result property="no" column="t_no"/>
		<result property="name" column="t_name"/>
		<!-- association:配置的一对一属性 -->
		<!-- property:名字
			 javaType:类型
		 -->
		<association property="pos" javaType="Position">
			<id property="id" column="id"/>
			<result property="name" column="t_pos_name"/>
		</association>
	</resultMap>
	
	<!-- 一对一关联查询,查询老师及其对应的职位 -->
	<!-- 注意:id不能相同,当多个值传入,比如包装类的时候,我们才能够用SQL片段的形式来做if判断,单个值是不行的 -->
	<select id="queryTeacherPositionResultMapById" resultMap="teacherPositionResultMap" parameterType="Integer">
		SELECT *
		FROM tb_teacher t
		LEFT JOIN tb_position p
		ON t.position_id = p.id
		where t.id = #{id}
	</select>

	<select id="queryTeacherPositionResultMap" resultMap="teacherPositionResultMap">
		SELECT *
		FROM tb_teacher t
		LEFT JOIN tb_position p
		ON t.`position_id` = p.id
	</select> 
</mapper>

TeacherMapper.java接口

package com.mapper;

import java.util.List;

import com.pojo.Teacher;

public interface TeacherMapper {
	public List<Teacher> queryTeacherPositionResultMap();
	
	public Teacher queryTeacherPositionResultMapById(Integer id);
}

测试一对一:

package com.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.mapper.TeacherMapper;
import com.pojo.Teacher;
import com.util.MyBatisUtil;

public class TestOneToOne {
	@Test
	public void testOneToOne() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		System.err.println(sqlSession);
		
		TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
		
		List<Teacher> list = teacherMapper.queryTeacherPositionResultMap();
		
		System.out.println(list);
		
		Teacher teacher = teacherMapper.queryTeacherPositionResultMapById(1);
		System.out.println(teacher);
	}
}

 

一对多:职位对老师

PositionMapper.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.mapper.PositionMapper"> 
	<resultMap type="Position" id="positionTeacherResultMap">
		<id property="id" column="id"/>
		<result property="name" column="t_pos_name"/> <!-- t_name -->
		<!-- 
		property同association中的一样是属性名称(javaBean中的);
		javaType也同association中的是类型,
		最后多了一个OfType,因为一对多,不像一对一是单个的!我们这里是List集合,list和List都可以。
		一对多其中是放的一个集合所以这个是集合的泛型的类型,这里我们的list中放的是Teacher:
		所以这里是Teacher。
		 -->
		<collection property="teacherList" javaType="List" ofType="Teacher" >
			<!-- 
				一对多出现的问题:
					当数据库表中,主表的主键id和明细表的 ...
					当表中的字段名相同时怎么办?多表联查?
					
					注意:Mybatis中做多表联查的时候,不管是
					一对一、一对多、一对多对多:多对多:
					都不能有字段重名的情况:不管是主键还是普通字段。
					一旦字段重名的话,就会造成数据少自动赋值,或者覆盖,甚至重复赋值!
					规避和解决此类问题的方法:
						1.尽量不要表间重名,mybatis里处理起来很麻烦!id和普通字段都是。
						但是在表多的时候,很难不会出现字段重名的情况。主键id最容易重名!
						那么就要用以下的办法了!
						
						2.在mybatis中写原生SQL进行查询的时候,查的字段尽可能的少,这
						也影响速率,强烈禁止使用*,用多少查多少!这样也能及时发现字段重
						名的情况!
						
						3.最后如果真的需要查出重名的字段,并且修改数据库字段名造成的更改
						过大,这里推荐的方式是给字段取别名,在写resultMap映射的时候,其
						中的column属性就填写SQL语句中查出字段取的别名,这样就能解决重复
						问题了!
			 -->
			<id property="id" column="t_id"/>
			<result property="no" column="t_no"/>
			<result property="name" column="t_name"/>	
		</collection>
	</resultMap>
	
	<select id="queryPositionTeacherResultMapById" resultMap="positionTeacherResultMap" 
		parameterType="Integer">
		<!-- 
		SELECT *
		FROM tb_position p
		LEFT JOIN tb_teacher t
		ON p.id = t.position_id
		WHERE p.id = #{id}
		-->
		
		SELECT 
		p.*, 
		t.id t_id,
		t.t_name,
		t.t_no
		FROM tb_position p
		LEFT JOIN tb_teacher t
		ON p.id = t.position_id
		WHERE p.id = #{id}
	</select>
	
	<select id="queryPositionTeacherResultMap" resultMap="positionTeacherResultMap" >
		<!-- 
		SELECT *
		FROM tb_position p
		LEFT JOIN tb_teacher t
		ON p.id = t.position_id
		-->
		
		SELECT 
		p.*, 
		t.id t_id,
		t.t_name,
		t.t_no
		FROM tb_position p
		LEFT JOIN tb_teacher t
		ON p.id = t.position_id
		
	</select>
</mapper>

TeacherMapper.java接口

package com.mapper;

import java.util.List;

import com.pojo.Position;

public interface PositionMapper {
	public Position queryPositionTeacherResultMapById(Integer id);
	
	public List<Position> queryPositionTeacherResultMap();
}

测试一对多:

package com.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.mapper.PositionMapper;
import com.pojo.Position;
import com.util.MyBatisUtil;

public class TestOneToMany {
	
	@Test
	public void testOneToMany() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		PositionMapper positionMapper = sqlSession.getMapper(PositionMapper.class);
		List<Position> list = positionMapper.queryPositionTeacherResultMap();
		
		System.out.println(list);
		
		Position pos = positionMapper.queryPositionTeacherResultMapById(1);
		
		System.out.println(pos);
	}
}

多对多:职位是教授的老师教授的所有学生(一对多对多:只要你愿意可以一直对多下去...)

PositionMapper.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.mapper.PositionMapper"> 
	<resultMap type="Position" id="positionStudentResultMap">
		<!-- <id property="id" column="id"/> -->
		<result property="name" column="t_pos_name"/>
		<collection property="teacherList" javaType="List" ofType="Teacher" >
			<result property="name" column="t_name"/>	
			<collection property="studentList" javaType="List" ofType="Student">
				<result property="name" column="t_stu_name"/>
			</collection>
		</collection>
	</resultMap>
	
	<select id="selectPositionStudentByPosId" resultMap="positionStudentResultMap" parameterType="Integer">
		SELECT p.t_pos_name, t.t_name, s.t_stu_name
		FROM tb_position p
		INNER JOIN tb_teacher t ON p.id = t.position_id
		LEFT JOIN tb_stu_teach st ON st.t_teach_id = t.id
		LEFT JOIN tb_student s ON s.id = st.t_stu_id
		WHERE p.id = #{id}
	</select>
</mapper>

PositionMapper.java接口

package com.mapper;

import com.pojo.Position;

public interface PositionMapper {
	public Position selectPositionStudentByPosId(Integer id);
	
}

测试:

package com.test;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.mapper.PositionMapper;
import com.pojo.Position;
import com.util.MyBatisUtil;

public class TestManyToMany {
	
	@Test
	public void testManyToMany() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		PositionMapper positionMapper = sqlSession.getMapper(PositionMapper.class);
		Position pos = positionMapper.selectPositionStudentByPosId(1);
		
		System.out.println(pos);
	}
}

 

转载于:https://www.cnblogs.com/mzywucai/p/11053358.html

发布了0 篇原创文章 · 获赞 17 · 访问量 8万+
展开阅读全文
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符

mybatis一对多关联查询问题

08-10

org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'roles' in 'class com.jsp.system.user.bo.User' at org.apache.ibatis.reflection.Reflector.getGetInvoker(Reflector.java:380) at org.apache.ibatis.reflection.MetaClass.getGetInvoker(MetaClass.java:170) at org.apache.ibatis.reflection.wrapper.BeanWrapper.getBeanProperty(BeanWrapper.java:152) at org.apache.ibatis.reflection.wrapper.BeanWrapper.get(BeanWrapper.java:48) at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:116) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.instantiateCollectionPropertyIfAppropriate(DefaultResultSetHandler.java:451) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyNestedResultMappings(DefaultResultSetHandler.java:800) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:758) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForNestedResultMap(DefaultResultSetHandler.java:724) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:267) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:239) at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:153) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:60) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137) <?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"> user.xml配置 <mapper namespace="User"> <resultMap id="UserMap" type="User"> <id property="user_id" column="user_id"/> <result property="userName" column="userName" /> <result property="password" column="password" /> <result property="age" column="age" /> <!-- 一对多的关系 --> <!-- property: 指的是集合属性的值, ofType:指的是集合中元素的类型 --> <collection property="roles" javaType="list" ofType="Role"> <id property="role_id" column="role_id"/> <result property="role_name" column="role_name"/> <result property="role_code" column="role_code"/> </collection> </resultMap> <!-- 按照用户ID查找角色信息 --> <select id="selectUserRole" parameterType="String" resultMap="UserMap"> select u.userName,r.role_name,r.role_name from s_user u,s_role r, s_relate g where u.user_id=g.user_id and r.role_id=g.role_id and u.user_id=#{user_id} </select> </mapper> 报错了,找了半天不知道什么原因. 麻烦大神帮看下,或留个联系方式. 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览