关闭

SpringMVC,Mybatis,FreeMarker连接mycat示例(一)

标签: mysqlfreemarkerspring mvcmybatismycat
16839人阅读 评论(0) 收藏 举报
分类:

人生最重要的一点是,永远不要迷失自己。

项目结构如图:

这里写图片描述
这里写图片描述

首先是各种配置文件,属性文件

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>

暂时先到这里

1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1075307次
    • 积分:16029
    • 等级:
    • 排名:第755名
    • 原创:313篇
    • 转载:770篇
    • 译文:120篇
    • 评论:274条
    博客专栏
    文章分类
    打赏
    如果你觉得我的文章对您有用,请随意打赏。 微信 支付宝