public List<Object[]> findByRoleId(final String roleid) {
List<Object[]> list = this.getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
String sql="select DISTINCT CASE r.RoleID WHEN ? THEN 1 ELSE 2 END AS flag,"
+" u.UserID, u.UserName,u.LogonName from elec_user u"
+" LEFT JOIN elec_user_role r"
+" on u.UserID=r.UserID"
+" and r.RoleID=?"
+" where u.IsDuty=1";
Query query = session. createSQLQuery(sql)
. addScalar("flag", Hibernate.STRING)
.addScalar("UserID", Hibernate.STRING)
.addScalar("UserName", Hibernate.STRING)
.addScalar("LogonName", Hibernate.STRING);
query.setParameter(0, roleid);
query.setParameter(1, roleid);
return query.list();
}
});
return list;
List<Object[]> list = this.getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session) throws HibernateException,
SQLException {
String sql="select DISTINCT CASE r.RoleID WHEN ? THEN 1 ELSE 2 END AS flag,"
+" u.UserID, u.UserName,u.LogonName from elec_user u"
+" LEFT JOIN elec_user_role r"
+" on u.UserID=r.UserID"
+" and r.RoleID=?"
+" where u.IsDuty=1";
Query query = session. createSQLQuery(sql)
. addScalar("flag", Hibernate.STRING)
.addScalar("UserID", Hibernate.STRING)
.addScalar("UserName", Hibernate.STRING)
.addScalar("LogonName", Hibernate.STRING);
query.setParameter(0, roleid);
query.setParameter(1, roleid);
return query.list();
}
});
return list;
}
createSQLQuery方法返回值是object对象数组,所以要想返回对象到jsp页面,还要重新设值
public List<User> findUsers(String roleid) {
//根据角色id查询用户信息
List<Object[]> list=userRoleDao.findByRoleId(roleid);
List<User> users=new ArrayList<User>();
if(list!=null &&list.size()>0){
for (int i=0;i<list.size();i++) {
User u=new User();
u.setFlag(list.get(i)[0].toString());
u.setUserID(list.get(i)[1].toString());
u.setUserName(list.get(i)[2].toString());
u.setLogonName(list.get(i)[3].toString());
users.add(u);
}
}
return users;
}