Java中使用Springboot和Mybatis框架中,Mapper.xml对SQL语句的用法-Seo优化-塔城地区网站建设公司

1.查询数据时,将实体类变量与数据库中对应

<sql id="colum"> USER_NAME as userName, USER_AGE as userAge </sql> Select <include refid =”colum”/> from userinfo

2.将插入后的数据id映射到实体类上

<insert id = "insertUser"> <selectKey keyProperty=”id” resultType = “int”order = “BEFORE”> (select nvl(max(ID),0) + 1 as ID from user t) </selectKey > insert into user(name) values( #{name} ) </insert>

3.for循环添加

Mapper.Java中

int inserUser( @Param("userList") List<User> userList);

Mapper.XML中

<insert id="insertUser" parameterType="java.util.List"> insert all <foreach collection = "userList" index = "index" item = "item"> into user values( #{item.userId} ) </foreach> select * from dual </insert>

4. 模糊查询

Mapper.Java中

List<User> getUserInfo(@Param("name") String name);

Mapper.XML中

<select id="getUserInfo"> <bind name="userName" value="'%' + name + '%'"/> select * from user where name like #{userName} </select>

5.多嵌套查询

<resultMap id = “getUser” type = “对应的实体类”> <result column = “数据库的名称” property = “实体类的名称”/> <collection property="featIds" select="getFeatId" column="{type = type, userName = userName}"/> <resultMap/> <select id = “getUserInfo” resultMap = “getUser”/> <select id = “getFeatId” resultType = “对应的实体类”/>

6.大于号小于号

大于号>小于号<

7. Oracle对应的数据类型

jdbcType=NUMERIC(数字)
jdbcType=VARCHAR (字符串)

8.时间格式

to_date(#{date},'yyyy-MM-dd hh24:mi:ss')

9.Oracle中添加当前时间

Sysdate
可以在对应的字段下直接写
列如:
INSERT INTO USER(username, age, time)
VALUES (
"张三",
"18",
sysdate
)

10.Trim标签属性

prefix表示以xxx开头
prefixOverrides表示若标签中间内容以xxx开头，就删除
suffix表示以xxx结尾
suffixOverrides表示若标签中间内容以xxx结尾，就删除
列如:
UPDATE USERNAME
< trime prefix = "set" sufficOverrdes = ",">
<if test="state != null">
state = #{state} ,
</if>
<if test="age!= null">
age= #{age} ,
</if>
where id = #{id}
这句话就是代表这段代码以 "set" 开头 , 如果结尾是"逗号" 就删掉.

11.If-Else标签

choose (when,otherwise)
例如:
<choose>
<when test="params!=null"> //when 代表如果
right JOIN
</when>
<otherwise> // otherwise代表否则
LEFT JOIN
</otherwise>
</choose>

12.if标签中大于号小于号

大于gt
大于等于gte
小于lt
小于等于lte
例如:
<if text = "age gt '1' and age lt '3'"/>

13.in多参查询

Mapper.Java中

List<User> getUser(@Parm("userIds") List<Integer> userIds);

Mapper.XML中

select * from user
where userid in
<foreach collection="userIds" item="item" index="index" open="(" separator="," close=")">
#{item}
</foreach>

14.查询多个字符串类型

and regexp_like(t.userName, '张三|李四|王五')

15.字符的拼接

<!--两表查询拼接字段,截取“/”第八个之前的数据--> <select id="getSys001JiancetjInfo" resultType="com.qc.jy.pojo.Sys001JiancetjInfo"> SELECT t2.*, SUBSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),'/'),t2.jctp),'/'), 1, INSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),'/'),t2.jctp),'/'), '/', 1, 8) - 1) AS pathjctb, SUBSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),'/'),t2.JCBG),'/'), 1, INSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),'/'),t2.JCBG),'/'), '/', 1, 8) - 1) AS pathjcbg, SUBSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),'/'),t2.JCSP),'/'), 1, INSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),'/'),t2.JCSP),'/'), '/', 1, 8) - 1) AS pathjcsp FROM SYS_001_Wenjiantjinfo t1 JOIN SyS_001_JIANCETJINFO t2 ON t1.gcbm = t2.gcbm <where> <if test="featid !=null and featid!=''"> FEATID = #{featid} </if> </where> </select>

16.使用insert-select进行添加数据

<insert id="addPipingNetworkSketch"> insert into SYS_002_GUANWANGCAOTU(FROMID,TOID,GDTYPE,GDCZ,GDKJ,REMARK,FROMX,FROMY,TOX,TOY,UPLOADPERSON,ID,FEATID,STYLE,WIDTH,SSQZ,SFJS,LXFS,DWMC) SELECT #{fromId},#{toId},#{gdType},#{gdcz},#{gdkj},#{remark},#{fromX},#{fromY},#{toX},#{toY},#{uploadPerson}, MAX(ID) + 1 , #{featId} , #{style}, #{width}, #{ssqz}, #{sfjs}, #{lxfs}, #{dwmc} FROM SYS_002_GUANWANGCAOTU </insert>

17.foreach标签
遍历集合或者数组，一般都是用于批量修改或者批量添加

<foreach collection = "集合名或者数组名" item = "元素" separator = "标签分隔符" open = "以什么开始" close = "以什么结束"> #{元素} </foreach>

18.Oracle行专列

select * from (
select PUMPID,PID_NAME,to_number(CUR_VAL) CUR_VAL from jc_bz_data_time t
) t
pivot(max(CUR_VAL) for PID_NAME in('流量' as flow,'液位' as YW,'cod' as COD,'氨氮' as NH3,'ph' as PH,'总磷' AS TP))

转之前数据结构

select PUMPID,PID_NAME,to_number(CUR_VAL) CUR_VAL from jc_bz_data_time t

转之后数据结构

select * from (
select PUMPID,PID_NAME,to_number(CUR_VAL) CUR_VAL from jc_bz_data_time t
) t
pivot(max(CUR_VAL) for PID_NAME in('流量' as flow,'液位' as YW,'cod' as COD,'氨氮' as NH3,'ph' as PH,'总磷' AS TP))

19.针对数据库中的表进行自增序列的设置和触发器的创建

create sequence <SEQUENCE_NAME> start with 1000 increment by 1 nomaxvalue; create or replace trigger <TRIGGER_NAME> before insert on <TABLE_NAME> for each row when (new.<FIELD_NAME> is null) begin select <SEQUENCE_NAME>.nextval into:new.<FIELD_NAME> from dual; end;

1.create sequence <SEQUENCE_NAME> start with 1000 increment by 1 nomaxvalue;：创建一个名为<SEQUENCE_NAME>的序列，起始值为1000，每次递增1，没有最大值限制。
2.create or replace trigger <TRIGGER_NAME> before insert on <TABLE_NAME> for each row when (new.<FIELD_NAME> is null)：创建一个名为<TRIGGER_NAME>的触发器，它在每次向<TABLE_NAME>表中插入新行之前触发，且当新行的<FIELD_NAME>字段为空时才触发。
3.begin select <SEQUENCE_NAME>.nextval into:new.<FIELD_NAME> from dual; end;：定义了触发器的动作，即在插入新行时，从上面创建的序列<SEQUENCE_NAME>中获取下一个值，并将其赋给新行的<FIELD_NAME>字段。
总结：这段代码的作用是为指定的表创建一个自增序列，并创建一个触发器，使得在向表中插入新行时，如果某个字段为空，就会自动从序列中获取下一个值并填充到该字段中。这种方式通常用于为表的主键字段生成唯一的递增值。

20.保证分组后获取第一条数据

with cte_table2 as ( select t1.*, row_number() over(partition by t1.psyh order by t1.psyh) as rank from psh_info t1 where t1.psfq='JDGY00008S' and t1.pshdj IN ('一级排水户', '二级排水户') ) select * from cte_table2 where rank=1;

这个SQL查询使用了一个公共表表达式（CTE），它的作用是为了计算每个psyh分组中的记录，并对每个分组中的记录进行排名。具体来说，这个查询的意思如下：
首先，使用公共表表达式（CTE）cte_table2，从表psh_info中选择符合条件psfq='JDGY00008S'并且pshdj为'一级排水户'或'二级排水户'的记录。
在cte_table2中，利用ROW_NUMBER()窗口函数，对每个psyh分组内的记录按照psyh的顺序进行排名，并将排名结果存储在rank列中。
最后，从cte_table2中选择所有rank为1的记录，即每个psyh分组中排名第一的记录。
总的来说，这个SQL查询的目的是找出每个psyh分组中排名第一的记录，并将它们返回。

21，SQL 解析

子查询：
select t.*, row_number() over(partition by t.EQUIPID order by t.SAMPLE_TIME desc nulls last) rn from JC_YW_DATA_HIS t
这个部分是一个子查询，它从表JC_YW_DATA_HIS中选择所有列（t.*）以及一个额外的列rn。
row_number() over(partition by t.EQUIPID order by t.SAMPLE_TIME desc nulls last)是一个窗口函数，它为每个EQUIPID的记录生成一个行号（rn）。
partition by t.EQUIPID：根据EQUIPID列将结果集分区。每个EQUIPID的记录会单独分组。
order by t.SAMPLE_TIME desc nulls last：在每个分区内，记录根据SAMPLE_TIME列的值降序排列，nulls last表示将SAMPLE_TIME为NULL的记录排在最后。
结果是每个EQUIPID的记录都有了一个行号，最新的记录行号为 1，第二新的记录行号为 2，以此类推。
外层查询：
select * from (...) c where rn = 2
外层查询从子查询中选择所有列（*），并且只保留行号rn等于 2 的记录。
这意味着只选择每个EQUIPID的第二条最新记录。

总结：综上所述，这个 SQL 查询的作用是：

从JC_YW_DATA_HIS表中，按照EQUIPID分组，并为每个组内的记录按SAMPLE_TIME降序排列。
选择每个EQUIPID的第二条最新记录（即SAMPLE_TIME的第二大值）。

22.获取时间倒叙，且空值排在最后

order by DATA_TIME desc nulls last

23.查询时根据时间是否添加时分秒

1.不添加时分秒

<if test="startTime!=null and startTime!=''"> <bind name="startTime" value="startTime+' 00:00:00'"/> and CREATETIME >= to_date(#{startTime},'yyyy-mm-dd hh24:mi:ss') </if> <if test="endTime!=null and endTime!=''"> <bind name="endTime" value="endTime+' 23:59:59'"/> and CREATETIME &lt;= to_date(#{endTime},'yyyy-mm-dd hh24:mi:ss') </if>

2.添加时分秒

<if test="startTime!=null and startTime!=''"> <bind value="startTime" name="startTime"/><!--用bind标签加了+' 00:00:00'就不需要加时分秒--> and t1.sample_time >=to_date(#{startTime}, 'yyyy-mm-dd hh24:mi:ss') </if> <if test="endTime!=null and endTime!=''"> <bind name="endTime" value="endTime"/> and t1.sample_time &lt;=to_date(#{endTime},'yyyy-mm-dd hh24:mi:ss') </if>

24.Oracle只显示年月日

1.TO_CHAR(your_date_column, 'YYYY-MM-DD')
2.TRUNC(your_date_column)

25.SQL 代码利用了SUM和DECODE函数对数据进行了行转列（也称为“行专列”或“透视”操作）。具体来说，它将多行数据的某些特定值汇总到单个结果集中，以便更方便地查看和分析。

<select id="getSeWageFromData" resultType="com.qc.web.ks.monitor.pojo.SeWageFromData"> select snfwid, YLSBID, STATION_ID, SSPQID, wscid, SCYID, name wscname, scyname name, sjcll, sum(decode(zbname, '瞬时流速', DATA, 0))*3.6*TO_CHAR(SYSDATE, 'HH24') DATACount, sum(decode(zbname, '瞬时流速', DATA, 0)) DATA1, sum(decode(zbname, '化学需氧量', DATA, 0)) DATA3, sum(decode(zbname, '氨氮', DATA, 0)) DATA4, sum(decode(zbname, '总氮', DATA, 0)) DATA6, sum(decode(zbname, '总磷', DATA, 0)) DATA5, sum(decode(zbname, '氢离子浓度指数', DATA, 0)) DATA2, sum(decode(zbname, '化学需氧量', DATA, 0)) DATA8, sum(decode(zbname, '水温', DATA, 0)) DATA7, sum(decode(zbname, '累计流量', DATA, 0)) FLOWTOTAL, max(MONITORTIME) MONITORTIME, max(lat) lat, max(lon) lon from jc_wsc_data_view a <where> <if test="name!=null and name!=''"> <bind name="name" value="'%'+name+'%'"/> and a.name like #{name} </if> </where> group by wscid, SCYID, name,scyname,sjcll,STATION_ID, SSPQID,snfwid,YLSBID order by MONITORTIME desc </select>

<select id="getSeWageFromData" resultType="com.qc.web.ks.monitor.pojo.SeWageFromData"> select snfwid, YLSBID, STATION_ID, SSPQID, wscid, SCYID, name wscname, scyname name, sjcll, sum(decode(zbname, '瞬时流速', DATA, 0)) * 3.6 * TO_CHAR(SYSDATE, 'HH24') AS DATACount, sum(decode(zbname, '瞬时流速', DATA, 0)) AS DATA1, sum(decode(zbname, '化学需氧量', DATA, 0)) AS DATA3, sum(decode(zbname, '氨氮', DATA, 0)) AS DATA4, sum(decode(zbname, '总氮', DATA, 0)) AS DATA6, sum(decode(zbname, '总磷', DATA, 0)) AS DATA5, sum(decode(zbname, '氢离子浓度指数', DATA, 0)) AS DATA2, sum(decode(zbname, '化学需氧量', DATA, 0)) AS DATA8, sum(decode(zbname, '水温', DATA, 0)) AS DATA7, sum(CASE WHEN zbname IN ('累计流量', '流量') THEN DATA ELSE 0 END) AS FLOWTOTAL, max(MONITORTIME) AS MONITORTIME, max(lat) AS lat, max(lon) AS lon from jc_wsc_data_view a <where> <if test="name!=null and name!=''"> <bind name="name" value="'%'+name+'%'"/> and a.name like #{name} </if> </where> group by wscid, SCYID, name, scyname, sjcll, STATION_ID, SSPQID, snfwid, YLSBID order by MONITORTIME desc </select>

注意：这是查询实时数据的内容，可以这样进行行专列。
26.oracle中如果原表没有进行建立分区，是无法再去修改建立分区的，需要建立新的表，建立分区，把原表数据导入。
以下是在oracle建立分区表举例

CREATE TABLE jc_ll_data_hour2_fenqu ( llid VARCHAR2(32), ll NUMBER, recordTime DATE <!--CONSTRAINT pk_jc_ll_data_hour2 PRIMARY KEY (llid, recordTime)--> <!--CONSTRAINT pk_jc_ll_data_hour2 PRIMARY KEY (llid, recordTime) 是定义一个主键约束的 SQL 语句。它指定了 llid 和 recordTime 这两列的组合作为表的主键。主键约束确保： 唯一性：每一行的数据在这两列的组合下是唯一的，没有重复。 非空性：这两列的值不能为 NULL。--> PARTITION BY RANGE (recordTime) INTERVAL (INTERVAL '1' MONTH) -- 自动分区间隔设置为一个月 ( PARTITION p_start VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')) );

27.oracle中根据四个小时进行分组（可以求平均值）。

SELECT
TRUNC(data_time, 'HH24') - MOD(TO_NUMBER(TO_CHAR(data_time, 'HH24')), 4) * INTERVAL '1' HOUR AS four_hour_period
FROM
jc_bzSZ_data_hour
GROUP BY
TRUNC(data_time, 'HH24') - MOD(TO_NUMBER(TO_CHAR(data_time, 'HH24')), 4) * INTERVAL '1' HOUR
ORDER BY
four_hour_period;

28 .Oracle遇到锁怎么解决

--查锁
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid
order by t2.logon_time;
--解锁
alter system kill session '40667'
ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>';

29.如果一个字段样式 01,02,03 统计出列表出现各个次数

SELECT
SUM(count_1) AS total_count_1,
SUM(count_2) AS total_count_2,
SUM(count_3) AS total_count_3,
SUM(count_4) AS total_count_4
FROM (
SELECT
REGEXP_COUNT(qxjb, '1') AS count_1,
REGEXP_COUNT(qxjb, '2') AS count_2,
REGEXP_COUNT(qxjb, '3') AS count_3,
REGEXP_COUNT(qxjb, '4') AS count_4
FROM Sys_001_Xiufutjinfo
WHERE gcbm = 'GD-20240717141327564-31337'
)

注意：如果还要分组按不同的字段统计可以这样

SELECT
gcbm,
SUM( CASE WHEN TRIM(qxjb) = '1' THEN 1 ELSE LENGTH(TRIM(qxjb)) - LENGTH(REPLACE(TRIM(qxjb), '1', '')) END) AS count_of_1,
SUM(CASE WHEN TRIM(qxjb) = '2' THEN 1 ELSE LENGTH(TRIM(qxjb)) - LENGTH(REPLACE(TRIM(qxjb), '2', '')) END ) AS count_of_2,
SUM( CASE WHEN TRIM(qxjb) = '3' THEN 1 ELSE LENGTH(TRIM(qxjb)) - LENGTH(REPLACE(TRIM(qxjb), '3', '')) END) AS count_of_3,
SUM( CASE WHEN TRIM(qxjb) = '4' THEN 1 ELSE LENGTH(TRIM(qxjb)) - LENGTH(REPLACE(TRIM(qxjb), '4', '')) END ) AS count_of_4

FROM
sys_001_xiufutjinfo
GROUP BY
gcbm

30.当出现一对多可以把一个字段拼起来

SELECT
t1.sim_num,
GROUP_CONCAT(t2.name SEPARATOR ', ') AS name_list
FROM
inspection_user_carlink_rivers_lakes t1
LEFT JOIN
rivers_lakes t2 ON t1.river_id = t2.id
GROUP BY
t1.sim_num;

31.Mysql优化

32.Oracle数据库锁表问题

数据库被锁定：
查锁：
--查锁
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid
order by t2.logon_time;
--解锁
alter system kill session '40667'
ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>';

32.获取当前本周时间

-- 本周一00:00:00;本周日23:59:59
SELECT TRUNC(SYSDATE, 'IW') AS week_start, TRUNC(SYSDATE, 'IW') + 6 + INTERVAL '23:59:59' HOUR TO SECOND AS week_end FROM dual;

33.Oracle创建触发器id自增

CREATE OR REPLACE TRIGGER "MENUSID"
BEFORE INSERT ON "SYS_MENUS"
REFERENCING OLD AS "OLD" NEW AS "NEW"
FOR EACH ROW
BEGIN
SELECT COALESCE(MAX(id), 0) + 1 INTO :new.id FROM SYS_MENUS;
END;

34.原java：sql

// 先安全处理输入 String lon = (longitude == null || longitude.trim().isEmpty()) ? "" : longitude.trim(); String lat = (latitude == null || latitude.trim().isEmpty()) ? "" : latitude.trim(); String type = (siteType == null || siteType.trim().isEmpty()) ? "" : siteType.trim(); String sampLoc = (samplingLocation == null || samplingLocation.trim().isEmpty()) ? "" : samplingLocation.trim(); // 再拼接 SQL String sql = "UPDATE KS_SAMPLING_SITE SET " + "LON = COALESCE(NULLIF('" + lon + "', ''), LON), " + "LAT = COALESCE(NULLIF('" + lat + "', ''), LAT), " + "TYPE = COALESCE(NULLIF('" + type + "', ''), LAT), " + "SAMPLINGLOCATION = COALESCE(NULLIF('" + sampLoc + "', ''), SAMPLINGLOCATION) " + "WHERE NO = '" + siteID + "' AND PARENTID = '" + stationID + "'"; "LON = COALESCE(CAST(NULLIF('" + lon + "', '') AS NUMBER), LON), " + "LAT = COALESCE(CAST(NULLIF('" + lat + "', '') AS NUMBER), LAT), " +

35.Oracle行专列多个值

SELECT * FROM (SELECT pid, pipediameter, repairValue, reviewValue FROM WX_REPAIRPOINT WHERE delflag = 0 and afterRepairTime >=to_date(#{startTime}, 'yyyy-mm-dd hh24:mi:ss') and afterRepairTime &lt;=to_date(#{endTime}, 'yyyy-mm-dd hh24:mi:ss')) PIVOT (SUM(repairValue) AS repair_sum,SUM(reviewValue) AS review_sum FOR pipediameter IN ('DN600以上' AS sixs, 'DN600以下(含)' AS sixx))

36.Oracle自定义排序函数--DECODE

select * from (select PARENTAREAID,PARENTAREANAME,AREAID,AREANAME from sys_area_tree
where AREAID not in (select AREAID from sys_area_tree t where length(to_char(parentareaid)) = 3 )
and length(to_char(parentareaid)) = 2 and PARENTAREANAME!='中环'
union all
select PARENTAREAID,PARENTAREANAME,AREAID,AREANAME from sys_area_tree t
where length(to_char(parentareaid)) = 3
and areaid not in (select areaid from sys_area_tree t where parentareaid = 1029)
union all
select * from sys_area_tree t where parentareaid = 1029
union all
select * from sys_area_tree t where parentareaid = 10034)

order by DECODE(PARENTAREAID,
15,1,
16,2,
17,3,
18,4,
13,5,
14,6,
11,7,
21,8,
10,9,
20,10,
19,11,
12,12,
22,13,
23,14,
9999) ,AREAID

37.oracle自增函数-NEXTVAL函数

<insert id="addShjProblemFiles"> INSERT ALL <foreach collection="shjProblemFiles" item="item"> INTO SHJ_PROBLEM_FILES (ID, PROBLEM_ID, FILE_NAME, FILE_PATH, UPLOAD_TIME, DEL_FLAG) VALUES ( SEQ_SHJ_PROBLEM_FILES.NEXTVAL, #{item.problemId}, #{item.fileName}, #{item.filePath}, #{item.uploadTime}, #{item.delFlag} ) </foreach> SELECT 1 FROM DUAL </insert>