mybatis基础 sql写法

最新推荐文章于 2024-09-30 18:56:07 发布

郭优秀的笔记

最新推荐文章于 2024-09-30 18:56:07 发布

阅读量459

点赞数

分类专栏： DB

本文链接：https://blog.csdn.net/guoYLong/article/details/103505582

版权

DB 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

1.以List<Map<String, Object>> list 查询结果集的写法

<select id="select" resultType="java.util.HashMap">
		SELECT * FROM 表名
</select>

<select id="select" parameterType="java.util.HashMap" resultType="Map">
	SELECT * FROM 表名
</select>

<select id="select" parameterType="long" resultType="Map">
    SELECT * FROM 表名
<select>

<select id="select" resultType="java.util.HashMap">
    SELECT * FROM 表名
<select>

2.以返回Double为查询结果

<select id="select" resultType="java.lang.Double">
		SELECT
		TRUNCATE(sum(amount)/100,2) as AMOUNT FROM 表名 WHERE `PROJECT_ID` = #{proId} 
</select>

3.以返回Long为查询结果

<select id="select" resultType="long">
		SELECT
		count(*)
		FROM  表名
</select>

4.以实体返回为查询结果

	<select id="select" resultMap="ResultMap">
		SELECT * from 表名
	</select>

5.新增写法

<insert id="insert" insert="com.实体"
		useGeneratedKeys="true" keyProperty="id" keyColumn="ID">
		INSERT INTO
		<include refid="table"></include>
		(
		`USER_ID`,`DISPLAY_NAME`,`USER_IMAGE`,`ORG_ID`,`PROJECT_ID`,`MODE`,`INVOICE_ID`,`AMOUNT`,`STATUS`,
		`GOODS_NAME`,`ORG_NAME`,
		`ALI_TRADE_NO`,`ALI_USER_ID`,`ALI_USER_NAME`,`ALI_CUSTOM_ID`,`BFB_CUSTOM_ID`,`BFB_TRADE_NO`,`ANONYMOUS`,`PAY_DATE`,`WEIXIN_TRADE_NO`,
		`CREATE_DATE`,`UPDATE_DATE`,`IS_RANDOM`,`PROJECT_MAIN_IMG`,`PROJECT_TARGET`,`SHARE_ORDER_ID`,`ORDER_TYPE`,`CLIENT_IP`,`DON_TOGETHER_ID`,`MONTH_DONATE_ID`
		)
		VALUES
		(
		#{userId},#{displayName},#{userImage},#{orgId},#{projectId},#{mode},#{invoiceId},#{amount},#{status},
		#{goodsName},#{orgName},
		#{aliTradeNo},#{aliUserId},#{aliUserName},#{aliCustomId},#{bfbCustomId},#{bfbTradeNo},#{anonymous},#{weixinTradeNo},#{payDate},
		#{createDate},
		#{updateDate},
		#{isRandom},
		#{projectMainImg},
		#{projectTarget},
		#{shareOrderId},
		#{orderType},
		#{clientIp},
		#{donateTogetherId},
		#{monthDonateId}
		)
	</insert>

6.修改写法

<update id="update" parameterType="com.实体">
		UPDATE
		<include refid="table"></include>
		SET
		`USER_ID` = #{userId},
		`DISPLAY_NAME` = #{displayName},
		`USER_IMAGE`
		= #{userImage},
		`ORG_ID` =#{orgId},
		`PROJECT_ID`
		=#{projectId},
		`MODE`
		=#{mode},
		`INVOICE_ID`=#{invoiceId},
		`AMOUNT`=#{amount},
		`STATUS`=#{status},
		`GOODS_NAME`=#{goodsName},
		`ORG_NAME`=#{orgName},
		`ALI_TRADE_NO`=#{aliTradeNo},
		`ALI_USER_ID`=#{aliUserId},
		`ALI_USER_NAME`=#{aliUserName},
		`ALI_CUSTOM_ID`=#{aliCustomId},
		`BFB_CUSTOM_ID`=#{bfbCustomId},
		`BFB_TRADE_NO` =#{bfbTradeNo},
		`ANONYMOUS`=#{anonymous},
		`PAY_DATE` =#{payDate},
		`WEIXIN_TRADE_NO`=#{weixinTradeNo},
		`CREATE_DATE`=#{createDate},
		`UPDATE_DATE`=#{updateDate},
		`IS_RANDOM`=#{isRandom},
		`PROJECT_MAIN_IMG`=#{projectMainImg},
		`PROJECT_TARGET`=#{projectTarget},
		`SHARE_ORDER_ID`=#{shareOrderId},
		`ORDER_TYPE`=#{orderType},
		`CLIENT_IP`=#{clientIp}
		WHERE
		`ID` = #{id};
	</update>

7.删除写法

	<delete id="delect" parameterType="java.lang.Long">
		delete from
		表名
		where id = #{id,jdbcType=BIGINT}
	</delete>

8.多条件排序

<select id="select" resultType="java.util.HashMap">
		SELECT
		d.id,
		d.company_name AS companyName,
		d.company_idea AS
		companyIdea,
		DATE_FORMAT(d.create_date, '%Y-%m-%d') AS entryDate,
		COUNT(c.pro_id) AS proNumber,
		IFNULL(c.donate_amount, 0) AS
		donateAmount,
		IFNULL(c.donate_material_amount, 0) AS
		donateMaterialAmount,
		d.update_date AS updateDate
		FROM
		gongyi_donation_company d
		LEFT JOIN gongyi_company_donate c ON d.id =
		c.donate_company_id
		WHERE
		1 = 1
		<if test="companyName != null and companyName != ''">
			AND
			d.company_name like CONCAT('%',#{companyName},'%')
		</if>
		AND (d.is_del = 0 OR c.is_del = 0)
		GROUP BY
		d.company_name
		ORDER BY
		<if test="sort == 1">
			entryDate ASC,
		</if>
		<if test="sort == 2">
			entryDate DESC,
		</if>
		<if test="sort == 3">
			proNumber ASC,
		</if>
		<if test="sort == 4">
			proNumber DESC,
		</if>
		<if test="sort == 5">
			c.donate_amount ASC,
		</if>
		<if test="sort == 6">
			c.donate_amount DESC,
		</if>
		<if test="sort == 7">
			c.donate_material_amount ASC,
		</if>
		<if test="sort == 8">
			c.donate_material_amount DESC,
		</if>
		<if test="sort == 7">
			d.update_date ASC,
		</if>
		<if test="sort == 8">
			d.update_date DESC,
		</if>
		d.update_date DESC
	</select>