数据库连接池及数据库连接获取(程序员的成长之路---第4篇)
闲话少说,直接列举问题:
①AB两台数据库服务器做集群,由于两台服务器配置不一样,如何在代码中要实现每访问服务器A的数据库2次,才能访问服务器B的数据库1次。
②AB两台数据库服务器做读写分离,如何在代码中控制,查询数据使用服务器A的数据库连接,增删改使用服务器B的数据库连接。
代码结构:
代码实现:
①数据库连接信息默认值
package com.keji10.core.jdbc.properties; /** * 类描述: 数据库连接信息默认值 * * @since 1.5 * @version 1.0 * @author xuanly * */ public interface JdbcPropertiesContants { /** * jdbc.maxActive最大活跃数(默认值) */ public static final int JDBC_MAXACTIVE_DEFAULT = 20; /** * jdbc.maxIdle最大连接数(默认值) */ public static final int JDBC_MAXIDLE_DEFAULT = 20; /** * jdbc.maxWait默认等待时间(默认值) */ public static final int JDBC_MAXWAIT_DEFAULT = 10000; /** * jdbc.weight默认数据库连接池权重(默认值) */ public static final int JDBC_WEIGHT_DEFAULT = 1; }
package com.keji10.core.jdbc.properties; /** * 类描述: 数据库配置文件实体类 * * @since 1.5 * @version 1.0 * @author xuanly * */ public class JdbcPropertiesBean implements JdbcPropertiesContants { private String jdbcDriver; private String jdbcUrl; private String jdbcUserName; private String jdbcPassword; private int jdbcMaxactive; private int jdbcMaxidle; private int jdbcMaxwait; private int jdbcWeight; public String getJdbcDriver() { return jdbcDriver; } public void setJdbcDriver(String jdbcDriver) { this.jdbcDriver = jdbcDriver; } public String getJdbcUrl() { return jdbcUrl; } public void setJdbcUrl(String jdbcUrl) { this.jdbcUrl = jdbcUrl; } public String getJdbcUserName() { return jdbcUserName; } public void setJdbcUserName(String jdbcUserName) { this.jdbcUserName = jdbcUserName; } public String getJdbcPassword() { return jdbcPassword; } public void setJdbcPassword(String jdbcPassword) { this.jdbcPassword = jdbcPassword; } public int getJdbcWeight() { if (jdbcWeight == 0) { // 返回默认值 return JDBC_WEIGHT_DEFAULT; } return jdbcWeight; } public void setJdbcWeight(int jdbcWeight) { this.jdbcWeight = jdbcWeight; } public int getJdbcMaxactive() { if (jdbcMaxactive == 0) { // 返回默认值 return JDBC_MAXACTIVE_DEFAULT; } return jdbcMaxactive; } public void setJdbcMaxactive(int jdbcMaxactive) { this.jdbcMaxactive = jdbcMaxactive; } public int getJdbcMaxidle() { if (jdbcMaxidle == 0) { // 返回默认值 return JDBC_MAXIDLE_DEFAULT; } return jdbcMaxidle; } public void setJdbcMaxidle(int jdbcMaxidle) { this.jdbcMaxidle = jdbcMaxidle; } public int getJdbcMaxwait() { if (jdbcMaxwait == 0) { // 返回默认值 return JDBC_MAXWAIT_DEFAULT; } return jdbcMaxwait; } public void setJdbcMaxwait(int jdbcMaxwait) { this.jdbcMaxwait = jdbcMaxwait; } public String toString() { StringBuffer s = new StringBuffer(); s.append("jdbcDriver =" + jdbcDriver); s.append("jdbcUrl =" + jdbcUrl); s.append("jdbcUserName =" + jdbcUserName); s.append("jdbcPassword =" + jdbcPassword); s.append("jdbcMaxactive =" + jdbcMaxactive); s.append("jdbcMaxidle =" + jdbcMaxidle); s.append("jdbcMaxwait =" + jdbcMaxwait); s.append("jdbcWeight =" + jdbcWeight); return s.toString(); } }
③数据库配置文件信息获取
package com.keji10.core.jdbc.properties; import java.util.ResourceBundle; import org.apache.log4j.Logger; import com.keji10.core.exception.DBException; /** * 类描述: 数据库配置文件信息获取 * * @since 1.5 * @version 1.0 * @author xuanly * */ public class JdbcPropertiesManager { private Logger logger = Logger.getLogger(JdbcPropertiesManager.class.getName()); // 单例模式Begin private static JdbcPropertiesManager instance = new JdbcPropertiesManager(); private JdbcPropertiesManager() { } public static JdbcPropertiesManager getInstance() { return instance; } // 单例模式End private JdbcPropertiesBean jdbcPropertiesBean; /** * 获取jdbc.propertiesBean配置文件 * * @return JdbcPropertiesBean * @throws DBException */ public JdbcPropertiesBean getJdbcPropertiesBean() throws DBException { return getJdbcPropertiesBean("jdbc"); } public JdbcPropertiesBean getJdbcPropertiesBean(String propertieFile) throws DBException { ResourceBundle rb = ResourceBundle.getBundle(propertieFile); jdbcPropertiesBean = new JdbcPropertiesBean(); try { jdbcPropertiesBean.setJdbcDriver(rb.getString("jdbc.driver")); jdbcPropertiesBean.setJdbcUrl(rb.getString("jdbc.url")); jdbcPropertiesBean.setJdbcUserName(rb.getString("jdbc.username")); jdbcPropertiesBean.setJdbcPassword(rb.getString("jdbc.password")); } catch (Exception e) { logger.warn("获取数据库连接配置文件【jdbc.driver】失败,执行无法启动!"); logger.warn("获取数据库连接配置文件【jdbc.url】失败,执行无法启动!"); logger.warn("获取数据库连接配置文件【jdbc.username】失败,执行无法启动!"); logger.warn("获取数据库连接配置文件【jdbc.password】失败,执行无法启动!"); logger.error(e); throw new DBException(e); } try { jdbcPropertiesBean.setJdbcMaxactive(Integer.valueOf(rb.getString("jdbc.maxActive"))); jdbcPropertiesBean.setJdbcMaxidle(Integer.valueOf(rb.getString("jdbc.maxIdle"))); jdbcPropertiesBean.setJdbcMaxwait(Integer.valueOf(rb.getString("jdbc.maxWait"))); jdbcPropertiesBean.setJdbcWeight(Integer.valueOf(rb.getString("jdbc.weight"))); } catch (Exception e) { logger.warn("获取数据库连接配置文件【jdbc.maxActive】失败,系统将执行默认配置"); logger.warn("获取数据库连接配置文件【jdbc.maxIdle】失败,系统将执行默认配置"); logger.warn("获取数据库连接配置文件【jdbc.maxWait】失败,系统将执行默认配置"); logger.warn("获取数据库连接配置文件【jdbc.weight】失败,系统将执行默认配置"); } return jdbcPropertiesBean; } }
④数据库连接池管理
package com.keji10.core.jdbc.dataSource; import java.util.HashMap; import java.util.Map; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSource; import org.apache.log4j.Logger; import com.keji10.core.commons.ConfigBean; import com.keji10.core.exception.DBException; import com.keji10.core.jdbc.properties.JdbcPropertiesBean; import com.keji10.core.jdbc.properties.JdbcPropertiesManager; /** * 类描述:数据库连接池管理 * * @since 1.5 * @version 1.0 * @author chenairu * */ public class DBDataSource { private Logger logger = Logger.getLogger(DBDataSource.class.getName()); // 单例模式begin private static DBDataSource instance = new DBDataSource(); private DBDataSource() { } public static DBDataSource getInstance() { return instance; } // 单例模式end // 非主数据库连接池集群池 private Map<String, DataSource> otherDataSourceMap = new HashMap<String, DataSource>(); // 主数据库连接池集群池 private Map<Integer, DataSource> mainDataSourceMap = new HashMap<Integer, DataSource>(); // 记录数据库负载均衡的配置 private Map<Integer, Integer> mainDataSourceWeightMap = new HashMap<Integer, Integer>(); // 记录获取数据库连接的指针 private int mainDataSourceWeightIndex = 0; /** * 获取连接池 * * @param bean * @return * @throws DBException */ public DataSource getDataSource(JdbcPropertiesBean bean) throws DBException { if (bean == null) { return getDataSource(); } if (bean.getJdbcDriver() == null || bean.getJdbcUrl() == null || bean.getJdbcUserName() == null || bean.getJdbcPassword() == null) { logger.error("********数据库配置信息不完整!!!*********"); logger.error("*********加载默认数据库配置!!!*********"); return getDataSource(); } DataSource dataSource = (DataSource) otherDataSourceMap.get(bean.toString()); if (dataSource != null) { return dataSource; } logger.info("*********加载数据库配置***********************"); logger.info("JdbcDriver =" + bean.getJdbcDriver()); logger.info("JdbcUrl =" + bean.getJdbcUrl()); logger.info("JdbcUserName =" + bean.getJdbcUserName()); logger.info("JdbcPassword =" + bean.getJdbcPassword()); logger.info("JdbcMaxactive=" + bean.getJdbcMaxactive()); logger.info("JdbcMaxidle =" + bean.getJdbcMaxidle()); logger.info("JdbcMaxwait =" + bean.getJdbcMaxwait()); BasicDataSource basicDataSource = new BasicDataSource(); basicDataSource.setDriverClassName(bean.getJdbcDriver()); basicDataSource.setUrl(bean.getJdbcUrl()); basicDataSource.setUsername(bean.getJdbcUserName()); basicDataSource.setPassword(bean.getJdbcPassword()); basicDataSource.setMaxActive(bean.getJdbcMaxactive()); basicDataSource.setMaxIdle(bean.getJdbcMaxidle()); basicDataSource.setMaxWait(bean.getJdbcMaxwait()); dataSource = basicDataSource; logger.info("*********加载数据库配置完成************************"); otherDataSourceMap.put(bean.toString(), dataSource); return dataSource; } /** * 获取连接池 * * @return 连接池 * @throws DBException */ public DataSource getDataSource() throws DBException { String jdbcFileName; JdbcPropertiesBean bean; DataSource dataSource; String jdbcs = ConfigBean.getInstance().getJdbcs(); if (mainDataSourceMap.isEmpty()) { for (int i = 0; i < jdbcs.split(",").length; i++) { jdbcFileName = jdbcs.split(",")[i]; bean = JdbcPropertiesManager.getInstance().getJdbcPropertiesBean(jdbcFileName); logger.info("*********加载数据库配置***********************"); logger.info("JdbcDriver =" + bean.getJdbcDriver()); logger.info("JdbcUrl =" + bean.getJdbcUrl()); logger.info("JdbcUserName =" + bean.getJdbcUserName()); logger.info("JdbcPassword =" + bean.getJdbcPassword()); logger.info("JdbcMaxactive=" + bean.getJdbcMaxactive()); logger.info("JdbcMaxidle =" + bean.getJdbcMaxidle()); logger.info("JdbcMaxwait =" + bean.getJdbcMaxwait()); logger.info("JdbcWeight =" + bean.getJdbcWeight()); BasicDataSource basicDataSource = new BasicDataSource(); basicDataSource.setDriverClassName(bean.getJdbcDriver()); basicDataSource.setUrl(bean.getJdbcUrl()); basicDataSource.setUsername(bean.getJdbcUserName()); basicDataSource.setPassword(bean.getJdbcPassword()); basicDataSource.setMaxActive(bean.getJdbcMaxactive()); basicDataSource.setMaxIdle(bean.getJdbcMaxidle()); basicDataSource.setMaxWait(bean.getJdbcMaxwait()); dataSource = basicDataSource; //负载设置关键代码---begin mainDataSourceMap.put(i, dataSource); for (int j = 0; j < bean.getJdbcWeight(); j++) { mainDataSourceWeightMap.put(mainDataSourceWeightMap.size() + 1, i); } //负载设置关键代码---end logger.info("*********加载数据库配置完成************************"); } } int mainDataSourceNowIndex; try { mainDataSourceNowIndex = mainDataSourceWeightMap.get(mainDataSourceWeightIndex % (mainDataSourceWeightMap.size())); mainDataSourceWeightIndex++; if (mainDataSourceWeightIndex >= mainDataSourceWeightMap.size()) { mainDataSourceWeightIndex = 0; } } catch (Exception e) { mainDataSourceNowIndex = 0; } return (DataSource) mainDataSourceMap.get(mainDataSourceNowIndex); } /** * 清空数据库连接池 */ public void clear() { otherDataSourceMap.clear(); mainDataSourceMap.clear(); mainDataSourceWeightMap.clear(); mainDataSourceWeightIndex = 0; } }
⑤数据库连接实现
package com.keji10.core.jdbc.dataSource; import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; import org.apache.log4j.Logger; import com.keji10.core.exception.DBException; import com.keji10.core.jdbc.properties.JdbcPropertiesBean; /** * 类描述:数据库连接实现 * * @since 1.5 * @version 1.0 * @author xuanly * */ public class DBConnection { private Logger logger = Logger.getLogger(DBConnection.class.getName()); // 单例模式begin private static DBConnection instance = new DBConnection(); private DBConnection() { } public static DBConnection getInstance() { return instance; } // 单例模式end /** * 获取默认数据库连接 * * @return 数据库连接 * @throws DBException */ public Connection getConnection() throws DBException { try { return DBDataSource.getInstance().getDataSource().getConnection(); } catch (SQLException e) { logger.error(e); throw new DBException(e); } } /** * 根据配置文件获取自定义数据库连接 * * @param JdbcPropertiesBean * @return * @throws DBException */ public Connection getConnection(JdbcPropertiesBean JdbcPropertiesBean) throws DBException { DataSource dataSource = DBDataSource.getInstance().getDataSource(JdbcPropertiesBean); try { return dataSource.getConnection(); } catch (SQLException e) { logger.error(e); throw new DBException(e); } } /** * 关闭数据库连接 * * @throws DBException */ public void close(Connection connection) throws DBException { try { if (connection == null) { return; } if (connection.isClosed()) { return; } if (connection != null) { connection.close(); connection = null; } } catch (SQLException e) { logger.error(e); throw new DBException(e); } finally { connection = null; } } /** * 清空数据库连接池 */ public void clear() { DBDataSource.getInstance().clear(); } }
封装框架最核心的部分,莫过于数据库连接的封装。数据库连接就像一条管道,一端连着数据库,一端连接java程序。
如果一个框架,能够像数据库连接一样,一端连着数据库,一端连着用户的界面,中间无需编辑java代码,即可完成项目开发。
那这套框架的思路将会为以后的开发,开辟一个新模式。
计划安排,下一编先把对象映射完成,再写数据库查询的封装。
生命不息,奋斗不止