/*
* 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;
}
/*
* 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;
}