mybatis中mysql和oracle的差异

注意：黑色为oracle配置，红色为mysql配置

1，jdbc配置

jdbc.driver=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@192.168.100.8:1521:name
jdbc.username=orcl
jdbc.password=orcl

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.100.9:3306/name?useUnicode=true&characterEncoding=utf8
jdbc.username=uname
jdbc.password=ps

2,pom.xml 配置

<dependency>                                                                                     
     <groupId>oracle</groupId>
     <artifactId>ojdbc14</artifactId>
     <version>10.2.0.4</version>
</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
</dependency>

3,applicationContext.xml配置

<property name="validationQuery" value="SELECT 'x' from dual" />

<property name="validationQuery" value="SELECT 'x' " />

另外需要注意几点sql的问题：

1，oracle 用SYSDATE     mysql用 now()

2，create表的时候 一定要注意字段和表名，如果都不加引号的话，默认都是大写（推荐，这样的话xml基本不用调整，改动量基本没有），加上引号的话，所有的sql语句都需要加上引号

  mysql和oracle语法有一定的差异，我们将服务由部署mysql的服务器迁移到部署oracle的服务器上时，需要修改sql语句。下面说说mybatis中由mysql转为oracle需要修改的语句。

1.批量插入

mysql：

1.  
    
2.  
   <insert id="insertBatch" parameterType="List">
3.  
   insert into black_list
4.  
   (uuid,type,value,delete_flag,gmt_create,gmt_modified) values
5.  
   <foreach collection="list" item="item" index="index"
6.  
   separator=",">
7.  
   (#{item.uuid},#{item.type},#{item.value}
8.  
   #{item.deleteFlag},#{item.gmtCreate},#{item.gmtModified})
9.  
   </foreach>
10.  
    </insert>
11.  
     
12.  
     

oracle：

1.  
    
2.  
   <insert id="insertBatch">
3.  
   <selectKey keyProperty="id" resultType="Long" order="BEFORE">
4.  
   select seq_black_list.nextval as id from dual
5.  
   </selectKey>
6.  
   insert into black_list
7.  
   (id,uuid,type,value,delete_flag,gmt_create,gmt_modified)
8.  
   select seq_black_list.nextval, A.* FROM (
9.  
   <foreach collection="list" item="item" index="index"
10.  
    separator="union all">
11.  
    select
12.  
    #{item.uuid,jdbcType=VARCHAR},
13.  
    #{item.type,jdbcType=VARCHAR},
14.  
    #{item.value,jdbcType=VARCHAR},
15.  
    #{item.deleteFlag,jdbcType=INTEGER},
16.  
    #{item.gmtCreate,jdbcType=DATE},
17.  
    #{item.gmtModified,jdbcType=DATE}
18.  
    from
19.  
    dual
20.  
    </foreach>
21.  
    ) A
22.  
    </insert>

其中，oracle在执行foreach之前，必须使用select语句。

2.批量更新

mysql：

1.  
    
2.  
   <update id="batchUpdate" parameterType="java.util.List">
3.  
    
4.  
   <foreach collection="list" item="item" index="index" open="" close="" separator=";">
5.  
   update test
6.  
   <set>
7.  
   test=${item.test}+1
8.  
   </set>
9.  
   where id = ${item.id}
10.  
    </foreach>
11.  
     
12.  
    </update>
13.  
     
14.  
     

oracle：

1.  
   <update id="batchUpdate" parameterType="java.util.List">
2.  
    
3.  
   <foreach collection="list" item="item" index="index" open="begin" close="end;" separator=";">
4.  
   update test
5.  
   <set>
6.  
   test=${item.test}+1
7.  
   </set>
8.  
   where id = ${item.id}
9.  
   </foreach>
10.  
     
11.  
    </update>

其中，oracle的foreach语句需要用begin和end包起来。

3.limit

mysql：

SELECT * FROM tablename LIMIT 100,15

oracle：

1.  
   select * from
2.  
   (select A.*,rownum rn from
3.  
   (
4.  
   原mysql的语句，注意去掉limit
5.  
   )A
6.  
   where rownum > 100
7.  
   )
8.  
   where rn <= 115

由于oracle中没有limit，因此可以通过rownum来实现相同的效果。

4.自增主键

mysql：

1.  
   <insert id="insert" parameterType="Role" useGeneratedKeys="true" keyProperty="roleId">
2.  
   insert into role (name, create_time, update_time) values (#{name,jdbcType=VARCHAR}, now(), now())
3.  
   </insert>

oracle：

在执行插入之前，需要创建一个序列：

1.  
   create sequence SEQ_T_USER_ID
2.  
   minvalue 1
3.  
   maxvalue 9999999999999999999999999999
4.  
   start with 1
5.  
   increment by 1
6.  
   cache 20;

序列生成后，就可以始终该序列实现主键自增的效果：

1.  
   <insert id="insert" parameterClass="ROLE">
2.  
   <selectKey resultType="java.lang.Long" order="AFTER" keyProperty="roleId">
3.  
   SELECT SEQ_T_USER_ID.currval as ROLEID from DUAL
4.  
    
5.  
   </selectKey>
6.  
   insert into ROLE(ID, NAME, CREATE, MODIFY) values (#{id}, #{name}, sysdate, sysdate)
7.  
   </insert>

mysql可以通过设置useGeneratedKeys为true来实现主键自增，oracle的语法不支持。为解决mysql和oracle的主键自增问题，可以用另外一张表TEMP记录其他表的主键，在插入时，由TEMP获取相应表的主键值，将其插入。

5.时间操作

mysql：

1.  
   返回系统日期,输出 2009-12-25 14:38:59
2.  
   select now();
3.  
   输出 09-12-25
4.  
   select date_format(now(),'%y-%m-%d');
5.  
    
6.  
   获得当前 UTC 日期时间函数：utc_date(), utc_time(), utc_timestamp()
7.  
    
8.  
   当前时间减去7分钟的时间 :select date_sub(now(),interval 7 minute);
9.  
    
10.  
    获取时间戳：select unix_timestamp('2008-08-08'); -- 1218124800

oracle：

1.  
   获取系统日期： SYSDATE()
2.  
   格式化日期： TO_CHAR(SYSDATE(),'YY/MM/DD HH24:MI:SS')
3.  
   返回系统日期,输出 25-12月-09
4.  
   select sysdate from dual;
5.  
   mi是分钟，输出 2009-12-25 14:23:31
6.  
   select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
7.  
   当前时间减去7分钟的时间
8.  
   select sysdate,sysdate - interval '7' MINUTE from dual;
9.  
   获取时间戳：SELECT (SYSDATE - TO_DATE('1970-1-1 8','YYY-MM-DD HH24'))*86400 FROM DUAL;
10.  
    用当前的时间减去1970年1月1日8时，得到的天数乘以24小时乘以3600秒，得到的结果就是系统时间戳。
11.  
    这里用8时的原因时系统所处时区为东8区
12.  
     
13.  
    含义解释：
14.  
    Dual伪列
15.  
    Dual 是 Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的select语句块中。
16.  
    不同系统可能返回日期的格式不一样。
17.  
    返回当前连接的用户：select user from dual;

     

     

    1.Oracle

    SELECT  *  FROM USER WHERE 1=1 AND NAME LIKE '%'||#{name}||'%'

    2.Mysql

    SELECT  *  FROM USER WHERE 1=1 AND NAME LIKE "%"#{name}"%"