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/mydb2";
- static final String DB_USER = "root";
- static final String DB_PASSWORD= "Tochilka1";
- 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 client");
- System.out.println("2: add product");
- System.out.println("3: add order");
- System.out.println("4: delete client");
- System.out.println("5: change product");
- System.out.println("6: view orders");
- System.out.println("-> ");
- String s = sc.nextLine();
- switch(s){
- case "1": addClient(sc);
- break;
- case "2": addProduct(sc);
- break;
- case "3": addOrder(sc);
- break;
- case "4": deleteClient(sc);
- break;
- case "5": changeProduct(sc);
- break;
- case "6": viewOrders();
- break;
- default:
- return;
- }
- System.out.println("Success");
- System.out.println("-----------------------------");
- }
- }
- finally {
- sc.close();
- if(conn!= null) conn.close();
- }
- } catch (SQLException e){
- e.printStackTrace();
- return;
- }
- }
- private static void initDB() throws SQLException{
- Statement st = conn.createStatement();
- try{
- //st.execute("DROP TABLE IF EXISTS Clients OR Products OR Orders");
- st.execute("CREATE TABLE Products (Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
- "Name VARCHAR(100) NOT NULL, Price INT NOT NULL)");
- st.execute("CREATE TABLE Clients (Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY," +
- "Name VARCHAR(100) NOT NULL,Phone VARCHAR(20) NOT NULL)");
- st.execute("CREATE TABLE Orders (Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
- "Pid INT NOT NULL, Cid INT NOT NULL, Info VARCHAR(100)");
- }
- finally {
- st.close();
- }
- }
- private static void addClient(Scanner sc) throws SQLException{
- System.out.println("Enter client name: ");
- String name = sc.nextLine();
- System.out.println("Enter client phone");
- String phone = sc.nextLine();
- PreparedStatement ps = conn.prepareStatement("INSERT INTO Clients(name, phone) VALUES(?,?)");
- try{
- ps.setString(1, name);
- ps.setString(2, phone);
- ps.executeUpdate();
- }
- finally {
- ps.close();
- }
- }
- private static void addProduct(Scanner sc) throws SQLException{
- System.out.println("Enter product name: ");
- String name = sc.nextLine();
- System.out.println("Enter product price");
- String sprice = sc.nextLine();
- double price = Double.parseDouble(sprice);
- 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 addOrder(Scanner sc) throws SQLException{
- System.out.println("Enter client name: ");
- String cname = sc.nextLine();
- System.out.println("Enter product name");
- String pname = sc.nextLine();
- PreparedStatement ps = conn.prepareStatement("INSERT INTO " +
- "Orders (Pid, Cid, Info) VALUES ((Select id FROM Products WHERE name = ?), (SELECT id FROM Clients WHERE name = ?), 'New Order')");
- try{
- ps.setString(1, pname);
- ps.setString(2, cname);
- ps.executeUpdate();
- }
- finally {
- ps.close();
- }
- }
- private static void deleteClient(Scanner sc) throws SQLException{
- System.out.println("Enter client name: ");
- String name = sc.nextLine();
- PreparedStatement ps = conn.prepareStatement("DELETE FROM Clients Where name = ?");
- try{
- ps.setString(1, name);
- ps.executeUpdate();
- }
- finally {
- ps.close();
- }
- }
- private static void changeProduct(Scanner sc) throws SQLException{
- System.out.println("Enter product name: ");
- String name = sc.nextLine();
- System.out.println("Enter new price");
- String sprice = sc.nextLine();
- int price = Integer.parseInt(sprice);
- PreparedStatement ps = conn.prepareStatement("UPDATE Products SET price = ? WHERE name = ?");
- try{
- ps.setInt(1, price);
- ps.setString(2, name);
- ps.executeUpdate();
- }
- finally{
- ps.close();
- }
- }
- private static void viewOrders() throws SQLException{
- PreparedStatement ps = conn.prepareStatement("Select Orders.id, Products.Name, Clients.name, Orders.info FROM Orders INNER JOIN Products ON Orders.Pid = Products.Id INNER JOIN Clients ON Orders.Cid = Clients.id");
- 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