springmvc+mybatis+pagehelper分页插件整合AbstractRoutingDataSource动态切换数据源（含例子代码）
程序员文章站
2022-07-14 11:25:57
...
一、前期工作
1）开发工具Eclipse+maven+jdk1.6以上
2）Tomcat服务器
3）技术架构Spring+Mybatis
二、在eclipse一个Maven的Web工程（略）
不懂的同学请参考博客http://www.cnblogs.com/leiOOlei/p/3361633.html
三、web.xml整合Springmvc和druid监控连接池
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
version="3.0">
<display-name>Archetype Created Web Application</display-name>
<!-- Spring和mybatis的配置文件 -->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:spring-mybatis.xml</param-value>
</context-param>
<!-- 编码过滤器 -->
<filter>
<filter-name>encodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<async-supported>true</async-supported>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>encodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!-- druid连接池配置 -->
<filter>
<filter-name>DruidWebStatFilter</filter-name>
<filter-class>com.alibaba.druid.support.http.WebStatFilter</filter-class>
<init-param>
<param-name>exclusions</param-name>
<param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*</param-value>
</init-param>
<init-param>
<param-name>sessionStatEnable</param-name>
<param-value>false</param-value>
</init-param>
<init-param>
<param-name>profileEnable</param-name>
<param-value>true</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>DruidWebStatFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!-- Spring监听器 -->
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!-- 防止Spring内存溢出监听器 -->
<listener>
<listener-class>org.springframework.web.util.IntrospectorCleanupListener</listener-class>
</listener>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!-- druid连接池配置 -->
<servlet>
<servlet-name>DruidStatView</servlet-name>
<servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DruidStatView</servlet-name>
<url-pattern>/druid/*</url-pattern>
</servlet-mapping>
<!-- Spring MVC servlet -->
<servlet>
<servlet-name>SpringMVC</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:spring-mvc.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
<async-supported>true</async-supported>
</servlet>
<servlet-mapping>
<servlet-name>SpringMVC</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>/index.jsp</welcome-file>
</welcome-file-list>
</web-app>
四、Mybatis分页插件 - PageHelper
https://github.com/pagehelper/Mybatis-PageHelper
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <setting name="logImpl" value="SLF4J" /> <!-- 字段为空时仍调用model的set方法或map的put方法 --> <setting name="callSettersOnNulls" value="true"/> </settings> <plugins> <!-- 分页查询插件 --> <plugin interceptor="com.github.pagehelper.PageHelper"> <property name="dialect" value="mysql" /> <property name="reasonable" value="true" /> </plugin> </plugins> </configuration>
五、springmvc的xml配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.1.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd">
<!-- 自动扫描该包，使SpringMVC认为包下用了@controller注解的类是控制器 -->
<context:component-scan base-package="com.flong.*" />
<!--避免IE执行AJAX时，返回JSON出现下载文件 -->
<bean id="mappingJacksonHttpMessageConverter"
class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter">
<property name="supportedMediaTypes">
<list>
<value>text/html;charset=UTF-8</value>
</list>
</property>
</bean>
<!-- 启动SpringMVC的注解功能，完成请求和注解POJO的映射 -->
<bean
class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter">
<property name="messageConverters">
<list>
<ref bean="mappingJacksonHttpMessageConverter" /> <!-- JSON转换器 -->
</list>
</property>
</bean>
<!-- 启动SpringMVC的注解功能，完成请求和注解POJO的映射 -->
<bean class="org.springframework.web.servlet.mvc.annotation.DefaultAnnotationHandlerMapping" />
<bean class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter">
<description>注解controller方法适配器</description>
<property name="messageConverters">
<list>
<!--JSON转换器 -->
<bean
class="com.alibaba.fastjson.support.spring.FastJsonHttpMessageConverter">
<property name="supportedMediaTypes" value="application/json;charset=UTF-8" />
<property name="features">
<array value-type="com.alibaba.fastjson.serializer.SerializerFeature">
<bean
class="org.springframework.beans.factory.config.FieldRetrievingFactoryBean">
<property name="staticField"
value="com.alibaba.fastjson.serializer.SerializerFeature.DisableCircularReferenceDetect" />
</bean>
<value>WriteMapNullValue</value>
<value>WriteNullStringAsEmpty</value>
</array>
</property>
</bean>
</list>
</property>
</bean>
<!-- 定义跳转的文件的前后缀 ，视图模式配置-->
<!-- <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
这里的配置我的理解是自动给后面action的方法return的字符串加上前缀和后缀，变成一个 可用的url地址
<property name="prefix" value="/WEB-INF/jsp/" />
<property name="suffix" value=".jsp" />
</bean>
-->
<!-- <bean id="velocityConfig" class="org.springframework.web.servlet.view.velocity.VelocityConfigurer">
<property name="resourceLoaderPath" value="/WEB-INF/jsp/"/>
</bean> -->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/WEB-INF/jsp/"/>
<property name="suffix" value=".jsp"/>
</bean>
<!-- 配置文件上传，如果没有使用文件上传可以不用配置，当然如果不配，那么配置文件中也不必引入上传组件包 -->
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<!-- 默认编码 -->
<property name="defaultEncoding" value="utf-8" />
<!-- 文件大小最大值 -->
<property name="maxUploadSize" value="10485760000" />
<!-- 内存中的最大值 -->
<property name="maxInMemorySize" value="40960" />
</bean>
<!-- 内容协商管理器 -->
<!--1、首先检查路径扩展名（如my.pdf）；2、其次检查Parameter（如my?format=pdf）；3、检查Accept Header-->
<bean id="contentNegotiationManager" class="org.springframework.web.accept.ContentNegotiationManagerFactoryBean">
<!-- 扩展名至mimeType的映射,即 /user.json => application/json -->
<property name="favorPathExtension" value="true"/>
<!-- 用于开启 /userinfo/123?format=json 的支持 -->
<property name="favorParameter" value="true"/>
<property name="parameterName" value="format"/>
<!-- 是否忽略Accept Header -->
<property name="ignoreAcceptHeader" value="false"/>
<property name="mediaTypes"> <!--扩展名到MIME的映射；favorPathExtension, favorParameter是true时起作用 -->
<value>
json=application/json
xml=application/xml
html=text/html
</value>
</property>
<!-- 默认的content type -->
<property name="defaultContentType" value="text/html"/>
</bean>
<!-- 这个映射配置主要是用来进行静态资源的访问
对静态资源文件的访问， 将无法mapping到Controller的path交给default servlet handler处理
<mvc:default-servlet-handler/>同等如下配置
<mvc:resources mapping="/js/**" location="/js/" cache-period="31556926"/>
<mvc:resources mapping="/css/**" location="/css/" />
<mvc:resources mapping="/**/**" location="/" />
<mvc:resources mapping="/WEB-INF/view/**" location="/WEB-INF/view/" /> -->
<mvc:default-servlet-handler/>
</beans>
六、Spring的AbstractRoutingDataSource实现动态数据源切换
参考博客
http://www.cnblogs.com/davidwang456/p/4318303.html
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.1.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
">
<!-- 自动扫描 -->
<context:component-scan base-package="com.flong.*" />
<!-- 引入配置文件 -->
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:jdbc.properties" />
</bean>
<!-- <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" lazy-init="false">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
初始化连接大小
<property name="initialSize" value="${jdbc.initialSize}"></property>
连接池最大数量
<property name="maxActive" value="${jdbc.maxActive}"></property>
连接池最大空闲
<property name="maxIdle" value="${jdbc.maxIdle}"></property>
连接池最小空闲
<property name="minIdle" value="${jdbc.minIdle}"></property>
获取连接最大等待时间
<property name="maxWait" value="${jdbc.maxWait}"></property>
</bean>
-->
<bean id="dbPasswordCallback" class="com.flong.utils.DBPasswordCallback">
<description>数据库连接回调密码解密</description>
</bean>
<bean id="statFilter" class="com.alibaba.druid.filter.stat.StatFilter" lazy-init="true">
<description>状态过滤器</description>
<property name="logSlowSql" value="true" />
<property name="mergeSql" value="true" />
</bean>
<!-- 操作数据库删除，修改，添加的数据源 -->
<bean id="writeDataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close" init-method="init" lazy-init="true">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!-- 初始化连接大小 -->
<property name="initialSize" value="${jdbc.initialSize}" />
<!-- 连接池最大数量 -->
<property name="maxActive" value="${jdbc.maxActive}" />
<!-- 连接池最小空闲 -->
<property name="minIdle" value="${jdbc.minIdle}" />
<!-- 获取连接最大等待时间 -->
<property name="maxWait" value="${jdbc.maxWait}" />
<!-- -->
<property name="defaultReadOnly" value="true" />
<property name="proxyFilters">
<list>
<ref bean="statFilter" />
</list>
</property>
<property name="filters" value="${druid.filters}" />
<property name="connectionProperties" value="password=${password}" />
<property name="passwordCallback" ref="dbPasswordCallback" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="validationQuery" value="SELECT 'x'" />
<property name="timeBetweenLogStatsMillis" value="60000" />
<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="${minEvictableIdleTimeMillis}" />
<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="${timeBetweenEvictionRunsMillis}" />
</bean>
<!-- 操作数据库读的数据源 -->
<bean id="readDataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close" init-method="init" lazy-init="true">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!-- 初始化连接大小 -->
<property name="initialSize" value="${jdbc.initialSize}" />
<!-- 连接池最大数量 -->
<property name="maxActive" value="${jdbc.maxActive}" />
<!-- 连接池最小空闲 -->
<property name="minIdle" value="${jdbc.minIdle}" />
<!-- 获取连接最大等待时间 -->
<property name="maxWait" value="${jdbc.maxWait}" />
<!-- -->
<property name="defaultReadOnly" value="true" />
<property name="proxyFilters">
<list>
<ref bean="statFilter" />
</list>
</property>
<property name="filters" value="${druid.filters}" />
<property name="connectionProperties" value="password=${password}" />
<property name="passwordCallback" ref="dbPasswordCallback" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="validationQuery" value="SELECT 'x'" />
<property name="timeBetweenLogStatsMillis" value="60000" />
<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="${minEvictableIdleTimeMillis}" />
<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="${timeBetweenEvictionRunsMillis}" />
</bean>
<!--利用AbstractRoutingDataSource实现动态数据源切换 -->
<bean id="dataSource" class="com.flong.utils.persistence.DynamicChooseDataSource" lazy-init="true">
<description>数据源</description>
<property name="targetDataSources">
<map key-type="java.lang.String" value-type="javax.sql.DataSource">
<!-- write -->
<entry key="write" value-ref="writeDataSource" />
<!-- read -->
<entry key="read" value-ref="readDataSource" />
</map>
</property>
<!--
从org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource这个类里面可以看到
defaultTargetDataSource这个对象，根据自己需求的情况默认的情况给一个默认的数据源.
-->
<property name="defaultTargetDataSource" ref="writeDataSource" />
<property name="methodType">
<map key-type="java.lang.String">
<!-- read -->
<entry key="read" value=",get,select,count,list,query" />
<!-- write -->
<entry key="write" value=",add,insert,create,update,delete,remove," />
</map>
</property>
</bean>
<!-- spring和MyBatis完美整合，不需要mybatis的配置映射文件 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<!-- 添加 mybatis-config配置上去。-->
<property name="configLocation" value="classpath:mybatis-config.xml" />
<!-- 扫描com.flong.pojo下面的所有类，减少mybatis的mapping所有实体类的路径（parameterType），直接写一个JavaBean即可 -->
<property name="typeAliasesPackage" value="com.flong.pojo"/>
<!-- 自动扫描mapping.xml文件 -->
<property name="mapperLocations" value="classpath:mapping/*.xml"></property>
</bean>
<!-- DAO接口所在包名，Spring会自动查找其下的类 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.flong.dao" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
</bean>
<!-- (事务管理)transaction manager, use JtaTransactionManager for global tx -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 配置事务通知属性 -->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<!-- 定义事务传播属性 -->
<tx:attributes>
<tx:method name="insert*" propagation="REQUIRED" />
<tx:method name="update*" propagation="REQUIRED" />
<tx:method name="edit*" propagation="REQUIRED" />
<tx:method name="save*" propagation="REQUIRED" />
<tx:method name="add*" propagation="REQUIRED" />
<tx:method name="new*" propagation="REQUIRED" />
<tx:method name="set*" propagation="REQUIRED" />
<tx:method name="remove*" propagation="REQUIRED" />
<tx:method name="delete*" propagation="REQUIRED" />
<tx:method name="change*" propagation="REQUIRED" />
<tx:method name="get*" propagation="REQUIRED" read-only="true" />
<tx:method name="find*" propagation="REQUIRED" read-only="true" />
<tx:method name="load*" propagation="REQUIRED" read-only="true" />
<tx:method name="*" propagation="REQUIRED" read-only="true" />
</tx:attributes>
</tx:advice>
<!-- 配置事务切面 -->
<aop:config>
<aop:pointcut id="serviceOperation" expression="execution(* com.flong.service.*.*(..))" />
<aop:advisor advice-ref="txAdvice" pointcut-ref="serviceOperation" />
</aop:config>
</beans>
七、mybatis的xml映射配置和数据库
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.flong.dao.UserMapper">
<resultMap id="BaseResultMap" type="com.flong.pojo.entity.User">
<id column="id" property="id" jdbcType="INTEGER" />
<result column="username" property="username" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
</resultMap>
<sql id="Base_Column_List">
id, username, password, age
</sql>
<!-- spring-mybatis.xml已经配置了可以省略包路径，直接用Vo名称即可 -->
<select id="list" resultType="User">
select
<include refid="Base_Column_List" />
from user
</select>
<select id="selectLogin" resultMap="BaseResultMap"
parameterType="String">
select * from user
where username = #{username,jdbcType=VARCHAR}
</select>
<select id="selectId" resultMap="BaseResultMap" parameterType="java.lang.Integer">
select * from user
where id = #{id,jdbcType=INTEGER}
</select>
<select id="selectByPrimaryKey" resultMap="BaseResultMap"
parameterType="java.lang.Integer">
select
<include refid="Base_Column_List" />
from user
where id = #{id,jdbcType=INTEGER}
</select>
<select id="query" parameterType="java.util.Map" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from user where 1=1
<if test="username != null">and username=#{username}</if>
<if test="password != null">and password=#{password}</if>
<if test="keyword != null">
and (username like "%"#{keyword}"%" or password like "%"#{keyword}"%" )
</if>
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from user
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="com.flong.pojo.entity.User">
insert into user (username, password, age,typeid)
values
(
#{username,jdbcType=VARCHAR},
#{password,jdbcType=VARCHAR},
#{age,jdbcType=INTEGER},
#{typeid,jdbcType=VARCHAR}
)
</insert>
<insert id="insertSelective" parameterType="com.flong.pojo.entity.User">
insert into user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="username != null">
username,
</if>
<if test="password != null">
password,
</if>
<if test="age != null">
age,
</if>
<if test="typeid != null">
typeid,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="username != null">
#{username,jdbcType=VARCHAR},
</if>
<if test="password != null">
#{password,jdbcType=VARCHAR},
</if>
<if test="age != null">
#{age,jdbcType=INTEGER},
</if>
<if test="typeid != null">
#{typeid,jdbcType=INTEGER},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.flong.pojo.entity.User">
update user
<set>
<if test="username != null">
username = #{username,jdbcType=VARCHAR},
</if>
<if test="password != null">
password = #{password,jdbcType=VARCHAR},
</if>
<if test="age != null">
age = #{age,jdbcType=INTEGER},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.flong.pojo.entity.User">
update user
set username = #{username,jdbcType=VARCHAR},
password = #{password,jdbcType=VARCHAR},
age = #{age,jdbcType=INTEGER}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
/*
Navicat MySQL Data Transfer
Source Server : 127.0.0.1
Source Server Version : 50515
Source Host : 127.0.0.1:3306
Source Database : sm
Target Server Type : MYSQL
Target Server Version : 50515
File Encoding : 65001
Date: 2016-07-01 14:05:54
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for type
-- ----------------------------
DROP TABLE IF EXISTS `type`;
CREATE TABLE `type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of type
-- ----------------------------
INSERT INTO `type` VALUES ('1', '老师');
INSERT INTO `type` VALUES ('2', '教授');
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`typeid` varchar(100) DEFAULT NULL COMMENT '类型',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '周伯通', '123456', '99', '1');
INSERT INTO `user` VALUES ('2', '瑛姑', 'user', '89', '2');
八、项目用到的jar和maven的配置
.m2\repository\junit\junit\4.11\junit-4.11.jar .m2\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar .m2\repository\javax\javaee-api\7.0\javaee-api-7.0.jar .m2\repository\com\sun\mail\javax.mail\1.5.0\javax.mail-1.5.0.jar .m2\repository\javax\activation\activation\1.1\activation-1.1.jar .m2\repository\org\apache\commons\commons-lang3\3.1\commons-lang3-3.1.jar .m2\repository\org\springframework\spring-core\4.0.2.RELEASE\spring-core-4.0.2.RELEASE.jar .m2\repository\commons-logging\commons-logging\1.1.3\commons-logging-1.1.3.jar .m2\repository\org\springframework\spring-web\4.0.2.RELEASE\spring-web-4.0.2.RELEASE.jar .m2\repository\org\springframework\spring-beans\4.0.2.RELEASE\spring-beans-4.0.2.RELEASE.jar .m2\repository\org\springframework\spring-context\4.0.2.RELEASE\spring-context-4.0.2.RELEASE.jar .m2\repository\org\springframework\spring-oxm\4.0.2.RELEASE\spring-oxm-4.0.2.RELEASE.jar .m2\repository\org\springframework\spring-tx\4.0.2.RELEASE\spring-tx-4.0.2.RELEASE.jar .m2\repository\org\springframework\spring-jdbc\4.0.2.RELEASE\spring-jdbc-4.0.2.RELEASE.jar .m2\repository\org\springframework\spring-webmvc\4.0.2.RELEASE\spring-webmvc-4.0.2.RELEASE.jar .m2\repository\org\springframework\spring-expression\4.0.2.RELEASE\spring-expression-4.0.2.RELEASE.jar .m2\repository\org\springframework\spring-aop\4.0.2.RELEASE\spring-aop-4.0.2.RELEASE.jar .m2\repository\aopalliance\aopalliance\1.0\aopalliance-1.0.jar .m2\repository\org\springframework\spring-context-support\4.0.2.RELEASE\spring-context-support-4.0.2.RELEASE.jar .m2\repository\org\springframework\spring-test\4.0.2.RELEASE\spring-test-4.0.2.RELEASE.jar .m2\repository\org\mybatis\mybatis\3.2.6\mybatis-3.2.6.jar .m2\repository\org\mybatis\mybatis-spring\1.2.2\mybatis-spring-1.2.2.jar .m2\repository\mysql\mysql-connector-java\5.1.30\mysql-connector-java-5.1.30.jar .m2\repository\commons-dbcp\commons-dbcp\1.2.2\commons-dbcp-1.2.2.jar .m2\repository\commons-pool\commons-pool\1.3\commons-pool-1.3.jar .m2\repository\jstl\jstl\1.2\jstl-1.2.jar .m2\repository\log4j\log4j\1.2.17\log4j-1.2.17.jar .m2\repository\com\alibaba\fastjson\1.2.8\fastjson-1.2.8.jar .m2\repository\org\slf4j\slf4j-api\1.7.7\slf4j-api-1.7.7.jar .m2\repository\org\slf4j\slf4j-log4j12\1.7.7\slf4j-log4j12-1.7.7.jar .m2\repository\org\codehaus\jackson\jackson-mapper-asl\1.9.13\jackson-mapper-asl-1.9.13.jar .m2\repository\org\codehaus\jackson\jackson-core-asl\1.9.13\jackson-core-asl-1.9.13.jar .m2\repository\commons-fileupload\commons-fileupload\1.3.1\commons-fileupload-1.3.1.jar .m2\repository\commons-io\commons-io\2.4\commons-io-2.4.jar .m2\repository\commons-codec\commons-codec\1.9\commons-codec-1.9.jar .m2\repository\com\github\pagehelper\pagehelper\4.1.3\pagehelper-4.1.3.jar .m2\repository\com\github\jsqlparser\jsqlparser\0.9.5\jsqlparser-0.9.5.jar .m2\repository\com\alibaba\druid\1.0.18\druid-1.0.18.jar .m2\repository\org\jsoup\jsoup\1.9.2\jsoup-1.9.2.jar .m2\repository\org\apache\velocity\velocity\1.7\velocity-1.7.jar .m2\repository\commons-collections\commons-collections\3.2.1\commons-collections-3.2.1.jar .m2\repository\commons-lang\commons-lang\2.4\commons-lang-2.4.jar .m2\repository\org\aspectj\aspectjrt\1.8.9\aspectjrt-1.8.9.jar .m2\repository\org\aspectj\aspectjweaver\1.8.9\aspectjweaver-1.8.9.jar
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>sm</groupId>
<artifactId>sm</artifactId>
<packaging>war</packaging>
<version>0.0.1-SNAPSHOT</version>
<name>M1 Maven Webapp</name>
<url>http://maven.apache.org</url>
<properties>
<!-- spring版本号 -->
<spring.version>4.0.2.RELEASE</spring.version>
<!-- mybatis版本号 -->
<mybatis.version>3.2.6</mybatis.version>
<!-- log4j日志文件管理包版本 -->
<slf4j.version>1.7.7</slf4j.version>
<log4j.version>1.2.17</log4j.version>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>javax</groupId>
<artifactId>javaee-api</artifactId>
<version>7.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.1</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<!-- 表示开发的时候引入，发布的时候不会加载此包 -->
<scope>test</scope>
</dependency>
<!-- spring核心包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-oxm</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- mybatis核心包 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<!-- mybatis/spring包 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<!-- 导入java ee jar 包 -->
<dependency>
<groupId>javax</groupId>
<artifactId>javaee-api</artifactId>
<version>7.0</version>
</dependency>
<!-- 导入Mysql数据库链接jar包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.30</version>
</dependency>
<!-- 导入dbcp的jar包，用来在applicationContext.xml中配置数据库 -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.2.2</version>
</dependency>
<!-- JSTL标签类 -->
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- 日志文件管理包 -->
<!-- log start -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j.version}</version>
</dependency>
<!-- 格式化对象，方便输出日志 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.1.41</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${slf4j.version}</version>
</dependency>
<!-- log end -->
<!-- 映入JSON -->
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.13</version>
</dependency>
<!-- 上传组件包 -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
<version>1.9</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.3</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.5</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.8</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.18</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.jsoup/jsoup -->
<dependency>
<groupId>org.jsoup</groupId>
<artifactId>jsoup</artifactId>
<version>1.9.2</version>
</dependency>
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity</artifactId>
<version>1.7</version>
</dependency>
<!-- 切面 -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>1.8.9</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/jstl/jstl -->
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
</dependencies>
<build>
<finalName>sm</finalName>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-war-plugin</artifactId>
<version>2.6</version>
<configuration>
<failOnMissingWebXml>false</failOnMissingWebXml>
</configuration>
</plugin>
</plugins>
</build>
</project>
九、Mybatis的The SQL Builder Class的使用Service层
package com.flong.service.impl;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.flong.BaseService;
import com.flong.dao.UserMapper;
import com.flong.pojo.entity.User;
import com.flong.pojo.vo.UserVo;
import com.flong.service.UserService;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageInfo;
@Service
public class UserServiceImpl extends BaseService implements UserService {
@Autowired
private UserMapper userMapper;
public User getUserById(int id) {
System.out.println(id + this.userMapper.selectId(id).getUsername());
return this.userMapper.selectId(id);
}
public Boolean getLoginUser(User userLogin) {
if (userLogin.getUsername().equals("") || (userLogin.getPassword().equals(""))) {
return false;
} else {
User user = new User();
if (this.userMapper.selectLogin(userLogin.getUsername()) != null) {
user = userMapper.selectLogin(userLogin.getUsername());
if (user.toString().isEmpty()) {
return false;
} else {
if (user.getPassword().equals(userLogin.getPassword())) {
return true;
} else {
return false;
}
}
} else {
return false;
}
}
}
public User user(User user) {
return null;
}
public User selectLogin(String username) {
return this.userMapper.selectLogin(username);
}
public User selectByPrimaryKey(int i) {
return this.userMapper.selectByPrimaryKey(i);
}
public User getUserByusername(String username) {
return this.userMapper.selectLogin(username);
}
public int inster(User user) {
int i = this.userMapper.insert(user);
return i;
}
public List<User> list() {
return userMapper.list();
}
/****
* ==============================================================通过The SQL Builder Class编写SQL进行操作数据库==================================
*
* http://www.mybatis.org/mybatis-3/index.html
* @param id
* @return
*/
public String listBySQL() {
return new SQL(){
{
SELECT(" A.id,A.username,A.password ,B.type ")//SELECT关键字
.FROM(" user A ")//FROM关键字
.LEFT_OUTER_JOIN(" type B on A.typeid=B.id"); //左连接
}}.toString();
}
/**
*
* @param user 这个是vo传值.#的字段要和vo实体一致才可以能获取到值.
* @return
*/
public String insertBySQL(UserVo user) {
String sql = new SQL()
.INSERT_INTO("user")
.VALUES("username, password ,typeid,age", "#{username}, #{password},#{type},#{age}")
.toString();
return sql;
}
public String updateBySQL(UserVo user) {
return new SQL() {{
UPDATE("user");
SET("username = #{username}");
WHERE("id = #{id}");
}}.toString();
}
public String deleteSql(String id) {
return new SQL() {{
DELETE_FROM("user");
WHERE("ID = #{id}");
}}.toString();
}
/****
* ==============================================================整合pagehelper分页==================================
*
* @param id
* @return
*/
public PageInfo<?> query(Map<String, Object> params) {
this.startPage(params);
Page<User> list = userMapper.query(params);
return new PageInfo<User>(list);
}
}
十、Mybatis的The SQL Builder Class的使用Dao层
package com.flong.dao;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.SelectProvider;
import com.flong.pojo.entity.User;
import com.flong.pojo.vo.UserVo;
import com.flong.service.impl.UserServiceImpl;
import com.github.pagehelper.Page;
public interface UserMapper {
/****
* ==============================================================通过xml进行操作数据库==============================================================
* @param id
* @return
*/
int deleteByPrimaryKey(Integer id);
int insert(User record);
int insertSelective(User record);
User selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(User record);
int updateByPrimaryKey(User record);
User selectId(int id);
User selectLogin(String username);
List<User> list();
/****
* ==============================================================通过注解和Java编写SQL进行操作数据库==============================================================
* @param id
* @return
*/
/**
* 通过mybatis的SQL方法进行查询，无xml配置的出来
* 告诉method = "listBySQL"方法的提供方，是来原于UserServiceImpl这个实现类里面获取的。
* List<UserVo>这个UserVo相当于是Vo
*/
@SelectProvider(type = UserServiceImpl.class, method = "listBySQL")
List<UserVo> listBySQL();
/**
* 插入
* @param user
* @return
*/
@SelectProvider(type = UserServiceImpl.class, method = "insertBySQL")
String insertBySQL(UserVo user);
/**
* 修改
* @param user
* @return
*/
@SelectProvider(type = UserServiceImpl.class, method = "updateBySQL")
String updateBySQL(UserVo user);
/**
* 删除
* @param id
* @return
*/
@SelectProvider(type = UserServiceImpl.class, method = "deleteSql")
String deleteSql(String id);
Page<User> query(Map<String, Object> params);
}
十一、spring的控制层
package com.flong.controller;
import java.util.List;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import com.flong.BaseController;
import com.flong.pojo.entity.User;
import com.flong.service.UserService;
@Controller
@RequestMapping("/user")
public class UserController extends BaseController{
@Resource
private UserService userService;
/**
*
* @param request
* @param model
*/
@RequestMapping(value="/list", method = {RequestMethod.POST,RequestMethod.GET})
public void list(HttpServletRequest request,HttpServletResponse response,Model model){
List<User> list=userService.list();
writeJson(list, response);
}
/**
* @param request
* @param model
* @return
*/
@RequestMapping(value="/register",method = {RequestMethod.POST,RequestMethod.GET})
public String registerIndex(HttpServletRequest request,Model model){
User user = new User();
user.setUsername("张三");
user.setPassword("123466");
user.setAge(45);
user.setTypeid("2");
this.userService.inster(user);
return "index";
}
}
package com.flong.controller;
import java.util.List;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import com.flong.BaseController;
import com.flong.dao.UserMapper;
import com.flong.pojo.vo.UserVo;
@Controller
@RequestMapping("/user2")
public class UserController2 extends BaseController{
@Resource
private UserMapper userMapper;
/**
* 查询
* @param request
* @param response
* @param model
*/
@RequestMapping(value="/listBySQL", method = {RequestMethod.POST,RequestMethod.GET})
public void listBySQL(HttpServletRequest request,HttpServletResponse response,Model model){
List<UserVo> list=userMapper.listBySQL();
writeJson(list, response);
}
/**
* 插入
* @param request
* @param response
* @param model
*/
@RequestMapping(value="/insertBySQL", method = {RequestMethod.POST,RequestMethod.GET})
public void insertBySQL(HttpServletRequest request,HttpServletResponse response,Model model){
UserVo user = new UserVo();
user.setUsername("张三");
user.setPassword("123466");
user.setAge(45);
user.setType("2");
userMapper.insertBySQL(user);
writeJson(user, response);
}
/**
* 修改
* @param request
* @param response
* @param model
*/
@RequestMapping(value="/updateBySQL", method = {RequestMethod.POST,RequestMethod.GET})
public void updateBySQL(HttpServletRequest request,HttpServletResponse response,Model model){
UserVo user = new UserVo();
user.setUsername("周伯通");
user.setId(1);
userMapper.updateBySQL(user);
writeJson(user, response);
}
/**
* 删除
* @param request
* @param response
* @param model
*/
@RequestMapping(value="/deleteSql", method = {RequestMethod.POST,RequestMethod.GET})
public void deleteSql(HttpServletRequest request,HttpServletResponse response,Model model){
String deleteSql = userMapper.deleteSql("1");
writeJson(deleteSql, response);
}
}
十二、利用AbstractRoutingDataSource实现动态数据源切换
package com.flong.utils.persistence;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
*
* 利用AbstractRoutingDataSource实现动态数据源切换
* spring2.0以后增加了AbstractRoutingDataSource
* 自定义一个类继承AbstractRoutingDataSource，重构一个setMethodType支持自定义开头的方法
* 进行使用数据源的操作数据库
*/
public class DynamicChooseDataSource extends AbstractRoutingDataSource {
public static Map<String, List<String>> METHODTYPE = new HashMap<String, List<String>>();
/**
* 获取数据源名称
*/
protected Object determineCurrentLookupKey() {
return HandleDataSource.getDataSource();
}
/**
* 设置方法名前缀对应的数据源
* @param map
*/
public void setMethodType(Map<String, String> map) {
for (String key : map.keySet()) {
List<String> v = new ArrayList<String>();
String[] types = map.get(key).split(",");
for (String type : types) {
if (StringUtils.isNotBlank(type)) {
v.add(type);
}
}
METHODTYPE.put(key, v);
}
}
}
package com.flong.utils.persistence;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.stereotype.Component;
/**
* 切换数据源(不同方法调用不同数据源)
*/
@Aspect
@Component
@EnableAspectJAutoProxy(proxyTargetClass = true)
public class DataSourceAspect {
private static Logger logger =Logger.getLogger(DataSourceAspect.class);
@Pointcut("execution(* com.flong.*.dao.*.*(..))")
public void aspect() {
}
/**
* 配置前置通知,使用在方法aspect()上注册的切入点
*/
@Before("aspect()")
public void before(JoinPoint point) {
String className = point.getTarget().getClass().getName();
String method = point.getSignature().getName();
logger.info(className + "." + method + "(" + StringUtils.join(point.getArgs(), ",") + ")");
try {
for (String key : DynamicChooseDataSource.METHODTYPE.keySet()) {
for (String type : DynamicChooseDataSource.METHODTYPE.get(key)) {
if (method.startsWith(type)) {
HandleDataSource.putDataSource(key);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
package com.flong.utils.persistence;
public class HandleDataSource {
/***
* 数据源名称线程池
*/
private static final ThreadLocal<String> holder = new ThreadLocal<String>();
public static void putDataSource(String datasource) {
holder.set(datasource);
}
public static String getDataSource() {
return holder.get();
}
}
十三、源代码和图解
http://download.csdn.net/detail/l_ji_l/9570154
上一篇： 130个微信小程序源代码案例