Run script with EntityManager JPA on Mysql

Question

I am trying to run a script file (.sql), but I have multiple errors, since I tried many ways, here is my main sql script:

INSERT INTO `Unity` VALUES (11,'paq',0,'2013-04-15 11:41:37','Admin','Paquete','Paq',0,'2013-04-15 11:41:37','AAA010101AAA',NULL); INSERT INTO `product` VALUES (11,'chi','USD','chi one',0,'2013-04-15 11:42:13',0,'Admin','Chi name',0.25,0,15,'2013-04-15 11:42:13','AAA010101AAA',NULL); 

and here is my main dao code:

 @Autowired private EntityManager em; @Override public Integer runSql(String path) { try { Archivo archivo = new Archivo(); String strQuery = archivo.readFileText(path); Query query = em.createNativeQuery(strQuery); return query.executeUpdate(); } catch (IOException e) { e.printStackTrace(); return 0; //TODO return false; } } 

If I run the script with only one insert, it works fine, but when my script has more than 1 insert, I get the following exception:

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to "INSERT INTO producto_servicio VALUES (11, 'chi', 'USD', 'chi one', 0, '2013-04-15 11: 42: 13 ', 0,' 'on line 2

Is there a way to run a script file with multiple inserts?

I also tried using BEGIN, END and START TRANSACTION AND COMMIT, but without good results.

Thanks for the help:)

Answer 1

You cannot execute a script using em.createNativeQuery as I know. You must break the script into statements and execute them one at a time.

You can use ScriptRunner . It can be used separately from MyBatis.

Example:

 em.getTransaction().begin(); Connection connection = em.unwrap(Connection.class); ScriptRunner sr = new ScriptRunner(connection); sr.runScript(new StringReader("INSERT INTO `Unity` VALUES (11,'paq',0,'2013-04-15 11:41:37','Admin','Paquete','Paq',0,'2013-04-15 11:41:37','AAA010101AAA',NULL);\r\nINSERT INTO `product` VALUES (11,'chi','USD','chi one',0,'2013-04-15 11:42:13',0,'Admin','Chi name',0.25,0,15,'2013-04-15 11:42:13','AAA010101AAA',NULL);")); em.getTransaction().commit();