/**
* Met à jour un Computer de la base
*
* @param comp le Computer à mettre à jour
*/
public void updateComputer(Computer comp) {
Logger log = LoggerFactory.getLogger(this.getClass());
Connection connection = null;
try {
connection = connectionFactory.getConnection();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
log.error("Erreur lors de la demande de connection.");
}
// ajoutez ici le code d'update d'un Computer
String query =
"UPDATE computer SET name = ?, introduced = ?, discontinued = ?, company_id = ? WHERE id = ?;";
int results = 0;
PreparedStatement pstmt = null;
try {
pstmt = connection.prepareStatement(query);
pstmt.setString(1, comp.getName());
pstmt.setTimestamp(2, new Timestamp(comp.getIntroducedDate().getMillis()));
pstmt.setTimestamp(3, new Timestamp(comp.getDiscontinuedDate().getMillis()));
if (comp.getCompany().getId() != null) pstmt.setLong(4, comp.getCompany().getId());
else pstmt.setNull(4, Types.NULL);
pstmt.setLong(5, comp.getId());
System.out.println("La requete: " + pstmt.toString());
results = pstmt.executeUpdate();
System.out.println("Mis à jour bien effectué...");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("Probleme dans la requete de mis à jour...");
} finally {
try {
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* Recherche le Computer dans la base de donnée
*
* @param paramId l'id du Computer rechercher
* @return l'instance de la Computer
*/
public Computer findComputerById(Long paramId) {
Logger log = LoggerFactory.getLogger(this.getClass());
Connection connection = null;
try {
connection = connectionFactory.getConnection();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
log.error("Erreur lors de la demande de connection.");
}
Computer computer = new Computer();
// Company company = new Company();
Company company = Company.builder().build();
// requete de recuperation des companies répertorié dans la base
String query =
"SELECT pc.id, pc.name, pc.introduced, pc.discontinued, comp.id, comp.name FROM computer AS pc LEFT JOIN company AS comp ON pc.company_id=comp.id WHERE pc.id=?;";
ResultSet results = null;
PreparedStatement pstmt = null;
if (connection != null) {
try {
pstmt = connection.prepareStatement(query);
pstmt.setLong(1, paramId);
results = pstmt.executeQuery();
while (results.next()) {
// Recuperation des donnéees de la ligne
Long computerId = results.getLong("pc.id");
String computerName = results.getString("pc.name");
DateTime computerIntroD = new DateTime(results.getDate("pc.introduced"));
DateTime computerDiscD = new DateTime(results.getDate("pc.discontinued"));
String companyName = results.getString("comp.name");
Long companyId = results.getLong("comp.id");
computer.setId(computerId);
computer.setName(computerName);
computer.setIntroducedDate(computerIntroD);
computer.setDiscontinuedDate(computerDiscD);
company.setId(companyId);
company.setName(companyName);
computer.setCompany(company);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("Problème dans la requete de recherche de company...");
} finally {
try {
if (results != null) results.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} else {
System.out.println("La connection est null...");
}
return computer;
}
/** Insert un ordinateur/computer dans la base */
public Long insertComputer(Computer cp) {
Logger log = LoggerFactory.getLogger(this.getClass());
Connection connection = null;
try {
connection = connectionFactory.getConnection();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
log.error("Erreur lors de la demande de connection.");
}
Long id = null;
// ajoutez ici le code d'insertion d'un produit
String query = "INSERT INTO computer(name,introduced,discontinued,company_id) VALUES(?,?,?,?);";
int results = 0;
PreparedStatement pstmt = null;
try {
pstmt = connection.prepareStatement(query);
pstmt.setString(1, cp.getName());
// pstmt.setTimestamp(2, new Timestamp(cp.getIntroducedDate().getMillis()));
pstmt.setDate(2, new java.sql.Date(cp.getIntroducedDate().getMillis()));
// pstmt.setTimestamp(3, new Timestamp(cp.getDiscontinuedDate().getMillis()));
pstmt.setDate(3, new java.sql.Date(cp.getDiscontinuedDate().getMillis()));
if (cp.getCompany().getId() != null) pstmt.setLong(4, cp.getCompany().getId());
else pstmt.setNull(4, Types.NULL);
System.out.println("La requete: " + pstmt.toString());
results = pstmt.executeUpdate();
System.out.println("Insertion bien effectué...");
try {
// On recupère l'id généré
ResultSet rsId = pstmt.getGeneratedKeys();
while (rsId.next()) {
id = rsId.getLong(1);
}
// fermeture de rsId
ConnectionFactory.closeObject(rsId);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
System.out.println("Probleme dans la génération des id Computer...");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("Probleme dans la requete d'insertion...");
} finally {
try {
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return id;
}
