普通java项目使用阿里巴巴druid连接池-数据处理

需要注意的是

execute()既能执行修改表中数据，又可以执行创建表，删除表，创建数据库，删除数据库等操作 excuteUpdate()针对于修改更新表中的数据，返回int类型的值，存储修改的行数

使用代码

package com.test;

/**
 * @Author:jeff
 * @Date: 2022/11/16
 * @Desc
 */
public class Test {

    public static void main(String[] args) throws Exception {
        DruidDemo druidDemo=new  DruidDemo();
        druidDemo.executeFuncAndSql("select * from alert_dataset limit 1");
    }
}

普通工具类

package com.test;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.google.protobuf.ServiceException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
 * @Author:jeff
 * @Date: 2022/11/16
 * @Desc
 */
class DruidDemo {

    private static final Logger logger = LoggerFactory.getLogger(DruidDemo.class);

    private DataSource ds;

    private static final int LIMIT = 10000;

    private int taskTimeout=20000;

    public DruidDemo() throws Exception {
        Properties pro = new Properties();
        InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("jdbc.properties");
        pro.load(is);

        //获取连接池对象
        ds = DruidDataSourceFactory.createDataSource(pro);
        if (ds == null) {
            throw new Exception("数据源不存在！");
        }
    }



    public void executeFuncAndSql(String mainSql){
        Connection connection = null;
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        try {
            // create connection
            connection = ds.getConnection();
            // pre sql
            stmt = prepareStatementAndBind(connection, mainSql);
            //查询
            resultSet = stmt.executeQuery();
            resultProcess(resultSet);
//            删除
//           int i= stmt.executeUpdate();
        } catch (Exception e) {
            logger.error("execute sql error",e);
            throw new RuntimeException("execute sql error");
        } finally {
            close(resultSet,stmt,connection);
        }
    }

    /**
     * preparedStatement bind
     * @param connection
     * @return
     * @throws Exception
     */
    private PreparedStatement prepareStatementAndBind(Connection connection, String mainSql) throws Exception {
        // is the timeout set
        PreparedStatement stmt = connection.prepareStatement(mainSql);
        if(0!=taskTimeout){
            stmt.setQueryTimeout(taskTimeout);
        }
        logger.info("prepare statement replace sql : {} ", stmt);
        return stmt;
    }

    /**
     * result process
     *
     * @param resultSet resultSet
     * @throws Exception
     */
    private void resultProcess(ResultSet resultSet) throws Exception{
        List<Map<String,Object>> resultMapList = new ArrayList<>();
        ResultSetMetaData md = resultSet.getMetaData();
        int num = md.getColumnCount();

        int rowCount = 0;

        while (rowCount < LIMIT && resultSet.next()) {
            Map<String,Object> mapOfColValues = new HashMap<>();
            for (int i = 1; i <= num; i++) {
                mapOfColValues.put(md.getColumnName(i), resultSet.getObject(i));
            }
            resultMapList.add(mapOfColValues);
            rowCount++;
        }
        System.out.println("execute sql : {}"+JSONObject.toJSONString(resultMapList, SerializerFeature.WriteMapNullValue));
    }
    /**
     *  close jdbc resource
     *
     * @param resultSet resultSet
     * @param pstmt pstmt
     * @param connection connection
     */
    private void close(ResultSet resultSet,
                       PreparedStatement pstmt,
                       Connection connection){
        if (resultSet != null){
            try {
                connection.close();
            } catch (SQLException e) {

            }
        }

        if (pstmt != null){
            try {
                connection.close();
            } catch (SQLException e) {

            }
        }

        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {

            }
        }
    }
}

引入阿里巴巴连接池

  <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <druid.version>1.0.9</druid.version>
        <mysql.version>8.0.28</mysql.version>
        <log4j2.version>2.16.0</log4j2.version>
        <fastjson.version>1.2.79</fastjson.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <!-- MySql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <!-- 连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>



        <!-- Log4j2 门面API-->
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.14.1</version>
        </dependency>
        <!-- Log4j2 日志实现 -->
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.14.1</version>
        </dependency>
        <!--使用slf4j作为日志的门面,使用log4j2来记录日志 -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.30</version>
        </dependency>
        <!--为slf4j绑定日志实现 log4j2的适配器 -->
        <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-slf4j-impl</artifactId>
        <version>2.12.1</version>
        </dependency>


        <!-- 阿里JSON解析器 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>${fastjson.version}</version>
        </dependency>

    </dependencies>

 配置文件  jdbc.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/gwm_alert?characterEncoding=utf8&useOldAliasMetadataBehavior=true&tinyInt1isBit=false
username=root
password=123456

maxActive=50
initialSize=5
minIdle=1
maxWait=60000
timeBetweenEvictionRunsMillis=30000
minEvictableIdleTimeMillis=300000
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
filters=stat,wall