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


    	<dl class="box_p">
    		<dd class="pRight"><a href="/">首页</a><span> &gt; </span><a href="https://www.2cto.com/kf/">程序开发</a> &gt; <a href="https://www.2cto.com/kf/ware/">软件开发</a> &gt; <a href="https://www.2cto.com/kf/ware/Java/news/">Java</a> &gt;  正文</dd>
    		
    		</dl>
    	<dl class="box_t"><dd>SpringMVC,Mybatis,FreeMarker连接mycat示例(一)</dd></dl>
    																									
    		
        <!-- 2cto_左一 -->
         <div class="cont_ibox">
         	<script type="text/javascript">cto_A_D("2cto_9");</script>
         </div>
    	<div class="box_body" id="fontzoom">
       <div id="Article">
        <h1 id="项目结构如图">项目结构如图:</h1>
    

    这里写图片描述

    这里写图片描述

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

    mycat.properties内容:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    #数据库连接配置
    #以下是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内容:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    <!--?xml version='1.0' encoding='UTF-8' ?-->
     
     
    <log4j:configuration xmlns:log4j="https://jakarta.apache.org/log4j/">
     
         
            <layout class="org.apache.log4j.PatternLayout"><param name="ConversionPattern" value="%d [%7r] %6p - %30.30c - %m ">
            </layout>
        </appender>
     
        <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 ">
            </layout>
        </appender>
     
        <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 ">
            </layout>
            <filter class="org.apache.log4j.varia.LevelRangeFilter"><param name="LevelMin" value="DEBUG">
            </filter>
        </appender>
     
        <!--
                <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 "/>
                </layout>
            </appender>
     
            <category name="org.apache.zookeeper" additivity="false">
                <priority value="debug"/>
                 
            </category>
        -->
     
        <category additivity="true" name="org.cometd">
            <priority value="ERROR">
             
        </appender-ref></priority></category>
     
        <category additivity="true" name="org.springframework.orm.ibatis3">
            <priority value="INFO">
             
        </appender-ref></priority></category>
     
        <category additivity="true" name="org.apache.ibatis">
            <priority value="ERROR">
             
        </appender-ref></priority></category>
     
        <category additivity="true" name="java.sql">
            <priority value="ERROR">
             
        </appender-ref></priority></category>
     
        <category additivity="true" name="com.mycat">
            <priority value="debug">
             
        </appender-ref></priority></category>
     
        <root>
            <priority value="ERROR">
             
             
        </appender-ref></appender-ref></priority></root>
     
    </log4j:configuration>

    sqlmap文件下的TestMapper.xml实体映射文件

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    <!--?xml version="1.0" encoding="UTF-8" ?-->
     
     
    <mapper namespace="com.mycat.test.model.Test">
     
        <resultmap id="testResultMap" type="com.mycat.test.model.Test">
            <result column="id_" property="id">
            <result column="name_" property="name">
            <result column="user_id" property="userId">
        </result></result></result></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" parametertype="java.lang.Object" resultmap="testResultMap">
            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 keyproperty="id" order="AFTER" resulttype="java.lang.Integer">
                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
             
                ${param.name} = #{param.value}
             
            limit 0, 1
        </select>
     
        <select id="findOneByObject" parametertype="com.mycat.test.model.Test" resultmap="testResultMap">
            select * from sam_test
             
                     
                        and name_=#{name}
                     
                     
                        and user_id=#{userId}
                     
             
            limit 0, 1
        </select>
     
        <select id="findByMap" resultmap="testResultMap">
            select * from sam_test where 1=1 AND
             
                ${param.name} = #{param.value}
             
        </select>
     
        <select id="findByObject" parametertype="com.mycat.test.model.Test" resultmap="testResultMap">
            select * from sam_test
             
                 
                    and name_=#{name}
                 
                 
                    and user_id=#{userId}
                 
             
        </select>
     
        <select id="findByIn" resultmap="testResultMap">
            select * from sam_test where 1=1 AND
             
                ${param.name} IN (#{param.value})
             
        </select>
     
        <select id="findByLike" resultmap="testResultMap">
            select * from sam_test where 1=1 AND
             
                ${param.name} LIKE #{param.value}
             
        </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
             
            ${whereClause}
             
        </select>
     
        <select id="query" parametertype="java.util.Map" resultmap="testResultMap">
            select ${fieldsClause} from sam_test where 1=1
             
            ${whereClause}
             
             
            ${orderClause}
             
            limit ${limit} offset ${offset}
        </select>
    </mapper>

    spring-config-db.xml配置:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    <!--?xml version="1.0" encoding="UTF-8"?-->
           https://www.springframework.org/schema/beans/spring-beans-3.1.xsd
           https://www.springframework.org/schema/tx
           https://www.springframework.org/schema/tx/spring-tx.xsd
           https://www.springframework.org/schema/aop
           https://www.springframework.org/schema/aop/spring-aop.xsd">
     
        <!-- 配置Mybatis会话工厂 -->
        <bean class="org.mybatis.spring.SqlSessionFactoryBean" id="sqlSessionFactory">
     
            <!-- 注入DataSource -->
            <!-- 数据源 -->
            <property name="dataSource" ref="druidDataSource">
     
            <!-- 需要加载的mapper.xml,该bean被创建后,会自动加载这些文件。 -->
            <property name="mapperLocations">
                <list>
                      <!-- 自动匹配Mapper映射文件  --> 
                    <value>classpath*:sqlmap/*Mapper.xml</value>
                </list>
            </property>
        </property></bean>
     
        <bean class="org.mybatis.spring.SqlSessionTemplate" id="sqlSession">
            <constructor-arg ref="sqlSessionFactory">
        </constructor-arg></bean>
     
        <!-- Druid集连接池,首先spring配置DataSource -->
        <bean class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close" id="druidDataSource" init-method="init">
            <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秒  修改为1010000 --> 
            <!-- <property name="connectionProperties" value="druid.stat.slowSqlMillis=5000" />   -->
     
        </property></property></property></property></property></property></property></property></property></property></property></property></property></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>   -->
     
     
         <!-- 配置事物管理器 -->
        <!-- 事务管理器 对mybatis操作数据库事务控制,spring使用jdbc的事务控制类 -->
        <bean class="org.springframework.jdbc.datasource.DataSourceTransactionManager" id="transactionManager">
             <!-- 数据源 dataSource在上面配置了 -->
            <property name="dataSource" ref="druidDataSource">
        </property></bean>
        <!-- 配置声明式事务 -->
        <tx:annotation-driven transaction-manager="transactionManager">
     
    </tx:annotation-driven></beans>
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    <!--?xml version="1.0" encoding="UTF-8"?-->
    <!--suppress ALL -->
                https://www.springframework.org/schema/beans
                https://www.springframework.org/schema/beans/spring-beans-3.1.xsd
                https://www.springframework.org/schema/context
                https://www.springframework.org/schema/context/spring-context-3.1.xsd
                https://www.springframework.org/schema/mvc
                https://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd
                https://www.springframework.org/schema/util
                https://www.springframework.org/schema/util/spring-util-3.1.xsd">
     
         <!-- spring 自动扫描组件 -->
         <!-- 自动扫描 controller 包下的所有类 --> 
         <!-- mvc controller -->
        <context:component-scan base-package="com.mycat">
            <context:include-filter expression=".*.controller.*" type="regex">
            <context:include-filter expression=".*.service.*" type="regex">
            <context:include-filter expression=".*.dao.*" type="regex">
        </context:include-filter></context:include-filter></context:include-filter></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 class="org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer" id="freemarkerConfig">
            <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>
        </property></bean>
        <bean class="org.springframework.web.servlet.view.freemarker.FreeMarkerViewResolver" id="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">
        </property></property></property></property></property></property></property></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">
                </entry></entry></entry></map>
            </property>
            <property name="viewResolvers">
                <list>
                    <ref bean="freeMarkerViewResolver">
                </ref></list>
            </property>
            <property name="defaultViews">
                <list>
                    <!-- for application/json -->
                    <bean class="org.springframework.web.servlet.view.json.MappingJacksonJsonView">
                </bean></list>
            </property>
        </property></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">
                </ref></util:list>
            </property>
        </bean>
        <bean class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter" id="mappingJacksonHttpMessageConverter">
            <property name="supportedMediaTypes">
                <list>
                    <value>text/html;charset=UTF-8</value>
                </list>
            </property>
        </bean>
     
    </bean></mvc:default-servlet-handler></mvc:annotation-driven></beans>

    spring-config.xml配置:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    <!--?xml version="1.0" encoding="UTF-8"?-->
           https://www.springframework.org/schema/beans/spring-beans-3.1.xsd
           https://www.springframework.org/schema/context https://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">
     
    </import></import></context:property-placeholder></beans>

    暂时先到这里

    </div>
  • 相关阅读:
    Mac 下VirtualBox与Linux共享文件
    安装LAMP 环境Linux 16.04 + Apache2 + Php7.0
    Python调用OpenCV读写视频
    [tensorflow] FutureWarning: Passing (type, 1) or '1type' as a synonym of type is deprecated
    VS Qt国际化操作
    OpenCV--基于直线检测的文本图像倾斜校正
    Python调用OpenCV形态学
    Keras设置后端数据维度顺序
    Python调用OpenCV代码优化
    Python调用OpenCV滤波
  • 原文地址:https://www.cnblogs.com/jpfss/p/8136607.html
  • Copyright © 2020-2023  润新知