[mysql] mysql-myibatis-整理
生活随笔
收集整理的這篇文章主要介紹了
[mysql] mysql-myibatis-整理
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
==================================== insert ==========================================
語句1<insert id="insertEcsUsers" parameterType="com.goldenvista.kjt.entity.message.EcsUsers" useGeneratedKeys="true" keyProperty="userId">
insert into
ecshop_new.ecs_users
(
reg_time,
mobile_phone,
is_validated
)
values
(
unix_timestamp(NOW()) - 8*3600,
#{mobilePhone},
'0'
)
</insert>語句2<insert id="insertEcsOrderInfo" parameterType="com.goldenvista.kjt.entity.message.EcsOrderInfoM" useGeneratedKeys="true" keyProperty="orderId">
insert
into kjt.ecs_order_info
(
order_sn,
user_id,
password2
<if test="empId != null">
,emp_id
</if>
)
values
(
#{orderSn,jdbcType=VARCHAR},
#{userId,jdbcType=DECIMAL},
#{password2,jdbcType=VARCHAR}
<if test="empId != null">
,#{empId}
</if>
)
</insert>知識點：
1、Mybatis主鍵自動生成：
在mysql、sql server等支持主鍵自動增長的數據庫中,mybatis插入時，對自動生成的字段的寫法：<insert id=”insertAuthor” parameterType=”傳參類型，可以是bean/map” useGeneratedKeys=”true” keyProperty=“主鍵id”>
insert into
(uername, password, email )
vlaues
(#{username}, #{password}, #{email})
<insert>2、可以用控制語句控制是否插入某字段
<if test="empId != null">
,emp_id
</if>3、在插入時可以指定該字段的字段類型
#{orderSn,jdbcType=VARCHAR}4、參數類型一般是相應的bean5、mysql的日期函數FROM_UNIXTIME(dateSerial,partten)
UNIX_TIMESTAMP(date)
NOW()mysql> SELECT UNIX_TIMESTAMP() ; （獲得當前時間序列）
->1249524739mysql> SELECT FROM_UNIXTIME( 1249488000, '%Y年%m月%d' )
->2007年11月20mysql> SELECT UNIX_TIMESTAMP('2009-08-06') ;
->1249488000mysql> SELECT * FROM `student` WHERE regTime > UNIX_TIMESTAMP(curdate()) //今天所有學生注冊記錄。==================================== update ==========================================語句1<update id="updateEcsUsersOpenId" parameterType="java.util.Map">
update
ecshop_new.ecs_users u
set
u.kjt_open_id = #{openId}
where
(u.user_name = #{userName} or u.mobile_phone = #{userName})
and
u.password = #{password}
</update>語句2<update id="updateOrderStatus" parameterType="java.util.Map">
update
kjt.ecs_order_info og
set
og.order_status='6',
og.password2 = #{password},
og.store_receive_date =UNIX_TIMESTAMP()
WHERE
og.order_sn = #{orderSn}
</update>知識點：
1 或條件查詢連接一定要加上括號，否則查詢或更改就會出錯
where (u.user_name = #{userName} or u.mobile_phone = #{userName}) and u.password = #{password}
where u.user_name = #{userName} or u.mobile_phone = #{userName} and u.password = #{password}
這兩個語句的查詢結果是不一樣的2 參數類型一般是 java.util.Map
parameterType="java.util.Map"==================================== select ==========================================語句1：<select id="querySaleEmployee" resultType="com.goldenvista.kjt.entity.message.EcsUsers">
select
e.emp_id as empId,
e.emp_name as empName,
e.sheng_code as empShengCode,
e.shi_code as empShiCode,
e.xian_code as empXianCode,
(select r1.region_name from kjt.ecs_region r1 where r1.region_id = e.sheng_code) as empShengName,
(select r2.region_name from kjt.ecs_region r2 where r2.region_id = e.shi_code) as empShiName,
(select r3.region_name from kjt.ecs_region r3 where r3.region_id = e.xian_code) as empXianName
from kjt.sale_employee_info e
where 1 = 1
<if test="empId != null">
and e.emp_id = #{empId}
</if>
</select>1 兩表聯查（最外層單表）
(select r1.region_name from kjt.ecs_region r1 where r1.region_id = e.sheng_code) as empShengName
通過 r1.region_id = e.sheng_code 從 ecs_region 表獲取省名稱2 resultType
查到的數據的類型
as empXianName 的 empXianName 必須和 resultType 的數據類型的字段名一致語句2：<select id="queryEcsUsersInfo" resultType="com.goldenvista.kjt.entity.message.EcsUsers">
select
u.user_id as userId,
ifnull(e.emp_id,'') as empId,
ifnull(e.sheng_code,'') as empShengCode,
ifnull((select r1.region_name from kjt.ecs_region r1 where r1.region_id = e.sheng_code),'') as empShengName
from
ecshop_new.ecs_users u
left join
kjt.emp_user_info eu on u.user_id = eu.user_id
left join
kjt.sale_employee_info e on eu.emp_id = e.emp_id
where
u.is_validated = '0'
<if test="openId != null">
and (u.kjt_open_id = #{openId} or u.user_id = #{openId})
</if>
<if test="userName != null">
and (u.user_name = #{userName} or u.mobile_phone = #{userName})
</if>
<if test="password != null">
and u.password = #{password}
</if>
</select>1 外層多表聯查LEFT JOIN 內層兩表聯查
實例：當查詢某銷售本月的銷售額的時候，如果不使用left join 連接，那么查詢結果不包含銷售額為0/null的銷售代表的記錄
但如果使用left join 則將銷售代表個人信息表放在最前面，會得到全部銷售的銷售額，為0/null的就顯示0/null語句3<select id="queryOrderInfoByOrderSn" parameterType="java.util.Map" resultType="com.goldenvista.kjt.entity.message.EcsOrderInfo">
select
o.order_id as orderId,
CASE
WHEN o.pay_status = 2 THEN (o.money_paid + o.surplus + o.cash_money + o.post_money)
ELSE o.order_amount
END as orderAmount,
o.surplus as surplus,
from_unixtime(o.add_time) as addTime,
o.password2 as password2
from
kjt.ecs_order_info o
where
o.order_sn = #{orderSn}
</select>知識點：① SQL Select語句完整的執行順序： 1、from子句組裝來自不同數據源的數據；
2、where子句基于指定的條件對記錄行進行篩選；
3、group by子句將數據劃分為多個分組；
4、使用聚集函數進行計算；
5、使用having子句篩選分組；
6、計算所有的表達式；
7、使用order by對結果集進行排序。舉例：1.select 列列表 from 表列表名/視圖列表名 where 條件.
2.select 列列表 from 表列表名/視圖列表名 where 條件 group by (列列表) having 條件 3.select 列列表 from 表列表名/視圖列表名 where 條件 group by (列列表) having 條件 order by 列列表 4.select 列列表 from 表1 join 表2 on 表1.列1=表2.列1...join 表n on 表n.列1=表(n-1).列1 where 表1.條件 and 表2.條件...表n. 執行順序： 分析：
1. 先where 后select（先選出符合where子句的元組，再在元組中抽取指定的列組成二維表）
2. 先where 再group 再having 后select
3. 先where 再group 再having 再select 后order
4. 先join 再where 后select綜上執行順序為：
join - where - group by - having - select - order by② 邏輯處理（多選擇分支語句）
CASE
WHEN o.pay_status = 1 THEN (o.money_paid + o.surplus)
WHEN o.pay_status = 2 THEN (o.money_paid + o.surplus + o.cash_money + o.post_money)
ELSE o.order_amount
END as orderAmount對最終篩選出來的元組進行最后的邏輯處理，因此是在select里進行處理的③ 時間函數
from_unixtime(o.add_time) as addTime語句4<select id="queryEcsGoods" parameterType="java.math.BigDecimal" resultType="com.goldenvista.kjt.entity.message.EcsGoodsInfo">
select
goods_id as goodsId,
goods_number as goodsNumber
from kjt.ecs_goods
where goods_id = #{goodsId}
</select>根據主鍵查詢 參數類型為 BigDecimal
parameterType="java.math.BigDecimal"語句5<!-- 查詢用戶今天消費的記錄 -->
<select id="queryUserConsumeDailyByCardId" parameterType="java.lang.String" resultType="com.goldenvista.kjt.entity.mapping.EcsUserConsumeDaily">
select
c.id as id,
c.identify_no as identifyNo,
c.real_name as realName,
c.consume_money as consumeMoney,
c.tax as tax,
c.consume_date as consumeDate
from
ecshop_new.ecs_user_consume_daily c
where
c.identify_no = #{cardIdNo}
and
c.consume_date = curdate()
</select>參數類型：parameterType="java.lang.String"
“今天”：c.consume_date = curdate()語句6<!-- 根據userId查詢用戶最后一個訂單所屬貨柜 -->
<select id="queryCabinetByUserId" resultType="com.goldenvista.kjt.entity.message.EcsOrderInfoM">
select
t1.order_id as orderId,
t1.order_sn as orderSn,
t1.station_no as stationNo,
t1.station_name as stationName,
t1.cabinet_no1 as cabinetNo1,
t1.box_no as boxNo
from
kjt.ecs_order_info t1, ecshop_new.ecs_users t2
where
t1.user_id = t2.user_id
and
t1.user_id = #{userId}
and
t1.shipping_id = '9'
and
t1.cabinet_no1 is not null
order by
t1.add_time desc
</select>注意：null 和 "" 和 0 是不一樣的，is not null 用于查詢不為 null 的元組語句7<!-- 按銷量從高到低查詢商品 -->
<select id="queryGoodsByNumInfo" parameterType="java.util.Map" resultType="com.goldenvista.kjt.entity.message.KjtGoodsInfoModel" >
SELECT
g.GOODS_ID as goodsID,
g.goods_sn as goodsSn,
g.tax as taxPrice,
g.MEASURE_UNIT as goodsUnit,
g.IS_WEIGHT as isWeight,
g.shop_price as goodsPrice,
g.GOODS_NAME as goodsName,
g.GOODS_IMG as goodsImg,
g.CAT_ID as catId,
g.STORE_ID as storeId,
SUM(o.GOODS_NUMBER) as goodsSum
FROM
kjt.ecs_order_goods o,kjt.ecs_goods g
WHERE
o.GOODS_ID = g.GOODS_ID
GROUP BY
g.GOODS_ID
ORDER BY
goodsSum DESC
</select>sum() 聚集函數常用于統計金額和數量 有聚集函數必定有 group by 子句 且group by 的字段最好使用不常改變的id類字段SUM(o.GOODS_NUMBER) as goodsSum
GROUP BY g.GOODS_ID語句8 <select id="queryEmployeeList" parameterType="java.util.Map" resultType="com.goldenvista.kjt.entity.message.SaleEmployeeInfo">
SELECT
s.emp_id as empId,
s.emp_name as empName
FROM
kjt.sale_employee_info s
WHERE
s.status = '1'
<if test="empName != null">
and (s.mobile like CONCAT('%',#{empName},'%') or s.emp_name like CONCAT('%',#{empName},'%'))
</if>
order by s.emp_id desc
</select>模糊查詢
s.mobile like CONCAT('%',#{empName},'%')語句9 <!-- 統計銷售每月發展的會員數 -->
<select id="queryPersonNumMonth" parameterType="java.math.BigDecimal" resultType="java.math.BigDecimal">
select
count(*)
from
ecshop_new.ecs_users u, kjt.emp_user_info e
where
u.user_id = e.user_id
and
e.emp_id = #{empId}
and
year(from_unixtime(u.reg_time + 3600*8)) = year(now())
and
month(from_unixtime(u.reg_time + 3600*8)) = month(now())
</select>日期函數
year()
month()語句10<!-- 統計銷售每月的銷售額 -->
<select id="querySaleMoneyMonth" parameterType="java.math.BigDecimal" resultType="java.math.BigDecimal">
select
sum(o.money_paid + o.surplus + o.cash_money + o.post_money)
from kjt.ecs_order_info o
where o.emp_id = #{empId}
and year(from_unixtime(o.add_time)) = year(now())
and month(from_unixtime(o.add_time)) = month(now())
and o.pay_status = 2
</select>語句11<!-- 統計某段時間內銷售代表發展的會員數 -->
<select id="queryPersonNumByDate" parameterType="java.util.Map" resultType="java.math.BigDecimal">
select
count(*)
from ecshop_new.ecs_users u, kjt.emp_user_info e
where u.user_id = e.user_id
and e.emp_id = #{empId}
<if test="startDate != null">
and from_unixtime(u.reg_time + 3600*8) >= #{startDate}
</if>
<if test="endDate != null">
<![CDATA[
and from_unixtime(u.reg_time + 3600*8) <= date_add(#{endDate}, interval 1 day)
]]>
</if>
</select>1 時間段查詢2 日期加減計算
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)date 是一個 DATETIME 或DATE值，用來指定起始時間。
expr 是一個表達式，用來指定從起始日期添加或減去的時間間隔值。
type 為關鍵詞，它指示了表達式被解釋的方式。===============================================================
mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
-> '1998-01-01 00:00:00'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL 1 SECOND);
-> '1998-01-01 00:00:00'
===============================================================
mysql> SELECT INTERVAL 1 DAY + '1997-12-31';
-> '1998-01-01'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',INTERVAL 1 DAY);
-> '1998-01-01 23:59:59'
===============================================================
mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND;
-> '1997-12-31 23:59:59'
===============================================================語句 12 <!-- 分頁查詢訂單列表 -->
<select id="queryKjtOrderList" resultType="com.goldenvista.kjt.entity.mapping.KjtStoreOrderInfo">
SELECT
ROW_.*
FROM (
select
t1.order_id as orderId,
t1.order_sn as orderSn,
CASE
WHEN t1.pay_status = 2
THEN (t1.money_paid + t1.surplus + t1.cash_money + t1.post_money)
ELSE t1.order_amount
END as orderTaxPrice,
t1.goods_amount as orderPrice
from
kjt.ecs_order_info t1, ecshop_new.ecs_users t2
where
t1.user_id = t2.user_id
<if test="userId != null">
and t1.user_id = #{userId}
</if>
order by t1.add_time desc
<![CDATA[
) ROW_
limit #{startRecord},#{pageSize}
]]>
</select>分頁使用 limit 起始索引 每頁條數語句13 多表聯合查詢select
max(s.emp_id) as empId,
max(s.emp_name) as empName,
ifnull(max(g.goods_name),'-') as goodsName,
ifnull(SUM(g.goods_number*g.goods_price),'0') as totalMoney
from
kjt.sale_employee_info s
LEFT JOIN
kjt.ecs_order_info o on s.emp_id = o.emp_id
LEFT JOIN
kjt.ecs_order_goods g on o.order_id = g.order_id
where 1=1
group by
g.goods_id,s.emp_id
order by
SUM(g.goods_number*g.goods_price) DESC============================ 拓展知識點 =========================① Mybatis主鍵自動生成： 在MYSQL、sql server等支持主鍵自動增長的數據庫中！mybatis插入時，對自動生成的字段的寫法：
<insert id=”insertAuthor” parameterType=”類型” useGeneratedKeys=”true” keyProperty=“自動生成的字段名”>
insert into
數據表名(uername, password, email )
vlaues
(#{username}, #{password}, #{email})
<insert> 對不支持自動生成功能的數據庫，mybatis提供以下寫法，不過，此寫法生成的ID是隨機的
<insert id=”insertAuthor” parameterType=“類型”>
<selectKey resultType="java.lang.Long" order="AFTER" keyProperty="id"> SELECT LAST_INSERT_ID() AS id </selectKey>
insert into 數據表名（id, username, password, email ）
values
(#{id}，#{username}，#{password}，#{email})
</insert> ② Mybaits-SQL語句包含：
<sql id=”Columns”> select/update/delete/insert 等操作</sql>
<select id=”selectUser” parameterType=”int” resultType=”hashMap”>
select
<include refid=”Columns”/>
from
表名
where
id=#{id}
</select>③ Mybatis動態SQL語句 a、if語句
select 字段名
from 表名
where state=”1”
<if test =”字段名!=null”>
AND 條件
</if>b、choose, when , otherwise select 字段名 from 表名
where state=”1”
<choose> <when test=”字段名!=null”>
And 條件
</when> <when test=”條件表達式”>
And 條件
</when>
<otherwise >
And 條件
</otherwise>
</choose> c、trim, where , set 1, where
Select 字段名 from 表名
<where>
條件
</where>
注 : 加 <where> 后則確保一定是 where 開頭2, set
Update 表名
<set> <if test=”條件”>
字段名=#{參數}
</if>
</set> Where 條件 d、foreach 通常構建在in條件中
Select 字段名 from 表名
Where 字段名 in
<foreach item=”參數名” index=”index” collection=”list” Open=”(” separator=”,” close=”)” >
#{參數名}
</foreach> e、作用例：批量刪除 <delete id = "delete" parameterType = "java.util.List"> <![CDATA[ delete from tests where id in
]]> <foreach collection="list" item = "要刪除的id" open="(" separator="," close=")">
#{要刪除的id} </foreach>
</delete> f、模糊查詢：
select 字段名 from 表名 where 字段名 like "%" #{參數} "%" ④ sql元素Sql元素用來定義一個可以復用的SQL 語句段，供其它語句調用。<!-- 復用sql語句 查詢student表所有字段 -->
<sql id="selectStudentAll">
SELECT ST.STUDENT_ID,
ST.STUDENT_NAME,
ST.STUDENT_SEX,
ST.STUDENT_BIRTHDAY,
ST.CLASS_ID
FROM STUDENT_TBL ST
</sql>這樣，在select的語句中就可以直接引用使用了<!-- 查詢學生，根據id -->
<select id="getStudent" parameterType="String" resultMap="studentResultMap">
<include refid="selectStudentAll"/>
WHERE ST.STUDENT_ID = #{studentID}
</select> ⑤ parameters
MyBatis可以使用的基本數據類型和Java的復雜數據類型。
基本數據類型包括String，int，date等。但是使用基本數據類型，只能提供一個參數，所以需要使用Java實體類或Map類型做參數類型。
通過#{}可以直接得到其屬性。
?
總結
以上是生活随笔為你收集整理的[mysql] mysql-myibatis-整理的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 网页布局的一点感触
- 下一篇： AWK 学习手札之一: an AWK t