0

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();
    }
}
Improve this question
3
  • 1
    If you don't explicitly put values into a column, then the default value is used. If no default is specified, then the value is NULL. Commented Sep 4, 2020 at 13:43
  • 1
    Your loops are inside out. The while should enclose the for. Commented Sep 4, 2020 at 15:11
  • I put the for loop below the while, but again didn't work, do you have in code any suggestion? How can I do that? Commented Sep 4, 2020 at 15:20

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I saw that, but I can't understand how to put all values, any suggestion?
Use StringBuilder to form a list of column names and a list of values.
I did a list of column names, but I don't know how to make a list of values there, because I need to take all columns in that filter...
1

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();
}
Improve this answer

4 Comments

This will be for transfer tables content from Hana DB to one MySQL DB, both use JDBC, the thing is, I understand that exist some differences for some data types, that why I try with switch
@RobertVasile Hana DB would require a different JDBC driver to that of MySQL, no? You should edit your question and add that information, i.e. that the target database is Hana DB and the source database is MySQL.
@Abra It's the other way, from Hana to MySQL. --- Anyway, since we don't know the content of urlDB1 and urlDB2, we shouldn't assume that they are the same database flavor, i.e. use the same JDBC driver.
I don't know yet, my mentor told, we have 2 DB, one is Hana DB and one is MySql, both using JDBC, and he needs something to transfer the table content from Hana to MySql. He suggested that switch because he says that can be some different data, for example, some data can be stored into String fields for example, for not losing data when he transfer, because of precision, length etc.
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
Improve this answer

5 Comments

I try and he gives me this exception: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
You're right, the first for loop's end condition must be column <= columnCount. = was missing.
Now I have another exception that say: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'numar' in 'field list' "numar" is the last column name in the table
@RobertVasile Seems your target table doesn't have the same columns as the source table. Check the tables to be sure they have the same columns, with the same names and types.
I've added a System.out.println to print out the prepared statement (not tested, hope it prints some human readable SQL). If you want this code to be more resilient, you should compare the metadatas of both tables to verify they have the same column definitions and throw an exception with the details of the diff(s).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.