SpringBoot 整合jdbc和mybatis的方法

以下是SpringBoot整合jdbc和mybatis的方法的完整攻略：

前置要求

在进行SpringBoot整合jdbc和mybatis之前，需要确保已满足以下要求：

• 已安装好JDK，并配置好环境变量。
• 已安装好Maven。
• 已安装好MySQL，创建好了相应的数据库和表，并记录下来数据库的连接信息。

建立SpringBoot项目

首先，需要新建一个SpringBoot项目，并且导入相应的依赖。

在pom.xml文件中添加以下依赖：

<dependencies>
    <!--SpringBoot依赖-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!--MyBatis依赖-->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.4</version>
    </dependency>

    <!--JDBC依赖-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

    <!--MySQL依赖-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.25</version>
    </dependency>

    <!--Log4j2日志依赖-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-log4j2</artifactId>
    </dependency>

</dependencies>

配置数据源

在application.yml文件中进行数据源的配置，如下所示：

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

配置MyBatis

在application.yml文件中进行MyBatis的配置，如下所示：

mybatis:
  # 配置mapper文件位置
  mapper-locations: classpath:/mapper/*.xml
  configuration:
    # 开启驼峰命名规则
    map-underscore-to-camel-case: true

配置Mapper

在/resources/mapper目录下，新建UserMapper.xml文件，并添加相应的Mapper语句。

UserMapper.xml文件示例代码如下：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.UserMapper">
    <select id="getUserById" resultType="User">
        select * from user where id = #{id}
    </select>
    <select id="getAllUsers" resultType="User">
        select * from user
    </select>
    <insert id="insertUser" parameterType="User">
        insert into user (name, age, sex) values (#{name}, #{age}, #{sex})
    </insert>
    <update id="updateUserById" parameterType="User">
        update user set name = #{name}, age = #{age}, sex = #{sex} where id = #{id}
    </update>
    <delete id="deleteUserById" parameterType="int">
        delete from user where id = #{id}
    </delete>
</mapper>

编写Mapper接口

在com.example.demo.mapper包下新建UserMapper接口，并添加相应的方法。

UserMapper.java文件示例代码如下：

package com.example.demo.mapper;

import com.example.demo.entity.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserMapper {
    @Select("select * from user where id = #{id}")
    User getUserById(@Param("id") int id);

    @Select("select * from user")
    List<User> getAllUsers();

    @Insert("insert into user (name, age, sex) values (#{name}, #{age}, #{sex})")
    int insertUser(User user);

    @Update("update user set name = #{name}, age = #{age}, sex = #{sex} where id = #{id}")
    int updateUserById(User user);

    @Delete("delete from user where id = #{id}")
    int deleteUserById(@Param("id") int id);
}

编写Service层

在com.example.demo.service包下新建UserService接口，和UserServiceImpl实现类，并编写相应的方法。

UserService.java示例代码如下：

package com.example.demo.service;

import com.example.demo.entity.User;

import java.util.List;

public interface UserService {
    User getUserById(int id);

    List<User> getAllUsers();

    int insertUser(User user);

    int updateUserById(User user);

    int deleteUserById(int id);
}

UserServiceImpl.java示例代码如下：

package com.example.demo.service.impl;

import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import com.example.demo.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 User getUserById(int id) {
        return userMapper.getUserById(id);
    }

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

    @Override
    public int insertUser(User user) {
        return userMapper.insertUser(user);
    }

    @Override
    public int updateUserById(User user) {
        return userMapper.updateUserById(user);
    }

    @Override
    public int deleteUserById(int id) {
        return userMapper.deleteUserById(id);
    }
}

编写Controller层

在com.example.demo.controller包下，新建UserController类，编写相应的接口：

UserController.java示例代码如下：

package com.example.demo.controller;

import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/{id}")
    public User getUserById(@PathVariable int id) {
        return userService.getUserById(id);
    }

    @GetMapping("/list")
    public List<User> getAllUsers() {
        return userService.getAllUsers();
    }

    @PostMapping("/add")
    public int insertUser(@RequestBody User user) {
        return userService.insertUser(user);
    }

    @PutMapping("/update")
    public int updateUserById(@RequestBody User user) {
        return userService.updateUserById(user);
    }

    @DeleteMapping("/{id}")
    public int deleteUserById(@PathVariable int id) {
        return userService.deleteUserById(id);
    }
}

测试代码

最后，在src/test/java目录下，新建一个UserMapperTest类，进行相应的测试。

UserMapperTest.java示例代码如下：

package com.example.demo;

import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    void testGetUserById() {
        User user = userMapper.getUserById(1);
        System.out.println(user);
    }

    @Test
    void testGetAllUsers() {
        System.out.println(userMapper.getAllUsers());
    }

    @Test
    void testInsertUser() {
        User user = new User();
        user.setName("张三");
        user.setAge(20);
        user.setSex("男");
        System.out.println(userMapper.insertUser(user));
    }

    @Test
    void testUpdateUserById() {
        User user = new User();
        user.setId(1);
        user.setName("李四");
        user.setAge(22);
        user.setSex("男");
        System.out.println(userMapper.updateUserById(user));
    }

    @Test
    void testDeleteUserById() {
        System.out.println(userMapper.deleteUserById(1));
    }
}

以上就是 SpringBoot 整合 jdbc 和 MyBatis 的完整攻略，下面是两个使用示例：

示例 1：用 MyBatis 查询一条数据

通过调用mapper接口中的方法，用MyBatis查询一条数据。

UserController.java示例代码如下：

package com.example.demo.controller;

import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @GetMapping("/{id}")
    public User getUserById(@PathVariable int id) {
        return userMapper.getUserById(id);
    }
}

示例 2：用jdbc查询所有数据

通过调用JdbcTemplate实例的query方法，用jdbc查询所有数据。

UserController.java示例代码如下：

package com.example.demo.controller;

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

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/list")
    public List<Map<String, Object>> getAllUsers() {
        String sql = "SELECT * FROM user";
        List<Map<String, Object>> users = jdbcTemplate.queryForList(sql);
        return users;
    }
}