I'd like to use JavaFX with DB access using Spring JDBC. However I'm completely new to Spring and it seems that I cannot fully understand it's features, especially transactions handling...
I've added following dependencies to my project:
compile 'org.springframework.boot:spring-boot-starter-jdbc'
runtime 'mysql:mysql-connector-java'
... and I want to use Spring transactions handling mechanism when GUI application is doing it's operations on DB. As I understand it, following code should:
- initialize and start JavaFX application - create and show GUI wireframe
- initialize Spring
- configure and inject JdbcTemplate dependency
- start transaction handling mechanism and begin transaction
- use jdbcTemplate object to create 5 entries in DB in
for loop - simulate error (by throwing
RuntimeException) - revert operations on DB
- exit
So, summing up: when RuntimeException is thrown in method annotated as @Transactional that should revert all entries already created by this method before application quits, isn't it?
However all created entries stay permanently in DB (I can see them there after application quits). So first of all - am I understanding correctly how these transactions should work? If so, then how to make them actually work as I expect?
import javafx.application.Application;
import javafx.application.Platform;
import javafx.geometry.Insets;
import javafx.scene.Scene;
import javafx.scene.control.Label;
import javafx.scene.layout.Pane;
import javafx.scene.layout.VBox;
import javafx.stage.Stage;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@SpringBootApplication
public class SpringTransactional extends Application {
private Pane viewPane;
private ConfigurableApplicationContext springContext;
/** application.properties:
spring.datasource.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/db_name?useSSL=false&serverTimezone=UTC
spring.datasource.username = db_username
spring.datasource.password = username123
*/
@Autowired
private JdbcTemplate jdbcTemplate;
public static void main(String[] args) {
launch(args);
}
@Override
public void init() throws Exception {
springContext = SpringApplication.run(SpringTransactional.class);
springContext.getAutowireCapableBeanFactory().autowireBean(this);
}
@Override
public void stop() throws Exception {
springContext.close();
}
@Override
public void start(Stage primaryStage) {
viewPane = assembleView(primaryStage);
try {
db_transaction_test();
} catch (RuntimeException e) {
e.printStackTrace();
}
Platform.exit();
}
private Pane assembleView(Stage primaryStage) {
VBox rootPane = new VBox();
rootPane.setSpacing(10);
rootPane.setPadding(new Insets(10));
rootPane.setStyle("-fx-base: #84a7ad;");
rootPane.getChildren().add(new Label("GUI goes here."));
primaryStage.setScene(new Scene(rootPane));
primaryStage.setResizable(false);
primaryStage.show();
return rootPane;
}
@Transactional
private void db_transaction_test() {
for (int i = 0; i < 10; i++) {
try {
int entry_name = getEntryId("entry_" + i);
System.out.println("Created entry id=" + entry_name);
} catch (DaoException e) {
e.printStackTrace();
}
if (i == 5) {
throw new RuntimeException("Testing data upload procedure break.");
}
}
}
/** DB creation and schema:
CREATE DATABASE db_name;
CREATE USER db_username;
USE db_name;
GRANT ALL ON db_name.* TO db_username;
SET PASSWORD FOR spz = PASSWORD('username123');
FLUSH PRIVILEGES;
CREATE TABLE Entry (
entry_ID INT NOT NULL AUTO_INCREMENT,
name TEXT NOT NULL,
PRIMARY KEY (entry_ID)
);
*/
private int getEntryId(String entryName) throws DaoException {
List<DbEntry> dbEntries = retrieveEntriesFor(entryName);
if (dbEntries.size() == 1) {
return dbEntries.get(0).getEntry_ID();
} else if (dbEntries.size() == 0) {
String sqlInsert = "INSERT INTO Entry (name) VALUES (?)";
jdbcTemplate.update(sqlInsert, entryName);
dbEntries = retrieveEntriesFor(entryName);
if (dbEntries.size() == 1) {
return dbEntries.get(0).getEntry_ID();
} else {
throw new DaoException("Invalid results amount received after creating new (" + dbEntries.size() + ") when getting entry for name: " + entryName);
}
} else {
throw new DaoException("Invalid results amount received (" + dbEntries.size() + ") when getting entry for name: " + entryName);
}
}
private List<DbEntry> retrieveEntriesFor(String entryName) {
return jdbcTemplate.query("SELECT * FROM Entry WHERE name=?;", (ResultSet result, int rowNum) -> unMarshal(result), entryName);
}
private DbEntry unMarshal(ResultSet result) throws SQLException {
DbEntry dbEntry = new DbEntry();
dbEntry.setEntry_ID(result.getInt("entry_ID"));
dbEntry.setName(result.getString("name"));
return dbEntry;
}
public class DbEntry {
private int entry_ID;
private String name;
int getEntry_ID() { return entry_ID; }
void setEntry_ID(int entry_ID) { this.entry_ID = entry_ID; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
}
private class DaoException extends Throwable {
DaoException(String err_msg) { super(err_msg); }
}
}
After more testing it seems that creating separate Spring component
EntryDaoworks (thanks James_D) but only ifdb_transaction_testannotaded with@Transactionalis in that class - OPTION A in code below.But what I'm really interested in is OPTION B - when
db_transaction_testannotaded with@Transactionalis in another class. This is because DAO class does not (and should not) know about DB-unrealted issues that are the reason of reverting a bunch of previous DB operations. This information comes from other 'controllers' which failrues must not cause data integrity issues. So in the example belowSpringTransactionalshould be the only one that can throw this particularRuntimeException("Testing data upload procedure break.");(as an example of real-life system/environment issues). However as stacktrace at the end shows - transaction is not initalized there.So is there a way to get it work as I need with Spring
@Transactional(aka. declarative transactions) or only with manual (aka. programmatic) Spring transactions control? And if this is the only way then how to configureDataSourceTransactionManagerwhile using@SpringBootApplicationfor "auto-configuration" and@AutowiredforjdbcTemplateobject?Main class:
EntryDao class:
STACKTRACE
SOLUTION:
Best solution I've found so far is using Spring
TransactionTemplatetogether with additional callback class:and in
SpringTransactionalclassdb_transaction_test()method (note that@Transactionalis out):EntryDaoclass requires this additional code: