Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package MainDB;
- import java.sql.*;
- import java.util.Scanner;
- public class App
- {
- static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/db1";
- static final String DB_USER = "root";
- static final String DB_PASSWORD = "password";
- static int i=0;
- static Connection conn;
- public static void initDB() throws SQLException{
- Statement st = conn.createStatement();
- try {
- st.execute("DROP TABLE IF EXISTS Products");
- st.execute("DROP TABLE IF EXISTS Clients");
- st.execute("DROP TABLE IF EXISTS 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();
- }
- }
- public static void addClient(Scanner sc) throws SQLException{
- sc.nextLine();
- System.out.print("Enter client name: ");
- String name = sc.nextLine();
- System.out.print("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(); // for INSERT, UPDATE & DELETE
- } finally {
- ps.close();
- }
- }
- private static void view(int key) throws SQLException {
- PreparedStatement ps=null;
- switch (key){
- case 1:
- ps= conn.prepareStatement("SELECT * FROM Clients");
- break;
- case 2:
- ps= conn.prepareStatement("SELECT * FROM Products");
- break;
- case 3:
- ps= conn.prepareStatement("SELECT * FROM Orders");
- break;
- }
- 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();
- }
- }
- public static void addProduct(Scanner sc) throws SQLException{
- sc.nextLine();
- System.out.print("Enter product name: ");
- String name = sc.nextLine();
- System.out.print("Enter product price: ");
- String sPrice = sc.nextLine();
- int price =Integer.parseInt(sPrice);
- PreparedStatement ps = conn.prepareStatement("INSERT INTO Products (name, price) VALUES(?, ?)");
- try {
- ps.setString(1, name);
- ps.setInt(2, price);
- ps.executeUpdate();
- } finally {
- ps.close();
- }
- }
- public static void addOrder(Scanner sc) throws SQLException{
- sc.nextLine();
- System.out.print("Enter product name: ");
- String pName = sc.nextLine();
- System.out.print("Enter client name: ");
- String cName = sc.nextLine();
- PreparedStatement ps = conn.prepareStatement("INSERT INTO Orders (Pid, Cid) VALUES(0,0)");
- i++;//------------------------------------------Здесь------------------------------------------------------------------
- PreparedStatement ps2=conn.prepareStatement("UPDATE Orders SET Pid=(SELECT id FROM Products WHERE name='"+pName+"') WHERE id="+i);
- PreparedStatement ps3=conn.prepareStatement("UPDATE Orders SET Cid=(SELECT id FROM Clients WHERE name='"+cName+"') WHERE id="+i);
- try {
- ps.executeUpdate();
- ps2.executeUpdate();
- ps3.executeUpdate();
- } finally {
- ps.close();
- ps2.close();
- ps3.close();
- }
- }
- public static void main( String[] args )
- {
- Scanner sc = new Scanner(System.in);
- try {
- try {
- // create connection
- 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: view clients");
- System.out.println("5: view products");
- System.out.println("6: view orders");
- System.out.print("-> ");
- byte s = sc.nextByte();
- switch (s) {
- case 1:
- addClient(sc);
- break;
- case 2:
- addProduct(sc);
- break;
- case 3:
- addOrder(sc);
- break;
- case 4:
- view(1);
- break;
- case 5:
- view(2);
- break;
- case 6:
- view(3);
- break;
- default:
- return;
- }
- }
- } finally {
- sc.close();
- if (conn != null) conn.close();
- }
- } catch (SQLException ex) {
- ex.printStackTrace();
- return;
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement