<dl class="box_p">
<dd class="pRight"><a href="/">首页</a><span> > </span><a href="https://www.2cto.com/kf/">程序开发</a> > <a href="https://www.2cto.com/kf/ware/">软件开发</a> > <a href="https://www.2cto.com/kf/ware/Java/news/">Java</a> > 正文</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' ?--> <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" ?--> <beans xmlns= "https://www.springframework.org/schema/beans" xmlns:aop= "https://www.springframework.org/schema/aop" xmlns:tx= "https://www.springframework.org/schema/tx" xmlns:xsi= "https://www.w3.org/2001/XMLSchema-instance" xsi:schemalocation="https://www.springframework.org/schema/beans 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 秒 修改为 10 秒 10000 --> <!-- <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> |
vcxml配置">spring-config-mvc.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 | <!--?xml version= "1.0" encoding= "UTF-8" ?--> <!--suppress ALL --> <beans xmlns= "https://www.springframework.org/schema/beans" xmlns:context= "https://www.springframework.org/schema/context" xmlns:mvc= "https://www.springframework.org/schema/mvc" xmlns:util= "https://www.springframework.org/schema/util" xmlns:xsi= "https://www.w3.org/2001/XMLSchema-instance" xsi:schemalocation=" 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" ?--> <beans xmlns= "https://www.springframework.org/schema/beans" xmlns:context= "https://www.springframework.org/schema/context" xmlns:xsi= "https://www.w3.org/2001/XMLSchema-instance" xsi:schemalocation="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.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> |