Spring Boot 集成 MyBatis和 SQL Server实践

文章共 509字，阅读大约需要 2分钟 ！概 述Spring Boot工程集成 MyBatis来实现 MySQL访问的示例我们见过很多，而最近用到了微软的 SQL Server数据库，于是本文则给出一个完整的 Spring Boot + MyBatis + SQL Server 的工程示例。注： 本文首发于 My Personal Blog：CodeSheep·程序羊，欢迎光临 小站工程搭建新建 Spring Boot工程pom.xml 中添加 MyBatis和 SQL Server相关的依赖<!–for mybatis–><dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version></dependency><!–for SqlServer–><dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.0</version></dependency>配置 application.properties这里同样主要是对于 MyBatis 和 SQL Server连接相关的配置server.port=89# mybatis 配置mybatis.type-aliases-package=cn.codesheep.springbt_mybatis_sqlserver.entitymybatis.mapper-locations=classpath:mapper/*.xmlmybatis.configuration.map-underscore-to-camel-case=true## ————————————————-## SqlServer 配置spring.datasource.url=jdbc:sqlserver://xxxx:1433;databasename=MingLispring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriverspring.datasource.username=xxxxspring.datasource.password=xxxx建立 SQL Server数据表和实体类首先在 SQL Server数据库中新建数据表 user_test作为测试用表DROP TABLE [demo].[user_test]GOCREATE 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然后在我们的工程中对应建立的 User实体类其字段和实际数据表的字段一一对应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; }}Mybatis Mapper映射配置MyBatis映射配置的 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=“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>与此同时，这里也给出对应 XML的 DAO接口public interface UserMapper { List<User> getAllUsers(); int addUser( User user ); int deleteUser( User user );}为了试验起见，这里给出了 增 / 删 / 查 三个数据库操作动作。编写 Service 和测试Controller上面这些准备工作完成之后，接下来编写数据库 CRUD的 Service类@Service@Primarypublic 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) { return userMapper.deleteUser( user ); }}这里的 Service功能同样主要关于数据表的 增 / 删 / 查 三个数据库操作动作。对照着上面的Service，我们编写一个对应接口测试的Controller@RestControllerpublic 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 ) { return userService.deleteUser( user ); }}实验测试插入数据依次用 POSTMAN通过 Post /addUser接口插入三条数据：{“userId”:1,“userName”:“刘能”,“sex”:true}{“userId”:2,“userName”:“赵四”,“sex”:false}{“userId”:3,“userName”:“王大拿”,“sex”:true}插入完成后去 SQL Server数据库里看一下数据插入情况如下：查询数据调用 Get /getAllUser接口，获取刚插入的几条数据删除数据调用 Post /deleteUser 接口，可以通过用户名来删除对应的用户后 记由于能力有限，若有错误或者不当之处，还请大家批评指正，一起学习交流！My Personal Blog：CodeSheep 程序羊我的半年技术博客之路