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;
<--需要提交事务-->