JPA ManyToMany fix the Delete of records in Join table while Merging

I was working with a ManyToMany relation. But I have got problem while adding new records in the Association Table.

In fact I am working with three tables: the Person table is related to table Project using an association table lt_person_project.

Using ORM, the generated entities are :

In the Person Entity (it is the owner of this relation):

@ManyToMany(fetch = FetchType.LAZY, cascade=CascadeType.ALL)
@JoinTable(name = "lt_person_project", schema = "enterprise_schema", joinColumns = { @JoinColumn(name = "person_id", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "project_id", nullable = false, updatable = false) })
public List getProjects() {
return this.projects;
}

In the Project Entity, we Have:
@ManyToMany(fetch = FetchType.LAZY, mappedBy = "projects")
public List getPersons() {
return this.persons;
}


Now I have created the person, and while doing it, I have assigned to him some projects. I wanted then to assign him new projects, so the code was:

@Transactional(propagation= Propagation.REQUIRES_NEW)
public void assignProjectsToPerson(String personId, List projectIds) {
Person person = this.findPerson(personId);
List projects = projectDao.findProjects(projectIds);

        person.setProjects(projects);

entityManager.merge(person);

}

Doing so, Hibernate will first execute a query where it Delete all the rows in the association table where the personId is the one given by our method. Then it inserts the NEW records.

Thinking about my code, it is normal that I have such behavior, as I have told hibernate to setProjects. So I have ignored the old ones.

So in order to tell Hibernate that In fact I am adding new Records in the Association table, I have changed my setProject with this line:

person.getProjects().addAll(projects);

Even using this line, Hibernate will first delete the records then put them again, but at least, it will add the old and new ones.

JPA 2.0 Criteria Query/Builder DELETE : Typesafe DELETE queries

JPA 2.0 don't provide a dynamic way to write delete queries. In fact we can only write a type safe queries for the SELECT but not for the DELETE and Update.

JPA 2.1 provides this possibility using  CriteriaUpdate and CriteriaDelete. You can have more details in this link.


But actually, as I am obliged to use JPA 2.0 which comes with JBoss eap 6, and waiting for JPA 2.1, I should use the native queries.

So the problem here is that I have multiple lists that I should use in the query and that may be null.

Query query = entityManager.createNativeQuery("DELETE FROM person_record pr WHERE pr.person.person_id IN (select person_id from person p WHERE p.age IN ?1 AND p.job IN  ?2 ");

query.setParameter(1, ageList);
query.setParameter(2, jobList);

Here I am using a SELECT subquery. In fact if I use this query,

DELETE.... Where pr.person.age IN ?1

Hibernate will not accept it and throw an exception.

Problem:

The problem with the first query is that when ageList or the jobList is null, nothing will be returned. So we should put the parameter only if it is not null. We can so create a StringBuilder and put the first part of the request and according to what parameter we have, we create the rest of the query.

Solution: 

But a more safe think to do is to use the Criteria Builder in order to select the rows to delete, then delete the rows using entityManager.remove(entity). This is also very useful if we need to do a Cascade DELETE.

So the first think is to do like this:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery query = builder.createQuery(PersonRecord.class);
Root root = query.from(PersonRecord.class);

List predicateList = new ArrayList();

if(ageList != null && !ageList .isEmpty()){
predicateList.add(builder.isTrue(root.get("person").get("age").in(ageList )));
}
if(jobList!= null && !jobList.isEmpty()){
predicateList.add(builder.isTrue(modelTypeRoot.get("person").get("job").in(jobList)));
}

query.where(builder.and(predicateList.toArray(new Predicate[predicateList.size()])));

List personToDelete= entityManager.createQuery(query).getResultList();

for(PersonRecord item:personToDelete){
       // Make the entity attached again
entityManager.merge(item);
      // Here a CASCADE remove will be done easily
entityManager.remove(item);
}

This is only a temporarely solution till we will get the JBoss EAP with JPA 2.1

JPQL Select the Latest record date for each User and Correct Use of LEFT OUTER JOIN

Using JPQL to write a complex query, I was checking for the latest record added for each user.
In fact, let's say that the user borrow books form library and we want to know the latest book taken for each user.

My SQL request should be like this :

select user_id, user_name, mobile_phone, g.group_name,  borrowing_date
from books_record b 
inner join user u on b.user_id = u.user_id
left outer join group g on b.group_id = g.group_id
where (user_id, borrowing_time) in (
    select br.user_id, max(br.borrowing_time)
    from books_record br
    group by br.user_id
)

I am using a LEFT OUTER JOIN as reference on group can be NULL.

Using criteria builder, I wasn't able to generate this query, as I didn't now how to put two columns for the IN.

query.where(builder.in(root.get("user").get("userId"), root.get("borrowingTime")).value(subQuery));

So I have decided to use JPQL, where I construct a new bean in the result by giving my columns.


So the JPQL query shoul look like this (I declared it as a NamedQuery) :

  SELECT new org.project.test.MyPerson(b.user.personId, b.user.userName, b.user.mobilePhone, g.groupName)

  FROM BooksRecord b 

  LEFT OUTER JOIN b.group g

  WHERE ( b.user.userId,  b.borrowingTime)

  IN (

SELECT mxb.user.userId, MAX(mxb.borrowingTime)

FROM BooksRecord mxb

GROUP BY mxb.user.userId

)

JPA Hibernate CriteriaBuilder, Order the returned rows, Limit Result and construct a new Bean in the result with Left Join

Using Criteria Builder, we need sometime to Order the result, and even to Limit the size of the returned rows. We need also to Create a new bean directly in the query with or without a LEFT JOIN. The join in used if the attribute (which is a foreign key) may be null.

To the above mentioned requirement using JPA criterai builder, the code is:

 CriteriaBuilder builder = entityManager.getCriteriaBuilder();
 CriteriaQuery query = builder.createQuery(MyPerson.class);
 Root root = query.from(Person.class);

query.select(builder.construct(MyPerson.class, 
root .get("personName"),
root .get("phone").get("mobilePhone"),
root .join("address", JoinType.LEFT).get("country"),
        root.get("age"),
));

query.orderBy(builder.desc(root.get("age")));

alerts = entityManager.createQuery(query).setMaxResults(MAX_RESULT).getResultList();

That's all, hope it helps you.

Hibernate JPA Resolve Error Unable to resolve attribute [phone] against path

Working with JPA, I have an entity called Person which have reference on another entity called Phone.
In order to get the MobilePhone, I do :

root.join("phone", JoinType.LEFT).get("mobilePhone"),
OR
root.get("phone").get("mobilePhone"),

Doind this, I got an error :
Unable to resolve attribute [status] against path.

After some investigation, I have found that I have changed the name of my attribute in my code, from personPhone to phone, but the getter and setter still with the old name.
So to get my code working, I should use the same attribute name in  the getter also :

Person{

    Phone phone;
    ...
   Phone getPhone{
             return phone;
   }
}

Jpa Hibernate insert Query: Error in named query: insertQuery: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: VALUES

In order to insert a record into the database, I was wondering to use SQL insert in order to entityManager.persist, as I would like to provide directly the foreign keys.
So I was using a Named Query.
@NamedQuery(name="insertQuery",
query="INSERT INTO person (person_id, name) VALUES (:person_id, :name)")

But at compilation time , I got this error:

 Error in named query: insertQuery: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: VALUES 

In fact, the solution is to use a Native Query.

So I needed to change the @NamedQuery into @NamedNativeQuery. 

As I should setParameters, I should use the createNamedParameter. Otherwise, I will have the error :
 could not locate named parameter.
So my query should look like this.

Query query = entityManager.createNamedQuery("insertQuery")
query.setParameter("person_id", person.getId()); 

Hibernate JPA Change the implicit INNER JOIN with a LEFT JOIN with Criteria Builder and build a new Result bean: Only one LEFT JOIN Query is generated

Here my experience with the Generated Queries using my lovely Hibernate. I was stuck for about four days with one of the generated query, but finally I find the solution.

I was developing a code in order to get Persons from my tables using JPA (Hibernate implementation) and Criteria Builder . Performing the test, I have noticed that Hibernates returns only Persons which have a Phone number (as I have a foreign Key on a Contact table). Hibernate do by default an INNER JOIN, and not a LEFT JOIN.

SO to force Hibernate doing a LEFT JOIN with the criteria Builder (I was creating a dynamic query), I used this code:

Root root = query.from(Person.class);

Join phone = root.join("contact", JoinType.LEFT);
...
query.select(builder.construct(MyPerson.class, 
               root.get("contact").get("phone")

The generated Query where :

SELECT phone2.number, person1.name
FROM person person1
LEFT OUTER JOIN phones phone1 ON person1.contact_id = phone1.id
,phones phone2
WHERE 
person1.contact_id = phone2.id

So Hibernate IGNORES in fact my Left JOIN even if it put it in the query. It use the Phone2 and not Phone1 present in the Left Join.  So the rows with contact null, is not yet returned.

If I build my bean by giving the root only, it will generate many other requests.
query.select(builder.construct(MyPerson.class, root);

It was very hard to finally find the solution which consists of removing the Join and do the join while creating the new bean. So the code should be in fact like this:

Root root = query.from(Person.class);
query.select(builder.construct(MyPerson.class, 
   root.join("contact", JoinType.LEFT).get("phone"),...));

So finally the generated query is exactly what I wanted:

SELECT phone1.number, person1.name
FROM person person1
LEFT OUTER JOIN phones phone1 ON person1.contact_id = phone1.id

Postgres Contraint One of the columns NOT NULL

While creating some tables for an application, I have the requirement that in a table at least one of two columns should be NOT Null. This can be checked in the application, but it is better also to do it in the database level in case where we have problems in the application and to make sure that we respect all the time this constraint.

To do so, we can add this constraint in Postgres :


CONSTRAINT chk_onenotnull CHECK (NOT ROW(mobile_phone, home_phone) IS NULL) 

That's all :) .

Resolve JPA Criteria Builder org.hibernate.QueryException: could not instantiate class from tuple

Using Criteria Builder in JPA, I construct a bean from the query result using this line:

query.select(builder.construct(MyNewBean.class, root));

I give here the root and not root.get("colName"), as I have difficulties in generating queries as I like. I will speak about that later. But now let us focus on the first problem.

While testing my code, I get

org.hibernate.QueryException: could not instantiate class className from tuple
at org.hibernate.transform.AliasToBeanConstructorResultTransformer.transformTuple
I have thought first that I shouldn't give root (as I was used to give root.get("user").get("name"), root.get("phone")). But the problems wasn't really that.
Just after reading again the error, I have found this :
Caused by: java.lang.NullPointerException

OK. The Problem is in my constructor. I do
this.userName = MyEntity.get("user").get("name");

In fact, User refers to  another entity, so when we get the user, it can be NULL. Which will generate the nullPointerException and thus the tuple Error. So before getting the entities attributes make sure that they are not null (No need to check that for the NOT NULL foreign Keys)

if( MyEntity.get("user") != null){
   this.userName = MyEntity.get("user").get("name");
}

An advice is to use also an EAGER fetch if you are sure that you need the parameters (to return to a Web Service Client). So, only one request will be generated. If you use a Lazy join, at every call to get, a new Select will be done which will degrade performance.

Resolve java.util.List is an interface, and JAXB can't handle interfaces.

Working on a Web service, I caught this error while deploying:

Caused by: com.sun.xml.bind.v2.runtime.IllegalAnnotationsException: 1 counts of IllegalAnnotationExceptions
java.util.List is an interface, and JAXB can't handle interfaces.
this problem is related to the following location:
at java.util.List

The problem comes from my WebMethod where I am returning a List<List<Car>>.  JAXB don't know how to handle this. So I have found a Solution where I define a new bean conating the List:


Class Cars{
    private List<car> cars;
...

}

So my WebMethod will return List<Cars>. This new bean resolves the problem. Make Sure also that the attribute is private (or don't put it at all, by default it will be private) otherwise you can have an error :
Class has two properties of the same name. See this post.




Ref : https://stackoverflow.com/questions/298733/java-util-list-is-an-interface-and-jaxb-cant-handle-interfaces

Fix UNIQUE CONSTRAINT with null value

In my database, I have UNIQUE constraint with two columns col_1 and col2.
But, I have detected that while inserting in the database two records with the same value in col_1 and null in col_2, postgres accept it and take null values as different.

So as solution, I have added a default value. My col_2 was a date, so I have inserted a future date '2040-01-01' and in the request, I just do :

select from table where col_2 >= now();

Using JPA, I also disable it to insert a NULL value and let the database putting the default value by using insertable =  flase. Please see this link for more details.

Resolved : Set the Database Default Value in a Hibernate/JPA Save Persist

Using JPA (with Hibernate), I was inserting a record in my database. In my table, one column is defined with a DEFAULT Value.
But when I insert a save, persist my record, JPA put it as NULL.
So using JPA, the solution was to use insertable = false in the column definition. JPA will ignore then that column while inserting in the Database and the default value will be used.

@Column(name = "myColumn", length = 35, insertable = false)

Resolve Error: ERROR: syntax error at or near "cross"

When I was testing my application, I have encountered an error using such a query:

@NamedQuery(name = "entity.myquery", 
query = "UPDATE MyEntity d SET d.isEnabled=false where d.myTest.id = :testId")

I get this error :

org.hibernate.exception.SQLGrammarException,
ERROR: syntax error at or near "cross"


Using this query, Hibernate translate it to a join query.

update mySchema.my_entity cross join  set is_enabled = false where id = ?

Or, no join is accepted in a Bulk HQL query.
So the Solution is to use a sub-select.

@NamedQuery(name = "entity.myquery", 
query = "UPDATE MyEntityd SET d.isEnabled=false where d.myTest.id IN "
+ "(SELECT e.id FROM myTest e where id = :testId)")

Use JBoss JPA 2.1 and Hibernate 4.3 on JBoss EAP 6.2 (JBoss As 7) and Spring Integration (JPA Spring and JBoss Integration )

I will speak today about how integration Spring with JBoss AS 7 (JBoss EAP 6). As the new JBoss inject implicitly its modules  (logging, JPA, Hibernate), it may be sometimes annoying. So here we give example about how to integrate Spring with JBoss by weather to let JBoss inject its library, or using JPA 2.1 which i not supported yet by JBoss, and prevnt the server from injecting JPA and Hibernate. 

Step 1 : Persistence.xml

The persistence.xml creation will depend on weather the Entity Manager will be managd by the container (JBoss so JPA 2.0) or by Spring (Where we will use JPA 2.1).

- JBoss manage the EntityManager: Use of JPA 2.0

In this case, the Transaction Type must be JTA. JBoss EAP 6.2 (based on JBoss 7.3) comes with JPA2.0 :

1 2 3 4 5 6 7 8 9 10 11 12 13 14

<?xml version="1.0" encoding="UTF-8"?> <persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"> <persistence-unit name="module_enterprise" transaction-type="JTA"> <jta-data-source>java:/myDS</jta-data-source> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/> <property name="hibernate.show_sql" value="true" /> <property name="hibernate.hbm2ddl.auto" value="validate"/> </properties> </persistence-unit> </persistence>

- Spring manage the EntityManager: Use of JPA2.1

As we need to exclude JPA and Hibernate coming from JBoss and we want to use JPA2.1, we need to :
- Create a jpa-persistence.xml. In the case where you don't want that JBoss inject its own libraries, it is better to change the name of the persistence.xml ( JBoss will load JPA implicitely if it detects perssistence.xml file).

- Use RESOURCE_LOCAL and not JTA, if you don't really need to access multiple data sources (use JTA in that case): Using RESOURCE_Local when the persistence unit is initialized by Spring or the application is a servlet-continer based application.

- Use "org.hibernate.jpa.HibernatePersistenceProvider" as a provider

- Add a property in the perssitence.xml
<property name="jboss.as.jpa.managed" value="false"/>

In fact, according to JBoss Reference, jboss.as.jpa.managed can be set to false to disable container managed JPA access to the persistence unit.  The default is true, which enables container managed JPA access to the persistence unit.  This is typically set to false for Seam 2.x + Spring applications. 

1 2 3 4 5 6 7 8 9 10 11 12 13 14

<?xml version="1.0" encoding="UTF-8"?> <persistence version="2.1" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_1.xsd"> <persistence-unit name="mdd_module_enterprise" transaction-type="RESOURCE_LOCAL"> <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider> <jta-data-source>java:/myDS</jta-data-source> <properties> <property name="jboss.as.jpa.managed" value="false" /> <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/> <property name="hibernate.show_sql" value="true" /> <property name="hibernate.hbm2ddl.auto" value="validate"/> </properties> </persistence-unit> </persistence>

Step 2 : Spring Configuration

Same think here: 

- JBoss manage the EntityManager or EntityManagerFactory and Spring have just the jndi name. Transaction Type here must be JTA. The ApplicationContext looks like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"> <!-- post-processors for all standard config annotations --> <tx:annotation-driven /> <context:annotation-config /> <context:component-scan base-package="org.test"/> <jee:jndi-lookup id="myDataSource" jndi-name="java:/myDS"/> <jee:jndi-lookup id="entityManagerFactory_module" jndi-name="java:comp/env/test/myfact" expected-type="javax.persistence.EntityManagerFactory"/> <tx:jta-transaction-manager/> </beans>

In this case, the application uses a server-deployed persistence unit. Thus the javax.persistence classes and the persistence provider (Hibernate) are contained in modules in JBoss ( in JBOSS_HOME\modules\system\layers\base\javax\persistence\api\main) and added automatically by the application while the deployment (When detecting the persistence.xml or persistence-unit, JBoss inject implicitly Hibernate).
So using the server-deployed persistence unit, you need also to declare the JNDI persistence context in the Web.xml:
NB: The persistence-unit-name specified in web.xml should be the same  in perssitence.xml file  ( <persistence-unit name="module_enterprise" )

1 2 3 4 5 6 7 8 9 10 11 12

<?xml version="1.0" encoding="UTF-8"?> <web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" metadata-complete="true"> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <persistence-unit-ref> <persistence-unit-ref-name>test/myfact</persistence-unit-ref-name> <persistence-unit-name>module_enterprise</persistence-unit-name> </persistence-unit-ref> </web-app>

Note here that the name that we have declared in the application context is java:comp/env/test/myfact, and in the web.xml, we need to put just test/myfact

If Spring cannot find the Default JBoss transaction Manager, you can guide it like explained in this post.

- Spring manage the EntityManager.

Using JPA2.0, we can enable the JPA in JBoss and use the first possibility. But as we have disabled JPA in JBoss and we are actually working with JPA2.1 (Or we want that Spring manage the persistence unit), so the EntityManagerFactory will be done using Spring.

My applicationContext looks like this:

That's all, so now you are able to integrate JBoss with Spring either by allowing JBoss manage your perssitence unit or by letting only Spring doing this and EXCLUDE JBOSS JPA and Hibernate modules.

Resolve org.springframework.beans.factory.NoSuchBeanDefinitionException: No bean named 'transactionManager' is defined

When testing my application, an error appear :

 org.springframework.beans.factory.NoSuchBeanDefinitionException: No bean named 'transactionManager' is defined

In fact, in the ApplicationContext, I am defining the transaction manager with another name:
<bean id="myTxManager" class="org.springframework.orm.jpa.JpaTransactionManager">

So, as Spring is checking for the default name, I just put it in my configuration.
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">

I have seen that it is possible to use another name, nut you should sepcify it in the @Transactional annotation:
@Transactional(value="myTxManager")

Now it is working ;)

Resolved: Entity Manager cannot persist entities in database

I was developing a DAO layer using JPA specification (with JTA transaction managed by  Spring). When I tried to insert a new entity. I was using a shared entity manager and managing my transaction like this.

@PersistenceContext
private EntityManager entityManager;

entityManager.getTransaction().begin();
.....
entityManager.getTransaction().commit(); 

Once tested, I had this error:
 java.lang.IllegalStateException: Not allowed to create transaction on shared EntityManager - use Spring transactions or EJB CMT instead
at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:220) [spring-orm-4.0.1.RELEASE.jar:4.0.1.RELEASE]
at $Proxy82.getTransaction(Unknown Source)

So I should use Spring Transactions. I had thus added @Transaction annotation to my method, and removed the getTransaction..  I also added flush in order to "commit" the work.

entityManager.persist(item);
entityManager.flush(); 

But it didn't work and I have this error in  my log.
entitymanager persist no transaction is in progress

So I think my @Transaction annotations are not taken into consideration and thus transactions are not initialized.
So in order to solve this problem I have added this line in my applicationContext
< tx:annotation-driven/>

(as I am using jndi entitymanagerfactory lookup, I have this :
<tx:jta-transaction-manager/>)

The problem is so resolved :)