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).

Answer 1

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.