Correct format of sql query in java

Question

I can seem to get the syntax correct for a jpa query towards the database. It works perfectly fine using squirrel SQL.

The database is Derby and the code is using JPA.

Updated with new query and error. This leads me to believe it's something in the entity mapping that is not correct. It might be something in the joinColumn clause?

Fixed the naming. that was causing the first problem with not using the correct entity names.

Query q = em.createQuery("select t, sum(t.result) from Serie t, Player p " +
        " where p.id = t.player" +
        " group by t.player");

Exception [EclipseLink-6076] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.QueryException
Exception Description: Object comparisons can only be used with OneToOneMappings.  Other mapping comparisons must be done through query keys or direct attribute level comparisons. 
Mapping: [org.eclipse.persistence.mappings.DirectToFieldMapping[id-->PLAYER.ID]] 
Expression: [
Query Key id
   Base com.jk.hcp.Player]
Query: ReportQuery(referenceClass=Serie jpql="select t, sum(t.result) from Serie t, Player p  where p.id = t.player group by t.player")
    org.eclipse.persistence.exceptions.QueryException.unsupportedMappingForObjectComparison(QueryException.java:1164)

Entities

public class Player implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;



    private String name;
    private static final long serialVersionUID = 1L;

    public Player() {
        super();
    }   

    public Long getId() {
        return this.id;
    }

    /*
    public void setId(Long id) {
        this.id = id;
    } 
    */

    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @ManyToOne(optional = false)
    @JoinColumn(name = "clubId", referencedColumnName = "id")
     private Club club;

    public Club getClub() {
        return club;
    }
    public void setClub(Club club) {
        this.club = club;
    }

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

public class Serie implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private int result;
    private static final long serialVersionUID = 1L;
    @Temporal(TemporalType.TIMESTAMP)
    private Date serieDate; //java.util.Date

    /***
     * Convert back and forth between string and date.
     * @return
     */
    public String getSerieDate()
    {
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String mDate = "";
        System.out.println(serieDate);
        try {
            mDate = df.format(serieDate);
        }
        catch (Exception ex) {
            //ex.printStackTrace();
        }

        return mDate; 
    }

    public void setSerieDate(String aTime) {
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date d = df.parse(aTime);
            serieDate = d;
        }
        catch (java.text.ParseException ex) {
            ex.printStackTrace();
        }
    }

    public Serie() {
        super();
    }   

    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }   
    public int getResult() {
        return this.result;
    }

    public void setResult(int result) {
        this.result = result;
    }

    @ManyToOne(optional = false)
    @JoinColumn(name = "clubId", referencedColumnName = "id")
     private Club club;

    public Club getClub() {
        return club;
    }
    public void setClub(Club club) {
        this.club = club;
    }

    @ManyToOne(optional = false)
    @JoinColumn(name = "playerId", referencedColumnName = "id")
     private Player player;

    public Player getPlayer() {
        return this.player;
    }
    public void setPlayer(Player player) {
        this.player = player;
    }

    @ManyToOne(optional = false)
    @JoinColumn(name = "serieTypeId", referencedColumnName = "id")
     private SerieType serieType;

    public SerieType getSerieType() {
        return this.serieType;
    }
    public void setSerieType(SerieType serieType) {
        this.serieType = serieType;
    }

}




public List getSeriesForPlayer(String clubName, String playerName)
{

    if (factory == null) {
        factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
    }

    EntityManager em = factory.createEntityManager();

    Query q = em.createQuery("select sum(result) as total, avg(result) as snitt, s.SERIEDATE, p.NAME, c.NAME " +
    " from jocke.serie s, jocke.player p, jocke.CLUB c" +
    " where s.PLAYERID = p.ID" +
    " and s.CLUBID = c.ID" +
    " and c.NAME = '" + "BK Strået" + "'" +
    " and p.NAME = '" + "Jocke" + "'" +
    " group by p.name, s.SERIEDATE, c.NAME");

    List resultList = q.getResultList();
    Object obj = resultList.get(0);

    em.close();

    return resultList;
}

xception Description: Syntax error parsing [select sum(result) as total, avg(result) as snitt, s.SERIEDATE, p.NAME, c.NAME  from jocke.serie s, jocke.player p, jocke.CLUB c where s.PLAYERID = p.ID and s.CLUBID = c.ID and c.NAME = 'BK Strået' and p.NAME = 'Jocke' group by p.name, s.SERIEDATE, c.NAME]. 
[11, 17] The encapsulated expression is not a valid expression.

Answer 1

In JPA queries you have to use the property names of the Entities. So don't use ID and PLAYER, but id and player.

I think something like this should work:

Query q = em.createQuery("select t, sum(t.result) from Serie t, Player p " +
                         "     where p = t.player" +
                         "     group by t.player");