mybatis动态SQL

insert插入

//insert 数据库后面的if判断的不是列，是values后面的字段值
 <insert id="save" parameterType="Custom">
        insert into t_custom
        (   username,
            password,
        <if test="name!=null and !name.trim().isEmpty()">name, </if>
        <if test="gender!=null and !gender.trim().isEmpty()">gender, </if>
        <if test="birthday!=null ">birthday, </if>
        <if test="registIp!=null and !registIp.trim().isEmpty()">regist_ip, </if>
        <if test="registDate!=null ">regist_date, </if>
            id
        ) values (
            #{username},
            #{password},
        <if test="name!=null and !name.trim().isEmpty()">#{name}, </if>
        <if test="gender!=null and !gender.trim().isEmpty()"> #{gender}, </if>
        <if test="birthday!=null ">#{birthday,jdbcType=DATE}, </if>
        <if test="registIp!=null and !registIp.trim().isEmpty()">#{registIp}, </if>
        <if test="registDate!=null ">#{registDate,jdbcType=DATE}, </if>
            #{id} )

        <selectKey resultType="int" keyProperty="id" order="BEFORE" databaseId="mysql">
            SELECT ifnull( max(id) , 0 ) + 1 FROM t_custom
        </selectKey>
        <selectKey resultType="int" keyProperty="id" order="BEFORE" databaseId="oracle">
            SELECT nvl( max(id) , 0 ) + 1 FROM t_custom
        </selectKey>
    </insert>

update更新

错误示例，会多出 ，使SQL错误，在执行过过程中需要去除 ，

<set></set> 标签会自动去除多余符号

<trim></trim> 不好把握  

<update id="update" parameterType="Custom">
        update t_custom
        <set>
             <if test="username!=null and !username.trim().isEmpty()">  username =#{username}, </if>
             <if test="password!=null and !password.trim().isEmpty()">  password = #{password},</if>
             <if test="name!=null and !name.trim().isEmpty()">      name = #{name},</if>
             <if test="gender!=null and !gender.trim().isEmpty()">    gender = #{gender},</if>
             <if test="birthday!=null">                   birthday = #{birthday,jdbcType=DATE},</if>
             <if test="registIp!=null and !registIp.trim().isEmpty()">  regist_ip = #{registIp},</if>
             <if test="registDate!=null ">                regist_date= #{registDate,jdbcType=DATE} </if>
        </set>
        where id=#{id}
    </update>

find查询

数据库中regist_date 对于java中的registIp，查询应该为 select regist_ip registIp,，这里没有为select 的列名没有起别名是因为在mybatis.xml配置文件中声明
<settings>
        <!--启用下划线命名自动转换驼峰命名
            如 register_date   -> registerDate
        -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

<!-- 注意这里的 parameterType 书写的是在 typeAlias 指定的别名 -->
<!-- 这里没有使用 resultMap 而是使用 resultType -->
<!-- 必须保证查询结果中的 列名或列别名 与 Customer 类中的 属性名 对应 -->
<!-- 如果有 MyBatis不能处理的类型，则需要首先在 MyBatis 配置文件中声明 typeHandler -->
<select id="find" parameterType="Integer" resultType="Custom" >
        select
            id,
            username,
            password ,
            name,
            gender,
            birthday,
            regist_ip ,
            regist_date 
        from t_custom
        where id=#{id}
    </select>

多条件查询

<select id="query" resultType="Custom" >
        select
            id,
            username,
            password ,
            name,
            gender,
            birthday,
            regist_ip ,
            regist_date 
        from t_custom
        <where>
            <if test="id!=null">
                and id=#{id}
            </if>
            <if test="username!=null and ！username.trim().isEmpty()">
                and username=#{username}
            </if>
        </where>
    </select>

 mapper接口

 /**
     * 注解@param 为CustomsMapper 显式指定了参数名称
     * @param id 作为条件进行查询时使用的主键值
     * @param username 作为条件进行查询时使用的用户名
     * @return
     */
    Custom query(@Param("id") Integer id,@Param("username") String username);

java测试类

//    两个参数
    public @Test void testQuery() {
//        Custom custom = mapper.query(2,null);
        Custom custom = mapper.query(null,"哼小白");
        System.out.println(custom);
        Assert.assertNotNull(custom);
    }

sql片段

<!--声明SQL片段-->
    <sql id="baseQuery">
        select
            id,
            username,
            password ,
            name,
            gender,
            birthday,
            regist_ip ,
            regist_date 
        from t_custom
    </sql>

使用SQL片段

<select id="find" parameterType="Integer" resultType="Custom" >
        /*使用include 标签将SQL片段包含进来 */
        <include refid="baseQuery"></include>
        where id=#{id}
</select>

<where> <if>

<select id="query" resultType="Custom" >
        <include refid="baseQuery"></include>
        <where>
            <if test="id!=null">
                and id=#{id}
            </if>
            <if test="username!=null and !username.trim().isEmpty()">
                and username=#{username}
            </if>
        </where>
</select>

<where> <choose> <when>

 <select id="findByKeyChoose" resultType="Custom" >
        <include refid="baseQuery"></include>
        <where>
            <choose>
                <when test="id!=null">
                    and id=#{id}
                </when>
                <when test="username!=null and username!=''">
                    and username=#{username}
                </when>
                <otherwise>
                    1=2 //保证上述条件都不满足时，不会有数据返回
                </otherwise>
            </choose>
        </where>
    </select>

模糊查询

    <select id="findByKey" resultType="Custom">
        <include refid="baseQuery"/>
        <where>
            <if test="key!=null and key!=''">
                <bind name="usernameLike" value="'%'+key+'%'"/>
                username like #{usernameLike}
            </if>
        </where>
    </select>

批量插入

mysql

insert into t_custom (id,username,password,regist_date)
	values
		(7,'唐伯虎','123456',now()),
		(8,'菜鸟','12123',now()),
		(9,'悟空','456456',now());
		
<!-- oracle -->

SQL> insert all
     into t_custom (id,username,password,regist_date) values (1,'唐伯虎','123456',sysdate)
     into t_custom (id,username,password,regist_date) values (2,'小白','1222',sysdate)
     into t_custom (id,username,password,regist_date) values (3,'菜鸟','22356',sysdate)
    select * from dual;
    <--需要提交事务-->