The article has 509 words and takes about 2 minutes to read!

We mentioned

We have seen many examples of Spring Boot project integration MyBatis to achieve MySQL access, and recently used Microsoft SQL Server database, So this article gives a complete Spring Boot + MyBatis + SQL Server project example.

Welcome to My Personal Blog CodeSheep

Engineering structures,

  • Create a Spring Boot project
  • pom.xmlAdd MyBatis and SQL Server dependencies to SQL Server
<! --for mybatis--> <dependency> <groupId>org.mybatis.spring.boot</groupId> < artifactId > mybatis - spring - the boot - starter < / artifactId > < version > 1.3.2 < / version > < / dependency > <! --for SqlServer--> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> The < version > 4.0 < / version > < / dependency >Copy the code
  • configurationapplication.properties

Again, this is mainly for MyBatis and SQL Server connection configuration

server.port=89

# mybatis configuration
mybatis.type-aliases-package=cn.codesheep.springbt_mybatis_sqlserver.entity
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.configuration.map-underscore-to-camel-case=true

# # -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -

# # is essentially configurationspring.datasource.url=jdbc:sqlserver://xxxx:1433; databasename=MingLi spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver spring.datasource.username=xxxx spring.datasource.password=xxxxCopy the code

Create SQL Server data tables and entity classes

  • Start by creating a new table in the SQL Server databaseuser_testAs a test table
DROP TABLE [demo].[user_test]
GO
CREATE TABLE [dbo].[user_test] (
[user_id] int NOT NULL ,
[user_name] varchar(50) NOT NULL ,
[sex] tinyint NOT NULL ,
[created_time] varchar(50) NOT NULL 
)

GO
Copy the code
  • And then we’re going to build that in our projectUserEntity class

The fields correspond to the fields of the actual table

public class User {

    private Long userId;
    private String userName;
    private Boolean sex;
    private String createdTime;

    public Long getUserId() {
        return userId;
    }

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

    public String getUserName() {
        return userName;
    }

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

    public Boolean getSex() {
        return sex;
    }

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

    public String getCreatedTime() {
        return createdTime;
    }

    public void setCreatedTime(String createdTime) { this.createdTime = createdTime; }}Copy the code

Mybatis Mapper mapping configuration

  • The MyBatis mapping configuration XML file is as follows:
<? 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="cn.codesheep.springbt_mybatis_sqlserver.mapper.UserMapper">

    <resultMap id="userMap" type="cn.codesheep.springbt_mybatis_sqlserver.entity.User">
        <id property="userId" column="user_id" javaType="java.lang.Long"></id>
        <result property="userName" column="user_name" javaType="java.lang.String"></result>
        <result property="sex" column="sex" javaType="java.lang.Boolean"></result>
        <result property="createdTime" column="created_time" javaType="java.lang.String"></result>
    </resultMap>

    <select id="getAllUsers" resultMap="userMap">
        select * from user_test
    </select>

    <insert id="addUser" parameterType="cn.codesheep.springbt_mybatis_sqlserver.entity.User">
        insert into user_test ( user_id, user_name, sex, created_time ) values ( #{userId}, #{userName}, #{sex}, #{createdTime} )
    </insert>

    <delete id="deleteUser" parameterType="cn.codesheep.springbt_mybatis_sqlserver.entity.User">
        delete from user_test where user_name = #{userName}
    </delete>

</mapper>
Copy the code
  • At the same time, the DAO interface corresponding to XML is also given here
public interface UserMapper {
    List<User> getAllUsers();
    int addUser( User user );
    int deleteUser( User user );
}
Copy the code

For the sake of experiment, there are three database operations: add/delete/search.

Write the Service and test Controller

  • With all of this done, it’s time to write the Service class for the database CRUD
@Service
@Primary
public class UserServiceImpl implements IUserService {

    @Autowired
    private UserMapper userMapper;

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

    @Override
    public int addUser(User user) {
        SimpleDateFormat form = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        user.setCreatedTime( form.format(new Date()) );
        return userMapper.addUser( user );
    }

    @Override
    public int deleteUser(User user) {
        returnuserMapper.deleteUser( user ); }}Copy the code

The Service function here is also mainly about the add/delete/query table three database operations.

  • In contrast to the Service above, we write a Controller corresponding to the interface test
@RestController
public class UserController {

    @Autowired
    private IUserService userService;

    @RequestMapping(value = "/getAllUser", method = RequestMethod.GET)
    public List<User> getAllUser() {
        return userService.getAllUsers();
    }

    @RequestMapping(value = "/addUser", method = RequestMethod.POST)
    public int addUser( @RequestBody User user ) {
        return userService.addUser( user );
    }

    @RequestMapping(value = "/deleteUser", method = RequestMethod.POST)
    public int deleteUser( @RequestBody User user ) {
        returnuserService.deleteUser( user ); }}Copy the code

The experimental test

  • Insert data

POSTMAN = Post /addUser; POSTMAN = Post /addUser

{"userId": 1,"userName":"Liu"."sex":true}
{"userId": 2."userName":"Zhao four"."sex":false}
{"userId": 3."userName":"Dana Wang"."sex":true}
Copy the code

Insert data into SQL Server database

  • Query data

Call the Get /getAllUser interface to Get the data just inserted

  • Delete the data

By calling the Post /deleteUser interface, you can delete the corresponding user by the user name

Remember after

Due to the limited ability, if there is a mistake or improper place, please also criticize and correct, study together!

  • My Personal Blog: CodeSheep program sheep
  • My six months of tech blogging

To subscribe to CodeSheep’s public account, long press or scan below, you can get more practical, understandable and reproducible original articles