· 9 years ago · Dec 05, 2016, 09:36 PM
1package OrdersDatabaseHomework;
2
3import java.sql.*;
4import java.util.Scanner;
5
6public class Main {
7 static final String HOST = "jdbc:mysql://localhost:3306/OrderDataBase";
8 static final String USERNAME = "root";
9 static final String PASSWORD = "root";
10
11 static Connection connection;
12
13 public static void main(String[] args) {
14 Scanner scanner = new Scanner(System.in);
15 try {
16 try {
17 //create connection
18 connection = DriverManager.getConnection(HOST, USERNAME, PASSWORD);
19 initDB();
20
21 while (true) {
22 System.out.println("1: add client");
23 System.out.println("2: view all clients");
24 System.out.println("3: add goods");
25 System.out.println("4: view all added goods");
26 System.out.println("5: add order by client id");
27 System.out.println("6: view all orders");
28 System.out.println("-->");
29
30 String s = scanner.nextLine();
31 switch (s) {
32 case "1":
33 addClient(scanner);
34 break;
35 case "2":
36 viewClients(scanner);
37 case "3":
38 addGoods(scanner);
39 break;
40 case "4":
41 viewGoods(scanner);
42 break;
43 case "5":
44 addOrder(scanner);
45 break;
46 case "6":
47 viewOrder(scanner);
48 break;
49 default:
50 return;
51 }
52 }
53 } finally {
54 scanner.close();
55 if (connection != null) connection.close();
56 }
57 } catch (SQLException ex) {
58 ex.printStackTrace();
59 return;
60 }
61 }
62
63 private static void initDB() throws SQLException {
64 Statement st = connection.createStatement();
65 try {
66 st.execute("DROP TABLE IF EXISTS Clients");
67 st.execute("CREATE TABLE Clients (id_clients INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT)");
68
69 st.execute("DROP TABLE IF EXISTS Goods");
70 st.execute("CREATE TABLE Goods (id_goods INT NOT NULL AUTO_INCREMENT PRIMARY KEY, gname VARCHAR(20) NOT NULL, quantity INT, price INT)");
71
72 st.execute("DROP TABLE IF EXISTS Orders");
73
74 st.execute("CREATE TABLE Orders (order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, id_clients INT, id_goods INT, date DATETIME NOT NULL , FOREIGN KEY (id_clients) REFERENCES Clients (id_clients), FOREIGN KEY (id_goods) REFERENCES Goods (id_goods))");
75 //st.execute("CREATE TABLE Orders( id_clients INT , id_goods INT, CONSTRAINT order_pk PRIMARY KEY(id_clients, id_goods), CONSTRAINT clients_pk FOREIGN KEY (id_clients) REFERENCES clients (id_clients), CONSTRAINT goods_pk FOREIGN KEY (id_goods) REFERENCES goods(id_goods), dateofOrder VARCHAR(20) NOT NULL,)");
76
77 } finally {
78 st.close();
79 }
80 }
81
82 private static void addClient(Scanner scanner) throws SQLException {
83 System.out.println("Enter client name: ");
84 String name = scanner.nextLine();
85 System.out.println("Enter client age: ");
86 String Sage = scanner.nextLine();
87 int age = Integer.parseInt(Sage);
88 System.out.println("Client added");
89 System.out.println("------");
90
91 PreparedStatement ps = connection.prepareStatement("INSERT INTO Clients (name, age) VALUES (?,?)");
92
93 try {
94 ps.setString(1, name);
95 ps.setInt(2, age);
96 ps.executeUpdate();
97 } finally {
98 ps.close();
99 }
100
101 }
102
103 private static void viewClients(Scanner scanner) throws SQLException {
104 PreparedStatement ps = connection.prepareStatement("SELECT * FROM Clients");
105
106 try {
107 ResultSet rs = ps.executeQuery();
108 try {
109 ResultSetMetaData md = rs.getMetaData();
110
111 for (int i = 1; i <= md.getColumnCount(); i++)
112 System.out.print(md.getColumnName(i) + "\t\t");
113 System.out.println();
114
115 while (rs.next()) {
116
117 for (int j = 1; j <= md.getColumnCount(); j++) {
118 System.out.print(rs.getString(j) + "\t\t");
119 }
120 System.out.println();
121 }
122 } finally {
123 rs.close();
124 }
125 } finally {
126 ps.close();
127 }
128
129 }
130
131 private static void addGoods(Scanner scanner) throws SQLException {
132 System.out.println("Enter goods name: ");
133 String gname = scanner.nextLine();
134 System.out.println("Enter goods quantity: ");
135 String squantity = scanner.nextLine();
136 int quantity = Integer.parseInt(squantity);
137 System.out.println("Enter total goods price: ");
138 String sprice = scanner.nextLine();
139 int price = Integer.parseInt(sprice);
140 System.out.println("Goods added");
141 System.out.println("------");
142
143 PreparedStatement ps = connection.prepareStatement("INSERT INTO Goods (gname, quantity, price) VALUES (?,?,?)");
144
145 try {
146 ps.setString(1, gname);
147 ps.setInt(2, quantity);
148 ps.setInt(3, price);
149 ps.executeUpdate();
150 } finally {
151 ps.close();
152 }
153 }
154
155 private static void viewGoods(Scanner scanner) throws SQLException {
156 PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM Goods");
157
158 try {
159 ResultSet rs2 = ps2.executeQuery();
160 try {
161 ResultSetMetaData md = rs2.getMetaData();
162
163 for (int i = 1; i <= md.getColumnCount(); i++)
164 System.out.print(md.getColumnName(i) + "\t\t");
165 System.out.println();
166
167 while (rs2.next()) {
168
169 for (int j = 1; j <= md.getColumnCount(); j++) {
170 System.out.print(rs2.getString(j) + "\t\t");
171 }
172 System.out.println();
173 }
174 } finally {
175 rs2.close();
176 }
177 } finally {
178 ps2.close();
179 }
180
181 }
182
183 private static void addOrder(Scanner scanner) throws SQLException {
184
185 System.out.println("Enter client's id: ");
186 String sid_clients = scanner.nextLine();
187 int id_clients = Integer.parseInt(sid_clients);
188 System.out.println("Enter good's id: ");
189 String sid_goods = scanner.nextLine();
190 int id_goods = Integer.parseInt(sid_goods);
191 System.out.println("Order added by client's id and good's id");
192 System.out.println("---------------------------");
193
194 PreparedStatement ps = connection.prepareStatement("INSERT INTO Orders (id_clients, id_goods, date) VALUES (?,?,NOW())");
195
196 try {
197 ps.setInt(1, id_clients);
198 ps.setInt(2,id_goods);
199
200 ps.executeUpdate();
201 } finally {
202 ps.close();
203 }
204 }
205
206
207 private static void viewOrder(Scanner scanner)throws SQLException{
208 PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM Orders");
209
210 try {
211 ResultSet rs2 = ps2.executeQuery();
212 try {
213 while (rs2.next()){
214 int id = rs2.getInt("order_id");
215 int idclient = rs2.getInt("id_clients");
216 int idgoods = rs2.getInt("id_goods");
217 Date date = rs2.getDate("date");
218
219 System.out.println("id = "+ id+" id client = "+idclient + " good id = "
220 +idgoods+ " date of order = "+ date);
221 }
222
223 } finally {
224 rs2.close();
225 }
226 } finally {
227 ps2.close();
228 }
229 }
230}