spring+hibernate多数据源+动态切换 事务 lazyload一应俱全

1、使用场景：

?以自己为例，现有一个后台管理系统管理着游戏数据。以前是一个后台地址对应一个后台管理一个游戏后台，现在改进为一个后台管理多个游戏数据。在登录或者其他地方切换下就可以查询数据。

?

2、步骤分为：多数据源配置和动态切换配置和事务+OpenSessionInViewFilter配置

?

先不考虑动态切换配置，一个datasource对应一个sessionFactory对应一个hibernateTemplate对应一个transactionManager对应一个OpenSessionInViewFilter

?

在多数据源模式下有多个sessionFactory，所以配置hibernateTemplate，transactionManager，OpenSessionInViewFilter的时候要指定sessionFactory！

?

<filter><filter-name>OpenSessionInViewFilter</filter-name><filter-class>org.springframework.orm.hibernate3.support.OpenSessionInViewFilter</filter-class><init-param><param-name>sessionFactoryBeanName</param-name><param-value>sessionFactory1</param-value></init-param></filter><filter-mapping><filter-name>OpenSessionInViewFilter</filter-name><url-pattern>/*</url-pattern></filter-mapping>

?<filter>

<filter-name>OpenSessionInViewFilter4</filter-name><filter-class>org.springframework.orm.hibernate3.support.OpenSessionInViewFilter</filter-class><init-param><param-name>sessionFactoryBeanName</param-name><param-value>sessionFactory4</param-value></init-param></filter><filter-mapping><filter-name>OpenSessionInViewFilter4</filter-name><url-pattern>/*</url-pattern></filter-mapping>

?<bean id="transactionManager4"

ref="sessionFactory4"></property></bean>

?

其中transactionManager有一个问题，就是配置aop的时候要注意2个transactionManager不重叠。

?

3、在dao中注入不同的sessionfactory/hibernateTemplate 操作各自所属数据源数据

HibernateTemplate hibernateTemplate;@Resource(name = "hibernateTemplate4")public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {this.hibernateTemplate = hibernateTemplate;}

?HibernateTemplate hibernateTemplate;

@Resource(name = "hibernateTemplate1")public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {this.hibernateTemplate = hibernateTemplate;}

?

4、动态切换数据源（用来切换服务器）

public class DynamicDataSource extends AbstractRoutingDataSource {private static final Logger log = Logger.getLogger(DynamicDataSource.class);@Overrideprotected Object determineCurrentLookupKey() {String i=  CustomerContextHolder.getCustomerType();return i;}}

?<bean id="dataSource00" name="code"><property name="targetDataSources"><map key-type="java.lang.String"><entry key="2" value-ref="dataSource2" /><entry key="1" value-ref="dataSource1" /></map></property><property name="defaultTargetDataSource" ref="dataSource1" /></bean>

?动态切换很简单，datasource配置改为AbstractRoutingDataSource实现类指定一个defaultTargetDataSource和targetDataSources

在需要切换的地方

public class CustomerContextHolder {private static final ThreadLocal contextHolder = new ThreadLocal();public static void setCustomerType(String customerType) {contextHolder.set(customerType);}public static String getCustomerType() {return (String) contextHolder.get();}public static void clearCustomerType() {contextHolder.remove();}}

?CustomerContextHolder.setCustomerType("2");//切换key为2的数据源

User uu = userService.get(User.class, "id = ?", 3l);

就这样查询出来的就是第二个数据源的数据了

?

?不出意外的话，是可以看到效果的。如果出现了事务问题就要查找下是否sessionfactory和aop配置正确。如果你把一个service类在aop中配置2次会出现错误的。

多数据源：管理系统数据源+游戏数据源

动态切换：游戏数据各服务器之间切换。

/* * Copyright 2008 biaoping.yin * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */package org.frameworkset.spi.mvc;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import org.junit.Test;import com.frameworkset.common.poolman.Record;import com.frameworkset.common.poolman.SQLExecutor;import com.frameworkset.common.poolman.handle.NullRowHandler;import com.frameworkset.common.poolman.handle.RowHandler;import com.frameworkset.util.ListInfo;public class SimpleApiTest {@Testpublic void insertOpera()throws SQLException{ListBean lb = new ListBean();lb.setFieldLable("tttt");lb.setFieldName("testttt");lb.setFieldType("int");lb.setIsprimaryKey(false);lb.setRequired(true);lb.setSortorder("ppp");lb.setFieldLength(20);lb.setIsvalidated(6);//用List存放Bean，在某特定的连接池中进行crud操作List<ListBean> beans = new ArrayList<ListBean>();beans.add(lb);String sql = "insert into LISTBEAN(ID,FIELDNAME,FIELDLABLE,FIELDTYPE,SORTORDER,ISPRIMARYKEY,REQUIRED,FIELDLENGTH,ISVALIDATED) " +"values(#[id],#[fieldName],#[fieldLable],#[fieldType],#[sortorder]," +"#[isprimaryKey],#[required],#[fieldLength],#[isvalidated])";SQLExecutor.insertBeans("mysql",sql,beans); SQLExecutor.insertBean("mysql", sql, lb);SQLExecutor.insertBeans("mysql", sql, beans);SQLExecutor.insertBean(sql, lb);sql ="insert into LISTBEAN(ID,FIELDNAME,FIELDLABLE,FIELDTYPE) " +"values(?,?,?,?)";//SQLExecutor.insert(sql,122,lb.getFieldName(),lb.getFieldLable(),lb.getFieldType()); //SQLExecutor.insertWithDBName("mysql", sql,101,"insertOpreation","ttyee","int");}@Testpublic void updateOpera() throws SQLException{//在某特定的连接池中直接crud对象ListBean bean = new ListBean();bean.setId(88);bean.setFieldLable("tttt");bean.setFieldName("test");bean.setFieldType("int");bean.setIsprimaryKey(false);bean.setRequired(true);bean.setSortorder("ppp");bean.setFieldLength(20);bean.setIsvalidated(6);List<ListBean> beans = new ArrayList<ListBean>();String sql ="";beans.add(bean);sql ="update LISTBEAN set FIELDNAME='yyyy' where ID=#[id]"; SQLExecutor.updateBeans("mysql", sql, beans);sql ="update LISTBEAN set FIELDNAME=#[fieldName] where ID=#[id]"; SQLExecutor.updateBean(sql,bean);sql ="update LISTBEAN set FIELDNAME=#[fieldName] where ID=#[id]"; SQLExecutor.updateBean("mysql",sql,bean);sql ="update LISTBEAN set FIELDNAME=#[fieldName] where ID=#[id]"; SQLExecutor.updateBeans(sql,beans);sql = "update LISTBEAN set FIELDNAME=? where ID=?";SQLExecutor.update(sql, "mytest",100);sql = "update LISTBEAN set FIELDNAME=? where ID=?";SQLExecutor.updateWithDBName("mysql", sql, "zhansans",101);}@Testpublic void deleteOpera() throws SQLException{ //在特定的连接池中对数组对象进行crudListBean lb = new ListBean();lb.setId(85);lb.setFieldLable("tttt");lb.setFieldName("testttt");lb.setFieldType("int");lb.setIsprimaryKey(false);lb.setRequired(true);lb.setSortorder("ppp");lb.setFieldLength(20);lb.setIsvalidated(6);ListBean lb2 = new ListBean();lb2.setId(15);lb2.setFieldName("this is lb2");List<ListBean> beans = new ArrayList<ListBean>();beans.add(lb);beans.add(lb2); String sql = "";sql = "delete from LISTBEAN where ID=?";SQLExecutor.delete(sql,68);sql = "delete from LISTBEAN where ID=?";SQLExecutor.deleteByKeys(sql,67);sql ="delete from LISTBEAN where ID=#[id]"; SQLExecutor.deleteBean(sql,lb);sql = "delete from LISTBEAN where ID=#[id]";SQLExecutor.deleteBeans(sql, beans);sql ="delete from LISTBEAN where ID=#[id]"; SQLExecutor.deleteBean("mysql",sql,lb);sql ="delete from LISTBEAN where ID=#[id]"; SQLExecutor.deleteBeans("mysql",sql,beans);sql = "delete from LISTBEAN where ID=?";SQLExecutor.deleteWithDBName("mysql", sql, 3);sql = "delete from LISTBEAN where FIELDNAME=?";SQLExecutor.deleteByKeysWithDBName("mysql", sql,"pppp");}@Testpublic void queryOprea() throws SQLException{List<ListBean> beans = null; String sql ="select * from LISTBEAN where ID=?";sql = "select * from LISTBEAN where id=?";List<ListBean> lbs = (List<ListBean>) SQLExecutor.queryList(ListBean.class, sql,22);sql = "select * from LISTBEAN where fieldName=?";beans = (List<ListBean>) SQLExecutor.queryListWithDBName(ListBean.class,"mysql",sql,"testttt");for(int i=0;i<beans.size();i++)System.out.println(beans.get(i).getId());sql = "select * from LISTBEAN where fieldName=?";List<ListBean> dbBeans = (List<ListBean>) SQLExecutor.queryListWithDBName(ListBean.class, "mysql", sql, "testttt");for(int i=0;i<dbBeans.size();i++)System.out.println(dbBeans.get(i).getFieldName());sql = "select * from LISTBEAN where fieldName=? and id=?";ListBean bean = SQLExecutor.queryObject(ListBean.class, sql,"object",22);System.out.println(bean.getId());sql="select * from LISTBEAN where FIELDNAME=? or id=?"; lbs = (List<ListBean>) SQLExecutor.queryList(ListBean.class, sql, "testttt",100);sql = "select FIELDNAME from LISTBEAN where ID=?";String lbs1 = SQLExecutor.queryField(sql,2);System.out.println(lbs1);sql="select FIELDNAME from LISTBEAN where ID=?";String result = SQLExecutor.queryFieldWithDBName("mysql", sql, 100);System.out.println(result);sql = "select * from LISTBEAN where ID=?";ListBean lb = (ListBean)SQLExecutor.queryObjectWithDBName(ListBean.class,"mysql",sql,20);sql="select * from LISTBEAN where ID<? and ID>?";ListInfo lif = SQLExecutor.queryListInfo(ListBean.class, sql, 0, 10, 20,10);beans = lif.getDatas();for(int i=0;i<beans.size();i++)System.out.println(beans.get(i).getFieldName()+"......."); bean = new ListBean(); bean.setFieldName("testttt"); bean.setFieldLable("lisi"); sql ="select * from LISTBEAN where ID=?";//bean = (ListBean)SQLExecutor.queryObjectBean(ListBean.class, sql, bean); sql ="select * from LISTBEAN where FIELDNAME=#[fieldName]"; result = SQLExecutor.queryFieldBean(sql, bean);System.out.println(result);result = SQLExecutor.queryFieldBeanWithDBName("mysql", sql, bean);System.out.println(result);beans = (List<ListBean>) SQLExecutor.queryListBean(ListBean.class, sql, bean);for(int i=0;i<beans.size();i++)System.out.println(beans.get(i).getId());beans = (List<ListBean>) SQLExecutor.queryListBeanWithDBName(ListBean.class, "mysql", sql, bean);for(int i=0;i<beans.size();i++)System.out.println(beans.get(i).getId());sql = "select * from LISTBEAN where ID>?";lif = SQLExecutor.queryListInfoWithDBName(ListBean.class, "mysql", sql, 0, 10,80);for(int i=0;i<beans.size();i++)System.out.println(beans.get(i).getFieldName()+"^^^^^");sql = "select * from LISTBEAN where FIELDNAME=#[fieldName]";lif = SQLExecutor.queryListInfoBean(ListBean.class, sql, 0, 4, bean);for(int i=0;i<beans.size();i++)System.out.println(beans.get(i).getId());lif = SQLExecutor.queryListInfoBeanWithDBName(ListBean.class, "mysql", sql, 0, 5, bean);for(int i=0;i<beans.size();i++)System.out.println(beans.get(i).getId());bean = SQLExecutor.queryObjectBeanWithDBName(ListBean.class, "mysql", sql, bean);System.out.println(bean.getId());}@Testpublic void rowHandlerQuery() throws SQLException{String sql ="";List<ListBean> beans = null;ListBean bean = new ListBean();ListInfo lif = new ListInfo();final List<ListBean> lbs = new ArrayList<ListBean>(); bean.setFieldName("testttt"); bean.setFieldLable("lisi"); sql ="select * from LISTBEAN where ID=?"; SQLExecutor.queryByNullRowHandler(new NullRowHandler(){@Overridepublic void handleRow(Record record) throws Exception {ListBean lb = new ListBean();lb.setId(record.getInt("id"));lb.setFieldName(record.getString("fieldName"));lbs.add(lb);}}, sql, 22); System.out.println(lbs.size()+"9999999"); sql = "select * from LISTBEAN where ID>?";beans = (List<ListBean>) SQLExecutor.queryListByRowHandler(new RowHandler(){@Overridepublic void handleRow(Object rowValue, Record record)throws Exception {System.out.println("queryListByRowHandler test Result**:"+record.getString("fieldName"));ListBean lb = new ListBean();lb.setId(record.getInt("id"));lb.setFieldName(record.getString("fieldName"));lbs.add(lb);}}, ListBean.class, sql, 80);for(int i=0;i<lbs.size();i++)System.out.println(lbs.get(i).getId()+"*****");lbs.clear();System.out.println(lbs.size());lif = SQLExecutor.queryListInfoByRowHandler(new RowHandler<ListBean>(){@Overridepublic void handleRow(ListBean rowValue, Record record)throws Exception {rowValue.setId(record.getInt("id"));rowValue.setFieldName(record.getString("fieldName"));}}, ListBean.class, sql, 0, 10, 20);System.out.println(lif.getTotalSize()+"----");sql = "select * from LISTBEAN where FIELDNAME=#[fieldName]";lbs.clear();beans = (List<ListBean>) SQLExecutor.queryListBeanByRowHandler(new RowHandler<ListBean>(){@Overridepublic void handleRow(ListBean rowValue, Record record)throws Exception {rowValue.setId(record.getInt("id"));rowValue.setFieldName(record.getString("fieldName"));}}, ListBean.class, sql, bean);for(int i=0;i<beans.size();i++)System.out.println(beans.get(i).getId()+" ggg");lbs.clear();beans = (List<ListBean>) SQLExecutor.queryListBeanWithDBNameByRowHandler(new RowHandler<ListBean>(){@Overridepublic void handleRow(ListBean rowValue, Record record)throws Exception {// TODO Auto-generated method stubrowValue.setId(record.getInt("id"));rowValue.setFieldName(record.getString("fieldName"));}}, ListBean.class, "mysql", sql, bean);for(int i=0;i<beans.size();i++)System.out.println(beans.get(i).getId()+" ccccccccc");lbs.clear();lif = (ListInfo) SQLExecutor.queryListInfoBeanByRowHandler(new RowHandler<ListBean>(){@Overridepublic void handleRow(ListBean rowValue, Record record)throws Exception {// TODO Auto-generated method stubrowValue.setId(record.getInt("id"));rowValue.setFieldName(record.getString("fieldName"));}},ListBean.class, sql, 5, 5, bean);beans = lif.getDatas();for(int i=0;i<beans.size();i++)System.out.println(beans.get(i).getId()+" ddddddddddddddddddddddddd");lbs.clear();lif = SQLExecutor.queryListInfoBeanWithDBNameByRowHandler(new RowHandler<ListBean>(){@Overridepublic void handleRow(ListBean rowValue, Record record)throws Exception {// TODO Auto-generated method stubrowValue.setId(record.getInt("id"));rowValue.setFieldName(record.getString("fieldName"));}},ListBean.class, "mysql",sql, 0, 5, bean);for(int i=0;i<lbs.size();i++)System.out.println(lbs.get(i).getId()+" ffff");sql = "select * from LISTBEAN where ID=#[id]";bean.setId(2);ListBean lb1 =SQLExecutor.queryObjectBeanByRowHandler(new RowHandler<ListBean>(){@Overridepublic void handleRow(ListBean rowValue, Record record)throws Exception {// TODO Auto-generated method stubrowValue.setId(record.getInt("id"));rowValue.setFieldName(record.getString("fieldName"));}}, ListBean.class, sql, bean);System.out.println(lb1.getFieldName());sql = "select * from LISTBEAN where ID<?";lbs.clear();lif = SQLExecutor.queryListInfoWithDBNameByRowHandler(new RowHandler(){@Overridepublic void handleRow(Object rowValue, Record record)throws Exception {ListBean lb = new ListBean();lb.setId(record.getInt("id"));lbs.add(lb);lb.setFieldName(record.getString("fieldName"));}},ListBean.class,"mysql", sql, 0, 5, 20);for(int i=0;i<lbs.size();i++)System.out.println(lbs.get(i).getId()+" kkkk");beans = (List<ListBean>) SQLExecutor.queryListWithDBNameByRowHandler(new RowHandler<ListBean>(){@Overridepublic void handleRow(ListBean rowValue, Record record)throws Exception {rowValue.setId(record.getInt("id"));rowValue.setFieldName(record.getString("fieldName"));}}, ListBean.class, "mysql", sql, 20);for(int i=0;i<beans.size();i++)System.out.println(beans.get(i).getFieldName()+" wwwww");ListBean lb3 = SQLExecutor.queryObjectByRowHandler(new RowHandler<ListBean>(){@Overridepublic void handleRow(ListBean rowValue, Record record)throws Exception {rowValue.setId(record.getInt("id"));rowValue.setFieldName(record.getString("fieldName"));}}, ListBean.class, sql, 20);System.out.println(lb3.getFieldName()+"lbbbbb");ListBean lb4 = SQLExecutor.queryObjectWithDBNameByRowHandler(new RowHandler<ListBean>(){@Overridepublic void handleRow(ListBean rowValue, Record record)throws Exception {rowValue.setId(record.getInt("id"));rowValue.setFieldName(record.getString("fieldName"));}}, ListBean.class,"mysql", sql, 20);System.out.println(lb4.getFieldName()+"lb4444");sql = "select * from LISTBEAN where ID=#[id]";ListBean lb2 = SQLExecutor.queryObjectBeanWithDBNameByRowHandler(new RowHandler<ListBean>(){@Overridepublic void handleRow(ListBean rowValue, Record record)throws Exception {// TODO Auto-generated method stubrowValue.setId(record.getInt("id"));rowValue.setFieldName(record.getString("fieldName"));}}, ListBean.class, "mysql", sql, bean);System.out.println(lb2.getId()+"++++");}@Testpublic void nullRowHandlerQuery() throws SQLException{String sql = "";List<ListBean> beans = null;ListBean b = new ListBean(); b.setFieldName("testttt"); b.setFieldLable("lisi"); sql = "select * from LISTBEAN where id>?";beans = null;final List<ListBean> lbs = new ArrayList<ListBean>();ListInfo lif = SQLExecutor.queryListInfoByNullRowHandler(new NullRowHandler(){@Overridepublic void handleRow(Record record) throws Exception {// TODO Auto-generated method stubListBean lb = new ListBean();lb.setId(record.getInt("id"));lb.setFieldName(record.getString("fieldName"));lbs.add(lb);System.out.println("queryListInfoByNullRowHandler test result:"+record.getInt("id"));}}, sql, 0, 5, 10);beans = (List<ListBean>)lif.getDatas();//for(int i=0;i<beans.size();i++)// System.out.println(beans.get(i).getId()+".......");for(int i=0;i<lbs.size();i++)System.out.println(lbs.get(i).getFieldName()+"####");lbs.clear();lif =SQLExecutor.queryListInfoWithDBNameByNullRowHandler(new NullRowHandler(){@Overridepublic void handleRow(Record record) throws Exception {// TODO Auto-generated method stubListBean lb = new ListBean();lb.setId(record.getInt("id"));lb.setFieldName(record.getString("fieldName"));lbs.add(lb);System.out.println("queryListInfoByNullRowHandler test result:"+record.getInt("id"));}},"mysql", sql, 0, 5, 10);for(int i=0;i<lbs.size();i++)System.out.println(lbs.get(i).getFieldName()+"oooooooo");lbs.clear();SQLExecutor.queryWithDBNameByNullRowHandler(new NullRowHandler(){@Overridepublic void handleRow(Record record) throws Exception {// TODO Auto-generated method stubListBean lb = new ListBean();lb.setId(record.getInt("id"));lb.setFieldName(record.getString("fieldName"));lbs.add(lb);}}, "mysql", sql, 80);for(int i=0;i<lbs.size();i++)System.out.println(lbs.get(i).getFieldName()+"ppppp");sql = "select * from LISTBEAN where FIELDNAME=#[fieldName]";lbs.clear();SQLExecutor.queryBeanByNullRowHandler(new NullRowHandler(){@Overridepublic void handleRow(Record record) throws Exception {// TODO Auto-generated method stubListBean lb = new ListBean();lb.setId(record.getInt("id"));lb.setFieldName(record.getString("fieldName"));lbs.add(lb);}}, sql, b);for(int i=0;i<lbs.size();i++)System.out.println(lbs.get(i).getId()+"yyyyy");lbs.clear();SQLExecutor.queryBeanWithDBNameByNullRowHandler(new NullRowHandler(){@Overridepublic void handleRow(Record record) throws Exception {// TODO Auto-generated method stubListBean lb = new ListBean();lb.setId(record.getInt("id"));lb.setFieldName(record.getString("fieldName"));lbs.add(lb);}}, "mysql",sql, b);for(int i=0;i<lbs.size();i++)System.out.println(lbs.get(i).getId()+"rrrrrrr");lbs.clear();lif = SQLExecutor.queryListInfoBeanByNullRowHandler(new NullRowHandler(){@Overridepublic void handleRow(Record record) throws Exception {// TODO Auto-generated method stubListBean lb = new ListBean();lb.setId(record.getInt("id"));lb.setFieldName(record.getString("fieldName"));lbs.add(lb);}}, sql, 0, 5, b);for(int i=0;i<lbs.size();i++)System.out.println(lbs.get(i).getId()+"eeee");SQLExecutor.queryListInfoBeanWithDBNameByNullRowHandler(new NullRowHandler(){@Overridepublic void handleRow(Record record) throws Exception {// TODO Auto-generated method stubListBean lb = new ListBean();lb.setId(record.getInt("id"));lb.setFieldName(record.getString("fieldName"));lbs.add(lb);}}, "mysql",sql, 0, 5, b);for(int i=0;i<lbs.size();i++)System.out.println(lbs.get(i).getId()+"-----");}}