MyBatis中xml文件配置以及demo案例(含多表查询)

前言

其中还使用了lombok插件

试题，demo实现多表以及单表操作

1.找出所有女性学员信息
2. 找出年龄在20-25岁之间，男性学员的信息
3. 找出所有带过女性学员的老师信息(去重复，同一个老师不重复出现)
4. 找出所有带过女性学员的老师带过的班级信息(去重复)
5.找出1807班所有学员及带班老师信息(学员及带班老师信息可重复)
6.找出只有一个老师带过的班级
7.找出老江带过的学员信息

结构分析

idea项目结构

1.pom文件引入mybatis

<dependencies>
    <!--  mybatis核心包  -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.3.0</version>
    </dependency>
    <!-- mysql驱动包 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.40</version>
    </dependency>
    <!-- junit测试包 -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.11</version>
        <scope>test</scope>
    </dependency>
    <!-- 日志文件管理包 -->
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.12</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>1.7.12</version>
    </dependency>
<!--lombok-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.2</version>
        <scope>provided</scope>
    </dependency>
</dependencies>

2.在resource文件夹中配置文件

<?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>
    <!--引入mysql配置文件-->
    <properties resource="mysql.properties"/>
    <settings>
        <!--全局性设置懒加载。如果设为‘false’，则所有相关联的都会被初始化加载,默认值为false-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!--当设置为‘true’的时候，懒加载的对象可能被任何懒属性全部加载。否则，每个属性都按需加载。默认值为true-->
        <setting name="aggressiveLazyLoading" value="false"/>
        <!--控制台打印日志-->
        <setting name="logImpl" value="STDOUT_LOGGING" />
    </settings>
    <typeAliases>
        <!-- 其实就是将bean的替换成一个短的名字-->
       <!-- 例如<typeAlias type="edu.bean.User" alias="User"/>-->
    </typeAliases>
    <!--对事务的管理和连接池的配置-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED"><!--POOLED：使用Mybatis自带的数据库连接池来管理数据库连接-->
                <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>
    <!--mapping文件路径配置-->
    <mappers>
        <mapper resource="UserMapper.xml"/>
    </mappers>

</configuration>

3.(重点)配置映射文件mapper

注意接口中方法名应与这里的id一致

<?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="edu.dao.IArticDao"> <!--Dao接口-->
 
    <!--找出所有女性学员信息-->
    <select id="findBySex" parameterType="java.lang.String" resultType="edu.bean.StuInfo">
        select * from  stuinfo where  stusex= #{stusex}

    </select>
    <!--找出年龄在20-25岁之间，男性学员的信息-->
    <select id="findBySexAndAge" parameterType="java.util.HashMap" resultType="edu.bean.StuInfo">
        select * from  stuinfo
        <where>
            <if test="sex !=null">
                and stusex = #{sex}
            </if>

            <if test="betweenAge !=null and endAge !=null">
                and stuage between #{betweenAge}  and #{endAge}
            </if>
        </where>

    </select>

    
<!--查询所有带过女性学员的老师信息 去重复-->
<select id="teachWomenInfo" resultType="edu.bean.TeacherInfo">

		SELECT  distinct t1.*
		from     teacherinfo t1
		JOIN stu_teach t2 on t1.teacherId = t2.teacherId
		join   stuinfo t3 on t3.stuId = t2.stuId
		where stusex ='女'
</select>
    
<!--所有带过女性学员的老师带过的班级信息(去重复)-->
   <select id="findTakeWomanClassInfo" resultType="edu.bean.ClassInfo">
       SELECT DISTINCT t1.*
	 FROM classinfo t1 JOIN stuinfo t2 on t1.classid =t2.classid
	 JOIN stu_teach t3 on t3.stuId =t2.stuId
	 WHERE teacherId in (

       SELECT   t1.teacherId
       from     teacherinfo t1
       JOIN stu_teach t2 on t1.teacherId = t2.teacherId
       join   stuinfo t3 on t3.stuId = t2.stuId
       where stusex ='女'

	 )
   </select>

    <!--老师 => 学生多对多关系-->
    <resultMap id="teacherStuMapper" type="edu.bean.TeacherInfo" >
        <id property="teacherId" column="teacherId"/>
        <result property="teacherName" column="teacherName"/>


        <collection property="stuInfoList" ofType="edu.bean.StuInfo">
            <id property="stuId" column="stuId"/>
            <result property="classid"    column="classid"/>
            <result property="stuname" column="stuname"/>
            <result property="stuage" column="stuage"/>
            <result property="stusex" column="stusex"/>
        </collection>

    </resultMap>
    <!--1807班所有学员及带班老师信息(学员及带班老师信息可重复)-->
<select id="Find1807Info" resultMap="teacherStuMapper">
    	select t2.*,t4.*
		FROM classinfo t1
		JOIN stuinfo t2 on t1.classname ='Java1807'
		JOIN stu_teach t3  on t2.stuId=t3.stuId
		JOIN teacherinfo t4 on t3.teacherId=t4.teacherId


</select>


    <!--找出只有一个老师带过的班级-->
    <select id="findByOneTeacher" resultType="edu.bean.ClassInfo">
		SELECT t2.*
		FROM stuinfo t1
		JOIN classinfo t2  ON	 t1.classid =t2.classid
		JOIN stu_teach t3  ON   t1.stuId =t3.stuId
		GROUP BY className
		HAVING count(DISTINCT(teacherId)) =1

    </select>

    <!--找出老江带过的学员信息-->
    <select id="findByJiaoStuInfo" resultType="edu.bean.StuInfo">
       SELECT t3.*
	  from	teacherinfo t1
		JOIN  stu_teach  t2  on t1.teacherId =t2.teacherId and t1.teacherName='老江'
		JOIN  stuinfo t3 on t2.stuId =t3. stuId
    </select>
</mapper>

4.由于mybatis的DataSource配置是引入外部文件，所以需要创建mysql的配置文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/homework
jdbc.username=root
jdbc.password=root.

5.配置接口IArticDao

package edu.dao;

import edu.bean.ClassInfo;
import edu.bean.StuInfo;
import edu.bean.TeacherInfo;

import java.util.List;
import java.util.Map;

public interface IArticDao {
    List<StuInfo> findBySex(String sex);

    List<StuInfo>findBySexAndAge(Map<String ,Object> map);

   List<TeacherInfo> teachWomenInfo();

   List<ClassInfo> findTakeWomanClassInfo();

   List<Object> Find1807Info();
   List<ClassInfo> findByOneTeacher();
   List<StuInfo>findByJiaoStuInfo();
}

6.配置实体类

配置均使用lambok插件

6.1 班级表ClassInfo

package edu.bean;

import lombok.Data;
import lombok.ToString;

import java.util.List;

@Data
@ToString
public class ClassInfo {
    private int classid;
    private String classname;
    private String desc;
    private List<StuInfo>stuInfoList;

    private List<TeacherInfo> teacherInfoList;
}

6.2 中间表Stu_teach

package edu.bean;

import lombok.Data;
import lombok.ToString;

import java.util.List;

@Data
@ToString
public class Stu_teach {
    private int stId;
    private  int stuId;
    private int teacherId;
    private List<StuInfo>stuInfoList;
    private List<TeacherInfo>teacherInfoList;

}

6.3 学生表StuInfo

package edu.bean;


import lombok.Data;
import lombok.ToString;

import java.util.List;

@ToString
@Data
public class StuInfo {
    private int stuId;
    private int classid;
    private  String stuname;
    private int stuage;
    private String stusex;
    private ClassInfo classInfo;
    private List<Stu_teach> stu_teach;


}

6.4 教师表TeacherInfo

package edu.bean;

import lombok.Data;
import lombok.ToString;

import java.util.List;

@Data
@ToString
public class TeacherInfo {
    private int teacherId;
    private String teacherName;
    private List<Stu_teach> listStu_teach;
    private List<StuInfo> stuInfoList;

    private List<ClassInfo> classInfoList;


}

7.测试代码

import edu.bean.ClassInfo;
import edu.bean.StuInfo;
import edu.bean.TeacherInfo;
import edu.dao.IArticDao;
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 java.io.IOException;
import java.io.Reader;
import java.util.*;

public class StuTest {

    public static void main(String[] args) {

        String filename="mybatis-config.xml";
        Reader resourceAsReader =null;
        try {
            resourceAsReader = Resources.getResourceAsReader(filename);

        } catch (IOException e) {
            e.printStackTrace();
        }
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsReader);
        SqlSession sqlSession = build.openSession();
        IArticDao mapper = sqlSession.getMapper(IArticDao.class);
         findBySex(mapper);
			  /*以下皆为测试方法*/
       /*     findBySexAndAge(mapper);
          teachWomenInfo(mapper);
         findTakeWomanClassInfo(mapper);
        Find1807Info(mapper);
        findByOneTeacher(mapper);
            findByJiaoStuInfo(mapper);*/

    }

    private static void findByJiaoStuInfo(IArticDao mapper) {
        List<StuInfo> byJiaoStuInfo = mapper.findByJiaoStuInfo();
        for (StuInfo stuInfo : byJiaoStuInfo) {
            System.out.println(stuInfo);
        }
    }

    private static void findByOneTeacher(IArticDao mapper) {
        List<ClassInfo> byOneTeacher = mapper.findByOneTeacher();
        for (ClassInfo classInfo : byOneTeacher) {
            System.out.println(classInfo.getClassname());
        }
    }

    private static void Find1807Info(IArticDao mapper) {
        List<Object> objects = mapper.Find1807Info();
        System.out.println(objects);
    }

    private static void findTakeWomanClassInfo(IArticDao mapper) {
        List<ClassInfo> takeWomanClassInfo = mapper.findTakeWomanClassInfo();
        System.out.println(takeWomanClassInfo);
    }

    private static void teachWomenInfo(IArticDao mapper) {
         List<TeacherInfo> allInfo = mapper.teachWomenInfo();
        for (TeacherInfo teacherInfo : allInfo) {
            System.out.println(teacherInfo.getTeacherName());
        }
    }

    private static void findBySex(IArticDao mapper) {

        List<StuInfo> sex = mapper.findBySex("女");
        System.out.println(sex);

    }


    private static void findBySexAndAge(IArticDao mapper) {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("sex","男");
        map.put("betweenAge",20);
        map.put("endAge",25);
        mapper.findBySexAndAge(map);
    }




}

其一方法运行结果