MyBatis标签以及常用的SQL

MyBatis标签以及常用的SQL

接博主上期的博文MyBatis知识点小结以及基本配置实现

常用标签

select

• id :唯一的标识符，对应Dao中的方法名。
• resultType：返回值类型路径
• resultMap：返回自定义的resultMap

insert、update和delete

• id :唯一的标识符，对应Dao中的方法名。
• parameterType：传进来的使实例，那么就是该实体类的全路径名；传进来的使参数，那么就是该参数类型的包

这三类的返回值都是int类型的一个数值，表示数据库表中修改了的行数，所以可以利用这一点作为简单判断执行是否成功的依据。

resultMap

• 建立SQL查询结果字段与实体属性的映射关系信息
• 查询的结果集转换为java对象，方便进一步操作。
• 将结果集中的列与java对象中的属性对应起来并将值填充进去
• 主标签id：该resultMap的标志
• 主标签type：返回值的类名（路径类名）
• 副标签id：用于设置主键字段与领域模型属性的映射关系
• 副标签result：用于设置普通字段与领域模型属性的映射关系

property是类中的属性名，column是数据库中的属性名

通过resultMap来进行数据库列名与实体类属性名的绑定（若两者一致，也可不通过resultMap绑定）。

<?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.cloneZjrt.dao.UserDAO">

    <resultMap id="UserEntityMap" type="com.cloneZjrt.model.UserEntity">
        <id property="userId" column="userid"></id>
        <result property="name" column="username"></result>
    </resultMap>


    <!--<select id="queryAll" resultType="com.cloneZjrt.model.UserEntity">-->
    <select id="queryAll" resultMap="UserEntityMap">
        SELECT * FROM userinfo
    </select>
</mapper>

resultMap进行多表联查

一对一

<?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.cloneZjrt.dao.UserDAO">

    <resultMap id="UserEntityMap" type="com.cloneZjrt.model.UserEntity">
        <id property="userId" column="userid"></id>
        <result property="name" column="username"></result>
        <association property="detailsEntity" javaType="com.cloneZjrt.model.DetailsEntity">
            <id  property="userId" column="user_id"/>
            <result  property="phone" column="phone"/>
            <result  property="description" column="description"/>
        </association>
    </resultMap>

    <select id="getUserById" resultMap="UserEntityMap">
        select *
        from userinfo u left join detailsinfo d
                on u.userid = d.user_id
        where u.userid = #{userId}
    </select>
</mapper>

对应的实体类

@Data
public class UserEntity {
    private long userId;
    private String name;

    private DetailsEntity detailsEntity;

}

@Data
public class DetailsEntity {

    private Long userId;
    private String phone;
    private String description;
}

打印结果

[com.alibaba.druid.pool.DruidDataSource] - {dataSource-1} inited
[com.cloneZjrt.dao.UserDAO.getUserById] - ==> Preparing: select * from userinfo u left join detailsinfo d on u.userid = d.user_id where u.userid = ?
[com.cloneZjrt.dao.UserDAO.getUserById] - > Parameters: 1(Long)
[com.cloneZjrt.dao.UserDAO.getUserById] - < Total: 1
{name=111, detailsEntity={“phone”:“15658156554”,“description”:“test”,“userId”:1}, userId=1}

一对多

<?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.cloneZjrt.dao.UserDAO">

    <resultMap id="UserEntityMap" type="com.cloneZjrt.model.UserEntity">
        <id property="userId" column="userid"></id>
        <result property="name" column="username"></result>
        <collection property="roleEntities" ofType="com.cloneZjrt.model.RoleEntity">
            <id property="roleId" column="roleid" />
            <result property="roleName" column="rolename" />
            <result property="description" column="description" />
        </collection>
    </resultMap>

    <select id="getUserById" resultMap="UserEntityMap">
        select *
        from userinfo u left join user_role ur
                on u.userid = ur.user_id
            left join roleinfo r
                on ur.role_id = r.roleid
        where u.userid = #{userId}
    </select>
</mapper>

对应的实体类

@Data
public class UserEntity {
    private long userId;
    private String name;

    private List<RoleEntity> roleEntities;

}

@Data
public class RoleEntity {
    private Long roleId;
    private Long roleName;
    private String description;
}

这两个表通过另一张表进行关联，实现一个多表联查，打印结果

[com.alibaba.druid.pool.DruidDataSource] - {dataSource-1} inited
[com.cloneZjrt.dao.UserDAO.getUserById] - ==> Preparing: select * from userinfo u left join user_role ur on u.userid = ur.user_id left join roleinfo r on ur.role_id = r.roleid where u.userid = ?
[com.cloneZjrt.dao.UserDAO.getUserById] - > Parameters: 1(Long)
[com.cloneZjrt.dao.UserDAO.getUserById] - < Total: 3
{name=111, roleEntities=[{“roleId”:1,“roleName”:111,“description”:“one”},{“roleId”:2,“roleName”:222,“description”:“two”},{“roleId”:3,“roleName”:333,“description”:“three”}], userId=1}

sql和include

<!--choose加模糊查询-->
<select id="getUserByNameTest" resultType="com.cloneZjrt.model.UserEntity">
    SELECT
    <include refid="User_Column_List" />
    From userinfo WHERE 1 = 1
    <include refid="Query_User" />
</select>

<sql id="User_Column_List">
    username
</sql>

<sql id="Query_User">
    <choose>
        <when test="userName!=null and userName!=''">
            <!--AND username LIKE CONCAT('%',#{userName},'%')-->
            <bind name="userNameLike" value=" '%' + userName + '%' "/>
            AND username LIKE #{userNameLike}
        </when>
        <otherwise>
            AND username = '111'
        </otherwise>
    </choose>
</sql>

可以把这个例子和下面的choose中的代码做比较，很明显，Sql标签可以作为一个通用模块通过include标签进行调用，可以使代码更清晰，也减少工作量。

selectKey

主要效用并不是用来处理自动生成主键的，而是用sql语句来处理，需要特殊处理的表中的列字段，当然也可以用来处理自增id。

• keyColumn：插入数据以后，要返回的内容在数据表中对应的字段名称(目标属性)。
• keyProperty：映射目标实体类的属性。
• order："AFTER"表示这个selectKey语句的执行是在insert语句之后；"BEFORE"表示这个selectKey语句的执行是在insert语句之前。
• resultType：目标属性的类型。
• statementType：设置sql语句的映射类型，主要有:STATEMENT,PREPARED,CALLABLE。

<!--用的不多，这里简单实现一个自增Id的返回-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="userId">
    INSERT INTO userinfo (username)
    values (#{userName})
    <selectKey resultType="java.lang.Long" keyProperty="userId" keyColumn="userid" order="AFTER" >
        SELECT LAST_INSERT_ID()
    </selectKey>
</insert>

order设置为AFTER，插入数据后，才能返回自增ID值

/**
 * Created by Administrator on 2020-1-23.
 */
@RunWith(SpringJUnit4ClassRunner.class)
//告诉junit spring配置文件
@WebAppConfiguration
@ContextConfiguration({"classpath*:applicationContext.xml"})
public class TestDAO {

    @Autowired
    private UserDAO userDAO;

    @Test
    public void testOne() throws Exception {

        UserEntity u1 = new UserEntity("user1");
        int i = userDAO.insertUser(u1);
        System.out.println(i + " == " + u1.getUserId());

    }
}

自增ID的数值不是方法直接返回，而是注入到返回的实体类中对应的ID里面返回

parameterMap

这个标签和resultMap标签的作用类似，一个传入，一个传出，使用不多，资料也不多，博主就简单说说自己的了解。

<?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.cloneZjrt.dao.UserDAO">

    <resultMap id="UserEntityMap" type="com.cloneZjrt.model.UserEntity">
        <id property="userId" column="userid"></id>
        <result property="name" column="username"></result>
        <association property="detailsEntity" javaType="com.cloneZjrt.model.DetailsEntity">
            <id  property="userId" column="user_id"/>
            <result  property="phone" column="phone"/>
            <result  property="description" column="description"/>
        </association>
    </resultMap>

    <parameterMap id="UserParameterMap" type="com.cloneZjrt.model.UserEntity">
        <parameter property="name" resultMap="UserEntityMap" />
    </parameterMap>

    <update id="update" parameterMap="UserParameterMap">
        UPDATE userinfo SET
        username = #{name}
        WHERE userid = #{userId}
    </update>
   
</mapper>

parameterMap的property属性对应实体类中的属性名，然后根据这个属性名，通过resultMap属性，到对应的resultMap里面去找对应的property属性，以及对应的column属性，这样实现一个入参的绑定。

动态SQL标签

动态查询就是根据用户提供的参数，拼接SQL语句，动态决定查询语句依赖的查询条件或SQL语句的内容。

<!--简单的Sql拼接进行动态查询-->
    <select id="getUserByNameTest" resultType="com.cloneZjrt.model.UserEntity">
        SELECT * From userinfo WHERE 1 = 1
        <if test="userName!=null and userName!=''">
            and username = #{userName}
        </if>
    </select>

MyBatis提供了一些标签进行逻辑判断和Sql拼接。

进行Sql拼接时，where后面加上1=1以防后面的条件均不成立造成Sql语句异常

foreach

foreach是进行关于list、array、map的操作

<insert id="insertUsers" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="userId">
        INSERT INTO userinfo (username)
        values
        <foreach collection="list" item="user" index="index" open="(" separator="," close=")">
            #{user.userName}
        </foreach>
    </insert>


    <!-- in查询所有，包含分页 -->
    <select id="queryByIds" resultType="com.cloneZjrt.model.UserEntity">
        select * from userinfo where 1 = 1
        <if test="userIds != null and userIds.size > 0">
            and userid in
            <foreach item="userId" collection="userIds" index="index" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        ORDER BY userid DESC
        limit ${(startRow - 1) * pageSize},#{pageSize}
    </select>

limit a,b 是取从第a个到第b个，所以这里要进行一步逻辑上的运算。

• collection：collection属性的值有三个分别是list、array、map三种，分别对应的参数类型为：List、数组、map集合。
• item ：表示在迭代过程中每一个元素的别名
• index ：表示在迭代过程中每次迭代到的位置（下标）
• open ：前缀
• close ：后缀
• separator ：分隔符，表示迭代时每个元素之间以什么分隔

Dao层

public interface UserDAO {
//    增加列表
    int insertUsers(List<UserEntity> userEntities);
//    批量查询
    List<UserEntity> queryByIds(@Param("userIds")List<Long> userIds,
                                @Param("startRow")Long startRow,
                                @Param("pageSize")Long pageSize);
}

choose、when和otherwise

choose标签下如果有多个when，按照顺序下来，如果test通过，则直接跳出choose语句，若所有的when都不成立，那么会执行otherwise，相当于switch中的default。

<!--choose加模糊查询-->
    <select id="getUserByNameTest" resultType="com.cloneZjrt.model.UserEntity">
        SELECT * From userinfo WHERE 1 = 1
        <choose>
            <when test="userName!=null and userName!=''">
                AND username LIKE CONCAT('%',#{userName},'%')
            </when>
            <otherwise>
                AND username = '111'
            </otherwise>
        </choose>
    </select>

bind

上一个例子中有涉及到模糊查询，使用CONCAT进行字符串的连接，在MySQL中，这个函数支持多个参数，但是在Oracle中只支持两个参数。 由于不同数据库之间的语法差异，如果更换了数据库，有些SQL语句可能就需要重写。

使用bind拼接字符串不仅可以避免因更换数据库而修改SQL，也能预防SQL注入。

<!--choose加模糊查询-->
    <select id="getUserByNameTest" resultType="com.cloneZjrt.model.UserEntity">
        SELECT * From userinfo WHERE 1 = 1
        <choose>
            <when test="userName!=null and userName!=''">
                <!--AND username LIKE CONCAT('%',#{userName},'%')-->
                <bind name="userNameLike" value=" '%' + userName + '%' "/>
                AND username LIKE #{userNameLike}
            </when>
            <otherwise>
                AND username = '111'
            </otherwise>
        </choose>
    </select>

这样稍作修改，也是可以实现功能。

trim

在动态查询的例子中，博主在where后面都加了1=1，这是为了避免条件均不成立造成Sql语句异常，当然也是偷懒一下，毕竟比较简单操作。其实，MyBatis提供了trim标签处理在拼接过程中出现的格式问题。

• prefix：给sql语句拼接的前缀
• suffix：给sql语句拼接的后缀
• prefixOverrides；去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"
• suffixOverrides：去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定

<!--简单的Sql拼接进行动态查询-->
    <select id="getUserByNameTest" resultType="com.cloneZjrt.model.UserEntity">
        SELECT * From userinfo WHERE 1 = 1
        <if test="userName!=null and userName!=''">
            and username = #{userName}
        </if>
    </select>

<select id="getUserByNameTest" resultType="com.cloneZjrt.model.UserEntity">
    SELECT * From userinfo
    <trim prefix="WHERE" prefixOverrides="AND">
        <if test="userName!=null and userName!=''">
            AND username = #{userName}
        </if>
    </trim>
</select>

这两组XML实现的效果是一致，在trim标签下，通过上面四个属性来规范Sql拼接时的格式。

踩坑小记

@Param

• 在传输list、array、map属性时，若没有@Param的注解，collection标签表示的集合只能是list、array、map这三个属性值；若是加上@Param的注解，collection标签中的属性值即为@Param中的值。因为，在传参的时候，会把参数以Map的形式传入，Key就是list、array、map，value就是属性值，所以想要获得属性值，需要list、array、map。

• 在IF的判断中，若是判断没有@Param注解入参的list、array、map属性时，应该用list(array、map).size!=0（根据具体业务进行判断），用入参名做判断会报错，比如ids.siez!=0会提示无法得到ids。解决的方法时加上@Param注解。这一条和上一条的原理时一样的。

• 在涉及到like(模糊查询)，limit(分页查询)，when(选择判断)时，Dao层在传参时一定要有@Param注解

${}和#{}

• Mybatis在处理#{}时，会将sql中的#{}替换为?号，调用PreparedStatement的set方法来赋值，用来防止SQL注入；

• Mybatis在处理$时，就是把${}替换成变量的值。

• limit语句中，接收的参数若是String，那么#{startRow}和#{pageSize}就会报错，因为比如String startRow=“1”，那么MyBatis根据#{}的转化，调用PreparedStatement的set方法来赋值，会再次加上一组引号，变成"“1"”。解决的方案有两个：（1）入参类型改成Long、int等；（2）将#{}改成$，${}是直接将值引入，而不会再加一组引号

ResultMap

• 一对多中，有集合属性，那么这里填写的是集合的泛型，而不是集合本身

• resultType与resultMap 不能并用

可参考博文mybatis常用标签