-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: Native Query
PostPosted: Sat Mar 10, 2007 10:39 am 
Newbie

Joined: Wed Feb 14, 2007 10:40 am
Posts: 5
this is the test case i have written

Code:
public class RemoteDatabaseDaoTest extends AbstractDependencyInjectionSpringContextTests {

    private static final String CLUBS="CREATE TABLE clubs "+
    "(cnumber int ,cname varchar(40) not null,owner int not null,"+
    "website varchar(40),created datetime not null,payment varchar(40) not null,"+
    "modified date not null,status varchar(40) not null,primary key (cnumber))";
   
    private static final String DOGS = "CREATE TABLE dogs(dnumber int not null,"+
    "club int not null,cname varchar(50) not null,rname varchar(50),"+
    "owner int not null,height int not null,measured varchar(40) not null,"+
    "breed int not null,rescue varchar(40) not null,status varchar(40) not null,"+
    "created datetime not null,payment varchar(40) not null,modified date not null, primary key(dnumber))";

    private RemoteDatabaseDao remoteDatabaseDao;
    private HibernateTemplate hibernateTemplate;
    private JdbcTemplate jdbcTemplate;
   
   
    @Override
    protected void onSetUp() throws Exception {
        super.onSetUp();
        System.out.println("enter");
        DataSource dataSource = (DataSource)applicationContext.getBean("dataSourceRemote");
       
        Connection connection = null;
        Statement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            statement.execute(CLUBS);
            statement.execute(DOGS);
            connection.commit();
            System.out.println("Created tables");
        } catch (Exception e) {
            e.printStackTrace();
            if(connection != null) {
                connection.close();
            }
           
        }
       
        jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(dataSource);
       
        remoteDatabaseDao = (RemoteDatabaseDao)applicationContext.getBean("remoteDatabaseDao");
    }
   
    @Override
    protected String[] getConfigLocations() {
        return new String[] {"spring/daoContextCommon.xml",
                             "spring/daoContextRemote.xml",
                             "spring/dataSourceContextRemote-UTest.xml"};
    }
   
    public void testLoadClubAndDogInfo() throws Exception {
        jdbcTemplate.execute("INSERT INTO clubs (cnumber,cname,owner,website,created,payment,modified,status) VALUES (1,'Touch N Go',3,'www.tng-flyball.com',{d '2005-03-08 03:35:49.0'},'processed',{d '2005-10-10'},'active')");
        jdbcTemplate.execute("INSERT INTO dogs (dnumber,club,cname,rname,owner,height,measured,breed,rescue,status,created,payment,modified) VALUES (1,1,'Reflex','Reflex',3,8, 'yes',1, 'no','active',{d '2005-03-08 03:42:02.0'},'processed',{d '2005-09-24'})");
        jdbcTemplate.execute("INSERT INTO dogs (dnumber,club,cname,rname,owner,height,measured,breed,rescue,status,created,payment,modified) VALUES (2,1,'Nick',  'Nick',  4,12,'no', 45,'no','active',{d '2005-03-08 05:22:43.0'},'processed',{d '2006-03-29'})");
       
        List<Club> clubs = this.remoteDatabaseDao.loadClubAndDogInfo();
       
        assertEquals(1, clubs.size());
        assertEquals(2, clubs.get(0).getDogs().size());
       
    }

    public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
        this.hibernateTemplate = hibernateTemplate;
    }
   
}

the RemoteDatabaseDAOImpl.java
Code:
public class RemoteDatabaseDaoImpl extends HibernateDaoSupport implements RemoteDatabaseDao {
   
    private static final String LOAD_CLUBS = "select d.owner, d.height,d.club, c.cnumber, d.dnumber, c.cname, d.cname from clubs c, dogs d where c.cnumber = d.club and c.payment='processed' and d.payment='processed' and d.status='active'";
    private static final Log logger = LogFactory.getLog(RemoteDatabaseDaoImpl.class);
   
   public List loadClubAndDogInfo() {
        Session session = getHibernateTemplate().getSessionFactory().openSession();
//        List<Club> clubs = session.createSQLQuery(LOAD_CLUBS).addEntity("club", ClubImpl.class).addEntity("dog",DogImpl.class).list();
        List<Club> clubs = session.createQuery("from clubs ").list();
        System.out.println(clubs.size());
      return clubs;
   }
}


now the problem comes when i try to assert the value of club size which should be 1 as u can see from the testcase. But when i print the size of clubs list it prints 2 on screen. Can any tell me what is the problem in the query. It should load 1 club and two dogs.


Top
 Profile  
 
 Post subject:
PostPosted: Sat Mar 10, 2007 11:04 am 
Expert
Expert

Joined: Tue Dec 28, 2004 7:02 am
Posts: 573
Location: Toulouse, France
Did you try applying setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) on the query before calling list() on it ?

_________________
Baptiste
PS : please don't forget to give credits below if you found this answer useful :)


Top
 Profile  
 
 Post subject:
PostPosted: Sun Mar 11, 2007 1:12 pm 
Newbie

Joined: Wed Feb 14, 2007 10:40 am
Posts: 5
no it didn't help any other idea.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Mar 11, 2007 1:23 pm 
Expert
Expert

Joined: Tue Dec 28, 2004 7:02 am
Posts: 573
Location: Toulouse, France
Did you debug to see what was in the list? What is the generated SQL?

_________________
Baptiste
PS : please don't forget to give credits below if you found this answer useful :)


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

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.