/*
* 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;
}
/*
* Add a computer to the database, and return the id auto incremented of the
* computer added
*/
public Long add(Computer computer) throws SQLException {
logger.debug("Enterring add in ComputerDAO.");
Connection connection = DataSourceUtils.getConnection(datasource);
Long id = null;
String query =
"INSERT INTO `computer-database-db`.`computer` (name,introduced,discontinued,company_id) VALUES (?,?,?,?);";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, computer.getName());
if (computer.getIntroduced() == null) {
statement.setDate(2, null);
} else statement.setDate(2, new java.sql.Date(computer.getIntroduced().getMillis()));
if (computer.getDiscontinued() == (null)) {
statement.setDate(3, null);
} else statement.setDate(3, new java.sql.Date(computer.getDiscontinued().getMillis()));
if (computer.getCompany().getId().equals((0L))) {
statement.setString(4, null);
} else statement.setLong(4, computer.getCompany().getId());
statement.executeUpdate();
ResultSet resultSet = null;
resultSet = statement.getGeneratedKeys();
if (resultSet != null) {
resultSet.next();
id = Long.parseLong(resultSet.getString(1));
}
if (statement != null) statement.close();
if (resultSet != null) resultSet.close();
logger.debug("Leaving add in ComputerDAO.");
return id;
}
/*
* Delete a computer from the database
*/
public void delete(Computer computer) throws SQLException {
logger.debug("Enterring delete in ComputerDAO.");
Connection connection = DataSourceUtils.getConnection(datasource);
String query = "DELETE FROM `computer-database-db`.`computer` WHERE id=?;";
PreparedStatement statement = connection.prepareStatement(query);
statement.setLong(1, computer.getId());
statement.executeUpdate();
if (statement != null) statement.close();
logger.debug("Leaving delete in ComputerDAO.");
}
/*
* Edit a computer from the database
*/
public void edit(Computer computer) throws SQLException {
logger.debug("Enterring edit in ComputerDAO.");
Connection connection = DataSourceUtils.getConnection(datasource);
String query =
"UPDATE computer SET name =?,introduced=?,discontinued=?,company_id=? WHERE id=?;";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, computer.getName());
if (computer.getIntroduced() == null) {
statement.setDate(2, null);
} else statement.setDate(2, new java.sql.Date(computer.getIntroduced().getMillis()));
if (computer.getDiscontinued() == (null)) {
statement.setDate(3, null);
} else statement.setDate(3, new java.sql.Date(computer.getDiscontinued().getMillis()));
if (computer.getCompany().getId().equals((0L))) {
statement.setString(4, null);
} else statement.setLong(4, computer.getCompany().getId());
statement.setLong(5, computer.getId());
statement.executeUpdate();
if (statement != null) statement.close();
logger.debug("Leaving edit in ComputerDAO.");
}
/*
* 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;
}
