有勇气的牛排博客
☰

SpringBoot (三) 整合数据库访问 jdbcTemplate、MyBatis

有勇气的牛排 509 Java 2023-02-25 12:21:36

哈喽，大家好，我是有勇气的牛排（全网同名）🐮🐮🐮

有问题的小伙伴欢迎在文末评论，点赞、收藏是对我最大的支持！！！。

文章目录

Spring Data了解下：

Spring Data官网：https://docs.spring.io/spring-data/jpa/docs/current/reference/html/

Spring Data主要为数据访问提供一个相似的、一致的、基于Spring的编程模型，同时保留各个数据库的存储特征，这使得数据访问技术变得非常简单。

常用的整合数据模型有：Jdbc、MyBatis、durid

1 准备数据库

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL COMMENT '用户名称',
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

insert into users values (null, '有勇气的牛排1', '18');
insert into users values (null, '有勇气的牛排2', '19');
insert into users values (null, '有勇气的牛排3', '20');

image.png

2 整合 JdbcTemplate

JdbcTemplate类是Spring对JDBC支持的核心，它提供了对数据库所有功能操作的支持。

2.1 pom依赖

<!-- SpringBoot整合jdbc 模板框架 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- SpringBoot整合mysql驱动类 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.21</version>
</dependency>

2.2 application.yml

spring:
  # 整合 JdbcTemplate
  datasource:
    url: jdbc:mysql://localhost:3306/cs_test_springboot
    username: root
    password: root123456
    driver-class-name: com.mysql.jdbc.Driver

2.3 后端

/*
 * @Author  : 有勇气的牛排
 * @FileName: UserService_jdbcTemplate.java
 * desc     :
 * */

package com.couragesteak.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserService_jdbcTemplate {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /*
     * 插入数据到user表
     * */
    // http://127.0.0.1:8080/insertUser?userName=cs666&age=21
    @RequestMapping("/insertUser")
    public String insertUser(String userName, Integer age) {
        int update = jdbcTemplate.update("insert into users values (null, ?, ?)", userName, age);
        return update > 0 ? "success" : "fail";
    }
}

3 整合MyBatis

MyBatis是一个优秀的持久层框架，它对jdbc的操作数据库的过程进行了封装，使开发者只需要关注SQL本身，而不需要花费精力去处理注册驱动、创建connection、创建statement、手动设置参数等操作。

3.1 Maven依赖

<!-- springboot 整合mybatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.1.1</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.21</version>
</dependency>

3.2 application.yml

<!-- springboot 整合mybatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.1.1</version>
</dependency>
<!-- SpringBoot整合mysql驱动类 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.21</version>
</dependency>

3.3 用户实体 UserEntity.java

/*
 * @Author  : 有勇气的牛排
 * @FileName: UserEntity.java
 * desc     :
 * */

package com.couragesteak.entity;

public class UserEntity {
    private Integer id;
    private String userName;
    private Integer age;


    public UserEntity() {
    }

    public UserEntity(String userName, Integer age) {
        this.userName = userName;
        this.age = age;
    }

    public UserEntity(Integer id, String userName, Integer age) {
        this.id = id;
        this.userName = userName;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

3.4 Mapper

接口: UserMapper.java

package com.couragesteak.mapper;


import com.couragesteak.entity.UserEntity;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface UserMapper {

    @Insert("insert into users values (null, #{userName}, #{age});")
    int insertUser(@Param("userName") String userName, @Param("age") Integer age);

    @Select("select id, name, age from users where id=#{id}")
    UserEntity selectByUserId(@Param("id") Integer id);
}

3.5 后端

/*
 * @Author  : 有勇气的牛排
 * @FileName: UserService_MyBatis.java
 * desc     :
 * */

package com.couragesteak.service;

import com.couragesteak.entity.UserEntity;
import com.couragesteak.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserService_MyBatis {

    @Autowired
    private UserMapper userMapper;

    /**
     * MyBatis 查询数据
     */
    // http://127.0.0.1:8080/mybatis_findUser?id=3
    @RequestMapping("/mybatis_findUser")
    public UserEntity mybatis_FindUserById(Integer id) {
        System.out.println("======mybatis_FindUserById=======");
        System.out.println(id);
        return userMapper.selectByUserId(id);
    }

    /**
     * MyBatis 插入数据
     */
   // http://127.0.0.1:8080/mybatis_insertUser?userName=cs7&age=22
    @RequestMapping("/mybatis_insertUser")
    public String mybatis_insertUser(String userName, Integer age) {
        int insert = userMapper.insertUser(userName, age);
        return insert > 0 ? "success" : "fail";
    }
}

image.png

4 MySQL链接配置

4.1 驱动类 driver-classs-name

spring boot 2.0 (内置jdbc5驱动)，驱动类使用：

driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/mybatis_plus?characterEncoding=utf-8&useSSL=false

spring boot 2.1+ （内置jdbc8驱动）

driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/mybatis_plus?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false

否则运行测试用例报告如下错误：
java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more

参考地址：

https://www.couragesteak.com/article/289

  • 余胜军
  • 尚硅谷

留言

专栏
文章
加入群聊
程序员兼职接单交流
编程交流