Spring 多数据库连接的兑现
Spring 多数据库连接的实现
１.使用Spring的配置文件完成多数据库连接：
1.1 applicationContext.xml [spring核心配置文件]
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
<!-- 知识管理数据库 -->
<bean id="km_source" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"></property>
<property name="url" value="jdbc:sqlserver://localhost:1433;databaseName=docmanager"></property>
<property name="username" value="sa"></property>
<property name="password" value="123"></property>
</bean>
<!-- 框架数据库 -->
<bean id="ma_source" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"></property>
<property name="url" value="jdbc:sqlserver://localhost:1433;databaseName=framemanager"></property>
<property name="username" value="sa"></property>
<property name="password" value="123"></property>
<property name="maxActive" value="100000"></property>
<property name="maxIdle" value="20"></property>
<property name="maxWait" value="1000"></property>
</bean>
<!-- 用户组织数据库 -->
<bean id="um_source" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"></property>
<property name="url" value="jdbc:sqlserver://localhost:1433;databaseName=usermanager"></property>
<property name="username" value="sa"></property>
<property name="password" value="123"></property>
</bean>
<!-- 工作流数据库 -->
<bean id="wfe_source" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"></property>
<property name="url" value="jdbc:sqlserver://localhost:1433;databaseName=wfemanager"></property>
<property name="username" value="sa"></property>
<property name="password" value="123"></property>
</bean>
<bean id="myDataSource" class="com.smartcom.util.jdbcUtil.DynamicDataSource">
<property name="targetDataSources">
<map>
<entry key="ma_source" value-ref="ma_source"/>
<entry key="um_source" value-ref="um_source"/>
</map>
</property>
<property name="defaultTargetDataSource" ref="km_source"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" abstract="false" lazy-init="false" autowire="default">
<property name="dataSource" ref="myDataSource"/>
</bean>
<import resource="config/ma/beans_pojo.xml"/>
<import resource="config/ma/beans_dao.xml"/>
<import resource="config/ma/beans_service.xml"/>
<import resource="config/ma/beans_action.xml"/>
<import resource="config/ma/beans_ajax.xml"/>
</beans>
１.２ JdbcContext.java [建立一个获得和设置上下文的类]
package com.smartcom.util.jdbcUtil;
/**
* 建立一个获得和设置上下文的类
* @author Liyongbin
* 此类正在使用
*/
public class JdbcContext {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
/**
* XXXXX
*/
public static void setJdbcType(String jdbcType) {
contextHolder.set(jdbcType);
}
/**
* XXXXX
*/
public static String getJdbcType() {
return (String) contextHolder.get();
}
/**
* 框架数据库
*/
public static void setMa_source(){
setJdbcType("ma_source");
}
/**
* 知识管理数据库
*/
public static void setKm_source(){
setJdbcType("km_source");
}
/**
* 知识管理数据库
*/
public static void setUm_source(){
setJdbcType("um_source");
}
/**
* 工作流引擎数据库
*/
public static void setWfe_source(){
setJdbcType("wfe_source");
}
/**
* 设置主连接
*/
public static void setMaster(){
clearJdbcType();
}
/**
* 清空连接类型
*/
public static void clearJdbcType() {
contextHolder.remove();
}
}
1.3 IGroupinfoDao.java [ＤＡＯ层接口]
package com.smartcom.ma.dao;
import com.smartcom.ma.pojo.Groupinfo;
import com.smartcom.ma.util.IBaseDao;
public interface IGroupinfoDao extends IBaseDao<Groupinfo> {
}
1.4 GroupinfoDaoImpl.java [ＤＡＯ层接口实现]
package com.smartcom.ma.dao.impl;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import com.smartcom.ma.dao.IGroupinfoDao;
import com.smartcom.ma.pojo.Groupinfo;
import com.smartcom.util.jdbcUtil.JdbcContext;
public class GroupinfoDaoImpl implements IGroupinfoDao {
private Groupinfo groupinfo ;
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public Groupinfo getGroupinfo() {
return groupinfo;
}
public void setGroupinfo(Groupinfo groupinfo) {
this.groupinfo = groupinfo;
}
/**
* 【查询】所有部门职位信息
* @author Liyongbin [May 4, 2012 8:17:20 PM]
*/
@SuppressWarnings("unchecked")
public List<Groupinfo> findAll() throws Exception {
JdbcContext.setUm_source();//设置数据源
List<Groupinfo> groupList = new ArrayList<Groupinfo>();
Groupinfo groupinfo = null;
String sql ="select sys_id,sys_createtime,sys_creator,g_name,g_code,parentId,uuid,";
sql += "g_level,g_state,sys_type,g_type,g_desc,own_subject,remark from groupinfo ";
sql += "order by sid";
try {
List rows=jdbcTemplate.queryForList(sql);
Iterator it=rows.iterator();
while(it.hasNext()){
Map groupMap=(Map)it.next();
groupinfo = new Groupinfo();
groupinfo.setSys_id((Integer)groupMap.get("sys_id"));
groupinfo.setSys_createtime(groupMap.get("sys_createtime").toString());
groupinfo.setSys_creator((String)groupMap.get("sys_creator"));
groupinfo.setG_name((String)groupMap.get("g_name"));
groupinfo.setG_code((String)groupMap.get("g_code"));
groupinfo.setParentId((String)groupMap.get("parentId"));
groupinfo.setUuid((String)groupMap.get("uuid"));
groupinfo.setG_level((Integer)groupMap.get("g_level"));
groupinfo.setG_state((Integer)groupMap.get("g_state"));
groupinfo.setSys_type((Integer)groupMap.get("sys_type"));
groupinfo.setG_type((Integer)groupMap.get("g_type"));
groupinfo.setG_desc((String)groupMap.get("g_desc"));
groupinfo.setOwn_subject((Integer)groupMap.get("own_subject"));
groupinfo.setRemark((String)groupMap.get("remark"));
groupList.add(groupinfo);
}
} catch (Exception e) {
e.printStackTrace();
}
return groupList;
}
/**
* 【新建】部门职位
* @author Liyongbin [May 5, 2012 8:02:24 AM]
*/
public int save(Groupinfo groupinfo) throws Exception {
Object [] args = null;
int ret = -1;
if(null==groupinfo){
return ret;
}
String sql ="insert into groupinfo (sys_id,sys_createtime,sys_creator,g_name,g_code,parentId,uuid,g_level,g_state,sys_type,g_type,g_desc,own_subject,remark)";
sql +=" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
try {
args = new Object[13];
args[0] = groupinfo.getSys_id();
args[1] = groupinfo.getSys_createtime();
args[2] = groupinfo.getSys_creator();
args[3] = groupinfo.getG_name();
args[4] = groupinfo.getG_code();
args[5] = groupinfo.getParentId();
args[6] = groupinfo.getUuid();
args[7] = groupinfo.getG_level();
args[8] = groupinfo.getG_state();
args[9] = groupinfo.getSys_type();
args[10]= groupinfo.getG_type();
args[11]= groupinfo.getG_desc();
args[12]= groupinfo.getOwn_subject();
args[13]= groupinfo.getRemark();
ret = jdbcTemplate.update(sql, args);
} catch (Exception e) {
System.out.println(e.getMessage());
}
return ret;
}
/**
* 【删除】部门职位
* @author Liyongbin [May 19, 2012 6:58:41 AM]
*/
public int delete(int id) throws Exception {
Integer ret = -1;
String sql = "delete from groupinfo where sys_id=?";
try {
ret = jdbcTemplate.update(sql,new Object[]{id});
} catch (Exception e) {
System.out.println(e.getMessage());
}
return ret;
}
/**
* 【查询】部门职位-->sysId
* @author Liyongbin [May 4, 2012 8:17:20 PM]
*/
@SuppressWarnings("unchecked")
public Groupinfo findById(int id) throws Exception {
JdbcContext.setUm_source();//设置数据源
Groupinfo groupinfo = null;
String sql ="select sys_id,sys_createtime,sys_creator,g_name,g_code,parentId,uuid,";
sql += "g_level,g_state,sys_type,g_type,g_desc,own_subject,remark from groupinfo";
try {
List rows=jdbcTemplate.queryForList(sql);
Iterator it=rows.iterator();
if(it.hasNext()){
Map groupMap=(Map)it.next();
groupinfo = new Groupinfo();
groupinfo.setSys_id((Integer)groupMap.get("sys_id"));
groupinfo.setSys_createtime(groupMap.get("sys_createtime").toString());
groupinfo.setSys_creator((String)groupMap.get("sys_creator"));
groupinfo.setG_name((String)groupMap.get("g_name"));
groupinfo.setG_code((String)groupMap.get("g_code"));
groupinfo.setParentId((String)groupMap.get("parentId"));
groupinfo.setUuid((String)groupMap.get("uuid"));
groupinfo.setG_level((Integer)groupMap.get("g_level"));
groupinfo.setG_state((Integer)groupMap.get("g_state"));
groupinfo.setSys_type((Integer)groupMap.get("sys_type"));
groupinfo.setG_type((Integer)groupMap.get("g_type"));
groupinfo.setG_desc((String)groupMap.get("g_desc"));
groupinfo.setOwn_subject((Integer)groupMap.get("own_subject"));
groupinfo.setRemark((String)groupMap.get("remark"));
}
} catch (Exception e) {
e.printStackTrace();
}
return groupinfo;
}
/**
* 【修改】部门职位
* @author Liyongbin [May 5, 2012 8:02:24 AM]
*/
public int update(Groupinfo groupinfo) throws Exception {
int ret = -1;
Object [] args = null;
String sql = "update groupinfo set sys_createtime=?,sys_creator=?,g_name=?,g_code=?,parentId=?,uuid=?,";
sql += "g_level=?,g_state=?,sys_type=?,g_type=?,g_desc=?,own_subject=?,remark=?";
sql += "where sys_id=? ";
try {
args = new Object[14];
args[0] = groupinfo.getSys_createtime();
args[1] = groupinfo.getSys_creator();
args[2] = groupinfo.getG_name();
args[3] = groupinfo.getG_code();
args[4] = groupinfo.getParentId();
args[5] = groupinfo.getUuid();
args[6] = groupinfo.getG_level();
args[7] = groupinfo.getG_state();
args[8] = groupinfo.getSys_type();
args[9]= groupinfo.getG_type();
args[10]= groupinfo.getG_desc();
args[11]= groupinfo.getOwn_subject();
args[12]= groupinfo.getRemark();
args[13]= groupinfo.getSys_id();
ret = jdbcTemplate.update(sql,args);
} catch (Exception e) {
System.out.println(e.getMessage());
}
return ret;
}
}