hibernate 多条件查询,查询部分字段等操作

1.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;

 

2.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;

 

3.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();

 

4.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();
}

 

5.hibernate 查询oracle序列

  long r = 0;
  Query query = this.getSession().createSQLQuery("SELECT SEQ_SYSMGMT.nextval FROM DUAL");
  List list = query.list();
  if (null!=list&& list.size() > 0) {
   r = Long.parseLong(list.get(0).toString());
  }
  return r;

 

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);

 

7.hibernate hql 删除

public void DeleteOfficeNote(OfficeNote officeNote) {
String hql = "delete OfficeNote where officeId=? and empId=?";
Query query = this.getSession().createQuery(hql);
query.setLong(0, officeNote.getOfficeId());
query.setLong(1, officeNote.getEmpId());
query.executeUpdate();
}

 

8.hibernate查询 带参数的查询

public OfficeNote findNoteByparam(OfficeNote officeNote) {
OfficeNote note=null;
String hql=" from OfficeNote t where t.officeId=? and empId=? ";
Object [] obj ={officeNote.getOfficeId(),officeNote.getEmpId()};
List list=this.getHibernateTemplate().find(hql, obj);
if(null!=list&&list.size()>0){
note=(OfficeNote)list.get(0);
}
return note;
}

 

9.Hibernate sql查询

String sql=" select e.id as id,e.name as name,e.address as address,d.id as deptId,d.dep_name as   deptName  from  emp_info e left join dep_info d on e.dep_id=d.id ";
 Session session =this.getSession();
 try{
  SQLQuery sqlquery=(SQLQuery)session.createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(MsgInfo.class));
  sqlquery.addScalar("id",Hibernate.INTEGER);
  sqlquery.addScalar("name",Hibernate.STRING);
  sqlquery.addScalar("address",Hibernate.STRING);
  sqlquery.addScalar("deptId",Hibernate.INTEGER);
  sqlquery.addScalar("deptName",Hibernate.STRING);
  List list=sqlquery.list();
  for(int i=0;i<list.size();i++){
   MsgInfo msgInfo=(MsgInfo)list.get(i);
   msgList.add(msgInfo);
  }

}catch(Exception ce){
  ce.printStackTrace();
 }

 

10.Hibernate sql查询2

DetentionWayBillDetail wayBillDetail=null;
  String sql=" SELECT T.WAYBILL_NO waybillNo,T.CHECK_RESULT chkResult,T.IS_FOLLOW isFollow,T.CHK_PERSON chkPerson," +
       " CAST(T.CREATE_TIME AS TIMESTAMP) createTime　FROM TM_DETEN_CHECKRESULT T WHERE T.WAYBILL_NO= ? ";
  Query query=session.createSQLQuery(sql);
  query.setParameter(0, waybillNo);
  List resultList=query.list();
  if(null!=resultList&&resultList.size()>0)
  try{
   Object[] result = (Object[]) resultList.get(0);
   wayBillDetail=new DetentionWayBillDetail();
   wayBillDetail.setWaybillNo((String) result[0]);
   wayBillDetail.setChkResult((String)result[1]);
   if("N".equals(result[2])){
    wayBillDetail.setIsFollow("否");
   }else{
    wayBillDetail.setIsFollow("是");
   }
   wayBillDetail.setChkPerson((String)result[3]);
   wayBillDetail.setCreateTime((Date)result[4]); 

 

11.Hibernate sql插入

String sql=" INSERT INTO TM_WORKTIME_CONFIG(WORK_ID,AREA_CODE,WORK_START_TIME,WORK_END_TIME,DEPT_CODE," +
       " EMP_NO,CREATE_TIME,EFFECTIVE_TIME) VALUES(SEQ_SYSMGMT.nextval,?,?,?,?,?,?,?)";
  Query query=this.getSession().createSQLQuery(sql);
  List<Object> paramList=new ArrayList<Object>();
  paramList.add(workTimeConfig.getAreaCode());
  paramList.add(workTimeConfig.getWorkStartTime());
  paramList.add(workTimeConfig.getWorkEndTime());
  paramList.add(workTimeConfig.getDeptCode());
  paramList.add(workTimeConfig.getEmpNo());
  paramList.add(new Date());
  paramList.add(workTimeConfig.getEffecTiveTime());
  Object[] values = paramList.toArray();
  for (int i = 0; i < values.length; i++) {
   query.setParameter(i, values[i]); 
  } 
  query.executeUpdate();   

 

12.hibernate 分页查询方法

public List<TransferOnTime> findTransferOnTimeByParam(final QueryParam queryParam,String [] deptValue,int deptLevel)throws DaoException{

final List<Object> paramList=new ArrayList<Object>();

final String sqlString = generateSQL(queryParam,paramList,deptValue,deptLevel);

return (List<TransferOnTime>) getHibernateTemplate().execute(new HibernateCallback() {

public Object doInHibernate(Session session) throws HibernateException {

Query query = session.createSQLQuery(sqlString);

query.setFirstResult(queryParam.getPageSize()*(queryParam.getPageIndex()));

query.setMaxResults(queryParam.getPageSize());

if (paramList != null && paramList.size() > 0) {

Object[] values = paramList.toArray();

for (int i = 0; i < values.length; i++) {

query.setParameter(i, values[i]);

}

}

return findTransferOnTimeResult(query.list(),queryParam);

}

});

}

 

private List<TransferOnTime> findTransferOnTimeResult(List<Object[]> transferValue,QueryParam queryParam){

List<TransferOnTime> transferOnTimeList=new ArrayList<TransferOnTime>();

//查询类型

String typeId=queryParam.getQueryValue("query_sumeryType");

TransferOnTime transferOnTime=null;

for(int i=0;i<transferValue.size();i++){

  transferOnTime=new TransferOnTime();

 Object [] transferObj=transferValue.get(i);

if(TdMonitorConstants.QUERY_BATCHCODE.equals(typeId)){

    this.setBatchCodeInfo(transferOnTime, transferObj, queryParam);

}else{

    this.setAreaAndTransfer(transferOnTime, transferObj, queryParam);

}

this.setMainlineVehicleLoadCapacity(transferOnTime);

transferOnTimeList.add(transferOnTime);

}

return transferOnTimeList;

}

13.HIbernate sql 修改

public void updateCommentDetail(final TimeCommitment commitment) {

final String sql=SQLProvider.getSQL("updateTimeDtl").trim();

getHibernateTemplate().execute(new HibernateCallback() {

public Object doInHibernate(Session session) {

SQLQuery query = session.createSQLQuery(sql); 

query.setParameter(0, commitment.getAuditDay());

query.setParameter(1, commitment.getAuditDay());

query.setParameter(2, commitment.getAuditTime());

query.setParameter(3, commitment.getIsUpdate());

query.setParameter(4, commitment.getEditEmp());

query.setParameter(5, commitment.getEditTime());

query.setParameter(6, commitment.getEditInfo());

query.setParameter(7, commitment.getProduceType());

query.setParameter(8, commitment.getSrcZoneCode());

query.setParameter(9, commitment.getDestCityCode());

query.setParameter(10, commitment.getBatchCode());

query.setParameter(11, commitment.getVersionDt());

query.executeUpdate();

               return null;

           }

       });

 

}

 

14.HIbernate sql 删除

public void deleteMissInfo(final String plateNumber, final String checkZoneCode,

final String doubtMissTime) throws Exception {

final String sql=this.generteMissTimeSql(plateNumber);

getHibernateTemplate().execute(new HibernateCallback() {

 public Object doInHibernate(Session session) {

    SQLQuery query = session.createSQLQuery(sql);  

    query.setParameter(0, checkZoneCode);

    query.setParameter(1, doubtMissTime+":00");

    query.setParameter(2, doubtMissTime+":59");

    query.setParameter(3, plateNumber);

    query.executeUpdate();

    return null;

        }

    });

}