在SpringMVC中使用Tomcat里配置的DB连接(SQLServer版)

首次在Spring里使用微软的SQLServer，还得用tomcat中配置的DB连接信息，

配置过程中，遇到了不少的坑，特此留个笔记，方便以后查看，大神勿喷。

首先环境如下所示

    JDK11

    Terasoluna5.7(日本NTTData公司在SpringMVC基础上2次开发的版本)

    Tomcat9.x

    SQLServer2019

第一步Tomcat设置

   server.xml的GlobalNamingResources节点中加入 Resource name="jdbc/testDbSource"这个子节点的内容

    注意 name="jdbc/testDbSource"这个属性很重要，context.xml还有Spring的配置中也会用到

<GlobalNamingResources>
    <Resource name="UserDatabase" auth="Container"
               type="org.apache.catalina.UserDatabase"
               description="User database that can be updated and saved"
               factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
               pathname="conf/tomcat-users.xml" />
    <Resource name="jdbc/testDbSource"
              auth="Container"
              type="javax.sql.DataSource"
              username="sa"
              password="chenxu1234"
              url="jdbc:sqlserver://127.0.0.1:1433;databaseName=test;integratedSecurity=false;"
              driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
              maxTotal="75"
              maxIdle="20"
              />
  </GlobalNamingResources>

context.xml的Context节点中加入下面的内容

   name和global和上面server.xml中设置的name属性要一致

<ResourceLink name="jdbc/testDbSource"
   global="jdbc/testDbSource"
   type="javax.sql.DataSource"/>

tomcat设置参照的是https://stackoverflow.com/questions/9183321/how-to-use-jndi-datasource-provided-by-tomcat-in-spring

第二步pom.xml配置

   加入Mybatis，SQLServer驱动，Spring事务管理三个依赖包

        <dependency>
            <groupId>org.terasoluna.gfw</groupId>
            <artifactId>terasoluna-gfw-mybatis3-dependencies</artifactId>
            <type>pom</type>
        </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>9.2.1.jre11</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.3.8</version>
        </dependency>

第三步Terasoluna配置

   projectName-env.xml

   注意jndi-name需要和tomcat的server.xml中配置的name一样

   还有最开始的

   关于事务的写法参照了

https://www.cnblogs.com/deolin/p/7251089.html(部分的写法)

http://terasolunaorg.github.io/guideline/5.7.0.RELEASE/ja/ArchitectureInDetail/DataAccessDetail/DataAccessMyBatis3.html#dataaccessmybatis3howtousesettingstransactionmanager

<?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:jee="http://www.springframework.org/schema/jee"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/jdbc
        https://www.springframework.org/schema/jdbc/spring-jdbc.xsd
        http://www.springframework.org/schema/jee
        https://www.springframework.org/schema/jee/spring-jee.xsd
        http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/tx
        https://www.springframework.org/schema/tx/spring-tx.xsd">

    <!-- omitted -->

    <jee:jndi-lookup id="dataSource" jndi-name="jdbc/testDbSource" />

    <!-- (1) -->
    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <!-- (2) -->
        <property name="dataSource" ref="dataSource" />
        <!-- (3) -->
        <property name="rollbackOnCommitFailure" value="true" />
    </bean>
    <tx:annotation-driven transaction-manager="transactionManager" />

    <!-- omitted -->

</beans>

projectName-infra.xml

  主要是引用projectName-env.xml里面配好的id="dataSource"，把它设置到

<?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"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://mybatis.org/schema/mybatis-spring
        http://mybatis.org/schema/mybatis-spring.xsd">

    <import resource="classpath:/META-INF/spring/projectName-env.xml" />

    <!-- (1) -->
    <bean id="sqlSessionFactory"
        class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- (2) -->
        <property name="dataSource" ref="dataSource" />
        <!-- (3) -->
        <property name="configLocation"
            value="classpath:/META-INF/mybatis/mybatis-config.xml" />
    </bean>

    <!-- (4) -->
    <mybatis:scan base-package="xxxxxx.yyyyyy.zzzzzz.domain.repository" />

</beans>

然后创建一个mybatis目录，里面放进去mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org/DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <settings>
        <!-- (1) -->
        <setting name="defaultFetchSize" value="100" />
    </settings>

</configuration>

这样就OK了，第一次配这个真是各种坑啊，百度出来的东西很多都不全或者不对，还好最后成功配置，以后就会少走很多弯路啦