数据库连接池及数据库连接获取（程序员的成长之路---第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代码，即可完成项目开发。

那这套框架的思路将会为以后的开发，开辟一个新模式。

计划安排，下一编先把对象映射完成，再写数据库查询的封装。