Hibernate中HQL子查询编写遇QuerySyntaxException异常求助

Let's break down why you're hitting that QuerySyntaxException and how to fix it, based on your code snippet:

First, here's your code for reference:

public List findTempSensorObjs(String systemId, Character isLatest) {
Map<String,Object> params = new HashMap<String,Object>();
List tSensorList = new ArrayList();
params.put("systemId", systemId);
params.put("status", isLatest);
String sql = "select * from " + "(select tsensor.time, tsensor.tId from URPTempSensor tsensor wh...";
}

Common Issues & Fixes #

1. HQL doesn't allow select * on subquery results
   HQL is object-oriented, so you can't use raw select * when querying against a subquery. Instead, you should select the entity itself or map the subquery fields to your entity properly. If you're trying to fetch full URPTempSensor objects, select the entity directly in the subquery instead of individual fields.

2. Incomplete subquery syntax
   Your code cuts off at wh...—I assume that's an unfinished where clause. Missing conditions will immediately trigger a syntax error. Make sure to complete the filtering logic with valid HQL conditions (using your bound parameters).

3. Subqueries as derived tables need an alias
   When you use a subquery as the source in your FROM clause, HQL requires you to assign it an alias. Without this, Hibernate can't parse the subquery results correctly.

Corrected Example Code #

Here's how to adjust your HQL and code to resolve the exception (assuming you want to fetch the latest sensor records):

public List<URPTempSensor> findTempSensorObjs(String systemId, Character isLatest) {
    Map<String,Object> params = new HashMap<>();
    List<URPTempSensor> tSensorList = new ArrayList<>();
    
    // Fix the HQL: add alias, select full entity, complete where clause
    String hql = "select tempSensor from (" +
                 "   select ts from URPTempSensor ts " +
                 "   where ts.systemId = :systemId and ts.isLatest = :status" +
                 ") tempSensor";
    
    // Execute the query (adjust based on your Hibernate setup)
    Query<URPTempSensor> query = session.createQuery(hql, URPTempSensor.class);
    query.setParameter("systemId", systemId);
    query.setParameter("status", isLatest);
    tSensorList = query.list();
    
    return tSensorList;
}

Extra Tips #

• Stick to HQL conventions: Avoid mixing native SQL syntax with HQL unless necessary. If you do need native SQL, use createSQLQuery and explicitly map results to your entity with addEntity(URPTempSensor.class).
• Double-check entity mappings: Ensure all fields referenced in your HQL (like systemId, isLatest) match the property names in your URPTempSensor class—typos here are a frequent cause of syntax exceptions.
• Keep using parameter binding: You're already doing this right! Parameter binding prevents SQL injection and avoids syntax errors from string concatenation (like missing quotes around text values).

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