（个人学习笔记）mybatis：trim，foreach、一对一，一对多关联查询

trim：

trim标签可以完成set或者是where标签的功能。

trim元素的主要功能是可以在自己包含的内容前加上某些前缀，也可以在其后加上某些后缀，与之对应的属性是prefix和suffix；可以把包含内容的首部某些内容覆盖，即忽略，也可以把尾部的某些内容覆盖，对应的属性是prefixOverrides和suffixOverrides；正因为trim有这样的功能，所以我们也可以非常简单的利用trim来代替where元素的功能。

foreach：

foreach 元素的功能是非常强大的，它允许你指定一个集合，声明可以用在元素体内的集合项和索引变量。它也允许你指定开闭匹配的字符串以及在迭代中间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。

你可以将任何可迭代对象（如列表、集合等）和任何的字典或者数组对象传递给foreach作为集合参数。当使用可迭代对象或者数组时，index是当前迭代的次数，item的值是本次迭代获取的元素。当使用字典（或者Map.Entry对象的集合）时，index是键，item是值。

下面是练习时写的代码：
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">

<!-- 通过这个配置文件完成mybatis与数据库的连接 -->
<configuration>
    <!-- 引入 database.properties 文件-->
    <properties resource="database.properties"/>
    <!-- 配置mybatis的log实现为LOG4J -->
    <settings>
        <setting name="logImpl" value="LOG4J" />
    </settings>

    <!-- 设置别名 -->
    <typeAliases>
        <package name="com.bdqn.entity"/>
    </typeAliases>


    <environments default="development">
        <environment id="development">
            <!--配置事务管理，采用JDBC的事务管理  -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- POOLED:mybatis自带的数据源，JNDI:基于tomcat的数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 将mapper文件加入到配置文件中 -->
    <mappers>
        <mapper resource="com/bdqn/dao/UserMapper.xml"/>
        <mapper resource="com/bdqn/dao/AddressMapper.xml"/>
        <!-- <mapper resource="com/bdqn/dao/UMapper.xml"/> -->
    </mappers>

</configuration>

工具类：

package com.bdqn.utils;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class GetSqlSession {
    private static GetSqlSession getSqlSession  = null;
    private static SqlSessionFactory factory = null;
    private SqlSession session = null;
    private InputStream is = null;


    private GetSqlSession() {
        try {
            is = Resources.getResourceAsStream("mybatis-config.xml");
            factory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static synchronized GetSqlSession sqlSession(){
        if (getSqlSession==null) {
            synchronized (GetSqlSession.class) {
                if (getSqlSession==null) {
                    getSqlSession=new GetSqlSession();
                }
            }
        }
        return getSqlSession;
    }

    public SqlSession getSession(){
        session =factory.openSession();
        return session;
    }

    public void closeAll(){
        if (session!=null) {
            session.close();
        }if (is!=null) {
            try {
                is.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

实体类：

package com.bdqn.entity;

import java.util.Date;

public class Address {
    private Integer id;             //主键ID
    private String postCode;    //邮编
    private String contact;     //联系人
    private String addressDesc; //地址
    private String tel;         //联系电话
    private Integer createdBy;      //创建者
    private Date creationDate;  //创建时间
    private Integer modifyBy;       //更新者
    private Date modifyDate;    //更新时间
    private Integer userId;         //用户ID

    public Integer getUserId() {
        return userId;
    }
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getPostCode() {
        return postCode;
    }
    public void setPostCode(String postCode) {
        this.postCode = postCode;
    }
    public String getContact() {
        return contact;
    }
    public void setContact(String contact) {
        this.contact = contact;
    }
    public String getAddressDesc() {
        return addressDesc;
    }
    public void setAddressDesc(String addressDesc) {
        this.addressDesc = addressDesc;
    }
    public String getTel() {
        return tel;
    }
    public void setTel(String tel) {
        this.tel = tel;
    }
    public Integer getCreatedBy() {
        return createdBy;
    }
    public void setCreatedBy(Integer createdBy) {
        this.createdBy = createdBy;
    }
    public Date getCreationDate() {
        return creationDate;
    }
    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }
    public Integer getModifyBy() {
        return modifyBy;
    }
    public void setModifyBy(Integer modifyBy) {
        this.modifyBy = modifyBy;
    }
    public Date getModifyDate() {
        return modifyDate;
    }
    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }

}

package com.bdqn.entity;

import java.util.Date;

public class Role {

    private Integer id;   //id
    private String roleCode; //角色编码
    private String roleName; //角色名称
    private Integer createdBy; //创建者
    private Date creationDate; //创建时间
    private Integer modifyBy; //更新者
    private Date modifyDate;//更新时间

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getRoleCode() {
        return roleCode;
    }
    public void setRoleCode(String roleCode) {
        this.roleCode = roleCode;
    }
    public String getRoleName() {
        return roleName;
    }
    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }
    public Integer getCreatedBy() {
        return createdBy;
    }
    public void setCreatedBy(Integer createdBy) {
        this.createdBy = createdBy;
    }
    public Date getCreationDate() {
        return creationDate;
    }
    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }
    public Integer getModifyBy() {
        return modifyBy;
    }
    public void setModifyBy(Integer modifyBy) {
        this.modifyBy = modifyBy;
    }
    public Date getModifyDate() {
        return modifyDate;
    }
    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }

}

package com.bdqn.entity;

import java.util.Date;
import java.util.List;

public class User {
    private Integer id; //id 
    private String userCode; //用户编码
    private String userName; //用户名称
    private String userPassword; //用户密码
    private Integer gender;  //性别
    private Date birthday;  //出生日期
    private String phone;   //电话
    private String address; //地址
    private Integer userRole;    //用户角色ID
    private Integer createdBy;   //创建者
    private Date creationDate; //创建时间
    private Integer modifyBy;     //更新者
    private Date modifyDate;   //更新时间

    private Integer age;//年龄
    private String userRoleName; //用户角色名称

    private Role role;   //用于一对一关联

    private List<Address> addressList;   //用于一对多关联

    public List<Address> getAddressList() {
        return addressList;
    }

    public void setAddressList(List<Address> addressList) {
        this.addressList = addressList;
    }

    public Role getRole() {
        return role;
    }

    public void setRole(Role role) {
        this.role = role;
    }

    public Integer getAge() {
        /*long time = System.currentTimeMillis()-birthday.getTime();
        Integer age = Long.valueOf(time/365/24/60/60/1000).IntegerValue();*/
        Date date = new Date();
        Integer age = date.getYear()-birthday.getYear();
        return age;
    }

    public String getUserRoleName() {
        return userRoleName;
    }
    public void setUserRoleName(String userRoleName) {
        this.userRoleName = userRoleName;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getUserCode() {
        return userCode;
    }
    public void setUserCode(String userCode) {
        this.userCode = userCode;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getUserPassword() {
        return userPassword;
    }
    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }
    public Integer getGender() {
        return gender;
    }
    public void setGender(Integer gender) {
        this.gender = gender;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public Integer getUserRole() {
        return userRole;
    }
    public void setUserRole(Integer userRole) {
        this.userRole = userRole;
    }
    public Integer getCreatedBy() {
        return createdBy;
    }
    public void setCreatedBy(Integer createdBy) {
        this.createdBy = createdBy;
    }
    public Date getCreationDate() {
        return creationDate;
    }
    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }
    public Integer getModifyBy() {
        return modifyBy;
    }
    public void setModifyBy(Integer modifyBy) {
        this.modifyBy = modifyBy;
    }
    public Date getModifyDate() {
        return modifyDate;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", userCode=" + userCode + ", userName="
                + userName + ", userPassword=" + userPassword + ", gender="
                + gender + ", birthday=" + birthday + ", phone=" + phone
                + ", address=" + address + ", userRole=" + userRole
                + ", createdBy=" + createdBy + ", creationDate=" + creationDate
                + ", modifyBy=" + modifyBy + ", modifyDate=" + modifyDate
                + ", age=" + age + ", userRoleName=" + userRoleName + "]";
    }

    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }
}

dao层：
UserMapper接口：

package com.bdqn.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.bdqn.entity.User;

public interface UserMapper {
    List<User> selRoleAndName(@Param("userName")String userName,@Param("userRole")Integer userRole);
    int updateById(User user);
    List<User> selectListByUserRole(List<Integer> list);
    List<User> selUserAndRoleByRoleid(@Param("userRole")Integer userRole);
    User selAddressByUserID(@Param("userRole")Integer userRole);
}

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

    <!-- 查询用户表记录数 -->
    <!-- <select id="count" resultType="int">
        select count(1) as count from smbms_user
    </select> -->
    <!-- 当数据库中的字段信息与对象的属性不一致时需要通过resultMap来映射 -->
    <resultMap type="User" id="userList">
        <result property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
        <result property="phone" column="phone"/>
        <result property="birthday" column="birthday"/>
        <result property="gender" column="gender"/>
        <result property="userRole" column="userRole"/>
        <result property="userRoleName" column="roleName"/>
    </resultMap>

    <select id="selRoleAndName" resultMap="userList">
        select * from smbms_user
        <trim prefix="where" prefixOverrides="and">
            <if test="userRole != null">
                and userRole = #{userRole}
            </if>
            <if test="userName != null and userName != ''">
                and userName like CONCAT ('%',#{userName},'%') 
            </if>
        </trim>
    </select>

    <update id="updateById" parameterType="User">
        update smbms_user
        <trim prefix="set" suffixOverrides="," suffix="where id= #{id}">
                <if test="userCode != null">userCode=#{userCode},</if>
                <if test="userName != null">userName=#{userName},</if>
                <if test="userPassword != null">userPassword=#{userPassword},</if>
                <if test="gender != null">gender=#{gender},</if>
                <if test="birthday != null">birthday=#{birthday},</if>
                <if test="phone != null">phone=#{phone},</if>
                <if test="address != null">address=#{address},</if>
                <if test="userRole != null">userRole=#{userRole},</if>
                <if test="modifyBy != null">modifyBy=#{modifyBy},</if>
                <if test="modifyDate != null">modifyDate=#{modifyDate},</if>
        </trim>
    </update>

    <select id="selectListByUserRole" resultMap="userList">
        select * from smbms_user where userRole in
        <foreach collection="list" item="setid" open="(" separator="," close=")">
            #{setid}
        </foreach>
    </select>

    <resultMap type="User" id="getUser">
    <id property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
        <result property="phone" column="phone"/>
        <result property="birthday" column="birthday"/>
        <result property="gender" column="gender"/>
        <result property="userRole" column="userRole"/>
        <result property="userRoleName" column="roleName"/>
    </resultMap>

    <resultMap type="User" id="usersMap">
        <id property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
        <result property="phone" column="phone"/>
        <result property="birthday" column="birthday"/>
        <result property="gender" column="gender"/>
        <result property="userRole" column="userRole"/>
        <result property="userRoleName" column="roleName"/>
        <association property="role" javaType="Role">
        <id property="id" column="r_id"/>
        <result property="roleCode" column="roleCode"/>
        <result property="roleName" column="roleName"/>
        </association>
    </resultMap>
    <select id="selUserAndRoleByRoleid" resultMap="usersMap" parameterType="Integer">
        select u.*,r.id as r_id,r.roleCode,r.roleName from smbms_user u,smbms_role r 
        where userRole=#{userRole} and u.userRole = r.id
    </select>

    <resultMap type="User" id="getUserAddress" extends="getUser">
        <collection property="addressList" ofType="Address" resultMap="com.bdqn.dao.AddressMapper.getAddress">
        </collection>
    </resultMap>

    <select id="selAddressByUserID" resultMap="getUserAddress" parameterType="int">
        SELECT u.*,a.id AS
        a_id,a.contact,a.addressDesc,a.postCode,a.tel,a.userId
        FROM smbms_user
        u LEFT JOIN
        smbms_address a ON u.id = a.userId
        WHERE u.id=#{id}
    </select>

    </mapper>

AddressMapper.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.bdqn.dao.AddressMapper">
    <resultMap type="Address" id="getAddress">
        <id property="id" column="a_id" />
        <result property="postCode" column="postCode" />
        <result property="tel" column="tel" />
        <result property="contact" column="contact" />
        <result property="addressDesc" column="addressDesc" />
    </resultMap>
</mapper>

测试类：
需要测试哪个方法，就删掉哪个方法的注释。

package com.bdqn.test;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.bdqn.dao.UserMapper;
import com.bdqn.entity.User;
import com.bdqn.utils.GetSqlSession;

public class UserTest {
    public static void main(String[] args) {
        GetSqlSession getSqlSession = GetSqlSession.sqlSession();
        SqlSession session = getSqlSession.getSession();
        //根据模糊的用户名和权限等级查询用户信息
        /*List<User> usersList = session.getMapper(UserMapper.class).selRoleAndName("李", 2);
        for (User user : usersList) {
            System.out.println(user.toString());
        }*/
        //根据ID修改某些信息
        /*User user = new User();
        user.setId(6);
        user.setUserName("wowowo");
        user.setUserRole(2);
        user.setAddress("广州市某个地方");
        int count = session.getMapper(UserMapper.class).updateById(user);
        session.commit();
        System.out.println(count);*/
        //根据权限查询用户信息
        /*List<Integer> idList = new ArrayList<Integer>();
        idList.add(2);
        idList.add(3);
        List<User> usersList = session.getMapper(UserMapper.class).selectListByUserRole(idList);
        for (User user : usersList) {
            System.out.println(user.toString());
        }*/
        //一对一
        /*List<User> list = session.getMapper(UserMapper.class).selUserAndRoleByRoleid(3);
        for (User user : list) {
            System.out.println(user.toString()+" roleCode="+user.getRole().getRoleCode()+",roleName="+user.getRole().getRoleName());
        }*/
    }
}