SpringBoot整合持久层

• 整合JdbcTemplate
• 整合Mybatis
• 整合SpringDataJpa

一、整合JdbcTemplate

       JdbcTemplate类是JDBC核心包中的中心类。 它简化了JDBC的使用，有助于避免常见错误。它执行核心JDBC工作流，使应用程序代码提供SQL并提取结果。此类执行SQL查询或更新，启动对ResultSet的迭代并捕获JDBC异常并将它们转换为org.springframework.dao包中定义的通用的，更具信息性的异常层次结构 。

1.1、添加依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>cn.waggag.springboot</groupId>
    <artifactId>SpringBoot_Persistent</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>war</packaging>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.7.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <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>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.19</version>
        </dependency>
    </dependencies>
</project>

1.2、数据库配置与SpringBoot启动类

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql://localhost/springboot?serverTimezone=UTC
spring.datasource.username=waggag
spring.datasource.password=225514

//SpringBoot启动类
package cn.waggag;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class,args);
    }

}

1.3、创建用户表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'zhangsan', '123', '张三');
INSERT INTO `user` VALUES (2, 'lisi', '123', '李四');

SET FOREIGN_KEY_CHECKS = 1;

1.4、创建实体类

package cn.waggag.pojo;


public class User {
    private int id;
    private String username;
    private String password;
    private String name;

    //getter和setter
}

1.5、创建数据库访问层

package cn.waggag.dao;

import cn.waggag.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<User> getAllUsers(){
        String sql = "select * from user";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
    }

}

1.6、创建Service层和Controller层

//Service层
package cn.waggag.service.impl;

import cn.waggag.dao.UserDao;
import cn.waggag.pojo.User;
import cn.waggag.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;
    @Override
    public List<User> findAllUsers() {
        return userDao.getAllUsers();
    }
}

//Controller层
package cn.waggag.controller;

import cn.waggag.pojo.User;
import cn.waggag.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/users")
    public void users(){
        List<User> userList = userService.findAllUsers();
        for (User user : userList) {
            System.out.println(user);
        }
    }
}

1.7、启动SpringBoot，访问localhost:8080/users结果如下

二、整合Mybatis

        MyBatis是一个Java持久化框架，它通过XML描述符或注解把对象与存储过程或SQL语句关联起来。 MyBatis是在Apache许可证 2.0下分发的自由软件，是iBATIS 3.0的分支版本。其维护团队也包含iBATIS的初创成员。

2.1、导入依赖

    <dependencies>
        <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.1.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.19</version>
        </dependency>
    </dependencies>

2.2、数据库、实体类、配置的数据库连接信息与上一节相同。

2.3、创建数据库访问层

package cn.waggag.mapper;

import cn.waggag.domain.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;

@Mapper
public interface UserMapper {
    List<User> queryList();
}

2.4、创建Service和Controller

//Service
package cn.waggag.service.impl;

import cn.waggag.domain.User;
import cn.waggag.mapper.UserMapper;
import cn.waggag.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    @Override
    public List<User> findUsers() {
        return userMapper.queryList();
    }
}

//Controller
package cn.waggag.controller;

import cn.waggag.mapper.UserMapper;
import cn.waggag.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.List;

@Controller
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @RequestMapping("/query")
    @ResponseBody
    public List<User> queryList(){
        List<User> userList = userMapper.queryList();
        return userList;
    }
}

2.5、访问http://localhost:8080/users，得到如下结果

注意：我的谷歌浏览器由于安装了Json-handle插件，返回的Json数据会被自动解析，所以显示的是上面的页面。

插件地址：https://chrome.google.com/webstore/detail/json-handle/iahnhfdhidomcpggpaimmmahffihkfnj，有需要的也可以下载尝试下

三、整合SpringDataJpa

      Jpa(Java Persistence API)和SpringData是两个范畴的概念。

      JPA的全称是Java Persistence API， 即Java 持久化API，是SUN公司推出的一套基于ORM的规范，内部是由一系列的接口和抽象类构成。Hibernate是一个ORM框架，而JPA是一个ORM规范。JPA和Hibernate的关系就像JDBC和JDBC驱动的关系，JPA是规范，Hibernate除了作为ORM框架之外，它也是一种JPA实现。

      Spring Data的任务是为数据访问提供一个熟悉的、一致的、基于Spring的编程模型，同时仍然保留底层数据存储的特殊特性。SpringDataJpa可以有效简化关系型数据库访问代码。

3.1、创建数据库JPA

 create database jpa default character set utf8;

3.2、 添加依赖

   <dependencies>
        <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>
        <!-- springBoot JPA的依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <!-- MySQL连接驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--使用lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

3.3、数据库配置

#DB Configuration:
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/jpa?serverTimezone=UTC
spring.datasource.username=waggag
spring.datasource.password=225514

#JPA Configuration:
spring.jpa.database=MySQL
spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy

3.4、创建实体类

package cn.waggag.pojo;

import lombok.Data;

import javax.persistence.*;

@Entity(name = "user")
@Data //自动添加setter和getter方法，依赖lombok
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "name",nullable = false)
    private String name;
    private String username;
    private String password;

    public User(String name,String username, String password) {
        this.name = name;
        this.username = username;
        this.password = password;
    }
}

3.5、创建UserRepository,继承JpaRepository，JpaRepository会提供一些简单的数据操作方法。

package cn.waggag.repository;

import cn.waggag.pojo.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
@Repository
public interface UserRepository extends JpaRepository<User,Long> {
    List<User> findAll();
}

3.6、创建UserService

package cn.waggag.service.impl;

import cn.waggag.pojo.User;
import cn.waggag.repository.UserRepository;
import cn.waggag.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserRepository userRepository;

    @Override
    public List<User> findUsers() {
        return userRepository.findAll();
    }

    @Override
    public void addUser(User user) {
        userRepository.save(user);
    }
}

3.7、创建UserCOntroller

package cn.waggag.controller;

import cn.waggag.pojo.User;
import cn.waggag.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.List;

@Controller
public class UserController {
    @Autowired
    private UserService userService;

    @RequestMapping("/users")
    @ResponseBody
    public List<User> findAll(){
        //添加一个用户
        userService.addUser(new User("name","username","password"));
        //查询所有的用户
        List<User> userList = userService.findUsers();
        return userList;
    }
}

3.8、访问localhost:8080结果如下：

注意：可以直接通过Controller使用UserRepository,为了规范我添加了service层的实现，想简单点的可以直接测试Repository。