No results in set for executeQuery() in Oracle

Issue:
executeQuery() returns no rows. Oracle SQL developer shows the rows persist into database.

Cause / error / solution:

Getting last element in Hibernate's repository by annotations

Getting last element in hibernate is useful for integration testing, where you have to ensure some object is stored in database, for example.

Using Hibernate SQL.

Code:
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.jpa.repository.Query;

public interface MyObjectRepository extends CrudRepository<MyObject, Long> {
 
  @Query("FROM com.yourcompany.zzz.MyObject c WHERE (c.id) IN (select max(id) from com.yourcompany.zzz.MyObject)")
  MyObject getLastOne();   
 
}


It returns null, when there are no elements in table (do not throw an exception).

Optimizing queries with EXPLAIN

The explain command gives information about indexes which are used for the query, so you could check, whether it is optimized as you expected.

Modify primary key syntax

MySQL:

ALTER TABLE your_table DROP PRIMARY KEY, ADD PRIMARY KEY ( some_field, other_field );


There is no problem some_field to be AUTO_INCREMENT.