What should LIMIT have for attributes in Spring JPA? SQL

Question

I'm trying to have limits with nativeQuery = true but Spring JPA can't find the query for this.

My error is:

Caused by: java.lang.IllegalStateException: Using named parameters for method public abstract void se.danielmartensson.repositories.DataRepository.deleteByJobNameOrderByDateTimeLimit(java.lang.String,long,long) but parameter 'Optional[jobName]' not found in annotated query 'DELETE FROM Data data ORDER BY data.dateTime WHERE data.jobName =: jobName LIMIT firstIndex = :firstIndex, selectedSamples = :selectedSamples'!

So I'm guessing that LIMIT should have different attributes, rather than firstIndex and selectedSamples ? What can it be then?

Where is my query from my Repository in Spring Boot

@Query(value = "SELECT * FROM Data data ORDER BY data.dateTime WHERE data.jobName = :jobName LIMIT firstIndex = :firstIndex, selectedSamples = :selectedSamples", nativeQuery = true)
List<Data> findByJobNameOrderByDateTimeLimit(@Param("jobName") String jobName, @Param("firstIndex") long firstIndex, @Param("selectedSamples") long selectedSamples);

@Modifying
@Query(value = "DELETE FROM Data data ORDER BY data.dateTime WHERE data.jobName =: jobName LIMIT firstIndex = :firstIndex, selectedSamples = :selectedSamples", nativeQuery = true)
void deleteByJobNameOrderByDateTimeLimit(@Param("jobName") String jobName, @Param("firstIndex") long firstIndex, @Param("selectedSamples") long selectedSamples);

Answer 1

If you just want to get the row which jobName and firstIndex and selectedSamples match the parameters. you should use correct SQL syntax and put them in where.

SELECT * FROM Data data
WHERE data.jobName = :jobName AND firstIndex = :firstIndex AND selectedSamples = :selectedSamples
ORDER BY data.dateTime

the correct syntax is like this :

select *
from [table]
where 'condiction'
order by [column]
limit [int]

---

LIMIT however is used to set the max tuples SQL return, for example if the query return 10K rows but you only want to look at first 5 rows, you can use limit 5 to tell SQL only return first 5 fetch.

Also in different DBMS there might need to use different syntax or method to achieve the same thing, like in ORACLE we got no limit clause, instead we got FETCH or simply using ROWNUM.

Answer 2

I have created a sample project that uses H2. You must change limit and offset part in which database you are using. For the delete operation, there is delete from table where id in (select id from table where .. order by .. limit .. offset ..) is defined.

Note: Lombok is used for getter, setter and toString. It is not required.

DataRepository.java

public interface DataRepository extends JpaRepository<Data, Integer> {
    @Query(value = "SELECT * FROM Data data WHERE data.jobName = :jobName ORDER BY data.dateTime limit :selectedSamples offset :firstIndex"
            , nativeQuery = true)
    List<Data>findByJobNameOrderByDateTimeLimit(@Param("jobName") String jobName, @Param("firstIndex") Integer firstIndex, @Param("selectedSamples") Integer selectedSamples);

    @Transactional
    @Modifying
    @Query(value = "DELETE FROM Data data WHERE data.id in (select id from Data d where d.jobName = :jobName order by d.dateTime limit :selectedSamples offset :firstIndex)"
            , nativeQuery = true)
    void deleteByJobNameOrderByDateTime(@Param("jobName") String jobName, @Param("firstIndex") Integer firstIndex, @Param("selectedSamples") Integer selectedSamples);
}

Data.java

@lombok.Data
@Entity
@Table
public class Data {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "jobName")
    private String jobName;

    @Column(name = "dateTime")
    private LocalDateTime dateTime;

    public Data() {
    }

    public Data(String jobName, LocalDateTime dateTime) {
        this.jobName = jobName;
        this.dateTime = dateTime;
    }
}

Test

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDateTime;
import java.util.List;

@SpringBootTest
class DemoApplicationTests {
    @Autowired
    private DataRepository dataRepository;
    
    @Test
    void test1() {
        // insert dummy records
        dataRepository.save(new Data("job1", LocalDateTime.now().minusMinutes(1)));
        dataRepository.save(new Data("job1", LocalDateTime.now().minusMinutes(2)));
        dataRepository.save(new Data("job1", LocalDateTime.now().minusMinutes(3)));
        dataRepository.save(new Data("job1", LocalDateTime.now().minusMinutes(4)));

        // get records
        List<Data> dataList = dataRepository.findByJobNameOrderByDateTimeLimit("job1", 0, 4);
        for (Data data : dataList) {
            System.out.println(data);
        }

        // delete
        dataRepository.deleteByJobNameOrderByDateTime("job1", 1, 2);

        // get records
        dataList = dataRepository.findByJobNameOrderByDateTimeLimit("job1", 0, 4);
        for (Data data : dataList) {
            System.out.println(data);
        }
    }
}

Output

Hibernate: SELECT * FROM Data data WHERE data.jobName = ? ORDER BY data.dateTime limit ? offset ?

Data(id=4, jobName=job1, dateTime=2021-01-08T05:25:31.830)
Data(id=3, jobName=job1, dateTime=2021-01-08T05:26:31.829)
Data(id=2, jobName=job1, dateTime=2021-01-08T05:27:31.827)
Data(id=1, jobName=job1, dateTime=2021-01-08T05:28:31.756)

Hibernate: DELETE FROM Data data WHERE data.id in (select id from Data d where d.jobName = ? order by d.dateTime limit ? offset ?)

Hibernate: SELECT * FROM Data data WHERE data.jobName = ? ORDER BY data.dateTime limit ? offset ?

Data(id=4, jobName=job1, dateTime=2021-01-08T05:25:31.830)
Data(id=1, jobName=job1, dateTime=2021-01-08T05:28:31.756)

Mysql

For mysql delete operation is successful with below script.

Reference: MySQL DELETE FROM with subquery as condition - Answer

    @Transactional
    @Modifying
    @Query(value = "DELETE FROM Data WHERE id in (select id from (select id from Data where jobName = :jobName order by dateTime limit :selectedSamples offset :firstIndex) x)"
            , nativeQuery = true)
    void deleteByJobNameOrderByDateTime(@Param("jobName") String jobName, @Param("firstIndex") Integer firstIndex, @Param("selectedSamples") Integer selectedSamples);