Spring-Boot-MyBatis-操作数据库

介绍

用小demo来进行介绍此次的操作。

MyBatis

• 易上手
• 映射为主
• 普通java映射成数据库的操作

前期准备

建立数据库

结构如下图所示，表格名字为user_table

加载依赖

pom.xml中的依赖，以及资源文件的加载xml等配置

<dependencies>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>
        <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>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>

配置文件

配置application.properties的内容如下列所示

spring.datasource.url=jdbc:mysql://localhost:3306/chapter5 #数据库连接
spring.datasource.username=root #账号
spring.datasource.password=123456 #密码
spring.datasource.tomcat.max-idle=10
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.max-wait=10000
spring.datasource.tomcat.initial-size=5

#mybatis映射文件配置
mybatis.mapper-locations=classpath:com/springboot/chapter5/mapper/*.xml
#Mybatis 扫描别名，和注解Alias联用
mybatis.type-aliases-package=com.springboot.chapter5.pojo
#配置typehandler的扫描包
mybatis.type-handlers-package=com.springboot.chapter5.typehandler
#日志配置
logging.level.root=DEBUG
logging.level.org.springframework=DEBUG
logging.level.org.org.mybatis=DEBUG

业务

pojo

编写User类，该类与数据库中表user_table类型是相互对应的。
@Alias(value = “user”) 即为了使其xml文件可以识别出来。

package com.springboot.chapter5.pojo;

import org.apache.ibatis.type.Alias;

@Alias(value = "user") //mabatis指定别名
public class User {
    private Long id = null;
    private String userName = null;
    private String note = null;
    private String sex = null;

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Long getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

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

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

}

dao与mapper

dao下的接口，分别对表格增删改查。

package com.springboot.chapter5.dao;

import com.springboot.chapter5.pojo.User;

public interface MyBatisUserDao {
    public User getUser(Long id);
    public void insertUser(User user);
    public void updateUser(User user);
    public void deleteUser(Long id);
}

mapper下的映射xml文件，将接口MyBatisUserDao需要的操作映射到xml文件中，进行操作。
dao与mapper在同一级别

• namespace="com.springboot.chapter5.dao.MyBatisUserDao"即为上述接口，进行映射
• id为映射的方法
• parameterType为传入的参数，即通过id查询，则传入long型
• resultType为查询的返回值

<?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.springboot.chapter5.dao.MyBatisUserDao"> <!--MyBatis会自动进行映射-->
    <select id="getUser" parameterType="long" resultType="user">
        select id, user_name as userName, sex, note from user_table where id = #{id}
    </select>
    <insert id="insertUser" parameterType="user"  >
        insert into user_table (id,user_Name,sex,note) value (#{id}, #{userName}, #{sex},#{note})
    </insert>
    <update id="updateUser" parameterType="user" >
        update user_table set user_Name = #{userName},sex = #{sex},note = #{note} where id = #{id}
    </update>
    <delete id="deleteUser" parameterType="long" >
        delete from user_table where id = #{id}
    </delete>
</mapper>

service与service下的impl

该接口即为业务需要实现的接口，下面会进行描述

package com.springboot.chapter5.service;

import com.springboot.chapter5.pojo.User;

public interface MyBatisUserService {
    public User getUser(Long id);
    public void insertUser(User user);
    public void updateUser(User user);
    public void deleteUser(Long id);
}

实现上述接口MyBatisUserService，并进行业务操作，通过MyBatisUserDao，对数据库进行操作，从而进行映射。

package com.springboot.chapter5.service.impl;

import com.springboot.chapter5.dao.MyBatisUserDao;
import com.springboot.chapter5.pojo.User;
import com.springboot.chapter5.service.MyBatisUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class MyBatisUserServiceImpl implements MyBatisUserService {

    @Autowired
    private MyBatisUserDao myBatisUserDao = null; //mybatis映射的接口


    @Override
    public User getUser(Long id) {
        return myBatisUserDao.getUser(id);
    }

    @Override
    public void insertUser(User user) {
        myBatisUserDao.insertUser(user);
    }

    @Override
    public void updateUser(User user) {
        myBatisUserDao.updateUser(user);
    }

    @Override
    public void deleteUser(Long id) {
        myBatisUserDao.deleteUser(id);
    }


}

controller

实现服务MyBatisUserService，并根据路径实现相应的操作。

package com.springboot.chapter5.controller;

import com.springboot.chapter5.pojo.User;
import com.springboot.chapter5.service.MyBatisUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
@RequestMapping("/mybatis")
public class MyBatisController {
    @Autowired
    private MyBatisUserService myBatisUserService = null;//连接 数据库的接口，会实例化其实现的类

    @GetMapping("/getUser")
    @ResponseBody
    public User getUser(Long id){
        return myBatisUserService.getUser(id);
    }

    @RequestMapping("/insertUser")
    @ResponseBody
    public void insertUser(User user){
        myBatisUserService.insertUser(user);
    }

    @RequestMapping("/updateUser")
    @ResponseBody
    public void updateUser(User user){
        myBatisUserService.updateUser(user);
    }

    @RequestMapping("/deleteUser")
    @ResponseBody
    public void deleteUser(Long id){
        myBatisUserService.deleteUser(id);
    }

}

SpringBootApplication

这里使用了SqlSessionFactory是spring boot自动为我们生成的，然后直接使用MapperFactoryBean来定义MyBatisUserDao接口。

package com.springboot.chapter5;

import com.springboot.chapter5.dao.MyBatisUserDao;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.mapper.MapperFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

@SpringBootApplication(scanBasePackages = {"com.springboot.chapter5"})
public class Chapter5Application {

    @Autowired
    SqlSessionFactory sqlSessionFactory = null;
    //定义一个mabatis的mapper接口
    @Bean
    public MapperFactoryBean<MyBatisUserDao> initMyBatisUserDao(){
        MapperFactoryBean<MyBatisUserDao> bean = new MapperFactoryBean<>();
        bean.setMapperInterface(MyBatisUserDao.class);
        bean.setSqlSessionFactory(sqlSessionFactory);
        return bean;
    }

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

}

测试

通过链接

• 插入：http://localhost:8080/mybatis/insertUser?id=4&userName=小连&sex=女&note=aaaaaa（根据自己定义修改）
• 修改：http://localhost:8080/mybatis/updateUser?id=4&userName=小周&sex=女&note=aaaaaa （根据id进行修改内容）
• 查询：http://localhost:8080/mybatis/getUser?id=4 （根据id进行查找）
• 删除：http://localhost:8080/mybatis/deleteUser?id=4 （根据id进行删除）
  如若错误，希望指出。