'No Dialect mapping for JDBC type: 1111

I'm working on a Spring JPA Application, using MySQL as database. I ensured that all spring-jpa libraries, hibernate and mysql-connector-java is loaded.

I'm running a mysql 5 instance. Here is a excerpt of my application.properties file:

spring.jpa.show-sql=false
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect

spring.datasource.url=jdbc:mysql://localhost/mydatabase
spring.datasource.username=myuser
spring.datasource.password=SUPERSECRET
spring.datasource.driverClassName=com.mysql.jdbc.Driver

When executing an integration test, spring startsup properly but fails on creating the hibernate SessionFactory, with the exception:

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

I think my dialects should be Mysql5Dialect, I also tried the one explicitly stating InnoDB, and the two dialect options which don't indicate the version 5. But I always end up with the same 'No Dialect mapping for JDBC type: 1111' message. My application.properties file resides in the test/resources source folder. It is recognized by the JUnit Test runner (I previously got an exception because of an typo in it).

Are the properties I'm setting wrong? I couldn't find some official documentation on these property names but found a hint in this stackoverflow answer: https://stackoverflow.com/a/25941616/1735497

Looking forward for your answers, thanks!

BTW The application is already using spring boot.

javamysqlspringhibernatejpa


Solution 1:[1]

Here the answer based on the comment from SubOptimal:

The error message actually says that one column type cannot be mapped to a database type by hibernate. In my case it was the java.util.UUID type I use as primary key in some of my entities. Just apply the annotation @Type(type="uuid-char") (for postgres @Type(type="pg-uuid"))

Solution 2:[2]

I got the same error because my query returned a UUID column. To fix that I returned the UUID column as varchar type through the query like "cast(columnName as varchar)", then it worked.

Example:

public interface StudRepository extends JpaRepository<Mark, UUID> {

    @Modifying
    @Query(value = "SELECT Cast(stuid as varchar) id, SUM(marks) as marks FROM studs where group by stuid", nativeQuery = true)
    List<Student> findMarkGroupByStuid();

    public static interface Student(){
        private String getId();
        private String getMarks();
    }
}

Solution 3:[3]

There is also another common use-case throwing this exception. Calling function which returns void. For more info and solution go here.

Solution 4:[4]

Please Check if some Column return many have unknow Type in Query .

eg : '1' as column_name can have type unknown

and 1 as column_name is Integer is correct One .

This thing worked for me.

Solution 5:[5]

I got the same error, the problem here is UUID stored in DB is not converting to object.

I tried applying these annotations @Type(type="uuid-char") (for postgres @Type(type="pg-uuid") but it didn't work for me.

This worked for me. Suppose you want id and name from a table with a native query in JPA. Create one entity class like 'User' with fields id and name and then try converting object[] to entity we want. Here this matched data is list of array of object we are getting from query.

@Query( value = "SELECT CAST(id as varchar) id, name from users ", nativeQuery = true)

public List<Object[]> search();

public class User{
   private UUID id;
   private String name;
}


List<User> userList=new ArrayList<>();

for(Object[] data:matchedData){
        userList.add(new User(UUID.fromString(String.valueOf(data[0])),
                String.valueOf(data[1])));

    }

Suppose this is the entity we have

Solution 6:[6]

Finding the column that triggered the issue

First, you didn't provide the entity mapping so that we could tell what column generated this problem. For instance, it could be a UUID or a JSON column.

Now, you are using a very old Hibernate Dialect. The MySQL5Dialect is meant for MySQL 5. Most likely you are using a newer MySQL version.

So, try to use the MySQL8Dialect instead:

spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect

Adding non-standard types

In case you got the issue because you are using a JSON column type, try to provide a custom Hibernate Dialect that supports the non-standard Type:

public class MySQL8JsonDialect
        extends MySQL8Dialect{
 
    public MySQL8JsonDialect() {
        super();
        this.registerHibernateType(
            Types.OTHER, JsonStringType.class.getName()
        );
    }
}

Ans use the custom Hibernate Dialect:

<property
    name="hibernate.dialect"
    value="com.vladmihalcea.book.hpjp.hibernate.type.json.MySQL8JsonDialect"
/>

If you get this exception when executing SQL native queries, then you need to pass the type via addScalar:

JsonNode properties = (JsonNode) entityManager
.createNativeQuery(
    "SELECT properties " +
    "FROM book " +
    "WHERE isbn = :isbn")
.setParameter("isbn", "978-9730228236")
.unwrap(org.hibernate.query.NativeQuery.class)
.addScalar("properties", JsonStringType.INSTANCE)
.getSingleResult();
 
assertEquals(
    "High-Performance Java Persistence",
    properties.get("title").asText()
);

Solution 7:[7]

Sometimes when you call sql procedure/function it might be required to return something. You can try returning void: RETURN; or string (this one worked for me): RETURN 'OK'

Solution 8:[8]

If you have native SQL query then fix it by adding a cast to the query.

Example:

CAST('yourString' AS varchar(50)) as anyColumnName

In my case it worked for me.

Solution 9:[9]

In my case the problem was that, I forgot to add resultClasses attribute when I setup my stored procedure in my User class.

@NamedStoredProcedureQuery(name = "find_email",
                procedureName = "find_email", resultClasses = User.class, //<--I forgot that. 
                parameters = {
                    @StoredProcedureParameter(mode = ParameterMode.IN, name = "param_email", type = String.class)
                }),

Solution 10:[10]

This also happens when you are using Hibernate and returning a void function. AT least w/ postgres. It doesnt know how to handle the void. I ended up having to change my void to a return int.

Solution 11:[11]

If you are using Postgres, check that you don't have a column of type Abstime. Abstime is an internal Postgres datatype not recognized by JPA. In this case, converting to Text using TO_CHAR could help if permitted by your business requirements.

Solution 12:[12]

Another simple explanation might be that you're fetching a complex Type (Entity/POJO) but do not specify the Entity to map to:

String sql = "select yourentity.* from {h-schema}Yourentity yourentity";
return entityManager.createNativeQuery(sql).getResultList();

simply add the class to map to in the createNativeQuery method:

return entityManager.createNativeQuery(sql, Yourentity.class).getResultList();

Solution 13:[13]

In my case, the issue was Hibernate not knowing how to deal with an UUID column. If you are using Postgres, try adding this to your resources/application.properties:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL9Dialect

Solution 14:[14]

if using Postgres

public class CustomPostgreSqlDialect extends PostgreSQL94Dialect{

    @Override
    public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor)
    {
        switch (sqlTypeDescriptor.getSqlType())
        {
        case Types.CLOB:
            return VarcharTypeDescriptor.INSTANCE;
        case Types.BLOB:
            return VarcharTypeDescriptor.INSTANCE;
        case 1111://1111 should be json of pgsql
            return VarcharTypeDescriptor.INSTANCE;
        }
        return super.remapSqlTypeDescriptor(sqlTypeDescriptor);
    }
    public CustomPostgreSqlDialect() {
        super();
        registerHibernateType(1111, "string");
    }}

and use

<prop key="hibernate.dialect">com.abc.CustomPostgreSqlDialect</prop>

Solution 15:[15]

For anybody getting this error with an old hibernate (3.x) version:

do not write the return type in capital letters. hibernate type implementation mapping uses lowercase return types and does not convert them:

CREATE OR REPLACE FUNCTION do_something(param varchar)
    RETURNS integer AS
$BODY$
...

Solution 16:[16]

This is for Hibernate (5.x) version

Calling database function which return JSON string/object

For this use unwrap(org.hibernate.query.NativeQuery.class).addScalar() methods for the same.

Example as below (Spring & Hibernate):

@PersistenceContext

EntityManager em;

@Override

    public String getJson(String strLayerName) {

        String *nativeQuery* = "select fn_layer_attributes(:layername)";

        return em.createNativeQuery(*nativeQuery*).setParameter("layername", strLayerName).**unwrap(org.hibernate.query.NativeQuery.class).addScalar**("fn_layer_attributes", **new JsonNodeBinaryType()**) .getSingleResult().toString();

    }

Solution 17:[17]

Function or procedure returning void cause some issue with JPA/Hibernate, so changing it with return integer and calling return 1 at the end of procedure may solved the problem.

SQL Type 1111 represents String.

Solution 18:[18]

If you are calling EntityManager.createNativeQuery(), be sure to include the resulting java class in the second parameter:

return em.createNativeQuery(sql, MyRecord.class).getResultList()

Solution 19:[19]

After trying many proposed solutions, including:

it was finally this one that fixed everything with the least amount of changes:

https://gist.github.com/agrawald/adad25d28bf6c56a7e4618fe95ee5a39

The trick is to not have @TypeDef on your class, but instead have 2 different @TypeDef in 2 different package-info.java files. One inside your production code package for your production DB, and one inside your test package for your test H2 DB.

Related Questions