java 查询sql_在java中正确的sql查询格式

我似乎可以获得对数据库的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.