Bgm7EPSS

1package ua.kiev.prog;
2
3import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
4import jdk.nashorn.internal.runtime.regexp.joni.Regex;
5
6import java.sql.*;
7import java.util.Random;
8import java.util.Scanner;
9import java.util.regex.Matcher;
10import java.util.regex.Pattern;
11
12/**
13 * Created by defaust on 17.05.16.
14 */
15public class Main {
16
17    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/dborders";
18    static final String DB_USER = "Server";
19    static final String DB_PASSWORD = "12345";
20    static final Scanner sc = new Scanner(System.in);
21
22    static Connection conn;
23
24    public static void main(String[] args) {
25        try {
26            try {
27                // create connection
28                conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
29                initDB();
30
31                //System.out.println(searchData("Denis", "clients"));
32
33                while (true) {
34                    System.out.println("1: add order");
35                    System.out.println("2: add random orders");
36                    System.out.println("3: view clients");
37                    System.out.println("4: view orders by name");
38                    System.out.print("-> ");
39
40                    String s = sc.nextLine();
41                    switch (s) {
42                        case "1":
43                            addClient(sc);
44                            break;
45                        case "2":
46                            insertRandomClients(sc);
47                            break;
48                        case "3":
49                            viewClients();
50                            break;
51                        case "4":
52                            viewClientsFilter(sc);
53                            break;
54                        default:
55                            continue;
56                    }
57                }
58            } finally {
59                sc.close();
60                if (conn != null) conn.close();
61            }
62        } catch (SQLException ex) {
63            ex.printStackTrace();
64            return;
65        }
66    }
67
68    private static void initDB() throws SQLException {
69        Statement st = conn.createStatement();
70        try {
71            st.execute("DROP TABLE IF EXISTS orders, clients, goods");
72
73            st.execute("CREATE TABLE goods   (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL UNIQUE, PRIMARY KEY (id)) ENGINE = InnoDB CHARACTER SET = UTF8;");
74            st.execute("INSERT INTO goods (name) VALUES (\"apple\")");
75
76            st.execute("CREATE TABLE clients (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL UNIQUE, PRIMARY KEY (id)) ENGINE = InnoDB CHARACTER SET = UTF8;");
77            st.execute("INSERT INTO clients (name) VALUES (\"Denis\")");
78
79            st.execute("CREATE TABLE orders  (id INT NOT NULL AUTO_INCREMENT, " +
80                    "clientsID INT NOT NULL, " +
81                    "goodsID INT NOT NULL, " +
82                    "quantity INT NOT NULL, " +
83                    "PRIMARY KEY(id), "+
84                    "FOREIGN KEY (clientsID) REFERENCES clients(id) ON UPDATE CASCADE ON DELETE RESTRICT, "+
85                    "FOREIGN KEY (goodsID) REFERENCES goods(id) ON UPDATE CASCADE ON DELETE RESTRICT) "+
86                    "ENGINE = InnoDB CHARACTER SET=UTF8;");
87            //st.execute("INSERT INTO orders (clientsID, goodsID, quantity)  VALUES ()");
88        } finally {
89            st.close();
90        }
91    }
92/**********************************************************************************************************************/
93    private static void addClient(Scanner sc) throws SQLException {
94        System.out.print("Enter person name: ");
95        String name = sc.nextLine();
96
97        System.out.print("Enter product: ");
98        String product = sc.nextLine();
99
100        System.out.print("Enter product's quantity:");
101
102        String sSquare = sc.nextLine();
103        int quantity = Integer.parseInt(sSquare);
104
105        writeOrders(name, product, quantity, false);
106    }
107
108    private static int searchData(String queryName, String nameTable) throws SQLException {
109        // так как я не понял как в разных частях запроса можно было бы использовать "?"
110        // то в середину пришлось вставлять посредствам StringBuilder
111
112        StringBuilder sqlQuery = new StringBuilder(50);
113        sqlQuery.append("SELECT * FROM ");
114        sqlQuery.append(nameTable);
115        sqlQuery.append(" WHERE name = (?)");
116
117        PreparedStatement preparedStatement = conn.prepareStatement(sqlQuery.toString());
118        preparedStatement.setString(1, queryName);
119
120        ResultSet resultSet = preparedStatement.executeQuery();
121        int id = -1;
122        while(resultSet.next()){
123              id = resultSet.getInt("id");
124            //добавить проверку на количество больше одного вывода!
125        }
126        return id;
127    }
128
129    private static void writeOrders(String name, String product, int quantity, boolean flag) throws SQLException {
130        int nameID = searchData(name, "clients");
131        int productID = searchData(product, "goods");
132
133        if (nameID == -1){
134            System.out.print("Absent client name in DB " + name + ". Are you going to add? (1 - \"Yes\"   /   \"Any keys\" - \"No\"): ");
135            if (flag || sc.nextLine().equals("1")){
136                writeField(name, "clients");
137                nameID = searchData(name, "clients");
138                System.out.println("nameID:" + nameID);
139            } else {
140                System.err.println("You refused to add!");
141                return;
142            }
143        }
144        if (productID == -1){
145            System.out.print("Absent product name in DB " + product + ". Are you going to add? (1 - \"Yes\"   /   \"Any keys\" - \"No\"): ");
146            if (flag || sc.nextLine().equals("1")){
147                writeField(product, "goods");
148                productID = searchData(product, "goods");
149                System.out.println("productID:" + productID);
150            } else {
151                System.err.println("You refused to add!");
152                return;
153            }
154        }
155
156        PreparedStatement ps = conn.prepareStatement("INSERT INTO orders (clientsID, goodsID, quantity) VALUES(?, ?, ?)");
157        try {
158            ps.setInt(1, nameID);
159            ps.setInt(2, productID);
160            ps.setInt(3, quantity);
161            ps.executeUpdate(); // for INSERT, UPDATE & DELETE
162        } catch (MySQLIntegrityConstraintViolationException ex) {
163
164        } finally {
165            ps.close();
166        }
167    }
168
169    private static void writeField(String queryName, String nameTable) throws SQLException {
170        StringBuilder sqlQuery = new StringBuilder(60);
171        sqlQuery.append("INSERT INTO ");
172        sqlQuery.append(nameTable);
173        sqlQuery.append(" (name) VALUES (?);");
174        PreparedStatement ps = conn.prepareStatement(sqlQuery.toString());
175        try {
176            ps.setString(1, queryName);
177            ps.executeUpdate(); // for INSERT, UPDATE &
178        } catch (SQLException sqlEx){
179            sqlEx.printStackTrace();
180        } finally {
181            ps.close();
182        }
183    }
184
185
186    private static void insertRandomClients(Scanner sc) throws SQLException {
187        System.out.print("Enter numbers of generate records: ");
188        String sCount = sc.nextLine();
189        int count = Integer.parseInt(sCount);
190        Random rnd = new Random();
191        for (int i = 0; i < count; i++) {
192            String name = "name"+rnd.nextInt(100);
193            String product = "product"+rnd.nextInt(100);
194            writeOrders(name, product, rnd.nextInt(100), true);
195        }
196    }
197
198    private static void viewClients() throws SQLException {
199        System.out.println("--------------------------------------------------------------------");
200        PreparedStatement ps = conn.prepareStatement("SELECT clients.name, goods.name FROM orders JOIN clients ON orders.clientsID=clients.id JOIN goods ON orders.goodsID=goods.id;");
201        try {
202            // table of data representing a database result set,
203            ResultSet rs = ps.executeQuery();
204            try {
205                // can be used to get information about the types and properties of the columns in a ResultSet object
206                ResultSetMetaData md = rs.getMetaData();
207
208                for (int i = 1; i <= md.getColumnCount(); i++)
209                    System.out.print(md.getColumnName(i) + "\t\t\t");
210                System.out.println();
211
212                while (rs.next()) {
213                    for (int i = 1; i <= md.getColumnCount(); i++) {
214                        System.out.print(rs.getString(i) + "          ");
215                    }
216                    System.out.println();
217                }
218            } finally {
219                rs.close(); // rs can't be null according to the docs
220            }
221        } finally {
222            ps.close();
223            System.out.println("--------------------------------------------------------------------");
224        }
225    }
226
227    private static void viewClientsFilter(Scanner sc) throws SQLException {
228        System.out.print("Enter clients name:");
229        String name = sc.nextLine();
230
231
232        PreparedStatement ps =
233                conn.prepareStatement
234                        ("SELECT goods.name FROM orders JOIN clients ON orders.clientsID=clients.id JOIN goods ON orders.goodsID=goods.id WHERE clients.name=(?);");
235        try {
236            ps.setString(1, name);
237            // table of data representing a database result set,
238            ResultSet rs = ps.executeQuery();
239            try {
240                // can be used to get information about the types and properties of the columns in a ResultSet object
241                ResultSetMetaData md = rs.getMetaData();
242
243                for (int i = 1; i <= md.getColumnCount(); i++)
244                    System.out.print(md.getColumnName(i) + "\t\t\t");
245                System.out.println();
246
247                while (rs.next()) {
248                    for (int i = 1; i <= md.getColumnCount(); i++) {
249                        System.out.print(rs.getString(i) + "\t\t\t");
250                    }
251                    System.out.println();
252                }
253            } finally {
254                rs.close(); // rs can't be null according to the docs
255            }
256        } finally {
257            ps.close();
258        }
259    }
260
261}