API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 23rd, 2016
89
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 16.51 KB | None | 0 0
raw download clone embed print report
  1. package conectors;
  2.  
  3. import java.sql.*;
  4.  
  5. public class MyDataBase {
  6.  
  7.     public static void addTableProduct() throws SQLException {
  8.         Connection connection = DriverManager.getConnection(
  9.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  10.                 "root", "root");
  11.         Statement statement = connection.createStatement();
  12.         statement
  13.                 .execute("create table if not exists Product(id int primary key auto_increment, cat_id int, brand_id int, size_id int, name varchar(255), price double(5,2))");
  14.         statement.close();
  15.         connection.close();
  16.     }
  17.  
  18.     public static void addTableCat() throws SQLException {
  19.         Connection connection = DriverManager.getConnection(
  20.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  21.                 "root", "root");
  22.         Statement statement = connection.createStatement();
  23.         statement
  24.                 .execute("create table if not exists Category(id int primary key auto_increment, category varchar(255))");
  25.         statement.close();
  26.         connection.close();
  27.     }
  28.  
  29.     public static void addTableBrand() throws SQLException {
  30.         Connection connection = DriverManager.getConnection(
  31.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  32.                 "root", "root");
  33.         Statement statement = connection.createStatement();
  34.         statement
  35.                 .execute("create table if not exists Brand(id int primary key auto_increment, brand varchar(255))");
  36.         statement.close();
  37.         connection.close();
  38.     }
  39.  
  40.     public static void addTableSize() throws SQLException {
  41.         Connection connection = DriverManager.getConnection(
  42.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  43.                 "root", "root");
  44.         Statement statement = connection.createStatement();
  45.         statement
  46.                 .execute("create table if not exists Size(id int primary key auto_increment, size int)");
  47.         statement.close();
  48.         connection.close();
  49.     }
  50.  
  51.     public static void addTableUsr() throws SQLException {
  52.         Connection connection = DriverManager.getConnection(
  53.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  54.                 "root", "root");
  55.         Statement statement = connection.createStatement();
  56.         statement
  57.                 .execute("create table if not exists Usr(id int primary key auto_increment, first_name varchar(255), last_name varchar(255), password varchar(255), mail varchar(255), role_id int)");
  58.         statement.close();
  59.         connection.close();
  60.     }
  61.  
  62.     public static void addTableRole() throws SQLException {
  63.         Connection connection = DriverManager.getConnection(
  64.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  65.                 "root", "root");
  66.         Statement statement = connection.createStatement();
  67.         statement
  68.                 .execute("create table if not exists Role(id int primary key auto_increment, role varchar(255))");
  69.         statement.close();
  70.         connection.close();
  71.     }
  72.  
  73.     public static void addTableOrderProduct() throws SQLException {
  74.         Connection connection = DriverManager.getConnection(
  75.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  76.                 "root", "root");
  77.         Statement statement = connection.createStatement();
  78.         statement
  79.                 .execute("create table if not exists Order_product(id int primary key auto_increment, usr_id int, product_id int)");
  80.         statement.close();
  81.         connection.close();
  82.     }
  83.  
  84.     public static void addFkForCategory() throws SQLException {
  85.         Connection connection = DriverManager.getConnection(
  86.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  87.                 "root", "root");
  88.         Statement statement = connection.createStatement();
  89.         try {
  90.             statement
  91.                     .execute("alter table Product add constraint fk_category_product foreign key (cat_id) references Category(id)");
  92.         } catch (SQLException e) {
  93.             e.printStackTrace();
  94.         }
  95.         statement.close();
  96.         connection.close();
  97.     }
  98.  
  99.     public static void addFkForBrand() throws SQLException {
  100.         Connection connection = DriverManager.getConnection(
  101.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  102.                 "root", "root");
  103.         Statement statement = connection.createStatement();
  104.         try {
  105.             statement
  106.                     .execute("alter table Product add constraint fk_brand_product foreign key (brand_id) references Brand(id)");
  107.         } catch (SQLException e) {
  108.             e.printStackTrace();
  109.         }
  110.         statement.close();
  111.         connection.close();
  112.     }
  113.  
  114.     public static void addFkForSize() throws SQLException {
  115.         Connection connection = DriverManager.getConnection(
  116.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  117.                 "root", "root");
  118.         Statement statement = connection.createStatement();
  119.         try {
  120.             statement
  121.                     .execute("alter table Product add constraint fk_size_product foreign key (size_id) references Size(id)");
  122.         } catch (SQLException e) {
  123.             e.printStackTrace();
  124.         }
  125.         statement.close();
  126.         connection.close();
  127.     }
  128.  
  129.     public static void addFkForRole() throws SQLException {
  130.         Connection connection = DriverManager.getConnection(
  131.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  132.                 "root", "root");
  133.         Statement statement = connection.createStatement();
  134.         try {
  135.             statement
  136.                     .execute("alter table Usr add constraint fk_role_usr foreign key (role_id) references Role(id)");
  137.         } catch (SQLException e) {
  138.             e.printStackTrace();
  139.         }
  140.         statement.close();
  141.         connection.close();
  142.     }
  143.  
  144.     public static void addFkForUsr() throws SQLException {
  145.         Connection connection = DriverManager.getConnection(
  146.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  147.                 "root", "root");
  148.         Statement statement = connection.createStatement();
  149.         try {
  150.             statement
  151.                     .execute("alter table Order_product add constraint fk_usr_order_order_product foreign key (usr_id) references Usr(id)");
  152.         } catch (SQLException e) {
  153.             e.printStackTrace();
  154.         }
  155.         statement.close();
  156.         connection.close();
  157.     }
  158.  
  159.     public static void addFkForProduct() throws SQLException {
  160.         Connection connection = DriverManager.getConnection(
  161.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  162.                 "root", "root");
  163.         Statement statement = connection.createStatement();
  164.         try {
  165.             statement
  166.                     .execute("alter table Order_product add constraint fk_product_order_product foreign key (product_id) references Product(id)");
  167.         } catch (SQLException e) {
  168.             e.printStackTrace();
  169.         }
  170.         statement.close();
  171.         connection.close();
  172.     }
  173.  
  174.     public static void insertProductIntoDB(String cat, String brand, int size,
  175.             String name, double price) throws SQLException {
  176.         Connection connection = DriverManager.getConnection(
  177.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  178.                 "root", "root");
  179.  
  180.         Statement stCat = connection.createStatement();
  181.         stCat.execute("insert into Category (category) values ('" + cat + "')");
  182.         stCat.close();
  183.  
  184.         Statement stBrand = connection.createStatement();
  185.         stBrand.execute("insert into Brand (brand) values ('" + brand + "')");
  186.         stBrand.close();
  187.  
  188.         Statement stSize = connection.createStatement();
  189.         stSize.execute("insert into Size (size) values (" + size + ")");
  190.         stSize.close();
  191.  
  192.         int catID = setCat(cat);
  193.         int brandID = setBrand(brand);
  194.         int sizeID = setSize(size);
  195.         addProduct(name, price, catID, brandID, sizeID);
  196.         connection.close();
  197.     }
  198.  
  199.     public static void insertUserIntoDB(String firstname, String lastname,
  200.             String password, String mail, String role) throws SQLException {
  201.         Connection connection = DriverManager.getConnection(
  202.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  203.                 "root", "root");
  204.  
  205.         Statement stCat = connection.createStatement();
  206.         stCat.execute("insert into Role (role) values ('" + role + "')");
  207.         stCat.close();
  208.  
  209.         int roleID = setRole(role);
  210.  
  211.         addUser(firstname, lastname, password, mail, roleID);
  212.         connection.close();
  213.     }
  214.  
  215.     public static void makeOrder(String yourName, String productName)
  216.             throws SQLException {
  217.         Connection connection = DriverManager.getConnection(
  218.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  219.                 "root", "root");
  220.  
  221.         int usrID = setUsr(yourName);
  222.         int productID = setProduct(productName);
  223.  
  224.         addOrderProduct(usrID, productID);
  225.         connection.close();
  226.     }
  227.  
  228.     public static int setCat(String cat) throws SQLException {
  229.         Connection connection = DriverManager.getConnection(
  230.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  231.                 "root", "root");
  232.         Statement statementCategory = connection.createStatement();
  233.         ResultSet setCategory = statementCategory
  234.                 .executeQuery("select id from Category where category = '"
  235.                         + cat + "'");
  236.         while (setCategory.next()) {
  237.             int catID = setCategory.getInt("id");
  238.             return catID;
  239.         }
  240.         setCategory.close();
  241.         statementCategory.close();
  242.         connection.close();
  243.         return 0;
  244.     }
  245.  
  246.     public static int setBrand(String brand) throws SQLException {
  247.         Connection connection = DriverManager.getConnection(
  248.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  249.                 "root", "root");
  250.         Statement statementBrand = connection.createStatement();
  251.         ResultSet setBrand = statementBrand
  252.                 .executeQuery("select id from Brand where brand = '" + brand
  253.                         + "'");
  254.         while (setBrand.next()) {
  255.             int brandID = setBrand.getInt("id");
  256.             return brandID;
  257.         }
  258.         setBrand.close();
  259.         statementBrand.close();
  260.         connection.close();
  261.         return 0;
  262.     }
  263.  
  264.     public static int setSize(int size) throws SQLException {
  265.         Connection connection = DriverManager.getConnection(
  266.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  267.                 "root", "root");
  268.         Statement statementSize = connection.createStatement();
  269.         ResultSet setSize = statementSize
  270.                 .executeQuery("select id from Size where size = " + size);
  271.         while (setSize.next()) {
  272.             int sizeID = setSize.getInt("id");
  273.             return sizeID;
  274.         }
  275.         setSize.close();
  276.         statementSize.close();
  277.         connection.close();
  278.         return 0;
  279.     }
  280.  
  281.     public static int setRole(String role) throws SQLException {
  282.         Connection connection = DriverManager.getConnection(
  283.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  284.                 "root", "root");
  285.         Statement statementRole = connection.createStatement();
  286.         ResultSet setRole = statementRole
  287.                 .executeQuery("select id from Role where role = '" + role + "'");
  288.         while (setRole.next()) {
  289.             int roleID = setRole.getInt("id");
  290.             return roleID;
  291.         }
  292.         setRole.close();
  293.         statementRole.close();
  294.         connection.close();
  295.         return 0;
  296.     }
  297.  
  298.     public static int setUsr(String yourName) throws SQLException {
  299.         Connection connection = DriverManager.getConnection(
  300.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  301.                 "root", "root");
  302.         Statement statementSize = connection.createStatement();
  303.         ResultSet setUser = statementSize
  304.                 .executeQuery("select id from Usr where first_name = '"
  305.                         + yourName + "'");
  306.         while (setUser.next()) {
  307.             int userID = setUser.getInt("id");
  308.             return userID;
  309.         }
  310.         setUser.close();
  311.         statementSize.close();
  312.         connection.close();
  313.         return 0;
  314.     }
  315.  
  316.     public static int setProduct(String productName) throws SQLException {
  317.         Connection connection = DriverManager.getConnection(
  318.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  319.                 "root", "root");
  320.         Statement statementProduct = connection.createStatement();
  321.         ResultSet setProduct = statementProduct
  322.                 .executeQuery("select id from Product where name = '"
  323.                         + productName + "'");
  324.         while (setProduct.next()) {
  325.             int productID = setProduct.getInt("id");
  326.             return productID;
  327.         }
  328.         setProduct.close();
  329.         statementProduct.close();
  330.         connection.close();
  331.         return 0;
  332.     }
  333.  
  334.     public static void addProduct(String name, double price, int catID,
  335.             int brandID, int sizeID) throws SQLException {
  336.         Connection connection = DriverManager.getConnection(
  337.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  338.                 "root", "root");
  339.         Statement statement = connection.createStatement();
  340.         statement
  341.                 .execute("insert into Product (cat_id, brand_id, size_id, name, price) values ("
  342.                         + catID
  343.                         + ", "
  344.                         + brandID
  345.                         + ", "
  346.                         + sizeID
  347.                         + ", "
  348.                         + "'"
  349.                         + name + "', " + price + ")");
  350.         statement.close();
  351.         connection.close();
  352.     }
  353.  
  354.     public static void addUser(String firstname, String lastname,
  355.             String password, String mail, int roleID) throws SQLException {
  356.         Connection connection = DriverManager.getConnection(
  357.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  358.                 "root", "root");
  359.         Statement statement = connection.createStatement();
  360.         statement
  361.                 .execute("insert into Usr (first_name, last_name, password, mail, role_id) values ("
  362.                         + "'"
  363.                         + firstname
  364.                         + "', "
  365.                         + "'"
  366.                         + lastname
  367.                         + "', "
  368.                         + "'"
  369.                         + password
  370.                         + "', "
  371.                         + "'"
  372.                         + mail
  373.                         + "', "
  374.                         + roleID
  375.                         + ")");
  376.         statement.close();
  377.         connection.close();
  378.     }
  379.  
  380.     public static void addOrderProduct(int usrID, int productID)
  381.             throws SQLException {
  382.         Connection connection = DriverManager.getConnection(
  383.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  384.                 "root", "root");
  385.         Statement statement = connection.createStatement();
  386.         statement
  387.                 .execute("insert into Order_product (usr_id, product_id) values ("
  388.                         + usrID + ", " + productID + ")");
  389.         statement.close();
  390.         connection.close();
  391.     }
  392.  
  393.     public static void readOrder() throws SQLException {
  394.         Connection connection = DriverManager.getConnection(
  395.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  396.                 "root", "root");
  397.         Statement statement = connection.createStatement();
  398.         ResultSet set = statement.executeQuery("select * from Order_product");
  399.         while (set.next()) {
  400.             int id = set.getInt("id");
  401.             System.out.print(id);
  402.             System.out.print(" ");
  403.             int userID = set.getInt("usr_id");
  404.             getUser(userID);
  405.             int productId = set.getInt("product_id");
  406.             getProduct(productId);
  407.             System.out.println();
  408.         }
  409.         statement.close();
  410.         set.close();
  411.         connection.close();
  412.     }
  413.  
  414.     public static void getUser(int userID) throws SQLException {
  415.         Connection connection = DriverManager.getConnection(
  416.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  417.                 "root", "root");
  418.         Statement statement = connection.createStatement();
  419.         ResultSet resultSet = statement
  420.                 .executeQuery("select * from Usr where id = " + userID);
  421.         while (resultSet.next()) {
  422.             String name = resultSet.getString("first_name");
  423.             System.out.print(name);
  424.             System.out.print(" ");
  425.         }
  426.         resultSet.close();
  427.         statement.close();
  428.         connection.close();
  429.     }
  430.  
  431.     public static void getProduct(int productId) throws SQLException {
  432.         Connection connection = DriverManager.getConnection(
  433.                 "jdbc:mysql://localhost:3306/shape_database?useSSL=false",
  434.                 "root", "root");
  435.         Statement statement = connection.createStatement();
  436.         ResultSet resultSet2 = statement
  437.                 .executeQuery("select * from Product where id = " + productId);
  438.         while (resultSet2.next()) {
  439.             String product = resultSet2.getString("name");
  440.             System.out.print(product);
  441.             System.out.print(" ");
  442.         }
  443.         resultSet2.close();
  444.         statement.close();
  445.         connection.close();
  446.     }
  447. }
  448.  
  449.  
  450. package conectors;
  451.  
  452. import java.sql.*;
  453. import java.util.Scanner;
  454.  
  455. public class Main {
  456.     private final static Scanner SC = new Scanner(System.in);
  457.  
  458.     public static void main(String[] args) throws SQLException {
  459.  
  460.         boolean isRun = true;
  461.         while (isRun) {
  462.             System.out.println();
  463.             System.out.println("Enter 1 to start DB");
  464.             System.out.println("Enter 2 to insert Products into DB");
  465.             System.out.println("Enter 3 to insert User into DB");
  466.             System.out.println("Enter 4 to make order");
  467.             System.out.println("Enter 5 to exit");
  468.             switch (SC.nextInt()) {
  469.             case 1:
  470.                 MyDataBase.addTableProduct();
  471.                 MyDataBase.addTableCat();
  472.                 MyDataBase.addTableBrand();
  473.                 MyDataBase.addTableSize();
  474.                 MyDataBase.addFkForCategory();
  475.                 MyDataBase.addFkForBrand();
  476.                 MyDataBase.addFkForSize();
  477.                 MyDataBase.addTableUsr();
  478.                 MyDataBase.addTableRole();
  479.                 MyDataBase.addFkForRole();
  480.                 MyDataBase.addTableOrderProduct();
  481.                 MyDataBase.addFkForProduct();
  482.                 MyDataBase.addFkForUsr();
  483.                 break;
  484.             case 2:
  485.                 MyDataBase.insertProductIntoDB("car", "AUDI", 2700, "S8",
  486.                         500.00);
  487.                 MyDataBase
  488.                         .insertProductIntoDB("car", "BMW", 2700, "X6", 500.00);
  489.                 MyDataBase.insertProductIntoDB("null", "null", 2700, "null",
  490.                         500.00);
  491.                 MyDataBase.insertProductIntoDB("null", "null", 2700, "null",
  492.                         500.00);
  493.                 MyDataBase
  494.                         .insertProductIntoDB("car", "LADA", 2000, "6", 500.00);
  495.                 break;
  496.             case 3:
  497.                 MyDataBase.insertUserIntoDB("Vova", "Pedko", "blabla",
  498.                         "pokemon", "Admin");
  499.                 MyDataBase.insertUserIntoDB("Vova1", "Pedko1", "blabla",
  500.                         "pokemon", "User");
  501.                 break;
  502.             case 4:
  503.                 MyDataBase.makeOrder("Vova", "X6");
  504.                 MyDataBase.readOrder();
  505.                 break;
  506.             case 5:
  507.                 isRun = false;
  508.             }
  509.  
  510.         }
  511.     }
  512.  
  513. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!