目录
一 为什么需要整合Mybatis
在MyBatis官网(官网地址:http://www.mybatis.org/mybatis-3/zh/index.html)上是这样介绍MyBatis的:MyBatis是一款优秀的持久层框架,它支持定制化SQL、存储过程以及高级映射。MyBatis避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。MyBatis可以使用简单的XML或注解来配置和映射原生信息,将接口和Java的POJOs(Plain Old Java Objects,普通的Java对象)映射成数据库中的记录。
通俗地理解,MyBatis最大的优点是:
• 可以手写SQL,比较灵活,对于很多互联网公司、业务迭代速度快的公司或者业务复杂的项目,MyBatis修改、维护等方面更加灵活。
• 从学习成本上来说,MyBatis上手更加容易,基本上没有更多学习成本,这是很多公司选用MyBatis的理由。
• 从SQL优化方面来说,手写的SQL优化起来更加方便。
二 如何整合Mybatis
有两种方式整合 Mybatis:注解方式、XML 方式
2.1 注解方式
2.1.1 引入依赖
<!-- Spring-Mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<!-- MySQL -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.45</version>
</dependency>
2.1.2 表结构设计
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(50) NOT NULL COMMENT '用户名',
`age` int(11) NOT NULL COMMENT '年龄',
`create_datetime` datetime NOT NULL COMMENT '创建时间',
`update_datetime` datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2.1.3 配置数据源
application.yml 添加如下配置,数据库、用户名、密码配置成自己的。
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/account
username: root
password: root
2.1.4 代码实现
2.1.4.1 配置指定 domain 扫描包。
application.yml 添加如下配置让 mybatis 自动扫描到自定义 domain 类。
mybatis:
type-aliases-package: com.sb.demo.domain
2.1.4.2 dao 层代码接口和实现
package com.sb.dao;
import com.sb.domain.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface UserDao {
@Select("SELECT * FROM user WHERE id = #{id}")
User getUserById(Long id);
@Select("SELECT * FROM user")
public List<User> getUserList();
@Insert("insert into user(name, age, create_datetime, update_datetime) values(#{name}, #{age}, now(), now())")
public int add(User user);
@Update("UPDATE user SET name = #{user.name} , age = #{user.age} WHERE id = #{id}")
public int update(@Param("id") Long id, @Param("user") User user);
@Delete("DELETE from user where id = #{id} ")
public int delete(Long id);
}
添加 @MapperScan
package com.sb;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@MapperScan(basePackages = "com.sb.dao")
@SpringBootApplication
public class SpringbootMybatisApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootMybatisApplication.class, args);
}
}
2.1.4.3 Service 层代码接口和实现
UserService 接口
package com.sb.service;
import com.sb.domain.User;
import java.util.List;
public interface UserService {
User getUserById(Long id);
public List<User> getUserList();
public int add(User user);
public int update(Long id, User user);
public int delete(Long id);
}
UserServiceImpl 实现
package com.sb.service.impl;
import com.sb.dao.UserDao;
import com.sb.domain.User;
import com.sb.service.UserService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
@Resource
private UserDao userDao;
@Override
public User getUserById(Long id) {
return userDao.getUserById(id);
}
@Override
public List<User> getUserList() {
return userDao.getUserList();
}
@Override
public int add(User user) {
return userDao.add(user);
}
@Override
public int update(Long id, User user) {
return userDao.update(id, user);
}
@Override
public int delete(Long id) {
return userDao.delete(id);
}
}
2.1.4.4 Controller 层代码实现
package com.sb.controller;
import com.sb.domain.User;
import com.sb.dto.RetDTO;
import com.sb.service.UserService;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.*;
@RestController
@RequestMapping(value="/users")
public class UserController {
@Resource
private UserService userService;
@RequestMapping(value={"/list"}, method=RequestMethod.GET)
public RetDTO<List<User>> getUserList() {
List<User> r = userService.getUserList();
return RetDTO.getReturnJson(r);
}
@RequestMapping(value="/insert", method=RequestMethod.POST)
public RetDTO insert(@RequestBody User user) {
return RetDTO.getReturnJson(userService.add(user));
}
@RequestMapping(value="/{id}", method=RequestMethod.GET)
public RetDTO<User> getUser(@PathVariable Long id) {
return RetDTO.getReturnJson(userService.getUserById(id));
}
@RequestMapping(value="/{id}", method=RequestMethod.PUT)
public RetDTO putUser(@PathVariable Long id, @RequestBody User user) {
return RetDTO.getReturnJson(userService.update(id, user));
}
@RequestMapping(value="/{id}", method=RequestMethod.DELETE)
public RetDTO deleteUser(@PathVariable Long id) {
return RetDTO.getReturnJson(userService.delete(id));
}
}
2.2 XML 方式
2.2.1 引入依赖
<!-- Spring-Mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<!-- MySQL -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.45</version>
</dependency>
2.2.2 表结构设计
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(50) NOT NULL COMMENT '用户名',
`age` int(11) NOT NULL COMMENT '年龄',
`create_datetime` datetime NOT NULL COMMENT '创建时间',
`update_datetime` datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2.2.3 配置数据源
application.yml 添加如下配置,数据库、用户名、密码配置成自己的。
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/account
username: root
password: root
2.2.4 代码实现
2.2.4.1 配置指定 domain 扫描包。
application.yml 添加如下配置让 mybatis 自动扫描到自定义 domain 类。
mybatis:
mapper-locations: classpath:dao/*.xml # xml文件路径
type-aliases-package: com.sb.domain # 注意:对应实体类的路径
2.2.4.2 dao 层代码接口和实现
dao 类
package com.sb.dao;
import com.sb.domain.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface UserDao {
User getUserById(Long id);
public List<User> getUserList();
public int add(User user);
public int update(@Param("id") Long id, @Param("user") User user);
public int delete(Long id);
}
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="com.sb.dao.UserDao" >
<resultMap id="BaseResultMap" type="com.sb.domain.User" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
<result column="create_datetime" property="createDatetime" jdbcType="TIMESTAMP"/>
<result column="update_datetime" property="updateDatetime" jdbcType="TIMESTAMP"/>
</resultMap>
<sql id="Base_Column_List" >
id, name, age, create_datetime, update_datetime
</sql>
<select id="getUserList" resultMap="BaseResultMap" >
SELECT
<include refid="Base_Column_List" />
FROM user
</select>
<select id="getUserById" parameterType="java.lang.Integer" resultMap="BaseResultMap" >
SELECT
<include refid="Base_Column_List" />
FROM user
WHERE id = #{id}
</select>
<insert id="add" parameterType="com.sb.domain.User" >
INSERT INTO
user
(name, age, create_date_time, update_date_time)
VALUES
(#{name}, #{age}, #{createDatetime}, #{updateDatetime})
</insert>
<update id="update" parameterType="java.util.Map" >
UPDATE
user
SET
name = #{user.name},age = #{user.age}
WHERE
id = #{id}
</update>
<delete id="delete" parameterType="java.lang.Integer" >
DELETE FROM
user
WHERE
id = #{id}
</delete>
</mapper>
添加 @MapperScan
package com.sb;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@MapperScan(basePackages = "com.sb.dao")
@SpringBootApplication
public class SpringbootMybatisApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootMybatisApplication.class, args);
}
}
2.2.4.3 Service 层代码接口和实现
UserService 接口
package com.sb.service;
import com.sb.domain.User;
import java.util.List;
public interface UserService {
User getUserById(Long id);
public List<User> getUserList();
public int add(User user);
public int update(Long id, User user);
public int delete(Long id);
}
UserServiceImpl 实现
package com.sb.service.impl;
import com.sb.dao.UserDao;
import com.sb.domain.User;
import com.sb.service.UserService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
@Resource
private UserDao userDao;
@Override
public User getUserById(Long id) {
return userDao.getUserById(id);
}
@Override
public List<User> getUserList() {
return userDao.getUserList();
}
@Override
public int add(User user) {
return userDao.add(user);
}
@Override
public int update(Long id, User user) {
return userDao.update(id, user);
}
@Override
public int delete(Long id) {
return userDao.delete(id);
}
}
2.2.4.4 Controller 层代码实现
package com.sb.controller;
import com.sb.domain.User;
import com.sb.dto.RetDTO;
import com.sb.service.UserService;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.*;
@RestController
@RequestMapping(value="/users")
public class UserController {
@Resource
private UserService userService;
@RequestMapping(value={"/list"}, method=RequestMethod.GET)
public RetDTO<List<User>> getUserList() {
List<User> r = userService.getUserList();
return RetDTO.getReturnJson(r);
}
@RequestMapping(value="/insert", method=RequestMethod.POST)
public RetDTO insert(@RequestBody User user) {
return RetDTO.getReturnJson(userService.add(user));
}
@RequestMapping(value="/{id}", method=RequestMethod.GET)
public RetDTO<User> getUser(@PathVariable Long id) {
return RetDTO.getReturnJson(userService.getUserById(id));
}
@RequestMapping(value="/{id}", method=RequestMethod.PUT)
public RetDTO putUser(@PathVariable Long id, @RequestBody User user) {
return RetDTO.getReturnJson(userService.update(id, user));
}
@RequestMapping(value="/{id}", method=RequestMethod.DELETE)
public RetDTO deleteUser(@PathVariable Long id) {
return RetDTO.getReturnJson(userService.delete(id));
}
}