I'm writing java console app using spring data jpa and mysql and I'm trying to solve the following situation:
App generates new object every second which should be saved to db at the same moment in the same order. If db connection will be lost or timeout exception will be invoked during object saving a long time then save upcoming objects to temporary buffer. When connection will be recovered, save all these accumulated objects and new upcoming generated object(in that particular moment) to db.
My questions are:
- How I can handle to save object in temp buffer when db connection lost?
I guess I should caught Throwable using ScheduledExecutorService when db connection lost and then save the particular object to CopyOnWriteArrayList, is it correct way?
- How I can stop saving new objects to db when connection lost ot timeout exception was invoked by previous object saving and resume process of saving upcoming objects when connection is up?
- How I can save all accumulated objects to db when connection is up before saving new coming generated objects?
UPDATE
I wrote the service which runs objects generation with above mentioned behavior:
Service
@Service
public class ReportService implements IReportService {
@Autowired
private ReportRepository reportRepository;
@Override
public void generateTimestamps() {
BlockingQueue<Report> queue = new LinkedBlockingQueue<>();
new Thread(new ReportsProducer(queue)).start();
new Thread(new ReportsConsumer(queue, reportRepository)).start();
}
@Override
public List<Report> showTimestamps() {
return reportRepository.findAll();
}
}
Object Producer:
public class ReportsProducer implements Runnable {
private final BlockingQueue<Report> reportsQueue;
ReportsProducer(BlockingQueue<Report> numbersQueue) {
this.reportsQueue = numbersQueue;
}
public void run() {
try {
while (true) {
generateReportEverySecond();
}
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
}
}
private void generateReportEverySecond() throws InterruptedException {
Thread.sleep(1000);
Report report = new Report();
reportsQueue.put(report);
System.out.println(Thread.currentThread().getName() + ": Generated report[id='" + report.getId() + "', '" + report
.getTimestamp() + "']");
}
}
Object consumer:
public class ReportsConsumer implements Runnable {
private final BlockingQueue<Report> queue;
private ReportRepository reportRepository;
ReportsConsumer(BlockingQueue<Report> queue, ReportRepository reportRepository) {
this.queue = queue;
// Not sure i do this correct way
this.reportRepository = reportRepository;
}
public void run() {
while (true) {
try {
if (!queue.isEmpty()) {
System.out.println("Consumer queue size: " + queue.size());
Report report = reportRepository.save(queue.peek());
queue.poll();
System.out.println(Thread.currentThread().getName() + ": Saved report[id='" + report.getId() + "', '" + report
.getTimestamp() + "']");
}
} catch (Exception e) {
// Mechanism to reconnect to DB every 5 seconds
try {
System.out.println("Retry connection to db");
Thread.sleep(5000);
} catch (InterruptedException e1) {
e1.printStackTrace();
}
}
}
}
}
Repository:
@Repository
public interface ReportRepository extends JpaRepository<Report, Long> {
}
Object:
@Entity
@Table(name = "reports")
public class Report {
@Id
@GeneratedValue
private Long id;
@Column
private Timestamp timestamp;
public Report() {
this.timestamp = new Timestamp(new Date().getTime());
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Timestamp getTimestamp() {
return timestamp;
}
public void setTimestamp(Timestamp timestamp) {
this.timestamp = timestamp;
}
}
Application.properties:
spring.datasource.url=jdbc:mysql://xyz:3306/xyz
spring.datasource.username=xyz
spring.datasource.password=xyz
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
spring.jpa.properties.hibernate.connection.driver_class=com.mysql.cj.jdbc.Driver
spring.jpa.properties.hibernate.ddl-auto = create-drop
# Below properties don't work actually
spring.jpa.properties.javax.persistence.query.timeout=1
# Reconnect every 5 seconds
spring.datasource.tomcat.test-while-idle=true
spring.datasource.tomcat.time-between-eviction-runs-millis=5000
spring.datasource.tomcat.validation-query=SELECT 1
build.gradle:
version '1.0'
buildscript {
ext {
springBootVersion = '1.5.9.RELEASE'
}
repositories {
mavenCentral()
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
}
}
apply plugin: 'java'
apply plugin: 'org.springframework.boot'
sourceCompatibility = 1.8
targetCompatibility = 1.8
ext {
mysqlVersion = '6.0.6'
dbcp2Version = '2.2.0'
hibernateVersion = '5.2.12.Final'
}
repositories {
mavenCentral()
}
dependencies {
compile group: 'mysql', name: 'mysql-connector-java', version: mysqlVersion
compile group: 'org.hibernate', name: 'hibernate-core', version: hibernateVersion
compile group: 'org.hibernate', name: 'hibernate-c3p0', version: hibernateVersion
compile("org.springframework.boot:spring-boot-starter-data-jpa")
testCompile("org.springframework.boot:spring-boot-starter-test")
compile group: 'org.assertj', name: 'assertj-core', version: '3.9.0'
}
Based on above code,I would like to know some moments:
How you recommend to check db connectivity?
I use save operation to check, if db connection lost I just wait 5 sec and repeat the operation. Moreover, datasource is configured to stand up db connection every 5 sec if it's down. Is there more correct way to do it?
What will happen if db connection is active but db is very slow or too busy(ovberloaded) at the moment?
As I understand I need to set timeout for query. What else I should undertake in this scenario?
