package com.lemon.lifecenter.common; import java.lang.reflect.Field; import java.sql.Statement; import java.util.List; import java.util.Map; import java.util.Properties; import java.util.regex.Matcher; import javax.servlet.http.HttpServletRequest; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.ParameterMapping; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Plugin; import org.apache.ibatis.plugin.Signature; import org.apache.ibatis.session.ResultHandler; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.context.request.RequestContextHolder; import org.springframework.web.context.request.ServletRequestAttributes; import com.lemon.lifecenter.dto.PrivateLogDTO; import com.lemon.lifecenter.service.PrivateLogService; import ch.qos.logback.classic.Logger; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.sql.*; import cubrid.sql.*; //a import cubrid.jdbc.driver.*; /* @Intercepts(@Signature( type=Executor.class, method="query", args= {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})) */ @Intercepts({ @Signature(type = StatementHandler.class, method = "update", args = { Statement.class }), @Signature(type = StatementHandler.class, method = "query", args = { Statement.class, ResultHandler.class }) }) public class LifeCenterQueryLog implements Interceptor { private final Logger logger = (Logger) LoggerFactory.getLogger(this.getClass()); private Connection con; private Statement stmt; @Override public Object intercept(Invocation invocation) throws Throwable { String method = invocation.getMethod().getName(); StatementHandler handler = (StatementHandler) invocation.getTarget(); HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder .getRequestAttributes()).getRequest(); String sql = bindSql(handler); // SQL 추출 String param = handler.getParameterHandler().getParameterObject()!=null ? handler.getParameterHandler().getParameterObject().toString() : ""; Object session = request.getSession().getAttribute( "sesId" ); String url = request.getRequestURI().toString(); // 환자관리, 진료관리, 의료진관리 if( url.contains( "/patient/" ) || url.contains( "/clinic/" ) || url.contains( "/staff/" ) ) { if( !url.contains( "/statistics/patient/" ) ) { if( session != null && !url.contains( "/mobile" ) ) { if( !param.contains( "com.lemon.lifecenter.dto.LoginDTO" ) ) { if( !sql.contains( "-- selectNowPathRoleCheckTotal" ) && !sql.contains( "-- selectNowPathRoleCheckData" ) && !sql.contains( "-- insertMenuAccessLog" ) && !sql.contains( "-- selectSideMenuList" ) && !sql.contains( "-- insertPrivateAccessLog" ) ) { //System.err.println("★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★"); //System.err.println(sql); try { //application.properties 에서 값을 들고옴 String durl = PropertyUtil.getProperty("spring.datasource.url"); String user = PropertyUtil.getProperty("spring.datasource.username"); String passwd = PropertyUtil.getProperty("spring.datasource.password"); con = DriverManager.getConnection(durl, user, passwd); // System.out.println("DB연결 성공"); stmt = con.createStatement(); // System.out.println("Statement객체 생성 성공"); String sesName = LifeCenterSessionController.getSession( request, "sesName" ); String sesCenterName = LifeCenterSessionController.getSession( request, "sesCenterName" ); String logCenterName = !sesCenterName.equals( "" )? "["+sesCenterName+"] " : ""; String sesId = LifeCenterSessionController.getSession( request, "sesId" ); String insertQuery = "" + "INSERT " + " INTO private_access_log " + " ( id, accessor_detail, ip, create_date, full_url, processing_contents, processing_target ) " + "VALUES ( ?, ?, ?, NOW(), ?, ?, '')"; PreparedStatement stmt = con.prepareStatement( insertQuery ); stmt.setString(1, sesId ); stmt.setString(2, logCenterName + sesName + " (" + sesId + ")" ); stmt.setString(3, LifeCenterFunction.getRemoteAddr( request )); stmt.setString(4, LifeCenterFunction.getFullURL( request ) ); stmt.setString(5, sql); stmt.executeUpdate(); stmt.close(); con.close(); } catch (SQLException e) { System.out.println("DB연결 실패"); System.out.print("사유 : " + e.getMessage()); } //System.err.println("★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★"); } } } } } return invocation.proceed(); } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { } /** * 
     * bindSql
     *
     * 
     *
     * @param boundSql
     * @param sql
     * @param param
     * @return
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     */
    @SuppressWarnings("rawtypes")
    private String bindSql(StatementHandler handler) throws NoSuchFieldException, IllegalAccessException {
        BoundSql boundSql = handler.getBoundSql();

        // 쿼리실행시 맵핑되는 파라미터를 구한다
        Object param = handler.getParameterHandler().getParameterObject();
        // 쿼리문을 가져온다(이 상태에서의 쿼리는 값이 들어갈 부분에 ?가 있다)
        String sql = boundSql.getSql();

        // 바인딩 파라미터가 없으면
        if (param == null) {
            sql = sql.replaceFirst("\\?", "''");
            return sql;
        }

        // 해당 파라미터의 클래스가 Integer, Long, Float, Double 클래스일 경우
        if (param instanceof Integer || param instanceof Long || param instanceof Float || param instanceof Double) {
            sql = sql.replaceFirst("\\?", param.toString());
        }
        // 해당 파라미터의 클래스가 String인 경우
        else if (param instanceof String) {
            sql = sql.replaceFirst("\\?", "'" + param + "'");
        }
        // 해당 파라미터의 클래스가 Map인 경우
        else if (param instanceof Map) {
            List paramMapping = boundSql.getParameterMappings();
            for (ParameterMapping mapping : paramMapping) {
                String propValue = mapping.getProperty();
                Object value = ((Map) param).get(propValue);
                if (value == null) {
                    continue;
                }

                if (value instanceof String) {
//                    sql = sql.replaceFirst("\\?", "'" + value + "'");
                    sql = sql.replaceFirst("\\?", "'"+Matcher.quoteReplacement(value.toString())+"'");
                } else {
                    sql = sql.replaceFirst("\\?", value.toString());
                }
            }
        }
        // 해당 파라미터의 클래스가 사용자 정의 클래스인 경우
        else {
            List paramMapping = boundSql.getParameterMappings();
            Class paramClass = param.getClass();

            for (ParameterMapping mapping : paramMapping) {
                String propValue = mapping.getProperty();
                Field field = paramClass.getDeclaredField(propValue);
                field.setAccessible(true);
                Class javaType = mapping.getJavaType();
                
                if (String.class == javaType) {
                    String str = field.get(param) == null ? "" : field.get(param).toString();
                    sql = sql.replaceFirst("\\?", "'"+Matcher.quoteReplacement(str)+"'");
                } else {
                    sql = sql.replaceFirst("\\?", field.get(param).toString());
                }
            }
        }

        // return sql
        return sql;
    }
}