说明
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控制层了