コード例 #1
0
  @Override
  public ArrayList<GearBoxOil> getGearBoxOils(
      int currentPage,
      int elementsInList,
      LinkedList<ManufacturerSelected> manufacturersSelected,
      LinkedList<GearBoxType> gearBoxTypeFilter,
      LinkedList<OilStuff> oilStuffFilter,
      HashMap<String, Boolean> viscosityFilter,
      double minPrice,
      double maxPrice,
      double currentMinValueFilter,
      double currentMaxValueFilter,
      double currentMinJudgementFilter,
      double currentMaxJudgementFilter,
      String searchField) {

    StringBuilder strManufacturerFilter = new StringBuilder();
    if (manufacturersSelected.size() > 0) {
      boolean bFind = false;
      for (ManufacturerSelected currentMan : manufacturersSelected) {
        if (currentMan.isSelected()) {
          bFind = true;
          break;
        }
      }
      if (bFind) {
        for (ManufacturerSelected currentMan : manufacturersSelected) {
          if (currentMan.isSelected()) {
            if (strManufacturerFilter.length() != 0) {
              strManufacturerFilter.append(", ");
            }

            strManufacturerFilter.append(currentMan.getId());
          }
        }
        strManufacturerFilter.insert(0, " and (man.id IN (");
        strManufacturerFilter.append("))");
      }
    }

    StringBuilder strGearBoxTypeFilter = new StringBuilder();
    if (gearBoxTypeFilter.size() > 0) {
      boolean bFind = false;
      for (GearBoxType current : gearBoxTypeFilter) {
        if (current.isSelected()) {
          bFind = true;
          break;
        }
      }
      if (bFind) {
        for (GearBoxType current : gearBoxTypeFilter) {
          if (current.isSelected()) {
            if (strGearBoxTypeFilter.length() != 0) {
              strGearBoxTypeFilter.append(", ");
            }

            strGearBoxTypeFilter.append(current.getId());
          }
        }
        strGearBoxTypeFilter.insert(0, " and (gbt.id IN (");
        strGearBoxTypeFilter.append("))");
      }
    }

    StringBuilder strOilStuffFilter = new StringBuilder();
    if (oilStuffFilter.size() > 0) {
      boolean bFind = false;
      for (OilStuff current : oilStuffFilter) {
        if (current.isSelected()) {
          bFind = true;
          break;
        }
      }
      if (bFind) {
        for (OilStuff current : oilStuffFilter) {
          if (current.isSelected()) {
            if (strOilStuffFilter.length() != 0) {
              strOilStuffFilter.append(", ");
            }

            strOilStuffFilter.append(current.getId());
          }
        }
        strOilStuffFilter.insert(0, " and (os.id IN (");
        strOilStuffFilter.append("))");
      }
    }

    StringBuilder strViscosityFilter = new StringBuilder();
    if (viscosityFilter.size() > 0) {
      boolean bFind = false;
      for (Map.Entry<String, Boolean> current : viscosityFilter.entrySet()) {
        if (current.getValue()) {
          bFind = true;
          break;
        }
      }
      if (bFind) {
        for (Map.Entry<String, Boolean> current : viscosityFilter.entrySet()) {
          if (current.getValue()) {
            if (strViscosityFilter.length() != 0) {
              strViscosityFilter.append(", ");
            }

            strViscosityFilter.append("'" + current.getKey() + "'");
          }
        }
        strViscosityFilter.insert(0, " and (go.viscosity IN (");
        strViscosityFilter.append("))");
      }
    }

    String sqlQuery =
        "select * from"
            + "(select go.id as id, go.name as name, go.price as price"
            + ", go.gearboxtype AS gearboxtype, gbt.name AS gbt_name, go.oilstuff AS oilstuff, os.name AS os_name"
            + ", go.description AS description, go.judgement AS judgement, go.instock AS instock, go.discount AS discount"
            + ", go.photo AS photo, go.specification AS specification, go.viscosity AS viscosity"
            + ", go.value AS value, go.manufacturer AS manufacturer, man.name as man_name "
            + "   from gearboxoils  as go"
            + " 	LEFT JOIN oilstuff AS os ON (go.oilstuff=os.id)"
            + " 	LEFT JOIN gearboxtype AS gbt ON (go.gearboxtype=gbt.id)"
            + "		left join manufacturer as man on go.manufacturer=man.id "
            + "				where (go.price>=:minPrice) and (go.price<=:maxPrice)"
            + "					 and (go.value>=:currentMinValueFilter) and (go.value<=:currentMaxValueFilter) "
            + "					 and (go.Judgement>=:currentMinJudgementFilter) and (go.Judgement<=:currentMaxJudgementFilter)"
            + strGearBoxTypeFilter
            + strOilStuffFilter
            + strManufacturerFilter
            + strViscosityFilter
            + strSearchFilter(searchField)
            + " ORDER BY go.name ) as rez LIMIT :firstRow, :number";

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("firstRow", (currentPage - 1) * elementsInList);
    params.addValue("number", elementsInList);
    params.addValue("minPrice", minPrice);
    params.addValue("maxPrice", maxPrice);
    params.addValue("currentMinValueFilter", currentMinValueFilter);
    params.addValue("currentMaxValueFilter", currentMaxValueFilter);
    params.addValue("currentMinJudgementFilter", currentMinJudgementFilter);
    params.addValue("currentMaxJudgementFilter", currentMaxJudgementFilter);

    try {
      return (ArrayList<GearBoxOil>)
          jdbcTemplate.query(sqlQuery, params, new GearBoxOilRowMapper());
    } catch (EmptyResultDataAccessException e) {
      return new ArrayList<GearBoxOil>();
    }
  }
コード例 #2
0
  @Override
  public int getCountRows(
      int currentPage,
      int elementsInList,
      LinkedList<ManufacturerSelected> manufacturersSelected,
      LinkedList<GearBoxType> gearBoxTypeFilter,
      LinkedList<OilStuff> oilStuffFilter,
      HashMap<String, Boolean> viscosityFilter,
      double minPrice,
      double maxPrice,
      double currentMinValueFilter,
      double currentMaxValueFilter,
      double currentMinJudgementFilter,
      double currentMaxJudgementFilter,
      String searchField) {
    int result = 0;

    StringBuilder strManufacturerFilter = new StringBuilder();
    if (manufacturersSelected.size() > 0) {
      boolean bFind = false;
      for (ManufacturerSelected currentMan : manufacturersSelected) {
        if (currentMan.isSelected()) {
          bFind = true;
          break;
        }
      }
      if (bFind) {
        for (ManufacturerSelected currentMan : manufacturersSelected) {
          if (currentMan.isSelected()) {
            if (strManufacturerFilter.length() != 0) {
              strManufacturerFilter.append(", ");
            }

            strManufacturerFilter.append(currentMan.getId());
          }
        }
        strManufacturerFilter.insert(0, " and (man.id IN (");
        strManufacturerFilter.append("))");
      }
    }

    StringBuilder strGearBoxTypeFilter = new StringBuilder();
    if (gearBoxTypeFilter.size() > 0) {
      boolean bFind = false;
      for (GearBoxType current : gearBoxTypeFilter) {
        if (current.isSelected()) {
          bFind = true;
          break;
        }
      }
      if (bFind) {
        for (GearBoxType current : gearBoxTypeFilter) {
          if (current.isSelected()) {
            if (strGearBoxTypeFilter.length() != 0) {
              strGearBoxTypeFilter.append(", ");
            }

            strGearBoxTypeFilter.append(current.getId());
          }
        }
        strGearBoxTypeFilter.insert(0, " and (gbt.id IN (");
        strGearBoxTypeFilter.append("))");
      }
    }

    StringBuilder strOilStuffFilter = new StringBuilder();
    if (oilStuffFilter.size() > 0) {
      boolean bFind = false;
      for (OilStuff current : oilStuffFilter) {
        if (current.isSelected()) {
          bFind = true;
          break;
        }
      }
      if (bFind) {
        for (OilStuff current : oilStuffFilter) {
          if (current.isSelected()) {
            if (strOilStuffFilter.length() != 0) {
              strOilStuffFilter.append(", ");
            }

            strOilStuffFilter.append(current.getId());
          }
        }
        strOilStuffFilter.insert(0, " and (os.id IN (");
        strOilStuffFilter.append("))");
      }
    }

    StringBuilder strViscosityFilter = new StringBuilder();
    if (viscosityFilter.size() > 0) {
      boolean bFind = false;
      for (Map.Entry<String, Boolean> current : viscosityFilter.entrySet()) {
        if (current.getValue()) {
          bFind = true;
          break;
        }
      }
      if (bFind) {
        for (Map.Entry<String, Boolean> current : viscosityFilter.entrySet()) {
          if (current.getValue()) {
            if (strViscosityFilter.length() != 0) {
              strViscosityFilter.append(", ");
            }

            strViscosityFilter.append("'" + current.getKey() + "'");
          }
        }
        strViscosityFilter.insert(0, " and (go.viscosity IN (");
        strViscosityFilter.append("))");
      }
    }

    String sqlQuery =
        "select count(*) from"
            + "(select go.id as id from gearboxoils  as go"
            + " 	LEFT JOIN oilstuff AS os ON (go.oilstuff=os.id)"
            + " 	LEFT JOIN gearboxtype AS gbt ON (go.gearboxtype=gbt.id)"
            + "		left join manufacturer as man on go.manufacturer=man.id "
            + "				where (go.price>=:minPrice) and (go.price<=:maxPrice)"
            + "				 and  (go.Value>=:currentMinValueFilter) and (go.Value<=:currentMaxValueFilter)"
            + "				 and  (go.Judgement>=:currentMinJudgementFilter) and (go.Judgement<=:currentMaxJudgementFilter)"
            + " "
            + strGearBoxTypeFilter
            + strOilStuffFilter
            + strManufacturerFilter
            + strViscosityFilter
            + strSearchFilter(searchField)
            + " ORDER BY go.name ) as rez";

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("minPrice", minPrice);
    params.addValue("maxPrice", maxPrice);
    params.addValue("currentMinValueFilter", currentMinValueFilter);
    params.addValue("currentMaxValueFilter", currentMaxValueFilter);
    params.addValue("currentMinJudgementFilter", currentMinJudgementFilter);
    params.addValue("currentMaxJudgementFilter", currentMaxJudgementFilter);

    try {
      result = jdbcTemplate.queryForObject(sqlQuery, params, Integer.class);
      return result;
    } catch (EmptyResultDataAccessException e) {
      return 0;
    }
  }