03SQL映射文件核心配置文件

我们之前拿最简单的查询做了例子演示框架搭建，现在，让我们看一下复杂的查询以及增删改如何去做，一般情况下，我们都是一个SQL映射文件对应一张表和一个实体类，有关该实体类的增删改查都在同一个映射文件中

一、回顾一下最简单的查询

select元素相关属性
id,必须存在的属性，值从概念上讲是唯一标示，不能随便写，应对应接口中方法的名
parameterType，若该方法有参，则必须有该属性，值和接口中参数的类型相对应
resultType，结果集的类型，经过查询，数据库中的信息自动封装，指定类型，就告诉框架你把信息封装成一个什么类型的对象

二、多个参数

需求：根据用户名和编号进行查询
接口中增加方法

User selectUser(String username,int id);

发现parameterType无法指定类型，
①加注解

User selectUser(@Param("username")String username,@Param("id")int id);

<select id="selectUser" resultType="User">
        select * from t_user where username = #{username} and id = #{id}
    </select>

②参数整合在一起，使用bean对象,

List<User> selectUser(User user);

<select id="selectUser" parameterType="User" resultType="User">
        select * from t_user where username = #{username} and id = #{id}
    </select>

注意，查询结果有多条，使用list集合作为返回值类型，resultType指定集合中元素的类型

三、删除

需求：根据id进行删除

void deleteUser(int id);

<delete id="deleteUser" parameterType="int">
        delete from t_user where id = #{value}
    </delete>

去测试类中执行发现并没有生效，没有自动提交，因此需要在try中调用session的commit方法，在catch中调用session的rollback方法

四、修改

void updateUser(User user);

<update id="updateUser" parameterType="User">
        update t_user set username = #{username},password = #{password}
        where id = #{id}
    </update>

五、插入

<!-- keyProperty属性主键列对应的属性的名称
    order属性BEFORE表示子查询在主查询前
     -->
    <insert id="insertUser">
        insert into t_user values(#{id},#{username},sysdate,#{password})
        <selectKey keyProperty="id" resultType="int" order="BEFORE">
            select max(id)+1 from t_user
        </selectKey>
    </insert>

void insertUser(User user);

User user = new User();
            user.setUsername("k");
            user.setPassword("k");
            userMapper.insertUser(user);
            session.commit();

六、特殊结果、参数

1、特殊参数类型使用
根据用户名和密码查询，比如此时用户名和密码不同时写在同一个实体类中，此时要使用一个参数就要用到Map

User selectUserByNAP(Map<String,String> map);

<select id="selectUserByNAP" parameterType="map" resultType="User">
        select * from t_user where username = #{m_username} and password = #{m_password}
    </select>

Map<String,String> map = new HashMap<String,String>();
            map.put("m_username", "l");
            map.put("m_password", "l");
            User user = userMapper.selectUserByNAP(map);
            System.out.println(user.getPassword());

sql语句中参数等于Map的键
2、特殊结果集使用
如果实体类属性与数据库列名不一样，就使用resultMap作为结果集类型

User selectUserByNAP1(Map<String,String> map);

<select id="selectUserByNAP1" parameterType="map" resultMap="userres">
        select * from t_user where username = #{m_username} and password = #{m_password}
    </select>
    <resultMap type="User" id="userres">
        <result column="password" property="password"></result>
    </resultMap>

Map<String,String> map = new HashMap<String,String>();
            map.put("m_username", "l");
            map.put("m_password", "l");
            User user = userMapper.selectUserByNAP1(map);
            System.out.println(user.getPassword());

type:查询结果最终得到的类型
3、建employee和department两张表，其中employee表有eid、eno、ename、deptid等字段，department表有did、dno、dname等字段

-- Create table
create table T_DEPARTMENT
(
  did   NUMBER(4) not null,
  dno   VARCHAR2(20),
  dname VARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_DEPARTMENT
  add constraint PK_DID_DEPARTMENT primary key (DID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create table
create table T_EMPLOYEE
(
  eid    NUMBER(4) not null,
  eno    VARCHAR2(20),
  ename  VARCHAR2(20),
  deptid NUMBER(4)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_EMPLOYEE
  add constraint PK_EID_EMPLOYEE primary key (EID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table T_EMPLOYEE
  add constraint FK_DEPTID_EMPLOYEE foreign key (DEPTID)
  references T_DEPARTMENT (DID);

public class Employee {
    private int eid;
    private String eno;
    private String ename;
    private Department dept;
}

public class Department {
    private int did;
    private String dno;
    private String dname;
    private List<Employee> employees;
}

七、多表查询

1、一对一关联
如果要做查询员工信息同时将部门信息同时查出来
（1）嵌套子查询

public Employee getEmployeeById(int eid);

<select id="getEmployeeById" parameterType="int" resultMap="empres">
        select * from t_employee where eid = #{eid}
    </select>
    <!-- 嵌套子查询
    association元素
    property属性嵌套查询的结果赋值给Employee中属性的名字 
    column属性外键字段的名称
    javaType属性嵌套子查询返回结果集的类型
    select属性嵌套的子查询对相应查询
    -->
    <resultMap type="Employee" id="empres">
        <association property="dept" column="deptid" javaType="Department" select="getDeptById"></association>
    </resultMap>
    <select id="getDeptById" parameterType="int" resultType="Department">
        select * from t_department where did = #{deptid}
    </select>

association元素
property属性嵌套查询的结果赋值给Employee中属性的名字 
column属性外键字段的名称
javaType属性嵌套子查询返回结果集的类型
select属性嵌套的子查询对相应查询

以上查询的做法实际上非常不好，仅做了一个多表查询就需要多次操作数据库
（2）嵌套结果

public Employee getEmployeeByNo(String eno);

<select id="getEmployeeByNo" parameterType="string" resultMap="empRes">
        select * 
        from t_employee e,t_department d
        where e.deptid = d.did
        and e.eno = #{eno}
    </select>
    <resultMap type="employee" id="empRes">
        <result column="eid" property="eid"/>
        <result column="eno" property="eno"/>
         <result column="ename" property="ename"/>
        <association property="dept" column="deptid" javaType="Department" resultMap="deptRes"></association>
    </resultMap>
    <resultMap type="department" id="deptRes">
        <result property="did" column="did"></result>
        <result property="dno" column="dno"></result>
        <result property="dname" column="dname"></result>
    </resultMap>

EmployeeDao employeeDao = session.getMapper(EmployeeDao.class);
            Employee employee = employeeDao.getEmployeeByNo("1");
            System.out.println(employee.getDept().getDid());

2、一对多关联
查询部门信息，并将该部门所有员工查询出来

Department getDepartmentById(int did);

<select id="getDepartmentById" parameterType="int" resultMap="deptRes">
        select *
        from t_employee e,t_department d
        where e.deptid = d.did
        and d.did = #{did}
    </select>
    <resultMap type="department" id="deptRes">
    <result property="did" column="did"></result>
    <result property="dno" column="dno"></result>
    <result property="dname" column="dname"></result>
    <!-- 一对多的关联查询
    collection元素
    property属性针对多个员工信息封装出的集合容器类型的对象，赋值给department类中的对应的属性
    ofType属性集合中每个元素的类型 -->
        <collection property="employees" ofType="employee">
            <result property="eid" column="eid"></result>
            <result property="eno" column="eno"></result>
            <result property="ename" column="ename"></result>
        </collection>
    </resultMap>

DepartmentDao departmentDao = session.getMapper(DepartmentDao.class);
            Department department = departmentDao.getDepartmentById(1);
            List<Employee> list = department.getEmployees();
            System.out.println(list.get(0).getEid());

3、向员工表中插入一条数据，同时指定其部门信息

<insert id="insert" parameterType="employee">
        insert into t_employee values (#{eid},#{eno},#{ename},#{dept.did})
        <selectKey keyProperty="eid"  resultType="int" order="BEFORE">
            select max(eid)+1 from t_employee
        </selectKey>
    </insert>

void insert(Employee e);

Employee employee = new Employee();
            employee.setEno("2");
            employee.setEname("Lily");
            Department department = new Department();
            department.setDid(1);
            employee.setDept(department);
            EmployeeDao employeeDao = session.getMapper(EmployeeDao.class);
            employeeDao.insert(employee);
            session.commit();

4、分页查询
利用pageHelper插件
首先引入jar包
jsqlparser-0.9.jar
pagehelper-3.6.4.jar
在核心配置文件中加入

<!-- plugins元素加在typeAliases下 -->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <!-- property元素指明和哪个数据库匹配使用 -->
            <property name="dialect" value="oracle"></property>
        </plugin>
    </plugins>

在查询映射文件中加入

<select id="getAll" resultType="user">
        select * from t_user
    </select>

List<User> getAll();

//获取第一页数据，每页显示1条
            Page page = PageHelper.startPage(1, 1);
            userMapper.getAll();
            System.out.println("共"+page.getTotal()+"条记录");
            List<User> list = page.getResult();
            for(User u:list){
                System.out.println(u.getPassword());
            }

5、组合查询

<!-- 条件查询where元素，补齐where关键字 -->
    <select id="getByCondition" parameterType="user" resultType="user">
        select * from t_user
        <!-- where 1=1 -->
        <where>
            <if test="id != null and id != 0">
                and id = #{id}
            </if>
            <if test="username != null and username != ''">
                and username = #{username}
            </if>
        </where>
    </select>

6、批量删除

<!-- 参数是数组，无需加参数
        foreach元素遍历集合容器对象
        collection属性指定的是方法传入的类型
        item属性每次遍历到的元素
        open属性遍历得到的结果的开始字符
        separator属性元素之间的分隔符
     -->
    <delete id="deleteUsers">
        delete from t_user where id in
        <foreach collection="array" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </delete>

void deleteUsers(int[] ids);

int[] i = new int[2];
            i[0] = 3;
            i[1] = 4;
            userMapper.deleteUsers(i);
            session.commit();

7、缓存

User user = userMapper.getUser(1);
            user.setPassword("k");
            //修改查询到的user信息
            System.out.println(user.getPassword());
            user.setPassword("j");
            //再次查询理论上应该得到数据库中的内容，但是却是上述修改后的内容，这是因为Mybatis缓存
            //缓存是因为提高性能
            user = userMapper.getUser(1);
            System.out.println(user.getPassword());

此时需要在sql映射文件中加入flushCache="true"

<select id="getUser" parameterType="java.lang.Integer" resultType="User" flushCache="true">
        select * from t_user where id = #{value}
    </select>

当查询数据库返回结果时，mybatis会生成key、value形式的对象存在于缓存，在接下来在内存中针对查询结果做变更，相当于改变了缓存中的数据，再次查询不访问数据库，而是直接采用缓存中的数据

八、模糊查询

接口中写入

List<User> getUserByName(String name);

注意！！！mybatis接口中方法不要重载
mapper文件中
写法1

<select id="getUserByName" parameterType="String" resultType="User">
        select * from t_user where username like '%${value}%'
    </select>

写法2

<select id="getUserByName" parameterType="String" resultType="User">
        select * from t_user where username like concat('%',#{value},'%')
    </select>

写法3

<select id="getUserByName" parameterType="String" resultType="User">
        select * from t_user where username like "%"#{value}"%"
    </select>