我有4张表,结构(字段)都一样,现在想通过union结查询出来,语句如下:
public List queryShopByName(String username) {
       List list =new ArrayList();
       String sql="select * from food where uid=(select id from user where username='"+username+"')" +
              " union " +
              "select * from house where uid=(select id from user where username='"+username+"')" +
              " union " +
              "select * from shopping where uid=(select id from user where username='"+username+"')" +
              " union " +
              "select * from entertainment where uid=(select id from user where username='"+username+"')";
       list=this.getSession().createSQLQuery(sql).list();
       returnlist;
    }
其中Shop是一个VO类,字段对应那4张表的字段,如果不这样循环操作,那么查询出来的是list里面包含Object的列表,在前台就不好取。所以我对它进行了实体类的封装。代码如下:
publicList queryShopByName(String username) {
       List list =new ArrayList();
       List shop=new ArrayList();
       User user=new User();
       String sql="select * from food where uid=(select id from user where username='"+username+"')" +
              " union " +
              "select * from house where uid=(select id from user where username='"+username+"')" +
              " union " +
              "select * from shopping where uid=(select id from user where username='"+username+"')" +
              " union " +
              "select * from entertainment where uid=(select id from user where username='"+username+"')";
       list=this.getSession().createSQLQuery(sql).list();
       for(int i=0; i<list.size(); i++) {
              Object[] o = (Object[])list.get(i);
              Shop shops=new Shop();
              shops.setId((String)o[0]);
              shops.setShop_name((String)o[1]);
              shops.setShop_alias((String)o[2]);
              shops.setShop_item((String)o[3]);
              shops.setShop_twoitem((String)o[4]);
              shops.setShop_provnice((String)o[5]);
              shops.setShop_city((String)o[6]);
              shops.setShop_area((String)o[7]);
              shops.setShop_full_address((String)o[8]);
              shops.setShop_near((String)o[9]);
              shops.setShop_phone((String)o[10]);
              shops.setShop_online_time((String)o[11]);
              shops.setShop_bus((String)o[12]);
              shops.setShop_examine((Integer)o[13]);
              shops.setShop_lock((Integer)o[14]);
              shops.setShop_lock_reason((String)o[15]);
              user.setId((Integer)o[16]);
              shops.setUser(user);
              shop.add(shops);
       }
       return shop;
    }
像我上面的方法虽然实现了,但我觉得不是很妥,因为要是我数据库结构变了,这里也得跟着改,很不容易处理,有木有人知道更好 的办法呀?跪求高手指点