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"

        "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>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

//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"

        "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>

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

       }

    }

}