我有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;
}
像我上面的方法虽然实现了,但我觉得不是很妥,因为要是我数据库结构变了,这里也得跟着改,很不容易处理,有木有人知道更好 的办法呀?跪求高手指点
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;
}
像我上面的方法虽然实现了,但我觉得不是很妥,因为要是我数据库结构变了,这里也得跟着改,很不容易处理,有木有人知道更好 的办法呀?跪求高手指点
解决方案 »
- java初学者想做一个交换物品的网站,应该从何下手,,急急急!!!
- JAVA 中的集合框架
- Ext中FormPanel数据显示的问题,高手帮忙下
- Struts2 遇到的问题
- Hibernate中关于MSSQL数据库的increment方式的疑问
- 为什么要继承DispatchAction,JdbcDaoSupport类
- 用过hibernate的请进
- resin 3.0.8 端口80,启动正常,为什么访问确显示404错误,resin3.0.8好像没有监听..
- <html〉标签出错,Cannot find bean org.apache.struts.taglib.html.BEAN in any scope
- 如何创建一个指定编码的XML文件
- 前后端分开后的数据传输 hessian
- 求一个Strut2+Spring+Hibernate实现的登录增删改查
不过结合着spring试试应该是可以的。