/** 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); } }
/** * 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; }