无实体类时JPA原生查询提取JSON数据报错求助

问题背景 #

数据库表结构 #

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

需求与尝试 #

需要通过JPA原生查询提取JSON字段，返回{"Name":"xxx","Age":"xxx"}格式的JSON字符串或JSONObject存入JsonArray。最初尝试的查询无结果且触发异常：

@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);

实际查询语句 #

改用json_build_object构造完整JSON对象，但依然报错（无对应实体类）：

@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);

报错信息 #

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

解决方法 #

原因 #

报错核心是Hibernate默认方言未映射PostgreSQL返回的JSON类型（JDBC类型1111对应OTHER），无法将查询结果转换为指定的JSONObject或String类型。

方案1：将查询结果转为字符串（最简便） #

修改SQL，把json_build_object的结果强制转为文本类型，返回类型改为String，之后自行转成JSONObject：

@Query(value = "select cast(json_build_object (
 'processInstanceId' , data ->> 'processInstanceId' ,
 -- 其他字段保持不变
) as text) from kul_flat_vars where data->>'processInstanceId'=:processInstanceId", nativeQuery = true)
String findData(@Param("processInstanceId") String processInstanceId);

调用后转换示例：

String jsonStr = yourRepository.findData("xxx");
JSONObject json = new JSONObject(jsonStr);

方案2：自定义Hibernate方言 #

扩展PostgreSQL方言，添加JSON类型映射：

public class CustomPostgreSQLDialect extends PostgreSQLDialect {
    public CustomPostgreSQLDialect() {
        super();
        // 映射JSON类型到Jackson的JsonNode
        registerHibernateType(Types.OTHER, JsonNode.class.getName());
        // 或者映射到String
        // registerHibernateType(Types.OTHER, String.class.getName());
    }
}

在Spring配置中指定自定义方言：

spring.jpa.properties.hibernate.dialect=com.your.package.CustomPostgreSQLDialect

之后可直接返回JsonNode或String，无需手动转换。

方案3：使用Spring Data Projection #

创建一个接口作为投影，对应需要提取的字段：

public interface LoanDataProjection {
    String getProcessInstanceId();
    String getLead_id();
    // 其他字段对应的getter方法，名称要和SQL中的别名一致
}

修改查询返回投影类型：

@Query(value = "select data->>'processInstanceId' as processInstanceId,
 data->>'leadId_lap' as lead_id,
 -- 其他字段依次定义别名
 from kul_flat_vars where data->>'processInstanceId'=:processInstanceId", nativeQuery = true)
LoanDataProjection findData(@Param("processInstanceId") String processInstanceId);

用ObjectMapper将投影转为JSON：

LoanDataProjection projection = yourRepository.findData("xxx");
ObjectMapper mapper = new ObjectMapper();
String json = mapper.writeValueAsString(projection);

内容的提问来源于stack exchange，提问作者Umar