No Dialect mapping for JDBC type: 1111 (Native Query)

Question

I encountered an error as per title at query.getResultList() ; on JsonbHibernateExampleApplicationTests class file when running native query to return a json object.

It has no issue returning of non json type of column.

Kindly assist and do let me know if there is a need for more information.

Below is the source code

public class JsonbHibernateExampleApplicationTests {

    @Autowired
    private PersonRepository repository;

    @Autowired
    private ApplicationContext appContext;

    @Test
    public void contextLoads() {
        EntityManagerFactory entityManagerFactory = (EntityManagerFactory) appContext
                .getBean("entityManagerFactory");

        EntityManager entityManager = entityManagerFactory.createEntityManager();

        String sql = "select additional_data  from person";

        javax.persistence.Query query = entityManager.createNativeQuery(sql);
        query.getResultList();
        //List<JsonDataUserType> list = (List<JsonDataUserType>) query.getResultList();
        /* System.out.println("list:" + list);
        for (JsonDataUserType json: list)
        {
            System.out.println("json:" + json);
        }*/
        //List<Object[]> objects = query.getResultList();
        //System.out.println("result:"+ objects);

    }

}

I specified the additional_data column as customized json type in the entity

@Type(type = "JsonDataUserType")
private Map<String, String> additionalData;

I can't paste all the source code here and you can check out the full source code at http://vojtechruzicka.com/postgresqls-jsonb-type-mapping-using-hibernate/

I just modified the original source as URL above to run a native SQL query.

Many Thanks,

Possible duplicate of [No Dialect mapping for JDBC type: 1111](https://stackoverflow.com/questions/28192547/no-dialect-mapping-for-jdbc-type-1111) — Fab, Sep 27 '17 at 07:36
Hi Fab, i have checked this out and found that issue is non json format and i can't put the json customized type as UUID. See the below @Type(type = "JsonDataUserType") private Map additionalData; — Banana Tech, Sep 27 '17 at 07:52
Hi Punit, entityManagerFactory is from JDK 8 source code javax.persistence.EntityManagerFactory — Banana Tech, Sep 27 '17 at 07:54
It has similar issue as this post but no answer .. https://stackoverflow.com/questions/32147303/org-hibernate-mappingexception-no-dialect-mapping-for-jdbc-type-1111-for-jsono?rq=1 — Banana Tech, Sep 27 '17 at 08:45
Hi all, i found an alternative solution, i cast the additional_data json data to text then able to get the output. see the code below Please do let me know if you guys have a better solution. Probably a POJO mapping. String sql = "select CAST(additional_data AS text) from person"; javax.persistence.Query query = entityManager.createNativeQuery(sql); List list = query.getResultList(); System.out.println("list:" + list); System.out.println("list 0:" + list.get(0)); — Banana Tech, Sep 28 '17 at 08:38
I used the @BananaTech technique it working for me. Thanks. Also after getting the resultList, I am using a simple mapper to convert the string back to proper Object. — Ajit Singh, Nov 15 '18 at 10:58

score 5 · Answer 1 · edited Nov 27 '18 at 12:18

I found an alternative solution. I cast the additional_data json data to text.
Then I am able to get the output.
See the code below.

Please do let me know if you guys have a better solution. Probably a POJO mapping.

String sql = "select CAST(additional_data AS text) from person"; 
javax.persistence.Query query = entityManager.createNativeQuery(sql); 
List list = query.getResultList(); System.out.println("list:" + list); 
System.out.println("list 0:" + list.get(0))

No Dialect mapping for JDBC type: 1111 (Native Query)

1 Answers1