Mybatis 之 SQL生成技巧
一、增
1、<trim> 和<if>实现数据插入
<insert id="addInOrder" parameterType="XXX.model.InOrder"> INSERT INTO inorder <trim prefix="(" suffix=")" suffixOverrides="," > <if test="inId != null and inId !=''" > inId, </if> <if test="gender != null and gender !=''" > gender, </if> <if test="modifiedDate != null and modifiedDate !=''" > modifiedDate, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="inId != null and inId !=''" > #{inId,jdbcType=VARCHAR}, </if> <if test="gender != null and gender !=''" > #{gender,jdbcType=INTEGER}, </if> <if test="modifiedDate != null and modifiedDate !=''" > #{modifiedDate,jdbcType=VARCHAR}, </if> </trim> </insert>
2、新增时返回新增数据的 获取新增数据的 id值 <selectKey>
<insert id="addUser" parameterType="com.hotel3.model.User"> <selectKey keyProperty="userId" keyColumn="userId" resultType="int" order="AFTER"> select last_insert_id(); </selectKey> INSERT INTO USER (userName,userPassword,userType) VALUES(#{userName},#{userPassword},#{userType}); </insert>
结果:
==> Preparing: INSERT INTO USER (userName,userPassword,userType) VALUES(?,?,?); ==> Parameters: Not_Copy(String), 222222(String), null <== Updates: 1 ==> Preparing: select last_insert_id(); ==> Parameters: <== Total: 1
结论:select last_insert_id(); 将插入数据的主键返回到 user 对象中。
二、删
<delete id="deleteInOrderById" parameterType="java.lang.String" > delete from inorder where inId = #{inId,jdbcType=VARCHAR} </delete>
三、改
Controller层传值代码:
@PostMapping("/OverInOrder")
@ResponseBody
@Transactional
public Message OverInOrder(@RequestParam(value = "InIds[]")String[] InIds, HttpSession session){
User user= (User)session.getAttribute("loginUser");
inOrderService.OverInOrder(InIds,"结单",user.getUserName());
return new Message("", "success");
}
DAO层代码:
public int OverInOrder(@Param("inIds") String[] inIds, @Param("status") String status, @Param("modifiedBy") String modifiedBy);
Mapper 的XML文件:
<update id="OverInOrder" parameterType="java.lang.String"> update inorder <set > <if test="status != null and status !=''" > status=#{status,jdbcType=VARCHAR}, </if> <if test="modifiedBy != null and modifiedBy !=''" > modifiedBy=#{modifiedBy,jdbcType=VARCHAR}, </if> modifiedDate= SYSDATE(), outTime= SYSDATE() </set> WHERE inId IN <foreach collection="inIds" item="inId" index="index" open="(" close=")" separator=","> #{inId} </foreach> </update>
四、查
<select id="getInOrderAll" resultType="XXX.model.InOrder" parameterType="XXX.model.InOrder"> select * from inorder <trim prefix="where 1=1" suffix=" " suffixOverrides="," > <if test="inId != null and inId !=''" > and inId=#{inId,jdbcType=VARCHAR} </if> <if test="gender != null and gender !=''" > and gender=#{gender,jdbcType=INTEGER} </if> <if test="inTime != null and inTime !=''" > and inTime>=#{inTime,jdbcType=VARCHAR} </if> </trim> ORDER BY createrDate DESC; </select>
1、模糊查询
<select id="getRoom" resultType="XXX.model.Room"> select * from room where roomId like CONCAT('%',#{roomId},'%' ) </select>
待续。。。
关于SQL查询的技巧还有很多,像<where>和<selectKey>等。。。
待续。。。