当前位置： 代码迷 >> 综合 >> spring boot + JdbcTemplate 使用druid数据源
  详细解决方案

spring boot + JdbcTemplate 使用druid数据源

热度：60   发布时间：2023-12-14 05:16:30.0

添加依赖

 

[html] view plain  copy
  1. <!-- spring-boot-starter-jdbc 模块  -->  
  2.      <dependency>  
  3.            <groupId>org.springframework.boot</groupId>  
  4.            <artifactId>spring-boot-starter-jdbc</artifactId>  
  5.        </dependency>  
  6.     <dependency>  
  7.              <groupId>mysql</groupId>  
  8.              <artifactId>mysql-connector-java</artifactId>  
  9.          </dependency>  

maven项目结构



pom.xml

[html] view plain  copy
  1. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  2.     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">  
  3.     <modelVersion>4.0.0</modelVersion>  
  4.     <groupId>com.me</groupId>  
  5.     <artifactId>springBootJdbc</artifactId>  
  6.     <version>0.0.1-SNAPSHOT</version>  
  7.   
  8.     <properties>  
  9.         <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>  
  10.     </properties>  
  11.   
  12.     <!-- Inherit defaults from Spring Boot -->  
  13.     <parent>  
  14.         <groupId>org.springframework.boot</groupId>  
  15.         <artifactId>spring-boot-starter-parent</artifactId>  
  16.         <version>1.4.0.RELEASE</version>  
  17.     </parent>  
  18.   
  19.     <!-- Add typical dependencies for a web application -->  
  20.     <dependencies>  
  21.         <dependency>  
  22.             <groupId>org.springframework.boot</groupId>  
  23.             <artifactId>spring-boot-starter-web</artifactId>  
  24.         </dependency>  
  25.         <!-- spring-boot-starter-test 模块 -->  
  26.         <dependency>  
  27.             <groupId>org.springframework.boot</groupId>  
  28.             <artifactId>spring-boot-starter-test</artifactId>  
  29.             <scope>test</scope>  
  30.         </dependency>  
  31.         <!-- spring-boot-starter-jdbc 模块  -->  
  32.          <dependency>  
  33.             <groupId>org.springframework.boot</groupId>  
  34.             <artifactId>spring-boot-starter-jdbc</artifactId>  
  35.         </dependency>  
  36.         <dependency>  
  37.               <groupId>mysql</groupId>  
  38.               <artifactId>mysql-connector-java</artifactId>  
  39.           </dependency>  
  40.            
  41.         <!-- 热部署 -->  
  42.         <dependency>  
  43.             <groupId>org.springframework.boot</groupId>  
  44.             <artifactId>spring-boot-devtools</artifactId>  
  45.             <optional>true</optional>  
  46.         </dependency>  
  47.     </dependencies>  
  48.   
  49.     <!-- Package as an executable jar -->  
  50.     <build>  
  51.         <plugins>  
  52.             <plugin>  
  53.                 <groupId>org.springframework.boot</groupId>  
  54.                 <artifactId>spring-boot-maven-plugin</artifactId>  
  55.             </plugin>  
  56.         </plugins>  
  57.     </build>  
  58. </project>  

application.properties

[html] view plain  copy
  1. ########################################################  
  2. ###datasource  
  3. ########################################################  
  4. spring.datasource.url = jdbc:mysql://localhost:3306/test  
  5. spring.datasource.username = root  
  6. spring.datasource.password = root  
  7. spring.datasource.driverClassName = com.mysql.jdbc.Driver  
  8. spring.datasource.max-active=20  
  9. spring.datasource.max-idle=8  
  10. spring.datasource.min-idle=8  
  11. spring.datasource.initial-size=10  

或者使用

application.yml

[html] view plain  copy
  1. logging:  
  2.   level:  
  3.     org.springframework: INFO  
  4.     com.example: DEBUG  
  5. ################### DataSource Configuration ##########################  
  6. spring:  
  7.   datasource:  
  8.     driver-class-name: com.mysql.jdbc.Driver  
  9.     url: jdbc:mysql://localhost:3306/test  
  10.     username: root  
  11.     password: root  
  12.     initialize: true  
  13. init-db: true  



User.java

[html] view plain  copy
  1. package com.example.domain;  
  2.   
  3. public class User {  
  4.     private Integer id;  
  5.     private String name;  
  6.     private String email;  
  7.   
  8.     public User() {  
  9.     }  
  10.   
  11.     public User(Integer id, String name, String email) {  
  12.         this.id = id;  
  13.         this.name = name;  
  14.         this.email = email;  
  15.     }  
  16.   
  17.     public Integer getId() {  
  18.         return id;  
  19.     }  
  20.   
  21.     public void setId(Integer id) {  
  22.         this.id = id;  
  23.     }  
  24.   
  25.     public String getName() {  
  26.         return name;  
  27.     }  
  28.   
  29.     public void setName(String name) {  
  30.         this.name = name;  
  31.     }  
  32.   
  33.     public String getEmail() {  
  34.         return email;  
  35.     }  
  36.   
  37.     public void setEmail(String email) {  
  38.         this.email = email;  
  39.     }  
  40.   
  41.     @Override  
  42.     public String toString() {  
  43.         return "User{" + "id=" + id + ", name='" + name + '\'' + ", email='" + email + '\'' + '}';  
  44.     }  
  45. }  
UserRepository.java

[html] view plain  copy
  1. package com.example.repositories;  
  2.   
  3. import java.sql.Connection;  
  4. import java.sql.PreparedStatement;  
  5. import java.sql.ResultSet;  
  6. import java.sql.SQLException;  
  7. import java.sql.Statement;  
  8. import java.util.List;  
  9.   
  10. import org.springframework.beans.factory.annotation.Autowired;  
  11. import org.springframework.jdbc.core.JdbcTemplate;  
  12. import org.springframework.jdbc.core.PreparedStatementCreator;  
  13. import org.springframework.jdbc.core.RowMapper;  
  14. import org.springframework.jdbc.support.GeneratedKeyHolder;  
  15. import org.springframework.jdbc.support.KeyHolder;  
  16. import org.springframework.stereotype.Repository;  
  17. import org.springframework.transaction.annotation.Transactional;  
  18.   
  19. import com.example.domain.User;  
  20.   
  21. @Repository  
  22. public class UserRepository {  
  23.     @Autowired  
  24.     private JdbcTemplate jdbcTemplate;  
  25.   
  26.     @Transactional(readOnly = true)  
  27.     public List<User> findAll() {  
  28.         return jdbcTemplate.query("select * from users", new UserRowMapper());  
  29.     }  
  30.   
  31.     @Transactional(readOnly = true)  
  32.     public User findUserById(int id) {  
  33.         return jdbcTemplate.queryForObject("select * from users where id=?", new Object[] { id }, new UserRowMapper());  
  34.     }  
  35.   
  36.     public User create(final User user) {  
  37.         final String sql = "insert into users(name,email) values(?,?)";  
  38.   
  39.         KeyHolder holder = new GeneratedKeyHolder();  
  40.   
  41.         jdbcTemplate.update(new PreparedStatementCreator() {  
  42.   
  43.             @Override  
  44.             public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {  
  45.                 PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);  
  46.                 ps.setString(1, user.getName());  
  47.                 ps.setString(2, user.getEmail());  
  48.                 return ps;  
  49.             }  
  50.         }, holder);  
  51.   
  52.         int newUserId = holder.getKey().intValue();  
  53.         user.setId(newUserId);  
  54.         return user;  
  55.     }  
  56.   
  57.     public void delete(final Integer id) {  
  58.         final String sql = "delete from users where id=?";  
  59.         jdbcTemplate.update(sql, new Object[] { id }, new int[] { java.sql.Types.INTEGER });  
  60.     }  
  61.   
  62.     public void update(final User user) {  
  63.         jdbcTemplate.update("update users set name=?,email=? where id=?",  
  64.                 new Object[] { user.getName(), user.getEmail(), user.getId() });  
  65.     }  
  66. }  
  67.   
  68. class UserRowMapper implements RowMapper<User> {  
  69.   
  70.     @Override  
  71.     public User mapRow(ResultSet rs, int rowNum) throws SQLException {  
  72.         User user = new User();  
  73.         user.setId(rs.getInt("id"));  
  74.         user.setName(rs.getString("name"));  
  75.         user.setEmail(rs.getString("email"));  
  76.   
  77.         return user;  
  78.     }  
  79. }  

测试类

[html] view plain  copy
  1. package com.example.SpringBootJdbcTest;  
  2.   
  3. import java.util.List;  
  4.   
  5. import org.junit.Test;  
  6. import org.junit.runner.RunWith;  
  7. import org.springframework.beans.factory.annotation.Autowired;  
  8. import org.springframework.boot.test.context.SpringBootTest;  
  9. import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;  
  10.   
  11. import com.example.Application;  
  12. import com.example.domain.User;  
  13. import com.example.repositories.UserRepository;  
  14.   
  15. @RunWith(SpringJUnit4ClassRunner.class)  
  16. @SpringBootTest(classes=Application.class)// 指定spring-boot的启动类   
  17. //@SpringApplicationConfiguration(classes = Application.class)// 1.4.0 前版本  
  18. public class SpringBootJdbcTest {  
  19.        
  20.         @Autowired  
  21.         private UserRepository userRepository;  
  22.   
  23.   
  24.         @Test  
  25.         public void findAllUsers()  {  
  26.             List<User> users = userRepository.findAll();  
  27.             System.out.println(users);  
  28.               
  29.   
  30.         }  
  31.   
  32.         @Test  
  33.         public void findUserById()  {  
  34.             User user = userRepository.findUserById(1);  
  35.           
  36.         }  
  37.         @Test  
  38.         public void updateById()  {  
  39.             User newUser = new User(3, "JackChen", "JackChen@qq.com");  
  40.             userRepository.update(newUser);  
  41.             User newUser2 = userRepository.findUserById(newUser.getId());  
  42.            
  43.         }  
  44.           
  45.           
  46.           
  47.         @Test  
  48.         public void createUser() {  
  49.             User user = new User(0, "tom", "tom@gmail.com");  
  50.             User savedUser = userRepository.create(user);  
  51.          
  52.         }  
  53. }  

Main 主类：Application.java

[html] view plain  copy
  1. package com.example;  
  2.   
  3. import org.springframework.boot.SpringApplication;  
  4. import org.springframework.boot.autoconfigure.SpringBootApplication;  
  5.   
  6.   
  7. @SpringBootApplication  
  8. public class Application {  
  9.     public static void main(String[] args) {  
  10.          SpringApplication.run(Application.class, args);  
  11.     }  
  12. }  

说明:UserRepository.java  必须在Application.java同包或者在Application.java的当前包的子包下，否则扫描不到@Repository 


spring-boot-starter-jdbc 默认使用tomcat-jdbc数据源
如何自定义数据源

定义自己的数据资源 这里使用了阿里巴巴的数据池管理,你也可以使用BasicDataSource

[html] view plain  copy
  1. <dependency>  
  2.             <groupId>com.alibaba</groupId>  
  3.             <artifactId>druid</artifactId>  
  4.             <version>1.0.19</version>  
  5. </dependency>  
[html] view plain  copy
  1.   

修改Application.java
[html] view plain  copy
  1. package com.example;  
  2.   
  3. import javax.sql.DataSource;  
  4.   
  5. import org.springframework.beans.factory.annotation.Autowired;  
  6. import org.springframework.boot.SpringApplication;  
  7. import org.springframework.boot.autoconfigure.SpringBootApplication;  
  8. import org.springframework.context.annotation.Bean;  
  9. import org.springframework.core.env.Environment;  
  10.   
  11. import com.alibaba.druid.pool.DruidDataSource;  
  12.   
  13.   
  14. @SpringBootApplication  
  15. public class Application {  
  16.     public static void main(String[] args) {  
  17.          SpringApplication.run(Application.class, args);  
  18.     }  
  19.     @Autowired  
  20.     private Environment env;  
  21.   
  22.     @Bean  
  23.     public DataSource dataSource() {  
  24.         DruidDataSource dataSource = new DruidDataSource();  
  25.         dataSource.setUrl(env.getProperty("spring.datasource.url"));  
  26.         dataSource.setUsername(env.getProperty("spring.datasource.username"));//用户名  
  27.         dataSource.setPassword(env.getProperty("spring.datasource.password"));//密码  
  28.         dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));  
  29.         dataSource.setInitialSize(2);  
  30.         dataSource.setMaxActive(20);  
  31.         dataSource.setMinIdle(0);  
  32.         dataSource.setMaxWait(60000);  
  33.         dataSource.setValidationQuery("SELECT 1");  
  34.         dataSource.setTestOnBorrow(false);  
  35.         dataSource.setTestWhileIdle(true);  
  36.         dataSource.setPoolPreparedStatements(false);  
  37.         return dataSource;  
  38.     }  
  39. }  

或者添加DataBaseConfiguration.java
[html] view plain  copy
  1. package com.example.configuration;  
  2.   
  3. import javax.sql.DataSource;  
  4.   
  5. import org.springframework.boot.bind.RelaxedPropertyResolver;  
  6. import org.springframework.context.EnvironmentAware;  
  7. import org.springframework.context.annotation.Bean;  
  8. import org.springframework.context.annotation.Configuration;  
  9. import org.springframework.core.env.Environment;  
  10. import org.springframework.transaction.annotation.EnableTransactionManagement;  
  11.   
  12. import com.alibaba.druid.pool.DruidDataSource;  
  13.   
  14. @Configuration  
  15. @EnableTransactionManagement  
  16. public class DataBaseConfiguration implements EnvironmentAware {  
  17.   
  18.      private RelaxedPropertyResolver propertyResolver;  
  19.   
  20.         @Override  
  21.         public void setEnvironment(Environment env) {  
  22.             this.propertyResolver = new RelaxedPropertyResolver(env, "spring.datasource.");  
  23.         }  
  24.           
  25.         @Bean(destroyMethod = "close", initMethod = "init")  
  26.         public DataSource writeDataSource() {  
  27.             System.out.println("注入druid！！！");  
  28.               
  29.              
  30.             DruidDataSource dataSource = new DruidDataSource();  
  31.             dataSource.setUrl(propertyResolver.getProperty("url"));  
  32.             dataSource.setUsername(propertyResolver.getProperty("username"));//用户名  
  33.             dataSource.setPassword(propertyResolver.getProperty("password"));//密码  
  34.             dataSource.setDriverClassName(propertyResolver.getProperty("driver-class-name"));  
  35.             dataSource.setInitialSize(2);  
  36.             dataSource.setMaxActive(20);  
  37.             dataSource.setMinIdle(0);  
  38.             dataSource.setMaxWait(60000);  
  39.             dataSource.setValidationQuery("SELECT 1");  
  40.             dataSource.setTestOnBorrow(false);  
  41.             dataSource.setTestWhileIdle(true);  
  42.             dataSource.setPoolPreparedStatements(false);  
  43.             return dataSource;  
  44.         }  
  45.   
  46. }  
[html] view plain  copy
  1. application.properties  
[html] view plain  copy
  1. ##数据库连接信息  
  2. spring.datasource.url=jdbc:mysql://localhost:3306/test  
  3. spring.datasource.username=root  
  4. spring.datasource.password=root  
  5. spring.datasource.driver-class-name=com.mysql.jdbc.Driver  
  6. ###################以下为druid增加的配置###########################  
  7. spring.datasource.type=com.alibaba.druid.pool.DruidDataSource  
  8. # 下面为连接池的补充设置，应用到上面所有数据源中  
  9. # 初始化大小，最小，最大  
  10. spring.datasource.initialSize=5  
  11. spring.datasource.minIdle=5  
  12. spring.datasource.maxActive=20  
  13. # 配置获取连接等待超时的时间  
  14. spring.datasource.maxWait=60000  
  15. # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒  
  16. #spring.datasource.timeBetweenEvictionRunsMillis=60000  
  17. # 配置一个连接在池中最小生存的时间，单位是毫秒  
  18. spring.datasource.minEvictableIdleTimeMillis=300000  
  19. spring.datasource.validationQuery=select 'x'  
  20. spring.datasource.testWhileIdle=true  
  21. spring.datasource.testOnBorrow=true  
  22. spring.datasource.testOnReturn=true  
  23. # 打开PSCache，并且指定每个连接上PSCache的大小  
  24. spring.datasource.poolPreparedStatements=true  
  25. spring.datasource.maxPoolPreparedStatementPerConnectionSize=20  
  26. # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙  
  27. spring.datasource.filters=stat,wall,log4j  
  28. # 通过connectProperties属性来打开mergeSql功能；慢SQL记录  
  29. spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000  
  30. # 合并多个DruidDataSource的监控数据  
  31. #spring.datasource.useGlobalDataSourceStat=true  
  32. ###############以上为配置druid添加的配置########################################  
使用ConfigurationProperties注解读取配置参数
http://blog.csdn.net/yingxiake/article/details/51263071
[html] view plain  copy
  1. <dependency>  
  2.     <groupId>org.springframework.boot</groupId>  
  3.     <artifactId>spring-boot-configuration-processor</artifactId>  
  4.     <optional>true</optional>  
  5. </dependency>  
读取配置参数，并注入
[html] view plain  copy
  1. import org.springframework.boot.context.properties.ConfigurationProperties;  
  2. import org.springframework.boot.web.servlet.FilterRegistrationBean;  
  3. import org.springframework.boot.web.servlet.ServletRegistrationBean;  
  4. import org.springframework.context.annotation.Bean;  
  5. import org.springframework.context.annotation.Configuration;  
  6.   
  7. import com.alibaba.druid.pool.DruidDataSource;  
  8. import com.alibaba.druid.support.http.StatViewServlet;  
  9. import com.alibaba.druid.support.http.WebStatFilter;  
  10.   
  11. @Configuration  
  12. public class DataBaseConfiguration {  
  13.       
  14.   
  15.     @Bean(destroyMethod = "close", initMethod = "init")  
  16.     @ConfigurationProperties("spring.datasource")  
  17.     public com.alibaba.druid.pool.DruidDataSource dataSource() {  
  18.   
  19.         System.out.println("注入druid！！！");  
  20.         DruidDataSource druidDataSource = new DruidDataSource();  
  21.         return druidDataSource;  
  22.     }  
  23. }  




参考：http://www.cnblogs.com/tomlxq/p/5514658.html
http://www.cnblogs.com/cl2Blogs/p/5679653.html
代码： http://download.csdn.net/detail/u014695188/9608670
  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.