API tools faq
paste
Guest User

Untitled

a guest
Apr 8th, 2018
104
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 16.88 KB | None | 0 0
raw download clone embed print report
  1. package logic;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.ResultSet;
  6. import java.sql.SQLException;
  7. import java.sql.Statement;
  8. import java.sql.PreparedStatement;
  9. import java.util.ArrayList;
  10. import java.util.logging.Level;
  11. import java.util.logging.Logger;
  12. import javax.swing.DefaultListModel;
  13. import javax.swing.JOptionPane;
  14.  
  15. import localData.Config;
  16.  
  17.  
  18. /** Klassen som setter opp koblingen til databasen
  19. * Bruk getConnection() for å få en kobling til databasen
  20. */
  21. public class DatabaseConnector{
  22.  
  23. public DatabaseConnector(){ }
  24. private static Connection con = null; // connection object
  25. private static Statement stmt = null; // statement object
  26.  
  27. public static void initialize(){
  28. try {
  29. con = getConnection();
  30. stmt = con.createStatement();
  31. con.setAutoCommit(true);
  32. }catch(Exception e){
  33. JOptionPane.showMessageDialog(null, "Klarte ikke laste inn databasen", "Database-error", JOptionPane.ERROR_MESSAGE);
  34. }
  35. Address.setConnection(con);
  36. }
  37. public static Connection getConnection() throws Exception {
  38. String url = "jdbc:mysql:" + Config.DBHost + ":" + Config.DBport + "/" + Config.DBName;
  39. String user = Config.DBUsername;
  40. String password = Config.DBPassword;
  41.  
  42. try{
  43. Class.forName("com.mysql.jdbc.Driver");
  44. }catch(Exception e){
  45. JOptionPane.showMessageDialog(null, "Klarte ikke laste inn sql driver", "Driver-error", JOptionPane.ERROR_MESSAGE);
  46.  
  47. }
  48. return DriverManager.getConnection(url, user, password);
  49. }
  50. public void closeConnection() {
  51. try {
  52. if (con != null) {
  53. con.close();
  54. }
  55. } catch (SQLException ex) {
  56. Logger lgr = Logger.getLogger(DatabaseConnector.class.getName());
  57. lgr.log(Level.WARNING, ex.getMessage(), ex);
  58. }
  59. }
  60. /**
  61. *
  62. * @param phoneNumber
  63. * @return User fra database med requested phoneNumber
  64. * @throws Exception
  65. */
  66. public static User getUser(String phoneNumber) throws Exception{
  67. String sql = "SELECT name, phone, address_id, id FROM users WHERE phone='" + phoneNumber + "'";
  68. ResultSet getUser_rs = stmt.executeQuery(sql);
  69. if (getUser_rs.first() == false) {
  70. return null;
  71. }
  72. String name = getUser_rs.getString(1);
  73. String phone = getUser_rs.getString(2);
  74. String address_id = getUser_rs.getString(3);
  75. String id = getUser_rs.getString(4);
  76. getUser_rs.close();
  77.  
  78. ResultSet getAddress_rs = stmt.executeQuery("SELECT street, houseNumber, zipcode, city, id FROM addresses WHERE id='"+address_id+"'");
  79. getAddress_rs.first();
  80. Address address = new Address(getAddress_rs.getInt(5), getAddress_rs.getString(1), Integer.parseInt(getAddress_rs.getString(2)), getAddress_rs.getString(3), getAddress_rs.getString(4));
  81. getAddress_rs.close();
  82. User user = new User(name, phone, address);
  83. user.setUserId(id);
  84. return user;
  85. }
  86. /**
  87. * @param Userid from database
  88. * @return users name from database
  89. * @throws Exception
  90. */
  91. public static String getUser(int id) throws Exception{
  92. ResultSet getUser_rs = stmt.executeQuery("SELECT name FROM users WHERE id='" + id + "'");
  93. getUser_rs.first();
  94. String s = getUser_rs.getString(1);
  95. getUser_rs.close();
  96. return s;
  97. }
  98. /**
  99. * user id, returns user
  100. */
  101. public static String getPhoneByUserId(int id) throws Exception{
  102. ResultSet getUser_rs = stmt.executeQuery("SELECT phone FROM users WHERE id='" + id + "'");
  103. getUser_rs.first();
  104. String s = getUser_rs.getString(1);
  105. getUser_rs.close();
  106. return s;
  107. }
  108.  
  109. /**
  110. *
  111. * @return arraylist med alle users fra database
  112. * @throws Exception
  113. */
  114. public static DefaultListModel getUsers() throws Exception{
  115. ResultSet address_rs = stmt.executeQuery("SELECT id, street, houseNumber, zipcode, city FROM addresses");
  116. DefaultListModel users = new DefaultListModel();
  117. address_rs.first();
  118. ArrayList<Address> addresses = new ArrayList<Address>();
  119. do{
  120. int id = address_rs.getInt(1);
  121. String street = address_rs.getString(2);
  122. String houseNumber = address_rs.getString(3);
  123. String zipcode = address_rs.getString(4);
  124. String city = address_rs.getString(5);
  125. Address address = new Address(id, street, Integer.parseInt(houseNumber), zipcode, city);
  126. addresses.add(address);
  127. }while(address_rs.next());
  128. address_rs.close();
  129. ResultSet users_rs = stmt.executeQuery("SELECT name, phone, address_id, id FROM users order by name");
  130. users_rs.first();
  131. do{
  132. String name = users_rs.getString(1);
  133. String phone = users_rs.getString(2);
  134. String address_id = users_rs.getString(3);
  135. String id = users_rs.getString(4);
  136. //finding address from array with id equal to address_id
  137. for(int i = 0; i<addresses.size(); i++){
  138. if(addresses.get(i).getId() == Integer.parseInt(address_id)){
  139. User user = new User(name, phone, addresses.get(i));
  140. user.setUserId(id);
  141. users.addElement(user);
  142. }
  143. }
  144. }while(users_rs.next());
  145. address_rs.close();
  146.  
  147. return users;
  148. }
  149. /**
  150. *
  151. * @param id
  152. * @return productet med angitt id i databasen
  153. * @throws Exception
  154. */
  155. public static Product getProduct(String id) throws Exception{
  156. switch(id.charAt(0)){
  157. case 'o': id = "10"; break;
  158. case 'n': id = "11"; break;
  159. case 'i': id = "12"; break;
  160. case 'e': id = "13"; break;
  161. case 'q': id = "14"; break;
  162. case 'a': id = "15"; break;
  163. }
  164. ResultSet getP_rs = stmt.executeQuery("SELECT name, description, price FROM products WHERE id='"+id+"'");
  165. if(getP_rs.first() == false){
  166. return null;
  167. }
  168. String name = getP_rs.getString(1);
  169. String description = getP_rs.getString(2);
  170. String price = getP_rs.getString(3);
  171. Product p = new Product(name, description, Double.parseDouble(price));
  172. return p;
  173. }
  174. /**
  175. *
  176. * @return alle produkter fra databasen
  177. * @throws Exception
  178. */
  179. public static DefaultListModel getProducts() throws Exception{
  180. DefaultListModel products = new DefaultListModel();
  181. ResultSet products_rs = stmt.executeQuery("SELECT name, description, price, id FROM products");
  182. products_rs.first();
  183. do{
  184. String name = products_rs.getString(1);
  185. String description = products_rs.getString(2);
  186. String price = products_rs.getString(3);
  187. String id = products_rs.getString(4);
  188. Product p = new Product(name, description, Double.parseDouble(price));
  189. p.setId(id);
  190. products.addElement(p);
  191. }while(products_rs.next());
  192. products_rs.close();
  193.  
  194. return products;
  195. }
  196.  
  197. public static void newUser(User user)throws Exception{
  198. con.setAutoCommit(true);
  199. int houseNumber = user.getAddress().getHouseNumber();
  200. String street = user.getAddress().getStreet(), zipcode = user.getAddress().getZipcode(), city = user.getAddress().getCity();
  201. stmt.executeUpdate("INSERT into addresses SET street='"+ street + "', houseNumber='"+ houseNumber +"', houseLetter='a', zipcode='"+ zipcode +"', city='"+ city +"', country='NO'");
  202. ResultSet newUser_rs = stmt.executeQuery("SELECT MAX(id) FROM addresses");
  203. newUser_rs.first();
  204. String address_id = newUser_rs.getString(1);
  205. stmt.executeUpdate("INSERT into users SET name='" + user.getName() + "', phone='" + user.getPhone() + "', address_id='"+ address_id + "'");
  206. newUser_rs.close();
  207. con.setAutoCommit(false);
  208. }
  209.  
  210. public static DefaultListModel getOrders(String action) throws Exception{
  211. String sql = "SELECT user_id, ordered, due, delivered, id, products, comment FROM orders WHERE ";
  212. if (action.equals("due")) {
  213. sql += "due IS NULL ORDER BY ordered";
  214. } else if(action.equals("deliver")) {
  215. sql += "due IS NOT NULL AND delivered IS NULL ORDER BY due";
  216. } else if(action.equals("delivered")) {
  217. sql += "delivered IS NOT NULL ORDER BY delivered";
  218. } else {
  219. throw new Exception("Action must be one of due, deliver or delivered.");
  220. }
  221.  
  222. ResultSet orders_rs = stmt.executeQuery(sql);
  223. DefaultListModel orders = new DefaultListModel();
  224. if(orders_rs.first() == false){
  225. return orders;
  226. }
  227. do{
  228. String user_id = orders_rs.getString(1);
  229. String ordered = orders_rs.getString(2);
  230. String due = orders_rs.getString(3);
  231. String delivered = orders_rs.getString(4);
  232. String id = orders_rs.getString(5);
  233. String products = orders_rs.getString(6);
  234. String comment = orders_rs.getString(7);
  235. Order o = new Order(user_id);
  236. o.createFoodList(products);
  237. o.setId(id);
  238. o.setDue(due);
  239. o.setDelivered(delivered);
  240. o.setKommentar(comment);
  241. orders.addElement(o);
  242. }while(orders_rs.next());
  243. orders_rs.close();
  244. return orders;
  245. }
  246.  
  247. public static void newOrder(Order order){
  248. try{
  249. stmt.executeUpdate("INSERT INTO orders SET user_id = '" + order.getUserId() + "', due = NULL, delivered = NULL, products = '" + order.getProducts() + "', comment='" + order.getKommentar() + "', levering='" + order.getLevering() + "', kort='" + order.getKort() + "'");
  250. con.setAutoCommit(false);
  251. }catch(Exception e){
  252. JOptionPane.showMessageDialog(null, "Klarte ikke lagre ny bestilling i databasen", "Error", JOptionPane.ERROR_MESSAGE);
  253. }
  254. }
  255.  
  256. public static void newProduct(Product product){
  257. try{
  258. con.setAutoCommit(true);
  259. ResultSet newProduct_rs = stmt.executeQuery("SELECT COUNT(*) FROM products");
  260. newProduct_rs.first();
  261. int id = Integer.parseInt(newProduct_rs.getString(1)) + 1;
  262. stmt.executeUpdate("INSERT INTO products VALUES (" + id + ", '" + product.getName() + "', '" + product.getDescription() + "', '" + product.getPrice() + "')");
  263. con.setAutoCommit(false);
  264. newProduct_rs.close();
  265. }catch(Exception e){
  266. JOptionPane.showMessageDialog(null, "Klarte ikke legge til nytt produkt i databasen", "SQL-feil", JOptionPane.ERROR_MESSAGE);
  267. }
  268. }
  269.  
  270. public static void deleteUser(User user){
  271. try{
  272. ResultSet deleteUser_rs = stmt.executeQuery("SELECT id, address_id from users WHERE name='" + user.getName() + "' AND phone='" + user.getPhone() + "'");
  273. deleteUser_rs.first();
  274. String id = deleteUser_rs.getString(1);
  275. String address_id = deleteUser_rs.getString(2);
  276. con.setAutoCommit(true);
  277. stmt.executeUpdate("DELETE from users WHERE id='" + id + "'");
  278. stmt.executeUpdate("DELETE from addresses WHERE id='" + address_id + "'");
  279. con.setAutoCommit(false);
  280. deleteUser_rs.close();
  281. }catch(Exception e){
  282. JOptionPane.showMessageDialog(null, "Klarte ikke slette kunde fra databasen", "SQL-feil", JOptionPane.ERROR_MESSAGE);
  283. }
  284. }
  285.  
  286. public static void deleteProduct(Product product){
  287. try{
  288. ResultSet deleteProduct_rs = stmt.executeQuery("SELECT id from products WHERE name='" + product.getName() + "' AND description='" + product.getDescription() + "' AND price='" + product.getPrice() + "'");
  289. deleteProduct_rs.first();
  290. int id = Integer.parseInt(deleteProduct_rs.getString(1));
  291. con.setAutoCommit(true);
  292. stmt.executeUpdate("DELETE from products WHERE id='" + id + "'");
  293. con.setAutoCommit(false);
  294. deleteProduct_rs.close();
  295. }catch(Exception e){
  296. JOptionPane.showMessageDialog(null, "Klarte ikke slette produkt fra databasen", "SQL-feil", JOptionPane.ERROR_MESSAGE);
  297. }
  298. }
  299.  
  300. public static void deleteOrder(Order order){
  301. try{
  302. con.setAutoCommit(true);
  303. stmt.executeUpdate("DELETE from orders WHERE id='" + order.getId() + "'");
  304. con.setAutoCommit(false);
  305. }catch(Exception e){
  306. JOptionPane.showMessageDialog(null, "Klarte ikke slette bestilling fra databasen", "SQL-feil", JOptionPane.ERROR_MESSAGE);
  307. }
  308. }
  309.  
  310. public static void edit(User oldUser, User newUser){
  311. try{
  312. String address_id, id;
  313. ResultSet edit_rs = stmt.executeQuery("SELECT address_id FROM users WHERE name='" + oldUser.getName() + "' AND phone='" + oldUser.getPhone() + "'");
  314. edit_rs.first();
  315. address_id = edit_rs.getString(1);
  316. edit_rs = stmt.executeQuery("SELECT id FROM users WHERE name='" + oldUser.getName() + "'AND phone ='" + oldUser.getPhone() + "'");
  317. edit_rs.first();
  318. id = edit_rs.getString(1);
  319. edit_rs.close();
  320. con.setAutoCommit(true);
  321. //oppdaterer addressen til address_id
  322. stmt.executeUpdate("UPDATE addresses SET street='" + newUser.getAddress().getStreet() + "', houseNumber='" + newUser.getAddress().getHouseNumber() + "', zipcode='" + newUser.getAddress().getZipcode() + "', city='" + newUser.getAddress().getCity() + "' WHERE id='" + address_id + "'");
  323. //Oppdaterer user
  324. stmt.executeUpdate("UPDATE users SET name='" + newUser.getName() + "', phone='" + newUser.getPhone() + "' WHERE id='" + id + "'");
  325. con.setAutoCommit(false);
  326. }catch(Exception e){
  327. JOptionPane.showMessageDialog(null, "Klarte ikke endre kundeinformasjon", "SQL-feil", JOptionPane.ERROR_MESSAGE);
  328. }
  329. }
  330.  
  331.  
  332. public static void edit(Product oldProduct, Product newProduct){
  333. try{
  334. ResultSet edit = stmt.executeQuery("SELECT id from products WHERE name='" + oldProduct.getName() + "' AND description='" + oldProduct.getDescription() + "' AND price='" + oldProduct.getPrice() + "'");
  335. edit.first();
  336. String id = edit.getString(1);
  337. edit.close();
  338. con.setAutoCommit(true);
  339. stmt.executeUpdate("UPDATE products SET name='" + newProduct.getName() + "', description='" + newProduct.getDescription() + "', price='" + newProduct.getPrice() + "' WHERE id='" + id + "'");
  340. con.setAutoCommit(false);
  341. }catch(Exception e){
  342. JOptionPane.showMessageDialog(null, "Klarte ikke endre produktet", "SQL-feil", JOptionPane.ERROR_MESSAGE);
  343. }
  344. }
  345.  
  346. public static void edit(String id){
  347. try{
  348. con.setAutoCommit(true);
  349. stmt.executeUpdate("UPDATE orders SET due=now() WHERE id='" + id + "'");
  350. con.setAutoCommit(false);
  351. }catch(Exception e){
  352. JOptionPane.showMessageDialog(null, "Bestillingen ble ikke satt til ferdig", "SQL-feil", JOptionPane.ERROR_MESSAGE);
  353. }
  354. }
  355.  
  356. public static void delivered(Order o){
  357. try{
  358. con.setAutoCommit(true);
  359. stmt.executeUpdate("UPDATE ORDERS SET delivered=now() WHERE id='" + o.getId() + "'");
  360. con.setAutoCommit(false);
  361. }catch(Exception e){
  362.  
  363. }
  364. }
  365.  
  366. public static void notFinished(String a){
  367. try {
  368. con.setAutoCommit(true);
  369. stmt.executeUpdate("UPDATE orders SET due = NULL WHERE id='" + a + "'");
  370. con.setAutoCommit(false);
  371. } catch (Exception e) {;
  372. JOptionPane.showMessageDialog(null, "Bestillingen ble ikke satt til 'ikke ferdig'", "SQL-feil", JOptionPane.ERROR_MESSAGE);
  373. }
  374. }
  375. public static Address getAddressFromUser(int user_id) {
  376. try {
  377. String sql = "SELECT a.id, a.street, a.houseNumber, a.zipcode, a.city FROM users AS u, addresses AS a WHERE u.address_id = a.id AND u.id = ?";
  378. PreparedStatement st = con.prepareStatement(sql);
  379. st.setInt(1, user_id);
  380. ResultSet rs = st.executeQuery();
  381. rs.first();
  382. Address ad = new Address(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getString(5));
  383. rs.close();
  384. return ad;
  385. } catch(Exception e) {
  386. // e.printStackTrace();
  387. }
  388. return null;
  389. }
  390.  
  391. public static Order getOrder(String s) throws Exception{
  392. if(s.length() <= 0){
  393. JOptionPane.showMessageDialog(null, "Ugyldig bestilling", "SQL-feil", JOptionPane.ERROR_MESSAGE);
  394. }
  395. String stringID = "";
  396. boolean b = true;
  397. for(int i = 0; i<s.length(); i++){
  398. if((int)s.charAt(i) == 32){
  399. b = false;
  400. }
  401. if(b){
  402. stringID += s.charAt(i);
  403. }
  404. }
  405. int id = Integer.parseInt(stringID);
  406. ResultSet getOrderRs = stmt.executeQuery("SELECT id, user_id, ordered, due, delivered, products, comment, levering, kort FROM orders WHERE id ='" + id + "'");
  407. getOrderRs.first();
  408. String i = getOrderRs.getString(1);
  409. String userID = getOrderRs.getString(2);
  410. String ordered = getOrderRs.getString(3);
  411. String due = getOrderRs.getString(4);
  412. String delivered = getOrderRs.getString(5);
  413. String products = getOrderRs.getString(6);
  414. String comment = getOrderRs.getString(7);
  415. int levering = getOrderRs.getInt(8);
  416. int kort = getOrderRs.getInt(9);
  417. Order o = new Order(userID);
  418. o.setDue(due);
  419. o.setId(i);
  420. o.setDelivered(delivered);
  421. o.createFoodList(products);
  422. o.setKommentar(comment);
  423. o.setLevering(levering);
  424. o.setKort(kort);
  425. return o;
  426. }
  427. }
Add Comment
Please, Sign In to add comment
Public Pastes
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!