java代码的方式从mysql数据库导入多张表到postgresql数据库

最新推荐文章于 2024-07-03 18:02:25 发布

大声哈哈哈

最新推荐文章于 2024-07-03 18:02:25 发布

阅读量664

点赞数 2

分类专栏： mysql postgresql 文章标签： java

本文链接：https://blog.csdn.net/qq_44600359/article/details/102364488

版权

mysql 同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

postgresql

1 篇文章 0 订阅

订阅专栏

由于本人是一个java开发人员，所以用了java的方式

步骤
1 。拷贝一些基础数据连接两个数据库，拷贝能考的
需要注意的是mysql数据库的时间格式和postgresql 不一样，我这边做了转换。

     //声明Connection对象
        Connection con;
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost/test?serverTimezone=GMT%2B8";
        String user = "root";
        String password = "root";

        Connection con2;
        String driver2 = "org.postgresql.Driver";
        String url2 = "jdbc:postgresql://localhost:5432/escort_dh";
        String user2 = "postgresql";
        String password2 = "root";
        try {
            //加载驱动程序
            Class.forName(driver);
            con = DriverManager.getConnection(url,user,password);
            Class.forName(driver2);
            con2 = DriverManager.getConnection(url2,user2,password2);
            if(!con.isClosed()){
                System.out.println("成功连接到数据库1!");
            }
            if (!con2.isClosed()){
                System.out.println("成功连接到数据库2!");
            }
            //2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();
            //要执行的SQL语句
            String sql = "select no,card,inputTime,removed,enabled from escortgoods";
            //要执行的SQL语句
            String sql2 = "insert into et_escort_goods (id,no,type,node,card_ids,create_time,create_by,update_time,update_by,removed,enabled) " +
                    "values(?,?,null,null,?,?,1,null,null,?,?)";
            //3.ResultSet类，用来存放获取的结果集！！
            ResultSet rs = statement.executeQuery(sql);
            PreparedStatement pst = con2.prepareStatement(sql2);
            int i = 226;
            while(rs.next()){

                System.out.println(i);
                System.out.print(rs.getLong(1)+" ");
                System.out.print(rs.getString(2)+" ");
                System.out.print(rs.getString(3)+" ");
                System.out.print(rs.getLong(4)+" ");
                System.out.print(rs.getLong(5)+" ");
//            	System.out.print(rs.getString(2)+"\n");
                ++i;
                String str = rs.getString(3);


                pst.setLong(1,i);


                pst.setString(2,rs.getString(1));
                pst.setString(3,rs.getString(2));


//时间转换

                if (str==null){
                    pst.setDate(4,rs.getDate(3));
                }else {
                    DateFormat format= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    Date date=format.parse(str);
                    Timestamp sqlDate = new Timestamp(date.getTime());
                    pst.setTimestamp(4,sqlDate);
                }

                boolean fouth  = true;
                if (rs.getLong(4)==0){
                    fouth= false;
                }
                boolean fifth = true;
                if (rs.getLong(5)==0){
                    fifth = false;
                }
                pst.setBoolean(5,fouth);
                pst.setBoolean(6,fifth);



                pst.executeUpdate();


            }
            System.out.println("完成");
            rs.close();
            con.close();
            con2.close();
        }catch(ClassNotFoundException e) {
            //数据库驱动类异常处理
            System.out.println("对不起,找不到驱动程序!");
            e.printStackTrace();
        } catch(SQLException e) {
            //数据库连接失败异常处理
            e.printStackTrace();
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }

步骤 2 对外键进行拷贝，首先搞清楚外键的关系，我这边由于mysql表中的数据是uuid加密的，所以无法对应拷贝。所以我这边比较麻烦

   //声明Connection对象
        Connection con;
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost/test?serverTimezone=GMT%2B8";
        String user = "root";
        String password = "root";

        Connection con2;
        String driver2 = "org.postgresql.Driver";
        String url2 = "jdbc:postgresql://localhost:5432/test";
        String user2 = "postgresql";
        String password2 = "root";
        try {
            //加载驱动程序
            Class.forName(driver);
            con = DriverManager.getConnection(url,user,password);
            Class.forName(driver2);
            con2 = DriverManager.getConnection(url2,user2,password2);
            if(!con.isClosed()){
                System.out.println("成功连接到数据库1!");
            }
            if (!con2.isClosed()){
                System.out.println("成功连接到数据库2!");
            }
            //2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();
            //要执行的SQL语句
            String sql = "select card,goods from card";
            //要执行的SQL语句
            String sql2 = "UPDATE good SET  id = ? WHERE  no = ?";
            //3.ResultSet类，用来存放获取的结果集！！
            ResultSet rs = statement.executeQuery(sql);
            PreparedStatement pst = con2.prepareStatement(sql2);
            int i = 1;
            while(rs.next()){

                System.out.println(i);
                System.out.print(rs.getString(1)+" ");
                System.out.print(rs.getString(2)+" ");
                ++i;

//                Connection   con3 = DriverManager.getConnection(url,user,password);
                //2.创建statement类对象，用来执行SQL语句！！

                String sql3 = "select no from escortgoods where id = ? ";
                PreparedStatement preparedStatement = con.prepareStatement(sql3);
                preparedStatement.setString(1,rs.getString(2));
                ResultSet resultSet = preparedStatement.executeQuery();


                int id = 0 ;
                while (resultSet.next()){
//                    Connection   con4 = DriverManager.getConnection(url2,user2,password2);
                    //2.创建statement类对象，用来执行SQL语句！！

                    String sql4 = "select idfrom goodswhere no= ? ";
//            	System.out.print(rs.getString(2)+"\n");
                    PreparedStatement preparedStatement1 = con2.prepareStatement(sql4);
                    preparedStatement1.setString(1,resultSet.getString(1));
                    ResultSet resultSet1 = preparedStatement1.executeQuery();
                    while (resultSet1.next()){
                        id = resultSet1.getInt(1);
                        break;

                    }
                     break;
                }


                System.out.println("id:"+id);

                if (id==0){
                    continue;
                }
                pst.setInt(1,id);
                pst.setString(2,rs.getString(1));




                pst.executeUpdate();


            }
            System.out.println("完成");
            rs.close();
            con.close();
            con2.close();
        }catch(ClassNotFoundException e) {
            //数据库驱动类异常处理
            System.out.println("对不起,找不到驱动程序!");
            e.printStackTrace();
        } catch(SQLException e) {
            //数据库连接失败异常处理
            e.printStackTrace();
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }

**
基本上就完成了，这边需要注意的是·拷贝外键时由于要查询多次sql，查出来的值有可能是null值，所以此时如果数据库不允许空值的存在，自己写一个判断，使用continue;关键词跳出本次循环，自己把握。