1水平分庫

最近在做一個IM系統，之前的舊系統沒有考慮到用戶量會增長得這么龐大，導致現在數據庫性能瓶頸非常嚴重，迫切需要分庫，用于減少每個庫的用戶數量，進而分攤負載，最終達到數據庫橫向擴展的目的。

數據庫水平分庫是以用戶Id為分庫的依據，同一個用戶的所有數據都在同一個庫上，每個庫有著相同的表結構。為了實現開發人員來說對數據庫的透明訪問，分庫框架需要解決二個問題：
1、 方法參數中有用戶id的數據的新增，查詢及修改
2、 方法參數中無用戶id的數據的查詢

2用戶id

把用戶名和密碼所在的表定義為用戶表，用戶id即是用戶表中的惟一性標識的整形值，如果用戶的用戶名只有一種方式，那么id可以是用戶名的hash值，此時用戶表也是分庫的；如果用戶的用戶名有多種方式，比如允許用戶使用email登陸，也允許用戶使用手機號碼登陸，那么用戶id應該是用戶表中的遞增字段值,此時用戶表應該是不分庫的，這時可以把用戶表獨立為另一個庫，稱之為認證庫。我們的項目應用是屬于后者。

1 3 解決方案

3.1    說明

簡單服務即為DAO,每個domain都對應一個簡單服務，簡單服務之間不允許互相依賴；復雜服務可以依賴多個簡單服務，但不能直接訪問數據庫，復雜服務對數據庫的操作必須通過單簡單服務。

使用hibernate作為訪問數據庫的中間層，結合Spring的Aop攔截方法，簡單服務代理與簡單服務實現相同的接口，一個簡單服務對應二個實例，一個引用動態獲取數據庫連接的sessionFactory，另一個引用Hibernate Shards的sessionFactory

3.2    方法參數中有用戶Id

Spring Aop攔截簡單服務代理的所有方法，如果方法的第一個參數為userid，則將userid

放到當前線程中，并選擇引用動態獲取數據庫連接的sessionFactory的簡單服務實例，在獲取數據庫連接時根據當前線程的userid選擇相應連接，流程如下：

3.3    方法參數中無用戶Id

Spring Aop攔截簡單服務代理的所有方法，如果方法的第一個參數為非userid，選擇引用Hibernate Shards的sessionFactory的簡單服務實例，遍歷所有數據庫，并返回匯總后的數據。這種情況下只允許讀，不允許寫。流程如下：

1  4實現

4.1    簡單服務代理

對每個簡單服務用jdk動態代理生成一個代理對像，復雜服務依賴代理對像。

4.2    實例化

在簡單服務類上標注@DetachDbService，則會產生三個實例(框架實現)：

1.       簡單服務代理實例

2.       引用動態獲取數據庫連接的sessionFactory的簡單服務實例

3.       引用Hibernate Shards的sessionFactory簡單服務實例

4.3    方法參數

如果是到某個庫獲取數據，則第一個參數必須為Long或者UseridAble類型，用于獲取userid

4.4    userid與數據庫關系

總的來說，取模是最優方案，但是考慮到服務器性能可能不一致，而又需要充分利用服務器資源，所以需要在取模的同時加上權重。比如現在有二臺數據庫，權重為1：2，那么用戶id先對3取模，0的為第一臺服務器，1，2的為第二臺服務器。

4.5精確分頁

由于hibernate shards不能到某個庫或者其中的幾個庫中去查詢，并且它的分頁是先到所有的庫中將所有符合條件的數據取回到內存中再進行分頁，所以不可能使用它的分頁。      

先用hibernate shards到各個庫上查出符合條件的數目及數據庫標識（標識為查詢表中最小用戶id），返回結果后對標識進行排序（這樣確保同樣的查詢條件在翻頁的時候能夠以同樣的順序查詢數據庫，以達到精確查詢的目的）。根據這個結果計算出每個數據庫取值的段，然后用動態數據庫連接按之前排好的順序遍歷數據庫進行查找，段為0的直接跳過，找滿結果則返回。

比如現在有3個庫，要查詢所在地為深圳的用戶，通過hibernate shards查得數據如下：

這時按用戶最小id排序結果是DB2,DB1,DB3

假設每頁10條記錄，

第一頁的數據是從DB2中取5條，DB1中取前5條，不需要到DB3去取

第二頁的數據是從DB1中取后2條，在DB3中取前8條，不需要到DB1中去取

第三頁數據是從DB3中取第9到第18條，不需要到DB1和DB2中去取

… …

缺點：不能精確排序

5關鍵代碼

Java代碼

1. package com.konceptusa.infinet.annotation;   
2.   
3. import java.lang.annotation.Documented;   
4. import java.lang.annotation.ElementType;   
5. import java.lang.annotation.Retention;   
6. import java.lang.annotation.RetentionPolicy;   
7. import java.lang.annotation.Target;   
8.   
9. import org.springframework.beans.factory.annotation.Autowire;   
10.   
11. /**  
12.  * 簡單服務類實例化標注  
13.  * @author Jwin  
14.  *  
15.  */  
16. @Retention(RetentionPolicy.RUNTIME)   
17. @Target( { ElementType.TYPE })   
18. @Documented  
19. public @interface DetachDbService   
20. {   
21.     boolean lazy() default false;   
22.     Autowire autoWire() default Autowire.BY_NAME;   
23.     String init() default "";   
24.     String destroy() default "";   
25. }  

package com.konceptusa.infinet.annotation;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import org.springframework.beans.factory.annotation.Autowire;
/**
* 簡單服務類實例化標注
* @author Jwin
*
*/
@Retention(RetentionPolicy.RUNTIME)
@Target( { ElementType.TYPE })
@Documented
public @interface DetachDbService
{
boolean lazy() default false;
Autowire autoWire() default Autowire.BY_NAME;
String init() default "";
String destroy() default "";
}

Java代碼

1. package com.konceptusa.infinet.annotation.handler;   
2.   
3. import java.util.ArrayList;   
4. import java.util.List;   
5.   
6. import org.apache.commons.logging.Log;   
7. import org.apache.commons.logging.LogFactory;   
8. import org.springframework.aop.framework.ProxyFactoryBean;   
9. import org.springframework.beans.MutablePropertyValues;   
10. import org.springframework.beans.factory.config.RuntimeBeanReference;   
11. import org.springframework.beans.factory.support.RootBeanDefinition;   
12.   
13. import com.konceptusa.framework.annotation.IllegalConfigException;   
14. import com.konceptusa.framework.annotation.spring.support.AbstractSpringListAnnotationHandler;   
15. import com.konceptusa.framework.annotation.spring.support.SpringAnnotationUtils;   
16. import com.konceptusa.infinet.annotation.DetachDbService;   
17.   
18. /**  
19.  * 向spring中注冊簡單服務代理實例，引用動態數據庫連結的簡單服務實例，引用hibernate shards的簡單服務實例  
20.  * @author Jwin  
21.  *   
22.  */  
23. public class DetachDbServiceAnnotationHandler extends AbstractSpringListAnnotationHandler<DetachDbService>   
24. {   
25.     private final static String SESSIONFACTORYNAME = "sessionFactory";   
26.     public final static String DYNAMIC_POSTFIX = "Dynamic";   
27.     public final static String SHARDS_POSTFIX = "Shards";   
28.     private final static String DETACHDBINTERCEPTOR = "detachDBInterceptor";   
29.   
30.     private final static Log LOG = LogFactory.getLog(DetachDbServiceAnnotationHandler.class);   
31.   
32.     public Class annotation()   
33.     {   
34.         return DetachDbService.class;   
35.     }   
36.   
37.     @Override  
38.     protected void handle(DetachDbService s, Class target)   
39.     {   
40.         String name = target.getSimpleName();   
41.         if (!name.endsWith("ServiceImpl"))   
42.         {   
43.             throw new IllegalConfigException(target.getName()   
44.                     + " is not a service bean.service bean 's class name must be end with 'ServiceImpl'");   
45.         }   
46.         name = getBeanName(name);   
47.         String dynamicName = name + DYNAMIC_POSTFIX;   
48.         String dynamicSessionFactory = SESSIONFACTORYNAME + DYNAMIC_POSTFIX;   
49.         //生成動態獲取數據庫連接的簡單服務實例   
50.         createBean(s, target, dynamicName, dynamicSessionFactory);                 
51.         String shardsName = name + SHARDS_POSTFIX;   
52.         String shardsFactory = SESSIONFACTORYNAME + SHARDS_POSTFIX;   
53.         //生成查詢所有數據庫的簡單服務實例   
54.         createBean(s, target, shardsName, shardsFactory);   
55.         //生成簡單服務代理類   
56.         RootBeanDefinition definition = createBeanDefinition(s, ProxyFactoryBean.class, name);   
57.         MutablePropertyValues mpv = new MutablePropertyValues();   
58.         mpv.addPropertyValue("target", new RuntimeBeanReference(shardsName));   
59.         List<String> interceptorNamesList = new ArrayList<String>();   
60.         interceptorNamesList.add(DETACHDBINTERCEPTOR);   
61.         mpv.addPropertyValue("interceptorNames", interceptorNamesList);   
62.         definition.setPropertyValues(mpv);   
63.         registerBeanDefinition(name, definition);          
64.     }   
65.   
66.     private void createBean(DetachDbService s, Class target, String name, String sessionFactory)   
67.     {   
68.         RootBeanDefinition beanDefinition = createBeanDefinition(s, target, name);   
69.         MutablePropertyValues mpv = new MutablePropertyValues();   
70.         mpv.addPropertyValue(SESSIONFACTORYNAME, new RuntimeBeanReference(sessionFactory));   
71.         beanDefinition.setPropertyValues(mpv);   
72.         registerBeanDefinition(name, beanDefinition);   
73.     }   
74.   
75.     private RootBeanDefinition createBeanDefinition(DetachDbService s, Class target, String name)   
76.     {   
77.         RootBeanDefinition definition = new RootBeanDefinition();   
78.         definition.setAbstract(false);   
79.         definition.setBeanClass(target);   
80.         definition.setSingleton(true);   
81.         definition.setLazyInit(s.lazy());   
82.         definition.setAutowireCandidate(true);   
83.         definition.setAutowireMode(s.autoWire().value());   
84.   
85.         if (!"".equals(s.init()))   
86.         {   
87.             definition.setInitMethodName(s.init().trim());   
88.         }   
89.         if (!"".equals(s.destroy()))   
90.         {   
91.             definition.setDestroyMethodName(s.destroy().trim());   
92.         }   
93.   
94.         if (LOG.isDebugEnabled())   
95.         {   
96.             LOG.debug("Reader Bean Definition[" + definition + "] with name[" + name + "]");   
97.         }   
98.         SpringAnnotationUtils.readProperties(target, definition);   
99.         return definition;   
100.     }   
101.   
102.     private String getBeanName(String name)   
103.     {   
104.         name = name.substring(0, name.length() - "Impl".length());   
105.         name = name.substring(0, 1).toLowerCase() + name.substring(1, name.length());   
106.         return name;   
107.     }   
108.   
109. }  

package com.konceptusa.infinet.annotation.handler;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.aop.framework.ProxyFactoryBean;
import org.springframework.beans.MutablePropertyValues;
import org.springframework.beans.factory.config.RuntimeBeanReference;
import org.springframework.beans.factory.support.RootBeanDefinition;
import com.konceptusa.framework.annotation.IllegalConfigException;
import com.konceptusa.framework.annotation.spring.support.AbstractSpringListAnnotationHandler;
import com.konceptusa.framework.annotation.spring.support.SpringAnnotationUtils;
import com.konceptusa.infinet.annotation.DetachDbService;
/**
* 向spring中注冊簡單服務代理實例，引用動態數據庫連結的簡單服務實例，引用hibernate shards的簡單服務實例
* @author Jwin
*
*/
public class DetachDbServiceAnnotationHandler extends AbstractSpringListAnnotationHandler<DetachDbService>
{
private final static String SESSIONFACTORYNAME = "sessionFactory";
public final static String DYNAMIC_POSTFIX = "Dynamic";
public final static String SHARDS_POSTFIX = "Shards";
private final static String DETACHDBINTERCEPTOR = "detachDBInterceptor";
private final static Log LOG = LogFactory.getLog(DetachDbServiceAnnotationHandler.class);
public Class annotation()
{
return DetachDbService.class;
}
@Override
protected void handle(DetachDbService s, Class target)
{
String name = target.getSimpleName();
if (!name.endsWith("ServiceImpl"))
{
throw new IllegalConfigException(target.getName()
+ " is not a service bean.service bean 's class name must be end with 'ServiceImpl'");
}
name = getBeanName(name);
String dynamicName = name + DYNAMIC_POSTFIX;
String dynamicSessionFactory = SESSIONFACTORYNAME + DYNAMIC_POSTFIX;
//生成動態獲取數據庫連接的簡單服務實例
createBean(s, target, dynamicName, dynamicSessionFactory);
String shardsName = name + SHARDS_POSTFIX;
String shardsFactory = SESSIONFACTORYNAME + SHARDS_POSTFIX;
//生成查詢所有數據庫的簡單服務實例
createBean(s, target, shardsName, shardsFactory);
//生成簡單服務代理類
RootBeanDefinition definition = createBeanDefinition(s, ProxyFactoryBean.class, name);
MutablePropertyValues mpv = new MutablePropertyValues();
mpv.addPropertyValue("target", new RuntimeBeanReference(shardsName));
List<String> interceptorNamesList = new ArrayList<String>();
interceptorNamesList.add(DETACHDBINTERCEPTOR);
mpv.addPropertyValue("interceptorNames", interceptorNamesList);
definition.setPropertyValues(mpv);
registerBeanDefinition(name, definition);
}
private void createBean(DetachDbService s, Class target, String name, String sessionFactory)
{
RootBeanDefinition beanDefinition = createBeanDefinition(s, target, name);
MutablePropertyValues mpv = new MutablePropertyValues();
mpv.addPropertyValue(SESSIONFACTORYNAME, new RuntimeBeanReference(sessionFactory));
beanDefinition.setPropertyValues(mpv);
registerBeanDefinition(name, beanDefinition);
}
private RootBeanDefinition createBeanDefinition(DetachDbService s, Class target, String name)
{
RootBeanDefinition definition = new RootBeanDefinition();
definition.setAbstract(false);
definition.setBeanClass(target);
definition.setSingleton(true);
definition.setLazyInit(s.lazy());
definition.setAutowireCandidate(true);
definition.setAutowireMode(s.autoWire().value());
if (!"".equals(s.init()))
{
definition.setInitMethodName(s.init().trim());
}
if (!"".equals(s.destroy()))
{
definition.setDestroyMethodName(s.destroy().trim());
}
if (LOG.isDebugEnabled())
{
LOG.debug("Reader Bean Definition[" + definition + "] with name[" + name + "]");
}
SpringAnnotationUtils.readProperties(target, definition);
return definition;
}
private String getBeanName(String name)
{
name = name.substring(0, name.length() - "Impl".length());
name = name.substring(0, 1).toLowerCase() + name.substring(1, name.length());
return name;
}
}

Java代碼

1. package com.konceptusa.infinet.detach.aop;   
2.   
3. import org.aopalliance.intercept.MethodInterceptor;   
4. import org.aopalliance.intercept.MethodInvocation;   
5. import org.apache.commons.logging.Log;   
6. import org.apache.commons.logging.LogFactory;   
7. import org.springframework.util.MethodInvoker;   
8.   
9. import com.konceptusa.framework.annotation.IllegalConfigException;   
10. import com.konceptusa.framework.core.support.ObjectFactory;   
11. import com.konceptusa.infinet.annotation.handler.DetachDbServiceAnnotationHandler;   
12. import com.konceptusa.infinet.detach.UseridAble;   
13. import com.konceptusa.infinet.detach.datasource.DataSourceIdContextHolder;   
14. import com.konceptusa.infinet.detach.datasource.UseridContextHolder;   
15.   
16. /**  
17.  * 分庫簡單服務代理  
18.  * @author Jwin  
19.  *  
20.  */  
21. public class DetachDBInterceptor implements MethodInterceptor   
22. {   
23.     private final static Log LOG = LogFactory.getLog(DetachDBInterceptor.class);   
24.     public Object invoke(MethodInvocation invoke) throws Throwable   
25.     {   
26.         int len = invoke.getArguments().length;   
27.         Long id = null;   
28.         if(len >= 1)   
29.         {              
30.             Object arg = invoke.getArguments()[0];   
31.             if(arg instanceof UseridAble)   
32.             {   
33.                 UseridAble useridAble = (UseridAble) arg;   
34.                 id = useridAble.getUserid();   
35.             }   
36.             else if(arg instanceof Long)   
37.             {   
38.                 id = (Long) arg;   
39.             }   
40.         }   
41.         if(id != null)   
42.         {   
43.             UseridContextHolder.setUserid(id);   
44.             try  
45.             {              
46.                 return invoke(invoke, id);   
47.             }finally  
48.             {   
49.                 UseridContextHolder.removeUserid();   
50.             }              
51.         }   
52.         else  
53.         {   
54.             return invoke(invoke, id);             
55.         }   
56.     }   
57.     private Object invoke(MethodInvocation invoke, Long id) throws Throwable   
58.     {   
59.         String str = invoke.getThis().toString();   
60.         int start = str.lastIndexOf(".");   
61.         int end = str.lastIndexOf("@");   
62.         String className = str.substring(start + 1, end);   
63.         String postFix = DetachDbServiceAnnotationHandler.DYNAMIC_POSTFIX;   
64.         if(id == null && DataSourceIdContextHolder.getDataSourceId() == null)   
65.         {   
66.             postFix = DetachDbServiceAnnotationHandler.SHARDS_POSTFIX;   
67.         }   
68.         String serviceName = className.substring(0,1).toLowerCase() + className.substring(1,className.length() - "Impl".length()) + postFix;   
69.         if(LOG.isDebugEnabled())   
70.             LOG.debug("select service " + serviceName + " for userid = " + id);   
71.         Object service = ObjectFactory.getManagedObject(serviceName);   
72.         if(service == null)   
73.         {   
74.             throw new IllegalConfigException("service name " + serviceName + " is not defined in spring context");   
75.         }   
76.         MethodInvoker invoker = new MethodInvoker();   
77.         invoker.setArguments(invoke.getArguments());   
78.         invoker.setTargetObject(service);   
79.         invoker.setTargetMethod(invoke.getMethod().getName());   
80.         invoker.prepare();   
81.         return invoker.invoke();   
82.     }   
83.   
84. }  

package com.konceptusa.infinet.detach.aop;
import org.aopalliance.intercept.MethodInterceptor;
import org.aopalliance.intercept.MethodInvocation;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.util.MethodInvoker;
import com.konceptusa.framework.annotation.IllegalConfigException;
import com.konceptusa.framework.core.support.ObjectFactory;
import com.konceptusa.infinet.annotation.handler.DetachDbServiceAnnotationHandler;
import com.konceptusa.infinet.detach.UseridAble;
import com.konceptusa.infinet.detach.datasource.DataSourceIdContextHolder;
import com.konceptusa.infinet.detach.datasource.UseridContextHolder;
/**
* 分庫簡單服務代理
* @author Jwin
*
*/
public class DetachDBInterceptor implements MethodInterceptor
{
private final static Log LOG = LogFactory.getLog(DetachDBInterceptor.class);
public Object invoke(MethodInvocation invoke) throws Throwable
{
int len = invoke.getArguments().length;
Long id = null;
if(len >= 1)
{
Object arg = invoke.getArguments()[0];
if(arg instanceof UseridAble)
{
UseridAble useridAble = (UseridAble) arg;
id = useridAble.getUserid();
}
else if(arg instanceof Long)
{
id = (Long) arg;
}
}
if(id != null)
{
UseridContextHolder.setUserid(id);
try
{
return invoke(invoke, id);
}finally
{
UseridContextHolder.removeUserid();
}
}
else
{
return invoke(invoke, id);
}
}
private Object invoke(MethodInvocation invoke, Long id) throws Throwable
{
String str = invoke.getThis().toString();
int start = str.lastIndexOf(".");
int end = str.lastIndexOf("@");
String className = str.substring(start + 1, end);
String postFix = DetachDbServiceAnnotationHandler.DYNAMIC_POSTFIX;
if(id == null && DataSourceIdContextHolder.getDataSourceId() == null)
{
postFix = DetachDbServiceAnnotationHandler.SHARDS_POSTFIX;
}
String serviceName = className.substring(0,1).toLowerCase() + className.substring(1,className.length() - "Impl".length()) + postFix;
if(LOG.isDebugEnabled())
LOG.debug("select service " + serviceName + " for userid = " + id);
Object service = ObjectFactory.getManagedObject(serviceName);
if(service == null)
{
throw new IllegalConfigException("service name " + serviceName + " is not defined in spring context");
}
MethodInvoker invoker = new MethodInvoker();
invoker.setArguments(invoke.getArguments());
invoker.setTargetObject(service);
invoker.setTargetMethod(invoke.getMethod().getName());
invoker.prepare();
return invoker.invoke();
}
}

Java代碼

1. package com.konceptusa.infinet.detach.datasource;   
2.   
3. import java.util.HashMap;   
4. import java.util.List;   
5. import java.util.Map;   
6. import java.util.Properties;   
7.   
8. import javax.sql.DataSource;   
9.   
10. import org.apache.commons.lang.StringUtils;   
11. import org.apache.commons.logging.Log;   
12. import org.apache.commons.logging.LogFactory;   
13. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;   
14. import org.springframework.util.Assert;   
15.   
16. import com.konceptusa.framework.annotation.IllegalConfigException;   
17. import com.konceptusa.infinet.detach.config.MultiHibernateProperties;   
18. import com.konceptusa.infinet.detach.service.ISelectDBService;   
19.   
20. /**  
21.  * 動態獲取數據庫連接基類  
22.  * @author Jwin  
23.  *   
24.  */  
25. public abstract class AbstractDynamicDataSource extends AbstractRoutingDataSource   
26. {   
27.     private final static Log LOG = LogFactory.getLog(AbstractDynamicDataSource.class);   
28.     public final static int defaultDataSourceId = -1;   
29.     protected MultiHibernateProperties multiHibernateProperties;   
30.     protected ISelectDBService selectDBService;   
31.     private String newWeights;   
32.     private String oldWeights;   
33.     private Map<Integer, DataSource> dataSourceMap = new HashMap<Integer, DataSource>();   
34.     public void setSelectDBService(ISelectDBService selectDBService)   
35.     {   
36.         this.selectDBService = selectDBService;   
37.     }   
38.     public void setMultiHibernateProperties(MultiHibernateProperties multiHibernateProperties)   
39.     {   
40.         this.multiHibernateProperties = multiHibernateProperties;   
41.     }   
42.     @Override  
43.     protected Object determineCurrentLookupKey()   
44.     {   
45.         Long id = UseridContextHolder.getUserid();   
46.         return selectDBService.selectDb(id);   
47.     }   
48.        
49.     @Override  
50.     public void afterPropertiesSet()   
51.     {   
52.         LOG.info("init dynamic datasource start");   
53.         Assert.notNull(multiHibernateProperties);   
54.         Assert.notNull(selectDBService);   
55.         List<Properties> properties = multiHibernateProperties.getShardProperties();   
56.         Assert.notEmpty(properties);   
57.         int dataSourceCount = 0;   
58.         for(Properties p : properties)   
59.         {   
60.             dataSourceCount++;   
61.             createDataSource(dataSourceMap, p);   
62.         }   
63.         createDefaultDataSource(dataSourceMap);   
64.         selectDBService.setDefaultDataSourceId(defaultDataSourceId);   
65.         selectDBService.setDataSourceCount(dataSourceCount);   
66.         setTargetDataSources(dataSourceMap);   
67.         setDefaultTargetDataSource(dataSourceMap.get(defaultDataSourceId));   
68.         initWeight(dataSourceCount);   
69.         super.afterPropertiesSet();   
70.         LOG.info("init dynamic datasource success");   
71.     }   
72.     public void initWeight(int dataSourceCount)   
73.     {   
74.         Map<Integer, Integer> oldWeightMap = new HashMap<Integer, Integer>();   
75.         Map<Integer, Integer> newWeightMap = new HashMap<Integer, Integer>();   
76.         int totalOldWeight = 0;   
77.         int totalNewWeight = 0;   
78.         if(newWeights != null)   
79.         {   
80.             if(LOG.isInfoEnabled())   
81.                 LOG.info("newWeights " + newWeights);   
82.             String[] weights = StringUtils.split(newWeights,";");   
83.             if(weights.length > dataSourceCount)   
84.             {   
85.                 throw new IllegalConfigException("newWeights's length ["+weights.length+"] can't be more than dataSourceCount["+dataSourceCount+"]");   
86.             }   
87.             for(int i=0;i<weights.length;i++)   
88.             {   
89.                 int w = Integer.parseInt(weights[i]);   
90.                 for(int j=0;j<w;j++)   
91.                 {   
92.                     newWeightMap.put(totalNewWeight + j, i);   
93.                 }   
94.                 totalNewWeight += w;   
95.             }   
96.         }   
97.         else  
98.         {   
99.             totalNewWeight = dataSourceCount;   
100.             for(int i=0;i<dataSourceCount;i++)   
101.             {   
102.                 newWeightMap.put(i, i);   
103.             }   
104.         }   
105.         if(oldWeights != null)   
106.         {   
107.             if(LOG.isInfoEnabled())   
108.                 LOG.info("oldWeights " + oldWeights);   
109.             String[] weights = StringUtils.split(oldWeights,";");   
110.             if(weights.length > dataSourceCount)   
111.             {   
112.                 throw new IllegalConfigException("oldWeights's length ["+weights.length+"] can't be more than dataSourceCount["+dataSourceCount+"]");   
113.             }   
114.             for(int i=0;i<weights.length;i++)   
115.             {   
116.                 int w = Integer.parseInt(weights[i]);   
117.                 for(int j=0;j<w;j++)   
118.                 {   
119.                     oldWeightMap.put(totalOldWeight + j, i);   
120.                 }   
121.                 totalOldWeight += w;   
122.             }   
123.         }   
124.         else  
125.         {   
126.             totalOldWeight = dataSourceCount;   
127.             for(int i=0;i<dataSourceCount;i++)   
128.             {   
129.                 oldWeightMap.put(i, i);   
130.             }   
131.         }   
132.         if(LOG.isInfoEnabled())   
133.             LOG.info("totalNewWeight " + totalNewWeight + " totalOldWeight " + totalOldWeight);   
134.         selectDBService.setTotalNewWeight(totalNewWeight);   
135.         selectDBService.setNewWeightIdMap(newWeightMap);   
136.         selectDBService.setTotalOldWeight(totalOldWeight);   
137.         selectDBService.setOldWeightIdMap(oldWeightMap);   
138.     }   
139.     protected abstract void createDataSource(Map<Integer, DataSource> dataSourceMap, Properties p);   
140.     protected abstract void createDefaultDataSource(Map<Integer, DataSource> dataSourceMap);   
141.     public void setNewWeights(String newWeights)   
142.     {   
143.         this.newWeights = newWeights;   
144.     }   
145.     public void setOldWeights(String oldWeights)   
146.     {   
147.         this.oldWeights = oldWeights;   
148.     }   
149.     public Map<Integer, DataSource> getDataSourceMap()   
150.     {   
151.         return dataSourceMap;   
152.     }   
153.   
154. }  

package com.konceptusa.infinet.detach.datasource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.util.Assert;
import com.konceptusa.framework.annotation.IllegalConfigException;
import com.konceptusa.infinet.detach.config.MultiHibernateProperties;
import com.konceptusa.infinet.detach.service.ISelectDBService;
/**
* 動態獲取數據庫連接基類
* @author Jwin
*
*/
public abstract class AbstractDynamicDataSource extends AbstractRoutingDataSource
{
private final static Log LOG = LogFactory.getLog(AbstractDynamicDataSource.class);
public final static int defaultDataSourceId = -1;
protected MultiHibernateProperties multiHibernateProperties;
protected ISelectDBService selectDBService;
private String newWeights;
private String oldWeights;
private Map<Integer, DataSource> dataSourceMap = new HashMap<Integer, DataSource>();
public void setSelectDBService(ISelectDBService selectDBService)
{
this.selectDBService = selectDBService;
}
public void setMultiHibernateProperties(MultiHibernateProperties multiHibernateProperties)
{
this.multiHibernateProperties = multiHibernateProperties;
}
@Override
protected Object determineCurrentLookupKey()
{
Long id = UseridContextHolder.getUserid();
return selectDBService.selectDb(id);
}
@Override
public void afterPropertiesSet()
{
LOG.info("init dynamic datasource start");
Assert.notNull(multiHibernateProperties);
Assert.notNull(selectDBService);
List<Properties> properties = multiHibernateProperties.getShardProperties();
Assert.notEmpty(properties);
int dataSourceCount = 0;
for(Properties p : properties)
{
dataSourceCount++;
createDataSource(dataSourceMap, p);
}
createDefaultDataSource(dataSourceMap);
selectDBService.setDefaultDataSourceId(defaultDataSourceId);
selectDBService.setDataSourceCount(dataSourceCount);
setTargetDataSources(dataSourceMap);
setDefaultTargetDataSource(dataSourceMap.get(defaultDataSourceId));
initWeight(dataSourceCount);
super.afterPropertiesSet();
LOG.info("init dynamic datasource success");
}
public void initWeight(int dataSourceCount)
{
Map<Integer, Integer> oldWeightMap = new HashMap<Integer, Integer>();
Map<Integer, Integer> newWeightMap = new HashMap<Integer, Integer>();
int totalOldWeight = 0;
int totalNewWeight = 0;
if(newWeights != null)
{
if(LOG.isInfoEnabled())
LOG.info("newWeights " + newWeights);
String[] weights = StringUtils.split(newWeights,";");
if(weights.length > dataSourceCount)
{
throw new IllegalConfigException("newWeights's length ["+weights.length+"] can't be more than dataSourceCount["+dataSourceCount+"]");
}
for(int i=0;i<weights.length;i++)
{
int w = Integer.parseInt(weights[i]);
for(int j=0;j<w;j++)
{
newWeightMap.put(totalNewWeight + j, i);
}
totalNewWeight += w;
}
}
else
{
totalNewWeight = dataSourceCount;
for(int i=0;i<dataSourceCount;i++)
{
newWeightMap.put(i, i);
}
}
if(oldWeights != null)
{
if(LOG.isInfoEnabled())
LOG.info("oldWeights " + oldWeights);
String[] weights = StringUtils.split(oldWeights,";");
if(weights.length > dataSourceCount)
{
throw new IllegalConfigException("oldWeights's length ["+weights.length+"] can't be more than dataSourceCount["+dataSourceCount+"]");
}
for(int i=0;i<weights.length;i++)
{
int w = Integer.parseInt(weights[i]);
for(int j=0;j<w;j++)
{
oldWeightMap.put(totalOldWeight + j, i);
}
totalOldWeight += w;
}
}
else
{
totalOldWeight = dataSourceCount;
for(int i=0;i<dataSourceCount;i++)
{
oldWeightMap.put(i, i);
}
}
if(LOG.isInfoEnabled())
LOG.info("totalNewWeight " + totalNewWeight + " totalOldWeight " + totalOldWeight);
selectDBService.setTotalNewWeight(totalNewWeight);
selectDBService.setNewWeightIdMap(newWeightMap);
selectDBService.setTotalOldWeight(totalOldWeight);
selectDBService.setOldWeightIdMap(oldWeightMap);
}
protected abstract void createDataSource(Map<Integer, DataSource> dataSourceMap, Properties p);
protected abstract void createDefaultDataSource(Map<Integer, DataSource> dataSourceMap);
public void setNewWeights(String newWeights)
{
this.newWeights = newWeights;
}
public void setOldWeights(String oldWeights)
{
this.oldWeights = oldWeights;
}
public Map<Integer, DataSource> getDataSourceMap()
{
return dataSourceMap;
}
}

Java代碼

1. package com.konceptusa.infinet.detach.datasource;   
2.   
3. import java.beans.PropertyVetoException;   
4. import java.util.Map;   
5. import java.util.Properties;   
6.   
7. import javax.sql.DataSource;   
8.   
9. import org.apache.commons.logging.Log;   
10. import org.apache.commons.logging.LogFactory;   
11.   
12. import com.konceptusa.framework.annotation.IllegalConfigException;   
13. import com.konceptusa.infinet.detach.config.MultiHibernateProperties;   
14. import com.mchange.v2.c3p0.ComboPooledDataSource;   
15.   
16. /**  
17.  * 基于c3p0連接池的動態獲取連接類  
18.  * @author Jwin  
19.  *   
20.  */  
21. public class DynamicC3p0DataSource extends AbstractDynamicDataSource   
22. {   
23.     private final static Log LOG = LogFactory.getLog(DynamicC3p0DataSource.class);   
24.     private int initialSize = 1;   
25.     private int maxActive = 1;   
26.     private int minActive = 1;   
27.     private int maxIdleTime = 30;   
28.     private String automaticTestTable = "Test";   
29.     private int acquireIncrement = 3;   
30.     private int maxStatements = 100;   
31.     private int maxStatementsPerConnection = 3;   
32.     private int numHelperThreads = 3;   
33.     private int idleConnectionTestPeriod = 30;   
34.     protected void createDefaultDataSource(Map<Integer, DataSource> dataSourceMap)   
35.     {   
36.         ComboPooledDataSource dataSource = new ComboPooledDataSource();   
37.         dataSource.setUser("sa");   
38.         dataSource.setPassword("");   
39.         dataSource.setJdbcUrl("jdbc:hsqldb:mem:" + getClass().getSimpleName().toLowerCase());   
40.         try  
41.         {   
42.             dataSource.setDriverClass("org.hsqldb.jdbcDriver");   
43.         } catch (PropertyVetoException e)   
44.         {   
45.             throw new IllegalConfigException(e);   
46.         }   
47.         dataSource.setInitialPoolSize(initialSize);   
48.         dataSource.setMaxPoolSize(maxActive);   
49.         dataSource.setMinPoolSize(minActive);   
50.         dataSource.setMaxIdleTime(maxIdleTime);   
51.         dataSource.setAcquireIncrement(acquireIncrement);   
52.         dataSource.setNumHelperThreads(numHelperThreads);   
53.         dataSource.setAutomaticTestTable(automaticTestTable);   
54.         dataSource.setMaxStatements(maxStatements);   
55.         dataSource.setMaxStatementsPerConnection(maxStatementsPerConnection);   
56.         dataSource.setIdleConnectionTestPeriod(idleConnectionTestPeriod);   
57.         dataSourceMap.put(defaultDataSourceId, dataSource);   
58.     }   
59.     @Override  
60.     protected void createDataSource(Map<Integer, DataSource> dataSourceMap, Properties p)   
61.     {   
62.         ComboPooledDataSource dataSource = new ComboPooledDataSource();   
63.         dataSource.setJdbcUrl(p.getProperty(MultiHibernateProperties.connectionUrlKey));   
64.         LOG.info("init datasource url " + dataSource.getJdbcUrl());   
65.         dataSource.setUser(p.getProperty(MultiHibernateProperties.connectionUsernameKey));   
66.         dataSource.setPassword(p.getProperty(MultiHibernateProperties.connectionPasswordKey));   
67.         try  
68.         {   
69.             dataSource.setDriverClass(p.getProperty(MultiHibernateProperties.connectionDriverClassKey));   
70.         } catch (PropertyVetoException e)   
71.         {   
72.             throw new IllegalConfigException(e);   
73.         }   
74.         dataSource.setInitialPoolSize(initialSize);   
75.         dataSource.setMaxPoolSize(maxActive);   
76.         dataSource.setMinPoolSize(minActive);   
77.         dataSource.setMaxIdleTime(maxIdleTime);   
78.         dataSource.setAcquireIncrement(acquireIncrement);   
79.         dataSource.setNumHelperThreads(numHelperThreads);   
80.         dataSource.setAutomaticTestTable(automaticTestTable);   
81.         dataSource.setMaxStatements(maxStatements);   
82.         dataSource.setMaxStatementsPerConnection(maxStatementsPerConnection);   
83.         dataSource.setIdleConnectionTestPeriod(idleConnectionTestPeriod);   
84.         String id = p.getProperty(MultiHibernateProperties.shardIdKey);   
85.         dataSourceMap.put(Integer.parseInt(id), dataSource);   
86.     }   
87.     public void setInitialSize(int initialSize)   
88.     {   
89.         this.initialSize = initialSize;   
90.     }   
91.     public void setMaxActive(int maxActive)   
92.     {   
93.         this.maxActive = maxActive;   
94.     }   
95.     public void setMaxIdleTime(int maxIdle)   
96.     {   
97.         this.maxIdleTime = maxIdle;   
98.     }      
99.     public void setAcquireIncrement(int acquireIncrement)   
100.     {   
101.         this.acquireIncrement = acquireIncrement;   
102.     }   
103.     public void setMaxStatements(int maxStatements)   
104.     {   
105.         this.maxStatements = maxStatements;   
106.     }   
107.     public void setMaxStatementsPerConnection(int maxStatementsPerConnection)   
108.     {   
109.         this.maxStatementsPerConnection = maxStatementsPerConnection;   
110.     }   
111.     public void setNumHelperThreads(int numHelperThreads)   
112.     {   
113.         this.numHelperThreads = numHelperThreads;   
114.     }   
115.     public void setAutomaticTestTable(String automaticTestTable)   
116.     {   
117.         this.automaticTestTable = automaticTestTable;   
118.     }   
119.     public void setMinActive(int minActive)   
120.     {   
121.         this.minActive = minActive;   
122.     }   
123.     public void setIdleConnectionTestPeriod(int idleConnectionTestPeriod)   
124.     {   
125.         this.idleConnectionTestPeriod = idleConnectionTestPeriod;   
126.     }   
127.        
128.   
129. }  

package com.konceptusa.infinet.detach.datasource;
import java.beans.PropertyVetoException;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.konceptusa.framework.annotation.IllegalConfigException;
import com.konceptusa.infinet.detach.config.MultiHibernateProperties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* 基于c3p0連接池的動態獲取連接類
* @author Jwin
*
*/
public class DynamicC3p0DataSource extends AbstractDynamicDataSource
{
private final static Log LOG = LogFactory.getLog(DynamicC3p0DataSource.class);
private int initialSize = 1;
private int maxActive = 1;
private int minActive = 1;
private int maxIdleTime = 30;
private String automaticTestTable = "Test";
private int acquireIncrement = 3;
private int maxStatements = 100;
private int maxStatementsPerConnection = 3;
private int numHelperThreads = 3;
private int idleConnectionTestPeriod = 30;
protected void createDefaultDataSource(Map<Integer, DataSource> dataSourceMap)
{
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setUser("sa");
dataSource.setPassword("");
dataSource.setJdbcUrl("jdbc:hsqldb:mem:" + getClass().getSimpleName().toLowerCase());
try
{
dataSource.setDriverClass("org.hsqldb.jdbcDriver");
} catch (PropertyVetoException e)
{
throw new IllegalConfigException(e);
}
dataSource.setInitialPoolSize(initialSize);
dataSource.setMaxPoolSize(maxActive);
dataSource.setMinPoolSize(minActive);
dataSource.setMaxIdleTime(maxIdleTime);
dataSource.setAcquireIncrement(acquireIncrement);
dataSource.setNumHelperThreads(numHelperThreads);
dataSource.setAutomaticTestTable(automaticTestTable);
dataSource.setMaxStatements(maxStatements);
dataSource.setMaxStatementsPerConnection(maxStatementsPerConnection);
dataSource.setIdleConnectionTestPeriod(idleConnectionTestPeriod);
dataSourceMap.put(defaultDataSourceId, dataSource);
}
@Override
protected void createDataSource(Map<Integer, DataSource> dataSourceMap, Properties p)
{
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl(p.getProperty(MultiHibernateProperties.connectionUrlKey));
LOG.info("init datasource url " + dataSource.getJdbcUrl());
dataSource.setUser(p.getProperty(MultiHibernateProperties.connectionUsernameKey));
dataSource.setPassword(p.getProperty(MultiHibernateProperties.connectionPasswordKey));
try
{
dataSource.setDriverClass(p.getProperty(MultiHibernateProperties.connectionDriverClassKey));
} catch (PropertyVetoException e)
{
throw new IllegalConfigException(e);
}
dataSource.setInitialPoolSize(initialSize);
dataSource.setMaxPoolSize(maxActive);
dataSource.setMinPoolSize(minActive);
dataSource.setMaxIdleTime(maxIdleTime);
dataSource.setAcquireIncrement(acquireIncrement);
dataSource.setNumHelperThreads(numHelperThreads);
dataSource.setAutomaticTestTable(automaticTestTable);
dataSource.setMaxStatements(maxStatements);
dataSource.setMaxStatementsPerConnection(maxStatementsPerConnection);
dataSource.setIdleConnectionTestPeriod(idleConnectionTestPeriod);
String id = p.getProperty(MultiHibernateProperties.shardIdKey);
dataSourceMap.put(Integer.parseInt(id), dataSource);
}
public void setInitialSize(int initialSize)
{
this.initialSize = initialSize;
}
public void setMaxActive(int maxActive)
{
this.maxActive = maxActive;
}
public void setMaxIdleTime(int maxIdle)
{
this.maxIdleTime = maxIdle;
}
public void setAcquireIncrement(int acquireIncrement)
{
this.acquireIncrement = acquireIncrement;
}
public void setMaxStatements(int maxStatements)
{
this.maxStatements = maxStatements;
}
public void setMaxStatementsPerConnection(int maxStatementsPerConnection)
{
this.maxStatementsPerConnection = maxStatementsPerConnection;
}
public void setNumHelperThreads(int numHelperThreads)
{
this.numHelperThreads = numHelperThreads;
}
public void setAutomaticTestTable(String automaticTestTable)
{
this.automaticTestTable = automaticTestTable;
}
public void setMinActive(int minActive)
{
this.minActive = minActive;
}
public void setIdleConnectionTestPeriod(int idleConnectionTestPeriod)
{
this.idleConnectionTestPeriod = idleConnectionTestPeriod;
}
}

Java代碼

1. package com.konceptusa.infinet.imsupport.detach;   
2.   
3. import java.util.ArrayList;   
4. import java.util.Collection;   
5. import java.util.Collections;   
6. import java.util.List;   
7.   
8. import org.apache.commons.logging.Log;   
9. import org.apache.commons.logging.LogFactory;   
10. import org.springframework.transaction.annotation.Propagation;   
11. import org.springframework.transaction.annotation.Transactional;   
12.   
13. import com.konceptusa.framework.annotation.IllegalConfigException;   
14. import com.konceptusa.framework.core.dao.HibernateQueryListCallback;   
15. import com.konceptusa.framework.core.dao.hql.Hql;   
16. import com.konceptusa.framework.core.service.BaseServiceSupport;   
17. import com.konceptusa.framework.core.service.Page;   
18. import com.konceptusa.framework.core.support.ObjectFactory;   
19. import com.konceptusa.infinet.annotation.handler.DetachDbServiceAnnotationHandler;   
20. import com.konceptusa.infinet.detach.CountId;   
21. import com.konceptusa.infinet.detach.CountIdComparetor;   
22. import com.konceptusa.infinet.detach.MagrateAble;   
23. import com.konceptusa.infinet.detach.QueryListAble;   
24. import com.konceptusa.infinet.detach.datasource.UseridContextHolder;   
25.   
26. /**  
27.  * 多個數據庫綜合查詢，簡單服務類父類  
28.  * @author Jwin  
29.  *  
30.  * @param <T>  
31.  */  
32. @Transactional(readOnly=true, rollbackFor = Exception.class)   
33. public abstract class BaseServiceSupportForMulti<T> extends BaseServiceSupport<T> implements QueryListAble<T>,MagrateAble<T>   
34. {   
35.     private final static Log LOG = LogFactory.getLog(BaseServiceSupportForMulti.class);   
36.     @Override  
37.     protected int findCountByHql(Hql hql)   
38.     {   
39.         List<Long> countList = (List<Long>) getHibernateTemplate().execute(   
40.                 new HibernateQueryListCallback(new Hql("select count(*) "  
41.                         + hql.getHql(), hql.getCache(), hql.getParameters())));   
42.         Long counts = 0L;   
43.         for(Long count : countList)   
44.         {   
45.             counts += count;   
46.         }   
47.         return counts.intValue();   
48.     }   
49.     @Transactional(readOnly=true, rollbackFor = Exception.class,propagation=Propagation.NOT_SUPPORTED)   
50.     public List<T> queryList(Hql hql, int from, int offset)   
51.     {   
52.         return queryListByHql(hql, from, offset);   
53.     }   
54.   
55.     public List<CountId> queryCount(Hql hql)   
56.     {   
57.         List<Object[]> list = queryListByHql(hql);   
58.         List<CountId> countList = new ArrayList<CountId>(list.size());    
59.         for(Object[] l : list)   
60.         {   
61.             if(l[1] != null)   
62.             {                  
63.                 CountId count = new CountId((Long) l[1],(Long)l[0]);   
64.                 countList.add(count);   
65.             }   
66.         }   
67.         Collections.sort(countList, new CountIdComparetor());   
68.         return countList;   
69.     }   
70.     protected String getBeanName(String name)   
71.     {   
72.         name = name.substring(0, name.length() - "Impl".length());   
73.         name = name.substring(0, 1).toLowerCase() + name.substring(1, name.length());   
74.         return name;   
75.     }   
76.     protected Page queryPageByHql(Hql hql,String useridName, int start, int offset)   
77.     {   
78.         Hql countHql = new Hql("select count(*),min(" + useridName + ") "  
79.                 + hql.getHql(), hql.getCache(), hql.getParameters());   
80.         return queryPageByHql(countHql, hql, start, offset);   
81.     }   
82.     //先查出各個數據庫的總數及標識，然后對標識進行排序，最后根據這個結果遍歷數據庫進行分頁查找，找滿結果則返回。   
83.     private Page queryPageByHql(Hql countHql,Hql listHql,int start, int offset)   
84.     {   
85.         QueryListAble<T> serviceShards = getShardsService();   
86.         QueryListAble<T> serviceDynamic = getDynamicService();   
87.         List<CountId> countList = serviceShards.queryCount(countHql);   
88.         //相對于當前之前所有數據庫的總數偏移   
89.         int totalCount = 0;   
90.         //相對于所有數據庫的結束偏移   
91.         int end = start + offset;   
92.         //相對于當前數據庫的開始偏移量   
93.         int startRelative = -1;   
94.         List<T> queryList = new ArrayList<T>(offset);   
95.         for(CountId count : countList)   
96.         {   
97.             totalCount += count.getCount();   
98.             //之前所有庫總數小于開始偏移量，繼續下一個數據庫   
99.             if(totalCount < start)   
100.             {   
101.                 continue;   
102.             }   
103.             //之前所有庫總數第一次大于開始偏移量   
104.             if(startRelative == -1)   
105.             {                  
106.                 startRelative = count.getCount().intValue() - (totalCount - start);   
107.             }   
108.             else  
109.             {   
110.                 startRelative = 0;   
111.             }   
112.             int relativeCount = totalCount - end;   
113.             if(relativeCount >= 0)   
114.             {   
115.                 UseridContextHolder.setUserid(count.getId());   
116.                 try  
117.                 {   
118.                     //計算相對于當前庫的偏移   
119.                     int offsetRelative = count.getCount().intValue() - relativeCount - startRelative;   
120.                     LOG.debug("query from " + startRelative + " offset " + offsetRelative + " for min(userid)=" + count.getId());   
121.                     queryList.addAll(serviceDynamic.queryList(listHql, startRelative, offsetRelative));                    
122.                 }finally  
123.                 {   
124.                     UseridContextHolder.removeUserid();   
125.                 }   
126.                 break;   
127.             }   
128.             UseridContextHolder.setUserid(count.getId());   
129.             try  
130.             {                  
131.                 //計算相對于當前庫的偏移   
132.                 int offsetRelative = totalCount - startRelative;   
133.                 LOG.debug("query from " + startRelative + " offset " + offsetRelative + " for min(userid)=" + count.getId());   
134.                 queryList.addAll(serviceDynamic.queryList(listHql, startRelative, offsetRelative));                    
135.             } finally  
136.             {   
137.                 UseridContextHolder.removeUserid();   
138.             }   
139.         }   
140.         totalCount = 0;   
141.         for(CountId count : countList)   
142.         {   
143.             totalCount += count.getCount();   
144.         }   
145.         return new Page<T>(totalCount, queryList);                   
146.     }   
147.     protected Page queryPageByHql(String hqlstr,String useridName, int start, int offset,Object ... values)   
148.     {   
149.         Hql listHql = Hql.createIndexHql(