package labsis.usuario.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import labsis.DataBase;
import labsis.usuario.model.Operacao;
import labsis.usuario.model.TipoUsuario;
import labsis.usuario.model.Usuario;
public class DaoUsuario implements Dao {
private Connection conn;
public DaoUsuario() throws ClassNotFoundException, SQLException {
DataBase db = DataBase.getDb();
Class.forName(db.getDriver());
conn = DriverManager.getConnection(db.getUrl(), db.getUser(), db
.getPassword());
}
@Override
public boolean create(Object o) throws SQLException {
Usuario u = (Usuario) o;
PreparedStatement st = conn
.prepareStatement("select username from usuario where username = ?");
st.setString(1, u.getUsername());
ResultSet r = st.executeQuery();
if(r.next()){
return false;
}
st = conn
.prepareStatement("insert into usuario (username, nome, senha,tipousuarioId) values(?,?,?,?)");
st.setString(1, u.getUsername());
st.setString(2, u.getNome());
st.setString(3, u.getSenha());
st.setInt(4, u.getTipoUsuario().getId());
if (st.executeUpdate() > 0)
return true;
return false;
}
@Override
public boolean delete(Object o) throws SQLException {
Usuario u = (Usuario) o;
PreparedStatement st = conn
.prepareStatement("delete from logusuario where username = ?");
st.setString(1, u.getUsername());
st.executeUpdate();
st = conn.prepareStatement("delete from usuario where username = ?");
st.setString(1, u.getUsername());
if (st.executeUpdate() <= 0)
return false;
return true;
}
@Override
public List<Object> retrieve() throws SQLException {
List<Object> l = new ArrayList<Object>();
PreparedStatement st = conn
.prepareStatement("select u.username, u.senha, u.nome, t.id ,t.descricao from usuario u join tipousuario t on u.tipousuarioId = t.id order by u.username");
ResultSet r = st.executeQuery();
while (r.next()) {
st = conn
.prepareStatement("select o.id, o.descricao, o.comando from tipousuariooperacoes uo join operacao o on uo.operacaoId = o.id where uo.tipousuarioId = ?");
st.setString(1, r.getString(4));
ResultSet op = st.executeQuery();
HashSet<Operacao> operacoes = new HashSet<Operacao>();
while (op.next()) {
operacoes.add(new Operacao(op.getInt(1), op.getString(2),r.getString(3)));
}
l.add(new Usuario(r.getString(1), r.getString(2), r.getString(3),
new TipoUsuario(r.getInt(4), r.getString(5), operacoes)));
}
return l;
}
@Override
public boolean update(Object o) throws SQLException {
Usuario u = (Usuario) o;
PreparedStatement st = conn
.prepareStatement("update usuario set nome = ?, senha = ?, tipousuarioId = ? where username = ?");
st.setString(1, u.getNome());
st.setString(2, u.getSenha());
st.setInt(3, u.getTipoUsuario().getId());
st.setString(4, u.getUsername());
if (st.executeUpdate() > 0)
return true;
return false;
}
@Override
public void close() throws SQLException {
if (conn != null)
conn.close();
}
@Override
public Object find(Object primaryKey) throws SQLException {
Usuario u = null;
PreparedStatement st = conn
.prepareStatement("select u.username, u.senha, u.nome, t.id ,t.descricao from usuario u join tipousuario t on u.tipousuarioId = t.id where username = ? order by u.username");
st.setString(1, primaryKey.toString());
ResultSet r = st.executeQuery();
if (r.next()) {
st = conn
.prepareStatement("select o.id, o.descricao, o.comando from tipousuariooperacoes uo join operacao o on uo.operacaoId = o.id where uo.tipousuarioId = ?");
st.setInt(1, r.getInt(4));
ResultSet op = st.executeQuery();
HashSet<Operacao> operacoes = new HashSet<Operacao>();
while (op.next()) {
operacoes.add(new Operacao(op.getInt(1), op.getString(2),op.getString(3)));
}
u = new Usuario(r.getString(1), r.getString(2), r.getString(3),
new TipoUsuario(r.getInt(4), r.getString(5), operacoes));
}
return u;
}
@Override
public List<Object> retrieve(int pageNum, int rowsPerPage)
throws SQLException {
List<Object> l = new ArrayList<Object>();
StringBuffer sb = new StringBuffer();
sb.append("select * from ( ");
sb.append("select top(? * ?) ROW_NUMBER() over (order by u.username) as resultNum, ");
sb.append("u.username, u.senha, u.nome, t.id ,t.descricao ");
sb.append("FROM usuario u ");
sb.append("JOIN tipousuario t on u.tipousuarioId = t.id ");
sb.append("order by u.username ) as p ");
sb.append("where p.resultNum > ((? - 1) * ?) ");
PreparedStatement st = conn.prepareStatement(sb.toString());
st.setInt(1, pageNum);
st.setInt(2, rowsPerPage);
st.setInt(3, pageNum);
st.setInt(4, rowsPerPage);
ResultSet r = st.executeQuery();
while (r.next()) {
st = conn
.prepareStatement("select o.id, o.descricao, o.comando from tipousuariooperacoes uo join operacao o on uo.operacaoId = o.id where uo.tipousuarioId = ?");
st.setString(1, r.getString(5));
ResultSet op = st.executeQuery();
HashSet<Operacao> operacoes = new HashSet<Operacao>();
while (op.next()) {
operacoes.add(new Operacao(op.getInt(1), op.getString(2),r.getString(3)));
}
l.add(new Usuario(r.getString(2), r.getString(3), r.getString(4),
new TipoUsuario(r.getInt(5), r.getString(6), operacoes)));
}
st = conn
.prepareStatement("select ((count(*) - 1) / ? ) + 1 from usuario");
st.setInt(1, rowsPerPage);
ResultSet t = st.executeQuery();
if (t.next())
l.add(new Integer(t.getInt(1)));
return l;
}
}