Spring boot integration with Postgres jsonb

Question

I am writing an application which is based on spring boot and tries to store and retrieve data from PostGreSQL db in its jsonb column. while saving the record it works fine but the moment i write basic method to find records in repository interface of it like this:-

public interface AgentProfileRepository extends CrudRepository<AgentProfileOuter,String> {

    public AgentProfileOuter findByJdataPcpAgentId(String id);

} 

then server starts giving this exception while restarting:-

Caused by: java.lang.IllegalStateException: Illegal attempt to dereference path source [null.jdata] of basic type
    at org.hibernate.jpa.criteria.path.AbstractPathImpl.illegalDereference(AbstractPathImpl.java:98) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.jpa.criteria.path.AbstractPathImpl.get(AbstractPathImpl.java:191) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
    at org.springframework.data.jpa.repository.query.QueryUtils.toExpressionRecursively(QueryUtils.java:524) ~[spring-data-jpa-1.9.4.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.QueryUtils.toExpressionRecursively(QueryUtils.java:478) ~[spring-data-jpa-1.9.4.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.JpaQueryCreator$PredicateBuilder.getTypedPath(JpaQueryCreator.java:300) ~[spring-data-jpa-1.9.4.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.JpaQueryCreator$PredicateBuilder.build(JpaQueryCreator.java:243) ~[spring-data-jpa-1.9.4.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.JpaQueryCreator.toPredicate(JpaQueryCreator.java:148) ~[spring-data-jpa-1.9.4.RELEASE.jar:na]

The point to wonder is when I try to find by id which is a normal numeric column in postgres it works fine but not if i try to find by a key inside json.This thing works successfully with MongoDB.

Here are the bean classes written:-

AgentProfileOuter.java

import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;



@Table(name = "Agentbonds")
@Entity

public class AgentProfileOuter {


     @GeneratedValue(strategy=GenerationType.AUTO)
     @Id
    private long id;

    @Convert(converter = ConverterAgent.class)
    @Column(name="jdata")
    private AgentProfile jdata;





    public long getId() {
        return id;
    }

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

    public AgentProfile getJdata() {
        return jdata;
    }

    public void setJdata(AgentProfile jdata) {
        this.jdata = jdata;
    }

}

AgentProfile.java

import java.util.HashMap;
import java.util.Map;

import javax.annotation.Generated;
import javax.persistence.Convert;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Table;

import org.springframework.data.annotation.Id;

import com.fasterxml.jackson.annotation.JsonAnyGetter;
import com.fasterxml.jackson.annotation.JsonAnySetter;
import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.annotation.JsonPropertyOrder;

@JsonInclude(JsonInclude.Include.NON_NULL)
@Generated("org.jsonschema2pojo")
@JsonPropertyOrder({
    "pcpAgentId",
    "name",
    "bio",
    "phone",
    "email",
    "sms",
    "imageUrl"
})





public class AgentProfile {



    @JsonProperty("pcpAgentId")
    private String pcpAgentId;

    /*
    public void setAdditionalProperties(Map<String, Object> additionalProperties) {
        this.additionalProperties = additionalProperties;
    }
    */

    @JsonProperty("name")
    private String name;
    @JsonProperty("bio")
    private String bio;
    @JsonProperty("phone")
    private String phone;
    @JsonProperty("email")
    private String email;
    @JsonProperty("sms")
    private String sms;
    @JsonProperty("imageUrl")
    private String imageUrl;
    @JsonIgnore
    private Map<String, Object> additionalProperties = new HashMap<String, Object>();

    /**
     * 
     * @return
     *     The pcpAgentId
     */
    @JsonProperty("pcpAgentId")
    public String getpcpAgentId() {
        return pcpAgentId;
    }

    /**
     * 
     * @param pcpAgentId
     *     The pcpAgentId
     */
    @JsonProperty("pcpAgentId")
    public void setAgentId(String pcpAgentId) {
        this.pcpAgentId = pcpAgentId;
    }

    /**
     * 
     * @return
     *     The name
     */
    @JsonProperty("name")
    public String getName() {
        return name;
    }

    /**
     * 
     * @param name
     *     The name
     */
    @JsonProperty("name")
    public void setName(String name) {
        this.name = name;
    }

    /**
     * 
     * @return
     *     The bio
     */
    @JsonProperty("bio")
    public String getBio() {
        return bio;
    }

    /**
     * 
     * @param bio
     *     The bio
     */
    @JsonProperty("bio")
    public void setBio(String bio) {
        this.bio = bio;
    }

    /**
     * 
     * @return
     *     The phone
     */
    @JsonProperty("phone")
    public String getPhone() {
        return phone;
    }

    /**
     * 
     * @param phone
     *     The phone
     */
    @JsonProperty("phone")
    public void setPhone(String phone) {
        this.phone = phone;
    }

    /**
     * 
     * @return
     *     The email
     */
    @JsonProperty("email")
    public String getEmail() {
        return email;
    }

    /**
     * 
     * @param email
     *     The email
     */
    @JsonProperty("email")
    public void setEmail(String email) {
        this.email = email;
    }

    /**
     * 
     * @return
     *     The sms
     */
    @JsonProperty("sms")
    public String getSms() {
        return sms;
    }

    /**
     * 
     * @param sms
     *     The sms
     */
    @JsonProperty("sms")
    public void setSms(String sms) {
        this.sms = sms;
    }

    /**
     * 
     * @return
     *     The imageUrl
     */
    @JsonProperty("imageUrl")
    public String getImageUrl() {
        return imageUrl;
    }

    /**
     * 
     * @param imageUrl
     *     The imageUrl
     */
    @JsonProperty("imageUrl")
    public void setImageUrl(String imageUrl) {
        this.imageUrl = imageUrl;
    }

    @JsonAnyGetter
    public Map<String, Object> getAdditionalProperties() {
        return this.additionalProperties;
    }

    @JsonAnySetter
    public void setAdditionalProperty(String name, Object value) {
        this.additionalProperties.put(name, value);
    }

}

Any help on this is greatly appreciated.

Answer 1

I think, it's because how both Mongo and PostGreSQL structure the data. From Mongo's point, AgentProfileOuter is one document saved in JSON format as key:value. Every field in your AgentProfile class is key for Mongo irrespective of the fact it's another/ child object. However, for PostGreSQL whole AgentProfile object is just one column of String blob, since you have not marked this class as @Entity and it does not have a primary id. So, when you try to search something like pcpAgentId=someid, it does not make any sense to PostGreSQL. This is my guess, verify by checking your data structure in PostGreSQL.

Also noticed that CrudRepository<AgentProfileOuter,String> should be like CrudRepository<AgentProfileOuter,long> since AgentProfilOuter class's primary key is long.