CODEWITHSUNDEEP
javaresultset
Elisetty Narendra
Elisetty Narendra

Reputation: 19

Working with Java String and ResultSet Object Dynamically

I would like to fetch data from Database Table A and create an insert statement to load Table B. To preserving the data type, I am creating a one Time statament using the ResultSet methods to read from table A as shown below.

Insert into table values (rs.getString(1),rs.getInt(3),rs.getString(5),rs.getInt(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getInt(10),rs.getString(11),rs.getInt(13),rs.getString(16))

My next step would be iterating over the records from Table A and sending these insert statements as BATCH to TABLE B. But how do I replace the Strings (rs.getString(1) and rs.getInt(3) ... ) with actual values while looping on rs.next() .

Upvotes: 1

Views: 984

Answers (3)

Ivan
Ivan

Reputation: 8758

You need to create PreparedStatement for insert like this using placeholders:

PreparedStatement insert = connection.prepareStatement("INSERT INTO TABLE_B VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
while (rs.next()) {
  insert.setString(1, rs.getString(1)); 
  insert.setInt(2, rs.getInt(3));   
  insert.setString(3, rs.getString(5));  
  insert.setInt(4, rs.getInt(6)); 
  insert.setString(5, rs.getString(7)); 
  insert.setInt(6, rs.getInt(8)); 
  insert.setString(7, rs.getString(9)); 
  insert.setInt(8, rs.getInt(10)); 
  insert.setString(9, rs.getString(11));
  insert.setInt(10, rs.getInt(13)); 
  insert.setString(11, rs.getString(16));
  insert.addBatch();
  // call insert.executeBatch() once you accumulate enough records for insert
}

If types of columns in target table match types of columns used from source table you could use ResultSetMetaData to set parameters in PreparedStatement

  ResultSetMetaData md = rs.getMetaData();
  while (rs.next()) {
  for (int m = 1; m <= md.getColumnCount(); m++) {
            if (rs.getObject(m) == null) {
                targetStatement.setNull(m, md.getColumnType(m));
            } else if (md.getColumnType(m) == OracleTypes.VARCHAR) {
                targetStatement.setString(m, rs.getString(m));
            } else if (md.getColumnType(m) == OracleTypes.NUMBER) {
                targetStatement.setDouble(m, rs.getDouble(m));
            } else if (md.getColumnType(m) == OracleTypes.CHAR) {
                targetStatement.setString(m, rs.getString(m));
            } else if (md.getColumnType(m) == OracleTypes.TIMESTAMP) {
                targetStatement.setTimestamp(m, rs.getTimestamp(m));
            } else if (md.getColumnType(m) == OracleTypes.DATE) {
                targetStatement.setDate(m, rs.getDate(m));
            } else {
                targetStatement.setString(m, rs.getString(m));
            }
  }
  insert.addBatch();
  // call insert.executeBatch() once you accumulate enough records for insert
}

Upvotes: 1

Joop Eggen
Joop Eggen

Reputation: 109567

Try to do all in a single statement first.

INSERT INTO xtable (x1, x2, x3, x4, x5, x6, x7, x8, x9, x10, x11)
(SELECT y1, y3, y5, y6, y7, y8, y9, y10, y11, y13, y16
 FROM ytable
 WHERE ...)

Otherwise use the PreparedStatement as of @Ivan.

Upvotes: 1

Marko Djurovic
Marko Djurovic

Reputation: 141

String template = "Insert into TABLE_B values(\"{0}\", {1}, \"{2}\", {3}, \"{4}\", {5}, \"{6}\", {7}, \"{8}\", {9}, \"{10}\")";
MessageFormat mf = new MessageFormat(template);
while (rs.next()}
   Object[] params = new Object[11];
   params[0] = rs.getString(1);
   params[1] = rs.getInt(3);
   params[2] = rs.getString(5);
   params[3] = rs.getInt(6);
   params[4] = rs.getString(7);
   params[5] = rs.getInt(8);
   params[6] = rs.getString(9);
   params[7] = rs.getInt(10);
   params[8] = rs.getString(11);
   params[9] = rs.getInt(13);
   params[10] = rs.getString(16);

   String command = mf.format(params);
   //do with command what ever want to do
}

Bu why to bother when you can do insert from table to table with one SQL command: INSERT INTO...SELECT

Upvotes: 0

Related Questions