public void ultimoID(PublicacaoBean objPublicacao) throws SSDAOException {
PreparedStatement ps = null;
Connection conn = null;
if (objPublicacao == null) {
throw new SSDAOException("O valor passado nao pode ser nulo");
}
try {
String SQL = "SELECT max(ID) as ultimo FROM PUBLICATION";
conn = this.conn;
ps = conn.prepareStatement(SQL);
ResultSet rs = ps.executeQuery();
int ultimo = 0;
while (rs.next()) {
ultimo = rs.getInt("ultimo");
}
ultimo++;
objPublicacao.setID(ultimo);
} catch (SQLException e) {
throw new SSDAOException("Erro ao inserir dados " + e);
} finally {
ConnectionSSFactory.closeConnection(conn, ps);
}
}
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;
}