mybatis-SQL · TUNA-daily

[TOC] ## 1. SQL中的前綴和后綴 `<trim>`標簽 > 1. prefix: > 在trim標簽內sql語句加上前綴。 > 2. suffix: > 在trim標簽內sql語句加上后綴。 > 3. suffixOverrides: > 指定去除多余的后綴內容，如：suffixOverrides=","，去除trim標簽內sql語句多余的后綴","。 > 4. prefixOverrides: > 指定去除多余的前綴內容 > ~~~ <select id="listMenu" resultType="net.aexit.inner.aexithome.common.model.SysMenu"> SELECT distinct m.* FROM sys_menu m LEFT JOIN sys_role_menu rm ON rm.menu_id = m.id // 加了前綴WHERE，去掉前后綴的AND或OR <trim prefix="WHERE" prefixOverrides="AND|OR" suffixOverrides="AND|OR"> <if test="roleId !=null and roleId != '' " > AND rm.role_id = #{roleId} </if> <if test="menuName !=null and menuName != '' " > AND name LIKE CONCAT('%',#{menuName},'%') </if> <if test="level !=null and level != '' " > AND level = #{level} </if> </trim> ORDER BY m.sort </select> ~~~ ## 2. 參數非空查詢用戶的檢車記錄 * 首先默認查詢該用戶的所有的檢車記錄 ![](https://box.kancloud.cn/3f1be045b7d80fa31e4abcfddf598550_537x506.png) * 提供根據車牌模糊搜索自己的檢車記錄 ![](https://box.kancloud.cn/da631623284d86c7515c2aa579c09ced_670x457.png) * 檢測記錄表某一輛車的檢車記錄（車牌號） ![](https://box.kancloud.cn/d3e2e44ea10c65285fe97bdb179d592b_816x445.png) * 用戶的車輛信息（用戶id和車牌號） ![](https://box.kancloud.cn/53aa0e6eb644b67229e19d297e2731ca_881x437.png) 1. controller ~~~ /** * 查詢用戶檢測記錄 * @param userId 用戶編號 * @param vehicleNo 車牌號（可以為空，此時查詢用戶所有車輛） * @return */ @RequestMapping(value = "/get_recored_byuserid",method = RequestMethod.GET) @ResponseBody public List<EdsDetectionRecord> getRecordByUserId(@RequestParam String userId, @RequestParam(required = false)String vehicleNo){ return bizService.getByUserId(userId,vehicleNo); } ~~~ 2. service ~~~ /** * 獲取檢測記錄列表 * @param userId 用戶id * @return */ public List<EdsDetectionRecord> getByUserId(String userId,String vehicleNo){ return edsDetectionRecordMapper.selectByUserId(userId,vehicleNo); } ~~~ 3. mapper ~~~ List<EdsDetectionRecord> selectByUserId(@Param("userId")String userId,@Param("vehicleNo")String vehicleNo); ~~~ 4. xml ~~~ <select id="selectByUserId" parameterType="java.lang.String" resultMap="BaseResultMap"> SELECT r.* FROM eds_detection_record r INNER JOIN app_vehicle a ON r.vehicle_no = a.vehicle_no AND a.user_id=#{userId,jdbcType=VARCHAR} <if test="vehicleNo != null"> AND r.vehicle_no LIKE "%"#{vehicleNo,jdbcType=VARCHAR}"%" </if> ORDER BY r.detect_date DESC ; </select> ~~~ * 如果vehicleNo參數非空，則將下邊的語句加入SQL ~~~ <if test="vehicleNo != null"> AND r.vehicle_no LIKE "%"#{vehicleNo,jdbcType=VARCHAR}"%" </if> ~~~ 如果vehicleNo參數非空，相當于 ~~~ SELECT r.* FROM eds_detection_record r INNER JOIN app_vehicle a ON r.vehicle_no = a.vehicle_no AND a.user_id=#{userId,jdbcType=VARCHAR} and r.vehicle_no LIKE ‘%vehicleNo%’ ~~~