1 2 3 4 5 6 7 8 9 | 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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | <? xml version = "1.0" encoding = "UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" < 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 > |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | //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); } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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; } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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; } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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; } |
1 2 3 4 | 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 |
1 2 3 4 5 6 7 8 | 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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | <? xml version = "1.0" encoding = "UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" < 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 > |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | 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(); } } } |