hibernate 的几种查询——工作中使用到的
HQL是hibernate自己的一套查询语言，于SQL语法不同，具有跨数据库的优点。示例代码：
// 获取检查单实例列表
String tplSql = "FROM CheckFormTemplate t where t.type=-1 and t.id in("+ instanceIdStr +")";
Session session = getSession();
return session.createQuery(tplSql).list();
Long count = (Long) session.createQuery("select count(*) from CheckFormTemplate where type=-1").uniqueResult();
//传递参数
String result = (String) session .createQuery("select result from CheckFormTemplate where id=:id")
.setParameter("id", id)
.uniqueResult();
List list = session.createQuery("from CheckForm where dataId=:id order by checkItem,checkNote")
.setParameter("id", id)
.list();
//删除
session.createQuery("delete from DictEntity").executeUpdate();
String hql = "delete BasicDataObject where id = '" + id + "'";
String sql = "delete from data_object_basic_old where id = '" + id
+ "' and owner_id = '" + ownerId + "'";
try {
this.getHibernateSession().createQuery(hql).executeUpdate();
this.getHibernateSession().createSQLQuery(sql).executeUpdate();
//查询
- session.createQuery("update Student s set s.name=? where s.id < ?")
- .setParameter(0, "李四")
- .setParameter(1, 5)
- .executeUpdate();
- List students = session.createQuery("select s.id, s.name from Student s where s.name like ?")
- .setParameter(0, "%1%")
- .list();
//分页查询
- List students = session.createQuery("select s.id, s.name from Student s where s.name like ?")
- .setParameter(0, "%1%")
- .list();
//createSqlQuery查询
public QueryResult<TeachnicalAgreement> loadTeachData(Map<String, Object> param) {
StringBuffer sbSql = new StringBuffer();
sbSql.append("select T.ID \"id\",\n");
sbSql.append(" T.DEPT_ID \"deptId\",\n");
sbSql.append (" tt.DEPARTMENT_NAME \"deptName\" ,\n");
sbSql.append(" T.DESIGNER \"designer\", \n");
sbSql.append(" T.PROTOCOL_NUMBER \"protocolNumber\",\n");
sbSql.append(" T.TEACH_NAME \"teachName\",\n");
sbSql.append(" T.VENDOR_NAME \"vendorName\" ,\n");
//sbSql.append("to_char( T.ISSUE_DATE ,'yyyy-MM-DD') \"issueDate\",");
sbSql.append(" T.ISSUE_DATE \"issueDate\",\n");
sbSql.append(" T.REQRETURN_DATE \"reqreturnDate\" ,\n");
//sbSql.append("to_char( T.REQRETURN_DATE ,'yyyy-MM-DD') \"reqreturnDate\",");
sbSql.append(" T.RETURN_DATE \"returnDate\" ,\n");
//sbSql.append("to_char( T.RETURN_DATE ,'yyyy-MM-DD') \"returnDate\",");
sbSql.append(" T.CONTRACT_NUMBER \"contractNumber\" ,\n");
sbSql.append(" T.CONTRACT_DATE \"contractDate\" ,\n");
//sbSql.append("to_char( T.CONTRACT_DATE ,'yyyy-MM-DD') \"contractDate\",");
sbSql.append(" T.REMARKS \"remarks\" ,\n");
sbSql.append(" T.TEACH_STATUS \"teachStatus\" ,\n");
sbSql.append(" T.TEACH_GROUP \"teachGroup\" ,\n");
sbSql.append(" T.TEACH_MODEL \"teachModel\" ,\n");
sbSql.append(" T.CREATE_TIME \"createTime\" ,\n");
//sbSql.append("to_char( T.CREATE_TIME ,'yyyy-MM-DD') \"createTime\",");
sbSql.append(" T.CREATOR \"creator\" \n");
sbSql.append("\n");
sbSql.append(" from TECHNICAL_AGREEMENT t, sys_department tt\n");
sbSql.append("\n");
sbSql.append(" where 1 = 1");
sbSql.append(" and T.DEPT_ID= tt.DEPARTMENT_ID");
if(param.get("modeltype")!=null && !"".equals(param.get("modeltype"))){
sbSql.append(" and T.TEACH_MODEL= '"+param.get("modeltype").toString()+"'");
}
//拼接查询参数
if(param.get("deptId")!=null && !"".equals(param.get("deptId"))){
sbSql.append(" and T.DEPT_ID= '"+param.get("deptId").toString()+"'");
}
//总条数
StringBuffer countSql = new StringBuffer();
countSql.append("select count(*) from technical_agreement t, sys_department tt where 1=1");
countSql.append(" and T.DEPT_ID= tt.DEPARTMENT_ID");
if(param.get("modeltype")!=null && !"".equals(param.get("modeltype"))){
countSql.append(" and T.TEACH_MODEL= '"+param.get("modeltype").toString()+"'");
}
if(param.get("modeltype")!=null && !"".equals(param.get("modeltype"))){
countSql.append(" and T.TEACH_MODEL= '"+param.get("modeltype").toString()+"'");
}
Query query = this
.getHibernateSession()
.createSQLQuery(sbSql.toString())
.addScalar("id")
.addScalar("deptId")
.addScalar("deptName")
.addScalar("designer")
.addScalar("protocolNumber")
.addScalar("teachName")
.addScalar("vendorName")
.addScalar("issueDate", DateType.INSTANCE)
// .addScalar("issueDate")
.addScalar("reqreturnDate", DateType.INSTANCE)
.addScalar("returnDate",DateType.INSTANCE)
.addScalar("contractNumber")
.addScalar("contractDate",DateType.INSTANCE)
.addScalar("teachStatus")
.addScalar("teachGroup")
.addScalar("teachModel")
.addScalar("remarks")
.addScalar("createTime",DateType.INSTANCE)
.addScalar("creator")
.setResultTransformer(
Transformers.aliasToBean(TeachnicalAgreement.class));
long counts = ((BigDecimal) this.getHibernateSession()
.createSQLQuery(countSql.toString()).uniqueResult()).longValue();
query.setFirstResult(Integer.parseInt(param.get("start").toString())).setMaxResults(
Integer.parseInt(param.get("limit").toString()));
List<TeachnicalAgreement> resultList = query.list();
for (TeachnicalAgreement object : resultList) {
//DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// 人员
if (object.getDesigner() != null && !object.getDesigner().equals("")) {
StringBuffer sbSqlq = new StringBuffer();
sbSqlq.append("select USER_NAME from SYS_USER T WHERE 1=1 AND\n");
sbSqlq.append("T.USER_ID='"+object.getDesigner()+"'");
List<Map<String, Object>> temp =JDBCBaseDaoImpl.getJDBCTemplate().queryForList(sbSqlq.toString());
String USER_NAME =temp.get(0).get("USER_NAME").toString();
object.setDesignerName(USER_NAME);
//map.put("DESIGNER", USER_NAME);
} else {
object.setDesignerName("");
}
}
QueryResult<TeachnicalAgreement> queryResult = new QueryResult();
queryResult.setRecordtotal(counts);
queryResult.setResultSet(resultList);
return queryResult;
}
String sql = "insert into SYS_TEAM (DESCRIPTION, " +
"JIANPIN_NAME, " +
"PARENT_ID, " +
"PINYIN_NAME, " +
"PROJECT_ID, " +
"PROJECT_TYPE, " +
"ROW_CREATE_CLIENT, " +
"ROW_CREATE_TIME, " +
"ROW_CREATOR, " +
"ROW_SORT_ORDER, " +
"ROW_UPDATE_CLIENT, " +
"ROW_UPDATE_TIME, " +
"ROW_UPDATER, " +
"TEAM_CODE, " +
"TEAM_NAME, " +
"TEAM_ID) values ('',:jianpinName,:parentId,:pinyinName, :projectId, :projectType, :createClient, :createTime, :creator, :sortOrder, :updateClient, :updateTime, :updater, '', :teamName, :teamId)";
Session session = this.getHibernateSession();
session.createSQLQuery(sql)
.setParameter("jianpinName", team.getJianPinName())
.setParameter("parentId", team.getParentId())
.setParameter("pinyinName", team.getPinYinName())
.setParameter("projectId", team.getProjectId())
.setParameter("projectType", team.getProjectType())
.setParameter("createClient", team.getRowCreateClient())
.setParameter("createTime", team.getRowCreateTime())
.setParameter("creator", team.getRowCreator())
.setParameter("sortOrder", team.getRowSortOrder())
.setParameter("updateClient", team.getRowUpdateClient())
.setParameter("updateTime", team.getRowUpdateTime())
.setParameter("updater", team.getRowUpdater())
.setParameter("teamName", team.getTeamName())
.setParameter("teamId", team.getTeamId())
.executeUpdate();
String sql = "update commnct_notice_notification cnn set cnn.filesize = '"
+ fileSize
+ "', cnn.filepath = '"
+ filePath
+ "', cnn.deleteflag = '', cnn.notification_security = '"
+ notificationSecurityLevel
+ "', cnn.notification_num = '"
+ notificationNum
+ "', cnn.model_identification = '"
+ notificationModelIdentify
+ "', cnn.notification_company = '"
+ notificationCompany
+ "', cnn.notification_drawing_num ='"
+ notificationDrawingNum
+ "', cnn.notification_replace = '"
+ notificationReplace
+ "' where cnn.pid = '"
+ notificationVo.getNotificationDocId()
+ "' and cnn.noticeid = '"
+ notificationVo.getNotificationId()
+ "'";
int updateCount = getNotificationDao().getHibernateSession()
.createSQLQuery(sql).executeUpdate();
