org.hibernate.hql.ast.QuerySyntaxException：意外的令牌

Question

我在使用Struts2和Hibernate时遇到了以下错误：

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 61 [SELECT s.codeOracle FROM sites as s INNER JOIN contrat as c on c.idSite = s.idSite INNER JOIN paiement as p on p.idcontrat = c.idcontrat INNER JOIN periodepay as pp on pp.idPaiement = p.idPaiement WHERE pp.statutPay = 1 group by s.codeOracle]
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)

我有这个方法：

@Override
    public List<Sites> listSearch() {
        List<Sites> site = null;
        String query="SELECT s.codeOracle FROM sites as s INNER JOIN contrat as c on c.idSite = s.idSite INNER JOIN Paiement as p on p.idcontrat = c.idcontrat INNER JOIN PeriodePay as pp on pp.idPaiement = p.idPaiement WHERE pp.statutPay = 1 group by s.codeOracle";
        try {
            site = session.createQuery(query).list();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return site;
    }

folling代码定义类之间的关系：

@Entity
@Table(name="Sites")
public class Sites {
    private Long                idSite;
    private Set<Contrat>        contratMaping;
    @Id
    @GeneratedValue
    @Column(name="idSite")  
    public Long getIdSite() {
        return idSite;
    }
    @OneToMany(mappedBy = "siteMaping")
    public Set<Contrat> getContratMaping() {
        return contratMaping;
    }
}

Contrat类：

@Entity
@Table(name = "Contrat")
public class Contrat{
    private Long                        idcontrat;  
    private Bailleur                    bailleurMaping;
    private Sites                       siteMaping;
    private Set<Paiement>               paiementMap;   

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Long getIdcontrat() {
        return idcontrat;
    }       
    @OneToMany(mappedBy = "contratMaping")
    public Set<Paiement> getPaiementMap() {
        return paiementMap;
    }
    @ManyToOne 
    @JoinColumn(name = "idBailleur")
    public Bailleur getBailleurMaping() {
        return bailleurMaping;
    }
    @ManyToOne 
    @JoinColumn(name = "idSite")
    public Sites getSiteMaping() {
        return siteMaping;
    }
}

Paiement类：

@Entity
@Table(name="Paiement")
public class Paiement {

    private Long                idPaiement;
    private Contrat             contratMaping ;
    private Set<PeriodePay>     periodePayMap;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="idPaiement")  
    public Long getIdPaiement() {
        return idPaiement;
    }   
    @OneToMany(mappedBy = "paimentMaping")
    public Set<PeriodePay> getPeriodePayMap() {
        return periodePayMap;
    }
    @ManyToOne
    @JoinColumn(name = "idcontrat")
    public Contrat getContratMaping() {
        return contratMaping;
    }
}

PeriodePay等级：

@Entity
@Table(name="PeriodePay")
public class PeriodePay {
    private Long                idPeriodePay;
    private Paiement            paimentMaping ;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="idPeriodePay")    
    public Long getIdPeriodePay() {
        return idPeriodePay;
    }
    @ManyToOne
    @JoinColumn(name = "idPaiement")
    public Paiement getPaimentMaping() {
        return paimentMaping;
    }
}

此查询在phpMyAdmin中工作良好；你能告诉我出了什么问题吗

编辑1：

我在试着别搞错了：

public List<Object[]> listSearch() {
        List<Object[]> site = null;
        try {
        site = session.createSQLQuery("Select * FROM sites as s " +
                                      "INNER JOIN contrat as c on c.idSite = s.idSite " +
                                      "INNER JOIN paiement as p on p.idcontrat = c.idcontrat " +
                                      "INNER JOIN periodepay as pp on pp.idPaiement = p.idPaiement " +
                                      "WHERE pp.statutPay = 1 group by s.codeOracle").list();
            for(Object[] arr : site){
                  System.out.println("Select * FROM sites   "+Arrays.toString(arr));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return site;
    }

我在控制台中得到结果：

INFOS: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Hibernate: Select * FROM sites as s INNER JOIN contrat as c on c.idSite = s.idSite INNER JOIN paiement as p on p.idcontrat = c.idcontrat INNER JOIN periodepay as pp on pp.idPaiement = p.idPaiement WHERE pp.statutPay = 1 group by s.codeOracle
Select * FROM sites   [1, Agadir, , , Aga-1212, Aga-1212, 2015-07-05, , , , false, , , , , 1, 7, 100.0, 2016-01-01, 2015-07-01, 2017-06-30, , 440.0, , , null, , 0.0, 1, 1, null, null, null, null, null, null, null, null, null, null, null, null, null, 13, null, null, null, null, null, null, , , testhamza, , null, , , 2, 7, 28, null, 2640.0, 0.0, true, 13, 2016-01-01, 2017-06-30]

但是使用jsp我在数据表中得不到结果，也许我必须修复迭代器

<s:if test="searchsiteList.size() > 0">
                    <div   id="mbox_inbox">
                    <table class="table table-striped table-condensed" id="dt_b">
                               <thead>
                                   <tr>
                                       <th>CodeOracle</th>
                                    <th>CodeGSM</th>
                                    <th>Area</th>
                                    <th>Date MiseOnAir</th>
                               </tr>
                               </thead>
                               <tbody>
                                   <s:iterator value="searchsiteList" status="userStatus">
                                   <tr class="<s:if test="#userStatus.odd == true ">odd</s:if><s:else>even</s:else>">
                                    <td><s:property value="codeOracle" /></td>
                                    <td><s:property value="codeGSM" /></td>
                                    <td><s:property value="area" /></td>
                                    <td><s:property value="dateMiseOnAir" /></td>

                                </tr>
                                   </s:iterator>
                               </tbody>
                           </table> 
                    </div>
                    </s:if>

Answer 1

这是一个HQL语法错误。HQL与对象一起工作，具有不同于SQL的语法。如果要执行SQL查询，则应使用

session.createSQLQuery(query)

list()方法返回list 。

在action类中

private List<Object[]> searchsiteList = new ArrayList<Object[]>(); 

public List<Object[]> getSearchsiteList() { return searchsiteList; }

但是这段代码只在JSP中绑定SearchSiteList。要绑定其他属性，您应该创建一个bean或使用一个映射。

public List<Map<String, Object>> convertToMapsList(List<Object[]> list){
  List<Map<String, Object>> res = new ArrayList<>();
  for(Object[] arr : list){
    Map<String, Object> map = new HashMap<>();
    map.put("codeOracle", arr[0]);
    map.put("codeGSM", arr[1]);
    map.put("area", arr[2]);
    map.put("dateMiseOnAir", arr[3]);
    res.add(map);
  }
  return res;
}

和更改

public List<Map<String, Object>> getSearchsiteList() { return convertToMapsList(searchsiteList); }