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

Question

When I try to execute two SELECT statements at once as follows below, the logging console returns a runtime error:

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 ...

The Java source code that generates the statements:

@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();
    }
}

Eventually the java source code above translates to

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;

I verified that SQL code running it on a MySQL client and it works properly.

How can I execute these two SELECT statements in a single query?

mpoznyak · Accepted Answer · 2020-03-05 15:00:37Z

1

It seems you can divide your compound sql query to two separate SELECT queries:

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

After that you can get result lists from them and add result lists to one compound List:

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

answered Mar 5, 2020 at 15:00

mpoznyak

1311 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

0xC0DEGURU Over a year ago

I want to avoid dividing them into chunks, because doing so will lead to a greater computational time. I'm aiming to the fastest execution time.

mikeb Over a year ago

"Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs" - Donald Knuth in "Structured Programming With Go To Statements" - 1974

julaine Over a year ago

@mikeb Stackoverflowers waste enormous amounts of time thinking about, or worrying about, strangers in unknown codebases doing premature optimization.

mikeb · Accepted Answer · 2020-03-05 15:18:17Z

1

Just use one select statement:

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;

If you don't want to do this for some reason, create a stored procedure that returns a result set and call it...

edited Mar 5, 2020 at 15:18

answered Mar 5, 2020 at 15:01

mikeb

11.5k9 gold badges74 silver badges135 bronze badges

4 Comments

0xC0DEGURU Over a year ago

I want to avoid nested SELECT statements. The queries in my question are shortened for simplicity. The actual queries are longer. Combining them into a single nested SELECT statement would make the query unreadble.

mikeb Over a year ago

See my edits - create a stored procedure in your DB and call it - that would be my alternative.

0xC0DEGURU Over a year ago

Unfortunately, I cannot use stored procedure because the name of database table is going to be dynamic and MySQL doesn't allow me to specify the table name on the go in a procedure.

mikeb Over a year ago

So use the nested selects. You are going to spend an absurd amount your time trying to make 3-5 lines of your solution "readable" (this is very subjective anyway), fast and "correct". At some point it has to work so you can go on to other things.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related