Oracle向MySQL迁移表结构和数据
实现思路
简单的实现了读取Oracle的表字段的字段名、类型、长度和小数位。根据Oracle的字段类型转换成MySQL的字段类型(对应在代码里面可以看到)并生成建表语句。代码比较原始,可以自行进行封装修改。
代码实现
ColumnData.java
package tm.entity;
/**
* Oracle字段信息实体类
* @author Cetrin Wang
*
*/
public class ColumnData {
private String columnName;
private String columnType;
private int columnSize;
private int colScale;
public ColumnData(){
}
public ColumnData(String columnName,String columnType,int columnSize){
this.columnName = columnName;
this.columnType = columnType;
this.columnSize = columnSize;
}
public ColumnData(String columnName,String columnType,int columnSize,int colScale){
this.columnName = columnName;
this.columnType = columnType;
this.columnSize = columnSize;
this.colScale = colScale;
}
public int getColScale() {
return colScale;
}
public void setColScale(int colScale) {
this.colScale = colScale;
}
public String getColumnName() {
return columnName;
}
public void setColumnName(String columnName) {
this.columnName = columnName;
}
public String getColumnType() {
return columnType;
}
public void setColumnType(String columnType) {
this.columnType = columnType;
}
public int getColumnSize() {
return columnSize;
}
public void setColumnSize(int columnSize) {
this.columnSize = columnSize;
}
@Override
public String toString(){
return "列名:"+getColumnName()+",字段类型:"+getColumnType()+",长度:"+getColumnSize()+",小数位:"+getColScale();
}
}
CopyTypeToMySQL.java
package tm.utility;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import tm.entity.ColumnData;
import tm.pretreatment.MySQLHelper;
import tm.pretreatment.ODaos;
/**
* 修改MySQL的blob字段为longtext字段
*
* @author Cetrin Wang
*
*/
public class CopyTypeToMySQL {
public static void main(String args[]) {
createTableMS("BD_ANALY_PA_INS_TOTAL");
}
/**
* 将oracle的表复制到MySQL中
*/
public static void createTableMS(String tableName) {
Connection conn = MySQLHelper.getConntion();
PreparedStatement ps = null;
List<ColumnData> list = getColList(tableName);
StringBuffer sb = new StringBuffer();
sb.append("CREATE TABLE ").append(tableName.toUpperCase()).append("(");
for (int i = 0; i < list.size(); i++) {
ColumnData col = list.get(i);
String type = getMySQLType(col);
if("ID".equalsIgnoreCase(col.getColumnName())){
sb.append(col.getColumnName()+" ").append(type+" not null PRIMARY KEY AUTO_INCREMENT,");
}else{
sb.append(col.getColumnName()+" ").append(type+",");
}
}
sb = sb.deleteCharAt(sb.length() - 1);
sb.append(") engine=innodb default charset=utf8 auto_increment=1 ");
System.out.println(sb.toString());
try {
ps = conn.prepareStatement(sb.toString());
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally{
MySQLHelper.closeConnection(ps, null);
}
}
/**
* 把Oracle的Type转成Oracle的Type
*/
private static String getMySQLType(ColumnData c){
if(c == null){
return null;
}
String type = c.getColumnType();
if("BLOB".equalsIgnoreCase(type)){
return "longtext";
}else if("DATE".equalsIgnoreCase(type)){
return "datetime";
}else if("VARCHAR2".equalsIgnoreCase(type)){
if(c.getColumnSize()>=2000){
return "text";
}else{
return "varchar("+c.getColumnSize()+")";
}
}else if("NUMBER".equalsIgnoreCase(type)){
if(c.getColScale() > 0 ){
int n = c.getColumnSize() - c.getColScale();
int s = c.getColScale();
return "decimal("+n+","+s+")";
}else{
return "int";
}
}
return c.getColumnType();
}
/**
* 获取oracle列信息
*
* @param tableName
* @return
*/
public static List<ColumnData> getColList(String tableName) {
Connection conn = ODaos.getOracleConnection();
PreparedStatement ps = null;
ResultSet res = null;
ArrayList<ColumnData> list = new ArrayList<ColumnData>();
String sql = "SELECT * FROM " + tableName.toUpperCase()
+ " WHERE ROWNUM = 1";
try {
ps = conn.prepareStatement(sql);
res = ps.executeQuery();
ResultSetMetaData metaData = res.getMetaData();
for (int i = 1, size = metaData.getColumnCount(); i <= size; i++) {
ColumnData cd = new ColumnData(metaData.getColumnName(i),
metaData.getColumnTypeName(i),
metaData.getColumnDisplaySize(i),metaData.getScale(i));
list.add(cd);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
try {
if (res != null) {
res.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取oracle指定列信息
*
* @param tableName
* @return
*/
public static List<ColumnData> getColList(String tableName,String colName) {
Connection conn = ODaos.getOracleConnection();
PreparedStatement ps = null;
ResultSet res = null;
ArrayList<ColumnData> list = new ArrayList<ColumnData>();
String sql = "SELECT "+colName+" FROM " + tableName.toUpperCase()
+ " WHERE ROWNUM = 1";
try {
ps = conn.prepareStatement(sql);
res = ps.executeQuery();
ResultSetMetaData metaData = res.getMetaData();
for (int i = 1, size = metaData.getColumnCount(); i <= size; i++) {
ColumnData cd = new ColumnData(metaData.getColumnName(i),
metaData.getColumnTypeName(i),
metaData.getColumnDisplaySize(i),
metaData.getScale(i));
list.add(cd);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
try {
if (res != null) {
res.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
CopyDataToMySQL.java
package tm.utility;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import tm.entity.ColumnData;
import tm.pretreatment.MySQLHelper;
import tm.pretreatment.ODaos;
/**
* 将Oracle数据迁移到MySQL
*
* @author Cetrin Wang
*
*/
public class CopyDataToMySQL {
public static void insertDataToMysql(String tableName) {
List<ColumnData> list = CopyTypeToMySQL.getColList(tableName,"id,content");
Connection conn = null;
PreparedStatement pstm = null;
StringBuffer sb = new StringBuffer();
sb.append("INSERT INTO ").append(tableName.toUpperCase()).append("(");
for (int i = 0; i < list.size(); i++) {
ColumnData c = list.get(i);
sb.append(c.getColumnName() + ",");
}
sb = sb.deleteCharAt(sb.length() - 1);
sb.append(") values ( ").append(ODaos.copyString("?", list.size()))
.append(")");
ResultSet rs = getOracleData(tableName,"id,content");
try {
conn = MySQLHelper.getConntion();
conn.setAutoCommit(false);
pstm = conn.prepareStatement(sb.toString());
int count = 0;
while (rs.next()) {
count ++;
for (int i = 1; i <= list.size(); i++) {
ColumnData c = list.get(i-1);
String type = c.getColumnType();
String name = c.getColumnName();
if("NUMBER".equalsIgnoreCase(type)){
if(c.getColScale()>0){
pstm.setDouble(i, rs.getDouble(name));
}else{
pstm.setInt(i,rs.getInt(name));
}
}else if("DATE".equalsIgnoreCase(type)){
pstm.setDate(i, rs.getDate(name));
}else if("BLOB".equalsIgnoreCase(type)){
pstm.setString(i, ODaos.formatContent3(rs.getBlob(name)));
}else{
pstm.setString(i, rs.getString(name));
}
}
pstm.addBatch();
if(count % 1000 == 0 || count ==150038){
pstm.executeBatch();
conn.commit();
System.out.println("第"+count+"条数据,1000条数据已更新!");
pstm.clearBatch();
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
MySQLHelper.closeConnection(pstm, null);
}
}
/**
* 获取oracle表中的数据
*
* @return 数据集
*/
private static ResultSet getOracleData(String tableName) {
String selectSql = "select * from " + tableName +" where content_clean is not null and rownum <= 100 ";
Connection oConn = ODaos.getOracleConnection();
PreparedStatement pstm = null;
try {
pstm = oConn.prepareStatement(selectSql);
return pstm.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
/**
* 获取oracle表中的数据
*
* @return 数据集
*/
private static ResultSet getOracleData(String tableName,String colName) {
String selectSql = "select "+colName+" from " + tableName;
Connection oConn = ODaos.getOracleConnection();
PreparedStatement pstm = null;
try {
pstm = oConn.prepareStatement(selectSql);
return pstm.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public static void main(String[] args) {
insertDataToMysql("BD_ANALY_PA_INS_TOTAL");
}
}