Spring templates: how to execute multiple SQL statements?


Problem :

I want to execute some SQL code (on MySQL) that looks like this (it's not a stored procedure):

START TRANSACTION;  

INSERT...;
SELECT...;

COMMIT;

How can I do it using Spring templates? When I try to use JdbcTemplate, it seems like Spring does not understand the query.

Is not allowed to start the transaction from the SQL code, when using Spring templates?

When using Spring templates, can I start a transaction from the SQL code, when using stored procedures?



Solution :

Have a look at the Spring TransactionTemplate, it's the best way to do transactions while using the JDBC template (see here the official docs):

transactionTemplate.execute(new TransactionCallbackWithoutResult() {

  protected void doInTransactionWithoutResult(TransactionStatus status) {

      jdbcTemplate.update("INSERT INTO PERSON (FIRSTNAME, LASTNAME) VALUES(?,?)",
         new Object[] { firstName, lastName });

     ... multiple calls to JDBC template, all inside the same transaction ....
  }
});

Mysql Tutorials

Mysql Howto..

How to log queries to stdout on MySQL?

How to search for record in mysql using php?

How to pull data from a MySQL column and put it into a single array

How to do a query with Thinking Sphinx and MySQL

MYSQL: How to split left join results in multiple rows

ZF2 + Doctrine - Entities - how to access related data from an Entity and do something to it?

How to properly include a var when creating a table in mysql php

How to limit result returned to 1 of each (MySQL+C#)

How a probably NULL-valued attribute could still reference another attribute in MySQL

How to store webcam captured image path in mysql database

MySQL - how to execute LARGE querys?

How to Monitor Database Changes in EF6 with MySQL

How should I modify my query

How to use GROUP By and HAVING in MySQL

how to build jar file from my project?

How to print a MySQLdb unicode result in human readable way

How does one build the mysql extension for PHP 5.6?

How to specify the parent query field from within a subquery in mySQL?

How to SELECT random rows from table with an exact number of row?

How to collapse rows into a comma-delimited list in an SQL Query in MySql

How to get pt-upgrade (Percona toolkit) to context switch on databases

Show text from rows in MySQL database equal 0 and 1?

how to join 2 tables having equal attributes but different data in each table using mysql

how to reuse deleted primary keys in mysql?

How to disable and enable all constraints in table mysql incl. PK FK CHK UNI etc

MYSQL: How to convert a string into a month (Number) [duplicate]

How can I kill MySQL queries or sleeping connections every 60 seconds in Windows?

how to get values from mysql using php?

How to insert values from File_get_contents into Mysql via php Script

How to import an SQL file to mysqldump using command line