人生最重要的一点是,永远不要迷失自己。
项目结构如图:
首先是各种配置文件,属性文件
mycat.properties内容:
#数据库连接配置
#以下是mycat中间件连接
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:8066/SSMDB?useUnicode=true&characterEncoding=UTF-8
jdbc.username=wu
jdbc.password=hello123
#以下是mysql连接
#jdbc.url=jdbc:mysql://localhost:3306/MyCAT_DB?useUnicode=true&characterEncoding=UTF-8
#jdbc.username=root
#jdbc.password=123456
#定义初始连接数
jdbc.initialPoolSize=10
jdbc.maxPoolSize=200
jdbc.minPoolSize=5
jdbc.maxIdleTime=20
jdbc.acquireIncrement=10
jdbc.maxStatements=50
jdbc.idleConnectionTestPeriod=60
jdbc.initialSize=5
#定义最大空闲
jdbc.maxIdle=20
#定义最小空闲
jdbc.minIdle=5
#定义最大连接数
jdbc.maxActive=200
jdbc.removeAbandonedTimeout=300
#定义最长等待时间
jdbc.maxWait=60000
jdbc.maxOpenPreparedStatements=10
log4j.xml内容:
<?xml version='1.0' encoding='UTF-8' ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="CONSOLE" class="org.apache.log4j.ConsoleAppender">
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d [%7r] %6p - %30.30c - %m \n"/>
</layout>
</appender>
<appender name="FILE-DEBUG" class="org.apache.log4j.FileAppender">
<param name="Append" value="false"/>
<param name="file" value="mycatDemo-debug.log"/>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d [%7r] %6p - %30.30c - %m \n"/>
</layout>
</appender>
<appender name="FILE" class="org.apache.log4j.DailyRollingFileAppender">
<param name="DatePattern" value="'.'yyyy-MM-dd"/>
<param name="Append" value="true"/>
<param name="file" value="mycatDemo.log"/>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d [%7r] %6p - %30.30c - %m \n"/>
</layout>
<filter class="org.apache.log4j.varia.LevelRangeFilter">
<param name="LevelMin" value="DEBUG"/>
</filter>
</appender>
<!--<appender name="FILE-ZOOKEEPER" class="org.apache.log4j.DailyRollingFileAppender">
<param name="DatePattern" value="'.'yyyy-MM-dd"/>
<param name="file" value="${las-im-scheduler.log.path}/las-report-zookeeper.log"/>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d [%7r] %6p - %30.30c - %m \n"/>
</layout>
</appender>
<category name="org.apache.zookeeper" additivity="false">
<priority value="debug"/>
<appender-ref ref="FILE-ZOOKEEPER"/>
</category>
-->
<category name="org.cometd" additivity="true">
<priority value="ERROR"/>
<appender-ref ref="FILE"/>
</category>
<category name="org.springframework.orm.ibatis3" additivity="true">
<priority value="INFO"/>
<appender-ref ref="CONSOLE"/>
</category>
<category name="org.apache.ibatis" additivity="true">
<priority value="ERROR"/>
<appender-ref ref="CONSOLE"/>
</category>
<category name="java.sql" additivity="true">
<priority value="ERROR"/>
<appender-ref ref="CONSOLE"/>
</category>
<category name="com.mycat" additivity="true">
<priority value="debug"/>
<appender-ref ref="FILE"/>
</category>
<root>
<priority value="ERROR"/>
<appender-ref ref="CONSOLE"/>
<appender-ref ref="FILE-DEBUG"/>
</root>
</log4j:configuration>
sqlmap文件下的TestMapper.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.mycat.test.model.Test">
<resultMap type="com.mycat.test.model.Test" id="testResultMap">
<result property="id" column="id_"/>
<result property="name" column="name_"/>
<result property="userId" column="user_id"/>
</resultMap>
<select id="selectAll" resultMap="testResultMap">
select * from sam_test
</select>
<select id="selectSome" parameterType="java.util.Map" resultMap="testResultMap">
select * from sam_test limit #{offset},#{limit}
</select>
<select id="getObject" resultMap="testResultMap" parameterType="java.lang.Object">
select * from sam_test where id_ = #{id}
</select>
<insert id="insert" keyProperty="id_" parameterType="com.mycat.test.model.Test">
INSERT INTO sam_test(
name_,
user_id
)
VALUES(
#{name,jdbcType=VARCHAR},
#{userId,jdbcType=BIGINT}
)
<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
select last_insert_id() as id
</selectKey>
</insert>
<update id="update" parameterType="com.mycat.test.model.Test">
update sam_test
<set>
<if test="name != null and name != ''">
name_ = #{name},
</if>
<if test="userId != null">
user_id = #{userId},
</if>
</set>
where id_=#{id}
</update>
<!-- #{}中的参数名与方法中的参数的复杂数据类型的属性名一致 -->
<delete id="delete" parameterType="java.lang.Integer">
delete from sam_test where id_ = #{id}
</delete>
<select id="findOneByMap" resultMap="testResultMap">
select * from sam_test where 1=1 AND
<foreach item="param" index="key" collection="list" open="" separator="AND" close="">
${param.name} = #{param.value}
</foreach>
limit 0, 1
</select>
<select id="findOneByObject" parameterType="com.mycat.test.model.Test" resultMap="testResultMap">
select * from sam_test
<where>
<if test="name != null">
and name_=#{name}
</if>
<if test="userId != null">
and user_id=#{userId}
</if>
</where>
limit 0, 1
</select>
<select id="findByMap" resultMap="testResultMap">
select * from sam_test where 1=1 AND
<foreach item="param" index="key" collection="list" open="" separator="AND" close="">
${param.name} = #{param.value}
</foreach>
</select>
<select id="findByObject" parameterType="com.mycat.test.model.Test" resultMap="testResultMap">
select * from sam_test
<where>
<if test="name != null">
and name_=#{name}
</if>
<if test="userId != null">
and user_id=#{userId}
</if>
</where>
</select>
<select id="findByIn" resultMap="testResultMap">
select * from sam_test where 1=1 AND
<foreach item="param" index="key" collection="list" open="" separator="AND" close="">
${param.name} IN (#{param.value})
</foreach>
</select>
<select id="findByLike" resultMap="testResultMap">
select * from sam_test where 1=1 AND
<foreach item="param" index="key" collection="list" open="" separator="AND" close="">
${param.name} LIKE #{param.value}
</foreach>
</select>
<select id="getTotalCount" resultType="int">
select count(1) from sam_test
</select>
<select id="getCount" parameterType="java.util.Map" resultType="int">
select count(1) from sam_test where 1=1
<if test="whereClause != null">
${whereClause}
</if>
</select>
<select id="query" parameterType="java.util.Map" resultMap="testResultMap">
select ${fieldsClause} from sam_test where 1=1
<if test="whereClause != null">
${whereClause}
</if>
<if test="orderClause != null">
${orderClause}
</if>
limit ${limit} offset ${offset}
</select>
</mapper>
spring-config-db.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: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/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 配置Mybatis会话工厂 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 注入DataSource -->
<!-- 数据源 -->
<property name="dataSource" ref="druidDataSource" />
<!-- 需要加载的mapper.xml,该bean被创建后,会自动加载这些文件。 -->
<property name="mapperLocations">
<list>
<!-- 自动匹配Mapper映射文件 -->
<value>classpath*:sqlmap/*Mapper.xml</value>
</list>
</property>
</bean>
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg ref="sqlSessionFactory" />
</bean>
<!-- Druid集连接池,首先spring配置DataSource -->
<bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<!-- 基本属性 url、user、password -->
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!--initialSize: 初始化连接-->
<property name="initialSize" value="${jdbc.initialSize}"/>
<!--minIdle: 最小空闲连接-->
<property name="minIdle" value="${jdbc.minIdle}"/>
<!--maxActive: 最大连接数量-->
<property name="maxActive" value="${jdbc.maxActive}"/>
<!--removeAbandoned: 对泄漏的连接,是否自动回收超时连接-->
<property name="removeAbandoned" value="true"/>
<!--removeAbandonedTimeout: 超时时间(以秒数为单位)-->
<property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}"/>
<!--maxWait: 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒-->
<property name="maxWait" value="${jdbc.maxWait}"/>
<property name="defaultAutoCommit">
<value>false</value>
</property>
<property name="validationQuery" value="select 1"/>
<!-- 打开PSCache,并且指定每个连接上PSCache的大小 -->
<!-- 如果用Oracle,则把poolPreparedStatements配置为true,mysql可以配置为false。分库分表较多的数据库,建议配置为false -->
<property name="poolPreparedStatements" value="true" />
<property name="maxOpenPreparedStatements" value="${jdbc.maxOpenPreparedStatements}" />
<!-- 配置监控统计拦截的filters,去掉后监控界面sql无法统计 -->
<!--<property name="filters" value="stat"/>-->
<!-- 慢日志查询 缺省为3秒 修改为10秒 10000 -->
<!-- <property name="connectionProperties" value="druid.stat.slowSqlMillis=5000" /> -->
</bean>
<!-- druid 监控 spring -->
<!-- <bean id="druid-stat-interceptor" class="com.alibaba.druid.support.spring.stat.DruidStatInterceptor"/> -->
<!-- 按类型拦截配置 -->
<!-- <bean id="druid-type-proxyCreator" class="com.alibaba.druid.support.spring.stat.BeanTypeAutoProxyCreator"> -->
<!-- 所有ABCInterface的派生类被拦截监控 -->
<!-- <property name="targetBeanType" value="xxxx.ABCInterface" />
<property name="interceptorNames">
<list>
<value>druid-stat-interceptor</value>
</list>
</property>
</bean> -->
<!-- 按照BeanId来拦截配置 -->
<!-- <bean class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
<property name="proxyTargetClass" value="true" />
<property name="beanNames">
<list> -->
<!-- 这里配置需要拦截的bean id列表 -->
<!-- <value>xxx-dao</value>
<value>xxx-service</value>
</list>
</property>
<property name="interceptorNames">
<list>
<value>druid-stat-interceptor</value>
</list>
</property>
</bean> -->
<!-- 方法名正则匹配拦截配置 -->
<!-- <bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut" scope="prototype"> -->
<!-- <property name="patterns"> -->
<!-- <list> -->
<!-- <value>com.xinnet.*.service.*</value> -->
<!-- <value>com.mycompany.service.*</value> -->
<!-- <value>com.mycompany.dao.*</value> -->
<!-- </list> -->
<!-- </property> -->
<!-- </bean> -->
<!-- <aop:config> -->
<!-- <aop:advisor advice-ref="druid-stat-interceptor" pointcut-ref="druid-stat-pointcut" /> -->
<!-- </aop:config> -->
<!-- 配置事物管理器 -->
<!-- 事务管理器 对mybatis操作数据库事务控制,spring使用jdbc的事务控制类 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!-- 数据源 dataSource在上面配置了 -->
<property name="dataSource" ref="druidDataSource" />
</bean>
<!-- 配置声明式事务 -->
<tx:annotation-driven transaction-manager="transactionManager"/>
</beans>
spring-config-mvc.xml配置:
<?xml version="1.0" encoding="UTF-8"?>
<!--suppress ALL -->
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:util="http://www.springframework.org/schema/util"
xmlns:context="http://www.springframework.org/schema/context"
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/util
http://www.springframework.org/schema/util/spring-util-3.1.xsd">
<!-- spring 自动扫描组件 -->
<!-- 自动扫描 controller 包下的所有类 -->
<!-- mvc controller -->
<context:component-scan base-package="com.mycat">
<context:include-filter type="regex" expression=".*.controller.*"/>
<context:include-filter type="regex" expression=".*.service.*"/>
<context:include-filter type="regex" expression=".*.dao.*"/>
</context:component-scan>
<!-- 开启注解的功能 -->
<!-- 启动支持 mvc 的注解,比如 @Controller, @RequestMapping 等-->
<mvc:annotation-driven/>
<!--<bean id="conversionService"-->
<!--class="org.springframework.format.support.FormattingConversionServiceFactoryBean">-->
<!--<property name="formatterRegistrars">-->
<!--<bean class="com.jd.common.springmvc.converter.DefaultFormatterRegistrar"/>-->
<!--</property>-->
<!--</bean>-->
<!-- 解决静态资源被拦截的问题 -->
<mvc:default-servlet-handler/>
<!-- static resources -->
<!--<mvc:resources location="/static/" mapping="/static/**" cache-period="864000"/>-->
<!--<mvc:resources location="/js/" mapping="/js/**" cache-period="864000"/>-->
<!-- 配置视图解析器,使得在JSP中能够使用完整的JSTL功能 -->
<!-- 设置视图解析工具 -->
<!-- template view -->
<bean id="freemarkerConfig"
class="org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer">
<property name="templateLoaderPath" value="/WEB-INF/views/"/>
<property name="freemarkerSettings">
<props>
<prop key="template_update_delay">0</prop>
<prop key="default_encoding">UTF-8</prop>
<prop key="number_format">0.##########</prop>
<prop key="datetime_format">yyyy-MM-dd HH:mm:ss</prop>
<prop key="classic_compatible">true</prop>
<prop key="template_exception_handler">ignore</prop>
</props>
</property>
</bean>
<bean id="freeMarkerViewResolver"
class="org.springframework.web.servlet.view.freemarker.FreeMarkerViewResolver">
<!--<property name="layoutUrl" value="layout/default.ftl"/>-->
<property name="cache" value="false"/>
<property name="suffix" value=".ftl"/>
<property name="requestContextAttribute" value="request"/>
<property name="exposeSpringMacroHelpers" value="true"/>
<property name="exposeRequestAttributes" value="true"/>
<property name="exposeSessionAttributes" value="true"/>
<property name="contentType" value="text/html;charset=UTF-8"/>
</bean>
<bean class="org.springframework.web.servlet.view.ContentNegotiatingViewResolver">
<property name="defaultContentType" value="text/html"/>
<!-- not by accept header -->
<property name="ignoreAcceptHeader" value="true"/>
<!-- by extension -->
<property name="mediaTypes">
<map>
<entry key="html" value="text/html"/>
<entry key="xml" value="application/xml"/>
<entry key="json" value="application/json"/>
</map>
</property>
<property name="viewResolvers">
<list>
<ref bean="freeMarkerViewResolver"/>
</list>
</property>
<property name="defaultViews">
<list>
<!-- for application/json -->
<bean class="org.springframework.web.servlet.view.json.MappingJacksonJsonView"/>
</list>
</property>
</bean>
<!-- locale related -->
<!--<bean id="localeResolver" class="org.springframework.web.servlet.i18n.CookieLocaleResolver">-->
<!--<property name="cookieName" value="_clientlocale"/>-->
<!--<property name="defaultLocale" value="zh_CN"/>-->
<!--<property name="cookieMaxAge" value="2147483647"/>-->
<!--</bean>-->
<bean class="org.springframework.web.servlet.mvc.annotation.DefaultAnnotationHandlerMapping"/>
<bean class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter">
<property name="messageConverters">
<util:list id="beanList">
<ref bean="mappingJacksonHttpMessageConverter"/>
</util:list>
</property>
</bean>
<bean id="mappingJacksonHttpMessageConverter" class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter">
<property name="supportedMediaTypes">
<list>
<value>text/html;charset=UTF-8</value>
</list>
</property>
</bean>
</beans>
spring-config.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:context="http://www.springframework.org/schema/context"
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.xsd">
<!-- spring容器中最多只能定义一个context:property-placeholder spring中 context:property-placeholder
导入多个独立的 .properties配置文件 Spring容器仅允许最多定义一个PropertyPlaceholderConfigurer(或<context:property-placeholder/>),其余的会被Spring忽略掉
通配符解决 -->
<!-- 引入项目配置文件 -->
<!-- 加载db.properties文件中的内容,db.properties文件中的key要有一定的特殊规则 -->
<context:property-placeholder location="classpath*:mycat.properties" />
<import resource="spring-config-mvc.xml" />
<!--文件中主要负责配置:加载db.properties、配置数据源、配置SqlSessionFactoryBean、Mapper扫描器-->
<import resource="spring-config-db.xml" />
</beans>