oracle数据迁移到mysql
今天遇到需求要把oracle的部分数据搬到mysql,用java代码抓数据,然后拼接成sql语句,然后用navicat执行sql脚本的方法,导入数据库。
import oracle.jdbc.driver.OracleDriver; import java.sql.*; import java.util.Properties; public class DataTransfer { public static void main(String[] args) { Connection connect = null; Statement statement = null; ResultSet resultSet = null; try { Driver driver = new OracleDriver(); DriverManager.deregisterDriver(driver); Properties pro = new Properties(); pro.put("user", "*******"); pro.put("password", "*******"); connect = driver.connect("jdbc:oracle:thin:@ip:port:orcl", pro); statement = connect.createStatement(); resultSet = statement.executeQuery("select user_login_name, user_password, user_real_name, user_status, create_time from CFG_USER"); while (resultSet.next()) { String s = "insert into system_user(user_account, user_password, user_name, user_status, user_create_datetime, user_register_ip) values("; String log = resultSet.getString("user_login_name"); s = s + "'" + log + "'" + ","; String password = resultSet.getString("user_password"); s = s + "'" + password + "'" + ","; String realName = resultSet.getString("user_real_name"); s = s + "'" + realName + "'" + ","; String userStatus = resultSet.getString("user_status"); s = s + userStatus + ","; Date create_time = resultSet.getDate("create_time"); if (create_time == null) { s = s + "null" + ","; } else { s = s + "'" + create_time.toString() + "'" + ","; } s = s +"'"+ "127.0.0.1" + "'" + ")"; System.out.println(s + ";"); //打印输出结果集 } } catch (Exception e) { e.printStackTrace(); } finally { try { if (resultSet != null) resultSet.close(); if (statement != null) statement.close(); if (connect != null) connect.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
以上基于我的数据库字段,可根据需求选用恰当的字段。
maven依赖:
<dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc14</artifactId> <version>10.2.0.5.0</version> </dependency>
依赖最好找跟那个数据库版本相同的
然后navicat执行下拼接好的脚本即可。