SpringBoot与数据访问
简介
对于数据访问层,无论是 SQL 还是 NOSQL , SpringBoot 默认采用整合 SpringData 的方式进行统一的处理,添加大量自动配置,屏蔽了很多设置.引入各种 xxxTemplate, xxxRepository 来简化我们对数据访问层的操作, 对我们来说只需要进行简单的设置即可.
整合 JDBC
pom 文件 :
<!-- jdbc 启动器 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 引入 mysql 驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
application.yml
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://192.168.48.133/jdbc
driver-class-name: com.mysql.jdbc.Driver
SpringBoot自动创建数据源 :
@RunWith(SpringRunner.class)
@SpringBootTest
public class Springboot08JdbcApplicationTests {
@Autowired
private DataSource dataSource;
@Test
public void contextLoads() throws SQLException {
System.out.println(dataSource.getClass());
Connection connection = dataSource.getConnection();
System.out.println(connection);
connection.close();
}
}
// 默认使用 HikariDataSource 作为数据源
// class com.zaxxer.hikari.HikariDataSource
// HikariProxyConnection@70165127 wrapping com.mysql.cj.jdbc.ConnectionImpl@78cd163b
SpringBoot 自动封装了 JdbcTemplate :
HelloController :
@Controller
public class HelloController {
@Autowired
private JdbcTemplate jdbcTemplate;
@RequestMapping("/hello")
@ResponseBody
public List<Map<String, Object>> hello(){
List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from department");
return list;
}
}
测试 :
整合 Mybatis
pom :
<dependencies>
<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>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
步骤 :
-
配置数据源的相关属性
-
数据库建表
-
建立Javabean
4-1. 注解版
- . 配置数据源
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql:///mybatis
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
- . 数据库建表
- . 建立 Javabean
public class User {
private Integer id;
private String username;
private String address;
private String sex;
private Date birthday;
//省略 get set 方法
}
- . 注解版
/**
* mapper接口: 注解版
*/
//指定这是一个操作数据库的 mapper
//也可以在主配置类中加入 @MapperScan 批量扫描 mapper
//@Mapper
public interface UserMapper {
@Select("select * from user where id = #{id}")
public User getUserById(Integer id);
@Options(useGeneratedKeys = true,keyProperty = "id")
@Insert("insert user (id,username,address,sex,birthday) values (#{id},#{username},#{address},#{sex},#{birthday})")
public Integer saveUser(User user);
@Update("update user set username = #{username},address = #{address},sex = #{sex},birthday = #{birthday} where id = #{id}")
public Integer updateUser(User user);
@Delete("delete from user where id = #{id}")
public Integer deleteUser(Integer id);
}
- . 测试
Controller
@RestController
public class MybatisControlelr {
@Autowired
private UserMapper userMapper;
@GetMapping("/user/{id}")
public User findUserById(@PathVariable("id") Integer id){
return userMapper.getUserById(id);
}
@GetMapping("/user")
public Integer saveUser(User user){
return userMapper.saveUser(user);
}
@DeleteMapping("/user/{id}")
public Integer deleteUser(Integer id){
return userMapper.deleteUser(id);
}
}
浏览器测试 :
增 :
查 :
4-2 : 配置文件版
- . 编写 mybatis 的配置文件
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DOD Config 3.0//EN"
"http://mybatis.org/schema/mybatis-3-config.dtd">
<configuration>
</configuration>
- . 编写 UserMapper.java 接口
package com.lfy.springboot.mapper;
import com.lfy.springboot.bean.User;
public interface UserMapper {
public User findUserById(Integer id);
}
- . 编写 UserMapper.xml 文件
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DOD Config 3.0//EN"
"http://mybatis.org/schema/mybatis-3-mapper.dtd">
<mapper namespace="com.lfy.springboot.mapper.UserMapper">
<select id="findUserById" parameterType="int" resultType="com.lfy.springboot.bean.User">
select * from user where id = #{id}
</select>
</mapper>
- . 在 application.properties 中配置 mapper 的路径以及 mybatis 的配置文件的位置
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql:///mybatis
# mybatis配置文件的位置
mybatis.config-location=classpath:mybatis/mybatis-config.xml
# mapper文件的位置
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
- . 编写 Controller
@RestController
public class MybatisController {
@Autowired
private UserMapper userMapper;
@GetMapping("/user/{id}")
public User findUserById(@PathVariable("id") Integer id){
return userMapper.findUserById(id);
}
}
- . 浏览器测试
问题 : 连接 Mysql 报时区错误
java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
解决 :
整合 SpringDataJPA
SpringData 简介 :
整合 SpringDataJPA
步骤 :
- 编写实体类 User 并注明和表之间的关系
package com.lfy.springboot.bean;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import javax.persistence.*;
import java.io.Serializable;
import java.sql.Date;
//使用 JPA 注解指定映射关系
//告诉 JPA 这是一个实体类
@Entity
//指定与哪个表对应
@Table(name="user")
@JsonIgnoreProperties(value = { "hibernateLazyInitializer", "handler" })
public class User implements Serializable {
@Id //主键
@GeneratedValue(strategy = GenerationType.IDENTITY) //自增主键
private Integer id;
@Column(name = "username")
private String username;
@Column(name = "address")
private String address;
@Column(name = "sex")
private String sex;
@Column(name = "birthday")
private Date birthday;
//省略 getter setter 方法
}
- 编写DAO接口操作数据库
//第一个泛型是操作哪个类型
//第二个泛型是主键的类型
public interface UserRepository extends JpaRepository<User,Integer> {
}
- 测试
@RestController
public class UserController {
@Autowired
private UserRepository userRepository;
@GetMapping("/user/{id}")
public User getUserById(@PathVariable("id") Integer id){
return userRepository.getOne(id);
}
}