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