Spring Boot整合MyBatis连接数据库

  当我们项目启动起来后，就要开始整合mybatis来连接数据
数据库：

1. 目录
   
2. 在application.yml中的配置

# 数据库设置
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://xxx.xxx.xx.xx:3306/imageserver?serverTimezone=GMT%2B8
    username: root
    password: root

3. 在pom.xml中的配置

<!--mybatis连接数据库-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>5.1.3.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>
		<!--通用mapper-->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>1.2.3</version>
        </dependency>

4. 创建实体类

package com.test.entity;

public class SpringBootModel {

    private int id;
    private String name;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

5. 创建DAO层

package com.test.dao;

import com.test.entity.SpringBootModel;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface SpringBootMapper {

    @Select("select id,name from tb_spring_boot")
    List<SpringBootModel> select();

    @Insert("insert into tb_spring_boot(name) values(#{name})")
    int insert(@Param("name") String name);

}

6. 创建Service层

package com.test.service;

import com.test.dao.SpringBootMapper;
import com.test.entity.SpringBootModel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class SpringBootService {

    @Autowired
    private SpringBootMapper springBootMapper;  //这里如果有报错，不用管

    public List<SpringBootModel> select() {
        return springBootMapper.select();
    }

    public int insert(String name) {
        return springBootMapper.insert(name);
    }

}

7. 创建Controller层

package com.test.coll;

import com.test.entity.SpringBootModel;
import com.test.service.SpringBootService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@Scope("prototype")
@RequestMapping("/mysql/test")
public class SpringBootClass {
    @Autowired
    private SpringBootService springBootService;

    @GetMapping(value = "/select")
    public List<SpringBootModel> select() throws Exception {
        return springBootService.select();
    }

    @PostMapping(value = "/insert")
    public int insert(@RequestParam(value = "name") String name) throws Exception {
        return springBootService.insert(name);
    }
}

8. 在postman中测试
   
9. 但是在项目中，为了解耦合，我们一般sql不会写在DAO层，这样
   启动代码：

package com.gulicheng.user;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan(basePackages = "com.gulicheng.user.mapper")
public class AppLication {

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

application.yml中的配置：

# jdbc
spring:
  datasource:
    url: jdbc:mysql://47.xxx.xx.xx:3306/imageserver?useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: xxxxx

# mybatis
mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  configuration:
    map-underscore-to-camel-case: true

在resources下建立mapper包，并在mapper下创建*Mapper.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.gulicheng.user.mapper.UserMapper">

    <select id="selectAllUser" resultType="com.gulicheng.user.bean.UserBean">
        select t.*  from tb_spring_boot t
    </select>

    <select id="selectUserGroup" resultType="com.gulicheng.user.bean.UserGroupBean">
        select t.*  from tb_users_groups t
    </select>

</mapper>

controller层：

package com.gulicheng.user.controller;

import com.gulicheng.user.bean.UserBean;
import com.gulicheng.user.bean.UserGroupBean;
import com.gulicheng.user.server.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("user/list")
    @ResponseBody
    public List<UserBean> getAllUser(){

        List<UserBean> userBeanList = userService.getAllUser();
        return userBeanList;
    }

    @RequestMapping("user/group/list")
    @ResponseBody
    public List<UserGroupBean> getUserGroup(){

        List<UserGroupBean> userGroupBeanList = userService.getUserGroup();

        return userGroupBeanList;
    }

}

server层：

package com.gulicheng.user.server;

import com.gulicheng.user.bean.UserBean;
import com.gulicheng.user.bean.UserGroupBean;
import com.gulicheng.user.mapper.UserMapper;

import java.util.List;

public interface UserService {

    List<UserBean> getAllUser();

    List<UserGroupBean> getUserGroup();
}

package com.gulicheng.user.server.impl;

import com.gulicheng.user.bean.UserBean;
import com.gulicheng.user.bean.UserGroupBean;
import com.gulicheng.user.mapper.UserMapper;
import com.gulicheng.user.server.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<UserBean> getAllUser() {

        List<UserBean> userBeanList = userMapper.selectAllUser();
//        System.out.println(userBeanList);
        for (UserBean i: userBeanList) {
            System.out.println(i.getName());
            System.out.println(i.getId());
        }

        return userBeanList;
    }

    @Override
    public List<UserGroupBean> getUserGroup(){

        List<UserGroupBean> userGroupBeanList = userMapper.selectUserGroup();

        return userGroupBeanList;
    }
}

Dao层：

package com.gulicheng.user.mapper;

import com.gulicheng.user.bean.UserBean;
import com.gulicheng.user.bean.UserGroupBean;
import tk.mybatis.mapper.common.Mapper;


import java.util.List;

public interface UserMapper extends Mapper<UserBean> {

    List<UserBean> selectAllUser();

    List<UserGroupBean> selectUserGroup();
}

bean层：

package com.gulicheng.user.bean;

public class UserBean {

    private String id;
    private String name;

    public String getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }


}

package com.gulicheng.user.bean;

public class UserGroupBean {

    private String id;
    private String userId;
    private String GroupId;

    public String getId() {
        return id;
    }

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

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getGroupId() {
        return GroupId;
    }

    public void setGroupId(String groupId) {
        GroupId = groupId;
    }
}