查看: 2161|回复: 0

[Java学习] Spring mvc hibernate多数据源(讀寫分離)

发表于 2016-5-14 11:26:44
太阳http代理AD
Spring MVC Hibernate集成, 使用多數據源,將數據庫分為讀數據庫和寫數據庫;
思路: 使用裝飾著模式將DataSource進行封裝, 使用AOP在執行查詢或寫入前動態切換數據源(本文默認為只讀數據源,在執行更新語句時將數據源切換為只寫數據源writeDataSource, 執行後立即切換為只讀數據源readDataSource);

說明:本文存在重複代碼部分, 請自行優化,感謝! 存在有問題的地方請大家指出, 感謝!
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans"
  3. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
  4. xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
  5. xmlns:task="http://www.springframework.org/schema/task" xmlns:tx="http://www.springframework.org/schema/tx"
  6. xmlns:util="http://www.springframework.org/schema/util"
  7. xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
  8. http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd
  9. http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd
  10. http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.2.xsd
  11. http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd
  12. http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.2.xsd">
  13. <!-- <bean id="propertyConfiguer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
  14. p:location="/WEB-INF/init.properties" /> -->
  15. <context:component-scan base-package="com.mlb.*" />
  16. <aop:aspectj-autoproxy />
  17. <!-- 导入资源文件 -->
  18. <context:property-placeholder location="/WEB-INF/init.properties" />
  19. <bean id="readDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
  20. <property name="driverClass">
  21. <value>${read.driver}</value>
  22. </property>
  23. <property name="jdbcUrl">
  24. <value>${read.url}</value>
  25. </property>
  26. <property name="user">
  27. <value>${read.username}</value>
  28. </property>
  29. <property name="password">
  30. <value>${read.password}</value>
  31. </property>
  32. <property name="minPoolSize">
  33. <value>5</value>
  34. </property>
  35. <property name="maxPoolSize">
  36. <value>30</value>
  37. </property>
  38. <property name="initialPoolSize">
  39. <value>10</value>
  40. </property>
  41. <property name="maxIdleTime">
  42. <value>${datasource.maxIdle}</value>
  43. </property>
  44. <property name="acquireIncrement">
  45. <value>5</value>
  46. </property>
  47. <property name="maxStatements">
  48. <value>0</value>
  49. </property>
  50. <property name="checkoutTimeout">
  51. <value>5000</value>
  52. </property>
  53. <property name="idleConnectionTestPeriod">
  54. <value>60</value>
  55. </property>
  56. <property name="acquireRetryAttempts">
  57. <value>30</value>
  58. </property>
  59. <property name="acquireRetryDelay">
  60. <value>1000</value>
  61. </property>
  62. <property name="breakAfterAcquireFailure">
  63. <value>false</value>
  64. </property>
  65. <property name="testConnectionOnCheckout">
  66. <value>false</value>
  67. </property>
  68. <property name="testConnectionOnCheckin">
  69. <value>false</value>
  70. </property>
  71. </bean>
  72. <bean id="writeDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
  73. <property name="driverClass">
  74. <value>${write.driver}</value>
  75. </property>
  76. <property name="jdbcUrl">
  77. <value>${write.url}</value>
  78. </property>
  79. <property name="user">
  80. <value>${write.username}</value>
  81. </property>
  82. <property name="password">
  83. <value>${write.password}</value>
  84. </property>
  85. <property name="minPoolSize">
  86. <value>5</value>
  87. </property>
  88. <property name="maxPoolSize">
  89. <value>30</value>
  90. </property>
  91. <property name="initialPoolSize">
  92. <value>10</value>
  93. </property>
  94. <property name="maxIdleTime">
  95. <value>${datasource.maxIdle}</value>
  96. </property>
  97. <property name="acquireIncrement">
  98. <value>5</value>
  99. </property>
  100. <property name="maxStatements">
  101. <value>0</value>
  102. </property>
  103. <property name="checkoutTimeout">
  104. <value>5000</value>
  105. </property>
  106. <property name="idleConnectionTestPeriod">
  107. <value>60</value>
  108. </property>
  109. <property name="acquireRetryAttempts">
  110. <value>30</value>
  111. </property>
  112. <property name="acquireRetryDelay">
  113. <value>1000</value>
  114. </property>
  115. <property name="breakAfterAcquireFailure">
  116. <value>false</value>
  117. </property>
  118. <property name="testConnectionOnCheckout">
  119. <value>false</value>
  120. </property>
  121. <property name="testConnectionOnCheckin">
  122. <value>false</value>
  123. </property>
  124. </bean>
  125. <bean id="dataSource" class="com.mlb.util.jdbc.MultiDataSource">
  126. <property name="dataSourceMap">
  127. <map>
  128. <entry key="readDataSource" value-ref="readDataSource"></entry>
  129. <entry key="writeDataSource" value-ref="writeDataSource"></entry>
  130. </map>
  131. </property>
  132. <property name="dataSource" ref="readDataSource"></property>
  133. </bean>
  134. <bean id="sessionFactory"
  135. class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
  136. <!-- 配置数据源属性 -->
  137. <property name="dataSource" ref="dataSource"></property>
  138. <!-- 使用 hibernateProperties 属相来配置 Hibernate 原生的属性 -->
  139. <property name="hibernateProperties">
  140. <props>
  141. <prop key="hibernate.dialect">${hibernate.dialect}</prop>
  142. <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
  143. <prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
  144. <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
  145. <prop key="hibernate.jdbc.fetch_size">${hibernate.jdbc.fetch_size}</prop>
  146. <prop key="hibernate.jdbc.batch_size">${hibernate.jdbc.batch_size}</prop>
  147. <prop key="hibernate.connection.autocommit">true</prop>
  148. <prop key="hibernate.current_session_context_class">thread</prop>
  149. </props>
  150. </property>
  151. <property name="mappingLocations" value="classpath:com/mlb/hbm/*.hbm.xml"></property>
  152. </bean>
  153. <bean id="changeDataSource" class="com.mlb.util.aop.DataSourceAop"></bean>
  154. <!-- 配置 Spring 的声明式事务 -->
  155. <!-- 1. 配置事务管理器 -->
  156. <bean id="transactionManager"
  157. class="org.springframework.orm.hibernate4.HibernateTransactionManager">
  158. <property name="sessionFactory" ref="sessionFactory"></property>
  159. </bean>
  160. <!-- 2. 配置事务属性, 需要事务管理器 -->
  161. <tx:advice id="txAdvice" transaction-manager="transactionManager">
  162. <tx:attributes>
  163. <tx:method name="add*" read-only="false" propagation="REQUIRED" />
  164. <tx:method name="save*" read-only="false" propagation="REQUIRED" />
  165. <tx:method name="delete*" read-only="false" propagation="REQUIRED" />
  166. <tx:method name="update*" read-only="false" propagation="REQUIRED" />
  167. <tx:method name="get*" read-only="false" propagation="REQUIRED" />
  168. </tx:attributes>
  169. </tx:advice>
  170. <!-- 3. 配置事务切点, 并把切点和事务属性关联起来 -->
  171. <aop:config>
  172. <aop:pointcut expression="execution(* com.mlb.dao.*.*(..))"
  173. id="txPointcut" />
  174. <aop:advisor advice-ref="txAdvice" pointcut-ref="txPointcut" />
  175. <!-- 通過AOP修改數據源 -->
  176. <aop:aspect id="dbAspect" ref="changeDataSource">
  177. <aop:pointcut
  178. expression="execution(* com.mlb.dao.*.*save*(..)) or execution(* com.mlb.dao.*.*delete*(..)) or execution(* com.mlb.dao.*.*Update*(..)) or execution(* com.mlb.dao.*.*update*(..))"
  179. id="dbPointcut" />
  180. <aop:before pointcut-ref="dbPointcut" method="changeToWrite" />
  181. <aop:after pointcut-ref="dbPointcut" method="changeToRead" />
  182. </aop:aspect>
  183. </aop:config>
  184. </beans>
复制代码
  1. #Read only
  2. read.driver=com.mysql.jdbc.Driver
  3. read.url=jdbc:mysql://localhost:3306/project?useUnicode=true&characterEncoding=UTF-8
  4. read.username=root
  5. read.password=root
  6. #Write only
  7. write.driver=com.mysql.jdbc.Driver
  8. write.url=jdbc:mysql://localhost:3306/project?useUnicode=true&characterEncoding=UTF-8
  9. write.username=root
  10. write.password=root
  11. datasource.maxActive=300
  12. datasource.maxIdle=1000
  13. datasource.maxWait=2500
  14. # action to take when the pool is exhausted {grow|block|fail}
  15. datasource.whenExhaustedAction=fail
  16. # connections will be validated before they are borrowed from the pool
  17. datasource.testOnBorrow=false
  18. # remove abandoned connections if they exceed the removeAbandonedTimeout
  19. datasource.removeAbandoned=true
  20. # seconds before an abandoned connection can be removed
  21. datasource.removeAbandonedTimeout=10
  22. datasource.defaultAutoCommit=true
  23. #mysql
  24. #hibernate.dialect=org.hibernate.dialect.MySQL2Dialect
  25. hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
  26. hibernate.jdbc.batch_size=100
  27. hibernate.jdbc.fetch_size=100
  28. hibernate.statement_cache.size=60
  29. #hibernate.max_fetch_depth=1
  30. #hibernate.show_sql=false
  31. hibernate.show_sql=false
  32. hibernate.format_sql=true
  33. hibernate.hbm2ddl.auto=update
  34. ###########################Hibernate DBCP END ######################################
  35. hibernate.jdbc.use_scrollable_resultset=true
复制代码
  1. package com.mlb.util.jdbc;
  2. import java.io.PrintWriter;
  3. import java.sql.Connection;
  4. import java.sql.SQLException;
  5. import java.sql.SQLFeatureNotSupportedException;
  6. import java.util.Map;
  7. import java.util.logging.Logger;
  8. import javax.sql.DataSource;
  9. public class MultiDataSource implements DataSource {
  10. private DataSource dataSource;
  11. private Map<String, DataSource> dataSourceMap;
  12. public MultiDataSource() {
  13. }
  14. public MultiDataSource(Map<String, DataSource> dataSourceMap, String defaultDataSourceName) {
  15. this.dataSourceMap = dataSourceMap;
  16. if (null != dataSourceMap) {
  17. this.dataSource = dataSourceMap.get(defaultDataSourceName);
  18. }
  19. }
  20. @Override
  21. public PrintWriter getLogWriter() throws SQLException {
  22. return getDataSource().getLogWriter();
  23. }
  24. @Override
  25. public void setLogWriter(PrintWriter out) throws SQLException {
  26. getDataSource().setLogWriter(out);
  27. }
  28. @Override
  29. public void setLoginTimeout(int seconds) throws SQLException {
  30. getDataSource().setLoginTimeout(seconds);
  31. }
  32. @Override
  33. public int getLoginTimeout() throws SQLException {
  34. return getDataSource().getLoginTimeout();
  35. }
  36. @Override
  37. public Logger getParentLogger() throws SQLFeatureNotSupportedException {
  38. return getDataSource().getParentLogger();
  39. }
  40. @Override
  41. public <T> T unwrap(Class<T> iface) throws SQLException {
  42. return getDataSource().unwrap(iface);
  43. }
  44. @Override
  45. public boolean isWrapperFor(Class<?> iface) throws SQLException {
  46. return getDataSource().isWrapperFor(iface);
  47. }
  48. @Override
  49. public Connection getConnection() throws SQLException {
  50. return getDataSource().getConnection();
  51. }
  52. @Override
  53. public Connection getConnection(String username, String password) throws SQLException {
  54. return getDataSource().getConnection(username, password);
  55. }
  56. public DataSource getDataSource() {
  57. return getDataSource(DataSourceObserver.getDataSourceName());
  58. }
  59. public DataSource getDataSource(String dataSourceName) {
  60. if (null != dataSourceName) {
  61. DataSource ds = dataSourceMap.get(dataSourceName);
  62. return null != ds ? ds : dataSource;
  63. }
  64. return dataSource;
  65. }
  66. public void setDataSource(DataSource dataSource) {
  67. this.dataSource = dataSource;
  68. }
  69. public Map<String, DataSource> getDataSourceMap() {
  70. return dataSourceMap;
  71. }
  72. public void setDataSourceMap(Map<String, DataSource> dataSourceMap) {
  73. this.dataSourceMap = dataSourceMap;
  74. }
  75. }
复制代码
  1. package com.mlb.util.jdbc;
  2. public class DataSourceObserver {
  3. private static ThreadLocal<String> local = new ThreadLocal<String>();
  4. public static void putDataSourceName(String dataSourceName) {
  5. local.set(dataSourceName);
  6. }
  7. public static String getDataSourceName() {
  8. return local.get();
  9. }
  10. }
复制代码
  1. package com.mlb.util.aop;
  2. import com.mlb.util.jdbc.DataSourceObserver;
  3. public class DataSourceAop {
  4. public void changeToRead() {
  5. DataSourceObserver.putDataSourceName("readDataSource");
  6. }
  7. public void changeToWrite() {
  8. DataSourceObserver.putDataSourceName("writeDataSource");
  9. }
  10. }
复制代码
  1. package com.mlb.dao;
  2. import java.io.Serializable;
  3. import java.util.Collection;
  4. import java.util.Iterator;
  5. import java.util.List;
  6. import org.hibernate.Query;
  7. import org.hibernate.Session;
  8. import org.hibernate.SessionFactory;
  9. import org.hibernate.Transaction;
  10. import org.springframework.beans.factory.annotation.Autowired;
  11. import org.springframework.stereotype.Repository;
  12. @Repository("baseDao")
  13. @SuppressWarnings("unchecked")
  14. public class BaseDao {
  15. @Autowired
  16. private SessionFactory sessionFactory;
  17. public <T> void saveObject(T t) {
  18. Session session = sessionFactory.getCurrentSession();
  19. Transaction tran = session.beginTransaction();
  20. session.save(t);
  21. tran.commit();
  22. }
  23. public <T> void saveAll(Collection<T> coll) {
  24. Session session = sessionFactory.getCurrentSession();
  25. Transaction tran = session.beginTransaction();
  26. int i = 0;
  27. for (Iterator<T> iterator = coll.iterator(); iterator.hasNext();) {
  28. T t = (T) iterator.next();
  29. session.save(t);
  30. if(i % 100 == 0){
  31. session.flush();
  32. session.clear();
  33. }
  34. i++;
  35. }
  36. tran.commit();
  37. }
  38. public <T> void deleteObject(T t) {
  39. Session session = sessionFactory.getCurrentSession();
  40. Transaction tran = session.beginTransaction();
  41. session.delete(t);
  42. tran.commit();
  43. }
  44. public <T> void deleteAll(Collection<T> coll) {
  45. Session session = sessionFactory.getCurrentSession();
  46. Transaction tran = session.beginTransaction();
  47. for (Iterator<T> iterator = coll.iterator(); iterator.hasNext();) {
  48. T t = (T) iterator.next();
  49. session.delete(t);
  50. }
  51. tran.commit();
  52. }
  53. public <T> void updateObject(T t) {
  54. Session session = sessionFactory.getCurrentSession();
  55. Transaction tran = session.beginTransaction();
  56. session.update(t);
  57. tran.commit();
  58. }
  59. public <T> void updateAll(Collection<T> coll) {
  60. Session session = sessionFactory.getCurrentSession();
  61. Transaction tran = session.beginTransaction();
  62. int i = 0;
  63. for (Iterator<T> iterator = coll.iterator(); iterator.hasNext();) {
  64. T t = (T) iterator.next();
  65. session.update(t);
  66. if(i % 100 == 0){
  67. session.flush();
  68. session.clear();
  69. }
  70. i++;
  71. }
  72. tran.commit();
  73. }
  74. public <T> void saveOrUpdateObject(T t) {
  75. Session session = sessionFactory.getCurrentSession();
  76. Transaction tran = session.beginTransaction();
  77. session.saveOrUpdate(t);
  78. tran.commit();
  79. }
  80. public <T> void saveOrUpdateAll(Collection<T> coll) {
  81. Session session = sessionFactory.getCurrentSession();
  82. Transaction tran = session.beginTransaction();
  83. for (Iterator<T> iterator = coll.iterator(); iterator.hasNext();) {
  84. T t = (T) iterator.next();
  85. session.saveOrUpdate(t);
  86. }
  87. tran.commit();
  88. }
  89. public int executeHqlUpdate(String hql, Object...objects){
  90. Session session = sessionFactory.getCurrentSession();
  91. Transaction tran = session.beginTransaction();
  92. Query query = session.createQuery(hql);
  93. setParameter(query, objects);
  94. int result = query.executeUpdate();
  95. tran.commit();
  96. return result;
  97. }
  98. public int executeSqlUpdate(String sql, Object...objects){
  99. Session session = sessionFactory.getCurrentSession();
  100. Transaction tran = session.beginTransaction();
  101. Query query = session.createSQLQuery(sql);
  102. setParameter(query, objects);
  103. int result = query.executeUpdate();
  104. tran.commit();
  105. return result;
  106. }
  107. public <T> T findObject(Class<T> cls, Serializable id) {
  108. Session session = sessionFactory.getCurrentSession();
  109. Transaction tran = session.beginTransaction();
  110. T t = (T) session.get(cls, id);
  111. tran.commit();
  112. return t;
  113. }
  114. public <T> T findFirstObject(String hql, Object... objects) {
  115. Session session = sessionFactory.getCurrentSession();
  116. Transaction tran = session.beginTransaction();
  117. Query query = session.createQuery(hql);
  118. setParameter(query, objects);
  119. query.setMaxResults(1);
  120. query.setFirstResult(0);
  121. List<T> list = query.list();
  122. tran.commit();
  123. return null != list && list.size() > 0 ? list.get(0) : null;
  124. }
  125. public <T> List<T> findObjectList(String hql, Object... objects) {
  126. Session session = sessionFactory.getCurrentSession();
  127. Transaction tran = session.beginTransaction();
  128. Query query = session.createQuery(hql);
  129. setParameter(query, objects);
  130. List<T> list = query.list();
  131. tran.commit();
  132. return list;
  133. }
  134. public <T> List<T> executeHqlQuery(String hql, Object...objects){
  135. Session session = sessionFactory.getCurrentSession();
  136. Transaction tran = session.beginTransaction();
  137. Query query = session.createQuery(hql);
  138. setParameter(query, objects);
  139. List<T> result = query.list();
  140. tran.commit();
  141. return result;
  142. }
  143. public <T> List<T> executeSqlQuery(String sql, Object...objects){
  144. Session session = sessionFactory.getCurrentSession();
  145. Transaction tran = session.beginTransaction();
  146. Query query = session.createSQLQuery(sql);
  147. setParameter(query, objects);
  148. List<T> result = query.list();
  149. tran.commit();
  150. return result;
  151. }
  152. void setParameter(Query query, Object... objects) {
  153. if(null == objects) return;
  154. for (int i = 0; i < objects.length; i++) {
  155. query.setParameter(i, objects[i]);
  156. }
  157. }
  158. }
复制代码
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3. xmlns="http://java.sun.com/xml/ns/javaee"
  4. xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
  5. id="WebApp_ID" version="3.0">
  6. <display-name>mlb</display-name>
  7. <!-- needed for ContextLoaderListener -->
  8. <context-param>
  9. <param-name>contextConfigLocation</param-name>
  10. <param-value>/WEB-INF/applicationContext.xml</param-value>
  11. </context-param>
  12. <!-- Bootstraps the root web application context before servlet initialization -->
  13. <listener>
  14. <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
  15. </listener>
  16. <!-- The front controller of this Spring Web application, responsible for
  17. handling all application requests -->
  18. <servlet>
  19. <servlet-name>springDispatcherServlet</servlet-name>
  20. <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
  21. <init-param>
  22. <param-name>contextConfigLocation</param-name>
  23. <param-value>/WEB-INF/dispatcherServlet.xml</param-value>
  24. </init-param>
  25. <load-on-startup>1</load-on-startup>
  26. </servlet>
  27. <!-- Map all requests to the DispatcherServlet for handling -->
  28. <servlet-mapping>
  29. <servlet-name>springDispatcherServlet</servlet-name>
  30. <url-pattern>/*</url-pattern>
  31. </servlet-mapping>
  32. <!-- filter for hibernate session -->
  33. <filter>
  34. <filter-name>SpringOpenSessionInViewFilter</filter-name>
  35. <filter-class>org.springframework.orm.hibernate4.support.OpenSessionInViewFilter</filter-class>
  36. </filter>
  37. <filter-mapping>
  38. <filter-name>SpringOpenSessionInViewFilter</filter-name>
  39. <url-pattern>/*</url-pattern>
  40. </filter-mapping>
  41. <session-config>
  42. <session-timeout>15</session-timeout>
  43. </session-config>
  44. </web-app>
复制代码


太阳http代理AD
回复

使用道具 举报