06_SpringBoot与数据访问

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>

步骤 :

1. 配置数据源的相关属性

2. 数据库建表

3. 建立Javabean

   4-1. 注解版

1. . 配置数据源

spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql:///mybatis
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

2. . 数据库建表

3. . 建立 Javabean

public class User {
    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;
    //省略 get set 方法
}

4. . 注解版

/**
 * 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);

}

5. . 测试

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 : 配置文件版

1. . 编写 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>

2. . 编写 UserMapper.java 接口

package com.lfy.springboot.mapper;

import com.lfy.springboot.bean.User;

public interface UserMapper {

    public User findUserById(Integer id);

}

3. . 编写 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>

4. . 在 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

5. . 编写 Controller

@RestController
public class MybatisController {
    @Autowired
    private UserMapper userMapper;

    @GetMapping("/user/{id}")
    public User findUserById(@PathVariable("id") Integer id){
        return userMapper.findUserById(id);
    }
}

6. . 浏览器测试
   

问题 : 连接 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

步骤 :

1. 编写实体类 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 方法
}

2. 编写DAO接口操作数据库

//第一个泛型是操作哪个类型
//第二个泛型是主键的类型
public interface UserRepository extends JpaRepository<User,Integer> {
}

3. 测试

@RestController
public class UserController {
    @Autowired
    private UserRepository userRepository;
    @GetMapping("/user/{id}")
    public User getUserById(@PathVariable("id") Integer id){
        return userRepository.getOne(id);
    }
}