CODEWITHSUNDEEP
javaspringpostgresqljdbc
Samantha Catania
Samantha Catania

Reputation: 5316

JDBC Auto-Commit Not Working with PostgreSQL 9 Driver

I have a spring application with a PostgreSQL DataSource loaded from JDNI:

<jee:jndi-lookup id="dataSource" jndi-name="jdbc/DB" expected-type="javax.sql.DataSource" />

I hooked it up with a JDBCTemplate 

// DataSource is Autowired
jdbcTemplate = new JdbcTemplate(dataSource);

and auto-commit is set to true

<Resource name="jdbc/DB" auth="Container" type="javax.sql.DataSource"
          username="postgres" password="localPostgres"
          url="jdbc:postgresql://localhost:5432/postgres"
          driverClassName="org.postgresql.Driver"
          defaultAutoCommit="true"/>

When I try to insert an item with a simple SQL insert statement the changes are not committed to the database. 

jdbcTemplate.update("INSERT INTO items (name, surname) VALUES ('samantha', 'catania')";

Entering the SQL statement directly in the database console the item gets inserted i.e. the SQL is correct.

Any ideas what could be the issue please?

Upvotes: 1

Views: 2588

Answers (1)

Samantha Catania
Samantha Catania

Reputation: 5316

This problem was happening because I am using Spring Batch and my commits where being done within the transaction created by Spring Batch. Adding @Transactional(Transactional.TxType.REQUIRES_NEW) to my method fixes the issues. This article describes common pitfalls when using @Transactional which helped me find a solution for my problem.

Upvotes: 1

Related Questions