Run script with EntityManager JPA on Mysql

Question

I'm trying to run a script (.sql file) but i have multiples errors since i tried many ways, here's 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's 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 runs ok, but when my script has more than 1 insert i get the following Exception:

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

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

I also tried with BEGIN, and END, and START TRANSACTION AND COMMIT, but with no good results.

Thank you for the help :)

I don't think you can do it with entityManager, you will need to use the JDBC api directly. Similar question - stackoverflow.com/questions/6734423/…. — gkamal
– gkamal, Commented May 30, 2013 at 2:25

Jarandinor · Accepted Answer · 2013-05-30 02:47:13Z

2

You can't execute the script by the em.createNativeQuery, as i know. You should to split the script into statements and execute them one by one.

You may use ScriptRunner. It can be used separately from the 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();

edited May 30, 2013 at 2:47

answered May 30, 2013 at 2:41

Jarandinor

1,87616 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Logan Over a year ago

Thanks... by now what i did is read each line and execute it alone, each script should not be more than 5 querys, so by now ill take this solution, ill check later for the myBatis one, thanks

W0lfw00ds Over a year ago

Is it possible to run JPQL scripts with this? Or is the scriptRunner always native sql only?

Collectives™ on Stack Overflow

Run script with EntityManager JPA on Mysql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related