一，引言
在学习一门新技术之前，还是需要下先看一下官网的
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语句，不常用的就先省略了！
