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;
    }
   
}

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;
   }
}