public boolean exsistNetwork(Network net) throws DataAccessException {
PreparedStatement ps = null;
Connection connInventory = null;
ResultSet rs = null;
boolean esiste = false;
try {
connInventory = DBConnectionManager.inventoryConnectionFactory();
if (net.getId() > 0) {
String sql = "select id from network where (mac_address = ? or ip_address = ?) and id<>?";
ps = connInventory.prepareStatement(sql);
ps.setString(1, net.getMac_address());
ps.setString(2, net.getIp_address());
ps.setInt(3, net.getId());
} else {
String sql = "select id from network where (mac_address = ? or ip_address = ?)";
ps = connInventory.prepareStatement(sql);
ps.setString(1, net.getMac_address());
ps.setString(2, net.getIp_address());
}
rs = ps.executeQuery();
if (rs.next()) esiste = true;
DBConnectionManager.CloseConnection(connInventory);
} catch (SQLException e) {
log.error("exsistNetwork: SQL failed", e);
e.printStackTrace();
throw new DataAccessException(e.toString(), e);
} catch (RuntimeException e) {
throw new DataAccessException(e.toString());
} catch (DataLayerException e) {
e.printStackTrace();
} finally {
try {
rs.close();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return esiste;
}
public int modifyNetwork(Network n) throws DataAccessException {
PreparedStatement ps = null;
Connection connInventory = null;
int risultato = 0;
if (!exsistNetwork(n)) {
try {
connInventory = DBConnectionManager.inventoryConnectionFactory();
connInventory.setAutoCommit(false);
String sql =
"update network set description=?,mac_address=?,ip_address=?,id_item_pc=? where id=?";
ps = connInventory.prepareStatement(sql);
ps.setString(1, n.getDescription());
ps.setString(2, n.getMac_address());
ps.setString(3, n.getIp_address());
ps.setInt(4, n.getId());
risultato = ps.executeUpdate();
if (risultato > 0) connInventory.commit();
else connInventory.rollback();
DBConnectionManager.CloseConnection(connInventory);
} catch (SQLException e) {
log.error("modifyNetwork: SQL failed", e);
e.printStackTrace();
throw new DataAccessException(e.toString(), e);
} catch (RuntimeException e) {
throw new DataAccessException(e.toString());
} catch (DataLayerException e) {
e.printStackTrace();
} finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} else {
risultato = -1;
}
return risultato;
}
public int deleteNetwork(Network n) throws DataAccessException {
PreparedStatement ps = null;
Connection connInventory = null;
int risultato = 0;
try {
connInventory = DBConnectionManager.inventoryConnectionFactory();
connInventory.setAutoCommit(false);
String sql = "delete from network where id=?";
ps = connInventory.prepareStatement(sql);
ps.setInt(1, n.getId());
risultato = ps.executeUpdate();
if (risultato > 0) connInventory.commit();
else connInventory.rollback();
DBConnectionManager.CloseConnection(connInventory);
} catch (SQLException e) {
log.error("deleteNetwork: SQL failed", e);
e.printStackTrace();
throw new DataAccessException(e.toString(), e);
} catch (RuntimeException e) {
throw new DataAccessException(e.toString());
} catch (DataLayerException e) {
e.printStackTrace();
} finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return risultato;
}