Correct SQL query format in Java

I seem to be able to get the syntax correct for JPA queries to the database Squirrel SQL works well

The database is Derby, and the code uses JPA

Updated new queries and errors This makes me believe that it is incorrect in entity mapping Could it be something in the join column clause?

Fixed naming This leads to the first problem, that is, the correct entity name is not used

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,Player p  where p.id = t.player group by t.player")
    org.eclipse.persistence.exceptions.QueryException.unsupportedMappingForObjectComparison(QueryException.java:1164)

entity

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,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,c.NAME  from jocke.serie s,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,c.NAME]. 
[11,17] The encapsulated expression is not a valid expression.

Solution

In a JPA query, you must use the attribute name of the entity So instead of using ID and player, use ID and player

I think such things should be effective:

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