filter lazy initialized collection

My objects: user and credential - many to many relationship, however user has a param

I want to get all users with certain param for every credential in a loop

requirement: users have to be loaded in batch.

  • simple right?

so i have 3 tables:

@Table(name = "CRED")
public class Credential {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name="CRED_ID")     
    Long credentialId;

    @OneToMany(fetch=FetchType.LAZY, cascade = CascadeType.ALL, mappedBy = "credential")
    @BatchSize(size = Search.DEFAULT_PAGE_SIZE)
    private Set<UserCredentialMapping> userCredentialMappingSet;  
}

@Table(name = "USER_CRED")
public class UserCredentialMapping {

    @JoinColumn(name = "user_id", referencedColumnName = "user_id")
    @ManyToOne
    @Filter(name="paramFilter", condition="param = :param")
    private User user;

    @JoinColumn(name = "cred_id", referencedColumnName = "cred_id")
    @ManyToOne
    private Credential credential;
}

@Table(name = "USER")
public class User  {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name="USER_ID")     
    Long userId;

    @Column(name = "PARAM")
    String param
}

i'm making a query in one place and return results:

    String hqlQuery =   "select c from UserCredentialMapping m " +
        " inner join m.credential c" +
        " inner join m.user u" +
        " where u.param = :param" +
        " and c.user_id in (:users)" ;

        Session session = getSession();
        //desparetly trying to set filter
        session.enableFilter("paramFilter").setParameter("param", param);

    Query query = session.createQuery(hqlQuery);
    query.setParameterList("users", USERLIST);
    query.setParameter("param", someparam);

    List<Credential> credentialList = (List<Credential>)query.list();
    return credentialList;

some processing on each credential in mean time and now i need to get list of users with given param:

    for(Credential credential : credentialList){

        //following line makes hibernate query for users
        Iterator<CredentialMapping> mappingIterator = e.getUserCredentialMappingSet().iterator();

        while (mappingIterator.hasNext()){
            UserCredentialMapping userCred = mappingIterator.next();

            User user = userCred.getUser(); 
            DOEVILSTUFFTOINNOCENT(user);
    }

My problem is that iterator generates SQL query that gets all users for credential and not all users with specified param for credential (in other words filter is not being applied)

Any advise how to make it work?

Thanks !

Answers


I've solved it by adding ManyToMany mapping to Credential class:

@ManyToMany(fetch = FetchType.LAZY)
@NotFound(action = NotFoundAction.IGNORE)
@JoinTable(name = "USER_CRED", 
    joinColumns = { 
        @JoinColumn(name = "CRED_ID") }, 
    inverseJoinColumns = { 
        @JoinColumn(name = "USER_ID") })
@Filter(name="param", condition="PARAM = :param")
@BatchSize(size = Search.DEFAULT_PAGE_SIZE)
private Set<User> users;

i couldn't get it working through UserCredentialMapping...


Need Your Help

How to use case and order by in Nhibernate?

hibernate nhibernate sql-order-by case queryover

I need to order result in DB table ChargeOperations in my own direction by typeId.

the place of the footer Html css

html css website

I have this Tow column page layout || but the problem that the footer is for the whole file

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.