Hibernate to execute multiple native sql statements

Question

How can I execute Multiple SQL statements in a single sql query using hibernate native sql.

String sql = "SELECT * FROM user; SELECT * FROM product;";
UserVO valueObject = new UserVO();
databaseObject.select(sql, valueObject);

Database Object

public List select(String sql, Object valueObject) throws Exception {
    Session session = Entitlement.getSessionFactory().openSession();
    session.beginTransaction();
    List list = session.createSQLQuery(sql).setProperties(valueObject).list();
    session.close();
    return list;
}

I don't understand what you are trying to do. What do you expect the `List` returned by `select` to contain? — Rob, Sep 28 '15 at 21:53
my expectation is a list of both `user` and `product` in a nested list, or how it works out there for multiple sql statements? — user2727195, Sep 28 '15 at 21:55
Possible duplicate: http://stackoverflow.com/questions/4831672/hibernate-multiple-result-sets — Rob, Sep 28 '15 at 22:01
there is something called UNION clause in sql you may try using. You will be able to execute the query as you want. Also remember you would need to do aliasing to make sure that the column name are similar. select * is not a valid operation IMO — Acewin, Oct 02 '15 at 17:13

score 0 · Answer 1 · answered Oct 02 '15 at 17:19

0

Use union to form a query which has same returning data (Select EMPLOYEEID as id, EMPLOYEE_NAME as name, "EMPOYEE" as type) UNION (SELECT PRODUCTID as id, Product_NAME as name, "PRODUCT" as type)

form an Entity to hold it

class EntityDetail {
    String id;
    String name;
    String type;
}

I have added an additional column value type to simply identify from which table row is coming from. And yes you will need to form a proper Entity with all valid annotations the above Entity is just for example.

answered Oct 02 '15 at 17:19

Acewin

1,657
4
17
36

confining to same structure data, the query can request data from multiple tables with varying structures – user2727195 Oct 02 '15 at 20:37
@user2727195 If understand what you have asked in plain English your question "How can I execute Multiple SQL statements in a single sql query using hibernate native sql." which means you want to fetch all the results in one single hit. Whereas what you are doing right now (based on your solution approach) you are executing multiple queries and storing it all in a List. – Acewin Oct 05 '15 at 23:37

user2727195 · Accepted Answer · 2015-10-02T17:00:17.907

-3

just a lateral approach.

public List<List<Object[]>> execute(String sqls, Object valueObject) throws Exception {
    String[] queries = sqls.split(";");
    List<List<Object[]>> result = new ArrayList<>();

    for(int i=0; i<queries.length; i++) {
        result.add(this.select(queries[i], valueObject));
    }
    return result;
}

edited Oct 02 '15 at 17:00

answered Sep 28 '15 at 22:58

user2727195

7,122
17
70
118

Ummm.... You are separately issuing the queries to the DB, so apparently it didn't work for you. – Rob Sep 29 '15 at 01:00
is there a solution for hibernate to execute multiple queries? I believe my attempt is describing my expectation – user2727195 Sep 29 '15 at 01:27
I think you are just bluffing your self using this approach , you are executing each query separately from the other . – Mohammad Apr 11 '19 at 06:33

Hibernate to execute multiple native sql statements

2 Answers2