Spring多数据源解决方案
Figure 2 多数据源的选择逻辑渗透至客户端
解决方案
Figure 3 采用Proxy模式来封转数据源选择逻辑
通过采用Proxy模式我们在方案实现中实现一个虚拟的数据源.并且通过它来封装数据源选择逻辑,这样就可以有效地将数据源选择逻辑从Client中分离出来.Client提供选择所需的上下文(因为这是Client所知道的),有虚拟的DataSource根据Client提供的上下文来实现数据源的选择.Spring2.x的版本中提供了实现这种方式的基本框架.虚拟的DataSource仅需要继承AbstractRoutingDataSource实现determineCurrentLookupKey()在其中封装数据源选择逻辑.
实例:
public class DynamicDataSource extends AbstractRoutingDataSource{
static Logger log=Logger.getLogger("DynamicDataSource");
@Override
protected Object determineCurrentLookupKey(){
String userId=(String)DbContextHolder.getContext();
Integer dataSourceId=getDataSourceIdByUserId(userId);
return dataSourceId;
}
}
实例中通过UserId来决定数据存放在哪个数据库中。
配置文件示例：
| <bean id="dataSource" class="com.bitfone.smartdm.datasource.DynamicDataSource"> <property name="targetDataSources"> <map key-type="java.lang.Integer"> <entry key="0" value-ref="dataSource0"/> <entry key="1" value-ref="dataSource1"/> <entry key="2" value-ref="dataSource2"/> </map> </property> <property name="defaultTargetDataSource" ref="dataSource0"/> </bean> <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"> <property name="configLocation" value="classpath:com/bitfone/smartdm/dao/sqlmap/sql-map-config.xml"/> <property name="dataSource" ref="dataSource"/> </bean> <bean id="UserInfoDAO" class="com.bitfone.smartdm.dao.impl.UserInfoDAO"> <property name="sqlMapClient" ref="sqlMapClient"/> </bean> |
下面是个人在项目中用过的配置方法(以后公布)：
程序中的控制代码
public class DbContextHolder{
private static final ThreadLocal contextHolder = new ThreadLocal();
public static void setDbType(String dbType)
{
contextHolder.set(dbType);
}
public static String getDbType()
{
String str=(String) contextHolder.get();
if(null==str || "".equals(str))
str="1";
return str;
}
public static void clearDbType()
{
contextHolder.remove();
}
}
在程序中通过设置DbContextHolder.setDbType(String dbType);来控制数据库的选取,其中key配置目标数据源的键值.
本文通过实例介绍了MySQL中的group_concat函数的使用方法，比如select group_concat(name) 。
MySQL中group_concat函数
完整的语法如下：
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
基本查询
mysql> select * from aa;
+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in set (0.00 sec)
以id分组，把name字段的值打印在一行，逗号分隔(默认)
mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)
以id分组，把name字段的值打印在一行，分号分隔
mysql> select id,group_concat(name separator ';') from aa group by id;
+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+------+----------------------------------+
3 rows in set (0.00 sec)
以id分组，把去冗余的name字段的值打印在一行，
逗号分隔
mysql> select id,group_concat(distinct name) from aa group by id;
+------+-----------------------------+
| id| group_concat(distinct name) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)
以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序
mysql> select id,group_concat(name order by name desc) from aa group by id;
+------+---------------------------------------+
| id| group_concat(name order by name desc) |
+------+---------------------------------------+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)
本人在做用户有哪些角色动态显示是用到.可以提高代码简洁性与程序的效率,不用对比用户角色关联表与用户角色与用户表.
|
JavaScript 动态实现树形菜单(使用dtree控件)
@author Bob 2012/08/09
数据库脚本：
use master
go if exists (select * from sysdatabases where name = 'tree') drop database tree go create database tree on ( Name = 'tree_data', --主数据文件的逻辑名称 FileName = 'D:/tree_data.mdf', --数据文件的物理名称 Size = 5Mb, -- 主数据文件的初始大小 FileGrowth = 20% --主数据文件增长率 ) log on ( Name = 'tree_log', FileName = 'd:/tree_log.ldf', Size = 3Mb, FileGrowth = 10% ) go if exists (select * from sysobjects where name = 'dtree') drop table dtree go use tree go /*--- 创建节点表 ---*/ create table dtree ( id varchar(20) , --节点编号 pid varchar(20) , --父节点 [name] varchar(20), --节点名称 url varchar(20), --连接地址 title varchar(20), --节点描述 target varchar(20), --Target icon varchar(20), --图标 iconOpen varchar(20), --展开状态下的图标路径 [open] varchar(20) --是否展开 ) /*--- 初始化测试数据 ---*/ insert into dtree values('0','-1','T16班','index.jsp','blank','','','','') insert into dtree values('1','0','教师','index.jsp','blank','','','','') insert into dtree values('2','0','班干部','index.jsp','blank','','','','') insert into dtree values('3','0','组长','index.jsp','blank','','','','') insert into dtree values('4','0','学员','index.jsp','blank','','','','') insert into dtree values('5','1','班主任','index.jsp','blank','','','','') insert into dtree values('6','1','教员','index.jsp','blank','','','','') insert into dtree values('7','2','班长','index.jsp','blank','','','','') insert into dtree values('8','2','学委','index.jsp','blank','','','','') insert into dtree values('9','4','学员1','index.jsp','blank','','','','') insert into dtree values('10','4','学员2','index.jsp','blank','','','','') insert into dtree values('11','4','学员3','index.jsp','blank','','','','') insert into dtree values('12','4','学员4','index.jsp','blank','','','','') insert into dtree values('13','4','学员5','index.jsp','blank','','','','') insert into dtree values('14','4','学员6','index.jsp','blank','','','','') insert into dtree values('15','4','学员7','index.jsp','blank','','','','') insert into dtree values('16','4','学员8','index.jsp','blank','','','','') insert into dtree values('17','4','学员9','index.jsp','blank','','','','') insert into dtree values('18','4','学员10','index.jsp','blank','','','','') insert into dtree values('19','4','学员11','index.jsp','blank','','','','') insert into dtree values('20','4','学员12','index.jsp','blank','','','','') insert into dtree values('21','4','学员13','index.jsp','blank','','','','') insert into dtree values('22','4','学员14','index.jsp','blank','','','','') insert into dtree values('23','4','学员15','index.jsp','blank','','','','') select id,pid,[name],url,title,target,icon,iconOpen,[open] from dtree 客户端代码 index.jsp：
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@page import="com.zsw.biz.TreeNodeBiz;"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>树形菜单</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <link rel="StyleSheet" href="dtree.css" type="text/css" /> <script type="text/javascript" src="dtree.js"></script> </head> <body> <div> <% TreeNodeBiz tnb = new TreeNodeBiz(); %> <script type="text/javascript"> d = <%= tnb.getList("dtree") %> document.write(d); </script> </div> </body> </html> 转换成节点数据转换成js文件 关键代码：
if(list==null || list.size() == 0){
System.out.println("没有节点"); return ""; } StringBuffer contents = new StringBuffer(); contents.append("dtree = new dTree('"+dtree+"');"); for(TreeNode tn : list){ contents.append("/n"); contents.append(dtree+".add('"); contents.append(tn.getId()); contents.append("','"); contents.append(tn.getPid()); contents.append("','"); contents.append(tn.getName()); contents.append("','"); contents.append(tn.getUrl()); contents.append("','"); contents.append(tn.getTitle()); contents.append("','"); contents.append(tn.getTarget()); contents.append("','"); contents.append(tn.getIcon()); contents.append("','"); contents.append(tn.getIconOpen()); contents.append("','"); contents.append(tn.getOpen()); contents.append("');"); contents.append("/n"); } 客户端的代码： 需要导入：dtree.js 和dtree.css
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@page import="com.zsw.biz.TreeNodeBiz;"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>树形菜单</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <link rel="StyleSheet" href="dtree.css" type="text/css" /> <script type="text/javascript" src="dtree.js"></script> </head> <body> <p><a href="javascript: d.openAll();">全部展开</a> | <a href="javascript: d.closeAll();">全部关闭</a> </p> <div> <% TreeNodeBiz tnb = new TreeNodeBiz(); |
动态生成树型菜单
