Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.*;
- import java.util.Scanner;
- public class Main {
- static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/orders";
- static final String DB_USER = "root";
- static final String DB_PASSWORD = "root";
- static Connection conn;
- public static void main(String[] args) {
- Scanner sc = new Scanner(System.in);
- try {
- try {
- conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
- initDB();
- while (true) {
- System.out.println("1: add new product");
- System.out.println("2: make an order");
- System.out.println("3: view orders");
- System.out.print("-> ");
- String s = sc.nextLine();
- switch (s) {
- case "1":
- addProduct();
- break;
- case "2":
- makeAnOrder();
- break;
- case "3":
- viewOrders();
- break;
- default:
- return;
- }
- }
- } finally {
- sc.close();
- if (conn != null) conn.close();
- }
- } catch (SQLException ex) {
- ex.printStackTrace();
- return;
- }
- }
- private static void initDB() throws SQLException {
- Statement st = conn.createStatement();
- try {
- st.execute("DROP TABLE IF EXISTS Orders");
- st.execute("DROP TABLE IF EXISTS Clients");
- st.execute("DROP TABLE IF EXISTS Products");
- st.execute("CREATE TABLE Clients (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- username VARCHAR(50) NOT NULL, email VARCHAR(30),
- phone VARCHAR (20) NOT NULL UNIQUE) ");
- st.execute("CREATE TABLE Products (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- name VARCHAR(50) NOT NULL,
- price DOUBLE NOT NULL)");
- st.execute("CREATE TABLE Orders(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- client_id INT,
- product_id INT)");
- } finally {
- st.close();
- }
- }
- private static void addProduct() throws SQLException {
- Scanner sc = new Scanner(System.in);
- System.out.println("Enter product name:");
- String name = sc.nextLine();
- System.out.println("Enter product price:");
- double price = sc.nextDouble();
- PreparedStatement ps = conn.prepareStatement("INSERT INTO Products (name,price) VALUES(?, ?)");
- try {
- ps.setString(1, name);
- ps.setDouble(2, price);
- ps.executeUpdate();
- } finally {
- ps.close();
- }
- }
- private static void makeAnOrder() throws SQLException {
- Scanner sc = new Scanner(System.in);
- System.out.println("Enter client's name:");
- String username = sc.nextLine();
- System.out.println("Enter client's email:");
- String email = sc.nextLine();
- System.out.println("Enter client's phone:");
- String phone = sc.nextLine();
- viewAllProducts();
- System.out.println("Choose a product. Enter product's id:");
- int productID = sc.nextInt();
- int usernameID = findUserID(username, email, phone);
- addOrder(usernameID, productID);
- }
- private static void addClient(String username, String email, String phone) throws SQLException {
- PreparedStatement ps = conn.prepareStatement("INSERT INTO Clients (username, email, phone) VALUES(?, ?, ?)");
- try {
- ps.setString(1, username);
- ps.setString(2, email);
- ps.setString(3, phone);
- ps.executeUpdate();
- } finally {
- ps.close();
- }
- }
- private static int findUserID(String username, String email, String phone) throws SQLException {
- PreparedStatement ps = conn.prepareStatement("SELECT id FROM Clients WHERE phone = ?");
- ResultSet rs = null;
- int userID = 0;
- try {
- ps.setString(1, phone);
- rs = ps.executeQuery();
- while (true) {
- if (rs.next()) {
- userID = Integer.parseInt(rs.getString("id"));
- break;
- } else {
- addClient(username, email, phone);
- rs = ps.executeQuery();
- }
- }
- } finally
- {
- rs.close();
- ps.close();
- }
- return userID;
- }
- private static void viewAllProducts() throws SQLException {
- PreparedStatement ps = conn.prepareStatement("SELECT * FROM Products");
- printTable(ps);
- }
- private static void addOrder(int usernameID, int productID) throws SQLException {
- PreparedStatement ps = conn.prepareStatement("INSERT INTO Orders (client_id, product_id) VALUES(?, ?)");
- try {
- ps.setInt(1, usernameID);
- ps.setInt(2, productID);
- ps.executeUpdate();
- } finally {
- ps.close();
- }
- }
- private static void viewOrders() throws SQLException {
- PreparedStatement ps = conn.prepareStatement("SELECT p.name,p.price, cl.username, cl.email, cl.phone FROM Products p, Orders ord, Clients cl WHERE cl.id = ord.client_id AND p.id = ord.product_id");
- printTable(ps);
- }
- private static void printTable(PreparedStatement ps) throws SQLException {
- try {
- ResultSet rs = ps.executeQuery();
- try {
- ResultSetMetaData md = rs.getMetaData();
- for (int i = 1; i <= md.getColumnCount(); i++) {
- System.out.print(md.getColumnName(i) + "\t\t");
- }
- System.out.println();
- while (rs.next()) {
- for (int i = 1; i <= md.getColumnCount(); i++) {
- System.out.print(rs.getString(i) + "\t\t");
- }
- System.out.println();
- }
- } finally {
- rs.close();
- }
- } finally {
- ps.close();
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement