SpringBoot与数据访问
JDBC、MyBatis、Spring Data JPA
Spring Boot底层是使用的Spring Data作为数据访问的默认处理方式。
1. 整合基本JDBC与数据源
Pom依赖
<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.yml
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://localhost:3306/jdbc
driver-class-name: com.mysql.jdbc.Driver
效果:
默认是用org.apache.tomcat.jdbc.pool.DataSource作为数据源;
数据源的相关配置都在DataSourceProperties里面;
有关数据源的自动配置原理:
org.springframework.boot.autoconfigure.jdbc:整个包下面都是跟数据源有关的配置
1、参考DataSourceConfiguration,根据配置创建数据源,默认使用Tomcat连接池作为数据源(org.apache.tomcat.jdbc.pool.DataSource);可以使用spring.datasource.type
指定自定义的数据源类型(C3P0、DBCP);
2、SpringBoot默认可以支持以下数据源
org.apache.tomcat.jdbc.pool.DataSource
HikariDataSource
org.apache.commons.dbcp.BasicDataSource
org.apache.commons.dbcp2.BasicDataSource
3、自定义数据源类型
/**
* Generic DataSource configuration.
*/
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type")
static class Generic {
@Bean
public DataSource dataSource(DataSourceProperties properties) {
//使用DataSourceBuilder创建数据源,利用反射创建相应type的数据源,并且绑定相关属性
return properties.initializeDataSourceBuilder().build();
}
}
4、DataSourceInitializer(ApplicationListener);
作用:
1)runSchemaScripts();初始化时运行建表语句;
2)runDataScripts();初始化时运行插入数据的sql语句;
默认只需要将文件命名为:
schema-*.sql、data-*.sql
默认规则:schema.sql,schema-all.sql;
可以使用
spring:
datasource:
schema:
- classpath:department.sql 指定位置
initialize: false 关闭初始化功能,不会运行脚本
5、操作数据库:JdbcTemplateAutoConfiguration中自动配置了JdbcTemplate操作数据库
2. 整合Druid数据源
druid:阿里的数据源产品,有成套的安全、监控解决方案
2.1 切换为Druid数据源
引入druid依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.8</version>
</dependency>
切换为自定义的数据源(application.yml)
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
2.2 druid数据源属性的设置
application.yml
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://localhost:3306/jdbc
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
# schema:
# - classpath:abc.sql
# 数据源其他配置
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
application.yml中虽然添加了配置,但默认是不会生效的,需要手动创建以下配置类进行设置
@Configuration
public class DruidConfig {
//这样我们自己添加的数据源配置就会生效了
//把我们自己配置的spring.datasource开头的属性和我们返回的DataSource这个Bean绑定起来
@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","");//默认(不写或者为null)就是允许所有访问
initParams.put("deny","192.168.15.21");//拒绝谁来访问
//设定StatViewServlet的初始化参数
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. 整合MyBatis
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
步骤:
1)配置数据源相关属性(见上一节Druid)
2)给数据库建表
3)创建JavaBean
3.1 注解版
//指定这是一个操作数据库的mapper
@Mapper
public interface DepartmentMapper {
@Select("select * from department where id=#{id}")
public Department getDeptById(Integer id);
@Delete("delete from department where id=#{id}")
public int deleteDeptById(Integer id);
//插入后会将自增生成的主键封装到department中
@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);
}
问题:
自定义MyBatis的配置规则;给容器中添加一个ConfigurationCustomizer;
@org.springframework.context.annotation.Configuration
public class MyBatisConfig {
@Bean
public ConfigurationCustomizer configurationCustomizer(){
return new ConfigurationCustomizer(){
@Override
public void customize(Configuration configuration) {
//开启数据库驼峰命名(数据库 d_id JavaBean dId)
configuration.setMapUnderscoreToCamelCase(true);
}
};
}
}
//使用@MapperScan批量扫描所有的Mapper接口;这样做的好处是不需要在每个Mapper上标注@Mapper
//@MapperScan可以标注在主配置类上,也可以标注在MybatisConfig类上
@MapperScan(value = "com.atguigu.springboot.mapper")
@SpringBootApplication
public class SpringBoot06DataMybatisApplication {
public static void main(String[] args) {
SpringApplication.run(SpringBoot06DataMybatisApplication.class, args);
}
}
3.2 配置文件版
创建Mapper
package com.atguigu.mapper;
import com.atguigu.bean.Employee;
public interface EmployeeMapper {
Employee getEmployeeById(Integer id);
void insertEmployee(Employee employee);
}
创建Mybatis主配置文件和Mapper配置文件
<!--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>
<!--EmployeeMapper.xml-->
<?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.atguigu.mapper.EmployeeMapper">
<select id="getEmployeeById" resultType="com.atguigu.bean.Employee">
select * from employee where id = #{id}
</select>
<insert id="insertEmployee">
insert into employee(lastName, email, gender, d_id) values(#{lastName}, #{email}, #{gender}, #{dId)
</insert>
</mapper>
配置Mybatis主配置文件和Mapper配置文件的位置
mybatis:
config-location: classpath:mybatis/mybatis-config.xml 指定全局配置文件的位置
mapper-locations: classpath:mybatis/mapper/*.xml 指定sql映射文件的位置
更多使用参照
http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/
4. 整合SpringData JPA
4.1 SpringData简介
简化数据访问的项目
上图的Hibernate、Toplink、OpenJPA都是JPA的实现,SpringData JPA底层默认是使用Hibernate作为JPA实现的
4.2 整合SpringData JPA
- 引入依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
- 配置数据源【可选】
可以配置Druid,也可以不配置用默认的
- 使用JPA操作数据库
JPA:ORM(Object Relational Mapping);
1)、编写一个实体类(bean)和数据表进行映射,并且配置好映射关系(JPA注解);
//使用JPA注解配置映射关系
@Entity //告诉JPA这是一个实体类(和数据表映射的类,不是一个普通的JavaBean)
@Table(name = "tbl_user") //@Table来指定和哪个数据表对应;如果省略默认表名就是user(类名小写);
public class User {
@Id //标注这是一个主键
@GeneratedValue(strategy = GenerationType.IDENTITY)//strategy = GenerationType.IDENTITY:标注主键的生成策略为自增主键
private Integer id;
@Column(name = "last_name",length = 50) //标注这是和数据表对应的一个列
private String lastName;
@Column //省略,默认列名就是属性名
private String email;
//以下省略get、set方法
......
2)、编写一个Dao接口(SpringData中称为Repository)来操作实体类对应的数据表
//继承JpaRepository来完成对数据库的操作,Integer 主键类型
public interface UserRepository extends JpaRepository<User,Integer> {
}
3)、基本的配置JpaProperties
spring:
jpa:
hibernate:
# 数据表的生成策略:更新或者创建数据表结构
ddl-auto: update
# 控制台显示SQL
show-sql: true
标签:SpringBoot,spring,1x,配置,public,访问,数据源,org,id
From: https://www.cnblogs.com/wzzzj/p/18039114