Spring Boot，JPA/Hibernate:如何同时执行两个原始SELECT查询

Question

当我尝试按如下方式同时执行两个SELECT语句时，日志控制台返回运行时错误：

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM ...

生成语句的Java源代码：

@Repository
public class VehicleObjectDbAccess {
@PersistenceContext
EntityManager entityManager;
public List<Object[]>  getObjectById(long objectId, long year)     
{
int limit = 10;
String tableName = ("i0i"+year)+objectId;
String queryText =
"START TRANSACTION;"
+ "SELECT t.created INTO @startTime FROM ObjectTable as t WHERE t.speed > 30 LIMIT 1;"
+ "SELECT * FROM ObjectTable WHERE created <= (CASE WHEN @startTime IS NULL THEN NOW() ELSE @startTime END) ORDER BY created DESC LIMIT 10;"
+ "COMMIT;";
Query query = this.entityManager.createNativeQuery(queryText);
return query.getResultList();
}
}

最终，上面的java源代码转换为

START TRANSACTION; 
SELECT t.created INTO @startTime FROM ObjectTable as t WHERE t.speed > 30 LIMIT 1;
SELECT * FROM ObjectTable WHERE created <= (CASE WHEN @startTime IS NULL THEN NOW() ELSE @startTime END) ORDER BY created DESC LIMIT 10; 
COMMIT;

我验证了在MySQL客户端上运行它的SQL代码，它工作正常。

如何在单个查询中执行这两个SELECT语句？

Answer 1

似乎可以将复合sql查询划分为两个独立的SELECT查询：

Query query1 = this.entityManager.createNativeQuery(queryText1);
Query query2 = this.entityManager.createNativeQuery(queryText2);

之后，你可以从他们那里获得结果列表，并将结果列表添加到一个复合列表中：

List<Object[]> result = new ArrayList<>();
result.addAll(query1.getResultList());
result.addAll(query2.getResultList());

Answer 2

只需使用一个select语句：

SELECT *, (SELECT t.created FROM ObjectTable as t WHERE t.speed > 30 LIMIT 1) as x FROM ObjectTable WHERE created <= 
(CASE WHEN x IS NULL THEN NOW() 
ELSE x
END) 
ORDER BY created DESC LIMIT 10;

如果您出于某种原因不想这样做，请创建一个返回结果集的存储过程并将其称为…