spring boot + JdbcTemplate 使用druid数据源
druid
阿里云计算平台DataWorks(https://help.aliyun.com/document_detail/137663.html) 团队出品，为监控而生的数据库连接池
项目地址：https://gitcode.com/gh_mirrors/druid/druid
免费下载资源
·
添加依赖
<!-- spring-boot-starter-jdbc 模块 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>maven项目结构
pom.xml
<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>
<groupId>com.me</groupId>
<artifactId>springBootJdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<!-- Inherit defaults from Spring Boot -->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.4.0.RELEASE</version>
</parent>
<!-- Add typical dependencies for a web application -->
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- spring-boot-starter-test 模块 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- spring-boot-starter-jdbc 模块 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 热部署 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
<!-- Package as an executable jar -->
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>application.properties
########################################################
###datasource
########################################################
spring.datasource.url = jdbc:mysql://localhost:3306/test
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.max-active=20
spring.datasource.max-idle=8
spring.datasource.min-idle=8
spring.datasource.initial-size=10或者使用
application.yml
logging:
level:
org.springframework: INFO
com.example: DEBUG
################### DataSource Configuration ##########################
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/test
username: root
password: root
initialize: true
init-db: true
User.java
package com.example.domain;
public class User {
private Integer id;
private String name;
private String email;
public User() {
}
public User(Integer id, String name, String email) {
this.id = id;
this.name = name;
this.email = email;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User{" + "id=" + id + ", name='" + name + '\'' + ", email='" + email + '\'' + '}';
}
}
package com.example.repositories;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.example.domain.User;
@Repository
public class UserRepository {
@Autowired
private JdbcTemplate jdbcTemplate;
@Transactional(readOnly = true)
public List<User> findAll() {
return jdbcTemplate.query("select * from users", new UserRowMapper());
}
@Transactional(readOnly = true)
public User findUserById(int id) {
return jdbcTemplate.queryForObject("select * from users where id=?", new Object[] { id }, new UserRowMapper());
}
public User create(final User user) {
final String sql = "insert into users(name,email) values(?,?)";
KeyHolder holder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, user.getName());
ps.setString(2, user.getEmail());
return ps;
}
}, holder);
int newUserId = holder.getKey().intValue();
user.setId(newUserId);
return user;
}
public void delete(final Integer id) {
final String sql = "delete from users where id=?";
jdbcTemplate.update(sql, new Object[] { id }, new int[] { java.sql.Types.INTEGER });
}
public void update(final User user) {
jdbcTemplate.update("update users set name=?,email=? where id=?",
new Object[] { user.getName(), user.getEmail(), user.getId() });
}
}
class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setEmail(rs.getString("email"));
return user;
}
}
测试类
package com.example.SpringBootJdbcTest;
import java.util.List;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import com.example.Application;
import com.example.domain.User;
import com.example.repositories.UserRepository;
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes=Application.class)// 指定spring-boot的启动类
//@SpringApplicationConfiguration(classes = Application.class)// 1.4.0 前版本
public class SpringBootJdbcTest {
@Autowired
private UserRepository userRepository;
@Test
public void findAllUsers() {
List<User> users = userRepository.findAll();
System.out.println(users);
}
@Test
public void findUserById() {
User user = userRepository.findUserById(1);
}
@Test
public void updateById() {
User newUser = new User(3, "JackChen", "JackChen@qq.com");
userRepository.update(newUser);
User newUser2 = userRepository.findUserById(newUser.getId());
}
@Test
public void createUser() {
User user = new User(0, "tom", "tom@gmail.com");
User savedUser = userRepository.create(user);
}
}
Main 主类：Application.java
package com.example;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
说明:UserRepository.java 必须在Application.java同包或者在Application.java的当前包的子包下，否则扫描不到@Repository
spring-boot-starter-jdbc 默认使用tomcat-jdbc数据源代码： http://download.csdn.net/detail/u014695188/9608670如何自定义数据源定义自己的数据资源 这里使用了阿里巴巴的数据池管理,你也可以使用BasicDataSource
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.19</version> </dependency>修改Application.java
package com.example; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.context.annotation.Bean; import org.springframework.core.env.Environment; import com.alibaba.druid.pool.DruidDataSource; @SpringBootApplication public class Application { public static void main(String[] args) { SpringApplication.run(Application.class, args); } @Autowired private Environment env; @Bean public DataSource dataSource() { DruidDataSource dataSource = new DruidDataSource(); dataSource.setUrl(env.getProperty("spring.datasource.url")); dataSource.setUsername(env.getProperty("spring.datasource.username"));//用户名 dataSource.setPassword(env.getProperty("spring.datasource.password"));//密码 dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name")); dataSource.setInitialSize(2); dataSource.setMaxActive(20); dataSource.setMinIdle(0); dataSource.setMaxWait(60000); dataSource.setValidationQuery("SELECT 1"); dataSource.setTestOnBorrow(false); dataSource.setTestWhileIdle(true); dataSource.setPoolPreparedStatements(false); return dataSource; } }
或者添加DataBaseConfiguration.java
package com.example.configuration; import javax.sql.DataSource; import org.springframework.boot.bind.RelaxedPropertyResolver; import org.springframework.context.EnvironmentAware; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.env.Environment; import org.springframework.transaction.annotation.EnableTransactionManagement; import com.alibaba.druid.pool.DruidDataSource; @Configuration @EnableTransactionManagement public class DataBaseConfiguration implements EnvironmentAware { private RelaxedPropertyResolver propertyResolver; @Override public void setEnvironment(Environment env) { this.propertyResolver = new RelaxedPropertyResolver(env, "spring.datasource."); } @Bean(destroyMethod = "close", initMethod = "init") public DataSource writeDataSource() { System.out.println("注入druid！！！"); DruidDataSource dataSource = new DruidDataSource(); dataSource.setUrl(propertyResolver.getProperty("url")); dataSource.setUsername(propertyResolver.getProperty("username"));//用户名 dataSource.setPassword(propertyResolver.getProperty("password"));//密码 dataSource.setDriverClassName(propertyResolver.getProperty("driver-class-name")); dataSource.setInitialSize(2); dataSource.setMaxActive(20); dataSource.setMinIdle(0); dataSource.setMaxWait(60000); dataSource.setValidationQuery("SELECT 1"); dataSource.setTestOnBorrow(false); dataSource.setTestWhileIdle(true); dataSource.setPoolPreparedStatements(false); return dataSource; } }application.properties##数据库连接信息 spring.datasource.url=jdbc:mysql://localhost:3306/test spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.jdbc.Driver ###################以下为druid增加的配置########################### spring.datasource.type=com.alibaba.druid.pool.DruidDataSource # 下面为连接池的补充设置，应用到上面所有数据源中 # 初始化大小，最小，最大 spring.datasource.initialSize=5 spring.datasource.minIdle=5 spring.datasource.maxActive=20 # 配置获取连接等待超时的时间 spring.datasource.maxWait=60000 # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 #spring.datasource.timeBetweenEvictionRunsMillis=60000 # 配置一个连接在池中最小生存的时间，单位是毫秒 spring.datasource.minEvictableIdleTimeMillis=300000 spring.datasource.validationQuery=select 'x' spring.datasource.testWhileIdle=true spring.datasource.testOnBorrow=true spring.datasource.testOnReturn=true # 打开PSCache，并且指定每个连接上PSCache的大小 spring.datasource.poolPreparedStatements=true spring.datasource.maxPoolPreparedStatementPerConnectionSize=20 # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 spring.datasource.filters=stat,wall,log4j # 通过connectProperties属性来打开mergeSql功能；慢SQL记录 spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 # 合并多个DruidDataSource的监控数据 #spring.datasource.useGlobalDataSourceStat=true ###############以上为配置druid添加的配置########################################使用ConfigurationProperties注解读取配置参数http://blog.csdn.net/yingxiake/article/details/51263071
读取配置参数，并注入<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <optional>true</optional> </dependency>import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.web.servlet.FilterRegistrationBean; import org.springframework.boot.web.servlet.ServletRegistrationBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; @Configuration public class DataBaseConfiguration { @Bean(destroyMethod = "close", initMethod = "init") @ConfigurationProperties("spring.datasource") public com.alibaba.druid.pool.DruidDataSource dataSource() { System.out.println("注入druid！！！"); DruidDataSource druidDataSource = new DruidDataSource(); return druidDataSource; } }
参考：http://www.cnblogs.com/tomlxq/p/5514658.htmlhttp://www.cnblogs.com/cl2Blogs/p/5679653.html
GitHub 加速计划 / druid / druid
27.83 K
8.56 K
下载
阿里云计算平台DataWorks(https://help.aliyun.com/document_detail/137663.html) 团队出品，为监控而生的数据库连接池
最近提交(Master分支：3 个月前 )
f060c270 - 9 天前
1613a765
* Improve gaussdb ddl parser
* fix temp table 10 天前
旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。
更多推荐
1
0- 0
已为社区贡献8条内容
所有评论(0)