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.List;
import labsis.DataBase;
import labsis.usuario.model.LogUsuario;
import labsis.usuario.model.Operacao;
import labsis.usuario.model.Usuario;
public class DaoLogUsuario implements Dao {
private Connection conn;
public DaoLogUsuario() throws SQLException, ClassNotFoundException {
DataBase db = DataBase.getDb();
conn = db.getConnection();
}
@Override
public boolean create(Object o) throws SQLException {
LogUsuario lu = (LogUsuario) o;
PreparedStatement st = conn
.prepareStatement("insert into logusuario(username, operacaoId) values(?,?)");
st.setString(1, lu.getUsuario().getUsername());
st.setInt(2, lu.getOperacao().getId());
if (st.executeUpdate() > 0)
return true;
return false;
}
@Override
public boolean delete(Object o) {
// TODO Auto-generated method stub
return false;
}
@Override
public List<Object> retrieve() throws SQLException {
List<Object> l = new ArrayList<Object>();
PreparedStatement st = conn
.prepareStatement("select l.id, o.descricao, u.nome, l.data, o.comando from logusuario l join operacao o on l.operacaoId=o.id join usuario u on l.username = u.username");
ResultSet r = st.executeQuery();
while (r.next()) {
l.add(new LogUsuario(r.getInt(1), new Operacao(0, r.getString(2),r.getString(5)),
new Usuario("", "", r.getString(3), null), r.getTimestamp(4)));
}
return l;
}
@Override
public boolean update(Object o) {
// TODO Auto-generated method stub
return false;
}
@Override
public void close() throws SQLException {
if (conn != null)
conn.close();
}
@Override
public Object find(Object primaryKey) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@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 l.id) as resultNum, ");
sb.append("l.id, o.descricao, u.nome, l.data, o.comando ");
sb.append("FROM logusuario l ");
sb.append("JOIN operacao o ON l.operacaoId = o.id ");
sb.append("JOIN usuario u ON l.username = u.username ");
sb.append(") 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()) {
l.add(new LogUsuario(r.getInt(2), new Operacao(0, r.getString(3),r.getString(6)),
new Usuario("", "", r.getString(4), null), r.getTimestamp(5)));
}
st = conn.prepareStatement("select ((count(*) - 1) / ? ) + 1 from logusuario");
st.setInt(1, rowsPerPage);
ResultSet t = st.executeQuery();
if(t.next())l.add(new Integer(t.getInt(1)));
return l;
}
}