示例2:从MY 迁移数据
下面示例演示如何通过CopyManager 从MY 向GBase 8c 进行数据迁移的过程。
import java.io.StringReader; import java.sql.Connection;
import java.sql.DriverManager; import java.sql.ResultSet;
import java.sql.SQLException; import java.sql.Statement;
GBase 8c V5 开发者手册
南大通用数据技术股份有限公司
313
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
public class Migration{
public static void main(String[] args) {
String url = new String("jdbc:postgresql://localhost:15432/postgres"); //数据库URL
String user = new String("username");
//GBase 8c 数据库用户名
String pass =
new String("passwd");//GBase 8c 数据库密码String
tablename = new String("migration_table_1"); //定义表信息
String delimiter = new String("|"); //定义分隔符
String encoding = new String("UTF8"); //定义字符集
String driver = "org.postgresql.Driver";
StringBuffer buffer = new StringBuffer();//定义存放格式化数据的缓存
try {
//获取源数据库查询结果集ResultSet rs = getDataSet();
//遍历结果集,逐行获取记录
//将每条记录中各字段值,按指定分隔符分割,由换行符结束,拼成一个字符串
//把拼成的字符串,添加到缓存buffer while (rs.next()) {
buffer.append(rs.getString(1) + delimiter
+ rs.getString(2) + delimiter
+ rs.getString(3) + delimiter
+ rs.getString(4)
+ "\n");
}
rs.close();
try {
//建立目标数据库连接Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, pass);
BaseConnection baseConn = (BaseConnection) conn;
baseConn.setAutoCommit(false);
//初始化表信息
String sql = "Copy " + tablename + " from STDIN with (DELIMITER "
+ "'" + delimiter + "'" +","+ " ENCODING " + "'" + encoding +
"'");
//提交缓存buffer 中的数据
CopyManager cp = new CopyManager(baseConn);
StringReader reader = new StringReader(buffer.toString());
cp.copyIn(sql, reader);
baseConn.commit(); reader.close(); baseConn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace(System.out);
} catch (SQLException e) {
GBase 8c V5 开发者手册
南大通用数据技术股份有限公司
314
e.printStackTrace(System.out);
}
} catch (Exception e) {
e.printStackTrace();
}
}
//********************************
// 从源数据库返回查询结果集
//*********************************
private static ResultSet getDataSet() { ResultSet rs = null;
try {
Class.forName("com.MY.jdbc.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:MY://10.119.179.227:3306/jack?
useSSL=false&allowPublicKeyRetrieval=true", "jack", "xxxxxxxxx");
Statement stmt = conn.createStatement();
rs = stmt.executeQuery("select * from migration_table");
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return rs;
}
}