SHARE
TWEET

Untitled

a guest Nov 6th, 2018 105 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package com.itstep.retailer.utils;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.PreparedStatement;
  5. import java.sql.ResultSet;
  6. import java.sql.SQLException;
  7. import java.sql.Timestamp;
  8. import java.util.ArrayList;
  9. import java.util.Date;
  10. import java.util.List;
  11.  
  12. import com.itstep.retailer.beans.Product;
  13. import com.itstep.retailer.beans.Role;
  14. import com.itstep.retailer.beans.UserAccount;
  15.  
  16. public class DBUtils {
  17.  
  18.     /**
  19.      * GENERAL_USER Role by Default
  20.      * @param conn
  21.      * @param userAccount
  22.      * @return
  23.      */
  24.     public static boolean saveUserAccount(Connection conn, UserAccount userAccount){
  25.        
  26.        
  27.         String sql = "INSERT INTO `USER_ACCOUNT`"
  28.                 + "(`USER_NAME`, `GENDER`, `PASSWORD`, "
  29.                 + "`ROLE_ID`, `EMAIL`, `ACTIVE`, `LAST_UPDATED_TS`, `CREATED_TS`)"
  30.                 + " VALUES (?, ?, ?, ?, ?, 'N', ?, CURRENT_TIMESTAMP)";
  31.         int result = 0;
  32.         try {
  33.             PreparedStatement pstms = conn.prepareStatement(sql);
  34.             pstms.setString(1, userAccount.getUserName());
  35.             pstms.setString(2, userAccount.getGender());
  36.             pstms.setString(3, userAccount.getPassword()); // MUST BE ALREADY ENCRYPTED
  37.             pstms.setInt(4, Role.GENERAL_USER.getId());//userAccount.getRole().getId()
  38.             pstms.setString(5, userAccount.getEmail());
  39.             pstms.setTimestamp(6, new Timestamp(new Date().getTime())); // GET Timestamp from JAVA
  40.             result = pstms.executeUpdate();
  41.            
  42.         } catch (SQLException e) {
  43.             // TODO Auto-generated catch block
  44.             e.printStackTrace();
  45.         }
  46.        
  47.         return result==1;
  48.     }
  49.    
  50.    
  51.    
  52.    
  53.     public static UserAccount findUser(Connection conn, String userName, String password) throws SQLException {
  54.  
  55.        
  56.        
  57.        
  58.         String sql = "Select a.User_Name, a.Password, a.Gender ,"
  59.                 + "a.ROLE_ID "
  60.                 + ", a.EMAIL, a.LAST_UPDATED_TS , "
  61.                 + "a.CREATED_TS, a.active  from User_Account a "
  62.                 + "where a.User_Name = ? and a.password= ?";
  63.  
  64.         PreparedStatement pstm = conn.prepareStatement(sql);
  65.         pstm.setString(1, userName);
  66.         pstm.setString(2, password);
  67.         ResultSet rs = pstm.executeQuery();
  68.  
  69.         if (rs.next()) {
  70.             String gender = rs.getString("Gender");
  71.             Role role = Role.getRole(rs.getInt("ROLE_ID"));
  72.             String email = rs.getString("EMAIL");
  73.             char active = rs.getString("ACTIVE").toCharArray()[0];
  74.             Timestamp last_ts = rs.getTimestamp("LAST_UPDATED_TS");
  75.             Timestamp created_ts = rs.getTimestamp("CREATED_TS");
  76.            
  77.             UserAccount user = new UserAccount();
  78.             user.setUserName(userName);
  79.             user.setPassword(password);
  80.             user.setGender(gender);
  81.             user.setRole(role);
  82.             user.setEmail(email);
  83.             user.setLastUpdatedTs(last_ts);
  84.             user.setCreatedTs(created_ts);
  85.             user.setActive(active);
  86.            
  87.            
  88.             return user;
  89.         }
  90.         return null;
  91.     }
  92.  
  93.     public static UserAccount findUser(Connection conn, String userName) throws SQLException {
  94.  
  95.         String sql = "Select a.User_Name, a.Password, a.Gender, a.ROLE_ID, a.EMAIL, a.LAST_UPDATED_TS, a.CREATED_TS, a.active from User_Account a "//
  96.                 + " where a.User_Name = ? ";
  97.  
  98.         PreparedStatement pstm = conn.prepareStatement(sql);
  99.         pstm.setString(1, userName);
  100.  
  101.         ResultSet rs = pstm.executeQuery();
  102.  
  103.         if (rs.next()) {
  104.             String password = rs.getString("Password");
  105.             String gender = rs.getString("Gender");
  106.             Role role = Role.getRole(rs.getInt("ROLE_ID"));
  107.             String email = rs.getString("EMAIL");
  108.             char active = rs.getString("ACTIVE").toCharArray()[0];
  109.             Timestamp last_ts = rs.getTimestamp("LAST_UPDATED_TS");
  110.             Timestamp created_ts = rs.getTimestamp("CREATED_TS");
  111.            
  112.             UserAccount user = new UserAccount();
  113.             user.setUserName(userName);
  114.             user.setPassword(password);
  115.             user.setGender(gender);
  116.             user.setRole(role);
  117.             user.setEmail(email);
  118.             user.setLastUpdatedTs(last_ts);
  119.             user.setCreatedTs(created_ts);
  120.             user.setActive(active);
  121.             return user;
  122.         }
  123.         return null;
  124.     }
  125.  
  126.     public static List<Product> queryProduct(Connection conn) throws SQLException {
  127.         String sql = "Select "
  128.                 + "a.productCode, a.productName, a.productLine, a.productScale, a.productVendor, a.productDescription, a.quantityInStock, a.buyPrice, a.MSRP "
  129.                 + "from Products a ";
  130.  
  131.         PreparedStatement pstm = conn.prepareStatement(sql);
  132.  
  133.         ResultSet rs = pstm.executeQuery();
  134.         List<Product> list = new ArrayList<Product>();
  135.         while (rs.next()) {
  136.             Product product = new Product();
  137.             product.setProductCode(rs.getString("productCode"));
  138.             product.setProductName(rs.getString("productName"));
  139.             product.setProductLine(rs.getString("productLine"));
  140.             product.setProductScale(rs.getString("productScale"));
  141.             product.setProductVendor(rs.getString("productVendor"));
  142.             product.setProductDescription(rs.getString("productDescription"));
  143.             product.setQuantityInStock(rs.getInt("quantityInStock"));
  144.             product.setBuyPrice(rs.getDouble("quantityInStock"));
  145.             product.setMSRP(rs.getDouble("MSRP"));
  146.             list.add(product);
  147.         }
  148.         return list;
  149.     }
  150.  
  151.     public static Product findProduct(Connection conn, String productCode) throws SQLException {
  152.         String sql = "Select "
  153.                 + "a.productCode, a.productName, a.productLine, a.productScale, a.productVendor, a.productDescription, a.quantityInStock, a.buyPrice, a.MSRP "
  154.                 + "from Products a where a.productCode = ?";
  155.  
  156.         PreparedStatement pstm = conn.prepareStatement(sql);
  157.         pstm.setString(1, productCode);
  158.  
  159.         ResultSet rs = pstm.executeQuery();
  160.  
  161.         while (rs.next()) {      
  162.             Product product = new Product(
  163.                     rs.getString("productCode"),
  164.                     rs.getString("productName"),              
  165.                     rs.getString("productLine"),              
  166.                     rs.getString("productScale"),              
  167.                     rs.getString("productVendor"),              
  168.                     rs.getString("productDescription"),            
  169.                     rs.getInt("quantityInStock"),              
  170.                     rs.getDouble("buyPrice"),              
  171.                     rs.getDouble("MSRP"));
  172.             return product;
  173.         }
  174.         return null;
  175.     }
  176.  
  177.     public static void updateProduct(Connection conn, Product product) throws SQLException {
  178.         String sql = "Update Products set "
  179.                 + "productName=?, productLine=?, productScale=?, productVendor=?, productDescription=?, quantityInStock=?, buyPrice=?, MSRP=? "
  180.                 + "where productCode=? ";
  181.  
  182.         PreparedStatement pstm = conn.prepareStatement(sql);
  183.  
  184.         pstm.setString(1, product.getProductName());
  185.         pstm.setString(2, product.getProductLine());
  186.         pstm.setString(3, product.getProductScale());
  187.         pstm.setString(4, product.getProductVendor());
  188.         pstm.setString(5, product.getProductDescription());
  189.        
  190.         pstm.setInt(6, product.getQuantityInStock());
  191.         pstm.setDouble(7, product.getBuyPrice());
  192.         pstm.setDouble(8, product.getMSRP());
  193.    
  194.         pstm.setString(9, product.getProductCode());
  195.         pstm.executeUpdate();
  196.     }
  197.  
  198.     public static void insertProduct(Connection conn, Product product) throws SQLException {
  199.         String sql = "Insert into Product(productCode,productName,productLine,productScale,productVendor,productDescription,quantityInStock,buyPrice,MSRP) "
  200.                 + "values (?,?,?,?,?,?,?,?,?)";
  201.  
  202.         PreparedStatement pstm = conn.prepareStatement(sql);
  203.  
  204.         pstm.setString(1, product.getProductName());
  205.         pstm.setString(2, product.getProductLine());
  206.         pstm.setString(3, product.getProductScale());
  207.         pstm.setString(4, product.getProductVendor());
  208.         pstm.setString(5, product.getProductDescription());
  209.        
  210.         pstm.setInt(6, product.getQuantityInStock());
  211.         pstm.setDouble(7, product.getBuyPrice());
  212.         pstm.setDouble(8, product.getMSRP());
  213.    
  214.         pstm.setString(9, product.getProductCode());
  215.  
  216.         pstm.executeUpdate();
  217.     }
  218.  
  219.     public static void deleteProduct(Connection conn, String code) throws SQLException {
  220.         String sql = "Delete From Product where Code= ?";
  221.  
  222.         PreparedStatement pstm = conn.prepareStatement(sql);
  223.  
  224.         pstm.setString(1, code);
  225.  
  226.         pstm.executeUpdate();
  227.     }
  228.  
  229. }
RAW Paste Data
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
 
Top