Spring projections select collection

Question

I am attempting to have a station projection include a list of associated logos. Below is my domain:

@Table(name = "Station")
public class Station implements Serializable {

@Id
@Column(name = "Id")
private int id;

@OneToMany(cascade = CascadeType.ALL,
        fetch = FetchType.LAZY,
        mappedBy = "station")
private Set<Logo> logos;
}

The @OneToMany associated logos:

@Table(name = "Logo")
public class Logo {

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

@Transient
private String fullUrl; // calculated

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "StationId", nullable = false)
private Station station;
}

My repository and query is as follows:

@Query(value = "SELECT s.id AS Id, s.logos As Logos FROM Station s JOIN s.users su WHERE su.username = ?1")
Collection<StationListViewProjection> findStationsByUsername(String username);

My station projection expects the Id and a list of logoProjections

@Projection(name = "StationListViewProjection", types = Station.class)
public interface StationListViewProjection {
   int getId();
   Set<LogoProjection> getLogos();
}

The logoProjection only needs the url

@Projection(name = "LogoProjection", types = Logo.class)
public interface LogoProjection {
   String getFullUrl();
}

When i execute my query I get a strange response:

MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as col_5_0_, . as col_6_0_, stationlog3_.id as id1_20_0_'

Answer 1

If I understand this

@Transient
private String fullUrl; // calculated

correct, your fullUrl gets calculated inside your java code and more important, it doesn't have a matching column in the database. You can't use such field in projections directly. You might be able to use an Open Projection and specify the calculation to obtain the fullUrl using a @Value annotation.