· 4 years ago · Jan 06, 2021, 07:14 AM
1// ReceiptManager.java
2package jdbc;
3
4import java.sql.Connection;
5import java.sql.DatabaseMetaData;
6import java.sql.ResultSet;
7import java.sql.ResultSetMetaData;
8import java.sql.SQLException;
9import java.sql.Statement;
10import java.util.Random;
11
12public class ReceiptManager {
13 public void createTable() {
14 try (Connection conn = DBUtil.getConnection();) {
15
16 boolean tableExists = false;
17
18 DatabaseMetaData dbmd = conn.getMetaData();
19
20 ResultSet rs = dbmd.getTables(null, null, null, new String[] { "TABLE" });
21 while (rs.next()) {
22 if (rs.getString(3).equals("RECEIPT")) {
23 tableExists = true;
24 }
25 System.out.println("Table " + rs.getString(3) + " exists");
26 }
27
28 if (!tableExists) {
29
30 Statement s = conn.createStatement();
31 s.execute(
32 "CREATE TABLE Receipt (ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),"
33 + " code INTEGER, client VARCHAR(20), contractor VARCHAR(20), amount DOUBLE, date DATE, CONSTRAINT primary_key_Receipt PRIMARY KEY (ID))");
34
35 conn.commit();
36 }
37 } catch (SQLException ex) {
38 DBUtil.dispaySQLExceptions(ex);
39 }
40
41 }
42
43 public void insertReceipt(Receipt f) {
44 try (Connection conn = DBUtil.getConnection();) {
45 Statement s = conn.createStatement();
46 //s.execute("INSERT INTO RECEIPT (code, client, contractor, amount, date) VALUES (123456, 'UVT', 'HP', 1000, '2015-03-21')");
47 s.execute(String.format(
48 "INSERT INTO RECEIPT (code, client, contractor, amount, date) VALUES (%d, '%s', '%s', %f, '%s')",
49 f.getCode(), f.getClient(), f.getSupplier(), f.getAmount(), Receipt.df.format(f.getDate())));
50 System.out.println("Factura " + f + " a fost inserata");
51 } catch (SQLException ex) {
52 System.out.println("Here!");
53 DBUtil.dispaySQLExceptions(ex);
54 }
55
56 }
57
58 public void updateFactura(long id, double amount) {
59 try (Connection conn = DBUtil.getConnection();) {
60 Statement s = conn.createStatement();
61 s.executeUpdate("UPDATE RECEIPT SET AMOUNT=" + amount + " WHERE ID=" + id);
62
63 } catch (SQLException ex) {
64 DBUtil.dispaySQLExceptions(ex);
65 }
66 }
67
68 public void displayAll() {
69 try (Connection conn = DBUtil.getConnection();) {
70 Statement s = conn.createStatement();
71 ResultSet results = s.executeQuery("SELECT * FROM RECEIPT");
72 ResultSetMetaData resultsMetaData = results.getMetaData();
73 int numberOfColumns = resultsMetaData.getColumnCount();
74 for (int i = 1; i <= numberOfColumns; i++) {
75 System.out.format("%15s", resultsMetaData.getColumnName(i));
76 }
77 System.out.println();
78 while (results.next()) {
79 for (int i = 1; i <= numberOfColumns; i++) {
80 System.out.format("%15s", results.getString(i));
81 }
82 System.out.println();
83 }
84
85 } catch (SQLException ex) {
86 DBUtil.dispaySQLExceptions(ex);
87 }
88 }
89
90 public void displayInTimePeriod(String firstDate, String secondDate) {
91 try (Connection conn = DBUtil.getConnection();) {
92 Statement s = conn.createStatement();
93 ResultSet results = s.executeQuery(String.format(
94 "SELECT * FROM RECEIPT WHERE date BETWEEN '%s' AND '%s'",
95 firstDate, secondDate));
96 ResultSetMetaData resultsMetaData = results.getMetaData();
97 int numberOfColumns = resultsMetaData.getColumnCount();
98 for (int i = 1; i <= numberOfColumns; i++) {
99 System.out.format("%15s", resultsMetaData.getColumnName(i));
100 }
101 System.out.println();
102 while (results.next()) {
103 for (int i = 1; i <= numberOfColumns; i++) {
104 System.out.format("%15s", results.getString(i));
105 }
106 System.out.println();
107 }
108
109 } catch (SQLException ex) {
110 DBUtil.dispaySQLExceptions(ex);
111 }
112 }
113
114 public static void main(String args[]) {
115 ReceiptManager test = new ReceiptManager();
116 Random r = new Random();
117 test.createTable();
118 //test.insertReceipt(Receipt.getReceipt(r.nextInt(3)));
119 //test.updateFactura(0, 2500.0);
120 //test.displayAll();
121 test.displayInTimePeriod("2012-01-01", "2014-01-01");
122 }
123
124}