Inserting rows in sql table problem, some columns inserted NULL value

Question

I have 2 DB with multiple tables. All tables have the same syntax. Here I have a method that takes name of the table as an argument. The table that I try to insert is with 3 columns (int, varchar, int). The problem is, only the first row is inserted, and the 2 and 3 row is NULL, I don't know what is the problem. Any suggestions, please?

public void getAndInsertData(String nameOfTable) {

    try {

        Class.forName("com.mysql.jdbc.Driver");

        Connection con1 = DriverManager.getConnection(urlDB1, user1, password1);
        Statement s1 = con1.createStatement();

        Connection con2 = DriverManager.getConnection(urlDB2, user2, password2);
        Statement s2 = con2.createStatement();

        ResultSet rs1 = s1.executeQuery("SELECT * FROM " + nameOfTable);

        ResultSetMetaData rsmd1 = rs1.getMetaData();
        int columnCount = rsmd1.getColumnCount();

        for (int column = 1; column <= columnCount; column++) {

            String columnName = rsmd1.getColumnName(column);
            int columnType = rsmd1.getColumnType(column);

            while (rs1.next()) {

                switch (columnType) {

                    case Types.INTEGER:
                    case Types.SMALLINT:
                    case Types.BIGINT:
                    case Types.TINYINT:

                        int integerValue = rs1.getInt(column);
                        
                        String integerQuery = "insert into " + nameOfTable + " (" + columnName + ") VALUES("
                                + integerValue + ");";
                        s2.executeUpdate(integerQuery);

                        break;

                    case Types.VARCHAR:
                    case Types.NVARCHAR:
                    case Types.LONGNVARCHAR:
                    case Types.LONGVARCHAR:

                        String varcharValue = rs1.getString(column);
                        
                        String varcharQuery = "insert into " + nameOfTable + " (" + columnName + ") VALUES("
                                + varcharValue + ");";
                        s2.executeUpdate(varcharQuery);

                    default:
                        System.out.println("Default");
                        break;

                }

            }
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}

Answer 1

As The Impaler already mentioned, your loops are at the wrong place. For every record of rs1, you want to insert one record using s2.

You can build a prepared statement first using the metadata and then inject the values: ResultSetMetaData rsmd1 = rs1.getMetaData(); int columnCount = rsmd1.getColumnCount();

StringBuffer sql=new StringBuffer("insert into "+nameOfTable+" (");
for (int column = 1; column <= columnCount; column++) {

    String columnName = rsmd1.getColumnName(column);
    if(column>1)
        sql.append(",");
    sql.append(columnName);
}
sql.append(") values (");
for(int i=1;i<=columnCount;i++)
{
    sql.append((i==1?"":",")+ "?");
}
sql.append(")");
System.out.println("Prepared SQL:"+sql.toString());
// sql = insert into nameOfTable (col1,col2,col3) values (?,?,?)
PreparedStatement s2= con2.prepareStatement(sql.toString());

while (rs1.next()) {
    s2.clearParameters();
    for (int column = 1; column <= columnCount; column++) {

        int columnType = rsmd1.getColumnType(column);

        switch (columnType) {

            case Types.INTEGER:
            case Types.SMALLINT:
            case Types.BIGINT:
            case Types.TINYINT:
                s2.setInt(column, rs1.getInt(column));
                break;

            case Types.VARCHAR:
            case Types.NVARCHAR:
            case Types.LONGNVARCHAR:
            case Types.LONGVARCHAR:
                s2.setString(column, rs1.getString(column));
                break;
            default:
                System.err.println("Not supported type for column "+column+" with type:"+columnType);
                s2.setNull(column, columnType);
                break;
        }
    } // end of for loop
    // execute statement once per record in rs1
    s2.executeUpdate();
} // end of while

Answer 2

A few issues:

• Use try-with-resources to make sure the JDBC resources are cleaned up correctly.

• No need for a switch statement, because we don't actually need to know the types of the columns. The JDBC driver will handle that if you use getObject() and setObject().

• Only execute one INSERT per row from the source table.

• Use batching when inserting a lot of records, for better performance.

Here is how to do it:

try (
    Connection conSource = DriverManager.getConnection(urlDB1, user1, password1);
    Connection conTarget = DriverManager.getConnection(urlDB2, user2, password2);
    Statement stmtSource = conSource.createStatement();
    ResultSet rsSource = stmtSource.executeQuery("SELECT * FROM " + nameOfTable);
) {
    // Build insert statement
    ResultSetMetaData metaData = rsSource.getMetaData();
    int columnCount = metaData.getColumnCount();
    StringBuilder sql = new StringBuilder("INSERT INTO " + nameOfTable + " (");
    for (int column = 1; column <= columnCount; column++) {
        if (column != 1)
            sql.append(", ");
        sql.append(metaData.getColumnName(column));
    }
    sql.append(") VALUES (");
    for (int column = 1; column <= columnCount; column++) {
        if (column != 1)
            sql.append(", ");
        sql.append("?");
    }
    sql.append(")");
    
    // Copy data
    conTarget.setAutoCommit(false);
    try (PreparedStatement stmtTarget = conTarget.prepareStatement(sql.toString())) {
        int batchSize = 0;
        while (rsSource.next()) {
            for (int column = 1; column <= columnCount; column++) {
                // Copy row here. Use switch statement to control the mapping
                // if source and target table columns don't have compatible types.
                // The following statement should work for most types, so switch
                // statement only needs to cover the exceptions.
                stmtTarget.setObject(column, rsSource.getObject(column), metaData.getColumnType(column));
            }
            stmtTarget.addBatch();
            if (++batchSize == 1000) { // Flush every 1000 rows to prevent memory overflow
                stmtTarget.executeBatch();
                batchSize = 0;
            }
        }
        if (batchSize != 0)
            stmtTarget.executeBatch();
    }
    conTarget.commit();
}

Answer 3

Your integerQuery and varcharQuery both insert into datebase table a record with one filled column and blank other columns. Because you provide value to one column only.