SpringBoot+Druid+Mybatis连接Oracle

最近有一个新项目需要开发搭建了个SpringBoot框架 记录一下!

Oracle连接jar编译到maven仓库参考:Maven编译jar包到本地仓库

1、SpringBoot版本:

2.1.6.RELEASE

2、pom配置

1. <!-- jdbc配置 -->
2. <dependency>
3. <groupId>org.springframework.boot</groupId>
4. <artifactId>spring-boot-starter-jdbc</artifactId>
5. </dependency>
6. <!-- web -->
7. <dependency>
8. <groupId>org.springframework.boot</groupId>
9. <artifactId>spring-boot-starter-web</artifactId>
10. </dependency>
11.  
12. <!-- druid连接池 -->
13. <dependency>
14. <groupId>com.alibaba</groupId>
15. <artifactId>druid</artifactId>
16. <version>1.1.10</version>
17. </dependency>
18.  
19. <!-- Oracle 驱动包 自行下载编译到maven仓库 -->
20. <dependency>
21. <groupId>org.oracle</groupId>
22. <artifactId>ojdbc6</artifactId>
23. <version>11.2.0.2.0</version>
24. </dependency>
25.  
26. <!-- Mybatis配置 -->
27. <dependency>
28. <groupId>org.mybatis.spring.boot</groupId>
29. <artifactId>mybatis-spring-boot-starter</artifactId>
30. <version>2.0.1</version>
31. </dependency>
32.  
33. <!-- AOP配置 -->
34. <dependency>
35. <groupId>org.springframework.boot</groupId>
36. <artifactId>spring-boot-starter-aop</artifactId>
37. </dependency>
38.  
39. <!-- log4j日志配置 -->
40. <dependency>
41. <groupId>log4j</groupId>
42. <artifactId>log4j</artifactId>
43. <version>1.2.17</version>
44. </dependency>

3、Druid配置

Druid配置类

1. package com.jst.tjgx.supervisionservice.datasource;
2.  
3. import com.alibaba.druid.pool.DruidDataSource;
4. import com.alibaba.druid.support.http.StatViewServlet;
5. import com.alibaba.druid.support.http.WebStatFilter;
6.  
7. import org.slf4j.Logger;
8. import org.slf4j.LoggerFactory;
9. import org.springframework.boot.context.properties.ConfigurationProperties;
10. import org.springframework.boot.web.servlet.FilterRegistrationBean;
11. import org.springframework.boot.web.servlet.ServletRegistrationBean;
12. import org.springframework.context.annotation.Bean;
13. import org.springframework.context.annotation.Configuration;
14.  
15. import javax.sql.DataSource;
16. import java.sql.SQLException;
17.  
18. @Configuration
19. public class DruidConfiguration {
20.  
21. private static final Logger logger = LoggerFactory.getLogger(DruidConfiguration.class);
22.  
23. private static final String DB_PREFIX = "spring.datasource";
24.  
25. @Bean
26. public ServletRegistrationBean druidServlet() {
27. logger.info("init Druid Servlet Configuration ");
28. ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
29. // IP白名单 (没有配置或者为空，则允许所有访问)
30. servletRegistrationBean.addInitParameter("allow", "");
31. // IP黑名单(共同存在时，deny优先于allow)
32. //servletRegistrationBean.addInitParameter("deny", "192.168.1.100");
33. //控制台管理用户
34. servletRegistrationBean.addInitParameter("loginUsername", "admin");
35. servletRegistrationBean.addInitParameter("loginPassword", "admin");
36. //是否能够重置数据 禁用HTML页面上的“Reset All”功能
37. servletRegistrationBean.addInitParameter("resetEnable", "false");
38. return servletRegistrationBean;
39. }
40.  
41. @Bean
42. public FilterRegistrationBean filterRegistrationBean() {
43. FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
44. filterRegistrationBean.addUrlPatterns("/*");
45. filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
46. return filterRegistrationBean;
47. }
48.  
49. @ConfigurationProperties(prefix = DB_PREFIX)
50. class IDataSourceProperties {
51. private String url;
52. private String username;
53. private String password;
54. private String driverClassName;
55. private int initialSize;
56. private int minIdle;
57. private int maxActive;
58. private int maxWait;
59. private int timeBetweenEvictionRunsMillis;
60. private int minEvictableIdleTimeMillis;
61. private String validationQuery;
62. private boolean testWhileIdle;
63. private boolean testOnBorrow;
64. private boolean testOnReturn;
65. private boolean poolPreparedStatements;
66. private int maxPoolPreparedStatementPerConnectionSize;
67. private String filters;
68. private String connectionProperties;
69.  
70. @Bean
71. public DataSource dataSource() {
72. DruidDataSource datasource = new DruidDataSource();
73. datasource.setUrl(url);
74. datasource.setUsername(username);
75. datasource.setPassword(password);
76. datasource.setDriverClassName(driverClassName);
77.  
78. //configuration
79. datasource.setInitialSize(initialSize);
80. datasource.setMinIdle(minIdle);
81. datasource.setMaxActive(maxActive);
82. datasource.setMaxWait(maxWait);
83. datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
84. datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
85. datasource.setValidationQuery(validationQuery);
86. datasource.setTestWhileIdle(testWhileIdle);
87. datasource.setTestOnBorrow(testOnBorrow);
88. datasource.setTestOnReturn(testOnReturn);
89. datasource.setPoolPreparedStatements(poolPreparedStatements);
90. datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
91. try {
92. datasource.setFilters(filters);
93. logger.info("druid configuration initialization success! ");
94. } catch (SQLException e) {
95. System.err.println("druid configuration initialization filter: " + e);
96. }
97. datasource.setConnectionProperties(connectionProperties);
98. return datasource;
99. }
100.  
101. public String getUrl() {
102. return url;
103. }
104.  
105. public void setUrl(String url) {
106. this.url = url;
107. }
108.  
109. public String getUsername() {
110. return username;
111. }
112.  
113. public void setUsername(String username) {
114. this.username = username;
115. }
116.  
117. public String getPassword() {
118. return password;
119. }
120.  
121. public void setPassword(String password) {
122. this.password = password;
123. }
124.  
125. public String getDriverClassName() {
126. return driverClassName;
127. }
128.  
129. public void setDriverClassName(String driverClassName) {
130. this.driverClassName = driverClassName;
131. }
132.  
133. public int getInitialSize() {
134. return initialSize;
135. }
136.  
137. public void setInitialSize(int initialSize) {
138. this.initialSize = initialSize;
139. }
140.  
141. public int getMinIdle() {
142. return minIdle;
143. }
144.  
145. public void setMinIdle(int minIdle) {
146. this.minIdle = minIdle;
147. }
148.  
149. public int getMaxActive() {
150. return maxActive;
151. }
152.  
153. public void setMaxActive(int maxActive) {
154. this.maxActive = maxActive;
155. }
156.  
157. public int getMaxWait() {
158. return maxWait;
159. }
160.  
161. public void setMaxWait(int maxWait) {
162. this.maxWait = maxWait;
163. }
164.  
165. public int getTimeBetweenEvictionRunsMillis() {
166. return timeBetweenEvictionRunsMillis;
167. }
168.  
169. public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
170. this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
171. }
172.  
173. public int getMinEvictableIdleTimeMillis() {
174. return minEvictableIdleTimeMillis;
175. }
176.  
177. public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
178. this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
179. }
180.  
181. public String getValidationQuery() {
182. return validationQuery;
183. }
184.  
185. public void setValidationQuery(String validationQuery) {
186. this.validationQuery = validationQuery;
187. }
188.  
189. public boolean isTestWhileIdle() {
190. return testWhileIdle;
191. }
192.  
193. public void setTestWhileIdle(boolean testWhileIdle) {
194. this.testWhileIdle = testWhileIdle;
195. }
196.  
197. public boolean isTestOnBorrow() {
198. return testOnBorrow;
199. }
200.  
201. public void setTestOnBorrow(boolean testOnBorrow) {
202. this.testOnBorrow = testOnBorrow;
203. }
204.  
205. public boolean isTestOnReturn() {
206. return testOnReturn;
207. }
208.  
209. public void setTestOnReturn(boolean testOnReturn) {
210. this.testOnReturn = testOnReturn;
211. }
212.  
213. public boolean isPoolPreparedStatements() {
214. return poolPreparedStatements;
215. }
216.  
217. public void setPoolPreparedStatements(boolean poolPreparedStatements) {
218. this.poolPreparedStatements = poolPreparedStatements;
219. }
220.  
221. public int getMaxPoolPreparedStatementPerConnectionSize() {
222. return maxPoolPreparedStatementPerConnectionSize;
223. }
224.  
225. public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
226. this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
227. }
228.  
229. public String getFilters() {
230. return filters;
231. }
232.  
233. public void setFilters(String filters) {
234. this.filters = filters;
235. }
236.  
237. public String getConnectionProperties() {
238. return connectionProperties;
239. }
240.  
241. public void setConnectionProperties(String connectionProperties) {
242. this.connectionProperties = connectionProperties;
243. }
244. }
245. }

application.properties Druid和Mybatis配置

1. #数据库配置
2. #连接池类型 阿里巴巴Druid
3. spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
4. #数据库地址
5. spring.datasource.url=jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 数据库IP地址)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = 更换成你的) ))
6. spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
7. spring.datasource.username=用户名
8. spring.datasource.password=密码
9. # 初始化大小，最小，最大
10. spring.datasource.initialSize=1
11. spring.datasource.minIdle=3
12. spring.datasource.maxActive=20
13. # 配置获取连接等待超时的时间
14. spring.datasource.maxWait=60000
15. # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
16. spring.datasource.timeBetweenEvictionRunsMillis=60000
17. # 配置一个连接在池中最小生存的时间，单位是毫秒
18. spring.datasource.minEvictableIdleTimeMillis=30000
19. spring.datasource.validationQuery=select 1 from dual
20. spring.datasource.testWhileIdle=true
21. spring.datasource.testOnBorrow=false
22. spring.datasource.testOnReturn=false
23. # 打开PSCache，并且指定每个连接上PSCache的大小
24. spring.datasource.poolPreparedStatements=true
25. spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
26. # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
27. spring.datasource.filters=stat,wall,slf4j
28. # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
29. spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
30. #mapper.xml文件 配置
31. #mybatis.config-location=classpath:mybatis/SQLMapConfig.xml
32. mybatis.mapper-locations=classpath:mybatis/*.xml

查看Druid的各种信息:

配置完成后启动项目可以访问:http://localhost:8080/druid