mybatis oracle mysql的xml的区别

1.针对插入获取自增主键的方式oracle为下面例子
 

创建自增序列

create sequence seqid minvalue 1 maxvalue 9999999999999999 start with 1 increment by 1

select seqid.nextval from dual;

CREATE SEQUENCE sequ_id INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10; 

<insert id="saveUserMess">
        INSERT INTO 表名(EMPID,PERNR,PASSWORD) VALUES(sequ_id .nextval,#{username},#{password})
</insert>

<insert id="save" parameterType="com.zw.admin.server.model.SysLogs">
<selectKey resultType="long" keyProperty="id" order="BEFORE">
   select seqid.nextval from dual
</selectKey>
   insert into sys_logs (ID, USERID, MODULE,
   flag, REMARK, CREATETIME
   )
   values (#{id,jdbcType=DECIMAL}, #{user.id,jdbcType=VARCHAR}, #{module,jdbcType=VARCHAR},
   #{flag,jdbcType=DECIMAL}, #{remark,jdbcType=DECIMAL}, sysdate
   )

</insert>
MySQL中有自增项如下
@Options(useGeneratedKeys = true, keyProperty = "id")
@Insert("insert into sys_role(name, description, createTime, updateTime) values(#{name}, #{description}, now(),now())")

2.mysql的separator是逗号“,”，oracle的separator是“union all”；

3.mysql中支持concat ( '%',#{roleName},'%')。例如：

<select id="query" resultMap="resultMap">
select * from role  
<where>
<if test="roleName != null and roleName != ''">and ROLE_NAME likeconcat ( '%',#{roleName},'%') </if>
</where>
order by ROLE_ID desc
</select>

而oracle中却要这样写： '%'||#{roleName}||'%' 。例如：

<select id="query" resultMap="resultMap">
select * from role  
<where>
<if test="roleName != null and roleName != ''">and ROLE_NAME like '%'||#{roleName}||'%' </i
</where>
order by ROLE_ID desc
</select>

4.文字与格式字符串不匹配问题

#{dateTime}改为to_date(#{dateTime},'yyyy-mm-dd') 

5.分页区别

mysql使用的分页是limit m,n，而oracle则使用的是rownum

mysql语句：select dp.*,db.*  from d_category_product dcp join d_product dp on (dcp.product_id=dp.id) join d_book db on (dp.id=db.id) where dcp.cat_id=? limit ?, ?
oracle语句：select * from (select dp.*,db.*,rownum rn  from d_category_product dcp join d_product dp on (dcp.product_id=dp.id) join d_book db on (dp.id=db.id) where dcp.cat_id=?  and rn >?)  where rn<?

 

select * from ( select rownum as rn, z.* from ( select * from channel t where 1=1 and t.id = 1 order by t.id desc ) z where rownum<=10 ) where rn >= 1

 效率高：select * from (select rownum as rowno, t.* from emp t where hire_date between to_date ('20060501', 'yyyymmdd') and to_date ('20060731', 'yyyymmdd') and rownum <= 20) table_alias where table_alias.rowno >= 10;

格式：select * from (select a.*, rownum rn from (select * from table_name) a where rownum <= 40) where rn >= 21

<select id="findByPage" resultMap="BaseResultMap" parameterType="Map">
    select * from
     (select t1.*,rownum rn from
    (select * from tableName
    //可传入其他条件
    <where>
        <if test="words!=null">
             words like #{words,jdbcType=VARCHAR}
        </if>
    </where>
    ) t1 
    where rownum &lt;= #{currentPage,jdbcType=INTEGER}*#{pageSize,jdbcType=INTEGER}
    )
    where rn &gt; (#{currentPage,jdbcType=INTEGER}-1)*#{pageSize,jdbcType=INTEGER}
        
</select>
//调用传入当前页和每页条数即可，如需其他条件也可传入
Map<String, Object> map = new HashMap<String, Object>();
map.put("currentPage", currentPage);
map.put("pageSize", pageSize);
List<Object> list = xxxService.findByPage(map);

6.批量插入

MySQL：
<insert id="saveUserRoles">
   insert into sys_role_user(roleId, userId) values
   <foreach collection="roleIds" item="roleId" separator=",">
      (#{roleId}, #{userId})
   </foreach>
</insert>

Oracle：

<insert id="saveUserRoles" parameterType="java.util.List" useGeneratedKeys="false"> INSERT INTO sys_role_permission(roleId,userId) <foreach collection="roleUsers" item="roleUsers" index="index" separator="union all"> (select #{roleUsers.roleId,jdbcType=DECIMAL}, #{roleUsers.userId,jdbcType=DECIMAL} from dual) </foreach> </insert>