spring boot 通过jndi访问mysql数据库

使用spring boot 通过jndi访问 tomcat 管理的mysql连接池
踩过许多坑 才总结出的经验分享给大家
首先配置Tomcat链接mysql的连接池

1. 下载mysql驱动 地址：
   下载地址
   如果是win系统 Select Operating System: 选择 Platform Independent (Architecture Independent), ZIP Archive
   其他根据自己系统选择相应安装包
2. 这里要在CLASSPATH中指定“mysql-connector-java-8.0.12.jar”文件的位置，否则会报错，提示driverClassName属性不能识别：
   　　a.mysql-connector-java-8.0.12.jar复制到D:\env\apache-tomcat-8.5.32\lib下
   　　b.在环境变量中设定CATALINA_HOME为tomcat路径“D:\env\apache-tomcat-8.5.32”；
   　　c.CLASSPATH中添加%CATALINA_HOME%\lib;
3. 需要修改tomcat的配置文件，在Tomcat的conf/Context.xml <Context>标签下添加<Resource>标签

<Context>

    <!-- Default set of monitored resources. If one of these changes, the    -->
    <!-- web application will be reloaded.                                   -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>

		
	<Resource name = "jdbc/dadiaocha" 
	    auth="Container" 
	    type="javax.sql.DataSource"
		maxTotal="100" 
		maxIdle="30" 
		maxWaitMillis="10000" 
		username="root"
		password="admin"
		driverClassName="com.mysql.cj.jdbc.Driver"
		url="jdbc:mysql://127.0.0.1:3306/dadiaocha?serverTimezone=GMT%2B8&amp;useSSL=false" />
    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <!--
    <Manager pathname="" />
    -->
</Context>

Resource标签属性
name:连接池名称，一般设定为jdbc/databasename
auth:设定控制权为容器，固定
type:数据类型，固定
maxTotal：最大活动连接数，在之前版本中是maxActive
maxIdle：最大空闲连接数
maxWaitMillis：最大空闲时间，在之前版本中是maxWait
userName：用户名
password：密码
dirverClassName：
MySQL6.0之后Driver(这里跟只跟ConnectionJ相关，ConnectionJ8.0驱动是com.mysql.cj.jdbc.Driver，但是MySQL也可以使用之前版本的ConnectionJ)名改为“com.mysql.cj.jdbc.Driver”，之前是“com.mysql.jdbc.Driver”。
url:
指定数据库连接ip和数据库名称
jdbc:mysql://127.0.0.1:3306/databaseName
对于MySQL5.*及之前版本只需写到此处就行，MySQL6.0及之后版本需要 指定服务器时区属性，设定useSSL属性等，个属性之间用&连接，在xml/html文件中&用&转义表示，应该写成：

jdbc:mysql://127.0.0.1:3306/databaseName?serverTimeZone=GMT%2B8&useSSL=false

若出现字符集问题则需添加下面两个参数：
useUnicode=true
characterEncoding=utf8
链接仍使用&

到此 tomcat链接mysql就设置完毕 接下来设置springboot 代码
使用的spring boot 版本 2.1.5.RELEASE 需要打包成war放在tomcat容器里
pom.xml 文件： 注意<packaging>war</packaging> 需要设置war

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.5.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId></groupId>
	<artifactId>spring-boot-jndi</artifactId>
	<packaging>war</packaging>
	<version>0.0.1-SNAPSHOT</version>
	<name>spring-boot-jndi</name>
	<description>Demo spring data jndi</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!-- 运行时不使用spring boot 自带tomcat -->
		<dependency> 
			<groupId>org.springframework.boot</groupId> 
				<artifactId>spring-boot-starter-tomcat</artifactId> 
			<scope>provided</scope> 
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>    
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

启动文件SpringBootApplication 设置

@SpringBootApplication
public class SpringBootJndiApplication extends SpringBootServletInitializer {

	@Override 
	protected SpringApplicationBuilder configure(SpringApplicationBuilder application) { 
		return application.sources(SpringBootJndiApplication.class); 
	} 
	
	public static void main(String[] args) throws Exception { 
		SpringApplication.run(SpringBootJndiApplication.class, args); 
	} 

}

创建bean
返回数据源

@Configuration
public class TomcatConfigs {
 
	 @Bean
	    public DataSource masterDataSource() throws IllegalArgumentException, NamingException {
	        JndiObjectFactoryBean bean = new JndiObjectFactoryBean();
	        bean.setJndiName("jdbc/dadiaocha");
	        bean.setProxyInterface(DataSource.class);
	        bean.setLookupOnStartup(false);
	        bean.setResourceRef(true);
	        bean.afterPropertiesSet();
	        return (DataSource) bean.getObject();
	    }
}

首先创建实体类

public class DadiaochaTable{
	private int id;		
	private String name;		
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}

创建controller测试

@Controller
@RequestMapping("/")
public class HomeController {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@GetMapping("")
	@ResponseBody
	public List<DadiaochaTable> upload() {
		List<DadiaochaTable> customers = new ArrayList<DadiaochaTable>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList("SELECT * FROM dadiaocha_table");		
		for (Map<String, Object> row : rows) {
			DadiaochaTable customer = new DadiaochaTable();			
			customer.setName((String)row.get("name"));			
			customers.add(customer);
		}
		return customers;
	}
}

测试成功