Inserting in my JPA using entity manager native query

Question

I am trying to insert a data in my database, i am using JPA in my project.

This is what my bean looks like.

@PersistenceContext
EntityManager em;

    em.createNativeQuery("INSERT INTO testtable ('column1','column2') VALUES ('test1','test2')").executeUpdate();

myfacade:

@Stateless
public class TestFacade extends AbstractFacade<Test> {
    @PersistenceContext(unitName = "TEST2PU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public TestFacade() {
        super(Test.class);
    }

i get an error:

javax.persistence.TransactionRequiredException: executeUpdate is not supported for a Query object obtained through non-transactional access of a container-managed transactional EntityManager

and if i dont use @PersistenceContext for EntityManager

EntityManagerFactory emf = Persistence.createEntityManagerFactory("TEST2PU");
EntityManager em = emf.createEntityManager();
em.createNativeQuery("INSERT INTO testtable ('column1','column2') VALUES ('test1','test2')").executeUpdate();

this is my error:

javax.persistence.TransactionRequiredException: 
Exception Description: No externally managed transaction is currently active for this thread

note: really need to use native query for this.

did you start transaction ? or what are you using for transaction management ? — mstzn, May 29 '13 at 06:31

score 13 · Answer 1 · answered Sep 13 '14 at 15:33

13

You can do it using NativeQuery and its executeUpdate method:

String query = "insert into Employee values(1,?)";

em.createNativeQuery(query)
   .setParameter(1, "Tom")
   .executeUpdate();

answered Sep 13 '14 at 15:33

Saeed Zarinfam

9,818
7
59
72

4

how to get last inserted id using this query? – Kumaresan Perumal Mar 04 '16 at 10:05
1

@KumaresanPerumal did you already found out? Also wondering whether that's possible – Jan-Willem Gmelig Meyling Jul 07 '16 at 11:49
3

@Kumaresan Perumal "insert into table (a, b) values ('a', 'b') returning id;" Try using that in combination with getResultList() and postgresql. – user988346 Mar 08 '20 at 04:44

score 9 · Answer 2 · answered Jun 06 '14 at 10:31

9

I had same problem. Here is solution.

EntityManager em = getEntityManager();
EntityTransaction et = em.getTransaction();
et.begin();
em.createNativeQuery("UPDATE ... ;").executeUpdate();
et.commit();

answered Jun 06 '14 at 10:31

Boris Y.

4,387
2
32
50

score 6 · Answer 3 · edited Jun 02 '22 at 10:37

6

You can use createNativeQuery to write a query specific to the database language.

String query = "INSERT INTO userinfo ( login, upassword, email, mobile, fax, dob)"
             + " VALUES ( :a, :b, :c, :d, :e, :f)"

em.createNativeQuery(query)
    .setParameter("a", objUser.getLogin())
    .setParameter("b", objUser.getUpassword())
    .setParameter("c", objUser.getEmail())
    .setParameter("d", objUser.getMobile())
    .setParameter("e", objUser.getFax())
    .setParameter("f", objUser.getDob())
    .executeUpdate();

edited Jun 02 '22 at 10:37

Alexandru Severin

6,021
11
48
71

answered Mar 18 '18 at 16:48

Debashis Nandi

61
1
1

3

Some explanations would significantly improve the quality of your answer. – mrun Mar 18 '18 at 17:10
1

how can we d this for bulk inserts – Windula Kularatne Mar 17 '21 at 14:42

score 4 · Answer 4 · answered May 29 '13 at 07:31

4

Assuming you're using the container-managed entityManager (injected with @PersistenceContext) you just miss an @Transactionnal annotation above your TestFacade method.

answered May 29 '13 at 07:31

Gab

7,869
4
37
68

score 0 · Answer 5 · answered Nov 11 '22 at 12:36

String query = "SELECT * FROM daily_clouser_report_summery b "
                + "WHERE operatorId='" + operatorId + "' AND "
                + "STR_TO_DATE(b.repDate, '%d-%m-%Y')"
                + "BETWEEN STR_TO_DATE('" + formattedDates + "', '%d-%m-%Y') AND STR_TO_DATE('" + formattedDates + "', '%d-%m-%Y');";
        SessionFactory sf = this.hibernateTemplate.getSessionFactory();
        EntityManager em = sf.createEntityManager();
        List<DailyClouserReportSummery> list = em.createNativeQuery(query, DailyClouserReportSummery.class).getResultList();
        return list;

Inserting in my JPA using entity manager native query

5 Answers5

Linked