十一、数据访问
1、整合JDBC
首先使用idea的向导化创建一个工程,选择我们需要的功能模块
在resources文件下创建一个application.yml配置文件链接上MySQL数据库
spring:
datasource:
username: root
password: xxxxxx
url: jdbc:mysql://192.168.3.158:3306/jdbc
driver-class-name: com.mysql.jdbc.Driver
2、DataSourceInitializer
DataSourceInitializer是ApplicationListener的一个继承类
作用
- runSchemaScripts();运行建表语句
- runDataScripts();运行插入数据的sql语句
使用方法
默认规则:schema.sql,schema-all.sql;
可以使用
schema:
- classpath:department.sql
指定位置
3、小案例
首先在resources文件下面创建一个.sql文件
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`departmentName` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
将.sql文件路径写在.yml文件中
schema:
- classpath: department.sql
测试代码:
@Controller
public class HelloController {
@Autowired
JdbcTemplate jdbcTemplate;
@ResponseBody
@GetMapping("/query")
public Map<String, Object> map(){
List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from department");
return maps.get(0);
}
}
向/query发送一个get请求,然后去到localhost:8080/页面就可以看到一个json格式的数据显示出来。
4、整合Druid数据源
<1>、配置属性
在pom文件中引入Druid的依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.8</version>
</dependency>
在yml文件中将数据源地址改成Druid
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
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druid(){
return new DruidDataSource();
}
}
<2>、配置Druid的监控
//配置Druid的监控
//1、配置管理后台的Servlet
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
Map<String, String> initParams = new HashMap<>();
initParams.put("loginUsername", "Tom");
initParams.put("loginPassword", "xxxxxx");
initParams.put("allow", ""); //默认是允许所有访问
initParams.put("deny", "192.168.3.158");
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;
}
重新运行打开localhost:8080/druid会自动跳转到login界面,进行登录之后就可以看到监控后台的界面。
5、整合MyBatis
<1>、搭建实验环境
新建工程,添加MyBatis依赖,再添加Druid依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.8</version>
</dependency>
重新创建yml文件,连上数据库,配置Druid,书写Druid的config类进行配置,可以参考上面
sql文件的路径
schema:
- classpath: sql/department.sql
- classpath: sql/Employee.sql
<2>、封装表数据
Department:
public class Department {
private Integer id;
private String departmentName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Department{" +
"id=" + id +
", departmentName='" + departmentName + '\'' +
'}';
}
}
Employee:
public class Employee {
private Integer id;
private String lastName;
private String email;
private Integer gender;
private Integer dId;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public Integer getdId() {
return dId;
}
public void setdId(Integer dId) {
this.dId = dId;
}
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", lastName='" + lastName + '\'' +
", email='" + email + '\'' +
", gender=" + gender +
", dId=" + dId +
'}';
}
}
<3>、基于注解来用MyBatis实现CRUD操作
(1)、创建表的接口
public interface DepartmentMapper {
}
//@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);
@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);
}
(2)、向页面发送请求,执行请求对应数据
@RestController
public class DeptController {
@Autowired
DepartmentMapper departmentMapper;
@GetMapping("/dept/{id}")
public Department getDept(@PathVariable("id") Integer id){
return departmentMapper.getDeptById(id);
}
@GetMapping("/dept")
public Department insertDept(Department department){
departmentMapper.insertDept(department);
return department;
}
}
然后运行来到对应页面测试即可(记得创建表)
localhost:8080/dept/1
localhost:8080/dept?departmentName=tom
如果我们想要获取到自增主键,只要在对应的语句前设置一个Option注解即可
@Options(useGeneratedKeys = true, keyProperty = "id")
@Insert("insert into department(departmentName) values(#{departmentName})")
public int insertDept(Department department);
(3)、自定义MyBatis的Config
@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.example.springboot.mapper") //批量扫描所有的Mapper接口
<4>、基于配置文件来用MyBatis实现CRUD操作
(1)、创建接口
public interface EmployeeMapper {
Employee getEmpById(Integer id);
void insertEmp(Employee employee);
}
(2)、编写配置文件
<?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>
</configuration>
(3)、SQL映射文件
<?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.springboot.mapper.EmployeeMapper">
<!-- public Employee getEmpById(Integer id);
public void insertEmp(Employee employee);-->
<select id="getEmpById" resultType="com.example.springboot.bean.Employee">
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>
(4)、MyBatis检索配置文件
@Autowired
EmployeeMapper employeeMapper;
@GetMapping(value = "/emp/{id}")
public Employee getEmp(@PathVariable("id") Integer id){
return employeeMapper.getEmpById(id);
}
(5)、在配置文件中配置自己想要实现的功能
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
6、整合SpringData JPA
<1>、SpringData JPA简介
概念
SpringData项目目的是为了简化构建基于Spring框架应用的数据访问技术,包括非关系型数据库,MapReduce框架,云服务等等;另外也包含对关系型数据库的访问支持
特点
SpringData提供统一的API来对数据访问层进行操作;这主要是SpringDataCommons项目来实现的。SpringDataCommons让我们在使用sql,nosql的时候都有基于spring的统一标准,标准包括crud等相关操作。
同一接口
Repository<T,ID extends Serializable>:统一接口
RevisionRepository<T,ID extends Serializable,N extends Number & Comparable>:基于乐观锁机制
乐观锁机制:
认为数据一般情况下不会造成冲突,所以在数据进行提交更新时,才会正式对数据的冲突与否进行检测,如果发现冲突了,则让用户返回错误的信息,让用户决定如何去做。
CrudRepository<T,ID extends Serializable>:基本Crud操作
PagingAndSortingRepository<T,ID extends Serializable>:基本CRUD及分页
提供数据访问模板xxxTemplate
就像JDBCTemplate…
JPA与SpringData
<2>、SpringData JPA
(1)JPARepository基本功能
(2)定义符合规范方法命名
(3)@Query自定义查询,定制查询Sql
(4)Specifications查询(SpringDataJPA支持JPA2.0 的Criteria查询)
<3>、创建适合的环境
创建application.yml进行数据库连接
spring:
datasource:
url: jdbc:mysql://192.168.3.158/jpa
username: root
password: xxxxxx
driver-class-name: com.mysql.jdbc.Driver
<4>、测试实例
(1)、创建一个带有几个私有属性的类,自动生成get、set方法
使用JPA注解配置映射关系,标记主键属性,列属性,和其他属性
//使用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;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
(2)、编写一个Dao接口来操作实体类对应的数据表(Repository)
//继承JpaRepository完成对数据库的操作
public interface UserRepository extends JpaRepository<User, Integer> {
}
(3)、基本的配置JpaProperties
jpa:
hibernate:
# 更新或者创建数据表结构
ddl-auto: update
# 控制台显示SQL
show-sql: true
运行程序,看到表已经创建出来。
(4)、CRUD操作
package com.example.springboot.controller;
import com.example.springboot.entity.User;
import com.example.springboot.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class UserController {
@Autowired
UserRepository userRepository;
@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;
}
}