FRVTUSfY

1package MainDB;
2
3import java.sql.*;
4import java.util.Scanner;
5
6public class App 
7{
8    static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/db1";
9    static final String DB_USER = "root";
10    static final String DB_PASSWORD = "password";
11    static int i=0;
12
13    static Connection conn;
14    public static void initDB() throws SQLException{
15        Statement st = conn.createStatement();
16        try {
17            st.execute("DROP TABLE IF EXISTS Products");
18            st.execute("DROP TABLE IF EXISTS Clients");
19            st.execute("DROP TABLE IF EXISTS Orders");
20            st.execute("CREATE TABLE Products (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, price INT NOT NULL)");
21            st.execute("CREATE TABLE Clients (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, phone VARCHAR(20) NOT NULL)");
22            st.execute("CREATE TABLE Orders (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Pid INT NOT NULL,Cid INT NOT NULL, Info VARCHAR(100))");
23        } finally {
24            st.close();
25        }
26    }
27    public static void addClient(Scanner sc) throws SQLException{
28        sc.nextLine();
29        System.out.print("Enter client name: ");
30        String name = sc.nextLine();
31        System.out.print("Enter client phone: ");
32        String phone = sc.nextLine();
33
34        PreparedStatement ps = conn.prepareStatement("INSERT INTO Clients (name, phone) VALUES(?, ?)");
35        try {
36            ps.setString(1, name);
37            ps.setString(2, phone);
38            ps.executeUpdate(); // for INSERT, UPDATE & DELETE
39        } finally {
40            ps.close();
41        }
42    }
43    private static void view(int key) throws SQLException {
44        PreparedStatement ps=null;
45        switch (key){
46            case 1:
47                ps= conn.prepareStatement("SELECT * FROM Clients");
48                break;
49            case 2:
50                ps= conn.prepareStatement("SELECT * FROM Products");
51                break;
52            case 3:
53                ps= conn.prepareStatement("SELECT * FROM Orders");
54                break;
55        }
56
57        try {
58
59            ResultSet rs = ps.executeQuery();
60            try {
61                ResultSetMetaData md = rs.getMetaData();
62
63                for (int i = 1; i <= md.getColumnCount(); i++)
64                    System.out.print(md.getColumnName(i) + "\t\t");
65                System.out.println();
66
67                while (rs.next()) {
68                    for (int i = 1; i <= md.getColumnCount(); i++) {
69                        System.out.print(rs.getString(i) + "\t\t");
70                    }
71                    System.out.println();
72                }
73            } finally {
74                rs.close();
75            }
76        } finally {
77            ps.close();
78        }
79    }
80    public static void addProduct(Scanner sc) throws SQLException{
81        sc.nextLine();
82        System.out.print("Enter product name: ");
83        String name = sc.nextLine();
84        System.out.print("Enter product price: ");
85        String sPrice = sc.nextLine();
86        int price =Integer.parseInt(sPrice);
87        PreparedStatement ps = conn.prepareStatement("INSERT INTO Products (name, price) VALUES(?, ?)");
88        try {
89            ps.setString(1, name);
90            ps.setInt(2, price);
91            ps.executeUpdate();
92        } finally {
93            ps.close();
94        }
95    }
96    public static void addOrder(Scanner sc) throws SQLException{
97        sc.nextLine();
98        System.out.print("Enter product name: ");
99        String pName = sc.nextLine();
100        System.out.print("Enter client name: ");
101        String cName = sc.nextLine();
102
103        PreparedStatement ps = conn.prepareStatement("INSERT INTO Orders (Pid, Cid) VALUES(0,0)");
104        i++;//------------------------------------------Здесь------------------------------------------------------------------
105        PreparedStatement ps2=conn.prepareStatement("UPDATE Orders SET Pid=(SELECT id FROM Products WHERE name='"+pName+"') WHERE id="+i);
106        PreparedStatement ps3=conn.prepareStatement("UPDATE Orders SET Cid=(SELECT id FROM Clients WHERE name='"+cName+"') WHERE id="+i);
107        try {
108            ps.executeUpdate();
109            ps2.executeUpdate();
110            ps3.executeUpdate();
111        } finally {
112            ps.close();
113            ps2.close();
114            ps3.close();
115        }
116    }
117    public static void main( String[] args )
118    {
119        Scanner sc = new Scanner(System.in);
120        try {
121            try {
122                // create connection
123                conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
124                initDB();
125
126                while (true) {
127                    System.out.println("1: add client");
128                    System.out.println("2: add product");
129                    System.out.println("3: add order");
130                    System.out.println("4: view clients");
131                    System.out.println("5: view products");
132                    System.out.println("6: view orders");
133                    System.out.print("-> ");
134
135                    byte s = sc.nextByte();
136                    switch (s) {
137                        case 1:
138                            addClient(sc);
139                            break;
140                        case 2:
141                            addProduct(sc);
142                            break;
143                        case 3:
144                            addOrder(sc);
145                            break;
146                        case 4:
147                            view(1);
148                            break;
149                        case 5:
150                            view(2);
151                            break;
152                        case 6:
153                            view(3);
154                            break;
155                        default:
156                            return;
157                    }
158                }
159            } finally {
160                sc.close();
161                if (conn != null) conn.close();
162            }
163        } catch (SQLException ex) {
164            ex.printStackTrace();
165            return;
166        }
167    }
168}