六、SpringBoot与数据访问
1、简介
SpringBoot默认采用整合SpringData的方式进行统一处理,添加大量自动配置,屏蔽了很多设置。引入各种xxxTemplate,xxxRepository来简化我们对数据访问层的操作。对我们来说只需要进行简单的设置即可。
- JDBC
- MyBatis
- JPA
2、JDBC
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
连接数据库配置
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://127.0.0.1:3306/jdbc?serverTimezone=GMT&userSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
#运行sql创建表,指定位置
schema:
- classpath:department.sql
initialization-mode: always
效果:
默认使用com.zaxxer.hikari.HikariDataSource
数据源的相关配置都在DataSourceProperties里面
操作数据库
自动配置了jsbcTemplate操作数据库
3、整合Druid&配置数据源监控
配置duird步骤:
-
引入依赖包
<!-- https://mvnrepository.com/artifact/com.alibaba/druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.22</version> </dependency>
-
配置application.yaml:
spring: datasource: # 数据源基本配置 username: root password: 123456 driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMT&userSSL=false type: com.alibaba.druid.pool.DruidDataSource # 数据源其他配置 initialSize: 5 minIdle: 5 maxActive: 20 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙 # filters: stat,wall,log4j maxPoolPreparedStatementPerConnectionSize: 20 useGlobalDataSourceStat: true connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
-
在configuration中绑定Servlet和Filter:
@Configuration public class DruidConfig { @ConfigurationProperties(prefix = "spring.datasource") @Bean public DataSource druid(){ return new DruidDataSource(); } //配置Druid的监控 //1.配置一个管理后台的servlet @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","");//默认所有ip访问 //initParams.put("deny",""); bean.setInitParameters(initParams); return bean; } //2.配置一个监控的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; } }
4、整合Mybatis
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
步骤:
-
配置数据源相关属性(见上一节Druid)
-
给数据库建表
-
创建响应的JavaBean
-
注解版使用
//指定这是一个操作数据库的mapper @Mapper public interface DepartmentMapper { @Select("select * from department where id=#{id}") public Department getDeptById(Integer id); @Delete("deete from department where id=#{id}") public int deleteDeptById(Integer id); //options获取自增id,使用自动生成的主键,id是封装主键的 @Options(useGeneratedKeys = true,keyProperty = "id") @Insert("insert into department(departmentName) values(#{departmentName}) ") public int insertDept(Department department); @Update("update department set departmentName=#{departmentName} where id=#{id}") public int UpdateDept(Department department); }
在controller中实现即可:
//@RestController:Responsebody和Controller的结合
@RestController
public class DeptController {
@Autowired
DepartmentMapper departmentMapper;
@GetMapping("/dept/{id}")
public Department getDepartment(@PathVariable("id") Integer id){
return departmentMapper.getDeptById(id);
}
@GetMapping("/dept")
public Department InsertDepte(Department department){
departmentMapper.insertDept(department);
return department;
}
}
问题:
自定义MyBatis的配置规则;给容器中添加一个ConfigurationCustomizer
@org.springframework.context.annotation.Configuration
public class MyBatisConfig {
@Bean
public ConfigurationCustomizer configurationCustomizer(){
return new ConfigurationCustomizer() {
@Override
public void customize(Configuration configuration) {
//开启驼峰命名法映射规则
configuration.setMapUnderscoreToCamelCase(true);
}
};
}
}
使用MapperScan批量扫描所有的mapper接口
@MapperScan(value="com.squash.springboot.mapper")
- 配置文件版使用
指定全局配置文件位置&指定sql映射文件的位置
mybatis:
config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mapper/*.xml
5、整合SpringData JPA
1、SpringData简介
1、SpringData的特点
SpringData让我们在使用关系型或者非关系型数据访问技术时都基于Spring提供的统一标准,标准包含了CRUD(创建、获取、更新、删除)、查询、排序和分页的相关操作。
2、统一的Repository接口
3、提供数据访问模板类xxxTemplate
4、JPA与SpringData
- 引入spring-boot-starter-data-jpa
- 配置文件打印sql语句
- 创建entity标注jpa注解
- 创建repository接口继承JpaRepository
- 测试方法
2、整合SpringData JPA
JPA:ORM(Object Relational Mapping);
-
编写一个实体类(bean)和数据表进行映射,并且配置好映射关系
//使用jpa注解配置映射关系 @Entity //告诉JPA这是一个实体类(和数据表映射的类) @Table(name="tbl_user") //@Table来指定和哪个数据表对应;如果省略默认表名就是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;
-
编写一个Dao接口来操作实体类对应的数据表(Repository)
//继承JpaRepository来完成对数据库的操作 public interface UserRepository extends JpaRepository<User,Integer> { }
-
基本的配置JpaProperties
spring: jpa: hibernate: #更新或者创建数据表 ddl-auto: update #控制台显示sql show-sql: true