How to handle exception while adding duplicate username to a database in spring mvc application

Question

I am building spring mvc application. I want to dissalow adding duplicate(username is primary key) entry and inform in view to choose another username.

I am working on register user option. Everything works until I put user with username that already exist. Well, It was deliberate because username column is a primary key in my database.

I am looking for an option to handle this so:

This is my SQL table

 create table users(
  username varchar(50) not null primary key,
  password varchar(50) not null);

This is my repository:

@Repository

class UserRepositoryImpl implements UserRepository{

    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Override
    public void addUser(User user) throws MySQLIntegrityConstraintViolationException {
        /*language=SQL*/
        String SQL_ADD_USER= "INSERT INTO users VALUES (?,?)";

        String username  = user.getUsername();
        String password  = user.getPassword();
        jdbcTemplate.update(SQL_ADD_USER, username, password);

    }
}

and this is part of my Controller class:

@RequestMapping(value="/register", method = RequestMethod.GET)
public String registerPage(@ModelAttribute("user") User user){return "register";}

@RequestMapping(value="/register", method=RequestMethod.POST)
public String processRegisterUser(@ModelAttribute("user") User user, BindingResult result){

    try {
        userRepository.addUser(user);
    } catch (com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException e) {

        //some line of code that would add information to the view that user with given username already exists.
    }


    return "redirect:/login";
}

As you can see I tried to handle an exception that occurs during adding duplicate entry. This is why I put this clause throws MySQLIntegrityConstraintViolationException in public void addUser(User user). In the next step I wanted to handle this exception in public String processRegisterUser method so that I could inform user in view that given username is occupied. Well, It doesn't work. I can't handle com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException and during adding duplicate error instead of warning in view I get:

 HTTP Status 500 - Request processing failed; nested exception is org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT INTO users VALUES (?,?)]; Duplicate entry 'user' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'user' for key 'PRIMARY'

type Exception report

message Request processing failed; nested exception is org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT INTO users VALUES (?,?)]; Duplicate entry 'user' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'user' for key 'PRIMARY'

description The server encountered an internal error that prevented it from fulfilling this request.

exception

    org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT INTO users VALUES (?,?)]; Duplicate entry 'user' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'user' for key 'PRIMARY'
        org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:973)
        org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
        org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
        org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
        org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
        org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:115)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)

If you need any details please comment.

Answer 1

I would solve the problem by catching the exception (to avoid race conditions) and then I would add an error to the BindingResult so that I would be able to display the error in the (JSP/Thymeleaf) view template.

@Controller
public class UserController {

  private final UserService userService;

  public UserController(UserService userService) {

    this.userService = userService;
  }

  /**
   * Adds new a user and redirect to users if there are no errors otherwise return add user template.
   */
  @PostMapping("/admin/add_user")
  public String postUser(Model model, @Valid UserForm userForm, BindingResult result) {

    if (result.hasErrors()) {
      // Maybe add attributes to the model here. Do not redirect when there are errors.

      return "add_user";
    }

    try {
      userService.createUser(
          User.withUsername(userForm.getUsername()).password(userForm.getPassword()).roles("ADMIN").build());
    } catch (final DuplicateKeyException e) {
      result.rejectValue("username", "usernameInUse", "Username is already in use");

      return postUser(model, userForm, result);
    }

    // Redirect after post if there are no errors
    return "redirect:/admin/users";
  }

}

The user form contains standard jsr 303 validation annotations.

@Data
public class UserForm {

  @Length(min = 1, max = 50)
  @NotEmpty
  private String username;

  @NotEmpty
  private String password;
}

Answer 2

Try Query.uniqueResult() as decribed in this article or else

public Account getAccountByAccountIdAndType(Long accountId, AccountType accountType) {

    Account account = null;
    try {
        Query query = getSession().getNamedQuery("getAccountByAccountId");
        query.setLong("accountId", accountId); 
        query.setString("accountType", AccountType.SAVING.toString());
        Account account = (Account)Query.uniqueResult();
    } catch(NonUniqueResultException) {
        throw new RuntimeException("Two account found with same account number and type : Acc No-" + accountId);
    }
    if (account == null) {
         throw new RuntimeException("Unable to find Account for account number :" + accountId);
    }
    return account;
}

Entity class

@NamedQuery(
name = "getAccountByAccountId",
query = "from Account where username = :username")
@Entity
@Table(name = "account")
public class Account {

Answer 3

You're using spring jdbcTemplate for db connection and it has his own exception hierarchy, so you can try to catch the spring exception org.springframework.dao.DuplicateKeyException

Answer 4

You are handling only MySQLIntegrityConstraintViolationException not other exceptions: NestedServletException and DuplicateKeyException and hence you are getting stacktrace and your try/catch is not working.

BTW, Why not simply create an extra method to check if the username is already present and if yes then display error message otherwise add user.

class UserRepositoryImpl implements UserRepository{
    //.....
    public int isUsernameExist(String username){
        String sql = "SELECT COUNT(*) FROM users WHERE username=?";
        return jdbcTemplate.queryForObject(sql, new Object[] { username }, String.class);
    }
    //....
}

@RequestMapping(value="/register", method=RequestMethod.POST)
public String processRegisterUser(@ModelAttribute("user") User user, BindingResult result){
    int status = userRepository.isUserExist(user.getUsername());
    if(status==1){
        //Username exist... redirect and display error msg.
    } else {
        userRepository.addUser(user);
    }
    //.....
}