Reputation: 1581

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.

Upvotes: 70

Answers (24)

Stefan

Reputation: 1

It may be due to the List passed as a parameter: cast it like this WHERE l.id IN (CAST(ENCODE(:locationIds, 'hex') AS UUID))

Upvotes: 0

epox

Reputation: 10978

In case of nativeQuery = true

A typical error after you copy-pasted a repository:

No Dialect mapping for JDBC type: 1111 means Hibernate cannot create a row object of your (say) MyEntityB return type. This happens when you mix the type you put into extends CrudRepository<MyEntityA, MyId> up.

So, double-check if the Template class fits the table you use in the native query. E.g.:

                                           // If this (A) type is wrong, then...
interface MyRepositoryA extends CrudRepository<MyEntityA, MyKey> {

  @Query(
      value       = "SELECT * FROM my_table_B mta WHERE ..."
      nativeQuery = true)
  List<MyEntityB> findManyB(List<UUID> refereceIds);
  // ... then you will get 'JDBC type: 1111' exception here, near to (B) return type.

To fix it

// replace
   extends CrudRepository<MyEntityA, ...
// with
   extends CrudRepository<MyEntityB, ...

Upvotes: 1

Adán Escobar

Reputation: 4783

you can solve this issue: "No Dialect mapping for JDBC type: xxx" in 3 steps

1.- Cast as text in query

SELECT CAST(my_column as text) From my_table;

2.- Create a custom AttributeConverter thats convert from text to your desired type, in this example String to integer[]

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.util.Arrays;
import java.util.stream.Collectors;

@Converter(autoApply = true)
public class IntegerArrayConverter implements AttributeConverter<Integer[], String> {

private static final String ARRAY_DELIMITER = ",";

@Override
public String convertToDatabaseColumn(Integer[] attribute) {
    System.out.println("convertToDatabaseColumn attribute: " + attribute);
    if (attribute == null || attribute.length == 0) {
        return null;
    }
    return Arrays.stream(attribute)
            .map(String::valueOf)
            .collect(Collectors.joining(ARRAY_DELIMITER));
}

@Override
public Integer[] convertToEntityAttribute(String dbData) {
    System.out.println("convertToEntityAttribute dbData: " + dbData);
    if (dbData == null || dbData.isEmpty()) {
        return null;
    }
    if(dbData.startsWith("{") && dbData.endsWith("}")) {
        dbData = dbData.substring(1, dbData.length() - 1);
    }
    String[] stringArray = dbData.split(ARRAY_DELIMITER);
    return Arrays.stream(stringArray)
            .map(Integer::parseInt)
            .toArray(Integer[]::new);
}
}

3.- Use convert in you entity like this:

@Convert(converter = IntegerArrayConverter.class)
@Column(name = "my_column", columnDefinition = "integer[]")
private Integer[] MyColumn;

Upvotes: 0

Vlad Mihalcea

Reputation: 154120

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()
        );
    }
}

And 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()
);

Upvotes: 10

drt

Reputation: 817

I had little different issue, I was facing this issue for pagination. When select query was pulling number of records (15) more than pageSize(10) then I was getting below error. -

Details: org.springframework.orm.jpa.JpaSystemException: No Dialect mapping for JDBC type: 1111; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

Before fixing I made sure -

My Dialect lib is latest
column data types are correct

Fix - added registerHibernateType

@Component
public class JSONBPostgreSQLDialect extends PostgreSQL95Dialect {

    public JSONBPostgreSQLDialect() {
        super();
        registerColumnType(Types.JAVA_OBJECT, JSONBUserType.JSONB_TYPE);
        //This line fixed Dialect mapping errorx
        registerHibernateType(Types.OTHER, String.class.getName());
    }
}

JSONBUserType is custom type created.

@Component
public class JSONBUserType implements UserType, ParameterizedType {

    private static final ClassLoaderService classLoaderService = new ClassLoaderServiceImpl();

    public static final String JSONB_TYPE = "jsonb";
    public static final String CLASS = "CLASS";

    private Class jsonClassType;

    @Override
    public Class<Object> returnedClass() {
        return Object.class;
    }

    @Override
    public int[] sqlTypes() {
        return new int[]{Types.JAVA_OBJECT};
    }

    @Override
    public Object nullSafeGet(ResultSet rs,
                              String[] names,
                              SharedSessionContractImplementor session,
                              Object owner)
            throws HibernateException, SQLException {

        final String cellContent = rs.getString(names[0]);
        if (cellContent == null) {
            return null;
        }
        try {
            final ObjectMapper mapper = new ObjectMapper();
            return mapper.readValue(cellContent.getBytes("UTF-8"), jsonClassType);
        } catch (final Exception ex) {
            throw new RuntimeException("Failed to convert String to Invoice: " + ex.getMessage(), ex);
        }
    }

    @Override
    public void nullSafeSet(PreparedStatement ps,
                            Object value,
                            int idx,
                            SharedSessionContractImplementor session)
            throws HibernateException, SQLException {
        if (value == null) {
            ps.setNull(idx,
                       Types.OTHER);
            return;
        }
        try {
            final ObjectMapper mapper = new ObjectMapper();
            final StringWriter w = new StringWriter();
            mapper.writeValue(w,
                              value);
            w.flush();
            ps.setObject(idx,
                         w.toString(),
                         Types.OTHER);
        } catch (final Exception ex) {
            throw new RuntimeException("Failed to convert Invoice to String: " + ex.getMessage(),
                                       ex);
        }

    }

    @Override
    public void setParameterValues(Properties parameters) {
        final String clazz = (String) parameters.get(CLASS);
        if (null != clazz) {
            jsonClassType = classLoaderService.classForName(clazz);
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public Object deepCopy(Object value) throws HibernateException {

        if (!(value instanceof Collection)) {
            return value;
        }

        Collection<?> collection = (Collection) value;
        Collection collectionClone = CollectionFactory.newInstance(collection.getClass());

        collectionClone.addAll(collection.stream()
                                         .map(this::deepCopy)
                                         .collect(Collectors.toList()));

        return collectionClone;
    }

    static final class CollectionFactory {
        @SuppressWarnings("unchecked")
        static <E, T extends Collection<E>> T newInstance(Class<T> collectionClass) {
            if (List.class.isAssignableFrom(collectionClass)) {
                return (T) new ArrayList<E>();
            } else if (Set.class.isAssignableFrom(collectionClass)) {
                return (T) new HashSet<E>();
            } else {
                throw new IllegalArgumentException("Unsupported collection type : " + collectionClass);
            }
        }
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    @Override
    public boolean equals(Object x,
                          Object y) throws HibernateException {
        if (x == y) {
            return true;
        }

        if ((x == null) || (y == null)) {
            return false;
        }

        return x.equals(y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        assert (x != null);
        return x.hashCode();
    }

    @Override
    public Object assemble(Serializable cached,
                           Object owner) throws HibernateException {
        return deepCopy(cached);
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        Object deepCopy = deepCopy(value);

        if (!(deepCopy instanceof Serializable)) {
            throw new SerializationException(String.format("%s is not serializable class",
                                                           value),
                                             null);
        }

        return (Serializable) deepCopy;
    }

    @Override
    public Object replace(Object original,
                          Object target,
                          Object owner) throws HibernateException {
        return deepCopy(original);
    }
}

And it is used in entity like this

@TypeDef(name = "addressValType", typeClass = JSONBUserType.class, parameters = {@Parameter(name = JSONBUserType.CLASS, value = "com.address.response.AddressResponse")})
@Table(name = "addressValidation")
public class Address implements Serializable {
    private static final long serialVersionUID = -2370572023911224797L;
    @Id
    @Column(name = "employeeNumber")
    private Integer employeeNumber;

    @Column(name = "inputAddress", columnDefinition = "jsonb")
    @Type(type = "addressValType")
    private SapPostalAddressResponse inputAddress;
  }

Upvotes: 0

Vitaly Kolesnikov

Reputation: 365

I had the same issue and wasted like 1 hour before I noticed that I use JpaRepository of one entity (let's say Person) to retrive another entity (let's say Order). Hope this helps someone.

Upvotes: 0

Chris

Reputation: 4648

After trying many proposed solutions, including:

https://stackoverflow.com/a/59754570/349169 which is one of the solutions proposed here
https://vladmihalcea.com/hibernate-no-dialect-mapping-for-jdbc-type/

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.

Upvotes: 0

user2510439

Reputation: 21

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()

Upvotes: 0

Ashish Rajput

Reputation: 161

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>

Upvotes: 1

Andrei Savin

Reputation: 2484

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

Upvotes: 3

icyerasor

Reputation: 5242

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();

Upvotes: 3

iamfnizami

Reputation: 193

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.

Upvotes: 1

Supreet Singh

Reputation: 1010

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

Upvotes: 15

Ashok Parmar

Reputation: 408

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();

    }

Upvotes: 0

Bharti Rawat

Reputation: 2073

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.

Upvotes: 5

tutak

Reputation: 1098

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.

Upvotes: 1

Mike Packer

Reputation: 83

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.

Upvotes: 1

Ramya

Reputation: 651

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();
    }
}

Upvotes: 55

GaborH

Reputation: 719

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)
                }),

Upvotes: 1

Anubis

Reputation: 481

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$
...

Upvotes: 0

SakeSushiBig

Reputation: 1581

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"))

Upvotes: 37

jaskirat Singh

Reputation: 836

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.

Upvotes: 11

Klapsa2503

Reputation: 919

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'

Upvotes: 3

Juraj

Reputation: 6638

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

Upvotes: 22

No Dialect mapping for JDBC type: 1111

Answers (24)

In case of nativeQuery = true

Finding the column that triggered the issue

Adding non-standard types

Related Questions