How to copy table values from a database to another database

Question

I have implemented this code but doesn't work. Can anyone tell me where the mistake is cause I can't find it :( This is the code:

PreparedStatement stmA = connsqlite.prepareStatement("SELECT * FROM  dbTable");
PreparedStatement stmB = connphpmyadmin.prepareStatement("INSERT INTO codes VALUES ('"??????????"')");
stmA.executeQuery("DROP TABLE dbTable");
stmB.executeUpdate();

connsqlite.close();
connphpmyadmin.close();

As you see i want to synchronize 2 different databases, one local and one server. When the synchronization is done I want to delete tha table from the local database. I think that the answer is inside the "??????" that I have put but I don't know what to write there. If anyone know please help me understand. Thank you!

I hope you want to synchronize one table between 2 database, not all database to another ? — Paweł Głowacz, Aug 04 '15 at 09:57
I have 1 table to 1 database and a similar table to a local database and i want to synchronize the table from the local database to the server table — Konstantinos Makrygiannis, Aug 04 '15 at 10:06
Possible duplicate of [How to copy table from one database to another?](https://stackoverflow.com/questions/22624711/how-to-copy-table-from-one-database-to-another) — Alex R, Sep 17 '18 at 16:20

score 0 · Answer 1 · answered Aug 04 '15 at 10:15

This is just an example how you could do this. Remeber that ? mark is one parameter/value to pass. So you need to modify your query for your purpose. I have no idea what type of columns your table has so modify this if you need.

        //Representation of single item in table
        class DbTableItem {
            private String value1;
            private String value2;
            private String value3;
            //... Whatever field that required to exchange information between 2 tables
            public String getValue1() {
                return value1;
            }

            public void setValue1(String value1) {
                this.value1 = value1;
            }

            public String getValue2() {
                return value2;
            }

            public void setValue2(String value2) {
                this.value2 = value2;
            }

            public String getValue3() {
                return value3;
            }

            public void setValue3(String value3) {
                this.value3 = value3;
            }
        }

        Connection connection1 =//your connection to local db;
        Connection connection2 =//your connection to another db;
        PreparedStatement stmA = connection.prepareStatement("SELECT * FROM  dbTable");
        //Executing query to retreive data
        ResultSet resultSet = stmA.executeQuery();

        List<DbTableItem> dbTableList = new ArrayList<>();

        //Setting items and adding to list
        while (resultSet.next()){
            DbTableItem dbTableItem = new DbTableItem();
            dbTableItem.setValue1(resultSet.getString("COLUMN_NAME"));
            dbTableItem.setValue2(resultSet.getString("COLUMN_NAME"));
            dbTableItem.setValue2(resultSet.getString("COLUMN_NAME"));
            dbTableList.add(dbTableItem);
        }
        //Preparing next query for batch process
        PreparedStatement stmB = connection2.prepareStatement("INSERT INTO codes VALUES (?,?,?)");
        //Adding to batch
        for(DbTableItem dbTableItem: dbTableList) {
            stmB.setObject(1, dbTableItem.getValue1());
            stmB.setObject(2, dbTableItem.getValue2());
            stmB.setObject(2, dbTableItem.getValue3());
            //And so on
            stmB.addBatch();
        }
        //Executing batch with query
        stmB.executeBatch();

        //Droping table
        //Closing connection1 and connection2

How to copy table values from a database to another database

1 Answers1