Named sql-query: could not execute native bulk manipulation query (but script works fine when executed directly in DB)

Question

i'm trying to execute a SQL native script using hibernate sql-query. The script is the following:

<sql-query name="setAllColors" >
UPDATE chair
    SET COLOR = 'Grey' WHERE NAME='AustrianWinter';
UPDATE chair
    SET COLOR = 'Brown' WHERE NAME='ItalianSummer';
</sql-query>

If i execute this same query in Mysql (it's the DB i'm using) all works fine. If i try to execute it via code, recalling the xml where this query is stored, i get:

could not execute native bulk manipulation query

THE STRANGE FACT is that if i try to execute just the first update, without the second one, all works fine. Any idea on how to avoid this error?

Answer 1

if you want simple case as you gave, you can use answer from @Adrian.

BUT - @Adrian 'In hibernate you can't do that.' -- it's not true. it's not hibernate restriction , it's restriction jdbc connection. By default it's not allowed to have multiple queries. BUT you can set setting database connection property to allow multiple queries, separated by a semi-colon by default.This is additional connection property.

allowMultiQueries=true

jdbc:mysql://localhost:3306/hibernate_db?allowMultiQueries=true

with whis option you can execute :

<sql-query name="setAllColors" >
UPDATE chair
    SET COLOR = 'Grey' WHERE NAME='AustrianWinter';
UPDATE chair
    SET COLOR = 'Brown' WHERE NAME='ItalianSummer';
</sql-query>

---

UPDATE 2: as you use DriverManagerDataSource , allowMultiQueries it's not a part of connection, you should put it as Properties.

from AbstractDriverBasedDataSource

    /**
      * Specify arbitrary connection properties as key/value pairs,     
      * to be passed to the Driver.      
      * <p>Can also contain "user" and "password" properties. However,   
      * any "username" and "password" bean properties specified on this      
      * DataSource will override the corresponding connection properties.    
      * @see java.sql.Driver#connect(String, java.util.Properties)   
     */
    public void setConnectionProperties(Properties connectionProperties) {      
        this.connectionProperties = connectionProperties;   
    }

so ,configuration should be:

   <bean id="dataSourceBean" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
      <property name="url" value="jdbc:mysql://localhost/chairDB"/>
      <property name="username" value="root"/>
      <property name="password" value="pass123"/>
      <property name="connectionProperties">
         <props>
            <prop key="allowMultiQueries">true</pro>
         </props>
      </property>
   </bean>