예제 #1
0
  /*
   * Return a computer using its id
   */
  public Computer get(Long computerId) throws SQLException, ParseException {

    logger.debug("Enterring get(Long computerId) in ComputerDAO.");
    Connection connection = DataSourceUtils.getConnection(datasource);
    String query =
        "SELECT * FROM `computer-database-db`.`computer` AS computer LEFT OUTER JOIN `computer-database-db`.`company` AS company ON computer.company_id=company.id WHERE computer.id=?;";
    PreparedStatement statement = connection.prepareStatement(query);
    statement.setLong(1, computerId);
    ResultSet resultSet = statement.executeQuery();
    Computer computer = null;
    while (resultSet.next()) {

      Company company =
          Company.builder().id(resultSet.getLong(5)).name(resultSet.getString(7)).build();
      computer =
          Computer.builder()
              .id(resultSet.getLong(1))
              .name(resultSet.getString(2))
              .company(company)
              .build();
      if (resultSet.getDate(3) != null) {
        computer.setIntroduced(new DateTime(resultSet.getDate(3)));
      }
      if (resultSet.getDate(4) != null) {
        computer.setDiscontinued(new DateTime(resultSet.getDate(4)));
      }
    }
    if (resultSet != null) resultSet.close();
    if (statement != null) statement.close();
    logger.debug("Leaving get(Long computerId) in ComputerDAO.");
    return computer;
  }
예제 #2
0
  /*
   * Return the list of computers with a specific company, ordered and limited
   */
  public List<Computer> getListByCompanyName(
      String computerCompanyName, String orderBy, Integer page, Integer recordsPerPage)
      throws SQLException {

    logger.debug("Entering getListByCompanyName in ComputerDAO.");
    Connection connection = DataSourceUtils.getConnection(datasource);
    StringBuilder query =
        new StringBuilder(
            "SELECT * FROM `computer-database-db`.`computer` AS computer LEFT OUTER JOIN `computer-database-db`.`company` AS company ON computer.company_id=company.id WHERE company.name=?");
    if (orderBy == null) {
      orderBy = "computer.id ASC";
    } else
      switch (orderBy) {
        case "nameASC":
          orderBy = "computer.name ASC";
          break;
        case "nameDESC":
          orderBy = "computer.name DESC";
          break;
        case "introducedASC":
          orderBy = "computer.introduced ASC";
          break;
        case "introducedDESC":
          orderBy = "computer.introduced DESC";
          break;
        case "discontinuedASC":
          orderBy = "computer.discontinued ASC";
          break;
        case "discontinuedDESC":
          orderBy = "computer.discontinued DESC";
          break;
        case "companyASC":
          orderBy = "computer.company_id ASC";
          break;
        case "companyDESC":
          orderBy = "company.name DESC";
          break;
      }
    query.append(" ORDER BY ").append(orderBy).append(" LIMIT ?,?;");
    List<Computer> listComputers = new ArrayList<Computer>();
    PreparedStatement statement = connection.prepareStatement(query.toString());
    statement.setString(1, computerCompanyName);
    statement.setInt(2, (page - 1) * recordsPerPage);
    statement.setInt(3, recordsPerPage);
    ResultSet resultSet = statement.executeQuery();
    while (resultSet.next()) {
      Company company =
          Company.builder().id(resultSet.getLong(5)).name(resultSet.getString(7)).build();
      Computer computer =
          Computer.builder()
              .id(resultSet.getLong(1))
              .name(resultSet.getString(2))
              .company(company)
              .build();
      if (resultSet.getDate(3) != null) {
        computer.setIntroduced(new DateTime(resultSet.getDate(3)));
      }
      if (resultSet.getDate(4) != null) {
        computer.setDiscontinued(new DateTime(resultSet.getDate(4)));
      }
      listComputers.add(computer);
    }
    if (resultSet != null) resultSet.close();
    if (statement != null) statement.close();
    logger.debug("Leaving getListByCompanyName in ComputerDAO.");
    return listComputers;
  }