当前位置:Gxlcms > 数据库问题 > springboot通过xml集成mybatis实现mysql数据库操作【探坑记】

springboot通过xml集成mybatis实现mysql数据库操作【探坑记】

时间:2021-07-01 10:21:17 帮助过:16人阅读

这里,通过springboot的@configuration的注解指令,以及@ImportResource注解,很方便的实现mybatis的xml配置文件加载,快速实现mybatis相关信息的配置(含Database)。下面,举一个简单的例子,基于mysql实现mybatis的集成。

mybatis和mysql的集成配置(基于durid组件)的xml文件如下(spring-dao.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:mybatis="http://mybatis.org/schema/mybatis-spring"  
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"  
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
       http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd  
       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.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">
        
    <!-- 数据库的配置信息jdbc.properties在applicationContext.xml当中配置 -->
    <!--数据源加密操作-->
    <bean id="myPasswordCallback" class="com.tk.logc.core.mysql.DatabasePasswordCallback" lazy-init="true"/>
    <!--数据库连接-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> 
        <property name="url" value="${dataSource.mysql.master-url}" />
        <property name="username" value="${dataSource.mysql.master-username}"/>
        <property name="password" value="${dataSource.mysql.master-password}"/>
        <!-- 配置初始化大小、最小、最大 -->
        <property name="initialSize"><value>${dataSource.mysql.initialSize}</value></property>
        <property name="maxActive"><value>${dataSource.mysql.maxActive}</value></property>
        <property name="minIdle"><value>${dataSource.mysql.minIdle}</value></property>
        <!-- 配置获取连接等待超时的时间 -->
        <property name="maxWait"><value>${dataSource.mysql.maxWait}</value></property>
        <!-- 配置监控统计拦截的filters -->
        <property name="filters"><value>stat</value></property>
        <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis"><value>${dataSource.mysql.timeBetweenEvictionRunsMillis}</value></property>
        <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis"><value>${dataSource.mysql.minEvictableIdleTimeMillis}</value></property>
        <!-- 配置连接中的密码信息 -->
        <property name="connectionProperties" value="password=${dataSource.mysql.master-password}"/>
        <!-- 调用密码回调,进行解密 -->
        <property name="passwordCallback" ref="myPasswordCallback"/>
        <!--
        <property name="validationQuery"><value>SELECT x</value></property>
        <property name="testWhileIdle"><value>true</value></property>
        <property name="testOnBorrow"><value>false</value></property>
        <property name="testOnReturn"><value>false</value></property>
        <property name="poolPreparedStatements"><value>true</value></property>
        <property name="maxOpenPreparedStatements"><value>20</value></property>
         -->
     </bean>
     
    <!-- ========================================针对myBatis的配置项============================== -->
    <!-- 配置sqlSessionFactory -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!-- 扫描com.jersey.dao.mapper这个包以及它的子包下的所有映射接口类 -->
        <property name="basePackage" value="com.tk.logc.consumer" />
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
    </bean>
    <!-- 配置扫描器 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--  实例化sqlSessionFactory时需要使用上述配置好的数据源以及SQL映射文件 -->
        <property name="dataSource" ref="dataSource" />
        <property name="mapperLocations" value="classpath:mybatis/consumer/*/mapper/*.xml"/>
        <!--
        <property name="mapperLocations" value="classpath:com/tk/logc/consumer/*/dao/mapper/*.xml" />
        -->
</bean>

<!-- ========================================分隔线========================================= -->
    <!-- 配置Spring的事务管理器 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!-- 注解方式配置事物 -->
    <!-- <tx:annotation-driven transaction-manager="transactionManager" /> -->

    <!-- 拦截器方式配置事物 -->
    <tx:advice id="transactionAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="add*" propagation="REQUIRED" />
            <tx:method name="append*" propagation="REQUIRED" />
            <tx:method name="insert*" propagation="REQUIRED" />
            <tx:method name="save*" propagation="REQUIRED" />
            <tx:method name="update*" propagation="REQUIRED" />
            <tx:method name="modify*" propagation="REQUIRED" />
            <tx:method name="edit*" propagation="REQUIRED" />
            <tx:method name="delete*" propagation="REQUIRED" />
            <tx:method name="remove*" propagation="REQUIRED" />
            <tx:method name="repair" propagation="REQUIRED" />
            <tx:method name="delAndRepair" propagation="REQUIRED" />

            <tx:method name="get*" propagation="SUPPORTS" />
            <tx:method name="find*" propagation="SUPPORTS" />
            <tx:method name="load*" propagation="SUPPORTS" />
            <tx:method name="search*" propagation="SUPPORTS" />
            <tx:method name="datagrid*" propagation="SUPPORTS" />

            <tx:method name="*" propagation="SUPPORTS" />
        </tx:attributes>
    </tx:advice>
    <aop:config>
        <aop:pointcut id="transactionPointcut" expression="execution(* com.tk.logc.consumer.*.service.impl.*.*(..))" />
        <aop:advisor pointcut-ref="transactionPointcut" advice-ref="transactionAdvice" />
    </aop:config>


    <!-- 配置druid监控spring jdbc -->
    <bean id="druid-stat-interceptor" class="com.alibaba.druid.support.spring.stat.DruidStatInterceptor">
    </bean>
    <bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut" scope="prototype">
        <property name="patterns">
            <list>
                <value>com.tk.logc.consumer.*.service.*</value>
            </list>
        </property>
    </bean>
    <aop:config>
        <aop:advisor advice-ref="druid-stat-interceptor" pointcut-ref="druid-stat-pointcut" />
    </aop:config>
</beans>

 

在springboot中,需要一个简单的配置文件实现(不同的配置项,启用不同的基于@Configuration注解的java配置),非常的简单。

@Configuration
@ImportResource(locations = {"classpath:config/spring-dao.xml"})
public class MySqlConfig {
}

 

因为springboot的默认约定大于配置的原则,所以,这个名义上方便了开发者,但是呢,对于不是太熟悉springboot的人来说,可能会是个灾难,因为不知道约定的配置都是些啥,不知道配置文件应该放什么地方,如何去访问。
默认情况下,springboot的资源性文件,放在resources目录下,例如静态资源文件目录是/static/或者/public/。 当然,这些默认的路径是可以修改的。 这里,我们要将spring-dao.xml文件放在resources目录下的一个子目录里,定义为config。目录层级关系如图:

技术分享图片

 

2.验证springboot和mybatis集成的效果

按照上图的结构图放置mapper对应的xml文件,然后配置一个controller,一个简单的form表单,做基本测试。

form表单:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Logc User</title>
</head>
<body>
    <div>
        <form id="createUser"  action="${basePath}/account/create" method="post">
            <label>用户名:</label>
            <input type="text" id="username" name="username">
            <br>
            <label>密码:</label>
            <input type="password" id="password" name="password">
            <br>
            <label>确认密码:</label>
            <input type="password" id="vpassword" name="vpassword">
            <br>
            <label>昵称:</label>
            <input type="text" id="nickname" name="nickname">
            <br>
            <label>图像:</label>
            <input type="text" id="thumb" name="thumb">
            <br>
            <label>邮箱:</label>
            <input type="text" id="email" name="email">
            <button type="submit" id="doCreate">创建</button>
        </form>
    </div>

    <script src="${basePath}/js/common/jquery-2.1.1.min.js?${softVersion!‘1010101‘}" type="text/javascript"></script>
    <!--
    <script type="text/javascript">
        $(function(){
            $("#doCreate").on("click", function(){
                console.log("click do create button");
                $.ajax({
                    url : "${basePath}/account/create",
                    type : "POST",
                    data : {
                        "username" : $("#username").val(),
                        "password" : $("#password").val(),
                        "vpassword" : $("#vpassword").val(),
                        "nickname" : $("#nickname").val(),
                        "thumb" : $("#thumb").val(),
                        "email" : $("#email").val()
                    },
                    async : false,
                    dataType : "json",
                    success : function(data) {
                      alert("submit successfully");
                    }
                });
            });
        })
    </script>
    -->
</body>
</html>

 

controller:

    @RequestMapping(value = "/newUser")
    public String newUser() {
        return "/oam/user";
    }

    @RequestMapping(value = "/create", method = RequestMethod.POST)
    @ResponseBody
    public String create(@RequestParam("username")String username,
                         @RequestParam("password")String password,
                         @RequestParam("vpassword")String vpassword,
                         @RequestParam("nickname")String nickname,
                         @RequestParam("thumb")String thumb,
                         @RequestParam("email")String email) {
        System.out.println("username: " + username);
        System.out.println("password: " + password);
        System.out.println("vpassword: " + vpassword);
        System.out.println("nickname: " + nickname);
        System.out.println("thumb: " + thumb);
        System.out.println("email: " + email);
        LogcUser lu = new LogcUser();
        lu.setEmail(email);
        lu.setNickname(nickname);
        lu.setThumb(thumb);
        lu.setUsername(username);
        String salt = PasswordHelper.genSalt();
        lu.setSalt(salt);
        String realPass = PasswordHelper.genPassword(password, salt);
        lu.setPassword(realPass);
        userService.insert(lu);
        return "OK";
    }

 

按照上面这种配置方式,会出现一个问题

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.tk.logc.consumer.system.dao.LogcUserMapper.insert
    at org.apache.ibatis.binding.MapperMethod$SqlCommand.<init>(MapperMethod.java:230) ~[mybatis-3.4.2.jar:3.4.2]
    at org.apache.ibatis.binding.MapperMethod.<init>(MapperMethod.java:48) ~[mybatis-3.4.2.jar:3.4.2]
    at org.apache.ibatis.binding.MapperProxy.cachedMapperMethod(MapperProxy.java:65) ~[mybatis-3.4.2.jar:3.4.2]
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58) ~[mybatis-3.4.2.jar:3.4.2]
    at com.sun.proxy.$Proxy79.insert(Unknown Source) ~[na:na]
    at com.tk.logc.consumer.system.service.impl.UserService.insert(UserService.java:60) ~[classes/:na]
    at com.tk.logc.consumer.system.service.impl.UserService$$FastClassBySpringCGLIB$$4c20e181.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:747) ~[spring-aop-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at com.alibaba.druid.support.spring.stat.DruidStatInterceptor.invoke(DruidStatInterceptor.java:72) ~[druid-1.0.18.jar:1.0.18]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) ~[spring-tx-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    。。。。

这种问题,其实比较常见,但是呢,这些常见多数是在springboot之外的spring框架下比较好处理。例如网上说的这么几点都是常见的处理上述问题的方法:
一般的原因是Mapper interface和xml文件的定义对应不上,需要检查包名,namespace,函数名称等是否匹配一直,细致的对比,常见错误是名称(全路径)对不上,不一致
但是呢,我这里的问题,表面上看,似乎也是Mapper interface的方法和xml里面的方法对不上。本质上,是springboot限制了mybatis访问src/main/java下面的xml配置文件,xml类型的配置文件被当做资源文件,必须放在src/main/resources下面,mybatis才可以访问

 

调整后,正确的工程文件目录结构如图right.jpg, 另外,spring-dao.xml的配置改动如下:

    <!-- 配置sqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--  实例化sqlSessionFactory时需要使用上述配置好的数据源以及SQL映射文件 -->
        <property name="dataSource" ref="dataSource" />        
        <property name="mapperLocations" value="classpath:mybatis/consumer/*/mapper/*.xml"/>        
        <!--
        <property name="mapperLocations" value="classpath:com/tk/logc/consumer/*/dao/mapper/*.xml" />
        -->
    </bean>

上面的红色部分配置,由<!-- -->注释掉的配置,就是指定mapper的xml文件在src/main/java路径下,是错误的。 调整后,放到src/main/resources下面的mybatis的主目录下,就可以正常工作了。调整后的项目文件结构图如下:

技术分享图片

这个时候,可以将原来处于src/main/java下面的mapper的xml文件可以完全删除了。

 

补充一点(基于javaconfig的方式实现,区别于xml配置模式的代码): 

@Configuration
@MapperScan(basePackages = MySqlConfig.PACKAGE, sqlSessionFactoryRef = "sqlSessionFactory")
public class MySqlConfig {

    static final String PACKAGE = "com.tk.logc.consumer";
    static final String MAPPER_LOCATION = "classpath:mybatis/consumer/*/mapper/*.xml";

    @Value("${dataSource.mysql.master-url}")
    private String url;

    @Value("${dataSource.mysql.master-username}")
    private String user;

    @Value("${dataSource.mysql.master-password}")
    private String password;

    @Value("${dataSource.mysql.driver}")
    private String driverClass;

    @Value("${dataSource.mysql.initialSize}")
    private Integer initialSize;

    @Value("${dataSource.mysql.minIdle}")
    private Integer minIdle;

    @Value("${dataSource.mysql.maxActive}")
    private Integer maxActive;

    @Value("${dataSource.mysql.maxWait}")
    private Integer maxWait;

    @Value("${dataSource.mysql.timeBetweenEvictionRunsMillis}")
    private Integer timeBetweenEvictionRunsMillis;

    @Value("${dataSource.mysql.minEvictableIdleTimeMillis}")
    private Integer minEvictableIdleTimeMillis;

    @Bean(name = "dataSource")
    @Primary
    public DataSource dataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        dataSource.setMaxActive(maxActive);
        dataSource.setMinIdle(minIdle);
        dataSource.setInitialSize(initialSize);
        dataSource.setMaxWait(maxWait);
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        return dataSource;
    }

    @Bean(name = "transactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean(name = "sqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MySqlConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

 

 

总结:

1. 基于springboot集成mybatis访问数据库,mapperLocation配置的路径必须指向资源文件,不能指向源代码文件。即mapperLocations必须指向src/main/resources目录下的某个路径,不能是src/mian/java路径下目录。否则mybatis不能正常运行而出现上述错误。

2. 不管是javaconfig还是xml的方式,思路其实是一样的,相对而言,xml的配置模式看起来更舒服。

 

springboot通过xml集成mybatis实现mysql数据库操作【探坑记】

标签:邮箱   sea   matching   sele   源代码   voc   必须   总结   1.0   

人气教程排行