×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: Java
Posted by: Nhan Tran
Added: Aug 7, 2021 1:04 PM
Modified: Aug 8, 2021 9:28 AM
Views: 3655
Tags: no tags
  1. package DAO;
  2.  
  3. import Entity.Account;
  4. import Utils.DBContext;
  5. import java.sql.Connection;
  6. import java.sql.PreparedStatement;
  7. import java.sql.ResultSet;
  8. import java.sql.SQLException;
  9. import java.util.ArrayList;
  10. import java.util.List;
  11. import java.util.Random;
  12. import javax.naming.NamingException;
  13.  
  14. /*
  15.  * To change this license header, choose License Headers in Project Properties.
  16.  * To change this template file, choose Tools | Templates
  17.  * and open the template in the editor.
  18.  */
  19. /**
  20.  *
  21.  * @author TranViNhan
  22.  */
  23. public class AccountDAO {
  24.  
  25.     public static final String DB_STATUS = "aStatus";
  26.     public static final Integer DB_STATUS_TRUE = 1;
  27.     public static final Integer DB_STATUS_FALSE = 0;
  28.     public static final String DB_TABLE = "tblAccount";
  29.     public static final String DB_ID = "aID";
  30.     public static final String DB_COLUMNS_WITHOUT_ID = "aUsername,aPassword,aRole";
  31.     public static final String DB_COLUMNS = DB_ID + "," + DB_COLUMNS_WITHOUT_ID;
  32.     public static final String DB_UPDATE_COLUMNS = "aUsername=?,aPassword=?,aRole=?";
  33. //    TODO change Account to your DB
  34.  
  35.     /**
  36.      *
  37.      * @return @throws SQLException
  38.      * @throws NamingException
  39.      */
  40.     public List<Account> getAll() throws SQLException, NamingException {
  41.         List<Account> list = new ArrayList<>();//make list
  42.         String query = " select " + DB_COLUMNS + " from " + DB_TABLE + " where " + DB_STATUS + " = "+DB_STATUS_TRUE;
  43.         try (Connection conn = DBContext.makeConnection(); PreparedStatement ps = conn.prepareStatement(query);) {
  44.             try (ResultSet rs = ps.executeQuery()) {
  45.                 while (rs.next()) {
  46.                     Account a = new Account(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
  47.                     list.add(a);
  48.                 }
  49.             }
  50.             return list;
  51.         }
  52.     }
  53.  
  54.     /**
  55.      *
  56.      * @param id
  57.      * @return
  58.      * @throws SQLException
  59.      * @throws NamingException
  60.      */
  61.     public Account getAccount(String id) throws SQLException, NamingException {
  62.         Account a = new Account();
  63.         String query = " select " + DB_COLUMNS + " from " + DB_TABLE + " WHERE " + DB_ID + " = ? AND " + DB_STATUS + " ="+DB_STATUS_TRUE;
  64.         try (Connection conn = DBContext.makeConnection(); PreparedStatement ps = conn.prepareStatement(query);) {
  65.             //viet cau lenh query
  66.             ps.setString(1, id);
  67.             try (ResultSet rs = ps.executeQuery()) {
  68.                 if (rs.next()) {
  69.                     a = new Account(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
  70.                 }
  71.             }
  72.             return a;
  73.         }
  74.     }
  75.  
  76.     /**
  77.      *
  78.      * @param a
  79.      * @return
  80.      * @throws SQLException
  81.      * @throws NamingException
  82.      */
  83.     public int save(Account a) throws SQLException, NamingException {
  84.         int status;
  85.         String role = "1";
  86.         String query = "insert into " + DB_TABLE + "(" + DB_COLUMNS + ") values (?,?,?,?)";
  87.         try (Connection conn = DBContext.makeConnection(); PreparedStatement ps = conn.prepareStatement(query);) {
  88.             ps.setString(1, getRandomID());
  89.             ps.setString(2, a.getUsername());
  90.             ps.setString(3, a.getPassword());
  91.             ps.setString(4, role);
  92.             status = ps.executeUpdate();//execute query va nhan ket qua tra ve
  93.         }
  94.         return status;
  95.     }
  96.  
  97.     /**
  98.      *
  99.      * @param a
  100.      * @return
  101.      * @throws SQLException
  102.      * @throws NamingException
  103.      */
  104.     public int update(Account a) throws SQLException, NamingException {
  105.         int status;
  106.         String query = "update " + DB_TABLE + " set " + DB_UPDATE_COLUMNS + " where " + DB_ID + "=?";
  107.         try (Connection conn = DBContext.makeConnection(); PreparedStatement ps = conn.prepareStatement(query);) {
  108.             ps.setString(1, a.getUsername());
  109.             ps.setString(2, a.getPassword());
  110.             ps.setString(3, a.getRole());
  111.             ps.setString(4, a.getId());
  112.             status = ps.executeUpdate();//execute query va nhan ket qua tra ve
  113.         }
  114.         return status;
  115.     }
  116.  
  117.     /**
  118.      *
  119.      * @param id
  120.      * @return
  121.      * @throws SQLException
  122.      * @throws NamingException
  123.      */
  124.     public int delete(String id) throws SQLException, NamingException {
  125.         int status;
  126.         String query = "update " + DB_TABLE + " set " + DB_STATUS + " = "+DB_STATUS_FALSE+" where " + DB_ID + "=?";
  127.         try (Connection conn = DBContext.makeConnection(); PreparedStatement ps = conn.prepareStatement(query);) {
  128.             ps.setString(1, id);
  129.             status = ps.executeUpdate();//execute query va nhan ket qua tra ve
  130.         }
  131.         return status;
  132.     }
  133.  
  134.     /**
  135.      *
  136.      * @param range
  137.      * @param searchValue
  138.      * @return
  139.      * @throws SQLException
  140.      * @throws NamingException
  141.      */
  142.     public int count(int range, String searchValue) throws SQLException, NamingException {
  143.         int countResult = 0;
  144.         String query = "select count(*) as count from " + DB_TABLE + " where aUsername like ? AND " + DB_STATUS + "="+DB_STATUS_TRUE;
  145.         try (Connection conn = DBContext.makeConnection(); PreparedStatement ps = conn.prepareStatement(query);) {
  146.             ps.setString(1, "%" + searchValue + "%");
  147.             try (ResultSet rs = ps.executeQuery()) {
  148.  
  149.                 if (rs.next()) {
  150.                     int count = rs.getInt(1);
  151.                     int countPaging = count / range;
  152.                     if (count % range != 0) {
  153.                         countPaging++;
  154.                     }
  155.                     countResult = countPaging;
  156.                 }
  157.             }
  158.             return countResult;
  159.         }
  160.     }
  161.  
  162.     /**
  163.      *
  164.      * @param index
  165.      * @param range
  166.      * @param searchValue
  167.      * @return
  168.      * @throws SQLException
  169.      * @throws NamingException
  170.      */
  171.     public List<Account> getSearchingAccount(int index, int range, String searchValue) throws SQLException, NamingException {
  172.         List<Account> list = new ArrayList<>();//make list
  173.         String query = "select " + DB_COLUMNS + ",r from (SELECT ROW_NUMBER() over (order by " + DB_ID + " asc) as r, * from " + DB_TABLE + " where aUsername like ? and " + DB_STATUS + "= 1) as x "
  174.                 + "where (r between ((?*?)+1) - ? and (?)*?) ";
  175.         try (Connection conn = DBContext.makeConnection(); PreparedStatement ps = conn.prepareStatement(query);) {
  176.             if (index == 0) {
  177.                 index = 1;
  178.             }
  179.             ps.setString(1, "%" + searchValue + "%");
  180.             ps.setInt(2, index);
  181.             ps.setInt(3, range);
  182.             ps.setInt(4, range);
  183.             ps.setInt(5, index);
  184.             ps.setInt(6, range);
  185.             try (ResultSet rs = ps.executeQuery()) {
  186.                 while (rs.next()) {
  187.                     Account a = new Account(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
  188.                     list.add(a);
  189.                 }
  190.             }
  191.             return list;
  192.         }
  193.     }
  194.     public static final String DB_USERNAME = "aUsername";
  195.     public static final String DB_PASSWORD = "aPassword";
  196.  
  197.     public Account loginAccount(String username, String password) throws SQLException, NamingException {
  198.         Account a = new Account();
  199.         String query = "select " + DB_COLUMNS + " from " + DB_TABLE + " WHERE " + DB_USERNAME + " = ? AND " + DB_PASSWORD + " = ? AND " + DB_STATUS + "= "+DB_STATUS_TRUE;
  200.         try (Connection conn = DBContext.makeConnection(); PreparedStatement ps = conn.prepareStatement(query);) {
  201.             //viet cau lenh query
  202.             ps.setString(1, username);
  203.             ps.setString(2, password);
  204.             try (ResultSet rs = ps.executeQuery()) {
  205.                 if (rs.next()) {
  206.                     a = new Account(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
  207.                 }
  208.             }
  209.             return a;
  210.         }
  211.     }
  212.     Random random = new Random();
  213.  
  214.     public String getRandomID() throws SQLException, NamingException {
  215.         String id;
  216.         //getRandomString
  217.         int leftLimit = 48; // numeral '0'
  218.         int rightLimit = 122; // letter 'z'
  219.         int targetStringLength = 10;
  220.         String generatedString = random.ints(leftLimit, rightLimit + 1)
  221.                 .filter(i -> (i <= 57 || i >= 65) && (i <= 90 || i >= 97))
  222.                 .limit(targetStringLength)
  223.                 .collect(StringBuilder::new, StringBuilder::appendCodePoint, StringBuilder::append)
  224.                 .toString();
  225.         id = generatedString;
  226.         String query = "select " + DB_ID + " from " + DB_TABLE;
  227.         try (Connection conn = DBContext.makeConnection(); PreparedStatement ps = conn.prepareStatement(query);) {
  228.             try (ResultSet rs = ps.executeQuery()) {
  229.                 while (rs.next()) {
  230.                     String currentid = rs.getString(1);
  231.                     if (id.equals(currentid)) {
  232.                         id = getRandomID();
  233.                     }
  234.                 }
  235.             }
  236.             return id;
  237.         }
  238.     }
  239. }
  240.