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
– mstzn, Commented May 29, 2013 at 6:31

Saeed Zarinfam · Accepted Answer · 2014-09-13 15:33:19Z

15

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, 2014 at 15:33

Saeed Zarinfam

10.3k7 gold badges63 silver badges74 bronze badges

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

3 Comments

Kumaresan Perumal Over a year ago

how to get last inserted id using this query?

Jan-Willem Gmelig Meyling Over a year ago

@KumaresanPerumal did you already found out? Also wondering whether that's possible

user988346 Over a year ago

@Kumaresan Perumal "insert into table (a, b) values ('a', 'b') returning id;" Try using that in combination with getResultList() and postgresql.

Yurkevich · Accepted Answer · 2014-06-06 10:31:30Z

10

I had same problem. Here is solution.

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

answered Jun 6, 2014 at 10:31

Yurkevich

4,5473 gold badges35 silver badges53 bronze badges

Comments

Alexandru Severin · Accepted Answer · 2022-06-02 10:37:42Z

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 2, 2022 at 10:37

Alexandru Severin

6,44812 gold badges54 silver badges79 bronze badges

answered Mar 18, 2018 at 16:48

Debashis Nandi

611 silver badge1 bronze badge

2 Comments

mrun Over a year ago

Some explanations would significantly improve the quality of your answer.

Windula Kularatne Over a year ago

how can we d this for bulk inserts

Gab · Accepted Answer · 2013-05-29 07:31:59Z

5

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

answered May 29, 2013 at 7:31

Gab

8,3504 gold badges43 silver badges73 bronze badges

Comments

Akhilesh Dhiman · Accepted Answer · 2022-11-11 12:36:43Z

0

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;

answered Nov 11, 2022 at 12:36

Akhilesh Dhiman

508 bronze badges

Collectives™ on Stack Overflow

Inserting in my JPA using entity manager native query

5 Answers 5

3 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related