mysql自动转库_JAVA自动操作0racle数据库转mysql数据库

1.要注意数据编码，在建表时就应该设置好默认编码，我这里是UTF-8；

CREATE TABLE `CODE_ZYB` (

`uid` int(64) NOT NULL AUTO_INCREMENT,

`XSH` varchar(5) DEFAULT NULL,

`ZYH` varchar(8) DEFAULT NULL,

`XKMLH` varchar(2) DEFAULT NULL,

`ZYM` varchar(40) DEFAULT NULL,

`YWZYM` varchar(80) DEFAULT NULL,

`BZ` varchar(255) DEFAULT NULL,

`XJZYH` varchar(8) DEFAULT NULL,

`XJZYM` varchar(40) DEFAULT NULL,

PRIMARY KEY (`uid`)

) engine=myisam default charset=utf8; 注意(如果是GBK就把下面的utf8改为gbk)：

engine=myisam default charset=utf8;

下面是java的代码：

package jw;

import java.io.UnsupportedEncodingException;

import java.sql.*;

public class Jw1 {

public static void main(String[] args) throws UnsupportedEncodingException {

// TODO code application logic here

String url = "jdbc:oracle:thin:@host:1521:test"; //test为自己数据库的名字，1521为端口

String username = "name";

String password = "password";

String driver = "oracle.jdbc.driver.OracleDriver";

try {

Class.forName(driver); //加载驱动

Connection conn = DriverManager.getConnection(url, username,

password); //连接数据库

PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM JW.CODE_ZYB"); // 预处理命令，比Statement 好一些

ResultSet rs = pstmt.executeQuery();

Connection con = DriverManager

.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF8"

,"user", "pass"); //mysql数据库连接并且设置为UTF8默认连接；

PreparedStatement sta = con.prepareStatement("set names utf8"); //连接后转为UTF8编码

sta.executeQuery(); //执行sql；

PreparedStatement st = con.prepareStatement("TRUNCATE TABLE CODE_ZYB"); //执行重复插入数据

st.executeUpdate(); //插入，删除，增加使用executeupdate；

while(rs.next())

{

//System.out.println(rs.getString(1));

String sql = "INSERT INTO CODE_ZYB(`XSH`,`ZYH`,`XKMLH`"

+ ",`ZYM`,`YWZYM`,`BZ`,`XJZYH`,`XJZYM`)"

+ "VALUES (?,?,?,?,?,?,?,?)";

sta = con.prepareStatement(sql);

for(int i=1;i<9;i++){

String s = rs.getString(i);

if (s == null || s.isEmpty() ) //处理oracle过来的为空的数据

s = "";

else

s = new String(s.getBytes("UTF-8"), "UTF-8"); //数据编码转换

System.out.println(s+","+i);

sta.setString(i,s); //占位符处理；

}

sta.execute();

sta.close(); //关闭sql

}

pstmt.close(); //Oracle sql查询关闭

conn.close(); //Oracle 连接关闭

con.close(); //Mysql 连接关闭

} catch (ClassNotFoundException e) {

} catch (SQLException e) {

System.out.print(e.getMessage());

}

System.out.println("-------- MySQL JDBC Connection Testing ------------");

try {

Class.forName("com.mysql.jdbc.Driver");

} catch (ClassNotFoundException e) {

System.out.println("Where is your MySQL JDBC Driver?");

return;

}

System.out.println("MySQL JDBC Driver Registered!");

Connection connection = null;

try {

connection = DriverManager

.getConnection("jdbc:mysql://localhost:3306/test","user", "pass");

connection.close();

} catch (SQLException e) {

System.out.println("Connection Failed! Check output console");

return;

}

if (connection != null) {

System.out.println("You made it, take control your database now!");

} else {

System.out.println("Failed to make connection!");

}

}

}