1.条件查询
<sql id="queryColumn"> id, name name </sql> <select id="findList" resultType="packageName"> SELECT <include refid="queryColumn"></include> FROM T_TABLE A WHERE 1 = 1 <if test="name != null and name != '' "> AND (A.NAME like '%${name}' or A.NAME like '${name}%' or A.NAME like '%${name}%') </if> ORDER BY A.CREATE_TIME DESC </select>
2.函数查询
<select id="queryAreaData" resultType="packageName"> SELECT
A.ID code,
A.NAME name, (SELECT group_concat(CONCAT('{"code":"',t.id,'",'),CONCAT('"name":"',t.NAME,'"}') SEPARATOR '/') FROM (SELECT b.PARENT_ID,b.id,b.NAME FROM ADS_ADDRESS b where b.level_type =3 GROUP BY b.id,b.NAME)t where t.PARENT_ID=a.id) chird FROM
ADS_ADDRESS A WHERE
A.LEVEL_TYPE =2 GROUP BY
A.ID,A.NAME </select>
3.循环查询
<select id="list" resultType="packageName"> SELECT ID id, NAME name FROM T_TABLE A WHERE
A.ID IN <foreach item="item" index="index" collection="array" open="(" separator="," close=")"> #{item} </foreach> </select>
4.循环插入
<insert id="insertList"> INSERT INTO T_TABLE( ID, NAME )VALUES <foreach collection="list" item="item" index="index" open="(" separator="),(" close=")"> #{item.id}, #{item.name} </foreach> </insert>
5.Msp参数判断查询
Map<String, Integer> map = new HashMap<String, Integer>();
map.put("pageIndex", pageIndex);
map.put("pageSize", pageSize);
<select id="queryBouncePage" parameterType="java.util.Map" resultType="JavaBean">
SELECT * FROM T_TABLE WHERE ID IN <foreach collection="ids" item="item" index="index" open="(" separator="," close=")"> #{item} </foreach> <if test="_parameter.containsKey('pageIndex') and _parameter.containsKey('pageSize')"> limit #{pageIndex}, #{pageSize} </if> </select>
6.属性为对象查询
public class StoreInfoDto { private int id; private List<StoreImgDto> imgDtos;// 店铺图片 private List<StoreLabelDto> labelDtos;// 店铺标签 private List<StoreReviewDto> reviewDtos;// 店铺评论 private List<StoreErrorDto> errorDtos;// 顾客报错信息 private ProvinceDto provinceDto;// 省 private CityDto cityDto;// 市 private AreaDto areaDto;// 区 private String merchantNo;// 商户编号 private String code;// 编码CODE private String name;// 店名 private String alias;// 店别名 private String address;// 店铺地址 private String addressDetail;// 店铺地址详情 ....... get、set.......
}
<sql id="storeInfoColumn"> A.ID, A.MERCHANT_NO, A.CODE, A.NAME, A.ALIAS, A.ADDRESS, A.ADDRESS_DETAIL, A.NEAR_AREA, A.LONGITUDE, A.LATITUDE, A.STATUS, A.PHONE, A.PHONE_STANDBY, A.MOBILE, A.MOBILE_STANDBY, A.OFFICE_HOURS, A.OFFICE_HOURS_PERIODS, A.OFFICE_HOURS_STANDBY, A.OFFICE_HOURS_PERIODS_STANDBY, A.REVIEW_TIMES, A.EFFECT_SCORE, A.ENVIRONMENT_SCORE, A.SERVER_SCORE, A.FAVORITES_TIMES, A.REMARK, A.CREATE_BY, A.CREATE_TIME, A.UPDATE_BY, A.UPDATE_TIME </sql> <sql id="storeImgColumn"> B.ID B_ID, B.STORE_ID B_STORE_ID, B.TITLE B_TITLE, B.SOURCE B_SOURCE, B.LARGE B_LARGE, B.MEDIUM B_MEDIUM, B.THUMBNAIL B_THUMBNAIL, B.IMAGE_PATH B_IMAGE_PATH, B.TYPE B_TYPE, B.STATUS B_STATUS, B.ORDER_BY B_ORDER_BY, B.CREATE_BY B_CREATE_BY, B.CREATE_TIME B_CREATE_TIME, B.UPDATE_BY B_UPDATE_BY, B.UPDATE_TIME B_UPDATE_TIME </sql> <sql id="storeLableColumn"> C.ID C_ID, C.STORE_ID C_STORE_ID, C.NAME C_NAME, C.CODE C_CODE, C.LARGE C_LARGE, C.ORDER_BY C_ORDER_BY, C.CREATE_BY C_CREATE_BY, C.CREATE_TIME C_CREATE_TIME, C.UPDATE_BY C_UPDATE_BY, C.UPDATE_TIME C_UPDATE_TIME </sql> <sql id="storeReviewColumn"> D.ID D_ID, D.USER_ID D_USER_ID, D.STORE_ID D_STORE_ID, D.CONTENT D_CONTENT, D.IP D_IP, D.IS_SHOW D_IS_SHOW, D.STATUS D_STATUS, D.SCORE D_SCORE, D.EFFECT_SCORE D_EFFECT_SCORE, D.ENVIRONMENT_SCORE D_ENVIRONMENT_SCORE, D.SERVER_SCORE D_SERVER_SCORE, D.HIT_TIMES D_HIT_TIMES, D.REMARKS D_REMARKS, D.CREATE_BY D_CREATE_BY, D.CREATE_TIME D_CREATE_TIME, D.UPDATE_BY D_UPDATE_BY, D.UPDATE_TIME D_UPDATE_TIME </sql> <sql id="storeErrorColumn"> E.ID E_ID, E.USER_ID E_USER_ID, E.STORE_ID E_STORE_ID, E.ERROR_TYPE_CODE E_ERROR_TYPE_CODE, E.REPETITION_STORE_ID E_REPETITION_STORE_ID, E.NEW_LONGITUDE E_NEW_LONGITUDE, E.NEW_LATITUDE E_NEW_LATITUDE, E.AFFIRM_STORE_ID E_AFFIRM_STORE_ID, E.CREATE_BY E_CREATE_BY, E.CREATE_TIME E_CREATE_TIME, E.UPDATE_BY E_UPDATE_BY, E.UPDATE_TIME E_UPDATE_TIME </sql> <sql id="storeReviewImgColumn"> F.ID F_ID, F.STORE_ID F_STORE_ID, F.STORE_REVIEW_ID F_STORE_REVIEW_ID, F.SOURCE F_SOURCE, F.LARGE F_LARGE, F.MEDIUM F_MEDIUM, F.THUMBNAIL F_THUMBNAIL, F.IMAGE_PATH F_IMAGE_PATH, F.HIT_TIMES F_HIT_TIMES, F.ORDER_BY F_ORDER_BY, F.CREATE_BY F_CREATE_BY, F.CREATE_TIME F_CREATE_TIME, F.UPDATE_BY F_UPDATE_BY, F.UPDATE_TIME F_UPDATE_TIME </sql> <sql id="provinceColumn"> G.ID G_ID, G.NAME G_NAME </sql> <sql id="cityColumn"> H.ID H_ID, H.PROVINCE_ID H_PROVINCE_ID, H.NAME H_NAME </sql> <sql id="areaColumn"> I.ID I_ID, I.CITY_ID I_CITY_ID, I.NAME I_NAME </sql> <sql id="queryColumn"> <include refid="storeInfoColumn"></include>, <include refid="storeImgColumn"></include>, <include refid="storeLableColumn"></include>, <include refid="storeReviewColumn"></include>, <include refid="storeErrorColumn"></include>, <include refid="storeReviewImgColumn"></include>, <include refid="provinceColumn"></include>, <include refid="cityColumn"></include>, <include refid="areaColumn"></include> </sql> <select id="queryStoreList" resultMap="storeInfoMap"> SELECT <include refid="queryColumn"></include> FROM lrm_store_info a LEFT JOIN lrm_store_image b ON a.id = b.store_id LEFT JOIN lrm_store_label c ON a.id = c.store_id LEFT JOIN lrm_store_review d ON a.id = d.store_id LEFT JOIN lrm_store_error e ON a.id = e.store_id LEFT JOIN lrm_store_review_image f ON d.id = f.store_review_id LEFT JOIN lrm_dict_provinces g ON a.privince_id = g.id LEFT JOIN lrm_dict_citys h ON a.city_id = h.id LEFT JOIN lrm_dict_areas i ON a.area_id = i.id </select> <resultMap id="storeInfoMap" type="cn.ibaiyou.bf.modules.lrm.wechat.common.store.entity.StoreInfoDto"> <id column="id" property="id" /> <result column="merchant_no" property="merchantNo" /> <result column="code" property="code" /> <result column="name" property="name" /> <result column="alias" property="alias" /> <result column="address" property="address" /> <result column="address_detail" property="addressDetail" /> <result column="near_area" property="nearArea" /> <result column="longitude" property="longitude" /> <result column="latitude" property="latitude" /> <result column="status" property="status" /> <result column="phone" property="phone" /> <result column="phone_standby" property="phoneStandby" /> <result column="mobile" property="mobile" /> <result column="mobile_standby" property="mobileStandby" /> <result column="office_hours" property="officeHours" /> <result column="office_hours_periods" property="officeHoursPeriods" /> <result column="office_hours_standby" property="officeHoursStandby" /> <result column="office_hours_periods_standby" property="officeHoursPeriodsStandby" /> <result column="review_times" property="reviewTimes" /> <result column="effect_score" property="effectScore" /> <result column="environment_score" property="environmentScore" /> <result column="server_score" property="serverScore" /> <result column="favorites_times" property="favoritesTimes" /> <result column="remark" property="remark" /> <result column="create_by" property="createBy" /> <result column="create_time" property="createTime" /> <result column="update_by" property="updateBy" /> <result column="update_time" property="updateTime" /> <association property="provinceDto" resultMap="provinceMap" columnPrefix="G_" /> <association property="cityDto" resultMap="cityMap" columnPrefix="H_"/> <association property="areaDto" resultMap="areaMap" columnPrefix="I_"/> <collection property="imgDtos" resultMap="storeImgMap" columnPrefix="B_"/> <collection property="labelDtos" resultMap="storeLabelMap" columnPrefix="C_"/> <collection property="reviewDtos" resultMap="storeReviewMap" columnPrefix="D_"/> <collection property="errorDtos" resultMap="storeErrorMap" columnPrefix="E_"/> </resultMap> <resultMap id="storeImgMap" type="cn.ibaiyou.bf.modules.lrm.wechat.common.store.entity.StoreImgDto"> <id column="id" property="id" /> <result column="store_id" property="storeId" /> <result column="title" property="title" /> <result column="source" property="source" /> <result column="large" property="large" /> <result column="medium" property="medium" /> <result column="thumbnail" property="thumbnail" /> <result column="image_path" property="imagePath" /> <result column="type" property="type" /> <result column="status" property="status" /> <result column="order_by" property="orderBy" /> <result column="create_by" property="createBy" /> <result column="create_time" property="createTime" /> <result column="update_by" property="updateBy" /> <result column="update_time" property="updateTime" /> </resultMap> <resultMap id="storeLabelMap" type="cn.ibaiyou.bf.modules.lrm.wechat.common.store.entity.StoreLabelDto"> <id column="id" property="id" /> <result column="store_id" property="storeId" /> <result column="name" property="name" /> <result column="code" property="code" /> <result column="large" property="large" /> <result column="order_by" property="orderBy" /> <result column="create_by" property="createBy" /> <result column="create_time" property="createTime" /> <result column="update_by" property="updateBy" /> <result column="update_time" property="updateTime" /> </resultMap> <resultMap id="storeReviewMap" type="cn.ibaiyou.bf.modules.lrm.wechat.common.store.entity.StoreReviewDto"> <id column="id" property="id" /> <result column="user_id" property="userId" /> <result column="store_id" property="storeId" /> <result column="content" property="content" /> <result column="ip" property="ip" /> <result column="is_show" property="isShow" /> <result column="status" property="status" /> <result column="score" property="score" /> <result column="effect_score" property="effectScore" /> <result column="environment_score" property="environmentScore" /> <result column="server_score" property="serverScore" /> <result column="hit_times" property="hitTimes" /> <result column="remarks" property="remarks" /> <result column="create_by" property="createBy" /> <result column="create_time" property="createTime" /> <result column="update_by" property="updateBy" /> <result column="update_time" property="updateTime" /> <collection property="reviewImgDtos" resultMap="storeReviewImgMap" columnPrefix="F_"/> </resultMap> <resultMap id="storeReviewImgMap" type="cn.ibaiyou.bf.modules.lrm.wechat.common.store.entity.StoreReviewImgDto"> <id column="id" property="id" /> <result column="store_id" property="storeId" /> <result column="store_review_id" property="storeReviewId" /> <result column="source" property="source" /> <result column="large" property="large" /> <result column="medium" property="medium" /> <result column="thumbnail" property="thumbnail" /> <result column="image_path" property="imagePath" /> <result column="hit_times" property="hitTimes" /> <result column="order_by" property="orderBy" /> <result column="create_by" property="createBy" /> <result column="create_time" property="createTime" /> <result column="update_by" property="updateBy" /> <result column="update_time" property="updateTime" /> </resultMap> <resultMap id="storeErrorMap" type="cn.ibaiyou.bf.modules.lrm.wechat.common.store.entity.StoreErrorDto"> <id column="id" property="id" /> <result column="user_id" property="userId" /> <result column="store_id" property="storeId" /> <result column="source" property="source" /> <result column="error_type_code" property="errorTypeCode" /> <result column="repetition_store_id" property="repetitionStoreId" /> <result column="new_longitude" property="newLongitude" /> <result column="new_latitude" property="newLatitude" /> <result column="affirm_store_id" property="affirmStoreId" /> <result column="create_by" property="createBy" /> <result column="create_time" property="createTime" /> <result column="update_by" property="updateBy" /> <result column="update_time" property="updateTime" /> </resultMap> <resultMap id="provinceMap" type="cn.ibaiyou.bf.modules.lrm.wechat.common.dict.ProvinceDto"> <id column="id" property="id" /> <result column="name" property="name" /> </resultMap> <resultMap id="cityMap" type="cn.ibaiyou.bf.modules.lrm.wechat.common.dict.CityDto"> <id column="id" property="id" /> <result column="province_id" property="provinceId" /> <result column="name" property="name" /> </resultMap> <resultMap id="areaMap" type="cn.ibaiyou.bf.modules.lrm.wechat.common.dict.AreaDto"> <id column="id" property="id" /> <result column="city_id" property="cityId" /> <result column="name" property="name" /> </resultMap>
7.属性为对象插入
<insert id="insertEpaper"> INSERT INTO T_TABLE( ID, USER_ID, NAME )VALUES <foreach collection="list" item="item" index="index" open="(" separator="),(" close=")"> #{item.id}, #{item.userDto.id}, #{item.name} </foreach> </insert>
8.批量修改 jdbc:mysql://127.0.0.1:3306/database?allowMultiQueries=true <update id="updateList"> <foreach collection="list" item="item" index="index" open="" close="" separator=";"> UPDATE tableName SET name = #{item.name}, update_time = DATE_FORMAT(#{item.updateTime},'%Y-%c-%d %H:%i:%s') WHERE id = #{item.id} </foreach> </update>
9.批量删除
<delete id="deleteList" parameterType="String">
Delete from t_base_user_role WHERE user_id in
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item.userId}
</foreach>
</delete>