How to handle exception while adding duplicate use

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.

回答1:

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

回答2:

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

回答3:

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 {