执行长时间SQL

场景描述

大多数SQL执行时间短，个别SQL可能执行时间长。不调整连接参数，长时间SQL执行超时，调整连接参数会导致普通出错情况等待时间长。

实现思路

对于这种情况，可以单独创建一个用于执行长时间SQL的连接，使用这个连接执行长时间SQL。

实现步骤

1 创建长时间数据库配置类

在src/main/java/main/common/config下创建DatasourceConfig.java，完整代码下面附上，需要说明如下几点

• 定义Bean的名字，以便使用

定义为longMainDataSource，代码如下

@Bean(name = "longMainDataSource", initMethod = "init", destroyMethod = "close")
public DruidDataSource longMainDataSource() {

在service中使用，代码如下

@Autowired
@Qualifier("longMainDataSource")
private DataSource longMainDatasource;

• 设置模块名，模块名是环境变量的前缀，以便正确获取环境变量

在main模块中，所以设置为main，环境变量名称为大写，代码如下

private String envPrefix = "MAIN";
private String archServiceName = "main";

• 设置连接参数

在createDataSoruce方法中设置了连接参数，可以按需修改，代码如下

private DruidDataSource createDataSoruce() {
        datasource.setMaxActive(3);//只允许三个并发
        datasource.setMaxWait(5000);
        datasource.setTimeBetweenEvictionRunsMillis(60000 * 10 * 6);
        datasource.setMinEvictableIdleTimeMillis(220000);
        datasource.setMaxEvictableIdleTimeMillis(300000);
        datasource.setConnectionErrorRetryAttempts(1);
        datasource.setTimeBetweenConnectErrorMillis(10000);    
}

为了和标准连接区分开，在url上需要添加一个参数

这一点非常关键，本例添加了useLong参数，用来和标准连接区分开，代码如下

url+= "useLong=true"; //添加useLong参数, 和标准的连接区分开

数据库配置类完整代码如下

package main.common.config;

import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import org.dom4j.Element;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.pool.vendor.MySqlValidConnectionChecker;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.justep.service.config.api.ServiceConfig;
import com.justep.service.config.api.ServiceConfigFactory;
import com.justep.util.db.DatabaseType;
import com.justep.util.db.JDBCUrl;

@Configuration
public class DatasourceConfig {
    private static final Logger logger = LoggerFactory.getLogger(DatasourceConfig.class);

    class DBResource {
        private String url;
        private String dialect;
        private String username;
        private String password;
        private String driverClassName;
        private String dbType;
        private String dbName;
        private String properties;
        private String dbHost;
        private String dbPort;
        private String dbSchema;
        private String validateSql;
        private String shardingConfig;
        private Properties connProp;

        private Element root;
        private boolean fromEnv = false;
        private boolean throughDbProxy = true;

        public DBResource() throws Exception {
            ServiceConfig config = ServiceConfigFactory.get(archServiceName,"maven");
            dbHost = config.getVar(envPrefix + "_DBHOST");
            if (StringUtils.isBlank(dbHost)) {
                throw new IllegalStateException("服务未定义数据库参数: " + archServiceName);
            } else {
                dbType = config.getVar(envPrefix + "_DBTYPE");
                dbName = config.getVar(envPrefix + "_DBNAME");
                dbPort = config.getVar(envPrefix + "_DBPORT");
                dbSchema = config.getVar(envPrefix + "_DBSCHEMA");
                username = config.getVar(envPrefix + "_DBUSER");
                password = config.getVar(envPrefix + "_DBPASSWD");
                properties = config.getProp(envPrefix + "_DBPROPERTIES");
                shardingConfig = config.getVar(envPrefix + "_DBEXTSHARDING");
                fromEnv = true;
                String envUseDbProxy = config.getVar("COMP_DBPROXY_USEDBPROXY");
                if (envUseDbProxy != null && (envUseDbProxy.equalsIgnoreCase("true") || envUseDbProxy.equalsIgnoreCase("yes") || envUseDbProxy.equalsIgnoreCase("1"))) {
                    throughDbProxy = true;
                } else {
                    throughDbProxy = false;
                }

            }
            init();
            logger.info("db resource url from env {} : {}", fromEnv, url);
        }

        private void init() throws Exception {
            if (fromEnv) {
                connProp = genConnProp();
                url = genJDBCUrl();
                DatabaseType dt = DatabaseType.getDatabaseType(url);
                validateSql = DatabaseType.getValidationSql(dt);
                driverClassName = dt.getDriverClass();
                dialect = dt.getDialect();
                if (!StringUtils.isEmpty(shardingConfig)) {
                    url += DatabaseType.getItemSeparator(dt) + "shardingConfig=" + shardingConfig;
                }
                if (useDbProxy()) {
                    driverClassName = "com.justep.dbproxy.ProxyDriver";
                }
            } else {
                throw new IllegalStateException("服务未定义数据库参数: " + archServiceName);
            }
        }
        public boolean useDbProxy() {
            if (url.indexOf("dbproxy=false") < 0 && throughDbProxy) {
                return true;
            }
            return false;
        }
        private Properties genConnProp() {
            Properties ret = new Properties();
            if (dbType.contains("oracle")) {
                ret.setProperty("oracle.jdbc.timezoneAsRegion", "false");
            }
            return ret;
        }

        private String genJDBCUrl() {
            JDBCUrl jdbcUrl = new JDBCUrl(DatabaseType.parse(dbType), dbHost, dbPort, dbName, username, password, dbSchema, properties);
            jdbcUrl.setWithUserPassword(false);
            return jdbcUrl.jdbcUrl();
        }

        public String getUrl() {
            return url;
        }

        public void setUrl(String url) {
            this.url = url;
        }

        public String getDialect() {
            return dialect;
        }

        public void setDialect(String dialect) {
            this.dialect = dialect;
        }

        public String getUsername() {
            return username;
        }

        public void setUsername(String username) {
            this.username = username;
        }

        public String getPassword() {
            return password;
        }

        public void setPassword(String password) {
            this.password = password;
        }

        public String getDriverClassName() {
            return driverClassName;
        }

        public void setDriverClassName(String driverClassName) {
            this.driverClassName = driverClassName;
        }
        public String getValidateSql() {
            return validateSql;
        }

        public Properties getConnProp() {
            return connProp;
        }
    }


    private String envPrefix = "MAIN";
    private String archServiceName = "main";
    private DBResource resource;
    public DatasourceConfig() throws Exception {
        resource = new DBResource();
    }

    @Bean(name = "longMainDataSource", initMethod = "init", destroyMethod = "close")
    public DruidDataSource longMainDataSource() {
        logger.info("creating longMainDataSource");
        DruidDataSource datasource = createDataSoruce();
        return datasource;
    }

    private DruidDataSource createDataSoruce() {
        try{
            Map<String, String> map = new HashMap<String, String>();
            map.put("init", "false");
            DruidDataSource datasource = (DruidDataSource) DruidDataSourceFactory.createDataSource(map);
            String url = resource.getUrl();
            url += (url.contains("?")) ? "&" : "?";
            url+= "useLong=true"; //添加useLong参数, 和标准的连接区分开
            datasource.setUrl(url);
            datasource.setUsername(resource.getUsername());
            datasource.setPassword(resource.getPassword());
            datasource.setDriverClassName(resource.getDriverClassName());
            if (resource.useDbProxy()) {
                datasource.setValidConnectionChecker(new MySqlValidConnectionChecker());
            }
            // configuration
            datasource.setInitialSize(1);
            datasource.setMinIdle(1);
            datasource.setMaxActive(3);//只允许三个并发
            datasource.setMaxWait(5000);
            datasource.setTimeBetweenEvictionRunsMillis(60000 * 10 * 6);
            datasource.setMinEvictableIdleTimeMillis(220000);
            datasource.setMaxEvictableIdleTimeMillis(300000);
            datasource.setTestWhileIdle(true);
            datasource.setTestOnBorrow(false);
            datasource.setTestOnReturn(false);
            datasource.setPoolPreparedStatements(false);
            datasource.setValidationQuery(resource.getValidateSql());            
            datasource.setConnectionErrorRetryAttempts(1);
            datasource.setInitExceptionThrow(false);
            datasource.setTimeBetweenConnectErrorMillis(10000);    
            datasource.setConnectProperties(resource.getConnProp());
            datasource.init();            
            return datasource;
        }catch(Exception ex){
            logger.error("datasource error",ex);
            throw new RuntimeException(ex);
        }
    }
}

2 使用长时间连接执行SQL

在service中注入上面创建的长时间数据库配置类，使用它创建连接，执行SQL，完整代码如下

package main.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

@Service
public class FuwuUserService {

    //自动注入长时间数据库配置类
    @Autowired
    @Qualifier("longMainDataSource")
    private DataSource longMainDatasource;

    public String zhangshijsql() throws Exception {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "select * from orgs";
        try {
            //使用长时间数据库配置类创建连接
            con = longMainDatasource.getConnection();
            //执行SQL
            stmt = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery();
            int count = 0;
            if (rs.first()) {
                  rs.last();  
                  count = rs.getRow();  
            }
            return "count=" + count;
        }catch(Exception e) {
            throw new RuntimeException("" + e.getMessage(), e);
        }finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            }catch(Exception e) {                    
            }
            try {
                if (rs != null) {
                    rs.close();
                }
            }catch(Exception e) {                    
            }
        }
    }    
}