Spring Boot整合MyBatis连接 SQL Server（1）通过SQL注解的方式操作数据库_java spring mybatis 操作 sqlserver 数据库

通过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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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
1
2
3
4
5
6
7
8
9
10
11

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;

}

1
2
3
4
5
6
7
8
9
10
11
12
13

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

package com.example.demo.repository;

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

import java.util.List;

@Repository
public interface MyUserRepository {
	//插入
    @Insert("insert into XXX (uid,uname,usex) values (#{uid},#{uname},#{usex})")
    public void insertData(@Param("uid")Integer uid,@Param("uname")String uname,@Param("usex")String usex);
	//查询
    @Select("select * from XXX")
    public List<MyUser> findAll();
	//删除
    @Delete("truncate table XXX")
    public void deleteData();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

这里用了SQL注解的方式操作数据库

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();
}

1
2
3
4
5
6
7
8
9
10
11
12
13

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

import com.example.demo.entity.MyUser;
import com.example.demo.repository.MyUserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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 insertData(Integer uid,String uname,String usex){
        myUserRepository.insertData(uid,uname,usex);
    }
    @Override
    public void deleteData(){
        myUserRepository.deleteData();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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("/insert")
    public String insertData(Integer uid, String uname, String usex){
        myUserService.insertData(20,"test","女");
        return "insert successfully";
    }
    @RequestMapping("/delete")
    public String deleteData(){
        myUserService.deleteData();
        return "delete successfully";
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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);
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

9. 运行结果
   
   
   
   下一篇将介绍使用基于XML的SQL映射文件操作数据库
   https://blog.csdn.net/Oceansssss/article/details/122668633

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