CODEWITHSUNDEEP
javasqlhibernatepostgresqlhql
Peter Jurkovic
Peter Jurkovic

Reputation: 2896

MappingException: No Dialect mapping for JDBC type: 2002

I try to query PostgreSQL database via Hibernate native sql interface. But I'm getting org.hibernate.MappingException: No Dialect mapping for JDBC type: 2002

SQL query is quite complex, but via (e.g.) pgAdmin works fine. Does anyone have any idea where the problem should be?

WebpageDaoImpl.java

    public PageDto search(final String term, final Long parentNodeId, final String localeCode){
        hqlQuery =  sessionFactory.getCurrentSession().createSQLQuery(buildSearchQuery(false));                 
        hqlQuery.setCacheable(false);
        hqlQuery.setString("locale", localeCode);
        hqlQuery.setString("query", term);
        hqlQuery.setString("fullTextQuery", term.replace(" ", "&"));
        hqlQuery.setLong("nodeId", parentNodeId);
        items.setItems(hqlQuery.list());
    }

  private String buildSearchQuery(){
    StringBuilder sql = new StringBuilder();
    sql.append("with recursive tmp_webpage(id, parent) as ( ")
   .append("    values(cast(-1 as bigint), cast(:nodeId as bigint)) ")
   .append(" union all ")
   .append("    select w.id, w.parent_id ")
   .append("    from tmp_webpage as tw, webpage as w ")
   .append("    where w.id = parent ")
   .append(" ) ")
   .append("SELECT w FROM webpage w ")
   .append("    inner join webpage_content wc ON w.id=wc.webpage_id ")
   .append("WHERE wc.localized_key= :locale AND w.enabled=true (")
   .append("    unaccent(lower(wc.title)) like CONCAT('%', unaccent(lower(:query)) , '%') OR ")
   .append("    wc.webpage_tsvector @@ to_tsquery( cast( wc.localized_key as regconfig), :fullTextQuery) ) ");
   .append("GROUP BY w.id, wc.webpage_tsvector, wc.localized_key ")
   .append("ORDER BY ts_rank(wc.webpage_tsvector, to_tsquery(cast( wc.localized_key as regconfig), :fullTextQuery )) DESC");
   return sql.toString();
}

Webpage.java 

public class Webpage{

    private Long id;
    private Webpage parent;
    private Set<Webpage> childrens;
    // ...

    @ElementCollection
    @CollectionTable(name = "webpage_content")
    @MapKeyJoinColumn(name = "locale")
    private Map<String, WebpageContent> localized;

    //...
}

WebpageContent.java

@Embeddable
public class WebpageContent {

    private String name;
    private String title;
    private String description;
    private String content;
    //...
}

Note: Table webpage has one more explicit created column webpage_tsvector, wherein is saved fulltext vector.

Upvotes: 3

Views: 10031

Answers (1)

Peter Jurkovic
Peter Jurkovic

Reputation: 2896

I solved given problem with .addEntity(Webpage.class)
:

hqlQuery =  sessionFactory
            .getCurrentSession()
            .createSQLQuery(buildSearchQuery())
            .addEntity(Webpage.class);

Upvotes: 3

Related Questions