1.hibernate sql语句查询
String sql=" select e.emp_id,e.emp_nam,d.dpt_nam from employee e,dept d where e.dpt_id=d.dpt_id "+
" and e.emp_id in (select t.emp_id from EMP_GRP_MAP t where t.egp_id="+groupId+")";
List<UserBean> userList=new ArrayList<UserBean>();
Session session = this.getSession();
Connection con =session.connection();
try{
PreparedStatement ps= con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()){
UserBean userBean=new UserBean();
userBean.setUserId(rs.getLong("emp_id"));
userBean.setUserName(rs.getString("emp_nam"));
userBean.setDeptName(rs.getString("dpt_nam"));
userList.add(userBean);
}
}catch(Exception ce){
ce.printStackTrace();
}finally{
try{
con.close();
}catch(Exception ce){
ce.printStackTrace();
}
}
return userList;
2.hibernate hql语句查询一个字段
String userName="";
String hql=" select employeeName from Employee where id="+empId+" ";
Query query =this.getSession().createQuery(hql);
List<String> list = query.list();
for(String str : list){
userName=str;
}
return userName;
3.hibernate hql语句查询二个字段(或更多的字段)
List<DropDownModel> modeList=new ArrayList<DropDownModel>();
String hql=" select id,departmentName from Department ";
Query query =this.getSession().createQuery(hql);
List<Object[]> list = query.list();
for(Object[] object : list){
DropDownModel model=new DropDownModel();
model.setId(Long.parseLong(object[0].toString()));
model.setName(object[1].toString());
modeList.add(model);
}
return modeList;
4.hibernate hql语句使用聚合函数
String hql = "select count(*) from Department where 1=1 and departmentNumber = ? " ;
Query query = this.getSession().createQuery(hql);
if (paraList != null) {
for (int i = 0; i < paraList.size(); i++) {
query.setParameter(i, paraList.get(i));
}
}
return ((Number) query.uniqueResult()).intValue();
5.hibernate hql语句查询
String sql=" from Employee where department.id = ? order by displayOrder";"
public List findByCriteriaByHQL(String sqlStmt, List args) {
Query query = this.getSession().createQuery(sqlStmt);
if (args != null) {
int size = args.size();
for (int i = 0; i < size; i++) {
query.setParameter(i, args.get(i));
}
}
return query.list();
}
6.hibernate hql语句修改部分字段
例如：
String hql=" Update OfficeReceipt set lwDept=?,repDate=?,lwWh=?,lwTitle=? where id=? ";
Object [] obj={officeReceipt.getLwDept(),officeReceipt.getRepDate(),officeReceipt.getLwWh (),officeReceipt.getLwTitle(),officeReceipt.getId()};
this.getHibernateTemplate().bulkUpdate(hql, obj);
