单例模式的连接池模板

因为在项目中，要使用到多数据源，且对该项目框架不熟悉，故写出用连接池操作其它的数据库

代码：

package com.ces.component.issuetable.action;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.stereotype.Component;

import java.io.*;
import java.sql.*;
import java.util.*;

/**
 * @author Cc
 * @data 2019/5/22 15:05
 */
@Component
public class JzsDBPoolConnection {

	private voliate static JzsDBPoolConnection jzsDBPoolConnection = null;
	private static BasicDataSource dataSource = null;
	private static Connection connection = null;

	static {
		Properties properties = new Properties();
		try {
		   //你当前 路径下的数据库配置文件
			String realPath = ServletActionContext.getServletContext().getRealPath("/WEB-INF/conf/db/db.properties"); 
			/*properties.setProperty("driverClassName","com.mysql.jdbc.Driver");
			properties.setProperty("url","jdbc:mysql://localhost:3306/arch_szda_jzs");
			properties.setProperty("username","root");
			properties.setProperty("password","123");
			properties.setProperty("initialSize","5");
			properties.setProperty("maxActive","10");
			properties.setProperty("minIdle","5");
			properties.setProperty("maxIdle","10");*/
			properties.load(new FileInputStream(realPath));
			dataSource = (BasicDataSource) BasicDataSourceFactory.createDataSource(properties);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
   //  volatile + 双重检测机制 -> 禁止指令重排
 public static JzsDBPoolConnection getInstance(){
        if (jzsDBPoolConnection == null) {
            synchronized (JzsDBPoolConnection.class){
                if (jzsDBPoolConnection == null) {
                    jzsDBPoolConnection = new JzsDBPoolConnection();
                }
            }
        }
        return jzsDBPoolConnection;
    }

	public Connection getConnection(){
		try {
			connection = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}


	/**
	 * @Description 插入数据
	 * @param:
	 * @param sql
	 * @param obj
	 * @return void
	 * @author Cc
	 * @date 2019/5/22 16:00
	 */
	public static void insertMess(String sql,Object [] obj){
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			connection = JzsDBPoolConnection.getInstance().getConnection();
			ps = connection.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i+1,obj[i]);
			}
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/** 批处理更新 **/
	public  static void batchUpdate(String sql,List<Object[]> list){
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			connection = JzsDBPoolConnection.getInstance().getConnection();
			ps = connection.prepareStatement(sql);
			for (int j = 0; j < list.size(); j++) {
				for (int i = 0; i < list.get(j).length; i++) {
					ps.setObject(i+1,list.get(j)[i]);
				}
				ps.addBatch();
			}
			ps.executeBatch();
			ps.clearBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}
	
	/**
	 * @Description 查询数据。  这个方法模板是大家值得借鉴的地方
	 * @param:
	 * @param sql
	 * @param objects
	 * @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
	 * @author Cc
	 * @date 2019/5/22 16:10
	 */
	public static List<Map<String,Object>> queryMess(String sql, Object [] objects){
		List<Map<String,Object>> cList = new ArrayList<>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			System.out.println("进入查询");
			conn = JzsDBPoolConnection.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < objects.length; i++) {
				ps.setObject(i+1,objects[i]);
			}
			rs = ps.executeQuery(sql);
			//这里是精华
			ResultSetMetaData rsd = rs.getMetaData();
			while (rs.next()) {
				Map<String,Object> cMap = new HashMap<>();
				for (int i = 0; i < rsd.getColumnCount(); i++) {
					cMap.put(rsd.getColumnName(i+1),rs.getObject(i+1));
				}
				cList.add(cMap);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
			}catch (SQLException e){
				e.printStackTrace();
			}
		}
		return cList;
	}

}

调用：

List<Map<String, Object>> jMaps = JzsDBPoolConnection.queryMess(cjzsSql, new Object[0])