java - Spring Boot、JPA/Hibernate : How to execute two raw SELECT queries at once?

Question

当我尝试一次执行两个 SELECT 语句(如下所示)时，日志记录控制台返回运行时错误:

java.sql.SQLSyntaxErrorException:您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在“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;

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

如何在单个查询中执行这两个 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());