Geek asks and answers

Extract data using spring jdbctemplate to load in another database

I need to extract data from some random tables from my database and insert into similar tables in another database.

I do not understand how I can write a general method that can process all tables.

this.jdbcTemplate.query("select * from TableName", new RowMapper() {
         @Override
         public Object mapRow(ResultSet resultSet, int i) throws SQLException {
             while(resultSet.next()){
//                Fetch data in a generic object for later updating in a different schema
             }
             return null;  //To change body of implemented methods use File | Settings | File Templates.
         }
     });
+5
source share
2 answers

To be honest, JdbcTemplatenot the best choice for this kind of task. You will need to do some one-time processing ResultSetto create the SQL insert, and in fact you have nothing to do using JdbcTemplate(as far as I know).

Anyway, that’s how I’ll make the copy you want in pure JDBC (you can use the same principles and compress it in JdbcTemplateif you want):

Connection sourceConnection = null; 
Connection destinationConnection = null; 

PreparedStatement selectStatement = null;
PreparedStatement insertStatement = null;

ResultSet resultSet = null;

try
{
  sourceConnection = ...
  destinationConnection = ...

  selectStatement = sourceConnection.prepareStatement("SELECT * FROM table");
  resultSet = selectStatement.executeQuery();

  insertStatement = destinationConnection.prepareStatement(createInsertSql(resultSet.getMetaData()));

  int batchSize = 0;
  while (resultSet.next())
  {
    setParameters(insertStatement, resultSet);
    insertStatement.addBatch();
    batchSize++;

    if (batchSize >= BATCH_EXECUTE_SIZE)
    {
      insertStatement.executeBatch();
      batchSize = 0;  
    }
  }

  insertStatement.executeBatch();
}
finally 
{
  JdbcUtils.closeResultSet(resultSet);

  JdbcUtils.closeStatement(insertStatement);
  JdbcUtils.closeStatement(selectStatement);

  JdbcUtils.closeConnection(destinationConnection);
  JdbcUtils.closeConnection(sourceConnection);
}

- , createInsertSql setParameters, ResultSetMetaData . , :

private String createInsertSql(ResultSetMetaData resultSetMetaData) throws SQLException
{
  StringBuffer insertSql = new StringBuffer("INSERT INTO ");
  StringBuffer values = new StringBuffer(" VALUES (");

  insertSql.append(resultSetMetaData.getTableName());

  for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++)
  {
    insertSql.append(resultSetMetaData.getColumnName(i));
    values.append("?");

    if (i <= resultSetMetaData.getColumnCount())
    {
      insertSql.append(", ");
      values.append(", ");
    }
    else 
    {
      insertSql.append(")");
      values.append(")");
    }
  }

  return insertSql.toString() + values.toString();
}


private void setParameters(PreparedStatement preparedStatement, ResultSet resultSet) throws SQLException
{
  for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++)
  {
    preparedStatement.setObject(i, resultSet.getObject(i)); 
  }  
}

, , . , , ETL.

/ .

DatabaseMetaData , , , .

, , , , , sql.

, 1, 2, 3, 4, 7 , 1, 2, 4, 5, 6, :

  • 1 =
  • 2 =
  • 3, 4
  • 4 =
  • 7 , 6, , 7 - .

, , .

+1

this.jdbcTemplate.setDateSource(sourceDB)


this.jdbcTemplate.setDateSource(targetDB)

.

spring -

@Autowired
@Qualifier("writeDataSource")
public void setDataSource(DataSource writeDataSource) {
    this.jdbcTemplate = new JdbcTemplate(writeDataSource);
}
0

More articles:


All Articles