Last active
June 12, 2017 20:56
-
-
Save wagnerluis1982/c73c2b3120968901b96c882ad0e4e316 to your computer and use it in GitHub Desktop.
Arquivos de apoio da aula de "Criando Serviços REST usando Java EE"
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package fanese.web.model; | |
import java.sql.Connection; | |
import java.sql.DriverManager; | |
import java.sql.PreparedStatement; | |
import java.sql.SQLException; | |
public class Database { | |
private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; | |
private static final String JDBC_URL = "jdbc:sqlserver://localhost;user=sa;password=123456;databaseName=UN3"; | |
public static Connection getConnection() throws SQLException { | |
try { | |
Class.forName(DRIVER); | |
} catch (ClassNotFoundException e) { | |
throw new IllegalStateException(e); | |
} | |
return DriverManager.getConnection(JDBC_URL); | |
} | |
@FunctionalInterface | |
public interface PSCommand { | |
void accept(PreparedStatement stmt) throws SQLException; | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package fanese.web.model.entity; | |
public class Usuario { | |
private Integer id; | |
private String nome; | |
private String senha; | |
private Integer idade; | |
public Usuario() { | |
} | |
public Usuario(int id) { | |
this.id = id; | |
} | |
/* Getters and Setters */ | |
public Integer getId() { | |
return id; | |
} | |
public void setId(Integer id) { | |
this.id = id; | |
} | |
public String getNome() { | |
return nome; | |
} | |
public void setNome(String nome) { | |
this.nome = nome; | |
} | |
public String getSenha() { | |
return senha; | |
} | |
public void setSenha(String senha) { | |
this.senha = senha; | |
} | |
public Integer getIdade() { | |
return idade; | |
} | |
public void setIdade(Integer idade) { | |
this.idade = idade; | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package fanese.web.model.dao; | |
import fanese.web.model.Database; | |
import fanese.web.model.Database.PSCommand; | |
import fanese.web.model.entity.Usuario; | |
import java.sql.*; | |
import java.util.ArrayList; | |
import java.util.List; | |
public class UsuarioDao { | |
public void create(Usuario usuario) throws SQLException { | |
try (Connection conn = Database.getConnection()) { | |
String sql = "INSERT INTO Usuarios (nome, senha, idade) VALUES (?, ?, ?)"; | |
PreparedStatement stmt = conn.prepareStatement(sql); | |
stmt.setString(1, usuario.getNome()); | |
stmt.setString(2, usuario.getSenha()); | |
stmt.setInt(3, usuario.getIdade()); | |
stmt.executeUpdate(); | |
} | |
} | |
public Usuario read(int id) throws SQLException { | |
try (Connection conn = Database.getConnection()) { | |
String sql = "SELECT * FROM Usuarios WHERE (id = ?)"; | |
PreparedStatement stmt = conn.prepareStatement(sql); | |
stmt.setInt(1, id); | |
ResultSet result = stmt.executeQuery(); | |
if (result.next()) { | |
Usuario usuario = new Usuario(id); | |
usuario.setNome(result.getString("nome")); | |
usuario.setSenha(result.getString("senha")); | |
usuario.setIdade(result.getInt("idade")); | |
return usuario; | |
} | |
return null; | |
} | |
} | |
public List<Usuario> readAll() throws SQLException { | |
try (Connection conn = Database.getConnection()) { | |
return listAll(conn); | |
} | |
} | |
public List<Usuario> readAll(Usuario filtro) throws SQLException { | |
try (Connection conn = Database.getConnection()) { | |
List<Usuario> resultadosBusca; | |
List<PSCommand> comandos = new ArrayList<>(); | |
StringBuilder sql = new StringBuilder("SELECT * FROM Usuarios WHERE 1=1"); | |
if (filtro.getId() != null) { | |
sql.append(" AND id = ?"); | |
int index = comandos.size() + 1; | |
comandos.add(stmt -> stmt.setInt(index, filtro.getId())); | |
} | |
if (filtro.getNome() != null) { | |
sql.append(" AND nome LIKE ?"); | |
int index = comandos.size() + 1; | |
comandos.add(stmt -> stmt.setString(index, filtro.getNome() + '%')); | |
} | |
if (filtro.getSenha() != null) { | |
sql.append(" AND senha LIKE ?"); | |
int index = comandos.size() + 1; | |
comandos.add(stmt -> stmt.setString(index, filtro.getSenha() + '%')); | |
} | |
if (filtro.getIdade() != null) { | |
sql.append(" AND idade = ?"); | |
int index = comandos.size() + 1; | |
comandos.add(stmt -> stmt.setInt(index, filtro.getIdade())); | |
} | |
// Se não houver filtro, retorna todos | |
if (comandos.size() == 0) { | |
return listAll(conn); | |
} else { | |
PreparedStatement stmt = conn.prepareStatement(sql.toString()); | |
for (PSCommand comando : comandos) { | |
comando.accept(stmt); | |
} | |
stmt.execute(); | |
return listQuery(stmt); | |
} | |
} | |
} | |
public void update(Usuario usuario) throws SQLException { | |
try (Connection conn = Database.getConnection()) { | |
List<PSCommand> comandos = new ArrayList<>(); | |
String sql = "UPDATE Usuarios SET %s WHERE (id = ?)"; | |
List<String> setStatements = new ArrayList<>(); | |
if (usuario.getNome() != null) { | |
setStatements.add("nome = ?"); | |
int index = comandos.size() + 1; | |
comandos.add(stmt -> stmt.setString(index, usuario.getNome())); | |
} | |
if (usuario.getSenha() != null) { | |
setStatements.add("senha = ?"); | |
int index = comandos.size() + 1; | |
comandos.add(stmt -> stmt.setString(index, usuario.getSenha())); | |
} | |
if (usuario.getIdade() != null) { | |
setStatements.add("idade = ?"); | |
int index = comandos.size() + 1; | |
comandos.add(stmt -> stmt.setInt(index, usuario.getIdade())); | |
} | |
if (comandos.size() == 0) { | |
throw new IllegalArgumentException("Não foi passado valores de atualização"); | |
} | |
sql = String.format(sql, String.join(", ", setStatements)); | |
PreparedStatement stmt = conn.prepareStatement(sql); | |
for (PSCommand comando : comandos) { | |
comando.accept(stmt); | |
} | |
int index = comandos.size() + 1; | |
stmt.setInt(index, usuario.getId()); | |
int rowCount = stmt.executeUpdate(); | |
if (rowCount == 0) { | |
throw new IllegalArgumentException("Usuário não encontrado"); | |
} | |
} | |
} | |
public void delete(int id) throws SQLException { | |
try (Connection conn = Database.getConnection()) { | |
String sql = "DELETE FROM Usuarios WHERE (id = ?) "; | |
PreparedStatement stmt = conn.prepareStatement(sql); | |
stmt.setInt(1, id); | |
int rowCount = stmt.executeUpdate(); | |
if (rowCount == 0) { | |
throw new IllegalArgumentException("Usuário não encontrado"); | |
} | |
} | |
} | |
public int countAll() throws SQLException { | |
try (Connection conn = Database.getConnection()) { | |
String sql = "SELECT count(*) FROM Usuarios"; | |
PreparedStatement stmt = conn.prepareStatement(sql); | |
ResultSet result = stmt.executeQuery(); | |
int count = 0; | |
if (result.next()) { | |
count = result.getInt(1); | |
} | |
return count; | |
} | |
} | |
private List<Usuario> listQuery(Statement stmt) throws SQLException { | |
List<Usuario> resultados = new ArrayList<>(); | |
ResultSet result = stmt.getResultSet(); | |
while (result.next()) { | |
Usuario u = new Usuario(); | |
u.setId(result.getInt("id")); | |
u.setNome(result.getString("nome")); | |
u.setSenha(result.getString("senha")); | |
u.setIdade(result.getInt("idade")); | |
resultados.add(u); | |
} | |
return resultados; | |
} | |
private List<Usuario> listAll(Connection conn) throws SQLException { | |
String sql = "SELECT * FROM Usuarios"; | |
Statement stmt = conn.createStatement(); | |
stmt.execute(sql); | |
return listQuery(stmt); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment