springBoot、mybatis mysql数据更换Oracle数据库。
1、将mysql数据更换成oracle数据格式。
(方法一:使用sqldeveloper工具进行转换(具体操作请自行百度。
方法二:使用Convert Mysql to Oracle。 转换后注释会消失。
方法二具体操作见https://blog.csdn.net/xb12369/article/details/40538729)
2、修改application-dev.yml里的配置文件 :
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/mysql?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
username: root
password: 123456
改成
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@127.0.0.1:1521:orcl
username: root
password: 123456
validationQuery: select ‘x’ from dual
3、修改application.yml文件下的以下文件后缀名。(如果直接在原xml文件上改sql则不需修改。)
mybatis:
configuration:
map-underscore-to-camel-case: true
mapper-locations: classpath*:mybatis/ ** /*MapperOracle.xml
typeAliasesPackage: com. ** .domain
mapperLocation: mybatis/**/*MapperOracle.xml
4、修改query.java(分页参数)便于mysql和oracle来回切换
其中
(String driverClassName= DataConfig.getDatabaseType(); driverClassName的值在) application-dev.yml里面根据
@ConfigurationProperties(prefix=“taiji”)
.
这个注解配置 。
配置在application-dev.yml里加上 (
databaseType: Oracle
) 。
public Query(Map<String, Object> params){
String driverClassName= DataConfig.getDatabaseType();
this.putAll(params);
// 分页参数
if(driverClassName!=null&&!"".equals(driverClassName)) {
if (driverClassName.equals(“mysql”)) {
this.offset = Integer.parseInt(params.get(“offset”).toString());
this.limit = Integer.parseInt(params.get(“limit”).toString());
this.put(“offset”, offset);
this.put(“page”, offset / limit + 1);
this.put(“limit”, limit);
} else if (driverClassName.equals(“oracle”)) {
this.offset = Integer.parseInt(params.get(“offset”).toString());
this.limit = Integer.parseInt(params.get(“limit”).toString());
this.put(“offset”, offset + 1);
this.put(“page”, offset / limit + 1);
this.put(“limit”, offset + limit);
}
}else{
this.limit = Integer.parseInt(params.get(“limit”).toString());
this.offset = Integer.parseInt(params.get(“offset”).toString());
this.put(“offset”, offset + 1);
this.put(“page”, offset / limit + 1);
this.put(“limit”, offset + limit);
}
}
5、修改sql分页查询 sql前面加上
select * from (select rownum rn, t.* from (
Sql最后将
order by updatetime desc,creattime desc或者
order by ${sort} ${order}
limit #{offset}, #{limit}
改成
order by updatetime desc,creattime desc )t)或者
order by ${sort} ${order} )t)
where rn between #{offset} and #{limit}
例如:
<select id=“list” resultType=“com.co.bu.domain.EvaluationDO”>
select * from (select rownum rn, t.* from (select b.*,sys_table.name as registername from
(SELECT a.name
FROM tablename e left JOIN tablename a on e.association = a.id where iscancel != ‘0’)
b LEFT JOIN tablename on b.register = tablename .value
<where>
sys_dict.type = ‘register’
<if test=“user != null and user!= ‘’”> and b.user= #{user}
</where>
<choose>
<when test=“sort != null and sort.trim() != ‘’”>
order by ${sort} ${order} )t)
</when>
<otherwise>
order by updatetime desc,creattime desc )t)
</otherwise>
</choose>
<if test=“offset != null and limit != null”>
where rn between #{offset} and #{limit}
</if>
</select>
6、将sql里的所以 ` 替换成空格
7、如果sql里的查询结果返回 的是map,添加标签,然后修改返回值。
例如:
<resultMap id=“listByMapResult” type=“java.util.Map”>
<!—该标签是为了将字段转成小写返回到页面,否则要将页面或者后台中的获取字段全改成大小。->
<result column=“NAME” javaType=“java.lang.String” property=“name”></result>
</resultMap>
<select id=“listMap” resultMap=“listByMapResult”>
select * from (select rownum rn, t.* from (SELECT a.name FROM tablename e left JOIN tablename a on e.association = a.id
LEFT JOIN (select id,name,value,type from tablename where type=‘mode’) b
ON e.operationMode=b.value
LEFT JOIN(select id,name,value,type from tablename where type=‘register’) c
ON e.register=c.value where e.iscancel != ‘0’ and e.apprstatus=‘1’
<if test=“id != null and id != ‘’”> and id = #{id} </if>
<choose>
<when test=“sort != null and sort.trim() != ‘’”>
order by ${sort} ${order} )t)
</when>
<otherwise>
order by id desc )t)
</otherwise>
</choose>
<if test=“offset != null and limit != null”>
where rn between #{offset} and #{limit}
</if>
</select>
8、修改sql中的日期函数
将 date_format 转换成to_char。(注意,如果查询返回结果是实体类,且实体类中的时间字段为date类型,则无法转成to_char)
例如:
修改前:
DATE_FORMAT(createDate,’%Y-%m-%d’)
修改后:
to_char(creattime, ‘yyyy-mm-dd’)
9、修改批量插入的sql
例如:
修改前:
<insert id=“batchSave” parameterType=“java.util.List”>
insert into creditfile( id
,updatetime
)
values
<foreach collection=“list” item=“creditfilePo” index= “index” separator ="," >
(
#{creditfilePo.id},
#{creditfilePo.updatetime}
)
</foreach>
</insert>
修改后:
<insert id=“batchSave” parameterType=“java.util.List”>
insert all
<foreach collection=“list” item=“creditfilePo” >
into creditfile(id,updatetime )
values( #{creditfilePo.id,jdbcType=VARCHAR},
#{creditfilePo.updatetime,jdbcType=DATE})
</foreach>
select * from dual
</insert>
10、修改所有插入sql字段的类型
类型转换见 https://www.cnblogs.com/yucongblog/p/7388648.html
例如:
修改前
<insert id=“save” parameterType=“com.bus.domain.CreditfileDO”>
insert into creditfile (id
, updatetime
)
values ( #{id}, #{updatetime} )
修改后:
<insert id=“save” parameterType=“com.bus.domain.CreditfileDO”>
insert into creditfile( id, updatetime )
values ( #{id,jdbcType=VARCHAR}, #{updatetime,jdbcType=DATE} )
</insert>
完成以上修改,方可启动应用。
注意:如果字段中的类型有用到BLOB
1)插入sql时字段(params)请改成
rawtohex(#{params,jdbcType=VARCHAR}),
2)查询sql请改成 UTL_RAW.CAST_TO_VARCHAR2(params) AS params