首页 > 代码库 > 利用apache commons组件实现WEB应用跨多数据库
利用apache commons组件实现WEB应用跨多数据库
1、前言
在一些多数据库的环境中，有时候应用系统需要同时访问多个数据库，在流行的spring+hibernate等架构中，涉及要修改和调整的内容会比较多；如果直接用jdbc，那么代码里就要充斥很多的连接、关闭数据库、以及从数据库中进行循环取结果的代码，感觉总是不够优化。
利用apachecommons 组件下的DBCP，dbUtils也能够编写相对比较优雅一点的代码。
本文主要提供更思路和方法，代码存在不够优化，架构存在不够清晰的情况，请辩证的看。
WEB应用涉及的东西有点多，写起来也挺繁琐的。如果有看不懂的地方，就多琢磨下吧，系统能正常运行的。
基于JDK1.6，ORACLE10g，tomcat6 开发和测试。
2、需要的文件
Oracle驱动：class12；commons-configuration-1.10.jar， commons-dbcp-1.4.jar，commons-dbutils-1.6.jar，commons-pool-1.6.jar在JRE5环境下，需要引入 struts2 core libraries库。
3、数据库表
两张
create table TB_STUDENT
(
ID INTEGER,
NAME VARCHAR2(256),
SEX VARCHAR2(256),
AGE VARCHAR2(256),
GRADE INTEGER,
CLASSNAME VARCHAR2(256)
)
Create table tb_user(
username VARCHAR2(256),
password VARCHAR2(256)
)
4、整体架构
1个web文件，index.jsp 放在WebRoot目录下
1个数据库配置文件，db.properties复制两份，一份放在工程根目录下（做测试用），另外一份放在src目录下，供运行正式用；
1个struts.xml文件，放在src目录下
4个package，action，bean，dao，utils
5、目录结构截图：
6、utils包
包括三个，一个读取配置文件，一个实现数据库连接，另外一个是一个tb_user的简单对象，供连接测试使用。
package cn.mrcast.util;
import org.apache.commons.configuration.CompositeConfiguration;
importorg.apache.commons.configuration.ConfigurationException;
importorg.apache.commons.configuration.PropertiesConfiguration;
/**
* 通过commonsconfiguration进行简单的配置
*
*@author 范芳铭
*/
public class ConfigurationUsage {
publicstatic void main(String[] args) {
StringjdbcUrl = propManager.getInstance().getProperty("jdbc.cms.url");
StringjdbcUsername = propManager.getInstance().getProperty(
"jdbc.cms.username");
StringjdbcPassword = propManager.getInstance().getProperty(
"jdbc.cms.password");
System.out.println("jdbcUrl:"+ jdbcUrl);
System.out.println("jdbcUsername:"+ jdbcUsername);
System.out.println("jdbcPassword:"+ jdbcPassword);
}
privateConfigurationUsage() {
}
privatestatic ConfigurationUsage propManager;
publicsynchronized static ConfigurationUsage getInstance() {
if(propManager == null) {
propManager= new ConfigurationUsage();
}
returnpropManager;
}
publicstatic CompositeConfiguration config = new CompositeConfiguration();
static{
try{
config.addConfiguration(newPropertiesConfiguration(
"db.properties"));
}catch (ConfigurationException e) {
e.printStackTrace();
}
}
publicString getProperty(String key) {
returnconfig.getString(key);
}
}
package cn.mrcast.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Properties;
importorg.apache.commons.dbcp.BasicDataSource;
importorg.apache.commons.dbcp.BasicDataSourceFactory;
import org.apache.commons.dbutils.DbUtils;
importorg.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
importorg.apache.commons.dbutils.handlers.ArrayListHandler;
importorg.apache.commons.dbutils.handlers.BeanHandler;
importorg.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
importorg.apache.commons.dbutils.handlers.KeyedHandler;
importorg.apache.commons.dbutils.handlers.MapHandler;
importorg.apache.commons.dbutils.handlers.MapListHandler;
importorg.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;
/**
* 通过commonsdbUtils进行数据库的简单访问和实用,版本： dbUtils1.6,Oracle驱动：class12；QueryRunner 代码实例
*
*@author 范芳铭
*/
public class DBUtils {
staticConfigurationUsage propManager = null;
privatestatic BasicDataSource dataSourceO2O = null;
privatestatic BasicDataSource dataSourceBM = null;
//连接O2O数据库
publicstatic void initO2O() {
if(dataSourceO2O != null) {
try{
dataSourceO2O.close();
}catch (Exception e) {
e.printStackTrace();
}
dataSourceO2O= null;
}
try{
Propertiesp = new Properties();
p.setProperty("driverClassName",propManager.getInstance().getProperty("jdbc.o2o.driverClassName"));
p.setProperty("url",propManager.getInstance().getProperty("jdbc.o2o.url"));
p.setProperty("password",propManager.getInstance().getProperty("jdbc.o2o.username"));
p.setProperty("username",propManager.getInstance().getProperty("jdbc.o2o.password"));
p.setProperty("maxActive",propManager.getInstance().getProperty("jdbc.o2o.maxActive"));
p.setProperty("maxIdle",propManager.getInstance().getProperty("jdbc.o2o.maxIdle"));
p.setProperty("maxWait",propManager.getInstance().getProperty("jdbc.o2o.maxWait"));
dataSourceO2O= (BasicDataSource) BasicDataSourceFactory
.createDataSource(p);
}catch (Exception e) {
e.printStackTrace();
}
}
//连接便民数据库
publicstatic void initBM() {
if(dataSourceBM != null) {
try{
dataSourceBM.close();
}catch (Exception e) {
e.printStackTrace();
}
dataSourceBM= null;
}
try{
Propertiesp = new Properties();
p.setProperty("driverClassName",propManager.getInstance().getProperty("jdbc.bm.driverClassName"));
p.setProperty("url",propManager.getInstance().getProperty("jdbc.bm.url"));
p.setProperty("password",propManager.getInstance().getProperty("jdbc.bm.username"));
p.setProperty("username",propManager.getInstance().getProperty("jdbc.bm.password"));
p.setProperty("maxActive",propManager.getInstance().getProperty("jdbc.bm.maxActive"));
p.setProperty("maxIdle",propManager.getInstance().getProperty("jdbc.bm.maxIdle"));
p.setProperty("maxWait",propManager.getInstance().getProperty("jdbc.bm.maxWait"));
dataSourceBM= (BasicDataSource) BasicDataSourceFactory
.createDataSource(p);
}catch (Exception e) {
e.printStackTrace();
}
}
publicstatic synchronized Connection getConnO2O() throws SQLException {
if(dataSourceO2O == null) {
initO2O();
}
Connectionconn = null;
if(dataSourceO2O != null) {
conn= dataSourceO2O.getConnection();
}
returnconn;
}
publicstatic synchronized Connection getConnBM() throws SQLException {
if(dataSourceBM == null) {
initBM();
}
Connectionconn = null;
if(dataSourceBM != null) {
conn= dataSourceBM.getConnection();
}
returnconn;
}
//内部使用和实例
privateQueryRunner runner = null;
publicDBUtils() {
initO2O();
runner= new QueryRunner(dataSourceO2O, true); //默认用O2O
}
publicstatic void main(String[] args) throws SQLException {
DBUtilsusage = new DBUtils();
usage.batch();
usage.fillStatement();
usage.query();
usage.closeDataSource();
}
privatevoid batch() {
Stringsql = "INSERT INTO tb_user (USERNAME, PASSWORD) values (?, ?)";
try{
System.out.println(StringUtils.center("TestQueryRunner batch", 50,
‘-‘));
int[]result = runner.batch(sql, new Object[][] {
{"范芳铭1", "ffm1" }, { "范芳铭2","ffm2" },
{"范芳铭3", "ffm3" }, { "范芳铭4","ffm" } });
System.out.printf("batch运行结果:", Arrays.toString(result));
}catch (SQLException ex) {
DbUtils.printStackTrace(ex);
}
}
privatevoid closeDataSource() throws SQLException {
((BasicDataSource)dataSourceO2O).close();
}
privatevoid fillStatement() throws SQLException {
Stringsql = "INSERT INTO tb_user (USERNAME, PASSWORD) VALUES (?, ?)";
Connectionconn = null;
try{
System.out.println(StringUtils.center("TestQueryRunner fillStatement", 50,
‘-‘));
conn= dataSourceO2O.getConnection();
PreparedStatementpsmt = conn.prepareStatement(sql);
runner.fillStatementWithBean(psmt,
newTb_user("范芳铭pstm", "ffm"), "username","password");
System.out.println("成功执行数据："+ psmt.executeUpdate() + " 条");
}catch (SQLException ex) {
DbUtils.printStackTrace(ex);
ex.printStackTrace();
}finally {
DbUtils.commitAndCloseQuietly(conn);
}
}
//列举一些主要的查询形态
privatevoid query() throws SQLException {
System.out.println(StringUtils
.center("TestQueryRunner query", 50, ‘-‘));
Stringsql = "SELECT * FROM tb_user WHERE rownum < ? ";
//把ResultSet第一行包装成Object[]
System.out.println(" Test QueryRunner query, ArrayHandler");
Object[]r1 = runner.query(sql, new ArrayHandler(), "3");
System.out.println(" " + Arrays.deepToString(r1));
//把ResultSet包装成List<Object[]>
System.out.println(" Test QueryRunner query,ArrayListHandler");
List<Object[]>r2 = runner.query(sql, new ArrayListHandler(), "3");
System.out.println(" " + Arrays.deepToString(r2.toArray()));
//把ResultSet第一行包装成一个List<JavaBean>；
System.out.println("Test QueryRunner query, BeanListHandler");
List<Tb_user>r4 = runner.query(sql, new BeanListHandler<Tb_user>(
Tb_user.class),"3");
System.out.println(" " + Arrays.deepToString(r4.toArray()));
//包装ResultSet，以Map<Object,Map<String,Object>>对象形式返回，第一个Object是指定的列值，第二个Map中String是列名且对大小写不敏感
System.out.println("TestQueryRunner query, KeyedHandler");
Map<Object,Map<String, Object>> r6 = runner.query(sql,
newKeyedHandler(2), "3");
System.out.println(" " + r6.toString());
//把ResultSet包装成List<Map<String,Object>>>，Map的key对大小写不敏感
System.out.println("TestQueryRunner query, MapListHandler");
List<Map<String,Object>> r8 = runner.query(sql, new MapListHandler(),
"3");
System.out.println(" " + r8.toString());
}
}
package cn.mrcast.util;
/**
* 通过commonsdbUtils进行数据库的简单访问和实用的javaBean
*QueryRunner 代码实例
*@author 范芳铭
*/
public class Tb_user {
privateString username;
privateString password;
publicTb_user(){
}
publicTb_user(String username, String password) {
this.username= username;
this.password= password;
}
publicString getUsername() {
returnusername;
}
publicvoid setUsername(String username) {
this.username= username;
}
publicString getPassword() {
return password;
}
publicvoid setPassword(String password) {
this.password= password;
}
@Override
publicString toString(){
return"name:" + username + ",pass:" + password;
}
}
7、Bean包
就一个bean，student，在两个数据库中，对应的数据库的数据最好不一样，否则可能看不出效果。
package cn.mrcast.bean;
/**
* 通过commonsdbUtils进行数据库的简单访问和实用的javaBean
*QueryRunner 代码实例
*@author 范芳铭
*/
public class Student {
privateint id;
privateString name;
private String sex;
private String age;
private int grade;
private String classname;
public int getId() {
returnid;
}
publicvoid setId(int id) {
this.id= id;
}
publicString getName() {
returnname;
}
publicvoid setName(String name) {
this.name= name;
}
publicString getSex() {
returnsex;
}
publicvoid setSex(String sex) {
this.sex= sex;
}
publicString getAge() {
returnage;
}
publicvoid setAge(String age) {
this.age= age;
}
publicint getGrade() {
returngrade;
}
publicvoid setGrade(int grade) {
this.grade= grade;
}
publicString getClassname() {
returnclassname;
}
publicvoid setClassname(String classname) {
this.classname= classname;
}
}
8、Action包
两个，一个访问A数据库，一个访问B数据库
package cn.mrcast.action;
import java.util.ArrayList;
import java.util.List;
importcom.opensymphony.xwork2.ActionSupport;
import cn.mrcast.bean.Student;
import cn.mrcast.dao.ShowAllDAO;
public class ShowAllAction extendsActionSupport {
List<Student>list = new ArrayList<Student>();
ShowAllDAOsd = new ShowAllDAO();
publicList<Student> getList() {
returnlist;
}
publicString execute() {
list= sd.useRunnerO2O_all();
return"success";
}
}
package cn.mrcast.action;
import java.util.ArrayList;
import java.util.List;
importcom.opensymphony.xwork2.ActionSupport;
import cn.mrcast.bean.Student;
import cn.mrcast.dao.ShowAllDAO;
public class ShowBMAction extendsActionSupport {
List<Student>list = new ArrayList<Student>();
ShowAllDAOsd = new ShowAllDAO();
publicList<Student> getList() {
returnlist;
}
publicString execute() {
list= sd.useRunnerBM_all();
return"success";
}
}
9、Dao包
一个类，包含了传统的JDBC使用的方式，以及新的方法作为对比
package cn.mrcast.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
importorg.apache.commons.dbutils.QueryRunner;
importorg.apache.commons.dbutils.handlers.BeanListHandler;
import cn.mrcast.bean.Student;
import cn.mrcast.util.DBUtils;
import cn.mrcast.util.Tb_user;
/**
* 通过commonsdbUtils进行数据库的简单访问和实用的javaBean,
* 放了一个传统的数据库访问对象获取的样例作为对比
*QueryRunner 代码实例
*@author 范芳铭
*/
public class ShowAllDAO {
privateQueryRunner runnerO2O = null;
privateQueryRunner runnerBM = null;
//如果不用 runner的方法，比较繁琐，不推荐使用
publicList<Student> ShowAll() {
List<Student>list = new ArrayList<Student>();
Connectioncon = null;
try{
con= DBUtils.getConnO2O();
Stringsql = "select id,name,sex,age,grade,classname from tb_student";
PreparedStatementps = con.prepareStatement(sql);
ResultSetrs = ps.executeQuery();
while(rs.next()) {
Studentst = new Student();
st.setId(rs.getInt("id"));
st.setName(rs.getString("name"));
st.setSex(rs.getString("sex"));
st.setAge(rs.getString("age"));
st.setGrade(rs.getInt("grade"));
st.setClassname(rs.getString("classname"));
list.add(st);
}
}catch (SQLException e) {
System.out.println(e.getMessage());
}
returnlist;
}
publicList<Student> useRunnerO2O_all() {
List<Student>list = new ArrayList<Student>();
//oracle数据库连接初始化方法
runnerO2O= new QueryRunner(true);
Connectioncon = null;
try{
con= DBUtils.getConnO2O();
Stringsql = "select id,name,sex,age,grade,classname from tb_student";
list= runnerO2O.query(con,sql, new BeanListHandler<Student>(Student.class));
System.out.println(" " +Arrays.deepToString(list.toArray()));
}catch (SQLException e) {
System.out.println(e.getMessage());
}finally{
DbUtils.closeQuietly(con);
}
returnlist;
}
publicList<Student> useRunnerBM_all() {
List<Student>list = new ArrayList<Student>();
//oracle数据库连接初始化方法
runnerBM= new QueryRunner(true);
Connectioncon = null;
try{
con= DBUtils.getConnBM();
Stringsql = "select id,name,sex,age,grade,classname from tb_student";
list= runnerBM.query(con,sql, new BeanListHandler<Student>(Student.class));
System.out.println(" " + Arrays.deepToString(list.toArray()));
}catch (SQLException e) {
System.out.println(e.getMessage());
}finally{
DbUtils.closeQuietly(con);
}
returnlist;
}
}
10、数据库配置文件
jdbc.o2o.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.o2o.url=jdbc:oracle:thin:@192.168.1.1:1521:ffm
jdbc.o2o.username=ffm
jdbc.o2o.password=ffm
jdbc.o2o.maxActive=20
jdbc.o2o.maxIdle=5
jdbc.o2o.maxWait=200
jdbc.bm.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.bm.url=jdbc:oracle:thin:@192.168.1.2:1521:TEST
jdbc.bm.username=test
jdbc.bm.password=test
jdbc.bm.maxActive=20
jdbc.bm.maxIdle=5
jdbc.bm.maxWait=200
11、struts.xml配置文件
<?xml version="1.0"encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC "-//ApacheSoftware Foundation//DTD Struts Configuration 2.1//EN" "http://struts.apache.org/dtds/struts-2.1.dtd">
<struts>
<package name="first"extends="struts-default"><!-- 定义一个package -->
<!-- 对action返回结果的配置-->
<actionname="showall" class="cn.mrcast.action.ShowAllAction">
<resultname="success">/index.jsp</result>
</action>
<actionname="showBMall" class="cn.mrcast.action.ShowBMAction">
<resultname="success">/index.jsp</result>
</action>
</package>
</struts>
12、web文件Index.jsp
<%@ page language="java"import="java.util.*" pageEncoding="GBK"%>
<%@taglib uri="/struts-tags"prefix="s"%>
<%
String path = request.getContextPath();
String basePath =request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTDHTML 4.01 Transitional//EN">
<html>
<head>
<base href=http://www.mamicode.com/"">
<title>My JSP ‘index.jsp‘ startingpage</title>
<metahttp-equiv="pragma" content="no-cache">
<metahttp-equiv="cache-control" content="no-cache">
<metahttp-equiv="expires" content="0">
<metahttp-equiv="keywords"content="keyword1,keyword2,keyword3">
<meta http-equiv="description"content="This is my page">
<style type="text/css">
<!--
.STYLE1 {
font-size:18px;
font-weight:bold;
}
-->
</style>
</head>
<body>
<form action="showall.action" method="post"name="form1">
<div align="center">
<p class="STYLE1"> </p>
<p class="STYLE1">连接两个数据库，找学生成绩统计</p>
<table width="392" border="1">
<tr bgcolor="#FFCCFF">
<td width="54"><div align="center">编号</div></td>
<td width="58"><div align="center">姓名</div></td>
<td width="52"><div align="center">性别</div></td>
<td width="44"><div align="center">年龄</div></td>
<td width="91"><div align="center">班级</div></td>
<td width="53"><div align="center">成绩</div></td>
<td width="53"><div align="center">级别</div></td>
</tr>
</table>
<table width="392" border="1">
<s:iterator id="student" value=http://www.mamicode.com/"list">
<tr>
<td width="54"><divalign="center"><s:property value=http://www.mamicode.com/"id"/>
<td width="58"><divalign="center"><s:propertyvalue=http://www.mamicode.com/"name"/>
<td width="52"><divalign="center"><s:propertyvalue=http://www.mamicode.com/"sex"/>
<td width="44"><divalign="center"><s:property value=http://www.mamicode.com/"age"/>
<td width="91"><divalign="center"><s:propertyvalue=http://www.mamicode.com/"classname"/>
<td width="53"><divalign="center"><s:property value=http://www.mamicode.com/"grade"/>分
<td width="53"><divalign="center"><s:if test = "#student.grade >= 60">及格</s:if>
<s:else> 不及格</s:else>
</div></td>
</tr>
</s:iterator>
<tr align="center">
<td colspan="2" bgcolor="#FFCCFF"><inputtype="submit" value=http://www.mamicode.com/"检索O2O数据库"/>>
</td>
</tr>
</table>
</div>
</form>
<form action="showBMall.action" method="post"name="form1">
<div align="center">
<table width="392" border="1">
<tr align="center">
<td colspan="2" bgcolor="#FFCCFF"><inputtype="submit" value=http://www.mamicode.com/"检索bm数据库"/>>
</td>
</tr>
</table>
</div>
</form>
调试信息的输出：
<s:debug></s:debug>
</body>
</html>
利用apache commons组件实现WEB应用跨多数据库