使用Java-Hibernate连接PostgreSQL与MSSQL的Criteria查询问题

Hey there! Let's walk through the common pitfalls and actionable fixes for your Hibernate Criteria query when working with both PostgreSQL and SQL Server 2012:

1. Ensure You're Using the MSSQL-Specific Session #

Since you're connecting two databases, the most critical mistake here is accidentally using the PostgreSQL Session for an MSSQL-targeted query. Hibernate relies on the Session's underlying SessionFactory (and its configured dialect) to generate valid SQL — using the wrong one will lead to dialect-specific syntax errors.

Fix:

• Configure two distinct SessionFactory beans, one for each database:
  • For MSSQL: Use org.hibernate.dialect.SQLServer2012Dialect and its corresponding datasource
  • For PostgreSQL: Use org.hibernate.dialect.PostgreSQLDialect and its datasource
• Inject the MSSQL-specific SessionFactory when creating your session for this query:

  // Example with Spring dependency injection
  @Autowired
  @Qualifier("mssqlSessionFactory")
  private SessionFactory mssqlSessionFactory;
  
  // Create the correct session for your MSSQL query
  Session session = mssqlSessionFactory.openSession();
  

2. Fix DetachedCriteria to Executable Criteria Conversion #

Your code snippet cuts off at criteria = session.createCriteria(...) — this is likely where you're losing the projection configuration from your DetachedCriteria. You need to bind the detached criteria to your session properly to preserve its settings:

Correct Code:

// After setting up your DetachedCriteria and ProjectionList
Criteria executableCriteria = detachedCriteria.getExecutableCriteria(session);
// Execute the query and retrieve results
List<Object[]> results = executableCriteria.list();
// Each Object[] will contain [maxVersionId, groupedId] in order

3. Verify Composite Key Mapping & Field Names #

From your field names (COMPOSEDID_VERSION_ID, COMPOSEDID_ID), it looks like you're working with a composite primary key. Hibernate will throw "could not resolve property" errors if these fields aren't mapped correctly in your entity.

Fix:

• Ensure your entity uses either @EmbeddedId or @IdClass for the composite key. Example with @EmbeddedId:

  @Embeddable
  public class EntityCompositeId implements Serializable {
      @Column(name = "id")
      private Long id;
      @Column(name = "version_id")
      private Integer versionId;
      
      // Required: Implement equals() and hashCode() for composite keys
      @Override
      public boolean equals(Object o) {
          if (this == o) return true;
          if (o == null || getClass() != o.getClass()) return false;
          EntityCompositeId that = (EntityCompositeId) o;
          return Objects.equals(id, that.id) && Objects.equals(versionId, that.versionId);
      }
  
      @Override
      public int hashCode() {
          return Objects.hash(id, versionId);
      }
  }
  
  @Entity
  public class YourEntity {
      @EmbeddedId
      private EntityCompositeId compositeId;
      // Other entity fields and getters/setters
  }
  

• Update your projection constants to match the entity's property path (not raw database column names):

  // Match your entity's nested property names
  public static final String COMPOSEDID_VERSION_ID = "compositeId.versionId";
  public static final String COMPOSEDID_ID = "compositeId.id";
  

4. Debug Generated SQL to Catch Dialect Issues #

SQL Server 2012 has strict syntax rules for GROUP BY clauses and aggregate functions. Enable Hibernate's SQL logging to see exactly what's being generated and spot dialect-specific bugs:

Add These Properties to Your MSSQL Hibernate Config:

hibernate.show_sql=true
hibernate.format_sql=true
hibernate.use_sql_comments=true

This will print formatted SQL to your logs — if you see PostgreSQL-specific syntax (like LIMIT or SERIAL) in the MSSQL query, you know you're using the wrong SessionFactory.

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