package cadastro.bean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import cadastro.bean.UserData;
public class AdminUsers {
protected static DataSource dataSource;
public AdminUsers() throws Exception {
if (dataSource == null) {
try {
InitialContext ic = new InitialContext();
// se for tomcat
dataSource = (DataSource) ic.lookup("java:comp/env/jdbc/cadastroDS");
// no JBoss faça
// dataSource = (DataSource) ic.lookup("java:jdbc/cadastroDS");
} catch (NamingException ex) {
System.out.println(ex.getMessage());
throw ex;
}
}
}
protected Connection getConnection() throws SQLException {
Connection conn = null;
try {
conn = dataSource.getConnection();
}
catch (SQLException e) {
throw e;
}
return conn;
}
protected void closeConnection(
Connection conn,
PreparedStatement stmt,
ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
public LinkedList getUserList() throws SQLException {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
LinkedList users = new LinkedList();
try {
conn = getConnection();
stmt = conn.prepareStatement("select * from dbcadastro");
rs = stmt.executeQuery();
while (rs.next()) {
UserData user = new UserData();
// user.setId(rs.getInt("id"));
user.setNome(rs.getString("nome"));
user.setSobrenome(rs.getString("sobrenome"));
user.setOpcaoemail(rs.getString("mostraremail"));
user.setEmail(rs.getString("email"));
user.setTelefone(rs.getString("telefone"));
user.setEndereco(rs.getString("endereco"));
user.setBairro(rs.getString("bairro"));
user.setCidade(rs.getString("cidade"));
user.setEstado(rs.getString("estado"));
user.setLogin(rs.getString("login"));
user.setSenha(rs.getString("senha"));
user.setInformativos(rs.getString("informativos"));
users.add(user);
}
}
catch (SQLException e) {
throw e;
}
finally {
closeConnection(conn, stmt, rs);
}
return users;
}
public void insertUser(UserData user) throws SQLException {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
stmt = conn.prepareStatement(
"insert into dbcadastro \n" +
"(nome, sobrenome, mostraremail, email, telefone, endereco, bairro, cidade, estado, login, senha, informativos) \n" +
"values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
stmt.setString(1, user.getNome());
stmt.setString(2, user.getSobrenome());
stmt.setString(3, user.getOpcaoemail());
stmt.setString(4, user.getEmail());
stmt.setString(5, user.getTelefone());
stmt.setString(6, user.getEndereco());
stmt.setString(7, user.getBairro());
stmt.setString(8, user.getCidade());
stmt.setString(9, user.getEstado());
stmt.setString(10, user.getLogin());
stmt.setString(11, user.getSenha());
stmt.setString(12, user.getInformativos());
stmt.executeUpdate();
}
catch (SQLException e) {
throw e;
}
finally {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
}
}
public void updateUser(UserData user) throws SQLException {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
stmt = conn.prepareStatement(
"update dbcadastro set \n" +
"nome = ?, sobrenome = ?, mostraremail = ?, email = ?, telefone = ?, endereco = ?, bairro = ?, cidade = ?, estado = ?, informativos = ?\n" +
"where id = ?");
stmt.setString(1, user.getNome());
stmt.setString(2, user.getSobrenome());
stmt.setString(3, user.getOpcaoemail());
stmt.setString(4, user.getEmail());
stmt.setString(5, user.getTelefone());
stmt.setString(6, user.getEndereco());
stmt.setString(7, user.getBairro());
stmt.setString(8, user.getCidade());
stmt.setString(9, user.getEstado());
stmt.setString(10, user.getInformativos());
stmt.setInt(11, user.getId());
stmt.executeUpdate();
}
catch (SQLException e) {
throw e;
}
finally {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
}
}
public void deleteUser(UserData user) throws SQLException {
// public void deleteUser(int id) throws SQLException {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
stmt = conn.prepareStatement(
“delete from dbcadastro where id = ?”);
stmt.setInt(1, user.getId());
stmt.executeUpdate();
}
catch (SQLException e) {
throw e;
}
finally {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
}
}
}