工作模板-----MySQL示例

sfy_csdn

于 2021-08-03 17:59:15 发布

阅读量211

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/sfy_csdn/article/details/119355993

版权

java 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

<?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.sheca.ent.auth.entauthservice.dao.BusLicenseMapper">
<select id="selectStatusById" parameterType="java.lang.String"
resultType="com.sheca.ent.auth.entauthservice.vo.QueryBusLicModel">
SELECT
t.status as "status",
t.bizid as "bizId",
t.ent_name as "entName",
t.credit_num as "creditNum",
t.remark as "remark"
from
tb_ent_business_verify t
WHERE
bizid = #{bizId}
</select>

<update id="updateBusLicInfo" parameterType="com.sheca.ent.auth.entauthservice.entity.BusLicenseEntity">
update tb_ent_business_verify
<set>
<if test="entName != null and entName != ''">ent_name=#{entName},</if>
<if test="creditNum != null and creditNum != ''">credit_num=#{creditNum},</if>
<if test="status != null">status=#{status},</if>
//时间不需要判空字符
<if test="updateTime != null">updateTime=#{updateTime},</if>
<if test="legalName != null and legalName != ''">legal_name=#{legalName},</if>
<if test="legalIdnum != null and legalIdnum != ''">legal_idnum=#{legalIdnum},</if>
<if test="remark != null and remark != ''">remark=#{remark},</if>
</set>
where qrcode_id = #{qrcodeId}
</update>

<insert id="save" parameterType="com.sheca.ent.auth.entauthservice.entity.BusLicenseEntity">
insert into tb_ent_business_verify
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="bizId != null">bizid,</if>
<if test="entName != null and entName != ''">ent_name,</if>
<if test="status != null">status,</if>
<if test="qrcodeId != null and qrcodeId != ''">qrcode_id,</if>
<if test="createTime != null">create_time,</if>
<if test="creditNum != null and creditNum != ''">credit_num,</if>

</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="bizId != null">#{bizId},</if>
<if test="entName != null and entName != ''">#{entName},</if>
<if test="status != null">#{status},</if>
<if test="qrcodeId != null and qrcodeId != ''">#{qrcodeId},</if>
<if test="createTime != null">#{createTime,jdbcType=TIMESTAMP},</if>
<if test="creditNum != null and creditNum != ''">#{creditNum},</if>
</trim>
</insert>

//执行顺序
<select id="selectBusByQrcode" parameterType="java.lang.String"
resultType="com.sheca.ent.auth.entauthservice.dto.BusLicDto">
SELECT
t.ent_name as "entName",
t.credit_num as "creditNum"
from
tb_ent_business_verify t
WHERE
qrcode_id = #{qrCodeid} and
like %2%
GROUP BY entName
having ent_name = #{entName}
ORDER BY
create_time DESC
LIMIT 1 5

</select>

   //模糊查询区间查询
<select id="selectVerifyInfo" parameterType="com.sheca.ent.auth.entauthservice.vo.QueryVerifyResultVO"
resultType="com.sheca.ent.auth.entauthservice.entity.ManualVerifyEntity">
SELECT
ent_name as "entName",
app_id as "appid",
app_name as "appName",
status as "status",
biz_id as "bizid",
create_time as "createTime"
from
tb_ent_manual_verify
<where>
<if test="status != null and status != ''">status=#{status}</if>
<if test="entName != null and entName != ''">
AND ent_name like CONCAT('%',#{entName},'%')
</if>
<if test="appName != null and appName != ''">
AND app_name like CONCAT('%',#{appName},'%')
</if>
<if test="creditNum != null and creditNum != ''">AND credit_num=#{creditNum}</if>
<if test="beginTime != null and endTime != null">AND create_time between #{beginTime} and #{endTime}</if>
</where>
</select>

   //多分支结构多个when只执行一个。如果输入了用户名则按照用户名模糊查找，
否则就按照住址查找，多个条件只能成立一个，
如果都不输入就查找用户名为“孙悟空”的用户。

<select id="queryUserByUsernameAndAddr" resultType="User">
select * from user where sex='男'
<choose>

<when test="username!=null and username.trim()!=''">
and user_name like concat('%',#{username},'%')
</when>
<when test="address!=null and address.trim()!=''">
and address =#{address}
</when>
<otherwise>
and user_name ='孙悟空'
</otherwise>
</choose>
</select>



   //循环 key=and device_no 采用拼接的方式形成sql语句
   <select id="listLicenseCode" parameterType="java.util.Map"
resultMap="LicenseCodeVO">
-- select
-- a.active_code,a.status,a.app_id,a.device_id,a.active_time,a.create_time,a.create_user,a.update_time,a.update_user,
-- d.device_id,d.device_name,d.version,d.mac_address,d.create_time,d.create_user
-- from tb_active_code a
-- left join tb_device_info d on a.device_id = d.device_id
<include refid="selectLicenseCodeVO"/>
<where>
<foreach collection="_parameter" item="tm" index="key">
<if test='tm != null and tm.toString().trim() != ""'>
${key} #{tm}
</if>
</foreach>
</where>
</select>

   //循环一个id匹配多个值
<delete id="batchDeleteLicenseCode" parameterType="java.lang.Long">
DELETE FROM tb_license_code WHERE id IN
<foreach collection="list" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</delete>

   <select id="selectVerifyInfo" resultType="com.sheca.ent.auth.entauthservice.entity.ManualVerifyEntity">
SELECT
ent_name as "entName",
app_id as "appid",
app_name as "appName",
status as "status",
biz_id as "bizid",
create_time as "createTime"
from
tb_ent_manual_verify
where id=#{Id} and article_id in (
   <foreach collection="collections" item = "item" separator = ",">
   #{item}
   </foreach>

   )
</select >

   //循环插入
   <insert id="selectVerifyInfo">
   insert into tb_ent_manual_verifyc (entry_id,article_id,create_time) values
   <foreach collection="collections" item = "item" separator = ",">
   (#{entryId},#{articleId},now())
   </foreach>

   //循环 key=and device_no 采用拼接的方式形成sql语句
   <resultMap id="DeviceInfoVO" type="DeviceInfo">
<id property="id" column="id"/>
<result property="deviceId" column="device_id"/>
<result property="deviceName" column="device_name"/>
<result property="version" column="version"/>
<result property="macAddress" column="mac_address"/>
<result property="serialNo" column="serial_no"/>
<result property="createTime" column="d.create_time"/>
<result property="createUser" column="d.create_user"/>
</resultMap>
   <sql id="selectLicenseCodeVO">
select a.id,
a.license_code,
a.status,
a.app_id,
a.device_id,
a.begin_time,
a.end_time,
a.valid_date,
a.create_time,
a.create_user,
a.batch
from tb_license_code a
</sql>
   <select id="listDeviceInfo" parameterType="java.util.Map"
resultMap="DeviceInfoVO">
<include refid="selectDeviceInfoVO"></include>
<where>
<foreach collection="_parameter" item="tm" index="key">
<if test='tm != null and tm.toString().trim() != ""'>
${key} #{tm}
</if>
</foreach>
</where>
</select>


   //一对多
   <select id="queryOneToMany" resultMap="oneToMany">
SELECT
tbo.id AS oid,
tbo.user_id,
tbo.order_number,
tbu.id AS uid,
tbu.user_name,
tbu. PASSWORD,
tbu. NAME,
tbu.age,
tbu.sex
FROM
tb_user AS tbu
INNER JOIN tb_order AS tbo ON tbu.id = tbo.user_id
WHERE
tbu.id = #{id}
</select>

   //多对多
   <select id="queryManyToMany" resultMap="manyToMany">
SELECT
tbo.id AS oid,
tbo.user_id,
tbo.order_number,
detail.id AS did,
detail.order_id,
detail.item_id,
detail.total_price,
detail. STATUS,
item.id AS iid,
item.item_name,
item.item_price,
item.item_detail
FROM
tb_order AS tbo
INNER JOIN tb_orderdetail AS detail ON tbo.id = detail.order_id
INNER JOIN tb_item AS item ON detail.item_id = item.id
WHERE
tbo.order_number = #{orderNumber}
</select>



   <select id="queryManyToMany2" resultMap="manyToMany2">
SELECT
tbo.id AS oid,
tbo.user_id,
tbo.order_number,
detail.id AS did,
detail.order_id,
detail.item_id,
detail.total_price,
detail. STATUS,
item.id AS iid,
item.item_name,
item.item_price,
item.item_detail,
tbu.id AS uid,
tbu.user_name,
tbu. PASSWORD,
tbu. NAME,
tbu.age,
tbu.sex
FROM
tb_order AS tbo
INNER JOIN tb_orderdetail AS detail ON tbo.id = detail.order_id
INNER JOIN tb_item AS item ON detail.item_id = item.id
INNER JOIN tb_user AS tbu ON tbo.user_id = tbu.id
WHERE
tbo.order_number = #{orderNumber}
</select>


   //分页前面穿过来什么字段根据什么字段查询字段涉及到四张表
   //难点多个join不能完全使用字段匹配不上多表查询实现不了多个区间查询不在一张表上
   //exists关键字判断子查询能否查到数据根据字段查到数据返回true 说明有数据反之false



   <select id="queryList" parameterType="com.sheca.order.orderservice.dto.req.OrderListVO"
resultType="com.sheca.order.orderservice.dto.resp.OrderListResp">
SELECT
t.order_id AS "orderNo",
t.create_time AS "createTime",
t.order_amount AS "amount",
t.order_status AS "orderStatus",
t.pay_time AS "payTime",
t.pay_status AS "payStatus",
t.invoice_status AS "invoiceStatus",
info.app_name as "appName"
FROM
tb_order t ,tb_app_info info where t.app_id=info.app_id

<if test="orderNo != null and orderNo != ''">AND t.order_id=#{orderNo}</if>
<if test="orderStatus != null and orderStatus != ''">AND t.order_status=#{orderStatus}</if>
<if test="payStatus != null and payStatus != ''">AND t.pay_status=#{payStatus}</if>
<if test="invoiceStatus != null and invoiceStatus != ''">AND t.invoice_status=#{invoiceStatus}</if>
<if test="beginPayTime != null and endPayTime != null">AND t.pay_time between #{beginPayTime} and #{endPayTime}</if>
<if test="maxAmount != null and minAmount != null">AND t.order_amount between #{minAmount} and #{maxAmount}</if>
<if test="beginCreateTime != null and endCreateTime != null">AND t.create_time between #{beginCreateTime} and #{endCreateTime}</if>
<if test="appName != null and appName != ''">AND info.app_name like CONCAT('%',#{appName},'%')</if>

<if test="(merchId != null and merchId != '') or (name != null and name != '') or ((endMinTime != null ) or (endMaxTime != null )) or (beginMinTime != null ) or (beginMaxTime != null )">
and exists (select * FROM tb_order_detail tod where t.order_id = tod.order_id
<if test="merchId != null and merchId != ''">AND tod.merch_id=#{merchId}</if>
<if test="name != null and name != ''">AND tod.name like CONCAT('%',#{name},'%')</if>
<if test="merchName != null and merchName != ''">AND tod.merch_name like CONCAT('%',#{merchName},'%')</if>

<if test="endMinTime != null">AND tod.end_time>=#{endMinTime}</if>
<if test="endMaxTime != null">AND tod.end_time<#{endMaxTime}</if>

<if test="beginMinTime != null">AND tod.begin_time>=#{beginMinTime}</if>
<if test="beginMaxTime != null">AND tod.begin_time<#{beginMaxTime}</if>

)
</if>
<if test="(payWay != null and payWay != '') or (transNo != null and transNo != '') or (thirdTransNo != null and thirdTransNo != '')">
and exists (select * FROM tb_order_pay top where t.order_id = top.order_id
<if test="payWay != null and payWay != ''">AND top.pay_way=#{payWay}</if>
<if test="transNo != null and transNo != ''">AND top.trans_id=#{transNo}</if>
<if test="thirdTransNo != null and thirdTransNo != ''">AND top.third_trans_id=#{thirdTransNo}</if>

)
</if>

</select>


   <select id="selectExportOrder" parameterType="com.sheca.order.orderservice.dto.req.OrderListVO"
resultType="com.sheca.order.orderservice.dto.resp.OrderExportResp">
SELECT
info.app_name as "appName",
d.order_id AS "orderNo",
t.create_time AS "createTime",
t.order_amount AS "amount",
t.order_status AS "orderStatus",
(select p.trans_id from tb_order_pay p where t.order_id=p.order_id and p.resp_code = '0' limit 1) AS "transNo",
(select p.pay_way from tb_order_pay p where t.order_id=p.order_id and p.resp_code = '0' limit 1) AS "payWay",
(select p.third_trans_id from tb_order_pay p where t.order_id=p.order_id and p.resp_code = '0' limit 1) AS "thirdTransNo",
t.pay_time AS "payTime",
t.pay_status AS "payStatus",
d.merch_name AS "merchName",
d.business_type AS "businessType",
d.price AS "price",
d.merch_sn AS "merchSn",
d.name AS "name",
d.card_no AS "cardNo",
d.begin_time AS "beginTime",
d.end_time AS "endTime",
t.invoice_status AS "invoiceStatus"

FROM
tb_order_detail d,
tb_order t,
tb_app_info info
where
t.app_id=info.app_id AND d.order_id=t.order_id
<if test="orderNo != null and orderNo != ''">AND d.order_id=#{orderNo}</if>
<if test="appName != null and appName != ''">AND info.app_name like concat('%',#{appName},'%')</if>
<if test="invoiceStatus != null and invoiceStatus != ''">AND invoice_status=#{invoiceStatus}</if>

<if test="beginPayTime != null">AND t.pay_time>=#{beginPayTime}</if>
<if test="endPayTime != null">AND t.pay_time<=#{endPayTime}</if>

<if test="beginCreateTime != null">AND t.create_time>=#{beginCreateTime}</if>
<if test="endCreateTime != null">AND t.create_time<=#{endCreateTime}</if>

<if test="minAmount != null and minAmount != ''">AND t.order_amount>=#{minAmount}</if>
<if test="maxAmount != null and maxAmount != ''">AND t.order_amount<=#{maxAmount}</if>

<if test="orderStatus != null and orderStatus != ''">AND t.order_status=#{orderStatus}</if>
<if test="payStatus != null and payStatus != ''">AND t.pay_status=#{payStatus}</if>

<if test="merchId != null and merchId != ''">AND d.merch_id=#{merchId}</if>
<if test="name != null and name != ''">AND d.name like CONCAT('%',#{name},'%')</if>
<if test="cardNo != null and cardNo != ''">AND d.card_no=#{cardNo}</if>

<if test="beginMinTime != null">AND d.begin_time>=#{beginMinTime}</if>
<if test="beginMaxTime != null">AND d.begin_time<=#{beginMaxTime}</if>

<if test="endMinTime != null">AND d.end_time>=#{endMinTime}</if>
<if test="endMaxTime != null">AND d.end_time<=#{endMaxTime}</if>

<if test="invoiceStatus != null and invoiceStatus != ''">AND invoice_status=#{invoiceStatus}</if>

<if test="(payWay != null and payWay != '') or (transNo != null and transNo != '') or (thirdTransNo != null and thirdTransNo != '')">
and exists (select * FROM tb_order_pay top where t.order_id = top.order_id and top.resp_code = '0'
<if test="payWay != null and payWay != ''">AND top.pay_way=#{payWay}</if>
<if test="transNo != null and transNo != ''">AND top.trans_id=#{transNo}</if>
<if test="thirdTransNo != null and thirdTransNo != ''">AND top.third_trans_id=#{thirdTransNo}</if>
)
</if>

</select>



</mapper>