JDBC使用Druid作为连接池

1. jdbc.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8
username=admin
password=admin

filters=stat
initialSize=100
maxActive=300
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
rewriteBatchedStatements=true
maxPoolPreparedStatementPerConnectionSize=200

2. 常量类

package conf;

public class SysConstants {
	
	/** jdbc配置文件路径(src/main/resources) **/
	public static final String JDBC_CONF_FILE = "jdbc.properties";
	
}

 3. Jdbc使用Druid管理连接

package jdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import conf.SysConstants;

@Deprecated
public class JdbcConnectionPool {
	public static Logger logger = LoggerFactory.getLogger(JdbcConnectionPool.class);
	
	private static ThreadLocal<Connection> connectionThreadLocal = new ThreadLocal<Connection>(); 
	private static DruidDataSource druidDataSource = null;

	static {
		Properties properties = loadPropertiesFile(SysConstants.JDBC_CONF_FILE);
		try {
			druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties); 
		} catch (Exception e) {
			logger.error("[JDBC Exception] --> "
					+ "Failed to configured the Druid DataSource, the exceprion message is:" + e.getMessage());
		}
	}
	
	public static Connection getConnection() {
		Connection connection = connectionThreadLocal.get();
		try {
			if(null == connection){
				connection = druidDataSource.getConnection();
				connectionThreadLocal.set(connection);
			}
		} catch (SQLException e) {
			logger.error("[JDBC Exception] --> "
					+ "Failed to create a connection, the exceprion message is:" + e.getMessage());
		}
		return connection;
	}
	
	public static void closeConnection() {
		Connection connection = connectionThreadLocal.get();
		if(null != connection){
			try {
				connection.close();
				connectionThreadLocal.remove();
			} catch (SQLException e) {
				logger.error("[JDBC Exception] --> "
						+ "Failed to close the DruidPooledConnection, the exceprion message is:" + e.getMessage());
			}
		}
	}
	
	public static void startTransaction() {
        Connection conn=connectionThreadLocal.get();
        
        try{
        	if(conn==null){
                conn=getConnection();
                connectionThreadLocal.set(conn);
            }
            conn.setAutoCommit(false);
        }catch(Exception e){
        	logger.error("[JDBC Exception] --> "
					+ "Failed to start the transaction, the exceprion message is:" + e.getMessage());
        }
    }
    public static void commit(){
        try{
            Connection conn=connectionThreadLocal.get();
            if(null!=conn){
                conn.commit();
            }
        }catch(Exception e){
        	logger.error("[JDBC Exception] --> "
					+ "Failed to commit the transaction, the exceprion message is:" + e.getMessage());
        }
    }
    
    public static void rollback(){
    	try{
            Connection conn=connectionThreadLocal.get();
            if(conn!=null){
                conn.rollback();
                connectionThreadLocal.remove();
            }
        }catch(Exception e){
        	logger.error("[JDBC Exception] --> "
					+ "Failed to rollback the transaction, the exceprion message is:" + e.getMessage());
        }
    }
	
	private static Properties loadPropertiesFile(String fullFile) {
		if (null == fullFile || fullFile.equals("")) {
			throw new IllegalArgumentException(
					"Properties file path can not be null" + fullFile);
		}
		Properties prop = new Properties();
		try {
			prop.load(JdbcConnectionPool.class.getClassLoader().getResourceAsStream(fullFile));
		} catch (IOException e) {
			logger.error("[Properties Exception] --> "
					+ "Can not load jdbc properties, the exceprion message is:" + e.getMessage());
		}
		return prop;
	}

}

4. CRUD

package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

@Deprecated
public class JdbcTemplate {
	
	public static Logger logger = LoggerFactory.getLogger(JdbcTemplate.class);
	
	private static JdbcTemplate jdbcTemplate = null;
	
	private JdbcTemplate(){ }
	
	public static JdbcTemplate getInstance() {
		if(jdbcTemplate == null)
			jdbcTemplate = new JdbcTemplate();
		return jdbcTemplate;
	}
	
	public boolean insert(String sql){
		boolean f = false;
		PreparedStatement prep = null;
		Connection conn = null;
		try {
			conn = JdbcConnectionPool.getConnection();
			prep = conn.prepareStatement(sql);
			f = prep.execute();
			
		} catch (SQLException e) {
			logger.error("[JDBC Exception] --> "
					+ "Can not insert, the exceprion message is:" + e.getMessage());
		} finally {
			try {
				if(null != prep)
					prep.close();
			} catch (SQLException e) {
				logger.error("[JDBC Exception] --> "
						+ "Failed to close connection, the exceprion message is:" + e.getMessage());
			}
		}
		
		return f;
	}
	
	public void insert(String sql, List<List<String>> data){
		PreparedStatement prep = null;
		Connection conn = null;
		try {
			conn = JdbcConnectionPool.getConnection();
			prep = conn.prepareStatement(sql);
			JdbcConnectionPool.startTransaction();
			for(int i=0; i<data.size(); i++){
				List<String> l = data.get(i);
				for(int j=0; j<l.size(); j++){
					prep.setObject(j+1, l.get(j));
				}
				prep.addBatch();
			}
			prep.executeBatch();
			prep.clearBatch();
			JdbcConnectionPool.commit();
			
		} catch (SQLException e) {
			logger.error("[JDBC Exception] --> "
					+ "Can not insert, the exceprion message is:" + e.getMessage());
		} finally {
			try {
				if(null != prep)
					prep.close();
				JdbcConnectionPool.closeConnection();
			} catch (SQLException e) {
				logger.error("[JDBC Exception] --> "
						+ "Failed to close connection, the exceprion message is:" + e.getMessage());
			}
		}
		
		return;
	}
	
	public String select(String sql){
		String r = "";
		ResultSet rs=null; 
		PreparedStatement prep = null;
		Connection conn = null;
		try {
			conn = JdbcConnectionPool.getConnection();
			prep = conn.prepareStatement(sql);
			rs = prep.executeQuery();
			r = ResultSetToJson(rs);
			
		} catch (SQLException e) {
			logger.error("[JDBC Exception] --> "
					+ "Can not select, the exceprion message is:" + e.getMessage());
		} finally {
			try {
				if(null != rs)
					rs.close();
				if(null != prep)
					prep.close();
				JdbcConnectionPool.closeConnection();
			} catch (SQLException e) {
				logger.error("[JDBC Exception] --> "
						+ "Failed to close connection, the exceprion message is:" + e.getMessage());
			}
		}
		
		return r;
	}
	
	public static String ResultSetToJson(ResultSet rs){
		JSONArray array = new JSONArray();  
	    try {
	    	ResultSetMetaData metaData = rs.getMetaData();  
		    int columnCount = metaData.getColumnCount();  
		    
		    while (rs.next()) {  
		    	JSONObject jsonObj = new JSONObject();  
		        for (int i = 1; i <= columnCount; i++) {  
		            String columnName =metaData.getColumnLabel(i);  
		            String value = rs.getString(columnName);  
		            jsonObj.put(columnName, value);  
		        }   
		        array.add(jsonObj);
		    }
		} catch (SQLException e) {
			logger.error("[ResultSetToJson Exception] --> "
					+ "Failed to covert ResultSet Data to Json, the exceprion message is:" + e.getMessage());
		}  
	    
	   return array.toString();
	}
	
	
}

 

转载于:https://my.oschina.net/jos/blog/1509685