Spring Boot整合MyBatis连接 SQL Server（2）基于XML的SQL映射文件操作数据库

基于XML的SQL映射文件操作数据库

1. 在pom.xml文件中添加依赖

        <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.4</version>
        </dependency>

        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <scope>runtime</scope>
        </dependency>

2. application.properties 文件中添加以下内容

spring.datasource.url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=xxx #这里填自己的数据库名

spring.datasource.username=xxx
#这里填自己的登录名
spring.datasource.password=xxx
#这里填自己的密码
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
logging.level.com.example.demo.repository=debug
#这里填自己的包路径
spring.jackson.serialization.indent-output=true
mybatis.type-aliases-package=com.example.demo.entity
#这里填自己的包路径
mybatis.mapper-locations=classpath:mappers/*xml

3. SQL Server中建表
   

4. 创建entity包，在包中创建MyUser实体类

package com.example.demo.entity;

import lombok.Data;

@Data
public class MyUser {
	//成员变量名与数据库中表的字段要对应上
    private Integer uid;
    private String uname;
    private String usex;

}

5. 创建repository包，包中创建MyUserRepository接口

package com.example.demo.repository;

import com.example.demo.entity.MyUser;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface MyUserRepository {
    public List<MyUser> findAll();
    public void insertData(@Param("uid")Integer uid,@Param("uname")String uname,@Param("usex")String usex);
    public void deleteData();
}

6. 创建service包，在包中创建MyUserService接口和MyUserServiceImpl实现类

//MyUserService
package com.example.demo.service;

import com.example.demo.entity.MyUser;

import java.util.List;

public interface MyUserService {
    public List<MyUser> findAll();
    public void insertData(Integer uid,String uname,String usex);
    public void deleteData();
}

}

//MyUserServiceImpl
import java.util.List;
@Service
public class MyUserServiceImpl implements MyUserService{
    @Autowired
    private MyUserRepository myUserRepository;
    @Override
    public List<MyUser> findAll() {
        return myUserRepository.findAll();
    }
    @Override
    public void deleteData(){
        myUserRepository.deleteData();
    }
    @Override
    public void insertData(Integer uid,String uname,String usex){
        myUserRepository.insertData(uid,uname,usex);
    }
}

7. 创建controller包，在包中创建控制器类MyUserController

package com.example.demo.controller;

import com.example.demo.entity.MyUser;
import com.example.demo.service.MyUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
@RestController
public class MyUserController {
    @Autowired
    private MyUserService myUserService;
    @RequestMapping("/findAll")
    public List<MyUser> findAll(){
        return myUserService.findAll();
    }
    @RequestMapping("/delete")
    public String deleteData(){
        myUserService.deleteData();
        return "delete successfully";
    }
    @RequestMapping("/insert")
    public String insertData(Integer uid,String uname,String usex){
        myUserService.insertData(50,"uuu","男");
        return "insert successfully";
    }

}

8. 入口类

package com.example.demo;

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

@SpringBootApplication
@MapperScan(basePackages={"com.example.demo.repository"})
public class DemoApplication {

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

}

9. 在src/main/resources目录下创建名为mappers的包，在包中创建映射文件MyUserMapper.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.repository.MyUserRepository">
    <select id="findAll"  resultType="MyUser">
        select * from My_user
    </select>
    <delete id="deleteData">
        truncate table My_user
    </delete>
    <insert id="insertData" >
        insert into My_user(uid,uname,usex) values (#{uid},#{uname},#{usex})
    </insert>
</mapper>

10. 运行结果
    
    
    

使用SQL注解操作数据库请看上一篇
https://blog.csdn.net/Oceansssss/article/details/122566549

下一篇将介绍如何配置双(多)数据源
https://blog.csdn.net/Oceansssss/article/details/122692556

源码下载：
https://download.csdn.net/download/Oceansssss/77684366