/**
  * This method execute query on azure SQL user table to get the user details by input user id.
  *
  * @param userId
  * @return UserEntity
  * @author rupesh_shirude
  */
 @Override
 public UserEntity getUserDetailsByUserId(Integer userId) throws Exception {
   LOGGER.info("[UserDAOImpl][getUserDetailsByUserId] start ");
   UserEntity user = null;
   Connection connection = null;
   PreparedStatement preparedStatement = null;
   ResultSet resultSet = null;
   try {
     connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
     preparedStatement = connection.prepareStatement(AzureChatSQLConstants.GET_USER_BY_USERID);
     preparedStatement.setInt(1, userId);
     resultSet = preparedStatement.executeQuery();
     if (resultSet.next()) {
       user = generateUserObject(resultSet);
     }
   } catch (Exception e) {
     LOGGER.error(
         "Exception occurred while executing get user details query on azure SQL database. Exception Message : "
             + e.getMessage());
     throw new AzureChatSystemException(
         "Exception occurred while executing get user details query on azure SQL database. Exception Message : "
             + e.getMessage());
   } finally {
     AzureChatUtils.closeDatabaseResources(preparedStatement, resultSet, connection);
   }
   LOGGER.info("[UserDAOImpl][getUserDetailsByUserId] end ");
   return user;
 }
 /**
  * This method executes adds new user query on the azure SQL user table.
  *
  * @return userEntity
  */
 public UserEntity saveNewUser(UserEntity user) throws Exception {
   LOGGER.info("[UserDAOImpl][saveNewUser] start ");
   int userId = 0;
   Connection connection = null;
   PreparedStatement preparedStatement = null;
   ResultSet resultSet = null;
   try {
     connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
     preparedStatement =
         connection.prepareStatement(
             AzureChatSQLConstants.SAVE_NEW_USER, Statement.RETURN_GENERATED_KEYS);
     preparedStatement = generatePreparedStatement(preparedStatement, user);
     preparedStatement.executeUpdate();
     resultSet = preparedStatement.getGeneratedKeys();
     if (resultSet.next()) {
       userId = resultSet.getInt(1);
     }
   } catch (Exception e) {
     LOGGER.error(
         "Exception occurred while executing save user query on azure SQL table. Exception Message : "
             + e.getMessage());
     throw new AzureChatSystemException(
         "Exception occurred while executing save user query on azure SQL table. Exception Message : "
             + e.getMessage());
   } finally {
     AzureChatUtils.closeDatabaseResources(preparedStatement, resultSet, connection);
   }
   user.setUserID(userId);
   LOGGER.info("[UserDAOImpl][saveNewUser] end ");
   return user;
 }
 /**
  * This method executes the get user photo blob URL query on the azure SQL user table for input
  * user id.
  *
  * @param userId
  * @return profileImageURL
  */
 @Override
 public String getUserPhotoBlobURL(Integer userId) throws Exception {
   String profileImageURL = AzureChatConstants.CONSTANT_EMPTY_STRING;
   Connection connection = null;
   PreparedStatement preparedStatement = null;
   ResultSet resultSet = null;
   try {
     connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
     preparedStatement =
         connection.prepareStatement(AzureChatSQLConstants.GET_USER_PROFILE_URL_BY_USERID);
     preparedStatement.setInt(1, userId);
     resultSet = preparedStatement.executeQuery();
     if (resultSet.next()) {
       profileImageURL = resultSet.getString(1);
     }
   } catch (Exception e) {
     LOGGER.error(
         "Exception occurred while executing get user profile image URL on azure SQL user table. Exception MEssage : "
             + e.getMessage());
     throw new AzureChatSystemException(
         "Exception occurred while executing get user profile image URL on azure SQL user table. Exception MEssage : "
             + e.getMessage());
   } finally {
     AzureChatUtils.closeDatabaseResources(preparedStatement, resultSet, connection);
   }
   return profileImageURL;
 }
 /**
  * This method executes the update user details query in the azure SQL database for the input user
  * entity.
  */
 @Override
 public UserEntity updateNewUser(UserEntity user) throws Exception {
   LOGGER.info("[UserDAOImpl][updateNewUser] start ");
   Connection connection = null;
   PreparedStatement preparedStatement = null;
   ResultSet resultSet = null;
   try {
     connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
     preparedStatement = connection.prepareStatement(AzureChatSQLConstants.UPDATE_NEW_USER);
     preparedStatement.setString(1, user.getFirstName());
     preparedStatement.setString(2, user.getLastName());
     preparedStatement.setString(3, user.getPhotoBlobUrl());
     preparedStatement.setString(4, user.getEmailAddress());
     preparedStatement.setInt(5, user.getPhoneCountryCode());
     preparedStatement.setLong(6, user.getPhoneNumber());
     preparedStatement.setString(7, user.getNameId());
     preparedStatement.executeUpdate();
   } catch (Exception e) {
     LOGGER.error(
         "Exception occurred while executing update user query on the azure SQL table. Exception Message : "
             + e.getMessage());
     throw new AzureChatSystemException(
         "Exception occurred while executing update user query on the azure SQL table. Exception Message : "
             + e.getMessage());
   } finally {
     AzureChatUtils.closeDatabaseResources(preparedStatement, resultSet, connection);
   }
   LOGGER.info("[UserDAOImpl][updateNewUser] end ");
   return user;
 }
 /**
  * This method executes query on azure SQL user table to get the user details by first or last
  * name
  *
  * @param name
  * @return userEntities
  */
 @Override
 public List<UserEntity> getUserDetailsByFirstNameOrLastName(String name) throws Exception {
   LOGGER.info("[UserDAOImpl][getUserDetailsByFirstNameOrLastName] start ");
   List<UserEntity> userEntities = new ArrayList<UserEntity>();
   Connection connection = null;
   PreparedStatement preparedStatement = null;
   ResultSet resultSet = null;
   try {
     connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
     preparedStatement =
         connection.prepareStatement(AzureChatSQLConstants.GET_USER_BY_FIRST_LAST_NAME);
     preparedStatement.setString(1, name + AzureChatConstants.CONSTANT_PERCENTAGE);
     preparedStatement.setString(2, name + AzureChatConstants.CONSTANT_PERCENTAGE);
     resultSet = preparedStatement.executeQuery();
     while (resultSet.next()) {
       userEntities.add(generateUserObject(resultSet));
     }
   } catch (Exception e) {
     LOGGER.error(
         "Exception occurred while executing get user details query on azure SQL database. Exception Message : "
             + e.getMessage());
     throw new AzureChatSystemException(
         "Exception occurred while executing get user details query on azure SQL database. Exception Message : "
             + e.getMessage());
   } finally {
     AzureChatUtils.closeDatabaseResources(preparedStatement, resultSet, connection);
   }
   LOGGER.info("[UserDAOImpl][getUserDetailsByFirstNameOrLastName] end ");
   return userEntities;
 }
 /** This method executes create user table query on the azure SQL database. */
 @Override
 public void createUserTable() throws Exception {
   Connection connection = null;
   Connection connection1 = null;
   PreparedStatement preparedStatement = null;
   PreparedStatement preparedStatement1 = null;
   try {
     connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
     preparedStatement = connection.prepareStatement(AzureChatSQLConstants.CREATE_USER_TABLE);
     preparedStatement.execute();
     connection1 = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
     preparedStatement1 =
         connection1.prepareStatement(AzureChatSQLConstants.CREATE_USER_TABLE_INDEX);
     preparedStatement1.execute();
   } catch (Exception e) {
     LOGGER.error(
         "Exception occurred while executing create user table query on the azure SQL database. Exception Message : "
             + e.getMessage());
     throw new AzureChatSystemException(
         "Exception occurred while executing create user table query on the azure SQL database. Exception Message : "
             + e.getMessage());
   } finally {
     AzureChatUtils.closeDatabaseResources(preparedStatement, connection);
     AzureChatUtils.closeDatabaseResources(preparedStatement1, connection1);
   }
 }