public void incluir(PublicacaoBean objPublicacao, UsuarioBean objUsuarioBean)
throws SSDAOException {
PreparedStatement ps = null;
PreparedStatement ps2 = null;
Connection conn = null;
if (objPublicacao == null) {
throw new SSDAOException("O valor passado nao pode ser nulo");
}
try {
int ID = objPublicacao.getID();
String titulo = objPublicacao.getTitulo();
String[] autor = objPublicacao.getAutor();
String local = objPublicacao.getLocal();
String pagina = objPublicacao.getPaginas();
int ano = objPublicacao.getAno();
String SQL = "INSERT INTO PUBLICATION(ID,BOOKTITLE,TITLE,YEAR) " + "VALUES (?,?,?,?) ";
conn = this.conn;
ps = conn.prepareStatement(SQL);
ps.clearParameters();
ps.setInt(1, ID);
ps.setString(2, local);
ps.setString(3, titulo);
ps.setInt(4, ano);
ps.executeUpdate();
SQL = "INSERT INTO ID_AUTHOR (ID,AUTHOR) VALUES (?,?)";
String SQL2 = "INSERT INTO T_AUTHOR (AUTHOR) VALUES (?)";
ps = conn.prepareStatement(SQL);
ps2 = conn.prepareStatement(SQL2);
for (int i = 0; i < autor.length; i++) {
if (!verificarExistencia(TBAUTHOR, autor[i])) {
ps2.clearParameters();
ps2.setString(1, autor[i]);
ps2.executeUpdate();
}
ps.clearParameters();
ps.setInt(1, ID);
ps.setString(2, autor[i]);
ps.executeUpdate();
}
SQL = "INSERT INTO ID_PAGES (ID,PAGES) VALUES (?,?)";
SQL2 = "INSERT INTO T_PAGES (PAGES) VALUES (?)";
ps = conn.prepareStatement(SQL);
if (!verificarExistencia(TBPAGES, pagina)) {
ps2 = conn.prepareStatement(SQL2);
ps2.clearParameters();
ps2.setString(1, pagina);
ps2.executeUpdate();
}
ps.clearParameters();
ps.setInt(1, ID);
ps.setString(2, pagina);
ps.executeUpdate();
SQL = "INSERT INTO ID_PUBLICADOR(ID,ID_PUBLICADOR) VALUES(?,?)";
ps = conn.prepareStatement(SQL);
ps.setInt(1, ID);
ps.setInt(2, objUsuarioBean.getIdusuario());
ps.executeUpdate();
} catch (SQLException e) {
throw new SSDAOException("Erro ao inserir dados " + e);
} finally {
ConnectionSSFactory.closeConnection(conn, ps);
}
}
public void alterar(PublicacaoBean objPublicacao, UsuarioBean objUsuario) throws SSDAOException {
PreparedStatement ps = null;
PreparedStatement ps2 = null;
PreparedStatement ps3 = null;
PreparedStatement ps4 = null;
Connection conn = null;
if (objPublicacao == null) {
throw new SSDAOException("O valor passado nao pode ser nulo");
}
try {
int ID = objPublicacao.getID();
String titulo = objPublicacao.getTitulo();
String[] autor = objPublicacao.getAutor();
String local = objPublicacao.getLocal();
String pagina = objPublicacao.getPaginas();
int ano = objPublicacao.getAno();
String SQL = "UPDATE PUBLICATION SET BOOKTITLE = ? , TITLE = ?,YEAR = ? WHERE ID = ?";
conn = this.conn;
ps = conn.prepareStatement(SQL);
ps.clearParameters();
ps.setString(1, local);
ps.setString(2, titulo);
ps.setInt(3, ano);
ps.setInt(4, ID);
ps.executeUpdate();
String SQL2 = "";
String SQL3 = "";
String SQL4 = "";
SQL = "UPDATE ID_AUTHOR SET AUTHOR = ? WHERE ID = ? AND AUTHOR = ?";
SQL2 = "INSERT INTO T_AUTHOR (AUTHOR) VALUES (?)";
SQL3 = "DELETE FROM ID_AUTHOR WHERE ID = ? AND AUTHOR = ?";
SQL4 = "INSERT INTO ID_AUTHOR (ID,AUTHOR) VALUES (?,?)";
ps = conn.prepareStatement(SQL);
ps2 = conn.prepareStatement(SQL2);
ps3 = conn.prepareStatement(SQL3);
ps4 = conn.prepareStatement(SQL4);
String[] autoresAntigos = retornaAutores(ID);
for (int i = 0; i < autor.length; i++) {
if (!verificarExistencia(TBAUTHOR, autor[i])) {
ps2.clearParameters();
ps2.setString(1, autor[i]);
ps2.executeUpdate();
} else if (!verificarExistencia(TBIDAUTHOR, autor[i], ID)) {
ps4.clearParameters();
ps4.setInt(1, ID);
ps4.setString(2, autor[i]);
ps4.executeUpdate();
} else {
ps.clearParameters();
ps.setString(1, autor[i]);
ps.setInt(2, ID);
ps.setString(3, autoresAntigos[i]);
ps.executeUpdate();
autoresAntigos[i] = "";
}
}
// verifica se existem autores a excluir
for (int i = 0; i < autoresAntigos.length; i++) {
if (!autoresAntigos[i].equals("")) {
ps3.clearParameters();
ps3.setInt(1, ID);
ps3.setString(2, autoresAntigos[i]);
ps3.executeUpdate();
autoresAntigos[i] = "";
}
}
SQL = "UPDATE ID_PAGES SET PAGES = ? WHERE ID = ?";
SQL2 = "INSERT INTO T_PAGES (PAGES) VALUES (?)";
ps = conn.prepareStatement(SQL);
if (!verificarExistencia(TBPAGES, pagina)) {
ps2 = conn.prepareStatement(SQL2);
ps2.clearParameters();
ps2.setString(1, pagina);
ps2.executeUpdate();
}
ps.clearParameters();
ps.setString(1, pagina);
ps.setInt(2, ID);
ps.executeUpdate();
} catch (SQLException e) {
throw new SSDAOException("Erro ao inserir dados " + e);
} finally {
ConnectionSSFactory.closeConnection(conn, ps);
}
}