Mybatis 相关

mapper.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.hd.dao.UserMapper">
		<select id="getAllUser" resultMap="UserAndRoleMap">
			select u.*, r.role_name from tb_user u inner join tb_role r on u.role = r.role_id
		</select>

		<resultMap id="UserAndRoleMap" type="User">
			<!--
			一般通过id标签来映射主键
			column = 数据库的列名
			property = 结果集对应的数据库列名的映射名
			-->
			<id column="user_id" property="id"/>
			<result column="name" property="name"/>
			<result column="password" property="password"/>
			<result column="description" property="description"/>
			<result column="cdate" property="cdate"/>
			<result column="udate" property="udate"/>

			<!--包含role对象，property属性名称，column数据库字段-->
			<association property="role"  column="role" resultMap="roleMap"/>
		</resultMap>

		<resultMap id="roleMap" type="com.hd.entity.Role">
			<id column="role_id" property="role_id"/>
			<result column="role_name" property="role_name"/>
		</resultMap>
	</mapper>

获取集合

List list();

	<mapper namespace="com.hd.dao.DeviceMapper">
		<!--id: 对应java方法名-->
		<!--resultType： 对应java方法返回类型-->
	    <select id="list" resultType="Device">
	        select * from   tb_device
	    </select>
	</mapper>

获取单个对象

User findUserByName(String name);

	<mapper namespace="com.hd.dao.UserMapper">
		<!--parameterType: 参数类型-->
	    <select id="findUserByName" parameterType="String" resultType="User">
	        select * from  tb_user where name = #{name}
	    </select>
	</mapper>

插入对象

void insert(Release release);

	<mapper namespace="com.hd.dao.ReleaseMapper">
	    <select id="insert" parameterType="com.hd.entity.Release">
	        insert into rpd_release (releasenr, md5,system, releaseconfig,releasefilepath, releasedesc, create_time) values (#{releasenr},#{md5},#{system},#{releaseconfig},#{releasefilepath},#{releasedesc},#{create_time})
	    </select>
	</mapper>

更新对象

    <update id="updateUser" parameterType="com.hd.entity.User">
        UPDATE tb_user SET name=#{name},password=#{password},description=#{description},cdate=#{cdate},udate=#{udate},role=#{role},token=#{token} WHERE user_id = #{id}
    </update>

参数绑定

索引参数绑定

dao接口

User getUserByLogin(String username, String password);

mapper.xml

    <select id="getUserByLogin" resultMap="UserMap" parameterType="java.lang.String">
    select * from  tb_user where tb_user.name = #{0} AND tb_user.password = #{1}
    </select>

注解参数绑定

dao

• @Param指定参数名称

User getUserByLogin(@Param("username") String username, @Param("password") String password);

mapper.xml

    <select id="getUserByLogin" resultMap="UserMap" parameterType="java.lang.String">
    select * from  tb_user where tb_user.name = #{username} AND tb_user.password = #{password}
    </select>

注解

public interface RoleMapper {

    @Select("SELECT * from tb_role")
    List<Role> list();
}

结果集包含对象

前提：获取所有用户和用户对应角色，使用连接查询，获取两张表的内容，将结果集填充到用户类（包含角色类）

<select id="getAllUser" resultMap="UserAndRoleMap">
			select u.*, r.role_name from tb_user u inner join tb_role r on u.role = r.role_id
		</select>

		<resultMap id="UserAndRoleMap" type="User">
			<!--
			一般通过id标签来映射主键
			column = 数据库的列名
			property = 结果集对应的数据库列名的映射名
			-->
			<id column="user_id" property="id"/>
			<result column="name" property="name"/>
			<result column="password" property="password"/>
			<result column="description" property="description"/>
			<result column="cdate" property="cdate"/>
			<result column="udate" property="udate"/>

			<!--包含role对象，property属性名称，column数据库字段-->
			<association property="role"  column="role" resultMap="roleMap"/>
		</resultMap>

		<resultMap id="roleMap" type="com.hd.entity.Role">
			<id column="role_id" property="role_id"/>
			<result column="role_name" property="role_name"/>
		</resultMap>

resultType返回hashmap

1. mapper中指定返回类型为hashmap

	<mapper namespace="com.hd.dao.ReleaseMapper">
		<select id="findAll" resultType="java.util.HashMap">
			select r.releasenr, r.system, r.releaseconfig, r.releasedesc, r.create_time from rpd_release r
		</select>
	</mapper>

2. cotroller返回List<Hashmap<String, Object>>

        List<HashMap<String, Object>> list = rpdService.list();
        map.addAttribute("rels", list);

3. 页面显示

            <table class="layui-table">
                <colgroup>
                    <col width="150">
                    <col width="200">
                    <col>
                </colgroup>
                <thead>
                <tr>
                    <th>版本号</th>
                    <th>系统名称</th>
                    <th>程序配置</th>
                    <th>描述</th>
                    <th>创建时间</th>
                </tr>
                </thead>
                <tbody>
                <c:forEach items="${rels}" var="rel">
                <tr>
                    <td><c:out value="${rel.releasenr}"/></td>
                    <td><c:out value="${rel.system}"/></td>
                    <td><c:out value="${rel.releaseconfig}"/></td>
                    <td><c:out value="${rel.releasedesc}"/></td>
                    <td><c:out value="${rel.create_time}"/></td>
                </tr>
                </c:forEach>
            </table>

动态sql

if

• 标签嵌套if时省略where
• if表达式中的字段为实体类字段

    <select id="listByDevice" resultType="Device" parameterType="Device">
		select * from tb_device
		<where>
			<if test="device_name != null and device_name != ''">
				and tb_device.device_name LIKE concat('%', concat(#{device_name}, '%'))
			</if>
			<if test="device_number != null and device_number != 0">
				and tb_device.device_number = #{device_number}
			</if>
		</where>
	</select>

choose、when、otherwise

• 多个条件中匹配一个语句，相当于switch
• otherwise标签用于无匹配项时的匹配条件

    <select id="listByDevice" resultType="Device" parameterType="Device">
		select * from tb_device WHERE TRUE
		<choose>
			<when test="device_name != null and device_name != ''">
				AND tb_device.device_name LIKE concat('%', concat(#{device_name}, '%'))
			</when>
			<when test="device_number != null and device_number != 0">
				AND tb_device.device_number = #{device_number}
			</when>
			<otherwise>
				AND tb_device.curr_vers = '2.5.5'
			</otherwise>
		</choose>
	</select>

trim

• trim用于定制类似where标签的功能

使用trim来定义where相同的功能如下：

    <select id="listByDevice" resultType="Device" parameterType="Device">
		select * from tb_device
		<trim prefix="where" prefixOverrides="and |or ">
			<if test="device_name != null and device_name != ''">
				and tb_device.device_name LIKE concat('%', concat(#{device_name}, '%'))
			</if>
			<if test="device_number != null and device_number != 0">
				and tb_device.device_number = #{device_number}
			</if>
		</trim>
	</select>

script

• 要在带注解的映射器接口类中使用动态 SQL，可以使用 script 元素。比如:

  @Update({"<script>",
      "update Author",
      "  <set>",
      "    <if test='username != null'>username=#{username},</if>",
      "    <if test='password != null'>password=#{password},</if>",
      "    <if test='email != null'>email=#{email},</if>",
      "    <if test='bio != null'>bio=#{bio}</if>",
      "  </set>",
      "where id=#{id}",
      "</script>"})
    void updateAuthorValues(Author author);

sql片段

• 使用include标签包含sql片段实现sql复用，可以包含其他mapper中的sql
• sql标签id唯一

    <select id="listByDevice" resultType="Device" parameterType="Device">
		select * from tb_device
		<include refid="query_device_where"></include>
	</select>

	<sql id="query_device_where">
		<where>
			<if test="device_name != null and device_name != ''">
				and tb_device.device_name LIKE concat('%', concat(#{device_name}, '%'))
			</if>
			<if test="device_number != null and device_number != 0">
				and tb_device.device_number = #{device_number}
			</if>
		</where>
	</sql>

避坑

• 表名和数据库字段不一致导致的返回为空，mybatis应该是根据sql字段名称对比类成员赋值的
• mapper.xml 包含连个相同resultMap名导致失败