使用JDBC数据迁移把Mysql数据到另一个库中

数据迁移

简介:

整体思路链接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();
    }
}