数据迁移
简介:
整体思路链接2个需要迁移的数据库 根据sql 进行查询 判断什么样的数据需要迁移,什么样的数据需要过滤掉,数据重复或者出错的情况输出到某个文件中
如果数据可以整体迁移而且不出格式差异的情况也可以直接导出sql文件进行迁移
此文仅供参考
数据库迁移方式: 1.JDBC 迁移
2.从一个库导出sql文件到另一个库中执行sql文件
public class CmsShopOrder {
public void transUserData(){
try{
//数据库连接配置
String urlaudiclub = "jdbc:mysql://127.0.0.1/AAA?useUnicode=true&characterEncoding=utf8";
Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动
String useraudiclub = "aaa";
String passwordaudiclub = "aaa";
//登陆数据库
Connection connaudiclub = DriverManager.getConnection(urlaudiclub,useraudiclub,passwordaudiclub);
Statement stmtaudiclub = connaudiclub.createStatement();
//数据库连接配置
String urlceshi = "jdbc:mysql://127.0.0.1/BBB?useUnicode=true&characterEncoding=utf8";
Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动
String userceshi = "bbb";
String passwordceshi = "bbb";
//登陆数据库
Connection connceshi = DriverManager.getConnection(urlceshi,userceshi,passwordceshi);
// 实例化Statement对象
String sqlaudiclub = "查询对应表所有的sql例如:select userid,email,username,mobile,regtype from user";
// 实例化Statement对象
Statement stmtaudionlineInset = connceshi.createStatement();
// 实例化Statement对象
Statement stmtaudionlineSelectUser = connceshi.createStatement();
//查询数据库查询结果存放在ResultSet类对象中
ResultSet rsaudiclub = stmtaudiclub.executeQuery(sqlaudiclub);
int countflag =0;
String flagJf="'jf'";
while(rsaudiclub.next()){
if(null!=rsaudiclub.getObject("userid")){
String userid = rsaudiclub.getObject("userid").toString();
String sqlaudiclubUser = "select userid,email,username,mobile,regtype from user where userid="+ userid;
// 实例化Statement对象
Statement stmtaudiclubUser = connaudiclub.createStatement();
//查询数据库查询结果存放在ResultSet类对象中
ResultSet rsaudiclubUser = stmtaudiclubUser.executeQuery(sqlaudiclubUser);
//判断是否存在上面查询的数据
if(rsaudiclubUser.next()){
String audionlineUser=null;
if(null !=rsaudiclubUser.getString("regtype")){
String regtype = rsaudiclubUser.getObject("regtype").toString();
// 判断注册用户类型后执行查询
if(regtype.equals("mobile")){
audionlineUser = "select userid,email,username,mobile,regtype from user where mobile = "+条件+" and regtype="+"'"+条件1+"'";
}else if(regtype.equals("email")){
audionlineUser = "select userid,email,username,mobile,regtype from user where email = '"+条件+"' and regtype="+"'"+条件+"'";
}else{
audionlineUser = "select userid,email,username,mobile,regtype from user where username = "+"'"+条件+"'";
}
}else{
audionlineUser = "select userid,email,username,mobile,regtype from roilandcms_user where username = "+"'"+条件+"'";
}
//查询数据库查询结果存放在ResultSet类对象中
ResultSet audionlineSelectUser = stmtaudionlineSelectUser.executeQuery(audionlineUser);
if(audionlineSelectUser.next()){
String aUserid = audionlineSelectUser.getObject("userid").toString();
if(null != audionlineUser && audionlineUser!=""){
// 查询导入库中数据 判断vin是否有重复
String yanzhengchongfu = "select count(1) from cms_shop_order where orderid="+"'"+条件+"'";
Statement stmAudionlineCompareA = connceshi.createStatement();
ResultSet yanzhengchongfuSet = stmAudionlineCompareA.executeQuery(yanzhengchongfu);
while(yanzhengchongfuSet.next()){
int identicalA = yanzhengchongfuSet.getInt(1);
if(identicalA==0){
String sqlcustomerinsert = "insert into cms_shop_order(插入的字段)";
sqlcustomerinsert += "values('"+值+");";
// 执行sql语句
stmtaudionlineInset.execute(sqlcustomerinsert);
}else{
String sqlcustomerinsert = "insert into cms_shop_order(字段)";
sqlcustomerinsert += "values('"+值+");";
File file = new File("D:/customerdata/CmsShopOrder.sql");
if (!file.exists()) {
try {
//如果目录下没有"xx"这个文件则新建一个。
file.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}
// 写入数据文件字节输出流 ,如果第二个参数为true,则将字节写入文件末尾处,而不是写入文件开始处
FileOutputStream fos = new FileOutputStream(file,true);
// 字符串转化为一个字节数组byte[]
countflag++;
if(countflag==1){
byte[] bytes = sqlcustomerinsert.getBytes();
//把字符串"xx"以字节的形式写入
fos.write(bytes);
}else{
byte[] bytes = ("\r\n"+sqlcustomerinsert).getBytes();
//把字符串"xx"以字节的形式写入
fos.write(bytes);
}
}
}
}
}else{
String sqlcustomerinsert = "insert into cms_shop_order()";
sqlcustomerinsert += "values('"+值+");'user表中暂无对应用户addressid为原始数据'";
File file = new File("D:/customerdata/CmsShopOrder.sql");
if (!file.exists()) {
try {
//如果目录下没有"xx"这个文件则新建一个。
file.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}
// 写入数据文件字节输出流 ,如果第二个参数为true,则将字节写入文件末尾处,而不是写入文件开始处
FileOutputStream fos = new FileOutputStream(file,true);
// 字符串转化为一个字节数组byte[]
countflag++;
if(countflag==1){
byte[] bytes = sqlcustomerinsert.getBytes();
//把字符串"xx"以字节的形式写入
fos.write(bytes);
}else{
byte[] bytes = ("\r\n"+sqlcustomerinsert).getBytes();
//把字符串"xx"以字节的形式写入
fos.write(bytes);
}
}
}else{
需要输出的形式或操作
}
}else{
String sqlcustomerinsert = "insert into cms_shop_order(字段)";
sqlcustomerinsert += "values('"+值+");";
File file = new File("D:/customerdata/CmsShopOrder.sql");
if (!file.exists()) {
try {
//如果目录下没有"xx"这个文件则新建一个。
file.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}
// 写入数据文件字节输出流 ,如果第二个参数为true,则将字节写入文件末尾处,而不是写入文件开始处
FileOutputStream fos = new FileOutputStream(file,true);
// 字符串转化为一个字节数组byte[]
countflag++;
if(countflag==1){
byte[] bytes = sqlcustomerinsert.getBytes();
//把字符串"xx"以字节的形式写入
fos.write(bytes);
}else{
byte[] bytes = ("\r\n"+sqlcustomerinsert).getBytes();
//把字符串"xx"以字节的形式写入
fos.write(bytes);
}
}
}
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
System.out.println("===========CmsShopOrder失败=============");
}finally{
System.out.println("===========CmsShopOrder完成=============");
}
}
public Object stringisnull(Object str){
if(str == null){
return str;
}else{
return "'"+str+"'";
}
}
public static void main(String[] args) {
CmsShopOrder cmsShopOrder = new CmsShopOrder();
cmsShopOrder.transUserData();
}
}