Hibernate Community • View topic - How to map a view like this?

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

How to map a view like this?

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

jacer

Post subject: How to map a view like this?

Posted: Fri Jun 22, 2007 9:56 pm

Newbie

Joined: Fri Jun 22, 2007 9:43 pm
Posts: 2

Hi, I have a table in SQL Server like this:

Code:

  create table t1
([name]   varchar(20),Subject   varchar(20),Result   int)   
  insert   into   t1   select   'Jack','Writing',80   
  insert   into   t1   select   'Jack','Math',90   
  insert   into   t1   select   'Jack','Physics',85   
  insert   into   t1   select   'Bob','Writing',85   
  insert   into   t1   select   'Bob','Math',92   
  insert   into   t1   select   'Bob','Physics',82   
  insert   into   t1   select   'Lee','Writing',78   
  insert   into   t1   select   'Lee','Physics',87   

And I wanna to create a view like this:

Code:

declare @sql varchar(4000)

set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from t1) as a
select @sql = @sql+' from t1 group by name'

so that I can get the resultset:

Code:

name    Writing    Math      Physics
Jack    80         90         85
Bob     85         92         82
Lee     78                     87

As you see, the columns of the result view is uncertain, so.....
i am wondering if I could create a map for such a view? thanks a lot.........

Top

jits_1998

Post subject:

Posted: Sat Jun 23, 2007 6:18 pm

Senior

Joined: Tue Jun 12, 2007 4:49 pm
Posts: 127
Location: India

Definitely you can create a map.

Try this :

Code:

<map name="subjMap" table="t1" lazy="false">
         <key column="name"/>
         <index column="Subject" type="string"/>
         <element type="int" column="Result"></element>
      </map>

*Please do rate if it helps*

Regards,
Jitendra

Top

jits_1998

Post subject:

Posted: Sat Jun 23, 2007 6:20 pm

Senior

Joined: Tue Jun 12, 2007 4:49 pm
Posts: 127
Location: India

The limitation in the above solution is that name has to be primary key in the parent object.

*Please do rate if it helps*

cheers!
Jitendra

Top

jacer

Post subject:

Posted: Sat Jun 23, 2007 11:13 pm

Newbie

Joined: Fri Jun 22, 2007 9:43 pm
Posts: 2

thanks a lot, but there still something wrong, :(((((

the mapping file:

Code:

<hibernate-mapping package="model">
   <class name="ViewCourse" mutable="true">
      <map name="subjMap" table="t1" lazy="false">
           <key column="name"/>
           <index column="Subject" type="string"/>
           <element type="Integer" column="Result"></element>
       </map>
    </class>
</hibernate-mapping>

and I synchronized it by Hibernate Sychronizer, and I get a ViewCourse instance as follows:

Code:

   public ViewCourse getViewCourseByName(String name)
   {
      Object result = null;
      Session session = null;

      try
      {
         session = HibernateUtils.currentSession();

         Query query = session.createQuery("SELECT vc FROM model.ViewCourse AS vc where vc.name=:name");
         query.setString("name", name);
         result = query.uniqueResult();

      } catch (HibernateException e)
      {
         // TODO Auto-generated catch block
         e.printStackTrace();
      } finally
      {
         session.close();         
      }

      if (result != null)
         return (ViewCourse) result;
      else
         return null;
   }

then I use like this:

Code:

ViewCourse vatl = new ViewCourseDAO().getViewCourseByName("Jack");
Map<String, Integer> map = vatl.getSubjMap();

I got a NullPointerException:
11:00:03,796 WARN QuerySplitter:116 - no persistent classes found for query class: SELECT vc FROM model.ViewCourse AS vc where vc.name=:name
Exception in thread "main" java.lang.NullPointerException
at model.dao.ViewCourse DAO.main(ViewCourse.java:291)

What's the problem?

AND:

Quote:

The limitation in the above solution is that name has to be primary key in the parent object.

Do you mean 'name' has to be primary key in table 't1'?
As you see, 'name' is repeated several times in 't1' for one can have different remarks in different course. So 'name' should not be primary key?

Top

jits_1998

Post subject:

Posted: Tue Jun 26, 2007 3:21 pm

Senior

Joined: Tue Jun 12, 2007 4:49 pm
Posts: 127
Location: India

Need to see your hibernate.cfg.xml, probably the hbm containing the above declaration is not present in config xml.

*Plesae do rate if it helps*

Top

Page 1 of 1

[ 5 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