0

I need to store the laboratory points for a certain student, the first Select works good but when i try to update the new value for lab_points with WHERE clause the program crash. When i skip the WHERE the program run fine but all values are changed.

What will be the problem for UPDATE?

EntityManagerFactory emf = (EntityManagerFactory)getServletContext().getAttribute("emf");
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();

        EntityManager updateEm = emf.createEntityManager();
        String nume, prenume;
        int lab_points;
        for(List<String> linie: linesAsList)
            if(linie.get(1).matches("[a-zA-Z- ]+") && linie.get(2).length()>=2) {

                nume = linie.get(1).split(" ")[0];
                prenume = linie.get(1).split(" ")[1];

                 lab_points = (Integer) em.createQuery("select n.lab_points from Record as n where n.student.nume=:nume and n.student.prenume=:prenume and n.student.grupa=:grupa and n.cours.numeCurs=:curs")
                        .setParameter("nume", nume).setParameter("prenume", prenume)
                        .setParameter("grupa",linie.get(2)).setParameter("curs","Programare avansata").getSingleResult();

                lab_points = lab_points + Integer.parseInt(linie.get(3).trim());
                System.out.println(lab_points);
                updateEm.getTransaction().begin();
                updateEm.createQuery("update  Record as n set n.lab_points=:points where n.student.nume=:nume").setParameter("nume",nume)
                        .setParameter("points",lab_points)
                        .executeUpdate();
                updateEm.getTransaction().commit();

                System.out.println(em.createQuery("select n.lab_points from Record as n where n.student.nume=:nume and n.student.prenume=:prenume and n.student.grupa=:grupa and n.cours.numeCurs=:curs")
                        .setParameter("nume", nume).setParameter("prenume", prenume)
                        .setParameter("grupa", linie.get(2)).setParameter("curs", "Programare avansata").getSingleResult());

            }

        if (updateEm.getTransaction().isActive())
            updateEm.getTransaction().rollback();
        updateEm.close();

        if (em.getTransaction().isActive())
            em.getTransaction().rollback();
        em.close();

And this my database error message: error

Ciprian Bodnar
  • 3
  • 3
  • Where do you handle any errors/exceptions related to the database and what is the error message you get from the database? – Progman Jun 09 '19 at 16:15
  • @Progman the error are not very helpful, but this i got from the database:[ObjectDB 2.8.0_04] Unexpected exception (Error 990) Generated by Java HotSpot(TM) 64-Bit Server VM 11.0.1 (on Windows 10 10.0). Please report this error on http://www.objectdb.com/issue/new com.objectdb.o.InternalException: null com.objectdb.o.InternalException – Ciprian Bodnar Jun 09 '19 at 16:25
  • Please post the exception stacktrace as **text** in your question. And as suggested in that exception, you should report a bug on http://www.objectdb.com/issue/new – Mark Rotteveel Jun 09 '19 at 17:29

1 Answers1

1

JPQL doesn't allow statement like 

n.student.nume

in an UPDATE statement, because it will try to translate to a SQL JOIN which it's not allowed in an UPDATE statement. Unfortunately JPQL doesn't support subqueries in UPDATE statements.

So the only solution it's to map the student_id to a property in Record entity 

@Column(name = "student_id", insertable=false, updatable=false)
private Long authorId;

And to split the task into two queries like this:

List<Long> ids = em.createQuery("SELECT s.id FROM Student s WHERE s.nume = :nume", Long.class)
    .setParameter("nume", nume)
    .getResultList();

em.createQuery("UPDATE Record r SET r.lab_points = :points WHERE b.student_id IN :ids")
    .setParameter("ids", ids)
    .setParameter("points",lab_points)
    .executeUpdate();
Lungu Daniel
  • 816
  • 2
  • 8
  • 15