1

I have a table something like this

TableA :

pid   | data
 ac1e |{ "cId":"ac-ga-55" ,"name":"omar","dob":"20/09/1999","age":"23","occupation":"SDE"}
 ns7f |{ "cId":"as-op-00","name":"raj","dob":"20/06/1999","age":"23","occupation":"SDE II"}

I am trying to fetch name and age from this table using a JPA native query like this

@Query(value = "select data->>'name' as Name,data->>'age' as Age from TableA where data->>'cId'=:processId", nativeQuery = true)
String findData(@Param("processId") String processId);

what i ultimately want is a query in JPA which will return to me a string or a jsonObject which looks like this {"Name" : "","Age": } which i will add into a JsonArray. But i am not getting any result from this query and my code is breaking and going into the catch block.

NOTE : This table does not have a model class it just exist in database

MyActualQuery :

@Query(value = "select json_build_object (
 'processInstanceId' , data ->> 'processInstanceId' ,
 'lead_id' , data ->> 'leadId_lap' ,
  'loanApplicationNumber' , data ->> 'loanApplicationNumber',
  'constitution' , data ->> 'applicant1EntityType_ch',
  'coborrower_pan_name' , data ->> 'applicant2FullNamePan',
  'lead_generation_date_and_time_timestamp' , data ->> 'leadGenerationDate_lap' ,
  'loan_moved_to_sent_for_disbursal_stage_date_and_time_timestamp' , data ->> 'loanOnBoardingTimestamp' ,
  'pan_number' , CASE when data ->> 'applicant1EntityPan_lap' IS null THEN data ->> 'applicant1Pan_lap' ELSE data ->> 'applicant1EntityPan_lap' END,
  'co_app_pan' , data ->> 'applicant2EntityPan_lap',
  'date_of_incorporation' , data ->> 'applicant1DateofIncorpProp_lap' ,
  'dob' , data ->> 'applicant1DateOfBirth_lap',
  'coborrower_date_of_incorporation' ,  data ->> 'applicant2DateofIncorpProp_lap' ,
  'co_app_dob' , data ->> 'applicant2DateOfBirth_lap' ,
  'mobile_number' , data ->> 'applicant1MobileNumber_lap',
  'co_app_mobileno' , data ->> 'applicant2MobileNumber_lap',
  'loan_type' ,data ->> 'applicantloantype_ch' ,
  'requested_amount_of_loan' , data ->> 'requestedLoanAmount_lap',
  'sanctioned_loan_amount' , data ->> 'sanctionedLoanAmount' ,
  'tenure' , data ->> 'requestedTenure_lap' ,
  'sanctioned_roi' , data ->> 'sanctionedRoi' ,
  'calculated_emi' , data ->> 'calculatedEmi' ,
  'cibil_score' , data ->> 'applicant1CibilScore_lap' ,
  'coborrower_bureau_score' , data ->> 'applicant2bureauScore_lap' ,
  'case_stage' , data ->> 'leadStatus_lap' 
) from kul_flat_vars where data->>'processInstanceId'=:processInstanceId"
          ,nativeQuery = true)
      JSONObject findData(@Param("processInstanceId") String processInstanceId);

StackTrace :

org.springframework.orm.jpa.JpaSystemException: No Dialect mapping for JDBC type: 1111; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:331)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke
Muhammad
  • 59
  • 9
  • Hi Umar, can you provide more details? which database are you using? what message the exception that you are catching has? may you post the stack trace? – Decly Nov 24 '22 at 18:00
  • Hi Decly,i have edited my question with my actual query and stack trace.My database is postgres – Muhammad Nov 24 '22 at 18:33
  • Remove the semi-colon ';' from your native query. It being there makes it apart of the parameter name. – Chris Nov 24 '22 at 18:44
  • i removed the semicolon,i am getting a new error now,have updated the stacktrace – Muhammad Nov 24 '22 at 18:49

1 Answers1

0

I have resolved this error by simply changing the return type of native query from JsonObject to String.

Muhammad
  • 59
  • 9