JPQL时间比较查询失效：PostgreSQL可用但JPQL报语法错误

Let's break down why your JPQL query is failing and fix it with two solid solutions:

Key Issues in Your Original JPQL #

1. PostgreSQL-specific ::time syntax isn't supported in JPQL: JPQL is a database-agnostic query language, so it doesn't recognize PostgreSQL's shorthand type cast operator ::.
2. limit isn't a valid JPQL keyword: Unlike native SQL, JPQL uses the setMaxResults() method to restrict result counts instead of the limit clause.
3. Entity name mismatch: JPQL references Java entity classes (not database table names). If your entity class follows standard Java naming conventions (e.g., StockTechnicalDataEntity), using the lowercase table name stocktechnicaldataentity will cause resolution issues.

Solution 1: Use JPQL-compatible Type Casting with FUNCTION() #

Replace the PostgreSQL ::time cast with JPQL's FUNCTION() to call PostgreSQL's cast function, and use setMaxResults() instead of limit:

// Use your actual Java entity class name (e.g., StockTechnicalDataEntity)
String jpql = "SELECT s.tickTime FROM StockTechnicalDataEntity s " +
              "WHERE FUNCTION('cast', s.tickTime, 'time') > '09:30:00.000' " +
              "AND FUNCTION('cast', s.tickTime, 'time') < '15:00:00.000'";

Query query = entityManager.createQuery(jpql);
query.setMaxResults(50); // Replace "limit 50" with this JPA-standard method

// Adjust the result type to match your tickTime field's actual type (e.g., LocalDateTime)
List<LocalDateTime> results = query.getResultList();

Solution 2: Use Native SQL Query (Simpler for PostgreSQL-specific Logic) #

If you prefer keeping your original PostgreSQL syntax intact, use createNativeQuery() instead of createQuery() to execute native SQL directly:

String nativeSql = "select ticktime from stocktechnicaldataentity where ticktime::time>'09:30:00.000' AND ticktime::time<'15:00:00.000' limit 50";

// Create a native SQL query instead of JPQL
Query query = entityManager.createNativeQuery(nativeSql);

// If you want to map results directly to your entity's field type, specify it:
// List<LocalDateTime> results = query.getResultList();
List<Object[]> results = query.getResultList();

Why This Works #

• FUNCTION() in JPQL: This lets you call database-specific functions (like PostgreSQL's cast) while staying within JPQL's standards, keeping your query somewhat portable across databases.
• setMaxResults(): This is the JPA-standard way to limit returned results, compatible with all JPA providers including EclipseLink.
• Native SQL: Bypasses JPQL entirely, allowing you to use PostgreSQL's native syntax directly for queries that rely heavily on database-specific features.

内容的提问来源于stack exchange，提问作者brij