java DAO类 select方法范例，查询关键词

最新推荐文章于 2023-02-24 13:50:43 发布

kissing7

最新推荐文章于 2023-02-24 13:50:43 发布

阅读量2.2k

点赞数

分类专栏： JAVAEE 文章标签：类

本文链接：https://blog.csdn.net/kissing7/article/details/9057993

版权

JAVAEE 专栏收录该内容

54 篇文章 1 订阅

订阅专栏

    public Page selectWarnList(String startDate, String lastDate, String warnName,String warnlightName,int isPage,
           String pageNum, String iswarn, int isPrint) {
       List<WarnDto> list = new ArrayList<WarnDto>();
       try {

           StringBuffer sql = new StringBuffer();

           StringBuffer where = new StringBuffer(" where 2>1 ");
           where.append(" and w_iswarn=").append(iswarn);
           where.append(" and w_type in (select set_id from systemsetting where set_value<>'' and set_value is not null and set_id between 1 and 40)");

           sql.append("select w.*,lt_name,lpt_name,road_name,area_name from warning w"
                           + " left join lightnode on (w_lightnodeid=lt_id)"
                           + " left join lamppost on (lt_lamppostid=lpt_id) "
                           + " left join roadnode on (road_id = lpt_roadId)"
                           + " left join areanode on (area_id=road_areaId)");

           if (startDate != null && !"".equals(startDate)) {
               where.append(" and w_warnDate>=STR_TO_DATE('");
               where.append(startDate).append("','%Y-%m-%d')");
           }

           if (lastDate != null && !"".equals(lastDate)) {
               where.append(" and w_warnDate<=date_add(STR_TO_DATE('");
               where.append(lastDate).append("','%Y-%m-%d'),interval 1 day)");

           }
           if (warnName != null && !"".equals(warnName)) {
               where.append(" and w_name like '%");
               where.append(warnName).append("%'");//使用append方式获取关键词的值。

           }

           if (warnlightName != null && !"".equals(warnlightName)) {
               where.append(" and lt_name like '%");
               where.append(warnlightName).append("%'");

           }

           sql.append(where);

           String sqlCount = sql.toString();

           sql.append(" order by w_warnDate desc");

           where_var = where.toString();

           log.info(sql);

           if (isPrint == 0) {
               con = DBConnection.getConnection();
               if(pageNum==null||"".equals(pageNum)){
                   pageNum="1";
               }
               if (isPage == 1) {
                   page.setTotalCount(BaseUtils.getTotalCount(sqlCount, con));

                   sql.append(" limit ").append(
                           (Integer.parseInt(pageNum) - 1)
                                   * page.getRowsPage()).append(",").append(
                           page.getRowsPage());
               }

               ps = con.prepareStatement(sql.toString());

               rs = ps.executeQuery();

               /*
                    //根据warning表的lightnodeid查询lightnode,lamppost,areanode表，并取出表中t_name,lpt_name,road_name,area_name.
                   String[] lightnodeid =new String[1];
                   con1 = DBConnection.getConnection();
                   ps1 = con1.prepareStatement("select lt_name,lpt_name,road_name,area_name from warning w"
                           + " left join lightnode on (w_lightnodeid=lt_id)"
                           + " left join lamppost on (lt_lamppostid=lpt_id) "
                           + " left join roadnode on (road_id = lpt_roadId)"
                           + " left join areanode on (area_id=road_areaId)"
                           + " where w.lightnodeid = lightnodeid[0]"

                   );
                   rs1 = ps1.executeQuery();
                      while (rs.next()){lightnodeid[0]=rs.getString("lightnodeid");}

                   sql.append(" where op_userid like '%").append(lightnodeid[0])
                   .append("%' ");
                   DBConnection.free(rs1, ps1, con1);

               */


               while (rs.next()) {
                   WarnDto dto = new WarnDto();

                   dto.setId(rs.getString("w_id"));
                   dto.setWarnName(rs.getString("w_name"));
                   dto.setContext(rs.getString("w_context"));
                   dto.setIsWarn(rs.getString("w_iswarn"));
                   dto.setPosition(rs.getString("w_position"));
                   dto.setCancelReasons(rs.getString("w_cancelreasons"));
                   dto.setCancelWarnUserId(rs.getString("w_cancelwarnuserid"));
                   dto.setCancelWarnUserName(rs.getString("w_cancelwarnusername"));
                   dto.setCancelDate(rs.getString("w_cancelwarndate"));
                   dto.setSysDate(rs.getString("w_warnDate"));
                   dto.setLtName(rs.getString("lt_name"));
                   dto.setLptName(rs.getString("lpt_name"));
                   dto.setRoadName(rs.getString("road_name"));
                   dto.setAreaName(rs.getString("area_name"));

                   list.add(dto);
               }

               page.setList(list);
               page.setCurrPage(Integer.parseInt(pageNum));
           }
       } catch (Exception ex) {
           ex.printStackTrace();
       } finally {
           DBConnection.free(rs, ps, con);
       }
       return page;
   }