I needed to copy data from a table to a table in another database with the same fields and the datatype layout. Below is the code reference that I used for the same with Spring Boot and Spring Batch.
The input query file ,the target table are the input parameters here.
import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepScope;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.batch.item.ItemProcessor;
import org.springframework.batch.item.ItemReader;
import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.database.JdbcCursorItemReader;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.FileSystemResource;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import javax.sql.DataSource;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Configuration
@EnableBatchProcessing
public class BatchConfiguration {
@Bean
@StepScope
public ItemReader < Map < String, Object >> reader(DataSource dataSource1, ApplicationArguments args) throws IOException {
JdbcCursorItemReader < Map < String, Object >> reader = new JdbcCursorItemReader < > ();
reader.setDataSource(dataSource1);
// Read the input SQL query from the specified text file
String queryTextFile = args.getOptionValues("sqlFile").get(0);
String sqlQuery = new String(Files.readAllBytes(Paths.get(queryTextFile)));
reader.setSql(sqlQuery);
reader.setRowMapper(new ColumnMapRowMapper());
return reader;
}
@Bean
public ItemProcessor < Map < String, Object > , Map < String, Object >> processor(ApplicationArguments args) {
String targetTableName = args.getOptionValues("outputTable").get(0);
return new MyDataItemProcessor(targetTableName);
}
@Bean
public ItemWriter < Map < String, Object >> writer(DataSource dataSource2) {
return new MyDataItemWriter(dataSource2);
}
@Bean
public Step step1(
StepBuilderFactory stepBuilderFactory,
ItemReader < Map < String, Object >> reader,
ItemProcessor < Map < String, Object > , Map < String, Object >> processor,
ItemWriter < Map < String, Object >> writer) {
return stepBuilderFactory.get("step1")
. < Map < String, Object > , Map < String, Object >> chunk(10)
.reader(reader)
.processor(processor)
.writer(writer)
.build();
}
@Bean
public Job importJob(JobBuilderFactory jobBuilderFactory, Step step1) {
return jobBuilderFactory.get("importJob")
.incrementer(new RunIdIncrementer())
.flow(step1)
.end()
.build();
}
public static void main(String[] args) {
SpringApplication.run(BatchConfiguration.class, args);
}
}
class ColumnMapRowMapper implements RowMapper < Map < String, Object >> {
@Override
public Map < String,
Object > mapRow(ResultSet resultSet, int rowNum) throws SQLException {
Map < String, Object > row = new HashMap < > ();
row.put("source_column1", resultSet.getObject("column1"));
row.put("source_column2", resultSet.getObject("column2"));
row.put("source_column3", resultSet.getObject("column3"));
// Add more columns as needed
return row;
}
}
class MyDataItemProcessor implements ItemProcessor < Map < String, Object > , Map < String, Object >> {
private final String targetTableName;
public MyDataItemProcessor(String targetTableName) {
this.targetTableName = targetTableName;
}
@Override
public Map < String,
Object > process(Map < String, Object > item) throws Exception {
Map < String, Object > mappedItem = new HashMap < > ();
// Implement your dynamic column mapping logic here
// Example: mappedItem.put("target_column1", item.get("source_column1"));
// ... Map other columns as needed
return mappedItem;
}
}
class MyDataItemWriter implements ItemWriter < Map < String, Object >> {
private final NamedParameterJdbcTemplate jdbcTemplate;
private final String targetTableName;
public MyDataItemWriter(DataSource dataSource2) {
this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource2);
// Initialize the target table name here if needed
this.targetTableName = "target_table_name";
}
@Override
public void write(List < ? extends Map < String, Object >> items) throws Exception {
// Implement your custom write logic here
// Example: for (Map<String, Object> item : items) {
// String sql = "INSERT INTO " + targetTableName + " (...) VALUES (...)";
// jdbcTemplate.update(sql, item);
// }
}
}
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
In this code:We’ve added a main method in the Application class to run the Spring Boot application.We use ApplicationArguments to retrieve command line arguments for the input SQL query text file (–sqlFile) and the output table name (–outputTable).The input SQL query is read from the specified text file and passed to the item reader.The output table name is passed to the item processor to determine the target table for the data.You can run this Spring Boot application by providing the required command line arguments when executing the JAR file. For example:
java -jar your-app.jar --sqlFile=/path/to/sql/query.sql --outputTable=target_table_name
Replace your-app.jar, /path/to/sql/query.sql, and target_table_name with your actual JAR file name, SQL query file path, and target table name.
Filed under: Java, Programming, Spring Boot | Tagged: DB1 Table to DB2 Table, JdbcCursorItemReader, JdbcCursorItemWriter, Oracle, Spring Batch, Spring Boot | Leave a comment »
The Blogger In Me 