正文
HQL多种查询实现
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
1、返回int
public int countByUsername(String username) {
String hql = "select count(*) from BeanCarUser bean where bean.userName=:username";
Query query = getSession().createQuery(hql);
query.setParameter("username", username);
return ((Number) query.iterate().next()).intValue();
}
2、返回list集合
@SuppressWarnings("unchecked")
public List<CmsZfysj> findByZqmj(String zqmj, Date clsj) {
Finder f = Finder.create("from CmsZfysj bean where 1=1 ");
if (null != zqmj && !"".equals(zqmj)) {
f.append(" and bean.police_id like :zqmj");
f.setParam("zqmj", "%" + zqmj + "%");
}
if (null != clsj && !"".equals(clsj)) {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
f.append(" and TO_CHAR(bean.import_time,'yyyy-MM-dd') >= '"
+ df.format(clsj) + "'");
}
if (null != clsj && !"".equals(clsj)) {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
f.append(" and TO_CHAR(bean.capture_time,'yyyy-MM-dd') <= '"
+ df.format(clsj) + "'");
}
return find(f);
}
3、多表关联查询返回List
（1）多表关联查询list
@SuppressWarnings("unchecked")
public List<BeanCompanyEmp> getListCompanyIsNull(BeanCompanyEmp beanCompanyEmpParam) {
StringBuffer sql = new StringBuffer();
sql.append(" select ");
sql.append(" cemp.id ,cemp.doc_no ,jux.mobile ,jux.comefrom,jux.realname,jr.role_name ,cemp.latitude latitude ,cemp.longitude longitude ");
sql.append(" from company_emp cemp, ");
sql.append(" jc_user jc, ");
sql.append(" jc_user_ext jux, ");
sql.append(" jc_role jr, ");
sql.append(" jc_user_role jur ");
sql.append(" where cemp.id = jc.user_id ");
sql.append(" and cemp.id = jux.user_id ");
sql.append(" and jur.user_id = jc.user_id ");
sql.append(" and jur.role_id = jr.role_id ");
sql.append(" and cemp.belong_company is null ");
String sqls = sql.toString();
Query query = sessionFactory.getCurrentSession().createSQLQuery(sqls.toString());
List list = query.list();
List<BeanCompanyEmp> result=new ArrayList<BeanCompanyEmp>();
if(list!=null&&list.size()>0){
for (Object object : list) {
int i = 0;
BeanCompanyEmp modell=new BeanCompanyEmp();
Object[] temp = (Object[]) object;
modell.setId(Integer.parseInt( temp[i++] +""));
modell.setDoc_no(String.valueOf(temp[i++]));
modell.setMobile(String.valueOf(temp[i++]));
modell.setComefrom(String.valueOf(temp[i++]));
modell.setRealName(String.valueOf(temp[i++]));
modell.setRoleNames(String.valueOf(temp[i++]));
modell.setLatitude(String.valueOf(temp[i++]));
modell.setLongitude(String.valueOf(temp[i++]));
result.add(modell);
}
}
return result;
}
（2）多表查询带分页功能
public Pagination getLookPage(BeanCompanyEmp beanCompanyEmpParam, int pageNo, int pageSize) {
Pagination pagination = new Pagination();
StringBuffer sql = new StringBuffer();
sql.append(" select ");
sql.append(" cemp.id id,cemp.doc_no doc_no,jux.mobile mobile,jux.comefrom comefrom,jux.realname realname,jr.role_name role_name ");
sql.append(" from company_emp cemp, ");
sql.append(" jc_user jc, ");
sql.append(" jc_user_ext jux, ");
sql.append(" jc_role jr, ");
sql.append(" jc_user_role jur ");
sql.append(" where cemp.id = jc.user_id ");
sql.append(" and cemp.id = jux.user_id ");
sql.append(" and jur.user_id = jc.user_id ");
sql.append(" and jur.role_id = jr.role_id ");
sql.append(" and cemp.belong_company is null ");
//TODO这最好查询角色名称
if(beanCompanyEmpParam.getDoc_no() != null && beanCompanyEmpParam.getDoc_no() != ""){
sql.append(" and cemp.doc_no like '%"+beanCompanyEmpParam.getDoc_no()+"%'" );
}
if(beanCompanyEmpParam.getRealName() != null && beanCompanyEmpParam.getRealName() != ""){
sql.append(" and jux.realname like '%"+beanCompanyEmpParam.getRealName()+"%'" );
}
String sqls = sql.toString();
Query query = sessionFactory.getCurrentSession().createSQLQuery(sqls.toString());
List list = query.list();
List<BeanCompanyEmp> result=new ArrayList<BeanCompanyEmp>();
if(list!=null&&list.size()>0){
for (Object object : list) {
int i = 0;
BeanCompanyEmp modell=new BeanCompanyEmp();
Object[] temp = (Object[]) object;
modell.setId(Integer.parseInt( temp[i++] +""));
modell.setDoc_no(String.valueOf(temp[i++]));
modell.setMobile(String.valueOf(temp[i++]));
modell.setComefrom(String.valueOf(temp[i++]));
modell.setRealName(String.valueOf(temp[i++]));
modell.setRoleNames(String.valueOf(temp[i++]));
result.add(modell);
}
}
pagination.setList(result);
pagination.setPageNo(pageNo);
pagination.setPageSize(pageSize);
return pagination;
}
4、获取参数Collection集合
/**
* 获取参数类型集合
*/
@SuppressWarnings("unchecked")
public Collection<Object> getList() {
Finder f = Finder.create("select distinct bean.paramType from BeanParamManager bean where bean.dataStatus=0 and bean.sslx ='中心参数'");
return find(f);
}
5、返回值是void
public void insertDayModel(Integer dayNo){
List<Object> list = dayDao.selectParkno();
for (Object b : list) {
String parkNo = b.toString();
BeanDayTraffic bean = new BeanDayTraffic();
bean.setParkno(parkNo);
Date date = new Date();
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
calendar.add(Calendar.DAY_OF_MONTH, +dayNo);//+1今天的时间加一天
date = calendar.getTime();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String sDate = sdf.format(date);
try {
Date dbDate = sdf.parse(sDate);
bean.setDay_time(dbDate);
} catch (ParseException e) {
// TODO Auto-generated catch block
System.out.println(e);
}
bean.setTypes("Day");
bean.setTime1(0);
bean.setTime2(0);
bean.setTime3(0);
bean.setTime4(0);
bean.setTime5(0);
bean.setTime6(0);
dayDao.save(bean);
}
}
