在java中正确的sql查询格式

数据库是Derby,代码使用的是JPA.

更新了新的查询和错误.这让我相信它在实体映射中是不正确的.它可能是joinColumn子句中的某些内容？

修正了命名.这导致了第一个问题,即没有使用正确的实体名称.

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)

实体

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.