1、分页
mysql:
select * from table limit m; --查询m行
select * from table limit m, n; --查询m+n行,撇去前面的m行,返回最后的n行
select * from table limit n offset m;
select * from table where id > 100 limit 0, 10; --第101~110行
oracle:
select * from (select T1.*, rownum rn from (select * from table) T1 where rownum <= 110) where rn > 100;
2、batchUpdateOrInsert
oracle:
<update id="batchUpdateOrInsert" parameterType="map">
merge into tabel T1
using (<foreach collection="inList" item="item" index="index" separator="union">
SELECT
#{item.XX1} XX1,
#{item.XX2} XX2,
FROM DUAL
</foreach>
) T2 on (T1.XX1 = T2.XX1)
when matched then
update set
T1.XX2 = T2.XX2,
T1.update_date = to_date(#{now}, 'yyyy-mm-dd hh24:mi:ss')
when not matched then
insert (ID, XX1, XX2, update_date)
values (SEQ_TABLE_ID.nextval, T2.XX1, T2.XX2, to_date(#{now}, 'yyyy-mm-dd hh24:mi:ss'))
</update>
by the way,oracle建表,需要对自增主键另外建序列
CREATE TABLE A
(
ID NUMBER(8,0) NOT NULL
, USER_MOBILE VARCHAR2(11) NOT NULL
, CREATE_DATE DATE NOT NULL
, CREATE_BY VARCHAR2(32) NOT NULL
, UPDATE_DATE DATE NOT NULL
, UPDATE_BY VARCHAR2(32) NOT NULL
, CONSTRAINT A_PK PRIMARY KEY
(
ID
)
ENABLE
);
COMMENT ON COLUMN A.ID IS '主键';
COMMENT ON COLUMN A.USER_MOBILE IS '会员账号';
--创建序列
create sequence SEQ_A_ID
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
nocache;
mysql:Todo
3、oracle:select 1 from dual;
mysql:select 1;
4、结合mybatis,插入后返回主键
oracle单条可以返回,批量多条不能返回。
<insert id="insertA" parameterType="com.XX.XXmodel">
<selectKey resultType="java.lang.Integer" keyProperty="id" order="BEFORE">
select SEQ_A_ID.nextval from dual
</selectKey>
insert into A
<trim prefix="(" suffix=")" suffixOverrides=",">
ID,
<if test="userId != null and userId !=''">
USER_ID,
</if>
<if test="changeType != null and changeType !=''">
CHANGE_TYPE,
</if>
<if test="updateDate != null">
UPDATE_DATE,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
#{id,jdbcType=DECIMAL},
<if test="userId != null and userId !=''">
#{userId,jdbcType=DECIMAL},
</if>
<if test="changeType != null and changeType !=''">
#{changeType,jdbcType=VARCHAR},
</if>
<if test="updateDate != null">
#{updateDate,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
插入后,XXmodel.getId()可以得到主键id。
<insert id="insertBatchA" parameterType="java.util.List">
<selectKey resultType="java.lang.Integer" keyProperty="id" order="BEFORE">
select SEQ_A_ID.nextval from dual
</selectKey>
insert into A
(ID, USER_ID, CHANGE_TYPE, UPDATE_DATE)
select SEQ_A_ID.nextval, A.* from
(
<foreach collection="list" item="item" index="index"
separator="union all">
select
#{item.userId,jdbcType=DECIMAL},
#{item.changeType,jdbcType=VARCHAR},
#{item.updateDate,jdbcType=TIMESTAMP}
FROM DUAL
</foreach>
) A
</insert>
oracle单条,批量多条都可以返回。Todo