mysql - 将 SQL 翻译成 JPQL(SELECT NEW 和 "AS"关键字)

Question

我是 JPQL 的新手，但我需要将一个简单的 SQL 请求转换为 JPQL:数据库:

SELECT 
  *, 
  t.column1 + t.column2 as coefficient 
FROM 
  practice.People as t
ORDER BY coefficient

我无法使用该原生查询，因为我不知道如何将它映射到类 :(我尝试使用这个 JPQL 查询:

  SELECT NEW entities.PersonWell(c.peopleId, c.name, c.age, c.height, c.weight, 1)
  FROM 
    People AS c
  ORDER BY c.age;

如何在此请求中将“1”更改为系数？我想象的唯一解决方案就是使用:

SELECT NEW entities.PersonWell(c.peopleId, c.name, c.age, c.height, c.weight, c.age + c.height)
FROM 
  People AS c
ORDER BY c.age + c.height;

但我认为这不是正确的解决方案 :( 我该怎么办？

Infragile，感谢您的回答!

但是还是不行:(这是将您的答案应用于我的代码的结果:

public List<PersonWell> findWell(People person){
    String queryString = 
            "SELECT\n"
            + "NEW entities.PersonWell(c.peopleId, c.name, c.age, c.height, c.weight, c.speechspeed, "
            + "ABS(c.age - :age) + ABS(c.height - :height) + ABS(c.weight - :weight) + ABS(c.speechspeed - :speechspeed)) AS p \n" 
           +"FROM "
            + "People AS c\n"
           +"ORDER BY p.compatibility DESC";
    Query query = em.createQuery(queryString);
    query.setParameter("age", person.getAge());
    query.setParameter("height", person.getHeight());
    query.setParameter("weight", person.getWeight());
    query.setParameter("speechspeed", person.getSpeechspeed());
    List<PersonWell> resultList = query.getResultList();
    return resultList;
}

这是来自 glassfish 服务器的回答:

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Error compiling the query [SELECT
NEW entities.PersonWell(c.peopleId, c.name, c.age, c.height, c.weight, c.speechspeed, ABS(c.age - :age) + ABS(c.height - :height) + ABS(c.weight - :weight) + ABS(c.speechspeed - :speechspeed)) AS p 
FROM People AS c
ORDER BY p.compatibility DESC], line 4, column 9: invalid navigation expression [p.compatibility], cannot navigate expression [p] of type [entities.PersonWell] inside a query.

Answer 1

这样的东西对你不起作用？

SELECT NEW entities.PersonWell(c.peopleId, c.name, c.age, c.height, c.weight, c.age + c.height) AS p
FROM 
  People AS c
ORDER BY p.coefficient;

p.coefficient 是字段，您的值 c.age + c.height 使用 PersonWell 构造函数放置。