MyBatis 常用SQL汇总
resultMap映射
<!-- resultMap映射 -->
<resultMap id="BaseResultMap" type="cn.scpro.model.UserInfo">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="doccode" property="doccode" jdbcType="VARCHAR"/>
<result column="telno" property="telno" jdbcType="VARCHAR"/>
</resultMap>
实体类查询条件
<!-- 实体类查询条件 -->
<sql id="conditionExample">
<where>
<if test="example.id != null">
AND id = #{example.id}
</if>
<if test="example.name != null">
AND name = #{example.name}
</if>
<if test="example.doccode != null">
AND doccode = #{example.doccode}
</if>
<if test="example.telno != null">
AND telno = #{example.telno}
</if>
</where>z`
</sql>
查询字段名
< ! -- 查询字段名 -- >
< sql id= "Base_Column_List" >
id,
name,
doccode,
telno
< / sql>
主键查询
< ! -- 主键查询 -- >
< select id= "selectByPrimaryKey" resultMap= "BaseResultMap"
parameterType= "java.lang.Integer" >
select
< include refid= "Base_Column_List" / >
from user_info
where id = #{ id}
< / select>
插入数据 返回主键1
< ! -- 插入数据 返回主键1 -- >
< insert id= "insertSelective" parameterType= "cn.scpro.model.UserInfo"
useGeneratedKeys= "true" keyProperty= "id" >
insert into user_info
< trim prefix= "(" suffix= ")" suffixOverrides= "," >
< if test= "id != null" >
id,
< / if >
< if test= "name != null" >
name,
< / if >
< if test= "doccode != null" >
doccode,
< / if >
< if test= "telno != null" >
telno,
< / if >
< / trim>
< trim prefix= "values (" suffix= ")" suffixOverrides= "," >
< if test= "id != null" >
#{ id} ,
< / if >
< if test= "name != null" >
#{ name, jdbcType= VARCHAR} ,
< / if >
< if test= "doccode != null" >
#{ doccode} ,
< / if >
< if test= "telno != null" >
#{ telno, jdbcType= VARCHAR} ,
< / if >
< / trim>
< / insert>
插入数据返回主键2
< ! -- 插入数据返回主键2 -- >
< insert id= "insertTaskHistory" parameterType= "cn.scpro.model.UserInfo" >
insert into user_info
< trim prefix= "(" suffix= ")" suffixOverrides= "," >
< if test= "userinfo.name != null" >
name,
< / if >
< if test= "userinfo.doccode != null" >
doccode,
< / if >
< if test= "userinfo.telno != null" >
telno,
< / if >
< / trim>
< trim prefix= "values (" suffix= ")" suffixOverrides= "," >
< if test= "userinfo.name != null" >
#{ userinfo. name, jdbcType= VARCHAR} ,
< / if >
< if test= "userinfo.doccode != null" >
#{ userinfo. doccode, jdbcType= VARCHAR} ,
< / if >
< if test= "userinfo.telno != null" >
#{ userinfo. telno, jdbcType= VARCHAR} ,
< / if >
< / trim>
< selectKey resultType= "java.lang.Integer" order= "AFTER" keyProperty= "userinfo.id" >
SELECT id FROM user_info ORDER BY id DESC LIMIT 0 , 1
< / selectKey>
< / insert>
更新数据
< ! -- 更新数据 -- >
< update id= "updateByPrimaryKeySelective" parameterType= "cn.scpro.model.UserInfo" >
update user_info
< set>
< if test= "name != null" >
name = #{ userinfo. name, jdbcType= VARCHAR} ,
< / if >
< if test= "doccode != null" >
doccode = #{ userinfo. doccode} ,
< / if >
< if test= "telno != null" >
telno = #{ userinfo. telno, jdbcType= VARCHAR} ,
< / if >
< / set>
where id = #{ userinfo. id}
< / update>
插入list
< ! -- 插入list -- >
< insert id= "insertNotifylist" parameterType= "java.util.List" useGeneratedKeys= "true" >
insert into user_info
values
< foreach collection= "userlist" item= "item" index= "index" separator= "," >
( #{ item. name, jdbcType= VARCHAR} , #{ item. doccode, jdbcType= VARCHAR} ,
#{ item. telno, jdbcType= VARCHAR} )
< / foreach>
< / insert>
更新list
< ! -- 更新list-- >
< update id= "UpdateById" >
update user_info set name= null
where id in
< foreach collection= "list" item= "id" separator= "," open= "("
close= ")" >
#{ id}
< / foreach>
< / update>
删除list
< ! -- 删除list -- >
< delete id= "DeleteById" parameterType= "java.util.List" >
delete from user_info where id in
< foreach collection= "list" item= "id" separator= "," open= "("
close= ")" >
#{ id}
< / foreach>
< / delete>
常用类型
< resultMap id= "BaseResultMap" type= "com.test.model.User" >
< id column= "id" property= "id" jdbcType= "VARCHAR" / > 字符串
< result column= "newtime" property= "newTime" jdbcType= "TIMESTAMP" / > 时间
< result column= "userid" property= "userId" jdbcType= "INTEGER" / > 整数
< result column= "money" property= "money" jdbcType= "DECIMAL" / > 小数
< / resultMap>
继承map,可查多项个实体
< ! -- 继承map,可查多项个实体 -- >
< resultMap id= "BaseResultMapForMore" type= "com.test.model.User"
extends = "BaseResultMap" >
< collection property= "score"
ofType= "com.test.model.Score" column= "userid"
javaType= "com.test.model.Score"
select= "com.test.mapper.ScoreMapper.findByuserid" >
< / collection>
< collection property= "teacherlist"
ofType= "com.test.model.Teacher" column= "teacherid"
javaType= "com.test.model.Teacher"
select= "com.test.model.TeacherMapper.findById" >
< / collection>
< ! -- < collection property= "homeinfo" -- >
< ! -- ofType= "com.test.model.Home" column= "homeid" -- >
< ! -- javaType= "com.test.model.Home" -- >
< ! -- select= "com.test.mapper.HomeMapper.findById" > -- >
< ! -- < / collection> -- >
< / resultMap>