mybatis XML 映射器

XML 映射器
MyBatis 的真正强大在于它的语句映射,这是它的魔力所在。由于它的异常强大,映射器的 XML 文件就显得相对简单。如果拿它跟具有相同功能的 JDBC 代码进行对比,你会立即发现省掉了将近 95% 的代码。MyBatis 致力于减少使用成本,让用户能更专注于 SQL 代码。

SQL 映射文件只有很少的几个顶级元素(按照应被定义的顺序列出):

cache – 该命名空间的缓存配置。
cache-ref – 引用其它命名空间的缓存配置。
resultMap – 描述如何从数据库结果集中加载对象,是最复杂也是最强大的元素。
parameterMap – 老式风格的参数映射。此元素已被废弃,并可能在将来被移除!请使用行内参数映射。文档中不会介绍此元素。
sql – 可被其它语句引用的可重用语句块。
insert – 映射插入语句。
update – 映射更新语句。
delete – 映射删除语句。
select – 映射查询语句。

Spring boot模块化项目Mybatis无法找到Mapper解决办法

最近设计系统架构,需要把项目功能子模块化,框架使用Spring Cloud、Spring Boot,在子模块化的时候遇到DAO层分离的问题,比较复杂难解,另外一个就是maven打包的问题,但是相对好解决一点。

首先创建一个子模块(DAO),配置好并且可以启动,然后再创建一个子模块(API),API再maven中继承了DAO模块,配置application.yml文件之后会出现错误:

 Invalid bound statement not found xxxx.xxMapper.selectByxxx()

原因有两个:配置的问题、xml放置位置的问题。

  • 配置
* POM文件加入:
    <dependency>
        <groupId>com.test</groupId>
        <artifactId>dao</artifactId>
        <version>1.0</version>
        <scope>compile</scope>
    </dependency>

* build栏目加入:
    <resource>
        <directory>src/main/resources</directory>
        <includes>
            <include>**/*.*</include>
        </includes>
        <filtering>false</filtering>
    </resource>
    
* 使用注解扫描mapper包
@MapperScan(value = "com.test.mapper.repository.mapper")

* 配置项(application.yml):
    spring:
      application:
        name: api-server
      profiles:
        active: test
      thymeleaf:
        cache: false
        prefix:
          classpath: /templates
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: org.apache.commons.dbcp2.BasicDataSource
        url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC&characterEncoding=utf-8
        username: root
        password: root
        dbcp2:
          initial-size: 5
          max-idle: 100
          min-idle: 5
          max-wait-millis: 3000
          test-on-borrow: true
          test-on-return: false
          test-while-idle: true
          validation-query: SELECT 1
          time-between-eviction-runs-millis: 30000
          soft-min-evictable-idle-time-millis: 1800000
          num-tests-per-eviction-run: 3
          remove-abandoned-timeout: 180
          pool-prepared-statements: true
          max-open-prepared-statements: 15
          
    mybatis:
      type-aliases-package: com.test.mapper.repository
      type-handlers-package: com.test.mapper.repository.mapper
      configuration:
        map-underscore-to-camel-case: true
        default-fetch-size: 100
        default-statement-timeout: 30
      mapper-locations: classpath*:mapper/*.xml

* 最后测试
  • xml位置

位置的问题就是xml需要放置在src/main/resources目录下,我之前是一直放在src/main/java/com/test/repository/src/main/resources目录下,所以一直都是失败的。

配置还需要注意的是:

mapper-locations: classpath*:mapper/*.xml

 

Spring+MyBatis实现数据库读写分离4种方案

通过MyBatis配置文件创建读写分离两个DataSource,每个SqlSessionFactoryBean对象的mapperLocations属性制定两个读写数据源的配置文件。将所有读的操作配置在读文件中,所有写的操作配置在写文件中。

优点:实现简单
缺点:维护麻烦,需要对原有的xml文件进行重新修改,不支持多读,不易扩展
实现方式

myBatis数据库自动断开的解决办法

使用Spring+SpringMVC+myBatis+Mysql开发一个应用服务端,由于在测试阶段没有上线,所以没有人会链接服务器。

放假回来才发现数据库被关闭了,而且要重启tomcat才有效,都不知道myBatis为什么会没有自动重连数据库的功能。

异常信息:

23-Sep-2017 09:43:39.880 SEVERE [http-nio-8081-exec-93] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [dispatcher] in context with path [/project] threw exception [Request processing failed; nested exception is org.springframework.dao.DataAccessResourceFailureException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
### The error may exist in file [/www/apache-tomcat-8081/webapps/project/WEB-INF/classes/com/test/system/mapper/xml/cateMapper.xml]
### The error may involve com.test.system.mapper.SdGoodsCateMapper.getCateByGroup
### The error occurred while executing a query
### SQL: SELECT b.* FROM …..;
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
; SQL []; No operations allowed after connection closed.; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.] with root cause
java.net.SocketException: Broken pipe (Write failed)
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3728)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2509)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2448)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
at com.mysql.jdbc.DatabaseMetaData.getUserName(DatabaseMetaData.java:6274)
at org.apache.commons.dbcp.DelegatingConnection.toString(DelegatingConnection.java:123)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.toString(PoolingDataSource.java:355)
at java.lang.String.valueOf(String.java:2994)
at java.lang.StringBuilder.append(StringBuilder.java:131)
at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:87)
at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)
at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:315)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:75)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:61)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:303)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:154)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:102)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:82)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:120)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:113)
at sun.reflect.GeneratedMethodAccessor2495.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:408)
at com.sun.proxy.$Proxy408.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:206)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:122)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:64)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
at com.sun.proxy.$Proxy434.getCateByGroup(Unknown Source)
at com.test.system.service.HomeServiceImpl.getHomeAlbumByHot(HomeServiceImpl.java:95)
at com.test.app.controller.HomeController.getAlbumByHot(HomeController.java:60)
at sun.reflect.GeneratedMethodAccessor2778.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:222)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:969)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:860)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1533)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1489)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)

原因分析:

查看了Mysql的文档,以及Connector/J的文档以及在线说明发现,出现这种异常的原因是:

Mysql服务器默认的“wait_timeout”是8小时,也就是说一个connection空闲超过8个小时,Mysql将自动断开该connection。这就是问题的所在,在C3P0 pools中的connections如果空闲超过8小时,Mysql将其断开,而C3P0并不知道该connection已经失效,如果这时有Client请求connection,C3P0将该失效的Connection提供给Client,将会造成上面的异常。

解决方案:

加上如下配置:

//SQL查询,用来验证从连接池取出的连接,在将连接返回给调用者之前.如果指定,则查询必须是一个SQL SELECT并且必须返回至少一行记录

validationQuery = select 1

//指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除.

testWhileIdle = true

//在空闲连接回收器线程运行期间休眠的时间值,以毫秒为单位.如果设置为非正数,则不运行空闲连接回收器线程

timeBetweenEvictionRunsMillis = 3600000

//1000 * 60 * 30  连接在池中保持空闲而不被空闲连接回收器线程,(如果有)回收的最小时间值,单位毫秒

minEvictableIdleTimeMillis = 18000000

//指明是否在从池中取出连接前进行检验,如果检验失败,则从池中去除连接并尝试取出另一个.

testOnBorrow = true

 

validationQuery参考:

validationQuery是用来验证数据库连接的查询语句,这个查询语句必须是至少返回一条数据的SELECT语句。每种数据库都有各自的验证语句,下表中收集了几种常见数据库的validationQuery。

DataBase validationQuery
hsqldb select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
Oracle select 1 from dual
DB2 select 1 from sysibm.sysdummy1
MySql select 1
Microsoft SqlServer select1
postgresql select version()
ingres select 1
derby values 1
H2 select 1

 

BasicDataSource Configuration Parameters:

http://commons.apache.org/proper/commons-dbcp/configuration.html