JPA insert statement

Question

What's the correct syntax of a JPA insert statement? This might sound like an easy question but I haven't been able to find an answer.

I know how to do it from Java code but I'm looking for a way to insert objects into the database if the database was created.

Any ideas?

Here is a good case where the least correct answer is marked as the proper solution. Please consider marking as valid either frm or Ben Hoffstein answer instead. — Pedro Borges, Apr 15 '17 at 00:33

score 35 · Answer 1 · answered Jun 21 '10 at 15:00

35

There is no INSERT statement in JPA. You have to insert new entities using an EntityManager. The only statements allowed in JPA are SELECT, UPDATE and DELETE.

answered Jun 21 '10 at 15:00

frm

3,346
1
21
21

1

I've seen native SQL being used for this purpose, but how can this been written for all database providers? Hibernate has such a feature, you can include a file called "import.sql" in the runtime classpath of Hibernate. At the time of schema export it will execute the SQL statements contained in that file after the schema has been exported. I'm looking for some more generic approach to this as I am using Eclipselink. – javydreamercsw Jun 21 '10 at 15:19
Really!, ahh, I was trying to insert a record into database using jpa-ql persistance console in Intellij idea. – Davoud Jan 17 '20 at 12:49

score 22 · Answer 2 · answered Jun 21 '10 at 15:06

22

Here is a good reference on persisting JPA objects using an EntityManager. As an example, this is how to insert objects using the persist method:

EntityManager em = getEntityManager();
em.getTransaction().begin();

Employee employee = new Employee();
employee.setFirstName("Bob");
Address address = new Address();
address.setCity("Ottawa");
employee.setAddress(address);

em.persist(employee);

em.getTransaction().commit();

answered Jun 21 '10 at 15:06

Ben Hoffstein

102,129
8
104
120

1

As I mentioned I'm aware on how to do it from Java. I'm looking for a way to use standard inserts when the database is created besides hardcoding it in java. – javydreamercsw Jun 21 '10 at 15:21
Gotcha - sorry, misunderstood the question. – Ben Hoffstein Jun 21 '10 at 15:32

score 5 · Answer 3 · answered Jun 27 '12 at 17:08

If you want to insert data to the database outside java you need to use native SQL. Use SQL Standard to make sure most databases can execute the script. When the application runs, JPA will make the mapping of the new data and convert it into objects when needed.

How to make sure the script works in all databases? well thats the same problem any DBA has when making Store Procedures or native queries... thats why JPA exists, to avoid making it directly in SQL, but I know sometimes is needed that way.

I suggest you to make 3 main scripts. One for Oracle, one for SQL Server (there are some issues in the date datatypes from 2005 to 2008 versions so be careful) and one for MySQL. Start your script with standard SQL and when you test it in this databases you will find some fixes you will need to do for each DBMS.

One you got it you can make a file script (*.sql) file and run it with the DB manager. If it works run the server, put the app online and the data will be integrated just fine.

This is the way I ended up doing it. I was looking for a more automatic way but I had to code it to do this kind of work. — javydreamercsw, Jun 29 '12 at 13:06

score 2 · Accepted Answer · edited Nov 14 '12 at 09:59

2

The option that looks more promising so far is using Flyway. It is a more automated way of doing it and handles the upgrade process of databases basically automatically.

edited Nov 14 '12 at 09:59

Axel Fontaine

34,542
16
106
137

answered Jun 29 '12 at 13:07

javydreamercsw

5,363
13
61
106

Shipra · Answer 5 · 2020-07-25T19:18:34.493

2

No need to write a separate INSERT query in JPA. JpaRepository has inbuilt saveAndFlush() method which you can use to insert into the database. Hope this works for you.

edited Jul 25 '20 at 19:18

answered May 26 '20 at 20:38

Shipra

91
1
4

JPA insert statement

5 Answers5

Linked