Создать проект «База данных заказов». Создать таблицы «Товары» , «Клиенты» и «Заказы». Написать код для добавления новых клиентов, товаров и оформления заказов.

java_connection_ConnectionDB.java

package connection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * Created by andrew on 25.04.17.
 */
public class ConnectionDB {
    private final String DB_CONNECTION = "jdbc:mysql://localhost:3306/ordersDB";
    private final String DB_USER = "root";
    private final String DB_PASSWORD = "9707anton";
    private static ConnectionDB instance = new ConnectionDB();

    private ConnectionDB(){
    }
    public  Connection initConnection() throws SQLException {

            return DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);

    }

    public static ConnectionDB getInstance(){
        return instance;
    }


}

java_MAIN_Main.java

package MAIN;

import Tables.ClientsTable;
import Tables.ItemsTable;
import Tables.OrdersTable;
import module.Client;
import module.Item;
import module.Order;

import java.sql.SQLException;
import java.util.Scanner;



/**
 * Created by andrew on 27.04.17.
 */
public class Main {
    static Scanner scanner = new Scanner(System.in);
    private static ClientsTable clients = new ClientsTable();
    private static OrdersTable orders = new OrdersTable();
    private static ItemsTable items = new ItemsTable();
    public static void main(String[] args) throws SQLException {
        clients.initDb();
        items.initDB();
        orders.initDB();

        while (true){

        System.out.println("1:add client");
        System.out.println("2:add item");
        System.out.println("3:add order");


        int x = scanner.nextInt();
        switch (x){
            case 1:addClient();break;
            case 2:addItem();break;
            case 3:addOrder();
           default:continue;
        }
        }
    }

    private static void addOrder() throws SQLException {
        System.out.println("print user name: ");
        String client  = scanner.next();
        System.out.println("print item name: ");
        String item = scanner.next();
        Client client1 = clients.getClient(client);
        Item item1 = items.getItem(item);
        if(client1==null){
            System.out.println("there is no such user");
            return;}
        if(item1==null){
            System.out.println("there is no such item");
        }
        orders.addOrder(new Order(client1.getId(),item1.getId()));
    }

    private static void addItem() throws SQLException {
        System.out.print("print name of item: ");
        String name = scanner.next();
        System.out.print("print its price: ");
        float price =  scanner.nextFloat();
        items.addItem(new Item(name,price));
    }

    private static void addClient() throws SQLException {
        System.out.print("print client name: ");
        String name = scanner.next();
        clients.addClient(new Client(name));
    }



}

java_module_Client.java

package module;

/**
 * Created by andrew on 25.04.17.
 */
public class Client {
    private int id;
    private String name;
    public Client(String name){
        this.name = name;
    }
    public Client(){}



    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }
}

java_module_Item.java

package module;

/**
 * Created by andrew on 25.04.17.
 */
public class Item {
    private int id;
    private String name;
    private float price;

    public Item() {

    }


    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public float getPrice() {
        return price;
    }

    public Item(String name, float price) {

        this.id = id;
        this.name = name;
        this.price = price;
    }

    public void setPrice(float price) {
        this.price = price;
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }
}

java_module_Order.java

package module;

/**
 * Created by andrew on 25.04.17.
 */
public class Order {
    private int id;
    private int client_id;
    private int item_id;

    public Order(int client_id, int item_id) {
        this.client_id = client_id;
        this.item_id = item_id;
    }

    public int getId() {
        return id;
    }

    public int getClient_id() {
        return client_id;
    }

    public int getItem_id() {
        return item_id;
    }



    public Order(int id, int client_id, int item_id) {
        this.id = id;
        this.client_id = client_id;
        this.item_id = item_id;
    }
}

java_Tables_ClientsTable.java

package Tables;

import connection.ConnectionDB;
import module.Client;

import java.sql.*;

/**
 * Created by andrew on 25.04.17.
 */
public class ClientsTable {



    private static Connection conn =null;


    public ClientsTable()  {

    }

    public void initDb() throws SQLException {
        conn = ConnectionDB.getInstance().initConnection();
        Statement st =null;
        try {
               st= conn.createStatement();
        st.execute("DROP TABLE  IF EXISTS Clients");

        st.execute("CREATE TABLE Clients(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(25) NOT NULL UNIQUE )");}
        catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(st!=null)
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }


    public void addClient(Client client) throws SQLException {
        PreparedStatement pr = conn.prepareStatement("INSERT INTO Clients(name) VALUES (?)");
        pr.setString(1,client.getName());
        pr.executeUpdate();
    }
    public static Client getClient(String name) throws SQLException {
        Client client = null;
        PreparedStatement statement = conn.prepareStatement("SELECT * FROM Clients WHERE name = ?");
        statement.setString(1,name);
        try {
            ResultSet res = statement.executeQuery();
            try {
                while (res.next()){
                client = new Client();
                client.setId(res.getInt(1));
                client.setName(res.getString(2));}

            }finally {
                if(res!=null)
                    res.close();
            }
        }finally {
            statement.close();
        }
        return client;
    }
}

java_Tables_ItemsTable.java

package Tables;

import connection.ConnectionDB;
import module.Client;
import module.Item;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by andrew on 25.04.17.
 */
public class ItemsTable {
    private static Connection conn =null;


    public ItemsTable()   {

    }

    public void initDB() throws SQLException {
        conn = ConnectionDB.getInstance().initConnection();
        Statement st =null;
        try {
            st= conn.createStatement();
            st.execute("DROP TABLE  IF EXISTS Items");

            st.execute("CREATE TABLE Items(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(25) NOT NULL UNIQUE , price FLOAT NOT NULL )");}
        catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(st!=null)
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }




    public void addItem(Item item) throws SQLException {
        PreparedStatement pr = conn.prepareStatement("INSERT INTO Items(name,price) VALUES (?,?)");
        pr.setString(1,item.getName());
        pr.setFloat(2,item.getPrice());
        pr.executeUpdate();
    }
    public Item getItem(String name) throws SQLException {
        Item item= null;
        PreparedStatement statement = conn.prepareStatement("SELECT * FROM Items WHERE name = ?");
        statement.setString(1,name);
        try {
            ResultSet res = statement.executeQuery();
            try {
                while (res.next()){
                    item = new Item();
                item.setName(res.getString(2));
                item.setId(res.getInt(1));
                item.setPrice(res.getFloat(3));}


            }finally {
                if(res!=null)
                    res.close();
            }


        }finally {
            statement.close();
        }
        return item;
    }

}

java_Tables_OrdersTable.java

package Tables;

import connection.ConnectionDB;
import module.Client;
import module.Item;
import module.Order;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by andrew on 25.04.17.
 */
public class OrdersTable {

     private static Connection conn =null;
     public void initDB() throws SQLException {
      conn = ConnectionDB.getInstance().initConnection();
      Statement st =null;
      try {
       st= conn.createStatement();
       st.execute("DROP TABLE  IF EXISTS Orders");

       st.execute("CREATE TABLE Orders(id INT PRIMARY KEY AUTO_INCREMENT, client_id INT NOT NULL,item_id INT NOT NULL)");}
      catch (SQLException e) {
       e.printStackTrace();
      }finally {
       if(st!=null)
        try {
         st.close();
        } catch (SQLException e) {
         e.printStackTrace();
        }
      }
     }


    public void addOrder(Order order) throws SQLException {
        PreparedStatement pr = conn.prepareStatement("INSERT INTO Orders(client_id,item_id) VALUES (?,?)");
        pr.setInt(1,order.getClient_id());
        pr.setFloat(2,order.getItem_id());
        pr.executeUpdate();
    }
}