java - java中sql查询的正确格式

Question

对于对数据库的 jpa 查询，我似乎可以得到正确的语法。使用 squirrel 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, 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)

实体

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

在 JPA 查询中，您必须使用实体的属性名称。所以不要使用 ID 和 PLAYER，而是使用 id 和 player。

我认为这样的事情应该可行:

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