Spring Boot Query Annotation with Nativequery Doesn't Work in Postgresql

Spring Boot Query annotation with nativeQuery doesn't work in Postgresql

Ok, I spend really long time trying to make this work for me, BUT I FINALLY GOT IT. Let the future adventurers and community know it as well.

What worked for me was casting to interval presented by A.H. here:

select current_timestamp + ( 2 || ' days')::interval;

Your solution would be then something like that (when 2nd query is analyzed):

@Query(value = "SELECT n.* from nodes n WHERE n.node_id = 10510 AND n.last_good_ping > CURRENT_DATE - ( :interval )\\:\\:interval ", nativeQuery = true)

Or for 3rd query (which is exactly how I made it work in my case)

@Query(value = "SELECT n.* from nodes n WHERE n.node_id = 10510 AND n.last_good_ping > CURRENT_DATE - ( ?1 )\\:\\:interval", nativeQuery = true)

Please notice escaping :: becomes \\:\\:. In your pure SQL which you would test in your SQL editor use ::, but in your repository code within @Query these casting signs needs to be escaped (otherwise your Hibernate/JPA will be displeased).

Spring Boot PostgreSQL JPA @Query doesn't seem to be working

Exceptions says that: Validation failed for query for method public abstract java.util.List com.limsr.testproject.persistence.BoardRepository.queryAnnotationTest1(java.lang.String)!, so the error is in query syntax.

I think the error is in the name of the entity. Try to remove study from study.Board.
So the query will be like SELECT b FROM Board b WHERE b.title like %?1% ORDER BY b.seq DESC

Based on https://www.objectdb.com/java/jpa/entity/types#Entity_Classes_

Entity classes are represented in queries by entity names. By default, the entity name is the unqualified name of the entity class (i.e. the short class name excluding the package name). A different entity name can be set explicitly by using the name attribute of the Entity annotation.Entity names must be unique. When two entity classes in different packages share the same class name, explicit entity name setting is required to avoid collision.

Or, if you have Board as table, not as entity name, and you query is native (Not JPQL), you can use @Query("SELECT b FROM study.Board b WHERE b.title like %?1% ORDER BY b.seq DESC", nativeQuery = true)

Postgres Interval not working with native spring data JPA query

You can't provide a value for an interval like that. You need to multiple the parameter value with your interval base unit:

"select * from orders 
where created_date  < clock_timestamp() - (interval '1' day) * :days"

As you are dealing with days, you can simplify that to:

"select * from orders 
where created_date  < clock_timestamp() - :days"

Another option is the make_interval() function. You can pass multiple parameters for different units.

"select * from orders 
where created_date  < clock_timestamp() - make_interval(days => :days)"

The notation days => ... is a named parameter for a function call. If the variable represents hours, you could use make_interval(hours => ..)

Spring data jpa native query with null parameter (PostgreSQL)

This is a common problem with Hibernate + PostgreSQL, and the solution is to implement that method yourself, instead of having Spring do it for you. Within the implementation you have to do something like this

List<CustomProjection> findByCustomCondition(
        @Param("identifierId") Long identifierId,
        @Param("eventTypes") List<String> eventTypes) {
     // entityManager is acquired with the @PersistenceContext annotation as an injectable
     Query q = entityManager.createNativeQuery(..., CustomProjection.class);
     // the important part:
     q.setParameter("identifierId", 0L);
     q.setParameter("identifierId", identifierId);
     ...

First call to setParameter ensures Hibenate uses the correct type setter, second call overrides the first value without having executed the query, and skips type detection.

Repositories with native queries fail in test environment - postgres, jpa, spring

You are doing manual configuration instead of using the runtime configuration. Hence different treatment of naming strategies. Instead you should be reusing the same configuration instead of writing your own.

Either use an @SpringBootTest or @DataJpaTest and only re-configure the DataSource.

Do something with an ApplicationContextInitializer to get the JDBC properties into the ApplicationContext.

@RunWith(SpringRunner.class)
@SpringBootTest
@ContextConfiguration(initializers = {TestPostgresql.JdbcInitializer.class})
public class TestPostgresql {

    @ClassRule
    public static PostgreSQLContainer postgreSQLContainer = PostgresDbContainer.getInstance();

    /**
     * ************ REPOSITORIES ************
     */
    @Autowired
    NativeQueryRepository nativeQueryRepository;

    static class JdbcInitializer
      implements ApplicationContextInitializer<ConfigurableApplicationContext> {
        public void initialize(ConfigurableApplicationContext configurableApplicationContext) {
            TestPropertyValues.of(
              "spring.datasource.url=" + postgreSQLContainer.getJdbcUrl(),
              "spring.datasource.username=" + postgreSQLContainer.getUsername(),
              "spring.datasource.password=" + postgreSQLContainer.getPassword()
            ).applyTo(configurableApplicationContext.getEnvironment());
        }
    }    
}

This will reuse the configuration from the runtime in your test. Instead of @SpringBootTest you should als be able to use @DataJpaTest(NativeQueryRepository.class) to make a sliced test for JPA only.

Spring data query not working properly, because the field is having Or in the name. Field name is - approvedOrRejectedBy

The query by method-name will interpret certain keywords from the name:

• Subject Keywords like here findAllBy
• Predicate Keywords like here And, Or, Between together with the property names

Since your field/property seems named as approvedOrRejectedBy you could work around by naming the object property differently and annotate it with the mapped DB-column name as given, e.g. @Column(name "ApprovedOrRejectedBy").

@Column(name "approvedOrRejectedBy")
String reviewedBy;

Then your query-method can be rewritten as

List<Object> findAllByTimeStampBetweenAndCameraSlugInAndReviewedBy(long startTime, long endTime, List<String> cameraSlugs, String id);

Alternatively, since the method name becomes hard to read, you could shorten the name and specify the SELECT on @Query annotation like this:

@Query("SELECT * FROM entityOrTable x WHERE x.timeStamp BETWEEN ?1 AND ?2 AND x.cameraSlug IN ?3 AND x.approvedOrRejectedBy = ?4")
List<Object> findByIntervalCameraSlugInAndReviewedBy(long startTime, long endTime, List<String> cameraSlugs, String id);

See also:

• Spring-Data-Jpa Repository - Underscore on Entity Column Name
• Spring Data JPA repository methods don't recognize property names with underscores

---

Related Topics