public PublicacaoBean recuperaPublicacoes(int id) throws SSDAOException {
PublicacaoBean objPublicacao = new PublicacaoBean();
PreparedStatement ps = null;
Connection conn = null;
String SQL = "";
try {
SQL = "SELECT * FROM PUBLICATION " + "WHERE ID=?";
conn = this.conn;
ps = conn.prepareStatement(SQL);
ps.clearParameters();
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
objPublicacao.setID(rs.getInt("ID"));
objPublicacao.setTitulo(rs.getString("TITLE"));
objPublicacao.setLocal(rs.getString("BOOKTITLE"));
objPublicacao.setAno(rs.getInt("YEAR"));
objPublicacao.setAutor(retornaAutores(objPublicacao.getID()));
objPublicacao.setPaginas(retornaPaginas(objPublicacao.getID()));
}
} catch (SQLException e) {
throw new SSDAOException("Erro ao consultar dados " + e);
} finally {
ConnectionSSFactory.closeConnection(conn, ps);
}
return objPublicacao;
}
public ArrayList<PublicacaoBean> retornaPublicacoes(UsuarioBean objUsuario)
throws SSDAOException {
ArrayList<PublicacaoBean> listaPublicacao = new ArrayList<PublicacaoBean>();
PreparedStatement ps = null;
Connection conn = null;
String SQL = "";
int cont = 0;
try {
SQL =
"SELECT * FROM ID_PUBLICADOR "
+ "INNER JOIN PUBLICATION ON ID_PUBLICADOR.ID=PUBLICATION.ID "
+ "WHERE ID_PUBLICADOR.ID_PUBLICADOR=?";
conn = this.conn;
ps = conn.prepareStatement(SQL);
ps.clearParameters();
ps.setInt(1, objUsuario.getIdusuario());
ResultSet rs = ps.executeQuery();
while (rs.next()) {
PublicacaoBean pb = new PublicacaoBean();
pb.setID(rs.getInt("ID"));
pb.setTitulo(rs.getString("TITLE"));
pb.setLocal(rs.getString("BOOKTITLE"));
pb.setAno(rs.getInt("YEAR"));
pb.setAutor(retornaAutores(pb.getID()));
pb.setPaginas(retornaPaginas(pb.getID()));
listaPublicacao.add(cont, pb);
cont++;
}
} catch (SQLException e) {
throw new SSDAOException("Erro ao consultar dados " + e);
} finally {
ConnectionSSFactory.closeConnection(conn, ps);
}
return listaPublicacao;
}
public ArrayList<PublicacaoBean> retornaPublicacoesPesquisa(String titulo, String local)
throws SSDAOException {
ArrayList<PublicacaoBean> listaPublicacao = new ArrayList<PublicacaoBean>();
PreparedStatement ps = null;
Connection conn = null;
String SQL = "";
int cont = 0;
try {
SQL =
"SELECT TOP 100 * FROM PUBLICATION WHERE TITLE LIKE ? AND BOOKTITLE LIKE ? ORDER BY ID DESC ";
String t = "%" + titulo + "%";
String l = "%" + local + "%";
conn = this.conn;
ps = conn.prepareStatement(SQL);
ps.clearParameters();
ps.setString(1, t);
ps.setString(2, l);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
PublicacaoBean pb = new PublicacaoBean();
pb.setID(rs.getInt("ID"));
pb.setTitulo(rs.getString("TITLE"));
pb.setLocal(rs.getString("BOOKTITLE"));
pb.setAno(rs.getInt("YEAR"));
pb.setAutor(retornaAutores(pb.getID()));
pb.setPaginas(retornaPaginas(pb.getID()));
listaPublicacao.add(cont, pb);
cont++;
}
} catch (SQLException e) {
throw new SSDAOException("Erro ao consultar dados " + e);
} finally {
ConnectionSSFactory.closeConnection(conn, ps);
}
return listaPublicacao;
}
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);
}
}