自己写的一个数据库访问类,请提出修改意见

BenChao 发布于 2012/12/20 10:42
阅读 846
收藏 2

解读下一代网络:算力网络正从理想照进现实!>>>

本人学java不久,最近在项目中需要访问多个数据库,我就写了一个数据库访问的通用类,需要使用c3p0的数据源管理组件。

1、数据源配置文件DBInfo.properties

test.AcquireIncrement         = 1
test.AcquireRetryAttempts     = 30
test.AcquireRetryDelay        = 1000
test.CheckoutTimeout          = 3000
test.Driver                   = com.mysql.jdbc.Driver
test.IdleConnectionTestPeriod = 18000
test.InitialPoolSize          = 10
test.MaxIdleTime              = 18000
test.MaxPoolSize              = 300
test.MaxStatements            = 0
test.MinPoolSize              = 10
test.Password                 = 123456
test.TestConnectionOnCheckin  = false
test.TestConnectionOnCheckout = false
test.Url                      = jdbc:mysql://127.0.0.1:3306/test
test.User                     = root

webdemo.AcquireIncrement         = 1
webdemo.AcquireRetryAttempts     = 30
webdemo.AcquireRetryDelay        = 1000
webdemo.CheckoutTimeout          = 3000
webdemo.Driver                   = com.mysql.jdbc.Driver
webdemo.IdleConnectionTestPeriod = 18000
webdemo.InitialPoolSize          = 10
webdemo.MaxIdleTime              = 18000
webdemo.MaxPoolSize              = 300
webdemo.MaxStatements            = 0
webdemo.MinPoolSize              = 10
webdemo.Password                 = 123456
webdemo.TestConnectionOnCheckin  = false
webdemo.TestConnectionOnCheckout = false
webdemo.Url                      = jdbc:mysql://127.0.0.1:3306/webdemo
webdemo.User                     = root

2、管理多个C3p0数据源的数据源管理类

package ben.JdbcUtility;

import java.io.InputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Properties;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * c3p0数据源管理类
 * Description: 把多个c3p0数据源放进容器中管理
 * @author: BenSharp
 * @create date: 2012-9-1 
 * */
public final class DataSourceManager {
	//保存数据库连接池的容器
	public static HashMap<String, ComboPooledDataSource> dataSourceMap = new HashMap<String, ComboPooledDataSource>();
	//数据库信息的配置文件
	private static Properties pp = null;
	private static InputStream fs = null;
	
	private DataSourceManager(){
		
	}
	
	/**
	 * 从连接池容器中返回连接池对象
	 * @return 连接池的名称
	 * */
	public static ComboPooledDataSource getDataSource(String dataSourceName){
		//如果指定数据源不存在,则创建
		if (!dataSourceMap.containsKey(dataSourceName.toString())){
			try {
				ComboPooledDataSource ds = new ComboPooledDataSource();
				//读取数据库配置文件
				pp = new Properties();
				fs = DataSourceManager.class.getClassLoader().getResourceAsStream("DBInfo.properties");
				pp.load(fs);
				
				//配置数据源
				ds.setDriverClass(pp.getProperty(dataSourceName + "." + "Driver"));
				ds.setJdbcUrl(pp.getProperty(dataSourceName + "." + "Url"));
				ds.setUser(pp.getProperty(dataSourceName + "." + "User"));
				ds.setPassword(pp.getProperty(dataSourceName + "." + "Password"));
				ds.setMaxPoolSize(Integer.parseInt(pp.getProperty(dataSourceName + "." + "MaxPoolSize")));
				ds.setMinPoolSize(Integer.parseInt(pp.getProperty(dataSourceName + "." + "MinPoolSize")));
				ds.setMaxIdleTime(Integer.parseInt(pp.getProperty(dataSourceName + "." + "MaxIdleTime")));
				ds.setInitialPoolSize(Integer.parseInt(pp.getProperty(dataSourceName + "." + "InitialPoolSize")));
				ds.setAcquireIncrement(Integer.parseInt(pp.getProperty(dataSourceName + "." + "AcquireIncrement")));
				ds.setAcquireRetryAttempts(Integer.parseInt(pp.getProperty(dataSourceName + "." + "AcquireRetryAttempts")));
				ds.setAcquireRetryDelay(Integer.parseInt(pp.getProperty(dataSourceName + "." + "AcquireRetryDelay")));
				ds.setMaxStatements(Integer.parseInt(pp.getProperty(dataSourceName + "." + "MaxStatements")));
				ds.setIdleConnectionTestPeriod(Integer.parseInt(pp.getProperty(dataSourceName + "." + "IdleConnectionTestPeriod")));
				ds.setCheckoutTimeout(Integer.parseInt(pp.getProperty(dataSourceName + "." + "CheckoutTimeout")));
				ds.setTestConnectionOnCheckin(Boolean.parseBoolean(pp.getProperty(dataSourceName + "." + "TestConnectionOnCheckin")));
				ds.setTestConnectionOnCheckout(Boolean.parseBoolean(pp.getProperty(dataSourceName + "." + "TestConnectionOnCheckout")));
				
				//把数据源放进容器中
				dataSourceMap.put(dataSourceName, ds);
				
				return ds;
				
			} catch (Exception e) {
				e.printStackTrace();
				throw new RuntimeException("无法根据配置文件创建连接池对象", e);
			}
			finally{
				try {
					fs.close();
				} catch (IOException e2) {
					e2.printStackTrace();
					throw new RuntimeException("无法找到配置文件", e2);
				}
			}

		}
		else {
			return (ComboPooledDataSource)dataSourceMap.get(dataSourceName.toString());
		}
	}
}

3、数据库访问的通用类

package ben.JdbcUtility;

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

import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.sun.rowset.CachedRowSetImpl;

/**
 * Jdbc应用类
 * Description: 1、需要在调用项目中引用c3p0数据源jar包
 * 				2、需要把DBInfo.properties文件放在classes文件夹中
 * 				3、DBInfo.properties文件的配置前序为数据源名称。例如:配置为webdemo.Driver,则引用时JdbcHelper.executeUpdate("webdemo", sql, params);
 * @author: BenSharp
 * @create date: 2012-9-1 
 * */
public final class JdbcHelper {
	/**
	 * 获取一个数据库连接
	 * @return 一个数据库连接
	 * */
	private static synchronized Connection getConnection(String dataSourceName){
		try {
			ComboPooledDataSource ds = DataSourceManager.getDataSource(dataSourceName);
			return ds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 关闭数据库连接释放资源
	 * */
	private static void close(Connection conn, PreparedStatement pstm, ResultSet rs){
		try {
			if (conn != null){
				conn.close();
			}
			if (pstm != null){
				pstm.close();
			}
			if (rs != null){
				rs.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 执行单个数据库操作 Insert,Update,Delete
	 * @return 成功执行的记录数
	 * */
	public static Integer executeUpdate(String dsName, String sql, String[] params){
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = getConnection(dsName);
			pstm = conn.prepareStatement(sql);
			if (params != null){
				for (int i=0; i<params.length; i++){
					pstm.setString(i+1, params[i]);
				}
			}
			
			return pstm.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
		finally{
			close(conn, pstm, null);
		}
	}
	
	/**
	 * 执行多个数据库操作,包含事务处理功能
	 * @return 如果事务执行成功返回1,如果事务执行不成功返回0
	 * */
	public static Integer executeUpdate(String dsName, String[] sqls, String[][] params){
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn=getConnection(dsName);
			//禁止自动提交事务
			conn.setAutoCommit(false);
			for (int i=0; i<sqls.length; i++){
				pstm = conn.prepareStatement(sqls[i]);
				if (params != null){
					for (int j=0; j<params[i].length; j++){
						pstm.setString(j+1, params[i][j]);
					}
				}
				pstm.executeUpdate();
			}
			
			conn.commit();
			
			return 1;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			return 0;
		}
		finally{
			close(conn, pstm, null);
		}
	}
	
	/**
	 * 执行数据库查询操作
	 * @return 查询结果的离线RowSet
	 * */
	public static RowSet executeQuery(String dsName, String sql, String[] params){
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		CachedRowSet crs = null;
		
		try {
			conn = getConnection(dsName);
			pstm = conn.prepareStatement(sql);
			if (params != null){
				for (int i=0; i<params.length; i++){
					pstm.setString(i+1, params[i]);
				}
			}
			rs = pstm.executeQuery();
			
			//创建CacheRowSet
			crs = new CachedRowSetImpl();
			crs.populate(rs);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			close(conn, pstm, rs);
		}
		
		return crs;
	}
	
	/**
	 * 执行需要分页的数据库查询操作
	 * @return 查询结果的离线RowSet
	 * */
	public static RowSet executeQuery(String dsName, String sql, String[] params, Integer pageIndex, Integer pageSize){
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		CachedRowSet crs = null;
		
		try {
			conn = getConnection(dsName);
			pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			if (params != null){
				for (int i=0; i<params.length; i++){
					pstm.setString(i+1, params[i]);
				}
			}
			rs = pstm.executeQuery();
			
			//创建CacheRowSet
			crs = new CachedRowSetImpl();
			crs.setPageSize(pageSize);
			crs.populate(rs, (pageIndex-1)*pageSize+1);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			close(conn, pstm, rs);
		}
		
		return crs;
	}
	
	/**
	 * 执行查询的存储过程"{ call addUser(?,?,?,?) }"
	 * @return 返回查询结果的RowSet集合
	 * */
	public static RowSet executeStoredProcedure(String dsName, String sp_name, String[] params){
		Connection conn = null;
		CallableStatement cstm = null;
		ResultSet rs = null;
		CachedRowSet crs = null;
		
		try {
			conn = getConnection(dsName);
			cstm = conn.prepareCall(sp_name);
			if (params != null){
				for (int i=0; i<params.length; i++){
					cstm.setString(i+1, params[i]);
				}
			}
			rs = cstm.executeQuery();
			
			//创建CacheRowSet
			crs = new CachedRowSetImpl();
			crs.populate(rs);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			close(conn, cstm, rs);
		}
		
		return crs;
	}
}



加载中
0
Wentasy
Wentasy
精神可嘉。
0
只会百度的程序员
只会百度的程序员
资源释放顺序。。。
0
酷酷的就
酷酷的就
释放顺序反了,
酷酷的就
酷酷的就
回复 @benzhao : yes, 一定是这个顺序,不可以错.
BenChao
BenChao
是JdbcHelper里面的close方法里面释放顺序反了吗? 先是释放rs,再释放pstm,最后释放conn吗?
0
wxmqwe
wxmqwe
没看完代码,但配置文件,是配置了两个mysql数据源吗?还是通用到能连接常见的关系型数据库?
BenChao
BenChao
只要你项目中有相应数据库的驱动,你都可以访问该类型的数据库。可以访问同一种数据库的多个库,也可以访问多个不同类型的数据库。
0
哈密瓜
哈密瓜

 可以参照hibernate用threadlocal对连接进行缓存


返回顶部
顶部