Connecting with multiple Databases, getting Data and writing data to a new database

Question

So, I guess this should be really easy, but I started with Java just a week ago and have to finish a little team project this semester.

I already have a program that creates databases, tables and adds data into it. That wasn't to hard since there are lots of tutorials on this topic.

Now I have to write a program that is able to connect with my databases, get their data and merge it into a new larger database containing all the data from the other DBs. And I just can't find any help on that since 3 days...

Someone told me the easiest way to to it, would be to get one Database into Java and copy the data to the second database and he wrote down a bit of code for me:

package Databse;
import java.sql.*;
import java.util.HashMap;
import java.util.Vector;
import java.util.Map;

/**
 *
 * @autho
 */
public class Join {
    public static void main( String args[] ) {
        Connection c;
        Statement stmt;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:test.db");
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            stmt = c.createStatement();

            Vector<HashMap<String, String>> alleDaten = new Vector<HashMap<String, String>>();

            try (ResultSet rs = stmt.executeQuery( "SELECT * FROM FILE;" )) {
                while ( rs.next() ) {
                    int id = rs.getInt("id");
                    String  name = rs.getString("name");
                    String extension = rs.getString("extension");
                    String  path  = rs.getString("path");
                    String timeStamp = rs.getString("timeStamp");

                    HashMap<String, String> datensatz = new HashMap<String, String>();
                    datensatz.put("name", name);
                    datensatz.put("extension", extension);
                    datensatz.put("path", path );
                    datensatz.put("timestamp", timeStamp);                
                    alleDaten.add(datensatz);            

                } 
            }  
            stmt.close();
            c.close();

        } catch ( ClassNotFoundException | SQLException e ) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            System.exit(0);
        }
        System.out.println("Operation done successfully");
    }

He told me that I have to iterate through the vector now. Something like for(int i = 0 ; i < alleDaten.length ; i++) doInsert(alleDaten[i]); and that I have to write a doInsert function as well...

I just dont know how i to do this and where to start. I don't even know what i should search for in google to get this done... So could anyone help me with that?

score 0 · Answer 1 · edited May 23 '17 at 12:11

If SqlLite works like most such databases, you can open a second connection, to the second database.

You do not need to pull all the records into a Java collection. As you fetch the next record from your source resultset, you can insert it into the destination. Assuming your second connection is "c2" and the statement for your insert there is "stmt2"... Then, inside the loop, you can do this (you do not need to read everything into a Java collection first).

 StringBuilder sb = new StringBuilder("insert into MY_DEST_TBL values ");
 //--- Build this into a proper insert SQL

 stmt2 = c2.createStatement();
 stmt2.executeUpdate(sb.toString());

If you have a sizable amount of data, then you should use PreparedStatement and executeBatch() to improve performance, but I won't go into that unless you have a question.

I just found a similar question.

Connecting with multiple Databases, getting Data and writing data to a new database

1 Answers1