Mybatis在SpringBoot下的使用

说明

Mybatis是ORM（对象关系映射）的一种，MyBatis是支持普通 SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis 消除了几乎所有的JDBC代码和参数的手工设置以及结果集的检索。MyBatis 使用简单的 XML或注解用于配置和原始映射，将接口和 Java 的POJOs（Plain Ordinary Java Objects，普通的 Java对象）映射成数据库中的记录。

本文将使用@注解和xml配置的两种方式混合来演示。两种模式各有特点，注解版适合简单快速的模式，其实像现在流行的这种微服务模式，一个微服务就会对应一个自已的数据库，多表连接查询的需求会大大的降低，会越来越适合这种模式。xml老传统模式比适合大型项目，可以灵活的动态生成SQL，方便调整SQL，也有痛痛快快，洋洋洒洒的写SQL的感觉。

快速开始

首先给出项目结构：

创建数据库表userinfo：

create table userinfo ( id int not null, username nvarchar(50), primary key (id) ) insert into usertest(id,username) values(0,'yunlingfly') insert into usertest(id,username) values(1,'sometest')

1 更新pom.xml(我使用的是SQLServer数据库，SQLServer驱动无法直接通过Maven下载，需要自行到官网下载安装-->SQLServer驱动，需要配置连接池（如Druid）请查看我的另一篇文章-->Druid在SpringBoot下的使用)

<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>yunlingfly</groupId> <artifactId>springcloud-mybatis</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>springcloud-mybatis</name> <description>Demo project for Spring Boot</description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>1.5.13.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> <spring-cloud.version>Edgware.SR3</spring-cloud.version> </properties> <dependencies> <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>1.3.2</version> </dependency> <!-- 添加SQLServer,需要自行到官网下载jar --> <!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/sqljdbc4 --> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.2</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <dependencyManagement> <dependencies> <dependency> <groupId>org.springframework.cloud</groupId> <artifactId>spring-cloud-dependencies</artifactId> <version>${spring-cloud.version}</version> <type>pom</type> <scope>import</scope> </dependency> </dependencies> </dependencyManagement> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>

2 更新application.yml文件（使用application.properties的话类似照着改就行）

server: port: 8767 info: name: 用于测试SpringBoot for mybatis version: 0.0.1 spring: application: name: springboot-mybatis datasource: driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver # 填写你数据库的url、登录名、密码和数据库名 url: jdbc:sqlserver://xxx.xxx.xxx.xxx:1433; DatabaseName=xxxx username: xxxxxxxxx password: xxxxxxxxx mybatis: # 使用xml配置的话需要配置xml文件路径 mapperLocations: classpath:mapper/*.xml # 添加需要扫描的包 typeAliasesPackage: yunlingfly.springcloudmybatis.mappers

3 启动类编写

package yunlingfly.springcloudmybatis; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication @MapperScan("yunlingfly.springcloudmybatis.mappers") //添加要扫描的包名，就不用每个接口都用@Mapper注释 public class SpringcloudMybatisApplication { public static void main(String[] args) { SpringApplication.run(SpringcloudMybatisApplication.class, args); } }

4 实体类Person编写（和Hibernate相比不需要@注解来标记属性，这就是一个POJO类）

package yunlingfly.springcloudmybatis.bean; public class Person { private Integer id; private String user_name;// 这里故意将该属性与数据库属性名称不一致 public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUser_name() { return user_name; } public void setUser_name(String user_name) { this.user_name = user_name; } }

5 Mybatis接口层IPersonInfoDao编写

package yunlingfly.springcloudmybatis.mappers; import org.apache.ibatis.annotations.*; import org.springframework.stereotype.Component; import yunlingfly.springcloudmybatis.bean.Person; import java.util.List; /** * @Select 是查询类的注解，所有的查询均使用这个 * @Result 修饰返回的结果集，关联实体类属性和数据库字段一一对应，如果实体类属性和数据库属性名保持一致，就不需要这个属性来修饰。 * @Insert 插入数据库使用，直接传入实体类会自动解析属性到对应的值 * @Update 负责修改，也可以直接传入对象 * @delete 负责删除 */ //@Mapper //前面启动类扫描过包了，就不要每个接口都添加@Mapper @Component public interface IPersonInfoDao { @Select("SELECT id, username FROM userinfo WHERE username = #{username}") @Results({ @Result(property = "id", column = "id"), @Result(property = "user_name", column = "username") }) Person findByUsername(@Param(value = "username") String username); @Select("SELECT * FROM userinfo") @Results({ @Result(property = "id", column = "id"), @Result(property = "user_name", column = "username") }) List<Person> getAll(); @Insert("INSERT INTO userinfo(username,id) VALUES(#{user_name}, #{id})") void insert(Person user); @Update("UPDATE userinfo SET userName=#{user_name} WHERE id =#{id}") void update(Person user); @Delete("DELETE FROM userinfo WHERE id =#{id}") void delete(Integer id); // 上面的是使用@注解配置的mybatis // 下面是使用xml配置的mybatis List<Person> selectAll(); void insertOne(Person person); void updateOne(Person person); void deleteOne(Integer id); }

6 mybatis的xml文件的编写（resource目录下新建文件夹mapper，然后新建文件IPersonInfoDao.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="yunlingfly.springcloudmybatis.mappers.IPersonInfoDao"> <resultMap id="BaseResultMap" type="yunlingfly.springcloudmybatis.bean.Person" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="username" property="user_name" jdbcType="NVARCHAR" /> </resultMap> <select id="selectAll" resultMap="BaseResultMap"> SELECT id,username FROM userinfo </select> <insert id="insertOne" parameterType="yunlingfly.springcloudmybatis.bean.Person"> INSERT INTO userinfo (id, username) VALUES (#{id}, #{user_name}) </insert> <update id="updateOne" parameterType="yunlingfly.springcloudmybatis.bean.Person" > UPDATE userinfo SET <!--<if test="user_name != null">username = #{user_name}</if>--> username = #{user_name} WHERE id = #{id} </update> <delete id="deleteOne" parameterType="java.lang.Integer"> DELETE FROM userinfo WHERE id=#{id} </delete> </mapper>

7 controller层编写（方便测试全用GET了）

package yunlingfly.springcloudmybatis.controller; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import yunlingfly.springcloudmybatis.bean.Person; import yunlingfly.springcloudmybatis.mappers.IPersonInfoDao; import java.util.List; @RestController public class HelloController { @Autowired private IPersonInfoDao iPersonInfoDao; @RequestMapping(value = "/hello",method = RequestMethod.GET) public String hello(@RequestParam String name){ Person person=iPersonInfoDao.findByUsername(name); if(null!=person){ System.out.println("查询成功"); return person.getUser_name(); } else{ System.out.println("账户不存在，查询失败"); return "账户不存在，查询失败"; } } @RequestMapping(value = "/getAll",method = RequestMethod.GET) public List<Person> getAll(){ System.out.println("查询所有"); return iPersonInfoDao.getAll(); } @RequestMapping(value = "/insert",method = RequestMethod.GET) public List<Person> insert(@RequestParam String name){ System.out.println("插入一条数据，然后返回值"); Person p=new Person(); p.setId(3); p.setUser_name(name); iPersonInfoDao.insert(p); return iPersonInfoDao.getAll(); } @RequestMapping(value = "/update",method = RequestMethod.GET) public List<Person> update(@RequestParam String id,@RequestParam String name){ System.out.println("更新数据，然后返回值"); Person p=new Person(); p.setId(Integer.valueOf(id)); p.setUser_name(name); iPersonInfoDao.update(p); return iPersonInfoDao.getAll(); } @RequestMapping(value = "/delete",method = RequestMethod.GET) public List<Person> delete(@RequestParam String id){ System.out.println("删除数据，然后返回值"); iPersonInfoDao.delete(Integer.valueOf(id)); return iPersonInfoDao.getAll(); } // 上面的是使用@注解配置的mybatis // 下面的是使用xml配置的mybatis @RequestMapping(value = "/selectAll",method = RequestMethod.GET) public List<Person> selectAll(){ System.out.println("查询所有"); return iPersonInfoDao.selectAll(); } @RequestMapping(value = "/insertOne",method = RequestMethod.GET) public List<Person> insertOne(@RequestParam String name){ System.out.println("插入一条数据，然后返回值"); Person p=new Person(); p.setId(3); p.setUser_name(name); iPersonInfoDao.insertOne(p); return iPersonInfoDao.getAll(); } @RequestMapping(value = "/updateOne",method = RequestMethod.GET) public List<Person> updateOne(@RequestParam String id,@RequestParam String name){ System.out.println("更新数据，然后返回值"); Person p=new Person(); p.setId(Integer.valueOf(id)); p.setUser_name(name); iPersonInfoDao.updateOne(p); return iPersonInfoDao.getAll(); } @RequestMapping(value = "/deleteOne",method = RequestMethod.GET) public List<Person> deleteOne(@RequestParam String id){ System.out.println("删除数据，然后返回值"); iPersonInfoDao.deleteOne(Integer.valueOf(id)); return iPersonInfoDao.getAll(); } }

运行

运行启动类SpringcloudMybatisApplication，测试：

通过@注解获取数据库列表：http://localhost:8767/getAll

通过xml的方式获取数据库列表：http://localhost:8767/selectAll

其他的测试就不演示了，方法和参数都写在HelloController控制层了