CODEWITHSUNDEEP
sqlspring-bootspring-data-jpajpql
GSUgambit
GSUgambit

Reputation: 4889

JPQL - Spring Boot Repository Query - Map Property

I have a map property in my domain class and I'm attempting to create a query in the repository or use the default "findByBlah" syntax to pull property from database. Will not work currently. I can easily write the query in SQL but I have no idea what JPQL expects. How can I pull this data from database using JPQL or the interface "findBy" syntax? No matter which repository method I current use, when it pulls the "Collector" from the database, the attribute (map or list of complex object) is always null.

Domain Object:

@Builder
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "collector")
public class Collector {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "collector_id")
    private Long id;

    @NotNull
    private String name;

    @ElementCollection
    @MapKeyColumn(name = "attribute_name")
    @Column(name = "attribute_value")
    @CollectionTable(name = "collector_attributes", joinColumns = @JoinColumn(name = "collector_id"))
    private Map<String, String> attributes;

    @Override
    public String toString() {
        return ObjectUtil.print(this);
    }
}

Repository:

public interface CollectorRepository extends PagingAndSortingRepository<Collector, Long> {

    @Query(value = "select c from Collector c where c.attributes[$1] = $2")
    Page<Collector> findByAttributesNameAndValue(String name, String value, Pageable pageable);
}

Here is the query that works in the H2 Console:

SELECT * FROM Collector a INNER JOIN collector_attributes b ON a.collector_id = b.collector_id where b.attribute_name= 'nickName' and b.attribute_value  = 'Critikon'

enter image description here

Upvotes: 0

Views: 652

Answers (1)

ValerioMC
ValerioMC

Reputation: 3166

The only this is working to me is to define an Object relation instead of a map:

    @ElementCollection
    @CollectionTable(name = "COLLECTOR_ATTRIBUTES", joinColumns = @JoinColumn(name = "COLLECTOR_ID"))
    private List<CollectorAttribute> attributes;

And the enbedded object

@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@Embeddable
public class CollectorAttribute {

    @Column(name = "ATTRIBUTE_NAME")
    private String key;

    @Column(name = "ATTRIBUTE_VALUE")
    private String value;
}

now you can query using properties of embedded object 

public interface CollectorRepository extends JpaRepository<Collector, Long> {
    List<Collector> findByAttributesKeyAndAttributesValue(String key, String value);
}

To retrieve COLLECTOR_ATTRIBUTES in select we can define a @NamedEntityGraph in Collector class and repository method as follows

@NamedEntityGraph(name = "Collector.attributes",
    attributeNodes = @NamedAttributeNode("attributes"))
@Entity
@Table(name = "COLLECTOR")
public class Collector {

and report @EntityGraph in Repository method

public interface CollectorRepository extends JpaRepository<Collector, Long> {

    @EntityGraph(value = "Collector.attributes", type = EntityGraph.EntityGraphType.LOAD)
    List<Collector> findByAttributesKeyAndAttributesValue(String key, String value);
    }
}

Now you have also attributes

If you want to load attributes also on other methods, you have to use @EntityGraph

@EntityGraph(value = "Collector.attributes", type = EntityGraph.EntityGraphType.LOAD)
Optional<Collector> findById(Long var1);

Upvotes: 1

Related Questions