-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 
  Previous topic | Next topic 
Author Message
ASHU_JAVA
 Post subject: How to delete multiple entries with JPA and Hibernate
PostPosted: Thu Feb 23, 2017 12:23 am 
Newbie

Joined: Thu Feb 23, 2017 12:11 am
Posts: 5
Hello All,

I've been using CheckBoxGroup in my application and followed the below example URL:-
http://examples7x.wicket.apache.org/compref/wicket/bookmarkable/org.apache.wicket.examples.compref.CheckGroupPage;jsessionid=A65260928022BEDC4A2943D0F5FD6996?0

I need to remove single, multiple or all entries from database by selecting checkbox.

On clicking the delete button, I was able to fetch the list of usernames where checkbox is selected and then I used a for loop in my Wicket Panel and for each user name I redirected to below Java Persistence (JPA) code:-

Code:
@Repository
public class MyDaoJpaImpl implements  MyDaoJpa {

   private static final String DELETE_USER_NAME=
         "DELETE FROM User x WHERE x.name=:name";

   @Override
   public boolean deleteUserStoreDomain(String userNAME) {
      try{
      
      if (userNAME!= null && !userNAME.isEmpty()) {
         Number result = em.createQuery(DELETE_USER_NAME)
               .setParameter(name,userNAME)
               .executeUpdate();
         
         
         return ((result.intValue() == 1) ? true : false);
      }
      }catch(Exception ex){
         System.out.println("Error: " + ex.getMessage());
      }
      return false;
      
   }
}




I want to delete several rows from database Table at once but I'm only able to delete only one row at a time.
For first username record, deleteUserStoreDomain() method return TRUE but for each next username record it returns FALSE.
Thus, I'm unable to perform 'multiple' and 'all' username records deletion.

Kindly suggest solutions for this issue.
Thanks in advance.
Top
 Profile  
 
vlad
 Post subject: Re: JPA Delete Multiple Entries
PostPosted: Fri Feb 24, 2017 6:17 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
What if two users have the same name? You should use the entity identifier for this task.

You can send the list of ids to be deleted, and issue a query like this one:

Code:
@Override
public boolean deleteUserStoreDomain(List<Long> ids) {
    if (ids != null && !ids.isEmpty()) {
        Number result = em.createQuery(
            "delete from User where id in :ids")
        .setParameter(ids, ids)
        .executeUpdate();
             
        return result.intValue() > 0;
    }
    else {
        return false; 
    }   
}
Top
 Profile  
 
ASHU_JAVA
 Post subject: Re: How to delete multiple entries with JPA and Hibernate
PostPosted: Mon Feb 27, 2017 12:12 am 
Newbie

Joined: Thu Feb 23, 2017 12:11 am
Posts: 5
Hi Vlad,

Thanks for your response.

I've tried to perform the deletion operation as suggested by you.
But the issue is, its deleting only one row and not multiple.
Scenario:-
There are 2 columns in a table: UserName and UserID. Both these columns are foreign keys.
Suppose I've 3 rows in a table and I want to remove 2 rows.
My query would be :-

Code:
private static final String DELETE_USER_NAME=
         "DELETE FROM User x WHERE xname in (:usernames) and xid = userId";

@Override
   public boolean deleteUserName(List<String> username, Integer userID) {

      if (username!= null && !username.isEmpty()) {
         Number result = em.createQuery(DELETE_USER_NAME)
               .setParameter("usernames",username)
               .setParameter("xid", userID)
               .executeUpdate();

         System.out.println("Result is:"+result);
         return ((result.intValue() == 1) ? true : false);
      }
               else
       return false;
   }



Now if I select 2 rows checkbox and press delete button, during execution only first row gets deleted and the 2nd row still exists.
Thus, I'm not able to delete multiple rows here.
Kindly suggest solutions for this issue.

Note: The application interacts with PostgreSQL database


Thanks in advance.
Top
 Profile  
 
vlad
 Post subject: Re: How to delete multiple entries with JPA and Hibernate
PostPosted: Mon Feb 27, 2017 2:38 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
Quote:
There are 2 columns in a table: UserName and UserID. Both these columns are foreign keys.


You need to use the Primary Key when identifying a database row or an entity.

Quote:
Now if I select 2 rows checkbox and press delete button, during execution only first row gets deleted and the 2nd row still exists.


Probably you have an issue in your Web Controller layer because the DELETE query is quite straightforward. In this case, a good old debug should probably reveal where you're doing it wrong.
Top
 Profile  
 
ASHU_JAVA
 Post subject: Re: How to delete multiple entries with JPA and Hibernate
PostPosted: Mon Feb 27, 2017 5:32 am 
Newbie

Joined: Thu Feb 23, 2017 12:11 am
Posts: 5
Hi Vlad,

The table consists two columns and both are foreign keys: UserName and UserID.
Already spend time with debugging, all looks good.

Thanks.
Top
 Profile  
 
vlad
 Post subject: Re: How to delete multiple entries with JPA and Hibernate
PostPosted: Mon Feb 27, 2017 5:42 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
If you're Primary Key is built out of two Foreign Keys, then it means you should be using a Composite Identifier. Check out this article about how you should map a Composite Key with Hibernate.
Top
 Profile  
 
ASHU_JAVA
 Post subject: Re: How to delete multiple entries with JPA and Hibernate
PostPosted: Wed Mar 01, 2017 11:49 pm 
Newbie

Joined: Thu Feb 23, 2017 12:11 am
Posts: 5
I've also tried with CreateNativeQuery->
Code:
em.createNativeQuery("Delete from User x WHERE x.name in (user1,user2) and x.id = :userId").setParameter("userId",1).executeUpdate();

It gives error "Relation 'user' doesn't exist. org.Hibernate.exception.SQLGrammerException: could not execute native bulk manipulation query".
Top
 Profile  
 
vlad
 Post subject: Re: How to delete multiple entries with JPA and Hibernate
PostPosted: Thu Mar 02, 2017 2:38 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
That exception happens when you haven't given the proper entity name.

Try adding the name explicitly like this:

Code:
@Entity(name = "User")
public class User {

    ...
}
Top
 Profile  
 
ASHU_JAVA
 Post subject: Re: How to delete multiple entries with JPA and Hibernate
PostPosted: Thu Mar 02, 2017 6:48 am 
Newbie

Joined: Thu Feb 23, 2017 12:11 am
Posts: 5
Finally its resolved..
It was not an issue of JPA or hibernate.

When we followed the Wicket Example, to fetch the column values where checkbox is selected, we use a code:
String users = group.getDefaultModelObjectAsString();
Suppose, we've selected 3 records checkbox thus, we receive users as [user1, user2, user3]

As one can see, we are getting an extra space in each user name after user1 i.e. [user1,<space>user2,<space>user3]
This extra space is causing the error and not allowing the deletion of records from 2nd row onward.

I added a trim() method before adding each user name in list like

Code:
List<String> users = new ArrayList<String>();

if(users.contains(",")){
                  users_no = users.split(",");
                  }
                  else
                  {
                     users_no = new String[1];
                     users_no[0]=users;
                  }
                  
                  for(int ar_users=0;ar_users<users_no.length;ar_users++)
                  {
                     String user_name = users_no[ar_users];
                     if(user_name.contains("[")){
                        user_name = user_name.replace("[", "");
                     }
                     if(user_name.contains("]")){
                        user_name = user_name.replace("]", "");
                     }
                     users.add(user_name.trim())
                     
                  }



Once that's done, following code works perfectly to remove multiple users:

Code:
private static final String DELETE_USER_NAME=
         "DELETE FROM User x WHERE x.name in (:usernames) and x.id = userId";

@Override
   public boolean deleteUserName(List<String> users, Integer userID) {

      if (username!= null && !username.isEmpty()) {
         Number result = em.createQuery(DELETE_USER_NAME)
               .setParameter("usernames",users)
               .setParameter("id", userID)
               .executeUpdate();

         return ((result.intValue() == 1) ? true : false);
      }
               else
       return false;
   }


thanks for all the support.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.