Nowbody calls me Ignacio. I’m plunchete, man!

JPQL and how to delete objects in JPA

Posted in Google Summer of Code, Java, JPA, OpenJPA by plunchete on May 30, 2007

Continuing with the example Relations with JPA now I’ve written another example about querys and deleting objects.

JPA includes a mechanism to write querys called JPQL (Java Persistence Query Language) in this example we’re going to execute some querys and deleting some objects and their relations.

At the end of the last article we had this values in database:

[java] Organization: The Apache Software Foundation
[java] -Streaming LOB support (for OpenJPA) asigned to Ignacio Andreu
[java] -Maven Dependency Visualization asigned to Peter Kolbus
[java] Organization: Mono Project
[java] -Gendarme Tasks asigned to Néstor Salceda

And the next relations between entities:

  • A organization has many projects
  • Each projects is asigned to one organization
  • A project has one student
  • Each student is asigned to one project

Ok, if we remember the last example we used a Query to list all the organizations

Query q = em.createQuery("select o from Organization o");

This is a simple Query, now we’re going to write a Query with a WHERE clause.

JPQL provides us two methods for parameterized queries. First is positional parameter, using an integer prefixed by a question mark and second is named parameter, using an string prefixed by a colon. We can populate the Query calling the setParameter method.

If we are writing a Query with a positional parameter:

Query query = em.createQuery(select o from Organization o " 
                  + "where o.name like ?1);
query.setParameter(1, "The Apache Software Foundation");

And if we are writing a Query with a named parameter:

Query query = em.createQuery(select o from Organization o " 
                  + "where o.name like :name);
query.setParameter("name", "The Apache Software Foundation");

EntityManager povides other method to search an object by his primary key, the method find. In our class Organization the primary key is an int value, auto-increment. In out table the value of the Organization calls “The Apache Software Foundation” is 1. If whe want to load our object by the primary key:

Organization organization = em.find(Organization.class, 1);

Now we are going to delete our organization called “Mono Project” using JPQL. The process is the same that a select.

Query query = em.createQuery(delete from Organization o " 
                  + "where o.name like :name);
query.setParameter("name", "Mono Project");
int deleted = query.executeUpdate();

The Query provides methods to get the result like getResultList() when we want a List or getSingleResult() when we’re sure that we only have one match. Also The Query provides a method to set the maximun number of results setMaxResults(int number)

And now we going to list all the projects

Query q = em.createQuery("select p from Project p");
for (Project project : (List)q.getResultList()) {
	System.out.println("-"
                 + project.getOrganization().getName() 
                 + " - " + project.getName()
                 + " asigned to "
                 + project.getStudent().getName());
}

The output is

[java] -Streaming LOB support (for OpenJPA) asigned to Ignacio Andreu
[java] -Maven Dependency Visualization asigned to Peter Kolbus

Wow, all the projects asigned to “Mono Project” have been deleted too (and all the student asigned to this projects). If we remember we have these relations

@Entity
public class Organization {	

	...

	@OneToMany(cascade = CascadeType.ALL,
			mappedBy = "organization")
	@InverseLogical("organization")
	private ArrayList<Project> projects;

	...
@Entity
public class Project {	

	...

	@OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
	@JoinColumn(name="student_id")
	@InverseLogical("project")
	private Student student;

	...

It’s seems that our relations works😀

Another way is using the method remove of the EntityManager, but in this method we must indicate all the Object.

Query q = em.createQuery("select o from Organization o " 
		+ "where o.name like :name");
q.setParameter("name", "Mono Project");

// This is unsecure beacause name not is unique
// we can use the method setMaxResults(1)

Organization o = (Organization)q.getSingleResult();
em.remove(o);

The result is the same.

That is all, folks😉

7 Responses

Subscribe to comments with RSS.

  1. David said, on February 27, 2008 at 7:21 pm

    Thanks for the work but I have tested the exact examples, and the delete by query does not remove the associated Projects.

    I have the following output by running insertValues, readValues and then the code for the delete by query:

    Organization: The Apache Software Foundation
    -Maven Dependency Visualization asigned to Peter Kolbus
    -Streaming LOB support (for OpenJPA) asigned to Ignacio Andreu
    Organization: Mono Project
    -Gendarme Tasks asigned to Néstor Salceda
    ————– deleted(1) ———-
    – The Apache Software Foundation – Maven Dependency Visualization asigned to Peter Kolbus
    -null – Gendarme Tasks asigned to Néstor Salceda
    – The Apache Software Foundation – Streaming LOB support (for OpenJPA) asigned to Ignacio Andreu

    (had to check null on organization of a project)

    On the other hand, the remove method works and I get the following output:

    Organization: The Apache Software Foundation
    -Maven Dependency Visualization asigned to Peter Kolbus
    -Streaming LOB support (for OpenJPA) asigned to Ignacio Andreu
    Organization: Mono Project
    -Gendarme Tasks asigned to Néstor Salceda
    ————– removed ———-
    – The Apache Software Foundation – Maven Dependency Visualization asigned to Peter Kolbus
    – The Apache Software Foundation – Streaming LOB support (for OpenJPA) asigned to Ignacio Andreu

    Which version of OpenJPA have you run your tests? I am using 1.0.2.
    Thanks.
    David

  2. David said, on February 27, 2008 at 7:25 pm

    Oops, forgot to mention that I am using Sybase with jconn3 driver.

    Will try with Oracle but doubt it has an effect.

    Thanks,
    David

  3. David said, on February 28, 2008 at 3:09 pm

    Hello,

    I was wrong… It is coming from the schema generation for Sybase. Sybase does not support cascading delete on foreign keys. With the proper openjpa properties in persistence.xml, the correct schema is generated for Oracle and the cascading delete works fine for the delete query as well.

    The only way to do that with Sybase is through triggers…

  4. plunchete said, on February 28, 2008 at 4:23 pm

    Hi,

    Great!! If you have more cuestions please pot them!!

    – Ignacio

  5. Sandeep said, on November 13, 2008 at 4:19 pm

    OK , i have a problem where i need to delete a row in DB , problem is when i delete using EntityManager it deletes the entity as well, so suppose i have 2 browsers on the same page, and the user clicks delete on one browser , now that entity is gone , on the other browser if he clicks delte on the same record , it goes for a toss,it actually deletes the next row. as i use a h:selectBooleanCheckbox value=#{someList[MyRow]}. Any way to get around this??

  6. plunchete said, on November 17, 2008 at 12:20 am

    Hi Sandeep,

    As far as I know you are talking about a JSF tag, I don´t have experience in JSF.

    If you use the id as part of the jpql query your code doesn’d delete a diferent entity or even, check if the entity still in the data base before to delete it.

    Also, you can control these operations and inform to the client http://en.wikipedia.org/wiki/Comet_(programming)

    Regards,

  7. Jaime Hablutzel said, on November 8, 2011 at 11:33 pm

    Thanks you helped me a lot😀


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: