Advertisement
Guest User

Untitled

a guest
May 8th, 2016
101
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.30 KB | None | 0 0
  1. import java.sql.*;
  2. import java.util.Scanner;
  3.  
  4. public class Main {
  5. static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/mydb2";
  6. static final String DB_USER = "root";
  7. static final String DB_PASSWORD= "Tochilka1";
  8.  
  9. static Connection conn;
  10.  
  11. public static void main(String[] args) {
  12. Scanner sc = new Scanner(System.in);
  13.  
  14. try {
  15. try{
  16. conn = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
  17. //initDB();
  18.  
  19. while(true){
  20. System.out.println("1: add client");
  21. System.out.println("2: add product");
  22. System.out.println("3: add order");
  23. System.out.println("4: delete client");
  24. System.out.println("5: change product");
  25. System.out.println("6: view orders");
  26. System.out.println("-> ");
  27.  
  28. String s = sc.nextLine();
  29. switch(s){
  30. case "1": addClient(sc);
  31. break;
  32. case "2": addProduct(sc);
  33. break;
  34. case "3": addOrder(sc);
  35. break;
  36. case "4": deleteClient(sc);
  37. break;
  38. case "5": changeProduct(sc);
  39. break;
  40. case "6": viewOrders();
  41. break;
  42. default:
  43. return;
  44. }
  45. System.out.println("Success");
  46. System.out.println("-----------------------------");
  47. }
  48. }
  49. finally {
  50. sc.close();
  51. if(conn!= null) conn.close();
  52. }
  53. } catch (SQLException e){
  54. e.printStackTrace();
  55. return;
  56. }
  57. }
  58.  
  59. private static void initDB() throws SQLException{
  60. Statement st = conn.createStatement();
  61. try{
  62. //st.execute("DROP TABLE IF EXISTS Clients OR Products OR Orders");
  63. st.execute("CREATE TABLE Products (Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
  64. "Name VARCHAR(100) NOT NULL, Price INT NOT NULL)");
  65. st.execute("CREATE TABLE Clients (Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY," +
  66. "Name VARCHAR(100) NOT NULL,Phone VARCHAR(20) NOT NULL)");
  67. st.execute("CREATE TABLE Orders (Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
  68. "Pid INT NOT NULL, Cid INT NOT NULL, Info VARCHAR(100)");
  69. }
  70. finally {
  71. st.close();
  72. }
  73. }
  74.  
  75. private static void addClient(Scanner sc) throws SQLException{
  76. System.out.println("Enter client name: ");
  77. String name = sc.nextLine();
  78. System.out.println("Enter client phone");
  79. String phone = sc.nextLine();
  80.  
  81. PreparedStatement ps = conn.prepareStatement("INSERT INTO Clients(name, phone) VALUES(?,?)");
  82. try{
  83. ps.setString(1, name);
  84. ps.setString(2, phone);
  85. ps.executeUpdate();
  86. }
  87. finally {
  88. ps.close();
  89. }
  90. }
  91.  
  92. private static void addProduct(Scanner sc) throws SQLException{
  93. System.out.println("Enter product name: ");
  94. String name = sc.nextLine();
  95. System.out.println("Enter product price");
  96. String sprice = sc.nextLine();
  97. double price = Double.parseDouble(sprice);
  98.  
  99. PreparedStatement ps = conn.prepareStatement("INSERT INTO Products(name, price) VALUES(?, ?)");
  100. try{
  101. ps.setString(1, name);
  102. ps.setDouble(2, price);
  103. ps.executeUpdate();
  104. }
  105. finally {
  106. ps.close();
  107. }
  108. }
  109. private static void addOrder(Scanner sc) throws SQLException{
  110. System.out.println("Enter client name: ");
  111. String cname = sc.nextLine();
  112. System.out.println("Enter product name");
  113. String pname = sc.nextLine();
  114.  
  115. PreparedStatement ps = conn.prepareStatement("INSERT INTO " +
  116. "Orders (Pid, Cid, Info) VALUES ((Select id FROM Products WHERE name = ?), (SELECT id FROM Clients WHERE name = ?), 'New Order')");
  117. try{
  118. ps.setString(1, pname);
  119. ps.setString(2, cname);
  120. ps.executeUpdate();
  121. }
  122. finally {
  123. ps.close();
  124. }
  125. }
  126.  
  127. private static void deleteClient(Scanner sc) throws SQLException{
  128. System.out.println("Enter client name: ");
  129. String name = sc.nextLine();
  130.  
  131. PreparedStatement ps = conn.prepareStatement("DELETE FROM Clients Where name = ?");
  132. try{
  133. ps.setString(1, name);
  134. ps.executeUpdate();
  135. }
  136. finally {
  137. ps.close();
  138. }
  139. }
  140.  
  141. private static void changeProduct(Scanner sc) throws SQLException{
  142. System.out.println("Enter product name: ");
  143. String name = sc.nextLine();
  144. System.out.println("Enter new price");
  145. String sprice = sc.nextLine();
  146. int price = Integer.parseInt(sprice);
  147.  
  148. PreparedStatement ps = conn.prepareStatement("UPDATE Products SET price = ? WHERE name = ?");
  149. try{
  150. ps.setInt(1, price);
  151. ps.setString(2, name);
  152. ps.executeUpdate();
  153. }
  154. finally{
  155. ps.close();
  156. }
  157. }
  158.  
  159. private static void viewOrders() throws SQLException{
  160. 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");
  161. try{
  162. ResultSet rs = ps.executeQuery();
  163. try{
  164. ResultSetMetaData md = rs.getMetaData();
  165.  
  166. for (int i = 1; i<= md.getColumnCount(); i++)
  167. System.out.print(md.getColumnName(i) + "\t\t");
  168. System.out.println();
  169.  
  170. while(rs.next()){
  171. for(int i = 1; i<=md.getColumnCount(); i++){
  172. System.out.print(rs.getString(i) + "\t\t");
  173. }
  174. System.out.println();
  175. }
  176. }finally {
  177. rs.close();
  178. }
  179. } finally {
  180. ps.close();
  181. }
  182. }
  183.  
  184. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement