一,引言
在学习一门新技术之前,还是需要下先看一下官网的
https://mybatis.org/mybatis-3/zh/dynamic-sql.html
在学spring的时候可能大家已经学了mybatis了,也许大家也学完了他的mybatis-plus,也或许在springboot整合mybatis中也写了大量的动态sql语句查询,但是今天还是总结了一下他的使用,以为这是成为java程序员的一门必修课。
用官网的一句话说就是 动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
二,数据库的建立
CREATE TABLE `users` (
`user_key_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) NOT NULL,
`user_password` varchar(255) NOT NULL,
`create_time` date NOT NULL,
`update_time` date NOT NULL,
PRIMARY KEY (`user_key_id`)
) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
springboot项目的建立
pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
分页插件
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.3</version>
</dependency>
<!--<dependency>-->
<!--<groupId>com.github.pagehelper</groupId>-->
<!--<artifactId>pagehelper</artifactId>-->
<!--<version>5.1.4</version>-->
<!--</dependency>-->
<!--<dependency>-->
<!--<groupId>com.github.jsqlparser</groupId>-->
<!--<artifactId>jsqlparser</artifactId>-->
<!--<version>0.9.5</version>-->
<!--</dependency>-->
application.properties
#端口号
server.port=8080
#清除缓存
spring.thymeleaf.cache=false
#配置mapper
mybatis.mapper-locations=classpath:mapper/*.xml
#开启驼峰映射
mybatis.configuration.map-underscore-to-camel-case=true
application.yml
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/mybatis-study?serverTimezone=GMT%2B8&useSSL=true
username: root
password: zhs03171812
model实体类
package com.zheng.model;
import java.util.Date;
public class User {
private Integer userKeyId;
private String userName;
private String userPassword;
private Date createTime;
private Date updateTime;
public Integer getUserKeyId() {
return userKeyId;
}
public void setUserKeyId(Integer userKeyId) {
this.userKeyId = userKeyId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPassword() {
return userPassword;
}
public void setUserPassword(String userPassword) {
this.userPassword = userPassword;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
@Override
public String toString() {
return "User{" +
"userKeyId=" + userKeyId +
", userName='" + userName + '\'' +
", userPassword='" + userPassword + '\'' +
", createTime=" + createTime +
", updateTime=" + updateTime +
'}';
}
}
三,测试
在controller中进行测试一下,看看代码能不能跑
package com.zheng.controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class controller {
@RequestMapping("/h1")
public String test(){
return "hello world";
}
}
建UserMapper
package com.zheng.mapper;
import com.zheng.model.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
@Mapper
public interface UserMapper {
//插入数据
public int toInsert(User user);
//获取全部的用户信息
public List<User> getUsers();
//删除数据
public int toDelete(@Param("userName") String userName);
//根据用户名进行用户的查询
public User getUserById(@Param("userName") String id);
//批量插入数据
public int insertUsers(List<User> list);
//动态sql之if
public List<User> getUserByIf(User user);
//动态sql之choose
public List<User> getUserByChoose(User user);
}
UserMapper.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.zheng.mapper.UserMapper">
<!--映射-->
<!--建议加上,无论字段名是否一致-->
<resultMap id="baseMap" type="com.zheng.model.User">
<id column="user_key_id" property="userKeyId"></id>
<result column="user_name" property="userName"></result>
<result column="user_password" property="userPassword"></result>
<result column="create_time" property="createTime"></result>
<result column="update_time" property="updateTime"></result>
</resultMap>
<!--插入数据-->
<insert id="toInsert" parameterType="com.zheng.model.User">
insert into users (user_key_id,user_name,user_password,create_time,update_time)
values (#{userKeyId},#{userName},#{userPassword},#{createTime},#{updateTime})
</insert>
<!--查询数据-->
<select id="getUsers" resultMap="baseMap" resultType="java.util.List">
select * from users
</select>
<!--根据用户名删除用户-->
<delete id="toDelete">
delete from users where user_name = #{userName}
</delete>
<!--根据用户名查询用户信息-->
<select id="getUserById" resultMap="baseMap" resultType="java.lang.String">
select * from users where user_name = #{userName}
</select>
<!--批量插入用户信息-->
<insert id="insertUsers" parameterType="java.util.List">
insert into users (user_key_id,user_name,user_password,create_time,update_time)
values
<foreach collection="list" item="item" separator=",">
(#{item.userKeyId},#{item.userName},#{item.userPassword},#{item.createTime},#{item.updateTime})
</foreach>
</insert>
<!-- 动态sql之if -->
<!-- 1=1 防止出现and重复的问题,后面加上and和or都行 -->
<select id="getUserByIf" parameterType="com.zheng.model.User" resultType="java.util.List" resultMap="baseMap">
select * from users where 1=1
<if test="userKeyId != null and userKeyId !=''">
and user_key_id = #{userKeyId}
</if>
<if test="userName != null and userName !=''">
and user_name = #{userName}
</if>
<if test="userPassword != null and userPassword !=''">
and user_password = #{userPassword}
</if>
<if test="createTime != null and createTime !=''">
and create_Time = #{createTime}
</if>
<if test="updateTime != null and updateTime !=''">
and update_time = #{updateTime}
</if>
</select>
<!-- 动态choose -->
<select id="getUserByChoose" parameterType="com.zheng.model.User" resultType="com.zheng.model.User" resultMap="baseMap">
select * from users
<where>
<choose>
<when test="userName == 'zhenghuisheng'">
user_name = #{userName}
</when>
<when test="userName == '538eaec3-a'">
user_name = #{userName}
</when>
<when test="userName == '94a1c998-d'">
user_name = #{userName}
</when>
</choose>
</where>
</select>
</mapper>
UserService.java
package com.zheng.service;
import com.zheng.model.User;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public interface UserService {
//插入数据
public int toInsert(User user);
//获取全部的用户信息
public List<User> getUsers();
//删除数据
public int toDelete(@Param("userName") String userName);
//根据用户名进行用户的查询
public User getUserById(@Param("userName") String id);
//批量插入数据
public int insertUsers(List<User> list);
//动态sql之if
public List<User> getUserByIf(User user);
//动态sql之choose
public List<User> getUserByChoose(User user);
//实现分页
public PageInfo<User> getUserByPage(@Param("pageNum")Integer pageNum,@Param("pageSize")Integer pageSize);
}
UserServiceImpl.java
package com.zheng.service.impl;
import com.zheng.mapper.UserMapper;
import com.zheng.model.User;
import com.zheng.service.UserService;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
/**
* @author zhenghuisheng
* @date 2021:17:05
*/
@Service
public class UserServiceImpl implements UserService {
@Resource
private UserMapper userMapper;
//插入数据
@Override
public int toInsert(User user) {
return userMapper.toInsert(user);
}
//查询全部数据
@Override
public List<User> getUsers() {
return userMapper.getUsers();
}
//删除数据
@Override
public int toDelete(String userName) {
return userMapper.toDelete(userName);
}
//根据用户名进行用户的查询
public User getUserById(@Param("userName") String id){
return userMapper.getUserById(id);
}
//批量插入数据
@Override
public int insertUsers(List<User> list) {
return userMapper.insertUsers(list);
}
//动态sql查询
@Override
public List<User> getUserByIf(User user) {
return userMapper.getUserByIf(user);
}
//动态sql之choose
@Override
public List<User> getUserByChoose(User user) {
return userMapper.getUserByChoose(user);
}
//分页查询
@Override
public PageInfo<User> getUserByPage(Integer pageNum, Integer pageSize) {
//设置初始分页
PageHelper.startPage(pageNum, pageSize);
//查询全部的用户
List<User> usersList = userMapper.getUsers();
//对所有用户信息作出分页
PageInfo<User> pageInfo = new PageInfo(usersList);
return pageInfo;
}
}
最终测试
package com.zheng;
import com.zheng.model.User;
import com.zheng.service.UserService;
import com.zheng.service.impl.UserServiceImpl;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
/**
* @author zhenghuisheng
* @date 2021/03/07 18:37
*/
@SpringBootTest
class DemoApplicationTests {
@Resource
private UserServiceImpl userService;
//插入数据的测试
@Test
void contextLoads() {
User user = new User();
//设置用户名为10为长度的id
user.setUserName(UUID.randomUUID().toString().substring(0,10));
//设置密码
user.setUserPassword(UUID.randomUUID().toString().substring(0,6));
user.setCreateTime(new Date());
user.setUpdateTime(new Date());
userService.toInsert(user);
System.out.println("数据插入成功");
}
//查询数据的测试
@Test
public void test(){
List<User> users = userService.getUsers();
//将数据全部查询出
users.forEach(e-> System.out.println(e));
}
//根据用户名进行id的删除
@Test
public void delete(){
int i = userService.toDelete("d3ac4192-3");
if (i > 0){
System.out.println("数据删除成功");
}
}
//根据username名进行用户的查询
@Test
public void getByID(){
User zhenghuisheng = userService.getUserById("zhenghuisheng");
if (!zhenghuisheng.equals("")){
System.out.println("数据查询成功" + zhenghuisheng);
}else{
System.out.println("数据查询失败");
}
}
@Test
//批量插入20条数据
public void insertUsers(){
int count = 20;
List<User> list = new ArrayList<>();
for (int i = 0; i < count; i++) {
User user = new User();
//设置用户名为10为长度的id
user.setUserName(UUID.randomUUID().toString().substring(0,10));
//设置密码
user.setUserPassword(UUID.randomUUID().toString().substring(0,6));
user.setCreateTime(new Date());
user.setUpdateTime(new Date());
list.add(user);
}
userService.insertUsers(list);
}
@Test
//动态sql查询语句之 if
public void getUserByIf(){
User user = new User();
user.setUserName("48cae73e-d");
user.setUserPassword("b60a54");
List<User> userByIf = userService.getUserByIf(user);
System.out.println("===================================");
userByIf.forEach(e-> System.out.println(e));
System.out.println("+++++++++++++++++++++++++++++++++++");
}
//动态sql查询语句之 choose
@Test
public void getUserByChoose(){
User user = new User();
user.setUserName("zhenghuisheng");
List<User> userByChoose = userService.getUserByChoose(user);
userByChoose.forEach(e-> System.out.println(e));
}
}
由于在后面加了一个分页查询,接下来就使用controller来测试
controller层
@GetMapping("/page")
public String toPage(@Param("pageNum") int pageNum, @Param("pageSize") int pageSize, Model model){
//获取分页信息
PageInfo<User> userByPage = userService.getUserByPage(pageNum, pageSize);
//获取分页信息里面的当前分页的全部用户
List<User> userList = userByPage.getList();
//将获取的数据传给前端
model.addAttribute("userList",userList);
//获取当前页
model.addAttribute("curPage",userByPage.getPageNum());
//获取全部人数的信息
model.addAttribute("userListCount",userService.getUsers().size());
//跳转到templates下面的list.html下面
return "list";
}
service
//分页查询
@Override
public PageInfo<User> getUserByPage(Integer pageNum, Integer pageSize) {
//设置初始分页
PageHelper.startPage(pageNum, pageSize);
//查询全部的用户
List<User> usersList = userMapper.getUsers();
//对所有用户信息作出分页
PageInfo<User> pageInfo = new PageInfo(usersList);
return pageInfo;
}
templeates下面新建list.html
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<br>
<br>
<table align="center" border="1">
<tr>
<td>用户id</td>
<td>用户名</td>
<td>密码</td>
<td>创建时间</td>
<td>更新时间</td>
<td></td>
</tr>
<!--<tr th:each="prod : ${prods}">
<td th:text="${prod.name}">Onions</td>
<td th:text="${prod.price}">2.41</td>
<td th:text="${prod.inStock}? #{true} : #{false}">yes</td>
</tr>-->
<tr th:each="userlist : ${userList}">
<td th:text="${userlist.userKeyId}"></td>
<td th:text="${userlist.userName}"></td>
<td th:text="${userlist.userPassword}"></td>
<td th:text="${userlist.createTime}"></td>
<td th:text="${userlist.updateTime}"></td>
</tr>
</table>
<div align="center">
<a th:href="success.html">首页</a>
<a th:href="success.html">上一页</a>
<a th:href="success.html">下一页</a>
<a th:href="success.html">末页</a>
当前页为:<span th:text="${curPage}" >US</span>
总量为:<span th:text="${userListCount}"></span>
</div>
</body>
</html>
运行测试: localhost:8080/page?pageNum=1&pageSize=10
总结
测试结果就先不粘贴出来了,可自行测试,都是没问题的代码。还有以上主要就是写了一些常用的动态sql语句,不常用的就先省略了!