java - How to use JPA for column of type JSON in entity of type Map - Stack Overflow

admin管理员组

文章数量:1123190

I have a usecase where I want to persist some configuration (flag name + boolean state pairs) into DB. I have picked the way to persist it as JSON due to internal reasons.

I am able to persist the data, but fetching persisted ones is failing.

Simplified table for storing data

CREATE TABLE "SCHEMA"."CONFIG" (    
    "ID" NUMBER(38,0) NOT NULL ENABLE, 
    "ITEMS" JSON DEFAULT '' NOT NULL ENABLE, 
     CONSTRAINT "PK_CONFIG" PRIMARY KEY ("ID")
   )

Related entity which should reflect the DB table:

@Entity
@Table(name = "CONFIG")
@SequenceGenerator(name = "ConfigIdGenerator", sequenceName = "CONFIG_ID_SEQ", allocationSize = 1)
public class ConfigEntity {
    
    private long id;
    private Map<String, Boolean> items;

    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ConfigIdGenerator")
    @Column(name = "ID", nullable = false)
    @Id
    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    @Convert(converter = ConfigItemsConverter.class)
    @Column(name = "ITEMS", nullable = false, columnDefinition = "JSON")
    public Map<String, Boolean> getItems() {
        return items;
    }

    public void setItems(Map<String, Boolean> items) {
        this.items = items;
    }   
}

Used converter which should ensure MAP <=> STRING conversion:

@Converter
public class ConfigItemsConverter implements AttributeConverter<Map<String, Boolean>, String> {
    
    private static final ObjectMapper MAPPER = new ObjectMapper();
    
    @Override
    public String convertToDatabaseColumn(Map<String, Boolean> value) {
        try {
            return MAPPER.writeValueAsString(value);
        } catch (JsonProcessingException ex) {
            // Should not happen. Map values should be in correct format due to generics.
            ex.printStackTrace();
        }
        return null;
    }

    @Override
    public Map<String, Boolean> convertToEntityAttribute(String dbValue) {
        try {
            return MAPPER.readValue(dbValue, new TypeReference<Map<String, Boolean>>() {});
        } catch (JsonProcessingException ex) {
            // Should not happen. DB data are in JSON format due to JSON datatype
            ex.printStackTrace();
        }
        return null;
    }

}

When I try to persist new data, it works correctly. The converter calls convertToDatabaseColumn method, do the conversion and data are stored in the DB.

On the other hand, when I try to fetch those data (query.setMaxResults(1).getResultStream()...), I get the exception

jakarta.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.2.v202306161219): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Invalid column type: getOracleObject not implemented for class oracle.jdbc.driver.T4CJsonAccessor
Error Code: 17004
Call: SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (SELECT t1.ID AS a1, t1.ITEMS AS a2 FROM MAINMENU_SETTING t1 ) a WHERE ROWNUM <= ?) WHERE rnum > ?
    bind => [1, 0]
Query: ReadAllQuery(name="ConfigEntity.FIND_ALL" referenceClass=ConfigEntity sql="SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (SELECT t1.ID AS a1, t1.ITEMS AS a2 FROM MAINMENU_SETTING t1 ) a WHERE ROWNUM <= ?) WHERE rnum > ?")
    at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:392) ~[org.eclipse.persistence.jpa-4.0.2.jar:?]
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:265) ~[org.eclipse.persistence.jpa-4.0.2.jar:?]
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:483) ~[org.eclipse.persistence.jpa-4.0.2.jar:?]
    at jakarta.persistence.TypedQuery.getResultStream(TypedQuery.java:93) ~[jakarta.persistence-api-3.2.0.jar:3.2.0]
    ...
Caused by: java.sql.SQLException: Invalid column type: getOracleObject not implemented for class oracle.jdbc.driver.T4CJsonAccessor
    at oracle.jdbc.driver.GeneratedAccessor.getOracleObject(GeneratedAccessor.java:1221) ~[ojdbc11-21.10.0.0.jar:21.10.0.0.0]
    at oracle.jdbc.driver.JsonAccessor.getObject(JsonAccessor.java:263) ~[ojdbc11-21.10.0.0.jar:21.10.0.0.0]
    at oracle.jdbc.driver.GeneratedStatement.getObject(GeneratedStatement.java:196) ~[ojdbc11-21.10.0.0.jar:21.10.0.0.0]
    at oracle.jdbc.driver.GeneratedScrollableResultSet.getObject(GeneratedScrollableResultSet.java:334) ~[ojdbc11-21.10.0.0.jar:21.10.0.0.0]
    at oracle.ucp.jdbc.proxy.oracle.ResultSetProxy.getObject(ResultSetProxy.java:152) ~[ucp11-21.10.0.0.jar:21.10.0.0.0]
    at oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1ResultSetProxy$2oracle$1jdbc$1internal$1OracleResultSet$$$Proxy.getObject(Unknown Source) ~[ucp11-21.10.0.0.jar:21.10.0.0.0]
    at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.getObjectFromResultSet(DatabasePlatform.java:1230) ~[org.eclipse.persistence.core-4.0.2.jar:?]
    at org.eclipse.persistence.platform.database.OraclePlatform.getObjectFromResultSet(OraclePlatform.java:575) ~[org.eclipse.persistence.core-4.0.2.jar:?]
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.getObject(DatabaseAccessor.java:1375) ~[org.eclipse.persistence.core-4.0.2.jar:?]
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.fetchRow(DatabaseAccessor.java:1094) ~[org.eclipse.persistence.core-4.0.2.jar:?]

The conversion method convertToEntityAttribute is never called.

Can you provide me some clue, why the conversion works in one way only? What is missing and how can I fix it?

Thank you

本文标签： javaHow to use JPA for column of type JSON in entity of type MapStack Overflow