ghAwGSHR

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