헤르메스 LIFE

[Spring] Spring + MyBatis 에러처리 본문

Spring Framework

[Spring] Spring + MyBatis 에러처리

헤르메스의날개 2020. 12. 21. 00:35
728x90


개발환경

Spring 3.2.9

MyBatis 3.4.6

Microsoft SQL Server

 

REST환경을 구축하고, Insert 를 수행하던 중 중복오류 발생.

Exception 메시지가 정제되지 않고 리턴되었습니다.

### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: PRIMARY KEY 제약 조건 'USER_PK'을(를) 위반했습니다. 개체 'dbo.TB_USER'에 중복 키를 삽입할 수 없습니다. 중복 키 값은 (4)입니다.
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: /* UserSqlMapper - insert */ INSERT INTO TB_USER ( ID , USERNAME , PASSWORD , ROLE_ID ) VALUES ( ? , ? , ? , ? )
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: PRIMARY KEY 제약 조건 'USER_PK'을(를) 위반했습니다. 개체 'dbo.TB_USER'에 중복 키를 삽입할 수 없습니다. 중복 키 값은 (4)입니다. ;
SQL []; PRIMARY KEY 제약 조건 'USER_PK'을(를) 위반했습니다. 개체 'dbo.TB_USER'에 중복 키를 삽입할 수 없습니다. 중복 키 값은 (4)입니다.;
nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: PRIMARY KEY 제약 조건 'USER_PK'을(를) 위반했습니다. 개체 'dbo.TB_USER'에 중복 키를 삽입할 수 없습니다. 중복 키 값은 (4)입니다.

 

정제된 메시지를 만들기 위해 테스트한 내용을 정리하였습니다.

 

 

참고 : https://www.cnblogs.com/robbi/p/9182178.html

참고 : http://mybatis.org/spring/ko/getting-started.html

참고 : https://my.oschina.net/u/3787772/blog/2963134

참고 : https://www.hxstrive.com/article/439.htm

 

package common.util.mybatis;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.ibatis.exceptions.PersistenceException;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.MyBatisSystemException;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.support.PersistenceExceptionTranslator;
import org.springframework.jdbc.support.SQLExceptionTranslator;

public class MyBatisExceptionTranslator implements PersistenceExceptionTranslator {
	private final DataSource dataSource;

    private SQLExceptionTranslator exceptionTranslator;


    public MyBatisExceptionTranslator(SqlSessionFactory sqlSessionFactory) {
        this.dataSource = sqlSessionFactory.getConfiguration().getEnvironment().getDataSource();

        this.initExceptionTranslator();
    }

    /**
     * {@inheritDoc}
     */
    public DataAccessException translateExceptionIfPossible(RuntimeException e) {
        if (e instanceof PersistenceException) {
            // Batch exceptions come inside another PersistenceException
            // recursion has a risk of infinite loop so better make another if
            if (e.getCause() instanceof PersistenceException) {
                e = (PersistenceException) e.getCause();
            }
            if (e.getCause() instanceof SQLException) {
                this.initExceptionTranslator();
                return this.exceptionTranslator.translate(e.getMessage() + "\n", null, (SQLException) e.getCause());
            }
            return new MyBatisSystemException(e);
        }
        return null;
    }

    /**
     * Initializes the internal translator reference.
     */
    private synchronized void initExceptionTranslator() {
        if (this.exceptionTranslator == null) {
            // 정정을 위해 DacSQLErrorCodeSQLExceptionTranslator
            this.exceptionTranslator = new DacSQLErrorCodeSQLExceptionTranslator(this.dataSource);
        }
    }

}

 

package common.util.mybatis;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.MetaDataAccessException;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.jdbc.support.SQLErrorCodes;
import org.springframework.jdbc.support.SQLErrorCodesFactory;
import org.springframework.jdbc.support.SQLExceptionTranslator;

public class DacSQLErrorCodeSQLExceptionTranslator implements SQLExceptionTranslator {

    private final Logger logger = LoggerFactory.getLogger( this.getClass() );

	private DataSource dataSource;

	public DacSQLErrorCodeSQLExceptionTranslator(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public DataAccessException translate(String task, String sql, SQLException ex) {
		String dbName = null;
		try {
			dbName = (String) JdbcUtils.extractDatabaseMetaData(dataSource, "getDatabaseProductName");
			if (dbName != null) {
				if (logger.isDebugEnabled()) {
					logger.debug("Database product name cached for DataSource [" + dataSource.getClass().getName() + '@'
							+ Integer.toHexString(dataSource.hashCode()) + "]: name is '" + dbName + "'");
				}
			}
		} catch (MetaDataAccessException mdaEx) {
			logger.warn("Error while extracting database product name - falling back to empty error codes", mdaEx);
		}

		SQLErrorCodes sqlErrorCodes = (dbName == null) ? new SQLErrorCodes() : SQLErrorCodesFactory.getInstance().getErrorCodes(dbName);
		
		//logger.debug("ex.code :: {}", ex.getErrorCode());

		return new SQLErrorCodeSQLExceptionTranslator(sqlErrorCodes).translate(task, sql, ex);
	}
}

 

sqlSessionTemplateMs 부분의 constructor-arg 가 중요합니다.

 

sql-error-codes.xml 파일은 Classpath가 적용되는 위치에 있어야 합니다. 예) /WEB-INF/classes 폴더




    
        
             1054,1064,1146 
        
        
             1062 
        
        
             630,839,840,893,1169,1215,1216,1217,1364,1451,1452,1557 
        
        
             1 
        
        
             1205 
        
        
             1213 
        
    

   
        
            900,903,904,917,936,942,17006,6550
        
        
            17003
        
        
            1
        
        
            1400,1722,2291,2292
        
        
            17002,17447
        
        
            54,30006
        
        
            8177
        
        
            60
        
    
     
    
        
            1054,1064,1146
        
        
            1062
        
        
            630,839,840,893,1169,1215,1216,1217,1364,1451,1452,1557
        
        
            1
        
        
            1205
        
        
            1213
        
    
    
    
        
            MSSQL
        
        
             -007,-027,-097,-104,-109,-115,-128,-199,-204,-206,-301,-408,-441,-491 
        
        
             -803 
        
        
             -404,-530,-531,-532,-543,-544,-545,-603,-667 
        
        
             -907,-971 
        
        
             -1035,-1218,-30080,-30081 
        
    

    
        
            Apache Derby
        
        
            true
        
        
            42802,42821,42X01,42X02,42X03,42X04,42X05,42X06,42X07,42X08
        
        
            23505
        
        
            22001,22005,23502,23503,23513,X0Y32
        
        
            04501,08004,42Y07
        
        
            40XL1
        
        
            40001
        
    
 
    
        
            42000,42001,42101,42102,42111,42112,42121,42122,42132
        
        
            23001,23505
        
        
            22001,22003,22012,22018,22025,23000,23002,23003,23502,23503,23506,23507,23513
        
        
            90046,90100,90117,90121,90126
        
        
            50200
        
    
 
    
        
            HSQL Database Engine
        
        
            -22,-28
        
        
            -104
        
        
            -9
        
        
            -80
        
    
 
    
        
            Informix Dynamic Server
        
        
            -201,-217,-696
        
        
            -239,-268,-6017
        
        
            -692,-11030
        
    
 
    
        
            Microsoft SQL Server
        
        
            156,170,207,208,209
        
        
            229
        
        
            2601,2627
        
        
            544,8114,8115
        
        
            4060
        
        
            1222
        
        
            1205
        
    

    
        
            DB2*
        
        
            -007,-029,-097,-104,-109,-115,-128,-199,-204,-206,-301,-408,-441,-491
        
        
            -803
        
        
            -407,-530,-531,-532,-543,-544,-545,-603,-667
        
        
            -904,-971
        
        
            -1035,-1218,-30080,-30081
        
        
            -911,-913
        
    
    
    
        
            true
        
        
            03000,42000,42601,42602,42622,42804,42P01
        
        
            23505
        
        
            23000,23502,23503,23514
        
        
            53000,53100,53200,53300
        
        
            55P03
        
        
            40001
        
        
            40P01
        
    
 
    
        
            
                Sybase SQL Server
                SQL Server
                Adaptive Server Enterprise
                ASE 
                sql server 
            
        
        
            101,102,103,104,105,106,107,108,109,110,111,112,113,116,120,121,123,207,208,213,257,512
        
        
            2601,2615,2626
        
        
            233,511,515,530,547,2615,2714
        
        
            921,1105
        
        
            12205
        
        
            1205

 

package rest.dao;

import java.util.List;

import org.mybatis.spring.SqlSessionTemplate;

import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Component;

import common.dao.BaseMsDao;
import java.util.Map;

@Component
public class SimpleRestDao {
	SqlSessionTemplate getTemplate() ;

	/* test 조회 */
	public List selectList(Object param) {
		return getTemplate().getList("ezsmart.tbUser.select", param);
	}

	/* test insert */
	public int insert(Object param) {
		return getTemplate().insert("ezsmart.tbUser.insert", param);
	}

	/* test update */
	public int update(Object param) {
		return getTemplate().update("ezsmart.tbUser.update", param);
	}

	/* test delete */
	public int delete(Object param) {
		return getTemplate().delete("ezsmart.tbUser.delete", param);
	}

}

 

package rest.controller;

import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.CannotAcquireLockException;
import org.springframework.dao.CannotSerializeTransactionException;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.dao.DeadlockLoserDataAccessException;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.InvalidResultSetAccessException;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.ResponseBody;

import com.microsoft.sqlserver.jdbc.SQLServerException;

@Controller
@ControllerAdvice
public class ErrorController {
	private Logger logger = LoggerFactory.getLogger(ErrorController.class);

	@ExceptionHandler(DataAccessException.class)
	public @ResponseBody Map keyError(DataAccessException ex, HttpServletRequest request, HttpServletResponse response)
			throws Exception {

		SQLException se = (SQLException) ((DataAccessException) ex.getRootCause();
		logger.debug("****** DataAccessException : {} // {}", se.getErrorCode(), se.getMessage());

		String errCode = "";
		String errMsg = "";

		if (ex instanceof BadSqlGrammarException) {

			SQLException se = ((BadSqlGrammarException) ex).getSQLException();

			logger.debug("**BadSqlGrammarException {} ", se.getErrorCode());

		} else if (ex instanceof InvalidResultSetAccessException) {

			SQLException se = ((InvalidResultSetAccessException) ex).getSQLException();

			logger.debug("**InvalidResultSetAccessException {} ", se.getErrorCode());

		} else if (ex instanceof DuplicateKeyException) {

			logger.debug("**DuplicateKeyException {} ", ex.getMessage());

		} else if (ex instanceof DataIntegrityViolationException) {
			// 고유성 제한 위반과 같은 데이터 삽입 또는 업데이트시 무결성 위반
			logger.debug("**DataIntegrityViolationException {} ", ex.getMessage());
			errCode = "1";
			errMsg = "데이터 중복오류";
		} else if (ex instanceof DataAccessResourceFailureException) {
			// 데이터 액세스 리소스가 완전히 실패했습니다 (예 : 데이터베이스에 연결할 수 없음)
			logger.debug("**DataAccessResourceFailureException {} ", ex.getMessage());
			errCode = "1";
			errMsg = "데이터베이스 연결오류";
		} else if (ex instanceof CannotAcquireLockException) {

			logger.debug("**CannotAcquireLockException {} ", ex.getMessage());

		} else if (ex instanceof DeadlockLoserDataAccessException) {
			// 교착 상태로 인해 현재 작업이 실패했습니다.
			logger.debug("**DeadlockLoserDataAccessException {} ", ex.getMessage());
			errCode = "1";
			errMsg = "교착 상태로 인한 현재 작업 실패";
		} else if (ex instanceof CannotSerializeTransactionException) {

			logger.debug("**CannotSerializeTransactionException {} ", ex.getMessage());

		} else {
			errMsg = ex.getMessage();
			logger.error("[DataAccessException] getMessage {}", ex.getMessage());
		}
        
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("errCode", errCode);
		map.put("errMsg", errMsg);

		return map;
	}

	@ExceptionHandler(Exception.class)
	public @ResponseBody void keyError(Exception ex, HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		logger.error("[Exception] {}", ex.getMessage());
		response.sendError(500);
	}
}

 

 

 

 

728x90

'Spring Framework' 카테고리의 다른 글

[Spring] Spring MVC의 라이프사이클  (0) 2021.01.09
[SpringBoot] 스프링 부트 스타터 프로젝트 옵션들  (0) 2021.01.03
[Spring] log4jdbc-remix 를 이용한 쿼리 로그 출력하기  (0) 2020.12.06
[전자정부프레임워크] EgovPropertyServiceImpl  (0) 2020.11.12
Spring Security on REST API 구축 실패기...!!!  (0) 2016.08.24
'Spring Framework' Related Articles more