Hibernate执行SQL报错：无JDBC类型1111的方言映射

Hey there, let's tackle those two frustrating Hibernate errors you're facing. First, let's break down what's causing each one and how to fix them step by step.

1. Empty columns returned #

This error is directly tied to your incomplete SQL statement—you cut off at position.! Even if the query seems to run in dbVisualizer (though it shouldn't, since it's incomplete), Hibernate can't parse a partial result set properly. Here's what to do:

• Finish your SQL query first: Make sure all columns you want to select are included, then test the full statement again in dbVisualizer to confirm it returns valid, non-empty columns.
• Ensure consistent aliases: Double-check that every selected column has a clear alias (like your "EDI" or "datedepart"), and that these aliases match the field names of the entity/DTO you're mapping results to. If you're using native SQL without an entity, you'll need to explicitly define how Hibernate handles each column.
• Use explicit result mapping: If you're not mapping to an entity, leverage addScalar() to tell Hibernate exactly what type each column is (we'll cover this more in the next section).

2. org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111 #

This error means Hibernate encountered a JDBC type it doesn't recognize (type 1111 corresponds to java.sql.Types.OTHER, a catch-all for unrecognized types). Common causes and fixes:

• Check for unsupported database types: If your query returns special types (like JSON, custom database-specific types, or results from complex functions), Hibernate might not know how to map them. For example, if you're using a JSON column, try casting it to a string in your SQL: CAST(your_json_column AS VARCHAR).
• Explicitly define column types with addScalar(): For native SQL queries, explicitly specify each column's Hibernate type to avoid automatic mapping guesswork. Here's an example:

  session.createSQLQuery("SELECT positionreferenceedi.reference AS \"EDI\", position.datedepart AS \"datedepart\", ...")
         .addScalar("EDI", StringType.INSTANCE)
         .addScalar("datedepart", DateType.INSTANCE)
         .addScalar("colis", IntegerType.INSTANCE) // For your position.colis+position.palette calculation
         // Add scalars for all your selected columns
  

• Verify your Hibernate dialect: Make sure your hibernate.dialect configuration matches your database and its version (e.g., org.hibernate.dialect.PostgreSQLDialect for PostgreSQL, org.hibernate.dialect.MySQL8Dialect for MySQL 8+). Outdated or incorrect dialects can cause type mapping issues.
• Cast function results: Your calculation position.colis+position.palette might return an unrecognized type. Explicitly cast it to a known type in your SQL: CAST((position.colis + position.palette) AS INTEGER) AS "colis".

Quick Recap of Steps #

1. Complete and validate your full SQL query in dbVisualizer to ensure it returns valid columns.
2. For native SQL in Hibernate, use addScalar() to define each column's type explicitly.
3. Convert any unsupported database types to Hibernate-friendly types (via CAST in SQL or custom type mappings).
4. Confirm your Hibernate dialect is correctly configured for your database version.

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