-
oracle取前5条数据:
SELECT * FROM (SELECT rri.id AS id, rri.title AS title, rf.file_size AS fileSize FROM RES_RESOURCE_INFO_NEW rri INNER JOIN RES_FILE_NEW rf ON rri.id = rf.id ORDER BY rri.created_time) WHERE ROWNUM < 6
-
xml处理大于,小于,等于的转义:
< 表示 <
> 表示 >
'${id}' ' 为转译字符,表示’单引号 -
遍历数组List:
INSERT INTO table1(id, name, description) SELECT t.* FROM ( <foreach collection="list" item="item" index="index" separator="union all"> SELECT SYS_GUID() AS id, #{item.name} AS name, #{item.description} AS description FROM DUAL </foreach>) t
-
模糊查询:
A: <if test="dataQuery != null and dataQuery != ''"> <bind name="defineName" value="'%' + dataQuery + '%'"></bind> AND (d.define_name LIKE #{defineName} OR d.table_name LIKE #{defineName} OR d.description LIKE # {defineName}) </if> (此处因为传递的条件一样,所以可以合并为一处进行,并可以以加上"()") B: <if test="code != null and code != ''"> <bind name="code" value="'%' + code + '%'"></bind> OR code LIKE #{code} </if> <if test="name != null and name != ''"> <bind name="name" value="'%' + name + '%'"></bind> OR NAME LIKE #{name} </if> C: ta.name LIKE '%'||#{apiName}||'%' 使用 ||
-
使用高级映射关系建立resultMap:
resultMap的column是调用SQL语句中命名后的别名(select a As b)b为别名,若未命名别名,则照常对应数据库字段,否则无法识别;
同理,附加的字段属性,可取出并命名后直接放入<result property="" column"">中赋值 -
动态替换表名或字段:
使用${}代替#{}
#{} 将传入的参数当成一个字符串,会给传入的参数加一个双引号
KaTeX parse error: Expected 'EOF', got '#' at position 29: …生成在sql中,不会添加引号 #̲{} 能够很大程度上防止sql…{}无法防止sql注入
${} 在预编译之前已经被变量替换了,这会存在sql注入的风险。如下sql -
case when 使用:
case when then when then end as 举例: CASE WHEN rri.is_system_recommend = 1 OR ttf.file_id IS NOT NULL THEN 1 WHEN rri.is_system_recommend is NULL OR rri.is_system_recommend = 0 OR ttf.file_id IS NULL THEN 0 else 0 END AS isGood
-
START WITH CONNECT BY PRIOR:
这个语法主要用于查询数据包中的树型结构关系。SELECT bc.ID FROM EDUBASE.BASE_CHAPTER bc START WITH bc.ID='402847f15a4aaa5b015a4b2824ba0002' CONNECT BY PRIOR bc.ID=bc.PARENT_ID
注意: 此用法在子查询中会出现找不到参数的问题,一般单独使用获取结果
-
查询数据库字段数:
select count(*) from user_tab_columns where table_name=upper('表名') select max(column_id) from user_tab_columns where table_name=upper('表名')
-
关联删除,不可连接查询,只可用子查询匹配删除:
<delete id="delAllo" parameterType="java.util.Map"> DELETE FROM ACTIVITY_ALLOTATION_NEW aa <where> 1 = 1 <if test="participantId != null"> AND aa.participant_id = #{participantId} </if> <if test="activityId != null"> AND aa.work_id IN ( SELECT id FROM ACTIVITY_WORK_NEW aw where aw.activity_id = #{activityId} ) </if> </where> </delete>
-
SQL连接问题:
内连接:inner join =>内连接:内连接查询操作列出与连接条件匹配的数据行,它使用比较运算符比较被连接列的列值。
左连接:left join =>左联接:是以左表为基准,将a.stuid = b.stuid的数据进行连接,然后将左表没有的对应项显示,右表的列为NULL
右连接:right join =>右连接:是以右表为基准,将a.stuid = b.stuid的数据进行连接,然以将右表没有的对应项显示,左表的列为NULL
全连接:full join =>全连接:完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
交叉连接:cross join =>交叉连接:交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。 -
orcale更新多条数据,多条字段:
<update id="updateJoinId" parameterType="java.util.List">
begin
<foreach collection="list" item="item" index="index" separator=";" >
UPDATE RES_RESOURCE_INFO_NEW
<set>
<if test="item.newId!=null and item.ID!=''">
id = #{item.newId},
</if>
</set>
where trim(id) = #{item.ID}
</foreach>
;end;
</update>
<update id="batchUpdate" parameterType="java.util.List" databaseId="Oracle">
<foreach collection="list" item="item" open="begin" close=";end;" separator=";">
UPDATE FORMAL_EXAM_SCORE
<trim prefix="SET" suffixOverrides=",">
USUAL_SCORE = #{item.usualScore},
FINAL_SCORE = #{item.finalScore},
PRACTICE_SCORE = #{item.practiceScore},
TOTAL_SCORE = #{item.totalScore},
IS_EXCEPTION = #{item.isException},
</trim>
<trim prefix="WHERE" prefixOverrides="AND">
<if test="null != item.id and '' != item.id">
AND ID = #{item.id}
</if>
</trim>
</foreach>
</update>
-
orcale插入多条数据,多条字段:
INSERT INTO ACTIVITY_LEVEL_NEW(ID,ACTIVITY_ID,LEVEL_NAME,UP_NUMBER,LOWN_NUMBER,LEVEL_DESC) <foreach collection="list" item="item" index="index" separator="union all"> SELECT #{item.id},#{item.activityId},#{item.levelName},#{item.upNumber},#{item.lownNumber},#{item.levelDesc} FROM dual </foreach>
-
oracle查询锁表情况:
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;