Mybatisd对sql表的一对多查询
select * from projectrecord pr left join projects po on po.pid=pr.pid left join emp e on e.empno = pr.empno where pr.pid=1
<?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.msb.mapper.EmpMapper">
<!--resultMap是一个映射表，对应的字段会对应数据表的字段
-->
<resultMap id="projectMap" type="projects">
<id column="pid" property="pid"></id>
<result column="pname" property="pname"></result>
<result column="money" property="money"></result>
<collection property="projectrecordList" ofType="projectrecord">
<id property="pid" column="pid"></id>
<id property="empno" column="empno"></id>
<association property="emp" javaType="emp">
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="job" column="job"></result>
<result property="sal" column="sal"></result>
<result property="hiredate" column="hiredate"></result>
<result property="mgr" column="mgr"></result>
<result property="comm" column="comm"></result>
<result property="deptno" column="deptno"></result>
</association>
</collection>
</resultMap>
<!--Projects findPiddle(int pid);-->
<select id="findPiddle" resultMap="projectMap">
select *from
emp e
inner join
projectrecord pr
on pr.empno=e.empno
inner join
projects po
on po.pid= pr.pid
where pr.pid=#{pid}
</select>
</mapper>
//mapper表
package com.msb.mapper;
import com.msb.pojo.Dept;
import com.msb.pojo.Emp;
import com.msb.pojo.Projects;
import java.util.List;
public interface EmpMapper {
/**
* 实现根据项目编号查看所有参与项目的人员信息
* @param pid 传入项目号
* @return 返回一个project的对象
*/
Projects findPiddle(int pid);
}
package com.msb.pojo;
//一对一关系引入
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Projectrecord implements Serializable {
private int empno;
private int pid;
//pr和e是一对一的关系
private Emp emp;
}
package com.msb.pojo;
//一对多关系实体类设计
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.List;
@NoArgsConstructor
@AllArgsConstructor
@Data
public class Projects implements Serializable {
private int pid;
private String pname;
private int money;
//po和pr是一对多的关系
List<Projectrecord> projectrecordList;
}
package com.msb.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.sql.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Integer sal;
private Integer comm;
private Integer deptno;
private Dept dept;
}
jdbc_driver=com.mysql.cj.jdbc.Driver jdbc_url=jdbc:mysql://127.0.0.1:3306/testtable?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai jdbc_username=root jdbc_password=root
log4j.rootLogger=debug,stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.SimpleLayout
log4j.appender.logfile=org.apache.log4j.FileAppender
log4j.appender.logfile.File=d:/msb.log
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %l %F %p %m%n
<?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="jdbc.properties"></properties>
<!--这里是起的别名，前面是名，后面是路径-->
<!--<typeAliases>
<typeAlias alias="dept" type="com.msb.pojo.Dept"/>
</typeAliases>-->
<!--包别名，用到时候调用名字小写即可,就会扫描msb下的所以实体类，用的时候实体类名小写-->
<typeAliases>
<package name="com.msb"/>
</typeAliases>
<environments default="development">
<environment id="development">
<!-- 简单使用了 JDBC 的提交和回滚设置 -->
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc_driver}"/>
<property name="url" value="${jdbc_url}"/>
<property name="username" value="${jdbc_username}"/>
<property name="password" value="${jdbc_password}"/>
</dataSource>
</environment>
</environments>
<!--加载mapper映射文件-->
<mappers>
<package name="com.msb.mapper"/>
</mappers>
</configuration>
import com.msb.mapper.EmpMapper;
import com.msb.pojo.Dept;
import com.msb.pojo.Emp;
import com.msb.pojo.Projectrecord;
import com.msb.pojo.Projects;
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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Test1 {
SqlSession sqlSession = null;
EmpMapper mapper =null;
@Before
public void test1(){
//首先做一个对象SqlSessionFactoryBuilder建立一个绘话
SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
//有一个文本输入的io流进行读取操作
InputStream stream = null;
try {
//这里的路径直接会定位到配置文件classes下面；所以这个文件在次目录下--编译和
//-图纸；对数据库文件进行读取，获取一个io流，由于配置文件在classes下面，直接写文件名即可
stream = Resources.getResourceAsStream("sqlMapConfig.xml");
} catch (IOException e) {
e.printStackTrace();
}
//build需要指向一个文件进行读取出来--工厂
SqlSessionFactory factory = ssfb.build(stream);
//需要用sqlSession去调用增删改查--工人去获取数据，打开这个绘话
sqlSession = factory.openSession();
}
/*多对多的查询*/
@Test
public void test7(){
mapper = sqlSession.getMapper(EmpMapper.class);
Projects piddle = mapper.findPiddle(1);
System.out.println("Pid");
System.out.println(piddle.getPid());
System.out.println("Pname");
System.out.println(piddle.getPname());
System.out.println("Money");
System.out.println(piddle.getMoney());
System.out.println("ProjectrecordList");
List<Projectrecord> projectrecordList = piddle.getProjectrecordList();
projectrecordList.forEach(System.out::println);
}
@After
public void test3(){
if (sqlSession!=null){
sqlSession.close();
}
}
}
