Oracle转Mysql
过程记录
1. 迁移数据库
2. 改⽅⾔等配置⽂件
3. 改sql(to_chat: date_format, ||: CONCAT())
4. 改id
jdbc.sql.cj.jdbc.Driver
注：Mysql版本⾼加cj，不⾼去掉
jdbc.url=jdbc:mysql://10..165:3306/hc_bus
jdbc.username=
jdbc.password=***
预告：
Oracle数据库的序列（Seq）在Mysql中没有，是使⽤的⾃增值⼀般，得看主键⽣成策略
Oracel数据库中数据表转换到Mysql是不能将视图转过去，需要将使⽤到的视图⼿动添加到Mysql数据库中
Oracle⽅⾔
import org.apachemons.lang3.StringUtils;
public class OracleDialect implements Dialect {
public OracleDialect() {
}
public boolean supportsLimit() {
return true;
}
public String getLimitString(String oldSql, int skipResults, int maxResults) {
int endRownumber = skipResults + maxResults;
String tmpSql = StringUtils.lowerCase(StringUtils.deleteWhitespace(oldSql));
boolean sqlFlag = ains(tmpSql, "orderby") || ains(tmpSql, "groupby") || ains(tmpSql, "having") || ains(tmpSql, "union") || ains(tmpSql, "distinct") || StringUtils.c        if (!sqlFlag) {
if (ains(tmpSql, "where")) {
tmpSql = null;
return "select * from (select rownum as myrownum,c.* from (" + oldSql + " and rownum<=" + endRownumber + ") c) where myrownum>" + skipResults;
} else {
tmpSql = null;
return "select * from (select rownum as myrownum,c.* from (" + oldSql + " where rownum<=" + endRownumber + ") c) where myrownum>" + skipResults;
}
} else {
tmpSql = null;
新代系统子程序编程实例return "select * from (select rownum as myrownum,c.* from (" + oldSql + ") c)where myrownum<=" + endRownumber + " and myrownum>" + skipResults;
}
}
}
Mysql⽅⾔
import org.apachemons.lang3.StringUtils;
public class MySQLDialect implements Dialect {
public MySQLDialect() {
}
public boolean supportsLimit() {
return true;
}
public String getLimitString(String oldSql, int skipResults, int maxResults) {
int var10000 = skipResults + maxResults;
String tmpSql = StringUtils.lowerCase(StringUtils.deleteWhitespace(oldSql));
boolean sqlFlag = ains(tmpSql, "orderby") || ains(tmpSql, "groupby") || ains(tmpSql, "having") || ains(tmpSql, "union") || ains(tmpSql, "distinct") || StringUtils.c        if (!sqlFlag) {
return "(" + oldSql + ") LIMIT " + skipResults + "," + maxResults;
图片代码识别
} else {
tmpSql = null;
return "(" + oldSql + ") LIMIT " + skipResults + "," + maxResults;
}
}
}
PostgreSQL⽅⾔
spring注解项public class PostgreSQLDialect implements Dialect {
public PostgreSQLDialect() {
}
mysql语句转oraclepublic boolean supportsLimit() {
return true;
}
public String getLimitString(String sql, int skipResults, int maxResults) {
StringBuilder pageSql = (new StringBuilder()).append(sql);
if (skipResults <= 0) {
pageSql.append(" limit ").append(maxResults);
} else {
pageSql.append(" limit ").append(maxResults).append(" offset ").append(skipResults);
}
String();
}
}
项⽬：
Mysql驱动包
Maven配置，下载驱动包
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
数据源配置，四项缺⼀不可
jdbc.sql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://10.***.165:3306/***
jdbc.username=***
jdbc.password=***
Hibernate映射⽂件中属性generator的值设置
<hibernate-mapping>
<class name="dules.security.spring.domain.po.TaonlinelogPo" table="TAONLINELOG">
<id name="logid" type="java.lang.Long">//主键
<column name="LOGID" precision="10" scale="0"/>
<generator class="native" />//主键⽣成策略
</id>
<property name="clientsystem" type="java.lang.String">//普通字段
<column name="CLIENTSYSTEM" length="50" />
</property>
</class>
构造函数的八种方法公式</hibernate-mapping>
主键⽣成策略：
常⽤的三种：uuid、native、assigned。uuid是Hibernate⾃动⽣成的⼀个字符串，⼀个被编码为32位16进制数字的字符串，save()前⽣成；native，⾃增，save()后⽣成；
assigned，⾃⼰⼿动给主键赋值，save()前⽣成。
Oracle数据库的数据类型与Mysql转换后的默认值有所出⼊，需要调整
如：number类型和data类型会⾃动换为decimal，需要注意调整
视图转换
复制sql到可视化Mysql创建视图的界⾯，删掉第⼀⾏CREATE OR REPLACE VIEW AA10A1 AS，保存即可
>shell启动脚本

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。