How can I have Spring Data JPA keep the connection open without a transaction?

Question

I have a Spring Boot 3.0 application that gets a Blob from a Spring Data repository:

@Service
public class CarService {

    private final CarRepository carRepository;

    public Set<CarDto> getValuations(LocalDate date) {          
        Blob reportData = carRepository.getReportData("sedan,truck", date);
        try {
            byte[] b= reportData.getBytes(1, (int) reportData.length());
            log.debug("connection is closed by Spring Data JPA");
            //...
        } 

public interface CarRepository extends Repository<Car, Long> {
    
    @Procedure(value="pValuation", outputParameterName="reportData")
    Blob getReportData(String carTypes, LocalDate tradeDate);

Unfortunately, Spring Data closes the connection after returning the Blob from the getReportData method. This results in the following error when I try to get the bytes from the Blob:

SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed. The connection is closed.

I can keep the connection open by making getValuations transactional (i.e., annotation the method as @Transactional), but then the database hangs because of locking issues.

How can I tell Spring Data to keep the connection open without a transaction so I can retrieve bytes from the Blob?

Note: I cannot use byte[] as the return type of getReportData since the data will be truncated at 8000 bytes.

Note: My procedure, pValuation, is read only.

Update

I can call this stored procedure without using Spring Data JPA (and without a transaction) as follows:

@Service
@RequiredArgsConstructor
@Slf4j
public class CarService {
  
    private final CarRepository carRepository;
    private final EntityManager em;

    public Set<CarDto> getValuations(LocalDate date) {
        
        StoredProcedureQuery q = em.createStoredProcedureQuery("pValuation");
        q.registerStoredProcedureParameter("carTypes", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("tradeDate", LocalDate.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("reportData", Blob.class, ParameterMode.OUT);
        q.setParameter("carTypes", "sedan,truck");
        q.setParameter("tradeDate", date);
        q.execute();
        
        Blob reportData = (Blob) q.getOutputParameterValue("reportData");
        
        log.debug("got Blob");
        try {
            byte[] b= reportData.getBytes(1, (int) reportData.length());
            log.debug("got bytes");
            return carRepository.getCarValuations(b);
        } 
        catch (SQLException convertBlobToBytesException) {
                log.error(convertBlobToBytesException.toString());
        }
    }

This code works because the connection stays open while reading the bytes. From what I can tell, Spring Data closes the connection after the repository method call unless I have a transaction (which I can't use b/c the DB SP hangs).

You really should be using @Transactional and make it readonly in this case. What I find weird is that when using a byte[] the value is truncated because in the end it all ends up in e Blob any way. — M. Deinum
– M. Deinum, Commented Oct 11, 2023 at 5:20
@M.Deinum - Thanks for your comment. Unfortunately, the stored procedure on the DB will hang if I run it using @Transactional. The stored procedure was written by a 3rd party vendor and I can't change it. By the way I can successfully call the stored procedure without a transaction and without using Spring Data. I'll post this code but I would like to use Spring Data. — James
– James, Commented Oct 11, 2023 at 14:32
If you can call it with plain JDBC, what is the added benefit of adding JPA on top of that? — M. Deinum
– M. Deinum, Commented Oct 11, 2023 at 14:52
@M.Deinum - I think two benefits of using Spring Data JPA over the JPA code posted above (or plain JDBC) are 1) The code is less verbose with Spring Data JPA and will be easier to maintain. 2) The rest of my code base is using Spring Data JPA. By using Spring Data JPA for this SP, it will keep the code more consistent and easier to maintain. — James
– James, Commented Oct 11, 2023 at 15:02
Using a SimpleJdbcCall or just JdbcTemplate.call/JdbcTemplate.execute isn't anymore complex, with the benefit that it is executed within the current connection. I find it weird the the procedure hangs when it is in a transaction, seems like a flaw/fault/bug in the SP. It isn't Spring Data that is closing the connection, that is the default behavior when there is no transaction (or rather being closes at the end of the transaction which is very small here). — M. Deinum
– M. Deinum, Commented Oct 11, 2023 at 15:17

M. Deinum · Accepted Answer · 2023-10-12 05:23:36Z

Instead of using the EntityManager with the createStoredProcedureQuery you might take a look at the SimpleJdbcCall. The API is quite similar and you could define it once (as a bean or in the constructor) and re-use the definition. The added benefit is that the callback to extract the result is operating on the current connection so it stays open.

Spring Boot automatically configures a JdbcTemplate which you can re-use to create a SimpleJdbcCall.

SimpleJdbcCall sp = new SimpleJdbcCall(jdbcTemplate);
sp.declareParameters(new SqlParameter("carTypes", Types.VARCHAR),
new SqlParameter("tradeDate", Types.DATE), new SqlOutParameter("reportData", Types.BLOB, (rs, row) -> { Blob blob = rs.getBlob("reportData"); return blob.getBytes(1, (int) blob.length());})


sp.executeObject(byte[].class, "sedan,truck", date);

Something along these lines should work. Not sure if the code is totally fine as I coded it from the top of my head.

Collectives™ on Stack Overflow

How can I have Spring Data JPA keep the connection open without a transaction?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related