No Dialect mapping for JDBC type: 2003

8
votes

Though there are some question exists with this title , but my query does not solve from those thread.

I am executing recursive (using with clause) query through hibernate in postgres, query's result contains path of search also

ex: one row of query result

5811;"axyz_3_3";"ABC";5782;5811;5797;4;"**{acl_3_3,acl3_4,acl3,acl_3_3}**";t;t

Does hibernate has any mapping type for "{acl_3_3,acl3_4,acl3,acl_3_3}" other than String, something similar to CHARACTER_ARRAY or CHAR_ARRAY.

Below is the sample of the query's output

id   |name|discri|pId|asscID|immeId|depth|path|cycle|canDelete
5797;"abc3";"abc";5782;5811;5788;7;"{abc_3_3,abc3_4,abc3,abc4}";t;f
5797;"abc3";"abc";5782;5786;5813;6;"{abc1,abc2,abc3,abc3}";t;f
5799;"abc4";"abc";5782;5811;5786;6;"{abc_3_3,abc3_4,abc4}";t;f
5788;"abc2";"abc";5782;5811;5786;6;"{abc_3_3,abc3_4,abc2}";f;f
5786;"abc1";"abc";5782;5786;5799;5;"{abc1,abc2,abc3,abc1}";t;f
5797;"abc3";"abc";5782;5786;5813;5;"{abc1,abc2,abc3,abc3}";t;f
5813;"abc3_4";"abc";5782;5786;5811;5;"{abc1,abc2,abc3_4}";f;f
5786;"abc1";"abc";5782;5811;5799;5;"{abc_3_3,abc4,abc1}";f;f
5813;"abc3_4";"abc";5782;5811;5797;4;"{abc3_4,abc3,abc3_4}";t;f
5811;"abc_3_3";"abc";5782;5811;5797;4;"{abc_3_3,abc3,abc_3_3}";t;t
5799;"abc4";"abc";5782;5811;5797;4;"{abc3,abc4}";f;f

Hibernate is throwing below exception

Caused by: com.vik.prod.service.UnidentifiedException: No Dialect mapping for JDBC type: 2003
at com.vik.prod.service.ServiceExecutorUtils.execute(ServiceExecutorUtils.java:93)
at com.vik.prod.service.ServerServiceExecutor.execute(ServerServiceExecutor.java:76)
at com.vik.prod.service.ClientDelegate.execute(ClientDelegate.java:197)
... 33 more

Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 2003

4
hibernatepostgresql
Perhaps you should consider including the query that produced this, and the relevant data types? - Craig Ringer
Sorry but due to confidential issue I can't provide query. Does hibernate has any mapping for java.sql.Types.ARRAY. - Vikas Singh
If you can't show the real query, produce a sample you can show, or at least provide a decent description of the problem. Am I guessing correctly that your question should actually read "How can I read and write PostgreSQL arrays like text[] in Hibernate?" . You haven't shown queries, error messages, code, etc. - Craig Ringer
Sorry for inconvenient, Your guess is correct, below is the query format - Vikas Singh
Read stackoverflow.com/q/1647583/398670, stackoverflow.com/q/14219498/398670 - Craig Ringer

4 Answers

3
votes

This is how I have solved the issue in SpringBoot:

  1. Add dependency to pom.xml:
        <dependency>
            <groupId>com.vladmihalcea</groupId>
            <artifactId>hibernate-types-52</artifactId>
            <version>2.11.1</version>
        </dependency>
  1. Extend your Hybernate Dialect as follows:
import com.vladmihalcea.hibernate.type.array.StringArrayType;
import org.hibernate.dialect.PostgreSQL94Dialect;

public class PostgreSQL94CustomDialect extends PostgreSQL94Dialect {

    public PostgreSQL94CustomDialect() {
        super();
        this.registerHibernateType(2003, StringArrayType.class.getName());
    }

}
  1. Specify the PostgreSQL94CustomDialect in application.properties:
spring.jpa.properties.hibernate.dialect=com.package.name.PostgreSQL94CustomDialect
2
votes

Hibernate does not provide and Converter class/Mapper class to convert DB text[] datatype, For this either we can write our own converted type implementing UserType or we using sqlQuery.addScalar( "path", Hibernate.TEXT ); we can map text[] to text and then in java code we can split it from ','

2
votes

Use a package 'com.vladmihalcea:hibernate-types-52:2.8.0' or add your database dialect to yml file in resources:

@Entity
@TypeDefs({
        @TypeDef(name = "string-array", typeClass = StringArrayType.class),
        @TypeDef(name = "json", typeClass = JsonStringType.class),
        @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
public class Post implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Type(type = "string-array")
    @Column(columnDefinition = "text[]")
    private String[] tags;
1
votes

One possibility for problem resolution use "UNNEST" postgree function, this can be used as a hint:

SELECT UNNEST({acl_3_3,acl3_4,acl3,acl_3_3})