Select new User(... with load List)

hankcoy · **Joined:** Wed Jun 14, 2006 9:44 am **Posts:** 22

Hibernate version: 2.1.7

Name and version of the database you are using: Oracle9i

Hi all again,

I Have a User and yours documents, like this:

Code:

public class User implements Serializable
{
    /** idUser attribute */
    private Long idUser;

    /** name attribute */
    private String name;

    /** login attribute */
    private String login;

    /** password attribute */
    private String password;

    /** email attribute */
    private String email;

    /** jobFunction attribute */
    private String jobFunction;

    /** list of userDocuments */
    private List userDocuments;

   // default constructor
   public User()
   {
           super();
   }

   // constructor 2
   public User(Long idUser, List userDocuments)
   {
           this.idUser = idUser;
         this.userDocuments = userDocuments;
   }
.
.
.
}

public class UserDocument implements Serializable
{
    /** idDocument attribute */
    private Long idDocument;

    /** nbDocument attribute */
    private String nbDocument;

    /** user attribute */
    private User user;
.
.
.
}

I need only to load the idUser and list of documents. So I´ve created
a constructor on the User class. I try to do a hql like this:

Code:

select new User(idUser, u.userDocuments)
from User u
left join u.userDocuments uDoc

but an error occurs, where is expected "elements".

when I try to use the "elements":

Code:

select new User(idUser, elements(u.userDocuments))
from User u
left join u.userDocuments uDoc

the above hql causes other error, because the elements(u.userDocuments) returns
only one userDocument.

Is there a way to return the list of objetcs User with idUser and yours list of documents
by using the constructor like above, with a attribute and a collection to load?

thanks!!!

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

As you've given u.userDocuments the alias of uDoc I think you just need to do:

Code:

select new User(idUser, elements(uDoc)) 
from User u 
left join u.userDocuments uDoc 

I've only used elements once though in the following code, which worked for me:

Code:

    public Date findNextBestStartDate(){
        Date nextBestStartDate = null;
        String hql = null;
        hql = "select new " + CostYearVO.class.getName() + " (\n"
            + "    cy.id,\n"
            + "    cy.name,\n"
            + "    cy.startDate,\n"
            + "    cy.endDate,\n"
            + "    cy.status,\n"
            + "    count(elements(cp))\n"
            + ")\n"
            + "from " + CostYear.class.getName() + " cy\n"
            + "left outer join cy.costPeriods cp\n"
            + "group by"
            + "    cy.id,\n"
            + "    cy.name,\n"
            + "    cy.startDate,\n"
            + "    cy.endDate,\n"
            + "    cy.status\n"
            + "order by cy.endDate desc\n";
        List results = getHibernateTemplate().find(hql);
        if( (results != null) && (!results.isEmpty()) ){
            nextBestStartDate = ((CostYearVO)results.get(0)).getEndDate();
            nextBestStartDate = new Date(nextBestStartDate.getTime()+(24*60*60*1000));
        }
        return nextBestStartDate;
    }

Have a look at the HQL doc chapter '11.4. The select clause' at: http://www.hibernate.org/hib_docs/refer ... ryhql.html

hankcoy · **Joined:** Wed Jun 14, 2006 9:44 am **Posts:** 22

Hi,

But when I use the elements(uDoc) on the HQL,
the following error occurs:

Code:

net.sf.hibernate.QueryException: could not resolve property: elements of: CompanyDocument

thanks!!!

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

Do all your Users have userDocuments?

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

Also can you post your hbm.xml files please.

hankcoy · **Joined:** Wed Jun 14, 2006 9:44 am **Posts:** 22

Hi,

The users can or cannot to have documents. So, the left join is necessary.

Sorry. The mapping file are:

Code:

<hibernate-mapping>
    <class name="User" table="tbl_user" proxy="User">
      <id name="idUser" column="id_user">
         <generator class="native">
            <param name="sequence">seq_tbl_user</param>
         </generator>
      </id>
        <property name="name" column="name"/>
        <property name="login" column="login"/>
        <property name="password" column="password"/>
      <property name="email" column="email"/>
        <property name="jobFunction" column="job_function"/>

        <bag name="userDocuments" inverse="true" lazy="true" batch-size="10" cascade="all">
         <key column="id_user"/>
         <one-to-many class="UserDocument"/>
        </bag>
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class name="UserDocument" table="tbl_user_document" proxy="UserDocument">
      <id name="idDocument" column="id_document">
         <generator class="native">
            <param name="sequence">seq_tbl_user_document</param>
         </generator>
      </id>

      <property name="nbDocument" column="nb_document"/>

      <many-to-one name="user" column="id_user" outer-join="false"
         class="User"/>
    </class>

</hibernate-mapping>

thank U very much!!!!

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

I think the reason you get it is because your Users don't always have documents. I know it's a little messy but how about this:

do a normal select:

Code:

from User u

then iterate over your user user list creating a new UserAndDocumentVO (or whatever your trying to make) by doing, then you can add that to your list of objects made out of the user and their document.

Code:

for(User user : users){
    userAndDocumentVOList.add(new UserAndDocumentVO(
        user,
        user.getDocuments()
    )),
}

I know it's not what you want but it would work... I'm not even totaly sure you can do what you want with hql...

Sorry.

hankcoy · **Joined:** Wed Jun 14, 2006 9:44 am **Posts:** 22

Hi,

Well, what I need is load only idUser and list of documents.
This way will load all user data.
So, I´ve changed the HQL to load only id user:

Code:

select new User(idUser)
from User u
left join u.userDocuments uDoc 

and after this HQL I iterate the list of users to initialize the documents:

Code:

           if ((list != null) && (list.size() > 0))
           {
              int count = 0;
              while (count < list.size())
              {
                 User u = (User) list.get(count);
                 
                 Hibernate.initialize(u.getUserDocuments());
                 
                   if ((u.getUserDocuments() != null) &&
                      (u.getUserDocuments().size() > 0))                       
                 {
                      UserDocument ud = new UserDocument();

                  // here, only test, getting the first
                       ud = (UserDocument) u.getUserDocuments().get(0);

                       Hibernate.initialize(ud);
                 }

                   count++;
              }
           }

but the above code doen´t load any list of documents....
I don´t know what to do.

But, thank you very much for your help!!!

hankcoy · **Joined:** Wed Jun 14, 2006 9:44 am **Posts:** 22

Sorry, I forgot....

The user has documents always. minimum one.

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

What's Hibernate.initialize for? Why don't people use the nice Spring/Hibernate DAOs? =(
You shouldn't need any of that nasty boilerplate code.
Lazy loading should take care of the instanciation of your list of user documents.

hankcoy · **Joined:** Wed Jun 14, 2006 9:44 am **Posts:** 22

Hi,

sorry. Only today I could see the post.
Well, the application is already in production since 2004.
We didn´t know about the Spring.
The new projects are using the Spring, but this not yet.

Other question:

Is there a way to return only the first element of the collection?

Like this:

Code:

select new User(idUser, FIRST(uDoc))
from User u
left join u.userDocuments uDoc 

This can to help too much!!!!
thanks!!!! :-)