nnVVbLp5

1package estudando0307;
2
3import java.sql.Connection;
4import java.sql.PreparedStatement;
5import java.sql.ResultSet;
6
7public class DAOGastos {
8
9	public static void createTable(Connection conn) {
10		try {
11			conn.createStatement().execute(
12			"CREATE TABLE IF NOT EXISTS GASTOS("
13			+ "ID INTEGER NOT NULL,"
14			+ "TITULAR VARCHAR(20) NOT NULL,"
15			+ "ESTABELECIMENTO VARCHAR(30) NOT NULL,"
16			+ "DATA VARCHAR(40),"
17			+ "VALOR DECIMAL(4,2),"
18			+ "PRIMARY KEY(ID))");
19		} catch (Exception e) {
20			e.printStackTrace();
21		}
22	}
23	
24	public static void inserir(Connection conn, Gastos novo) {
25		PreparedStatement psInsert = null;
26		try {
27			String insert = "INSERT INTO GASTOS(ID, TITULAR, ESTABELECIMENTO, DATA, VALOR) VALUES (?,?,?,?,?)";
28			psInsert = conn.prepareStatement(insert);
29			psInsert.setInt(1, novo.getId());
30			psInsert.setString(2, novo.getTitular());
31			psInsert.setString(3, novo.getEstabelecimento());
32			psInsert.setString(4, novo.getData());
33			psInsert.setDouble(5, novo.getValor());
34			psInsert.execute();
35		} catch (Exception e) {
36			e.printStackTrace();
37		}finally {
38			try {
39				psInsert.close();
40			} catch (Exception e2) {
41				// TODO: handle exception
42			}
43		}
44		
45	}
46	
47	public static void atualizar(Connection conn, Gastos alterar) {
48		PreparedStatement psUpdate = null;
49		try {
50			String update = "UPDATE GASTOS SET VALOR = ? WHERE ID = ?";
51			psUpdate = conn.prepareStatement(update);
52			psUpdate.setDouble(1, alterar.getValor());
53			psUpdate.setInt(2, alterar.getId());
54			psUpdate.execute();
55		} catch (Exception e) {
56			e.printStackTrace();
57		}finally {
58			try {
59				psUpdate.close();
60			} catch (Exception e2) {
61			}
62		}
63	}
64	
65	public static void excluir(Connection conn, Gastos excluir) {
66		PreparedStatement psDelete = null;
67		try {
68			String delete = "DELETE FROM GASTOS WHERE ID = ?";
69			psDelete = conn.prepareStatement(delete);
70			psDelete.setInt(1, excluir.getId());
71			psDelete.execute();
72		} catch (Exception e) {
73			e.printStackTrace();
74		}finally {
75			try {
76				psDelete.close();
77			} catch (Exception e2) {
78			}
79		}
80	}	
81
82	public static Gastos select(Connection conn, int id, String estabelecimento, String data, double valor) {
83		PreparedStatement psSelect = null;
84		try {
85			String select = "SELECT * FROM GASTOS WHERE ID = ? AND ESTABELECIMENTO = ? AND DATA = ?";
86			psSelect = conn.prepareStatement(select);
87			psSelect.setInt(1,	id);
88			psSelect.setString(2, estabelecimento);
89			psSelect.setString(3, data);
90			ResultSet rsGastos = psSelect.executeQuery();
91			if(rsGastos.next()) { 
92				Gastos retorno = new Gastos();
93				retorno.setId(rsGastos.getInt("ID"));
94				retorno.setEstabelecimento(rsGastos.getString("ESTABELECIMENTO"));
95				retorno.setData(rsGastos.getString("DATA"));
96				retorno.setValor(rsGastos.getDouble("VALOR"));
97				rsGastos.close();
98				return retorno;
99			} else {
100				throw new RuntimeException("Dado não encontrado.");
101			}
102		} catch (Exception e) {
103			e.printStackTrace();
104		}
105		return null;
106	}
107	
108}