This is a Spring Boot application that provides a REST API endpoint to transfer data from a source table to a destination table in a database. The source table, destination table, and columns to be transferred can be configured through a properties file.
- Java 11 or higher
- Maven
The application properties can be configured in the application.properties file. Here's an example:
# Server configuration
server.port=8080
# Datasource configuration
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
# H2 console configuration
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
# Application configuration
app.source.table=source_table
app.source.columns=column1,column2,column3
app.destination.table=destination_table
# Logging configuration
logging.level.root=INFO
logging.level.com.example.datatransfer=DEBUG
logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - %msg%n
logging.file.name=logs/data-transfer.log
logging.file.max-size=10MB
logging.file.max-history=7<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.9</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>data-transfer</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>data-transfer</name>
<description>Data Transfer Service</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}@Component
@ConfigurationProperties(prefix = "app")
class DataTransferProperties {
private String sourceTable;
private List<String> sourceColumns;
private String destinationTable;
// Getters and setters
public String getSourceTable() {
return sourceTable;
}
public void setSourceTable(String sourceTable) {
this.sourceTable = sourceTable;
}
public List<String> getSourceColumns() {
return sourceColumns;
}
public void setSourceColumns(List<String> sourceColumns) {
this.sourceColumns = sourceColumns;
}
public String getDestinationTable() {
return destinationTable;
}
public void setDestinationTable(String destinationTable) {
this.destinationTable = destinationTable;
}
}public class DataTransferException extends RuntimeException {
public DataTransferException(String message) {
super(message);
}
public DataTransferException(String message, Throwable cause) {
super(message, cause);
}
}@Service
class DataTransferService {
private final JdbcTemplate jdbcTemplate;
private final DataTransferProperties properties;
public DataTransferService(JdbcTemplate jdbcTemplate, DataTransferProperties properties) {
this.jdbcTemplate = jdbcTemplate;
this.properties = properties;
}
public void transferData() {
deleteDataFromDestinationTable();
List<Object[]> rows = selectDataFromSourceTable();
insertDataIntoDestinationTable(rows);
}
private void deleteDataFromDestinationTable() {
String destinationTable = properties.getDestinationTable();
try {
jdbcTemplate.update("DELETE FROM " + destinationTable);
} catch (Exception e) {
throw new DataTransferException("Failed to delete data from destination table", e);
}
}
private List<Object[]> selectDataFromSourceTable() {
String sourceTable = properties.getSourceTable();
List<String> sourceColumns = properties.getSourceColumns();
String selectQuery = "SELECT " + String.join(",", sourceColumns) + " FROM " + sourceTable;
try {
return jdbcTemplate.query(selectQuery, (rs, rowNum) -> {
Object[] row = new Object[sourceColumns.size()];
for (int i = 0; i < sourceColumns.size(); i++) {
row[i] = rs.getObject(i + 1);
}
return row;
});
} catch (Exception e) {
throw new DataTransferException("Failed to fetch data from source table", e);
}
}
private void insertDataIntoDestinationTable(List<Object[]> rows) {
String destinationTable = properties.getDestinationTable();
List<String> sourceColumns = properties.getSourceColumns();
String columnList = sourceColumns.stream().collect(Collectors.joining(","));
String insertQuery = "INSERT INTO " + destinationTable + "(" + columnList + ") VALUES (" + String.join(",", Collections.nCopies(sourceColumns.size(), "?")) + ")";
try {
jdbcTemplate.batchUpdate(insertQuery, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Object[] row = rows.get(i);
for (int j = 0; j < row.length; j++) {
ps.setObject(j + 1, row[j]);
}
}
@Override
public int getBatchSize() {
return rows.size();
}
});
} catch (Exception e) {
throw new DataTransferException("Failed to insert data into destination table", e);
}
}
}@RestController
class DataTransferController {
private final DataTransferService dataTransferService;
public DataTransferController(DataTransferService dataTransferService) {
this.dataTransferService = dataTransferService;
}
@GetMapping("/transfer-data")
public ResponseEntity<String> transferData() {
try {
dataTransferService.transferData();
return ResponseEntity.ok("Data transfer successful");
} catch (DataTransferException e) {
return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(e.getMessage());
}
}
}