No Dialect mapping for JDBC type: 2003

Question

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

Perhaps you should consider including the query that produced this, and the relevant data types? — Craig Ringer, Feb 07 '14 at 14:51
Sorry but due to confidential issue I can't provide query. Does hibernate has any mapping for java.sql.Types.ARRAY. — Vikas Singh, Feb 10 '14 at 04:56
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, Feb 10 '14 at 04:58
Sorry for inconvenient, Your guess is correct, below is the query format — Vikas Singh, Feb 10 '14 at 05:03
Read http://stackoverflow.com/q/1647583/398670, http://stackoverflow.com/q/14219498/398670 — Craig Ringer, Feb 10 '14 at 05:33

score 14 · Answer 1 · answered May 17 '21 at 22:13

14

This is how I have solved the issue in SpringBoot:

Add dependency to pom.xml:

        <dependency>
            <groupId>com.vladmihalcea</groupId>
            <artifactId>hibernate-types-52</artifactId>
            <version>2.11.1</version>
        </dependency>

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

}

Specify the PostgreSQL94CustomDialect in application.properties:

spring.jpa.properties.hibernate.dialect=com.package.name.PostgreSQL94CustomDialect

answered May 17 '21 at 22:13

Alex Shavlovsky

321
3
8

we don't need super() here - it is called by default – Alexander Jun 03 '21 at 11:06
Yes! But you might be interested to know that this pattern can be found in many places in hibernate core itself, for example: [here](https://github.com/hibernate/hibernate-orm/blob/5df5ddbe3ad0d68a14f34f89a54f79d6d2cf51d2/hibernate-core/src/main/java/org/hibernate/dialect/PostgreSQL81Dialect.java#L83) and [here](https://github.com/hibernate/hibernate-orm/blob/5df5ddbe3ad0d68a14f34f89a54f79d6d2cf51d2/hibernate-core/src/main/java/org/hibernate/dialect/PostgreSQL94Dialect.java#L21) – Alex Shavlovsky Jun 04 '21 at 18:43
2

Worked perfectly when using `ARRAY_AGG` on PostgreSQL database. – Wim Deblauwe Mar 30 '22 at 09:16

score 3 · Answer 2 · answered Jan 03 '20 at 18:43

3

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;

answered Jan 03 '20 at 18:43

42n4

1,292
22
26

1

This helps for when you explicitly specify the type definition, but I'm not sure how to "activate" the type definitions on an ad-hoc query created with session.createNativeQuery() – Wouter Lievens May 21 '20 at 12:45
@WouterLievens I'm facing the same challenge as you describe. I found an [integration test](https://github.com/vladmihalcea/hibernate-types/blob/master/hibernate-types-52/src/test/java/com/vladmihalcea/hibernate/type/array/ArrayTypeNativeQueryTest.java) in the hibernate-types project that indicates that it _must_ be possible somehow. But I didn't manage to make it work with the type given in the `@ColumnResult` as `String[].class`. However, for me it works if I define the type as `StringArrayList.class`. But that didn't work with a 2-dimensional array. Probably requires some specific setup. – Hein Blöd Oct 20 '20 at 11:04

score 2 · Accepted Answer · answered Apr 28 '14 at 05:35

2

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 ','

answered Apr 28 '14 at 05:35

Vikas Singh

2,838
5
17
32

How? Where to put `text[]` in the `UserType`? Can you give example? – Hendy Irawan Nov 03 '15 at 12:19
Related: [Hibernate, Postgres & Array Type](http://stackoverflow.com/a/21984484/320036) – z0r May 18 '16 at 07:42
1

You can also put a cast in the SQL query to convert the array `e.g. select cast(text_array_col as text)` and then parse that – David Tinker Jan 27 '20 at 08:13
I used a `cast(text_array_col as text)` and then in code parsed it using ```java private List toList(String textArrayString) { return Optional.ofNullable(textArrayString) .map(s-> s.substring(1, s.length() - 1)) /* Remove {} from start and end */ .map(s -> Arrays.asList(s.split(","))) .orElse(List.of()) .stream() .map(s -> Long.parseLong(s.trim())) .collect(Collectors.toList()); } ``` – anztenney Aug 31 '22 at 21:51

score 2 · Answer 4 · answered Jun 14 '21 at 16:31

2

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

answered Jun 14 '21 at 16:31

vitali_y

400
6
13

Works well...I dont get why but that do the trick! Thanks! – Camille Jul 28 '21 at 13:25

score 1 · Answer 5 · answered Mar 14 '23 at 21:08

1

consider also function array_to_string(anyarray, ',')

answered Mar 14 '23 at 21:08

Igor Choriy

37
6

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 19 '23 at 10:17

No Dialect mapping for JDBC type: 2003

5 Answers5

Linked