I have a problem - I cannot insert data in the table with Spring JDBC.
Don't know is it important, just in case - I have an app that uses Spring/SpringBoot with Spring JPA and other things. The database is Postgresql and everything works fine there.
The problem is, that in the same app I need to connect to another database, which is Firebird. I am trying to insert values to some table in this Firebird database using Spring JDBC and looks like the changes ain't committed.
I've created a simple table for tests:
CREATE TABLE table_master
(
id INTEGER NOT NULL,
name VARCHAR(200),
CONSTRAINT t_master_pk PRIMARY KEY (id)
);
And I tried both approaches taken from the Spring documentation, like this:
@SpringBootTest
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
@Slf4j
class JdbcTest {
private DataSource fbDataSource;
@Autowired
private PlatformTransactionManager transactionManager;
@BeforeEach
public void setUp() {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:firebirdsql://localhost:3055//opt/fb/db/DB.GDB");
config.setUsername("user");
config.setPassword("passwd");
config.setAutoCommit(false);
fbDataSource = new HikariDataSource(config);
}
private static final String insertQuery = "INSERT INTO table_master(id, name) VALUES(?, ?)";
private static final String cntQuery = "SELECT COUNT(*) FROM table_master";
@Test
public void testFbLoad() throws SQLException {
JdbcTemplate fbJdbcTemplate = new JdbcTemplate(fbDataSource);
DefaultTransactionDefinition def = new DefaultTransactionDefinition();
def.setPropagationBehavior(DefaultTransactionDefinition.PROPAGATION_REQUIRES_NEW);
def.setIsolationLevel(DefaultTransactionDefinition.ISOLATION_READ_COMMITTED);
TransactionStatus status = transactionManager.getTransaction(def);
for (int i = 0; i < 100; i++) {
if (fbJdbcTemplate.update(insertQuery, i, "Row #" + i) == 0) {
throw new RuntimeException("Cannot insert row #" + i);
}
}
transactionManager.commit(status);
Integer cnt = fbJdbcTemplate.queryForObject(cntQuery, Integer.class);
log.info("Rows in table_master: {}", cnt);
}
and this:
@Test
public void testFbLoad() throws SQLException {
JdbcTemplate fbJdbcTemplate = new JdbcTemplate(fbDataSource);
TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
transactionTemplate.execute(status ->
{
try {
for (int i = 0; i < 100; i++) {
if (fbJdbcTemplate.update(insertQuery, i, "Row #" + i) == 0) {
throw new RuntimeException("Cannot insert row #" + i);
}
}
} catch (Exception e) {
log.error("Rolling back: {}", e.getMessage(), e);
status.setRollbackOnly();
}
return null;
}
);
Integer cnt = fbJdbcTemplate.queryForObject(cntQuery, Integer.class);
log.info("Rows in table_master: {}", cnt);
}
And in both cases I observe that JdbcTemplate#update calls are executed but on completion I see no data in the table and "Rows in table_master: 0" in the log with no errors.
I have no idea what am I doing wrong. Please help, how should I use Spring JDBC for such a tasks?
Update:
Well, I tried @Transactional and it also gives me a strange result:
@Test
@Transactional
public void testFbLoad() throws SQLException {
JdbcTemplate fbJdbcTemplate = new JdbcTemplate(fbDataSource);
TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
transactionTemplate.execute(status ->
{
try {
for (int i = 0; i < 100; i++) {
if (fbJdbcTemplate.update(insertQuery, i, "Row #" + i) == 0) {
throw new RuntimeException("Cannot insert row #" + i);
}
}
status.setRollbackOnly();
} catch (Exception e) {
log.error("Rolling back: {}", e.getMessage(), e);
status.setRollbackOnly();
}
return null;
}
);
Integer cnt = fbJdbcTemplate.queryForObject(cntQuery, Integer.class);
log.info("Rows in table_master: {}", cnt);
}
In this case all inserts made are rolled back, but I observe "Rows in table_master: 100"
Please help. Thank you!
Update 2:
looks like I found the reason. Here's the log:
2024-11-30T04:00:50.693+02:00 DEBUG 15770 --- [ Test worker] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL update
2024-11-30T04:00:50.693+02:00 DEBUG 15770 --- [ Test worker] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [INSERT INTO table_master(id, name) VALUES(?, ?)]
2024-11-30T04:00:50.693+02:00 DEBUG 15770 --- [ Test worker] o.s.jdbc.datasource.DataSourceUtils : Fetching JDBC Connection from DataSource
2024-11-30T04:00:50.701+02:00 DEBUG 15770 --- [ Test worker] o.f.gds.ng.wire.AbstractWireOperations : readStatusVector arg:isc_arg_gds int: 0
2024-11-30T04:00:50.711+02:00 DEBUG 15770 --- [ Test worker] o.f.gds.ng.wire.AbstractWireOperations : readStatusVector arg:isc_arg_gds int: 0
2024-11-30T04:00:50.711+02:00 DEBUG 15770 --- [ Test worker] o.f.gds.ng.wire.AbstractWireOperations : readStatusVector arg:isc_arg_gds int: 0
2024-11-30T04:00:50.724+02:00 DEBUG 15770 --- [ Test worker] o.f.gds.ng.wire.AbstractWireOperations : readStatusVector arg:isc_arg_gds int: 0
2024-11-30T04:00:50.726+02:00 DEBUG 15770 --- [ Test worker] o.f.gds.ng.wire.AbstractWireOperations : readStatusVector arg:isc_arg_gds int: 0
2024-11-30T04:00:50.728+02:00 DEBUG 15770 --- [ Test worker] o.f.jaybird.xca.FBManagedConnection : End called: Xid[1219176952]
2024-11-30T04:00:50.731+02:00 DEBUG 15770 --- [ Test worker] o.f.gds.ng.wire.AbstractWireOperations : readStatusVector arg:isc_arg_gds int: 0
2024-11-30T04:00:50.731+02:00 DEBUG 15770 --- [ Test worker] o.f.gds.ng.wire.AbstractWireOperations : readStatusVector arg:isc_arg_gds int: 0
2024-11-30T04:00:50.731+02:00 DEBUG 15770 --- [ Test worker] com.zaxxer.hikari.pool.ProxyConnection : HikariPool-1 - Executed rollback on connection org.firebirdsql.jdbc.FBConnection@5f5fe604 due to dirty commit state on close().
2024-11-30T04:00:50.731+02:00 DEBUG 15770 --- [ Test worker] o.s.orm.jpa.JpaTransactionManager : Initiating transaction commit
2024-11-30T04:00:50.732+02:00 DEBUG 15770 --- [ Test worker] o.s.orm.jpa.JpaTransactionManager : Committing JPA transaction on EntityManager [SessionImpl(1821322830<open>)]
2024-11-30T04:00:50.732+02:00 DEBUG 15770 --- [ Test worker] o.h.e.t.internal.TransactionImpl : committing
2024-11-30T04:00:50.733+02:00 DEBUG 15770 --- [ Test worker] o.s.orm.jpa.JpaTransactionManager : Closing JPA EntityManager [SessionImpl(1821322830<open>)] after transaction
So, JdbcTemplate closes connection after update and Hikari rolls it back then. The question is still the same - what am I doing wrong and how to fix it? Thank you!
Update3:
Just in case I've filed a issue report here, but maybe it's my fault and there's a solution. Please help.
The problem is your test as it doesn't really make sense.
DataSource outside the scope of SpringPlatformTransactionManager that is tied to a different DataSource.Due to this there is no dedicated PlatformTransactionManager for your own embedded DataSource. If you would rewrite your test to the following
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
@Slf4j
class JdbcTest {
private DataSource fbDataSource;
private PlatformTransactionManager transactionManager;
@BeforeEach
public void setUp() {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:firebirdsql://localhost:3055//opt/fb/db/DB.GDB");
config.setUsername("user");
config.setPassword("passwd");
config.setAutoCommit(false);
fbDataSource = new HikariDataSource(config);
this.transactionManager = new DataSourceTransactionManager(fbDataSource);
}
private static final String insertQuery = "INSERT INTO table_master(id, name) VALUES(?, ?)";
private static final String cntQuery = "SELECT COUNT(*) FROM table_master";
@Test
public void testFbLoad() throws SQLException {
JdbcTemplate fbJdbcTemplate = new JdbcTemplate(fbDataSource);
DefaultTransactionDefinition def = new DefaultTransactionDefinition();
def.setPropagationBehavior(DefaultTransactionDefinition.PROPAGATION_REQUIRES_NEW);
def.setIsolationLevel(DefaultTransactionDefinition.ISOLATION_READ_COMMITTED);
TransactionStatus status = transactionManager.getTransaction(def);
for (int i = 0; i < 100; i++) {
if (fbJdbcTemplate.update(insertQuery, i, "Row #" + i) == 0) {
throw new RuntimeException("Cannot insert row #" + i);
}
}
transactionManager.commit(status);
Integer cnt = fbJdbcTemplate.queryForObject(cntQuery, Integer.class);
log.info("Rows in table_master: {}", cnt);
}
}
This will work as you now have a PlatformTransactionManager for the specific transactional resource, in this case your DataSource.
If you define an additional DataSource and are not using JTA then you also need an additional transaction manager.
@Configuration
public class MyFirebaseConfig {
@Bean
public DataSource firebaseDataSource() { ... }
@Bean
public DataSourceTransactionManager firebaseTransactionManager(DataSource firebaseDataSource) {
return new DataSourceTransactionManager(firebaseDataSource);
}
// Other beans like JdbcTemplate etc.
}
Now if you have a service that requires this you can specify the name of the transaction manager to use in the @Transactional.
@Service
public class SomeService {
private final JdbcTemplate jdbc;
public SomeService(@Qualifier("firebaseJdbcTemplate") JdbcTemplate jdbc) {
this.jdbc=jdbc;
}
@Transactional("firebaseTransactionManager")
public void foobar() { ... }
}
To inject the specific DataSource or JdbcTemplate you can use an @Qualifier.