mybatis 一二事（2）

db.properties 单独提取出来的数据库配置，方便以后维护管理

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=root

SqlMapConfig.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">
<configuration>
    <!-- 加载数据库连接参数配置文件 -->
    <properties resource="db.properties" />
    
    <!-- 
        全局配置参数
        比如 二级缓存 延迟加载...等
        此全局参数会影响mybatis运行的性能，要谨慎配置    
     -->
<!--     <settings> -->
<!--     <setting name="" value=""/> -->
<!--     </settings> -->
    
    <!-- 定义别名 -->
    <typeAliases>
        <!-- 单个别名定义
        type：pojo的路径 
        alias：别名的名称
        -->
        <!-- <typeAlias type="cn.itcast.mybatis.po.User" alias="user"/> -->
        <!-- 批量别名定义
        name：指定包名，将包下边的所有pojo定义别名 ，别名为类名（首字母大写或小写都行）
         -->
        <package name="com.mybatis.bean"/>
    </typeAliases>
    
    <!-- 和spring整合后 environments配置将废除 -->
    <environments default="development">
        <environment id="development">
            <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>
        <!-- resource方式
        在UserMapper.xml，定义namespace为mapper接口的地址，映射文件通过namespace找到对应的mapper接口文件
         -->
        <!-- <mapper resource="sqlmap/UserMapper.xml" /> -->
        <!-- class方式
        class：指定 mapper接口的地址
        遵循规则：将mapper.xml和mapper.java文件放在一个目录 且文件名相同
         -->
        <!-- <mapper class="cn.itcast.mybatis.mapper.UserMapper"/> -->
        
        <!--
            批量mapper扫描
            遵循规则：将mapper.xml和mapper.java文件放在一个目录 且文件名相同
            主要以这样的方式为主来加载mapper
          -->
        <package name="com.mybatis.mapper"/>
        
        
    </mappers>
</configuration>

UserMapper.java

package com.mybatis.mapper;

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

import com.mybatis.bean.QueryVo;
import com.mybatis.bean.User;

public interface UserMapper {
    
    public User findUserById(int id) throws Exception;
    
    public List<User> findUserList(String name) throws Exception;
    
    public Integer insertUser(User user) throws Exception;
    
    public void deleteUser(int id) throws Exception;
    
    public void updateUser(User user) throws Exception;
    
    public List<User> findUserByBean(User user) throws Exception;
    
    public List<User> findUserByMap(Map<String, Object> map) throws Exception;
    
    public List<User> findUserByCustom(QueryVo queryVo) throws Exception;
    
//    public Map findUserMapByCustom(QueryVo queryVo) throws Exception;
    
    public void updateUserSet(User user) throws Exception;
    
}

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

<mapper namespace="com.mybatis.mapper.UserMapper" >

    <!-- 
        sql 片段，可以供其他的sql一起使用
        建议以单表抽取查询条件
     -->
     <sql id="query_for_user">
        <if test=" user != null ">
            <if test=" user.name != null and user.name != '' ">
                and name like '%${user.name}%'
            </if>
            <if test=" user.sex != null and user.sex != '' ">
                and sex = #{user.sex}
            </if>
        </if>
        
        <if test="ids != null">
             <foreach collection="ids" separator="or" item="item" open="and (" close=")">
                 id = #{item}
             </foreach>
         </if>
     </sql>
     

    <select id="findUserById" parameterType="int" resultType="com.mybatis.bean.User">
        SELECT * FROM USER WHERE id = #{id}
    </select>
    
    <!-- 
        #{} 表示占位符，#{}可以使用value或者其他字符，可以防止sql注入，使用时无需考虑参数的类型
        ${} 表示sql拼接，把原始的内容不加修饰的放入sql中，${}只能使用value，不可以防止sql注入，必须考虑参数的类型
        一般在没有特殊情况下使用#{}为主
        有些情况必须使用${}，比如
            动态拼接表名：select * from ${tablename}, 如果使用了#{}则会在传入的表名上加单引号 ''
            动态拼接排序字段：select * from user order by ${username}
            
        举个栗子：
        查询日期的区别：
            select * from user where birthday >= #{date}
            select * from user where birthday >= to_date('${date}', 'yyyy-MM-dd')
     -->
    
    <select id="findUserList" parameterType="java.lang.String" resultType="com.mybatis.bean.User" >
        select * from user where name like '%${value}%'
    </select>
    
    <insert id="insertUser" parameterType="com.mybatis.bean.User">
        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            select LAST_INSERT_ID()
        </selectKey>
        insert into user(name,age,sex) values(#{name},#{age},#{sex})
    </insert>
    
    <delete id="deleteUser" parameterType="int">
       delete from user where id=#{id}
    </delete>
    
    <update id="updateUser" parameterType="com.mybatis.bean.User">
       update user set name=#{name},age=#{age},sex=#{sex}  where id=#{id}
    </update>
    
    <select id="findUserByBean" parameterType="User" resultType="User">
        select * from user where name like '%${name}%' and sex = #{sex}
    </select>
    
    <select id="findUserByMap" parameterType="hashmap" resultType="User">
        select * from user where name like '%${name}%' and age >= #{age}
    </select>
    
    <!-- 
        parameterMap 已经过期不建议使用, 官方已经废除
        resultMap 不建议使用，太复杂
     -->
    <select id="findUserByCustom" parameterType="QueryVo" resultType="User">
        select * from user 
        
        <!-- 
            where标签自动将 where后的第一个and去掉，比where 1=1 and 要好很多
            where name like '%${user.name}%' and sex = #{user.sex}    
         -->
<!--         <where> -->
<!--             <if test=" user != null "> -->
<!--                 <if test=" user.name != null and user.name != '' "> -->
<!--                     and name like '%${user.name}%' -->
<!--                 </if> -->
<!--                 <if test=" user.sex != null and user.sex != '' "> -->
<!--                     and sex = #{user.sex} -->
<!--                 </if> -->
<!--             </if> -->
<!--         </where> -->
        
        <where>
            <include refid="query_for_user"></include>
        </where>
        
    </select>
    
    <!-- 
        不建议使用map作为返回值，因为在代码中需要对key进行硬编码
     -->
<!--     <select id="findUserMapByCustom" parameterType="QueryVo"  resultType="hashmap"> -->
<!--         select * from user where name like '%${user.name}%' and sex >= #{user.sex} -->
<!--     </select> -->

    <update id="updateUserSet" parameterType="User">   
        update user
        <set>   
            <if test="name != null and name != '' ">   
                name = #{name}, 
            </if>   
            <if test="age != null and age != '' and age != 0 ">   
                age = #{age},
            </if>  
            <if test="sex != null and sex != '' ">   
                sex = #{sex},
            </if>   
        </set>   
        where id = #{id};    
    </update>     
    
</mapper>

QueryVo.java

package com.mybatis.bean;

import java.util.List;

/**
 * 查询的封装类
 * 
 * @author leechenxiang
 * @date 2016年3月5日
 *
 */
public class QueryVo {

    private User user;
    
    private UserCustom uc;
    
    private List<Integer> ids;

    public UserCustom getUc() {
        return uc;
    }

    public void setUc(UserCustom uc) {
        this.uc = uc;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }

}

User.java

package com.mybatis.bean;

public class User {

    private int id;
    private String name;
    private int age;
    private String sex;
    
    public User() {
        super();
    }

    public User(String name, int age, String sex) {
        super();
        this.name = name;
        this.age = age;
        this.sex = sex;
    }
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", age=" + age + ", sex="
                + sex + "]";
    }
    
}

UserCustom.java

package com.mybatis.bean;

/**
 * 扩展User的自定义类
 * 扩展对象以'XxxxCustom'的格式命名
 * 
 * @author leechenxiang
 * @date 2016年3月5日
 *
 */
public class UserCustom extends User {
    
    private String youngOrOld;

    public String getYoungOrOld() {
        return youngOrOld;
    }

    public void setYoungOrOld(String youngOrOld) {
        this.youngOrOld = youngOrOld;
    }
    
}

最后附上github地址：https://github.com/leechenxiang/mybatis002-dynamic-proxy