Mybatis动态sql实例
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.chinaunicom.asset.server.assetpublish.dao.AssetPublishTransLogMapper"> <resultMap id="BaseResultMap" type="com.chinaunicom.asset.server.assetpublish.entity.AssetPublishTransLogEntity" > <result column="id" jdbcType="BIGINT" property="id" /> <result column="version_id" jdbcType="BIGINT" property="versionId" /> <result column="asset_id" jdbcType="BIGINT" property="assetId" /> <result column="version_name" jdbcType="VARCHAR" property="versionName" /> <result column="publisher_id" jdbcType="VARCHAR" property="publisherId" /> <result column="version_note" jdbcType="VARCHAR" property="versionNote" /> <result column="screen_urls" jdbcType="VARCHAR" property="screenUrls" /> <result column="cover_img_url" jdbcType="VARCHAR" property="coverImgUrl" /> <result column="description" jdbcType="VARCHAR" property="description" /> <result column="package_id" jdbcType="BIGINT" property="packageId" /> <result column="sketch_url" jdbcType="VARCHAR" property="sketchUrl" /> <result column="create_time" jdbcType="TIMESTAMP" property="createTime" /> <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" /> <result column="is_delete" jdbcType="INTEGER" property="deleted" /> <result column="asset_type_id" jdbcType="INTEGER" property="assetTypeId" /> <result column="asset_subtype_id" jdbcType="INTEGER" property="assetSubTypeId" /> </resultMap> <sql id="Base_Column_List"> id, version_id, asset_id, version_name, publisher_id, version_note, screen_urls , cover_img_url, description, package_id, sketch_url, create_time, update_time, is_delete, asset_type_id, asset_subtype_id </sql> <insert id="insert" useGeneratedKeys="true" keyColumn="id" keyProperty="id" parameterType="com.chinaunicom.asset.server.assetpublish.entity.AssetPublishTransLogEntity"> INSERT INTO asset_publish_trans <trim prefix="(" suffix=")" suffixOverrides=","> version_id, asset_id, version_name, publisher_id, <if test="null != versionNote and '' != versionNote"> version_note, </if> <if test="null != coverImgUrl and '' != coverImgUrl"> cover_img_url, </if> <if test="null != screenUrls and '' != screenUrls"> screen_urls, </if> <if test="null != description and '' != description"> description, </if> package_id, sketch_url, create_time, is_delete, asset_type_id, asset_subtype_id </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> #{versionId}, #{assetId}, #{versionName}, #{publisherId}, <if test="null != versionNote and '' != versionNote"> #{versionNote}, </if> <if test="null != coverImgUrl and '' != coverImgUrl"> #{coverImgUrl}, </if> <if test="null != screenUrls and '' != screenUrls"> #{screenUrls}, </if> <if test="null != description and '' != description"> #{description}, </if> #{packageId}, #{sketchUrl}, now(), #{deleted}, #{assetTypeId}, #{assetSubTypeId} </trim> </insert> <update id="delete" > UPDATE asset_publish_trans SET is_delete = 1 WHERE version_id = #{versionId} </update> <update id="update" parameterType="com.chinaunicom.asset.server.assetpublish.entity.AssetPublishTransLogEntity"> UPDATE asset_publish_trans <set> description = #{description}, asset_type_id = #{assetTypeId}, asset_subtype_id = #{assetSubTypeId}, cover_img_url = #{coverImgUrl}, update_time = #{updateTime}, sketch_url = #{sketchUrl} </set> WHERE version_id = #{versionId} </update> <select id="queryAssetPubLogByVersionId" resultMap="BaseResultMap"> SELECT <include refid="Base_Column_List" /> FROM asset_publish_trans where version_id = #{versionId} and is_delete =0 </select> <select id="queryAssetPubLogByAssetId" resultMap="BaseResultMap"> SELECT <include refid="Base_Column_List" /> FROM asset_publish_trans where asset_id = #{assetId} and is_delete =0 </select> <select id="obtainOldestPublishLog" resultMap="BaseResultMap"> SELECT <include refid="Base_Column_List" /> FROM asset_publish_trans where asset_id = #{assetId} and version_name = #{versionName} and is_delete =0 order by create_time limit 1 </select> <select id="obtainNewestPublishLog" resultMap="BaseResultMap"> SELECT <include refid="Base_Column_List" /> FROM asset_publish_trans where asset_id = #{assetId} and is_delete =0 order by create_time desc limit 1 </select> <select id="queryAssetPubLogByAssetIdAndVersion" resultMap="BaseResultMap"> SELECT <include refid="Base_Column_List" /> FROM asset_publish_trans where asset_id = #{assetId} <if test="null != assetVersion and '' != assetVersion"> and version_name = #{assetVersion} </if> and is_delete =0 order by create_time desc limit 1 </select> <select id="publishCountByPackageId" resultType="int"> SELECT count(1) FROM asset_publish_trans where package_id = #{packageId} and is_delete =0 </select> </mapper>