一、JDBC
1、使用默认数据源类型
①在使用IDEA创建web项目时,选中Jdbc和MySQL的启动器
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
② 在全局配置文件(application.properties)中配置数据源相关信息
数据源相关的配置都在DataSourceProperties里面
spring.datasource.url=jdbc:mysql://localhost:3306/jdbc
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
③测试(springboot已经自动配置了JdbcTemplat):
@Controller
public class HelloController {
@Autowired
private JdbcTemplate jdbcTemplate;
@ResponseBody
@GetMapping("/query")
public Map<String, Object> map(){
List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from department");
return list.get(0);
}
}
2、自定义数据源类型
①引入自定义数据源依赖(关于数据源依赖可以查看博客:https://blog.csdn.net/fancheng614/article/details/85543816)
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
②自定义数据源
@Configuration
public class MyDataSource {
@Value("${spring.datasource.url}")
String url;
@Value("${spring.datasource.username}")
String username;
@Value("${spring.datasource.password}")
String password;
@Value("${spring.datasource.driver-class-name}")
String driverClassName;
@Bean("dataSource")
public DataSource druidDataSource(StandardEnvironment env) throws Exception {
Properties properties = new Properties();
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass(driverClassName);
dataSource.setJdbcUrl(url);
dataSource.setUser(username);
dataSource.setPassword(password);
return dataSource;
}
}
③ 在全局配置文件(application.properties)中配置数据源相关信息
spring.datasource.url=jdbc:mysql://localhost:3306/jdbc
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.type=com.mchange.v2.c3p0.ComboPooledDataSource
3、自动执行建表语句和插入数据语句
默认只需要将文件命名为:
schema‐*.sql、data‐*.sql
默认规则:schema.sql,schema‐all.sql;
可以使用
schema:
‐ classpath:department.sql
指定位置
sql文件放在src/main/resources目录下。
二、整合Druid数据源
使用Druid数据源可以监控数据库访问性能,Druid内置提供了一个功能强大的StatFilter插件,能够详细统计SQL的执行性能,这对于线上分析数据库访问性能有帮助。(更多Druid特性自行百度)
1、导入Druid数据源依赖
<!--引入druid数据源-->
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.8</version>
</dependency>
2、自定义数据源
@Configuration
public class MyDataSource {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druid(){
return new DruidDataSource();
}
//配置Druid的监控
//1、配置一个管理后台的Servlet,
//Druid后台监控平台:http://localhost:8081/druid/index.html
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
Map<String, String> initParams = new HashMap<>();
initParams.put("loginUsername","admin");
initParams.put("loginPassword","123456");
initParams.put("allow","");//默认就是允许所有访问
initParams.put("deny","127.0.0.1");
bean.setInitParameters(initParams);
return bean;
}
//2、配置一个web监控的filter
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
Map<String,String> initParams = new HashMap<>();
initParams.put("exclusions","*.js,*.css,/druid/*");
bean.setInitParameters(initParams);
bean.setUrlPatterns(Arrays.asList("/*"));
return bean;
}
}
3、在全局配置文件(application.properties)中配置数据源相关信息(还可以配置更多信息)
spring.datasource.url=jdbc:mysql://localhost:3306/jdbc
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.type=com.mchange.v2.c3p0.ComboPooledDataSource
Druid数据源监控访问:http://localhost:8081/druid/index.html
三、整合Mybatis
1、注解版
①使用IDEA创建Web项目时选中Mybatis、JDBC、MySQL、Web模块启动器
②创建实体类DepartmentDto.java
public class DepartmentDto {
private Integer id;
private String departmentName;
public void setId(Integer id) {
this.id = id;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
public Integer getId() {
return id;
}
public String getDepartmentName() {
return departmentName;
}
}
③使用上述的Druid数据源,配置数据源
④(可选)自定义Mybatis的配置规则;给容器中添加一个ConfigurationCustomizer
@Configuration
public class MyBatisConfig {
@Bean
public ConfigurationCustomizer configurationCustomizer(){
return new ConfigurationCustomizer() {
@Override
public void customize(org.apache.ibatis.session.Configuration configuration) {
configuration.setMapUnderscoreToCamelCase(true);
}
};
}
}
⑤编写操作数据库的Mapper
/*
这里可以在每一个Mapper上添加@Mapper
或者使用MapperScan批量扫描所有的Mapper接口;
@MapperScan(value = "com.example.springbootmybatis.mapper")@MapperScan将接口扫描装配到容器中
*/
@Mapper
public interface DepartmentMapper {
@Select("select * from department where id=#{id}")
public DepartmentDto getDeptById(Integer id);
@Delete("delete from department where id=#{id}")
public int deleteDeptById(Integer id);
@Options(useGeneratedKeys = true,keyProperty = "id")
@Insert("insert into department(departmentName) values(#{departmentName})")
public int insertDept(DepartmentDto department);
@Update("update department set departmentName=#{departmentName} where id=#{id}")
public int updateDept(DepartmentDto department);
}
⑥编写测试Controller
@Controller
public class DepartmentController {
@Autowired
private DepartmentMapper departmentMapper;
@ResponseBody
@GetMapping("/dept/{id}")
public DepartmentDto getDepartment(@PathVariable("id") Integer id){
DepartmentDto departmentDto = departmentMapper.getDeptById(id);
System.out.println("=========>"+departmentDto.getDepartmentName());
return departmentDto;
}
@ResponseBody
@GetMapping("/dept")
public DepartmentDto insertDept(DepartmentDto department){
departmentMapper.insertDept(department);
return department;
}
}
2、配置文件版
①使用IDEA创建Web项目时选中Mybatis、JDBC、MySQL、Web模块启动器
②创建实体类EmployeeDto.java
public class EmployeeDto {
private Integer id;
private String lastName;
private Integer gender;
private String email;
private Integer dId;
// set && GET
}
③使用上述的Druid数据源,配置数据源
④编写操作数据库的Mapper
这里没有使用@Mapper注解,而是在SpringBootMybatisApplication.java 上面加了@MapperScan(value = "com.example.springbootmybatis.mapper")注解。
public interface EmployeeMapper {
public EmployeeDto getEmpById(Integer id);
public void insertEmp(EmployeeDto employee);
}
//使用MapperScan批量扫描所有的Mapper接口;
@MapperScan(value = "com.example.springbootmybatis.mapper")
@SpringBootApplication
public class SpringBootMybatisApplication {
public static void main(String[] args) {
SpringApplication.run(SpringBootMybatisApplication.class, args);
}
}
⑤在resource/mybatis/mapper文件夹下面放了一个RmployeeMapper.xml文件映射EmployeeMapper.java
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.springbootmybatis.mapper.EmployeeMapper">
<!-- public Employee getEmpById(Integer id);
public void insertEmp(Employee employee);-->
<select id="getEmpById" resultType="com.example.springbootmybatis.entity.EmployeeDto">
SELECT * FROM employee WHERE id=#{id}
</select>
<insert id="insertEmp">
INSERT INTO employee(lastName,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{dId})
</insert>
</mapper>
⑥在resource/mybatis/文件夹下放了mybatis-config.xml全局配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
</configuration>
⑦使用配置文件时注意在全局配置文件中配置上这些配置文件的路径
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# 指定全局配置文件位置
mybatis.config-location=classpath:mybatis/mybatis-config.xml
# 指定sql映射文件位置
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
⑧测试controller
@Controller
public class DepartmentController {
@Autowired
private EmployeeMapper employeeMapper;
@ResponseBody
@GetMapping("/emp/{id}")
public EmployeeDto getEmp(@PathVariable("id") Integer id){
return employeeMapper.getEmpById(id);
}
}
四、整合SpringData JPA
1、使用IDEA创建Web项目时选中JPA、JDBC、MySQL、Web模块启动器
2、使用上述的Druid数据源,配置数据源
3、创建实体类User.java
package com.example.springbootjpa.entity;
import javax.persistence.*;
@Entity
@Table(name = "tbl_user")
public class User {
// 主键
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY) // 自增主键
private Integer id;
@Column(name = "last_name", length = 50)
private String lastName;
// /省略默认列名就是属性名
@Column
private String email;
// set && get
}
4、编写一个Dao接口来操作实体类对应的数据表(Repository)
public interface UserRepository extends JpaRepository<User, Integer> {
}
5、测试Controller
@Controller
public class UserController {
@Autowired
private UserRepository userRepository;
@ResponseBody
@GetMapping("/user/{id}")
public User getUser(@PathVariable("id") Integer id){
User user = userRepository.findOne(id);
return user;
}
@GetMapping("/user")
public User insertUser(User user){
User save = userRepository.save(user);
return save;
}
}