Mybatis(动态SQL大全)

配置bean Employee.java

package org.apwla.domain;

import java.io.Serializable;

public class Employee implements Serializable {
    private Integer id;
    private String loginname;
    private String password;
    private String name;
    private String sex;
    private String age;
    private String phone;
    private String sal;
    private String state;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLoginname() {
        return loginname;
    }

    public void setLoginname(String loginname) {
        this.loginname = loginname;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getSal() {
        return sal;
    }

    public void setSal(String sal) {
        this.sal = sal;
    }

    public String getState() {
        return state;
    }

    public void setState(String state) {
        this.state = state;
    }
    @Override
    public String toString() {
        return "Employee [id=" + id + ", loginname=" + loginname
                + ", password=" + password + ", name=" + name + ", sex=" + sex
                + ", age=" + age + ", phone=" + phone + ", sal=" + sal
                + ", state=" + state + "]";
    }
}

配置接口EmployeeMapper.java

package org.apwla.mapper;

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

import org.apwla.domain.Employee;

public interface EmployeeMapper {
    
    List<Employee> selectEmployeeByIdLike(HashMap<String, Object> params);
    
    List<Employee> selectEmployeeByLoginLike(HashMap<String, Object> params);
    
    List<Employee> selectEmployeeChoose(HashMap<String, Object> params);
    
    List<Employee> findEmployeeLike(HashMap<String, Object> params);
    
    List<Employee> selectEmployeeLike(HashMap<String, Object> params);
    
    Employee selectEmployeeWithId(Integer id);
    
    void updateEmployeeIfNecessary(Employee employee);
    
    List<Employee> selectEmployeeIn(List<Integer> ids);
    
    List<Employee> selectEmployeeLikeName(Employee employee);
    
}

配置mybatis-config.xml 接口的实现类

<?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">
<!-- namespace指用户自定义的命名空间。 -->
<mapper namespace="org.apwla.mapper.EmployeeMapper">

    <select id="selectEmployeeWithId" parameterType="int"
        resultType="org.apwla.domain.Employee">
        SELECT * FROM tb_employee where id = #{id}
    </select>

    <!-- if -->
    <select id="selectEmployeeByIdLike" resultType="org.apwla.domain.Employee" parameterType="java.util.HashMap">
        SELECT * FROM tb_employee WHERE state = 'ACTIVE'
        <!-- 可选条件，如果传进来的参数有id属性，则加上id查询条件 -->
        <if test="id != null ">
            and id = #{id}
        </if>
    </select>

    <!-- if -->
    <select id="selectEmployeeByLoginLike" resultType="org.apwla.domain.Employee">
        SELECT * FROM tb_employee WHERE state = 'ACTIVE'
        <!-- 两个可选条件，例如登录功能的登录名和密码查询 -->
        <if test="loginname != null and password != null">
            and loginname = #{loginname} and password = #{password}
        </if>
    </select>

    <!-- choose（when、otherwise） -->
    <select id="selectEmployeeChoose" parameterType="hashmap"
        resultType="org.apwla.domain.Employee">
        SELECT * FROM tb_employee WHERE state = 'ACTIVE'
        <!-- 如果传入了id，就根据id查询，没有传入id就根据loginname和password查询，否则查询sex等于男的数据 -->
        <choose>
            <when test="id != null">
                and id = #{id}
            </when>
            <when test="loginname != null and password != null">
                and loginname = #{loginname} and password = #{password}
            </when>
            <otherwise>
                and sex = '男'
            </otherwise>
        </choose>
    </select>

    <select id="findEmployeeLike" resultType="org.apwla.domain.Employee">
        SELECT * FROM tb_employee
        <where>
            <if test="state != null ">
                state = #{state}
            </if>
            <if test="id != null ">
                and id = #{id}
            </if>
            <if test="loginname != null and password != null">
                and loginname = #{loginname} and password = #{password}
            </if>
        </where>
    </select>

    <!-- where -->
    <select id="selectEmployeeLike" resultType="org.apwla.domain.Employee">
        SELECT * FROM tb_employee
        <where>
            <if test="state != null ">
                state = #{state}
            </if>
            <if test="id != null ">
                and id = #{id}
            </if>
            <if test="loginname != null and password != null">
                and loginname = #{loginname} and password = #{password}
            </if>
        </where>
    </select>

    <!-- set -->
    <update id="updateEmployeeIfNecessary" parameterType="org.apwla.domain.Employee">
        update tb_employee
        <set>
            <if test="loginname != null">loginname=#{loginname},</if>
            <if test="password != null">password=#{password},</if>
            <if test="name != null">name=#{name},</if>
            <if test="sex != null">sex=#{sex},</if>
            <if test="age != null">age=#{age},</if>
            <if test="phone != null">phone=#{phone},</if>
            <if test="sal != null">sal=#{sal},</if>
            <if test="state != null">state=#{state}</if>
        </set>
        where id=#{id}
    </update>

    <!-- foreach -->
    <select id="selectEmployeeIn" resultType="org.apwla.domain.Employee">
        SELECT *
        FROM tb_employee
        WHERE ID in
        <foreach item="item" index="index" collection="list" open="("
            separator="," close=")">
            #{item}
        </foreach>
    </select>
    
    

    <!-- bind -->
    <select id="selectEmployeeLikeName" resultType="org.apwla.domain.Employee">
        <bind name="pattern" value="'%' + _parameter.getName() + '%'" />
        SELECT * FROM tb_employee
        WHERE loginname LIKE #{pattern}
    </select>

</mapper>

核心配置文件 mybatis-config.xml

<?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">
<!-- XML配置文件包含对MyBatis系统的核心配置 -->
<configuration>
<!-- 加载属性文件 -->
    <properties resource="db.properties">
        <!--properties中还可以配置一些属性名和属性值  -->
        <!-- <property name="jdbc.driver" value=""/> -->
    </properties>
    <settings>
        <setting name="logImpl" value="LOG4J" />
        <setting name="lazyLoadingEnabled" value="true" />
        <setting name="aggressiveLazyLoading" value="false" />
    </settings>
<!-- 和spring整合后 environments配置将废除-->
    <environments default="development">
        <environment id="development">
        <!-- 使用jdbc事务管理，事务控制由mybatis-->
            <transactionManager type="JDBC" />
        <!-- 数据库连接池，由mybatis管理-->
            <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>

    <!-- mappers告诉MyBatis去哪里找持久化类的映射文件 -->
    <mappers>
        <mapper resource="org/apwla/mapper/PersonMapper.xml"></mapper>
        <mapper resource="org/apwla/mapper/CardMapper.xml"></mapper>
        <mapper resource="org/apwla/mapper/EmployeeMapper.xml" />
    </mappers>
</configuration>