<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
通过这个模块为我们做了以下几件事
1).tomcat-jdbc-{version}.jar为我们自动配置DataSource.
2).如果你没有定义任何DataSource,SpringBoot将会自动配置一个内存的数据库资源设置
3).如果没有设置任一个beans,SpringBoot会自动注册它
4).初始化数据库
如果我们在classpath里定义了schema.sql和data.sql文件,springBoot将会使用这些文件自动初始化数据库(但你必须选建库)
除了载入schema.sql和data.sql外,SpringBoot也会载入schema-${platform}.sql和data-${platform}.sql,如果在你的classpath下存在的话。
spring.datasource.schema=xxxx-db.sql 可以定义你的建库文件
spring.datasource.data=xxxx-data.sql 可以定义你的数据文件
spring.datasource.initialize=true|false 可以决定是不是要初始化这些数据库文件
spring.datasource.continueOnError=true|false 有了错误是否继续运行
一.数据连接池配置:
1.默认系统定义
1).属性配置文件(application.properties)
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
如果使用JNDI,则可以替代 spring.datasource 的 url、username、password,如:
spring.datasource.jndi-name=java:tomcat/datasources/example
2).pom.xml 配置maven依赖
<!-- MYSQL -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- Spring Boot JDBC -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
2.自定义数据连接池管理
如果你不想用默认的配置数据源,如你想用阿里巴巴的数据池管理数据源,你也可以自己配置先排除tomcat-jdbc的默认配置dataSource
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<exclusions>
<exclusion>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
</exclusion>
</exclusions>
</dependency>
定义自己的数据资源 这里使用了阿里巴巴的数据池管理,你也可以使用BasicDataSource
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.19</version>
</dependency>
/src/main/java/com/example/SpringBootJdbcDemoApplication.java
package com.example;
import com.alibaba.druid.pool.DruidDataSource;
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 javax.sql.DataSource;
@SpringBootApplication
public class SpringBootJdbcDemoApplication {
public static void main(String[] args) {
SpringApplication.run(SpringBootJdbcDemoApplication.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;
}
}
也可以用别的:
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
Bean
public DataSource dataSource() {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
dataSource.setUrl(env.getProperty("spring.datasource.url"));
dataSource.setUsername(env.getProperty("spring.datasource.username"));
dataSource.setPassword(env.getProperty("spring.datasource.password"));
return dataSource;
}
二.Java代码范例
StudentService.java
package org.springboot.sample.service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springboot.sample.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
@Service
public class StudentService {
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Student> getList(){
String sql = "SELECT ID,NAME,SCORE_SUM,SCORE_AVG, AGE FROM STUDENT";
return (List<Student>) jdbcTemplate.query(sql, new RowMapper<Student>(){
@Override
public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
Student stu = new Student();
stu.setId(rs.getInt("ID"));
stu.setAge(rs.getInt("AGE"));
stu.setName(rs.getString("NAME"));
stu.setSumScore(rs.getString("SCORE_SUM"));
stu.setAvgScore(rs.getString("SCORE_AVG"));
return stu;
}
});
}
}
Student.java 实体类
package org.springboot.sample.entity;
import java.io.Serializable;
public class Student implements Serializable{
private static final long serialVersionUID = 2120869894112984147L;
private int id;
private String name;
private String sumScore;
private String avgScore;
private int age;
// 节省文章长度,get set 方法省略
}
StudentController.java
package org.springboot.sample.controller;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springboot.sample.entity.Student;
import org.springboot.sample.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/stu")
public class StudentController {
private static Logger logger = LogManager.getLogger(StudentController.class);
@Autowired
private StudentService studentService;
@RequestMapping("/list")
public List<Student> getStus(){
logger.info("从数据库读取Student集合");
return studentService.getList();
}
}
然后启动项目,访问地址:
http://localhost:8080/stu/list 响应结果如下:
[{"id":1,"name":"张三","sumScore":"180","avgScore":"90","age":25}]