msagala msagala - 19 days ago 5
SQL Question

Is there a way in hibernate that can manipulate many query in one String

Im using hibernate in my web project. And Im executing this line of

String
in
createSQLQuery(queryString)
. below is the value I assigned in
queryString
:

DROP TABLE monthlyevaluatedbudgettabletemp;
DROP TABLE monthlyadjustedbudgettabletemp;

CREATE TABLE monthlyevaluatedbudgettabletemp (budgetid bigint NOT NULL,
approvedbudget numeric(19,2),
april numeric(19,2),
august numeric(19,2),
budgetforyear bigint NOT NULL,
december numeric(19,2),
february numeric(19,2),
january numeric(19,2),
july numeric(19,2),
june numeric(19,2),
march numeric(19,2),
may numeric(19,2),
november numeric(19,2),
october numeric(19,2),
september numeric(19,2),
branchid integer,
accountid bigint,
budgetlastyear bigint);

CREATE TABLE monthlyadjustedbudgettabletemp (adjustedid bigint NOT NULL,
april numeric(19,2),
august numeric(19,2),
december numeric(19,2),
february numeric(19,2),
january numeric(19,2),
july numeric(19,2),
june numeric(19,2),
march numeric(19,2),
may numeric(19,2),
november numeric(19,2),
october numeric(19,2),
september numeric(19,2),
yeartodatemonth character varying(255),
budgetid bigint);

INSERT INTO monthlyevaluatedbudgettabletemp SELECT * FROM monthlyevaluatedbudgettable where budgetforyear=2017;
INSERT INTO monthlyadjustedbudgettabletemp SELECT a.* from monthlyadjustedbudgettable as a join monthlyevaluatedbudgettable as e on a.budgetid=e.budgetid where e.budgetforyear = 2017

UPDATE monthlyevaluatedbudgettabletemp SET budgetforyear=2017,budgetlastyear=2016,april=0.00,approvedbudget=0.00,august=0.00,february=0.00,january=0.00,july=0.00,june=0.00,march=0.00,may=0.00,november=0.00,october=0.00,september=0.00,december=0.00;

UPDATE monthlyadjustedbudgettabletemp SET january=0.00,february=0.00,march=0.00,april=0.00,may=0.00,june=0.00,july=0.00,august=0.00,september=0.00,november=0.00,
october=0.00,december=0.00;


now when I run it with
createSQLQuery(queryString).executeUpdate();
. I'm encountering this kind of error.

org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query


Now my question, is there a way that I can manipulate query in one shot String. or do I have to separate each query statement and execute it one by one. It is like I want to execute a
.sql
file in hibernate.

Answer

You cannot run multiple native queries at one go in Hibernate.

You will have to separate the queries and then run them individually.

That's how you would do it in JDBC as well.