ZgNCyDQh

1package db;
2
3import java.sql.Connection;
4import java.sql.DatabaseMetaData;
5import java.sql.PreparedStatement;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.sql.Statement;
9import java.util.Random;
10
11public class ReceiptManager {
12	public void createTable() {
13		Connection conn = DBUtil.getConnection();
14
15		boolean tableExists = false;
16		try {
17			DatabaseMetaData dbmd = conn.getMetaData();
18
19			ResultSet rs = dbmd.getTables(null, null, null, new String[] { "TABLE" });
20			while (rs.next()) {
21				if (rs.getString(3).equals("RECEIPT")) {
22					tableExists = true;
23				}
24				System.out.println("Table " + rs.getString(3) + " exists");
25			}
26		} catch (SQLException e) {
27
28			// e.printStackTrace();
29		}
30		if (!tableExists) {
31			try {
32				Statement s = conn.createStatement();
33				s.execute(
34						"CREATE TABLE Receipt  (ID  INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),"
35								+ " code INTEGER, client VARCHAR(20), contractor VARCHAR(20), amount DOUBLE, date DATE, CONSTRAINT primary_key_Receipt PRIMARY KEY (ID))");
36
37				conn.commit();
38
39			} catch (SQLException ex) {
40
41				DBUtil.dispaySQLExceptions(ex);
42
43			}
44		}
45	}
46
47
48	public void updateFactura(int nrfactura) {
49		// get by id
50		// update
51	}
52
53	public void displayAll() throws SQLException {
54		Connection conn = DBUtil.getConnection();
55		PreparedStatement ps = conn.prepareStatement(
56		"SELECT * FROM Receipt"
57		);
58		ResultSet rs = ps.executeQuery();
59		while(rs.next()) {
60			System.out.println(rs.getInt("id") + " " + rs.getString("client") + " " + rs.getString("contractor") 
61			+ " " + rs.getDate("date") + " " + rs.getInt("amount"));
62		}
63		rs.close();
64	}
65
66	public void displayInTimePeriod() {
67
68	}
69
70	public void insertReceipt(Receipt f) throws SQLException {
71		Connection conn = DBUtil.getConnection();
72		PreparedStatement ps = conn.prepareStatement(
73				"INSERT INTO Receipt (code, client, contractor, date, amount) VALUES (?, ?, ?, ?, ?)"
74		);
75		ps.setInt(1, f.getCode());
76		ps.setString(2,  f.getClient());
77		ps.setString(3, f.getSuplier());
78		ps.setDate(4, new java.sql.Date(f.getDate().getTime()));
79		ps.setDouble(5, f.getAmount());
80		ps.executeUpdate();
81	}
82	
83	public static void main(String args[]) throws SQLException {
84		ReceiptManager test = new ReceiptManager();
85		Random r = new Random();
86		test.createTable();
87		test.insertReceipt(Receipt.getReceipt(r.nextInt(3)));
88		test.displayAll();
89		test.displayInTimePeriod();
90		
91	}
92
93}