JPA query with GROUP BY in Spring Data on Oracle database

Question

I'm using Oracle as database and Spring Data on a project with jBoss AS.

A simple query with a GROUP BY doesn't work.
This is the Repository code:

public interface UseCaseRepository extends JpaRepository<UseCase, Long> {

  @Query("SELECT uc.channel FROM UseCase uc WHERE uc.activity = ?1 GROUP BY uc.channel")
  public List<Channel> getUseCaseChannelByActivity(Activity activity);

[...]

The error is shown below

2016-06-29 17:22:12,780 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-443-3) SQL Error: 979, SQLState: 42000
2016-06-29 17:22:12,780 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-443-3) ORA-00979: not a GROUP BY expression

The same query with a MySQL database has no problem.

These are the entities

@Entity
@Table(name="USE_CASE")
@NamedQuery(name="UseCase.findAll", query="SELECT u FROM UseCase u")
public class UseCase implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="USE_CASE_ID")
    private long useCaseId;

    //uni-directional many-to-one association to Activity
    @ManyToOne
    @JoinColumn(name="ACTIVITY_ID")
    private Activity activity;

    //uni-directional many-to-one association to Channel
    @ManyToOne
    @JoinColumn(name="CHANNEL_ID")
    private Channel channel;

    //uni-directional many-to-one association to PushTool
    @ManyToOne
    @JoinColumn(name="PUSH_TOOL_ID")
    private PushTool pushTool;

    //uni-directional many-to-one association to Tool
    @ManyToOne
    @JoinColumn(name="TOOL_ID")
    private Tool tool;

    //uni-directional many-to-many association to DataSource
    @ManyToMany
    @JoinTable(
        name="USE_CASE_DATA_SOURCE"
        , joinColumns={
            @JoinColumn(name="USE_CASE_ID")
            }
        , inverseJoinColumns={
            @JoinColumn(name="DATA_SOURCE_ID")
            }
        )
    private List<DataSource> dataSources;
[...]


@Entity
@NamedQuery(name="Activity.findAll", query="SELECT a FROM Activity a")
public class Activity implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="ACTIVITY_ID")
    private long activityId;

    private String attribute;

    @Column(name="\"TYPE\"")
    private String type;
[...]


@Entity
@NamedQuery(name="Channel.findAll", query="SELECT c FROM Channel c")
public class Channel implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="CHANNEL_ID")
    private long channelId;

    private String attribute;

    @Column(name="\"TYPE\"")
    private String type;
[...]

Any idea how to solve? Thanks

This may be a bad or irrelevant question, but what does `WHERE uc.activity = ?1` mean? I would have expected to see `WHERE uc.activity = ?`, with just a single question mark denoting the parameter. — Tim Biegeleisen, Jun 29 '16 at 16:11
i think it was anwered here http://stackoverflow.com/questions/20074562/group-by-without-aggregate-function — m.ostroverkhov, Jun 29 '16 at 16:38
You probably don't even need a group by query, in fact, if you're resolving a channel by activity, you would do `SELECT distinct(uc.channel) FROM UseCase uc WHERE uc.activity = ?` — Finbarr O'B, Jun 29 '16 at 16:57
Your SQL is likely using the CHANNEL_ID foreign key, but your JPQL is using a relationship and so trying to group by every field within the channel entity. Try using "SELECT uc.channel.id FROM UseCase uc WHERE uc.activity = ?1 GROUP BY uc.channel.id" or make it more obvious with "SELECT channel.id FROM UseCase uc join uc.channel channel WHERE uc.activity = ?1 GROUP BY channel.id" — Chris, Jun 29 '16 at 18:23
@TimBiegeleisen The number after the ? is the position of the parameter on the method signature. For example: `public List getUseCaseChannelByActivity(Activity activity, Channel channel);` ` ... WHERE uc.activity = ?1 AND uc.channel = ?2 ... ` — notting65, Jun 30 '16 at 07:43

score 0 · Answer 1 · answered Jun 29 '16 at 16:52

You MUST include all on aggregate expressions in the SELECT list when you use a GROUP BY clause in your query.

I think here the query generated by Hibernate is

SELECT uc.id, uc.user_case_id,[...] FROM use_case uc WHERE [...] GROUP BY uc.channel_id

(Try to check the SQL query generated by Hibernate, it's always helpful !)

JPA query with GROUP BY in Spring Data on Oracle database

1 Answers1