hibernate原生态sql

最新推荐文章于 2023-05-05 22:49:21 发布

寂寞烟

最新推荐文章于 2023-05-05 22:49:21 发布

阅读量688

点赞数

CC 4.0 BY-SA版权

分类专栏： hibernate

本文链接：https://blog.youkuaiyun.com/lijianhua_showit/article/details/28635977

hibernate 专栏收录该内容

21 篇文章

订阅专栏

本文详细介绍了如何利用Hibernate进行原生态SQL查询，包括设置查询参数、使用SimpleDateFormat进行日期格式转换以及实现复杂过滤条件。通过实例展示了如何在查询文章时，结合用户信息、关键字搜索、时间范围筛选等条件，高效地获取所需数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

hibernate原生态sql查询

SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

  StringBuffer sql = new StringBuffer("select ka.article_ID,ka.article_name,ka.article_desc,ka.remark,ka.article_content,ka.node_ID,ka.create_time,ka.user_name,"
  +" ka.article_file_name,ka.file_size,ka.file_type,pkn.node_title,pkn.node_code"
  +" from t_kbm_article ka LEFT JOIN( "
  +" SELECT kn.node_ID,kn.node_title, kn.node_code from t_kbm_node kn where kn.node_code LIKE '001%' and exists("
  +" select pk.kbm_node from t_permission_kbm pk where pk.role_id="+values.getUser().getRole().getId()+" and pk.org_id="+values.getUser().getOrg().getId()+")) pkn "
  +" on ka.node_ID= pkn.node_ID where 1=1 ");


  KbmNode fkbmNode = this.findById(KbmNode.class, values.getKbmNode().getNodeId());
  if (fkbmNode!=null) {
   values.getKbmNode().setNodeCode(fkbmNode.getNodeCode());
   sql.append(" and pkn.node_code like '"+values.getKbmNode().getNodeCode()+"%' ");
  }
  if(values.getUser().getName() != null&&!"".equals(values.getUser().getName())){
   sql.append(" and ka.user_name like '%"+values.getUser().getName()+"%' ");
  }
  if(StringUtil.isNotEmpty(values.getKbmArticleName())){
   sql.append(" and ka.article_name like '%"+values.getKbmArticleName()+"%' ");
  }
  if(StringUtil.isNotEmpty(values.getKeyWord())){
   sql.append(" and ka.remark like like '%"+values.getKeyWord()+"%' ");
  }
  if(values.getStartTime() != null){
      String startTime = formatter.format(values.getStartTime());
    sql.append(" and ka.create_time >= '"+startTime+"'");
   }
   if(values.getEndTime() != null){
    String endTime = formatter.format(values.getEndTime());
    sql.append(" and ka.create_time <= '"+endTime+"' ");
   }
    sql.append(" ORDER BY ka.sortCode desc");
//    sql.append(" limit "+pageSize*(page-1)+", "+pageSize+"");
    List kbmArticleList = this.getSession().createSQLQuery(sql.toString()).setMaxResults(pageSize).setFirstResult(pageSize*(page-1)).list();
          List list = new ArrayList();
    for (Object temp : kbmArticleList) {
     Object[] articleObj = (Object[])temp;
           KbmArticle kbmArticle = new KbmArticle();
           KbmNode kbmNode = new KbmNode();
           User user =new User();
           kbmArticle.setArticleId(toInteger(articleObj[0]));
           kbmArticle.setArticleName(toStr(articleObj[1]));
           kbmArticle.setArticleDesc(toStr(articleObj[2]));
           kbmArticle.setRemark(toStr(articleObj[3]));
           kbmArticle.setArticleContent(toStr(articleObj[4]));
           kbmNode.setNodeId(toInteger(articleObj[5]));
           kbmArticle.setCreateTime(toDate(articleObj[6]));
           user.setName(toStr(articleObj[7]));
           kbmArticle.setUserName(toStr(articleObj[7]));
           kbmArticle.setActicleFileName(toStr(articleObj[8]));
           kbmArticle.setFileSize(toLong(articleObj[9]));
           kbmArticle.setFileType(toStr(articleObj[10]));
           kbmNode.setNodeTitle(toStr(articleObj[11]));
           kbmNode.setNodeCode(toStr(articleObj[12]));
           kbmArticle.setKbmNode(kbmNode);
           kbmArticle.setUser(user);
           list.add(kbmArticle);
  }