2

I have a native query to select datafrom database, I'm using postgrsql, hibernate and spring boot with java ... I'm trying to find an user according to some criterias in where clause but i get the "No Dialect mapping for JDBC type: 1111" error caused by the json field, how can i overcome this error? and this is my code:

EntityManager em = entityManagerFactory.createEntityManager();
List<Object[]> resultat = new ArrayList<>();

String sqlString = "SELECT u.name, u.age, u.address, u.phone, u.activated FROM user as u ";

Query query = em.createNativeQuery(sqlString);
List<User> resultats = query.getResultList();
em.close();

the user entity has name is a text, age is a number, address is an address object and phone is a json object.

(for some reasons i need to clean the value of listOperators using regular expression and it works on posgrsql).

the dialect that I'm using in my application.properties is spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

I will appreciate your help, thanks in advance.

Nick
  • 419
  • 9
  • 19
  • 1
    Different RDBMS, I know, but similar Hibernate context: [see here](https://stackoverflow.com/questions/28192547/no-dialect-mapping-for-jdbc-type-1111). Any help there? – andrewJames Apr 17 '20 at 21:51
  • Also looks like you are concatenating your SQL string - best to use a [prepared statement](https://stackoverflow.com/questions/2099425/when-should-we-use-a-preparedstatement-instead-of-a-statement). – andrewJames Apr 17 '20 at 21:51
  • @andrewjames thanks ... yes i saw it and i tried to cast the primary key to varchar and json object to text but i still have the same error. – Nick Apr 17 '20 at 22:07
  • @andrewjames the problem is in the request it self or somewhere? – Nick Apr 17 '20 at 22:11
  • I don't know. A suggestion: remove columns from your SQL statement, one-by-one, and re-test each time, until it works. If you remove them all and it still does not work, that tells you something, too. Sorry - that's all I got. – andrewJames Apr 17 '20 at 22:57
  • I identify the source of the problem, it's the json object, how can i overcome it? Thank you – Nick Apr 18 '20 at 09:12
  • Edit the question, clarify the problem - and include a sample of your data, including the JSON. – andrewJames Apr 18 '20 at 13:04
  • @andrewjames Thank you for your indication, I resolve the issue, thanks once again – Nick Apr 18 '20 at 14:54

1 Answers1

1

Thanks to the @andrewjames indications, I resolved the issue by casting the json field to text

Nick
  • 419
  • 9
  • 19