SpringBoot整合持久层

港控/mmm° 2021-10-13 23:30 447阅读 0赞

*  整合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结果如下

![20190816155024150.png][]![20190816155031168.png][]

二、整合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][http_localhost_8080_users]，得到如下结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjY0OTk5_size_16_color_FFFFFF_t_70][]

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

插件地址：[https://chrome.google.com/webstore/detail/json-handle/iahnhfdhidomcpggpaimmmahffihkfnj][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>
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
            
            <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结果如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjY0OTk5_size_16_color_FFFFFF_t_70 1][]

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

[20190816155024150.png]: https://img-blog.csdnimg.cn/20190816155024150.png
[20190816155031168.png]: /images/20211013/44a6535ae1614abbbe157ab86fe13960.png
[http_localhost_8080_users]: http://localhost:8080/users
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjY0OTk5_size_16_color_FFFFFF_t_70]: /images/20211013/1b4d2c5e930d4d4996ed3b37faef3b40.png
[https_chrome.google.com_webstore_detail_json-handle_iahnhfdhidomcpggpaimmmahffihkfnj]: https://chrome.google.com/webstore/detail/json-handle/iahnhfdhidomcpggpaimmmahffihkfnj
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MjY0OTk5_size_16_color_FFFFFF_t_70 1]: /images/20211013/0a015a22a84949249c0e686b0d4cc94d.png