LifeCenter/src/main/java/com/lemon/lifecenter/common/LifeCenterQueryLog.java

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) {
    }

    /**
     * <pre>
     * bindSql
     *
     * <pre>
     *
     * @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<ParameterMapping> 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<ParameterMapping> paramMapping = boundSql.getParameterMappings();
            Class<? extends Object> 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;
    }
}