mybatis 3

Scroll Down

mybatis 连接池

在主配置文件中的<dataSource>标签,中的type属性

  1. POOLED 传统的连接池s
  2. UNPOOLED 传统获取连接的方式,没用使用连接池
  3. JNDI 采用服务器提供的JNDI技术来获取DataSource对象,不是web或者war工程是不能使用的
<dataSource type="POOLED">
    ...
</dataSource>

如果要用别的连接池,例如Druid

<dataSource type="com.gyb.mybatis.common.DruidDataSourceFactory" >
    ...
</dataSource>

填写全限定类名

查询加强

标签

<select id="findUserByCondition" resultMap="userMap" parameterType="user">
    select * from users where 1=1
    <if test="user != null">
    and user = #{user}
    </if>
</select>

标签

where标签不需要我们写1=1这个条件,直接在下面写if就可以了,非常方便

<select id="findUserByCondition" resultMap="userMap" parameterType="user">
    select * from users
    <where>
        <if test="user != null">
            and user = #{user}
        </if>
        <if test="password != null">
            and password = #{password}
        </if>
    </where>
</select>

标签

这种标签一般用于循环

先在java中进行定义

List<User> findUserByNames(QueryVo vo);
public class QueryVo {
    List<String> list;
}
<select id="findUserByNames" parameterType="com.lucas.dao.QueryVo" resultMap="userMap">
    select * from users
    <where>
        <foreach collection="list" open="user in (" close=")" item="user" separator=",">
            #{user}
        </foreach>
    </where>
</select>

collection是一个集合,open是开始部分,close是结束,item是每一项,separator是分隔符,下面的#{user}是根据上面item属性的内容来的

测试代码

UserDao mapper = sqlSession.getMapper(UserDao.class);
List<String> list = new ArrayList<>();
list.add("teacher");
list.add("admin");
QueryVo queryVo = new QueryVo();
queryVo.setList(list);
List<User> userByCondition = mapper.findUserByNames(queryVo);
for (User u : userByCondition) {
    System.out.println(u);
}

mybatis 多表查询

  1. 建立两张表
  2. 建立两个实体类

示例

步骤

  1. 建立两张表,用户表和账户表
  2. 建立两个实体类:用户实体类和账户实体类
  3. 建立两个配置文件:用户配置文件和账户配置文件
  4. 实现配置:我们查询用户时,可以同时得到用户下所包含的账户的信息,查询账户时,可以得到用户的信息

一对一映射

从表实体包含主表实体的引用

实例

Java类的建立
public class ClassBean {
    private String classNo;
    private String className;
    private String deptNo;
    //一个班级只属于一个学院,在这里保留一个学院的引用
    private Department department;
}
public class Department {
    private String deptNo;
    private String deptName;
}
xml基础的findAll的配置
<select id="findAll" resultMap="classMap" >
    select * from class
</select>
<select id="findAll" resultMap="departmentMap" >
    select * from dept
</select>
一对一联系的查询
通过Mapper配置
<resultMap id="classMap" type="com.lucas.pojo.ClassBean">
   <id property="classNo" column="ClassNo"/>
   <result property="className" column="ClassName"/>
   <result property="deptNo" column="DeptNo"/>
   <association property="department" javaType="com.lucas.pojo.Department">
       <id column="dDeptNo" property="deptNo"/>
       <result column="DeptName" property="deptName"/>
   </association>
</resultMap>

这里用了association标签,property中对应class中引用类型属性的名称,javaType是引用类型的全限定类名

<select id="findAllClassAndDepartment" resultMap="classMap">
    select class.*, d.DeptNo dDeptNo, DeptName
    from class left join dept d on class.DeptNo = d.DeptNo
</select>
通过子查询
<select id="findAllClassAndDepartment" resultMap="classMap">
    select * from class
</select>

<select id="findDept" parameterType="String" resultMap="departmentMap">
    select * from dept where DeptNo = #{deptNo}
</select>

<resultMap id="departmentMap" type="com.lucas.pojo.Department">
    <id column="DeptNo" property="deptNo"/>
    <result column="DeptName" property="deptName"/>
</resultMap>

<result property="deptNo" column="DeptNo"/>
   <association property="department" javaType="com.lucas.pojo.Department" column="DeptNo" select="findDept"/>
</resultMap>

这种方式相当于在 association中创建了一个子查询, 通过子查询来查询dept的信息,这里传入的变量就是column属性

一对多映射

主表实体应该包含从表实体的集合引用

刚刚的一对一反过来就是一对多,因为一个学院有n个班级

首先在学院类中加入一个List

public class Department {
    private String deptNo;
    private String deptName;
    List<ClassBean> classes;
}

再在xml中进行配置

<resultMap id="departmentMap" type="com.lucas.pojo.Department">
    <id column="DeptNo" property="deptNo"/>
    <result column="DeptName" property="deptName"/>
    <collection property="classes" ofType="com.lucas.pojo.ClassBean">
        <id column="ClassNo" property="classNo"/>
        <result column="cdeptNo" property="deptNo"/>
        <result column="ClassName" property="className"/>
    </collection>
</resultMap>

通过使用collection标签,进行对集合的设置,property表示实体类中集合属性,ofType中填写集合中存放的全限定类名,在集合中的类使用ofType

<select id="findAllDeptWithClasses" resultMap="departmentMap" >
    select dept.*,c.DeptNo cdeptNo,ClassName,ClassNo
    from dept left join class c on dept.DeptNo = c.DeptNo
</select>
子查询方式
<select id="findClasses" resultMap="classMap">
    select * from class where DeptNo = #{DeptNo}
</select>

<resultMap id="classMap" type="com.lucas.pojo.ClassBean">
    <id property="classNo" column="ClassNo"/>
    <result property="className" column="ClassName"/>
    <result property="deptNo" column="DeptNo"/>
</resultMap>

通过对每一个学院的编号,再去班级表中进行查询

<resultMap id="departmentMap" type="com.lucas.pojo.Department">
    <id column="DeptNo" property="deptNo"/>
    <result column="DeptName" property="deptName"/>
    <collection property="classes" ofType="com.lucas.pojo.ClassBean" javaType="java.util.List" column="DeptNo" select="findClasses"/>
</resultMap>

这里的ofType指集合里面的内容,javaType指集合的类,column指传入的参数

<select id="findAllDeptWithClasses" resultMap="departmentMap" >
    select * from dept
</select>

然后这种查询就可以写的比较简单

多对多映射

各自包含对方一个集合引用

建立多对多的联系

public class Student {
    private String no;
    private String name;
    private String sex;
    private String age;
    private List<Course> courses;
}
public class Course {
    private String cno;
    private String cname;
    private String cpno;
    private int credit;
    private double grade;
    private List<Student> students;
}

在xml中进行配置

<resultMap id="courseMap" type="com.lucas.pojo.Course">
   <id column="Cno" property="cno"/>
   <result column="Cname" property="cname"/>
   <result column="Cpno" property="cpno"/>
   <result column="Ccredit" property="credit"/>
   <collection property="students" ofType="com.lucas.pojo.Student">
       <id property="no" column="Sno"/>
       <result property="name" column="Sname"/>
       <result property="sex" column="Ssex"/>
       <result property="age" column="Sage"/>
   </collection>
</resultMap>

<select id="findAllCoursesAndStudents" resultMap="courseMap">
    select course.*,student.*
    from student,course,sc
    where sc.Sno=student.Sno and sc.Cno = course.Cno
</select>
<resultMap id="studentMap" type="com.lucas.pojo.Student">
    <id property="no" column="Sno"/>
    <result property="name" column="Sname"/>
    <result property="sex" column="Ssex"/>
    <result property="age" column="Sage"/>
    <collection property="courses" ofType="com.lucas.pojo.Course">
        <id column="Cno" property="cno"/>
        <result column="Cname" property="cname"/>
        <result column="Cpno" property="cpno"/>
        <result column="Ccredit" property="credit"/>
        <result column="grade" property="grade"/>
    </collection>
</resultMap>

<select id="findAllStudentAndCourses" resultMap="studentMap">
    select student.*,sc.Grade grade,c.*
    from student left join sc on student.Sno = sc.Sno
        left join course c on sc.Cno = c.Cno
</select>
多表的子查询

建立子查询映射

<resultMap id="courseMap" type="com.lucas.pojo.Course">
    <id column="Cno" property="cno"/>
    <result column="Cname" property="cname"/>
    <result column="Cpno" property="cpno"/>
    <result column="Ccredit" property="credit"/>
    <result column="grade" property="grade"/>
</resultMap>

子查询

<select id="findCourse" resultMap="courseMap" >
    select course.*,sc.Grade grade
    from course,sc where course.Cno = sc.Cno and sc.Sno = #{Sno};
</select>
<resultMap id="studentMap" type="com.lucas.pojo.Student">
    <id property="no" column="Sno"/>
    <result property="name" column="Sname"/>
    <result property="sex" column="Ssex"/>
    <result property="age" column="Sage"/>
    <collection property="courses" ofType="com.lucas.pojo.Course" column="Sno" select="findCourse"/>
</resultMap>

主查询映射,属性配置和一对多情况相同

<select id="findAllStudentAndCourses" resultMap="studentMap">
    select * from student
</select>

主查询非常简单