javaweb中调用mysql线程池

1.需要两个jar包，网盘下载地址commons-dbcp2-2.4.0.jar，链接：https://pan.baidu.com/s/10708-M4GPAEgW0W8ar6c0Q
提取码：m444
commons-pool2-2.8.0.jar网盘下载地址，链接：https://pan.baidu.com/s/1N3Mpff1S5X2Wc_pgoBaMOQ
提取码：rult

2.创建线程池公共类JdbcUtilPool.java

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;


public class JdbcUtilPool {
	private static DataSource pool;
    
    static{
 
        try {
            //读取配置文件
            Properties p = new Properties();
            p.setProperty("driverClassName", "com.mysql.cj.jdbc.Driver");
            p.setProperty("url", "jdbc:mysql://localhost:3306/mydatabase?serverTimezone=UTC");
         
            p.setProperty("username", "root");
            p.setProperty("password", "123123123");
            p.setProperty("maxActive", "30");
            p.setProperty("maxIdle", "30");
            p.setProperty("maxWait", "1000");
            p.setProperty("removeAbandoned", "true");
            p.setProperty("removeAbandonedTimeout", "180");
            
            //p.load(JdbcUtilPool.class.getResourceAsStream("dbcp.properties"));// 配置文件和当前类的class放在一起
            pool = BasicDataSourceFactory.createDataSource(p);
            
            
        } catch (Exception e) {
            e.printStackTrace();
        } 
    }
 
    //返回DataSource--池
    public static DataSource getDataSource(){
        return pool;
    }
 
    public static Connection getConnection() throws SQLException{
       
    	Connection con = pool.getConnection();
    	
        System.out.println("连接数据库："+con.hashCode());
        return con;
    }

   
    /**
     * 查询语句
     * @param sql sql语句
     * @param parms 占位符参数数组
     * @return 返回List<Object[]>
     */
    public static List<Object[]> select(String sql, String[] parms) {
    	PreparedStatement preparedStatement=null;
    	Connection connection=null;
        try {
            //获取连接
        	
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //对sql中的占位符进行赋值
            if (parms != null && parms.length > 0) {
                for (int i = 0; i < parms.length; i++) {
                    if("null".equals(parms[i])){
                        preparedStatement.setNull(i + 1, Types.NULL);
                    }else {
                        preparedStatement.setString(i + 1, parms[i]);
                    }
                }
            }
            //获得结果集合
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            //定义List存放每行数据
            List<Object[]> objectList = new ArrayList<>();
            //获取列数
            int columnCount = metaData.getColumnCount();
            //对结果集合进行遍历并将每行的数据存入Object[]中
            while (resultSet.next()) {
                //对象数组，表示一行数据
                Object[] objects = new Object[columnCount];
                for (int i = 1; i <= columnCount; i++) {
                    objects[i - 1] = resultSet.getObject(i);
                }
                //将数组存入list
                objectList.add(objects);
            }
            return objectList;
        } catch (Exception e) {
            System.err.println("查询失败！");
            e.printStackTrace();
        } finally {
        	if(preparedStatement!=null) {
	    		try {
	    			preparedStatement.close();
				} catch (Exception e2) {
					e2.printStackTrace(); 
				}
	    	}
        	
        	if(connection!=null) {
	    		try {
	    			connection.close();
				} catch (Exception e2) {
					e2.printStackTrace(); 
				}
	    	}
        	
		}
        return null;
    }

    /**
     * 可处理insert/delete/update语句
     * @param sql sql语句
     * @param parms 占位符参数数组
     * @return 返回bool值，表示是否成功
     */
    public static int addOrInsertOrUpdate(String sql, String[] parms){
    	PreparedStatement preparedStatement=null;
    	 Connection connection =null;
        try {
            //获取连接
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //对占位符进行赋值
            if (parms != null && parms.length > 0) {
                for (int i = 0; i < parms.length; i++) {
                    if("null".equals(parms[i])){
                        preparedStatement.setNull(i + 1, Types.NULL);
                    }else {
                        preparedStatement.setString(i + 1, parms[i]);
                    }
                }
            }
            //提交sql
            int count =preparedStatement.executeUpdate();
            return count;
        } catch (Exception e){
            System.err.println("表更新失败!");
            e.printStackTrace();
        } finally {
        	if(preparedStatement!=null) {
	    		try {
	    			preparedStatement.close();
				} catch (Exception e2) {
					e2.printStackTrace(); 
				}
	    	}
	    	if(connection!=null) {
	    		try {
	    			connection.close();
				} catch (Exception e2) {
					e2.printStackTrace(); 
				}
	    	}
        }
        return -1;
    }

   
    
	}

3.测试结果

4.测试代码：ip查询.java

import java.util.List;


public class ip查询 {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try{
            
        	long iplong = ipToLong("1.50.101.228");
        	String sql = "SELECT country,province,city FROM ipinfo where ipstart<=? and ipend>=?";
			  
			  System.out.println(sql);
			  // 查找数据返回结果集
			  String[] param = new String[2];
			  param[0]=iplong+"";
			  param[1]=iplong+"";
			  
			  List<Object[]> objectList = JdbcUtilPool.select(sql,param);
			  //对返回的结果进行遍历
			  for (Object[] objects : objectList) {
	        	
		    	System.out.println(objects[0]+"");
		    	System.out.println(objects[1]+"");
		    	System.out.println(objects[2]+"");
			  }  	        
        
       
    }catch (Exception e) {
        e.printStackTrace();
    }

	}
	public static long ipToLong(String ip) {
		long ret = 0L;
		try {
			if(!ip.isEmpty()&&ip.contains(".")) {
				String[] s = ip.split("\\.");
				if(s.length==4) {
					
					ret = Long.parseLong(s[0])*256*256*256+Long.parseLong(s[1])*256*256+Long.parseLong(s[2])*256+Long.parseLong(s[3]);
					return ret;
				}
			}
		} catch (Exception e) {
		}
		return ret;
	}
}