How can I execute a native SQL script in JPA/Hibernate?

Question

I have a SQL script with database dump. How can I execute it using Hibernate's EntityManager?

I tried it this way:

EntityManager manager = getEntityManager(); 
Query q = manager.createNativeQuery(sqlScript);
q.executeUpdate();

but it works only when sqlScript contains a single SQL query, while I need to run multiple inserts and other complex stuff.

RDBMS: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

hkutluay · Accepted Answer · 2016-05-20 06:23:46Z

71

Wrap your query with begin end block. Like

EntityManager manager = getEntityManager(); 
Query q = manager.createNativeQuery("BEGIN " + sqlScript + " END;");
q.executeUpdate();

answered Jan 16, 2013 at 12:42

hkutluay

6,9742 gold badges37 silver badges57 bronze badges

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

4 Comments

I'm getting You have an error in your SQL syntax with EclipseLink 2.6 and MySQL.

This answer for Oracle. Omit semicolon and it may work on MySql.

FYI: Doesn't work for me in Postgres.

@Jurass can you try with do $$ before begin and $$ after end. enterprisedb.com/postgres-tutorials/…