CODEWITHSUNDEEP
javaspring-bootjpajpql
vibetribe93
vibetribe93

Reputation: 267

Unable to pass a collection to a constructor via @Query

I am getting an SQL exception 

java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as col_7_0_ from locales offerlocal0_ cross join offers offer2_ inner join offer' at line 1

While calling the repository method

@Query("SELECT DISTINCT new com.greenflamingo.staticplus.model.catalog.dto.OfferGet(ol.root.id,ol.title "
            + ",ol.description,dl.name,ol.root.price,ol.root.currency,ol.root.visible,ol.root.images) "
            + "FROM OfferLocale ol,DescriptorLocale dl  "
            + "WHERE ol.root.webfront.id = (:webId) AND ol.culture.languageCode = (:langCode) "
            + "AND dl.culture.languageCode = (:langCode) "
            + "AND ol.root.category = dl.root")
    Page<OfferGet> findAllWebfrontLocalized(@Param("webId")int webfrontId,@Param("langCode")String langCode,Pageable pageable );

I have narrowed the issue down to the Collection i am trying to pass to constructor (ol.root.images) . Tried with List (it gave me a constructor missmatch) and with Set (had the same error as shown here) This is the bean i am using

public class OfferGet implements Serializable{

    private static final long serialVersionUID = 6942049862208633335L;
    private int id;
    private String title;
    private String shortDescription;
    private String price;
    private String category;
    private boolean visible;
    private List<Image> images;




    public OfferGet(String title, String category) {
        super();
        ..........
    }
    public OfferGet() {
        super();
    }
    public OfferGet(int id, String title, String description
            , BigDecimal price
            ,String currency,
            boolean visible) {

        .........
    }
    public OfferGet(int id, String title, String description,String category
            , BigDecimal price
            ,String currency,
            boolean visible,
            Collection<Image> images) {
        ..........
    }

}

I am using java 11, mariaDb and Springboot 2.0.5 Does anyone know why is this happening and if there is any way around it? Any help would be much appreciated, mustache gracias! :D

Upvotes: 0

Views: 471

Answers (1)

Simon Martinelli
Simon Martinelli

Reputation: 36133

It's not possible to create an object with the constructor expression that takes a collection as argument.

The result of a SQL query is always a table.

The reason is that identification variables such that they represent instances, not collections.

Additionally you cannot return root.images you must join the OneToMany relationship and then you no longer have a collection but each attribute.

The result of the query will be cartesian product.

This is a correct query:

@Query("SELECT DISTINCT new com.greenflamingo.staticplus.model.catalog.dto.OfferGet(ol.root.id,ol.title "
            + ",ol.description,dl.name,ol.root.price,ol.root.currency,ol.root.visible, image) "
            + "FROM OfferLocale ol,DescriptorLocale dl  "
            + "JOIN ol.root.images image
            + "WHERE ol.root.webfront.id = (:webId) AND ol.culture.languageCode = (:langCode) "
            + "AND dl.culture.languageCode = (:langCode) "
            + "AND ol.root.category = dl.root")

Upvotes: 1

Related Questions