MyBatis学习笔记(七) —— 特殊SQL的执行

7、特殊SQL的执行

7.1、模糊查询

模糊查询的三种方式：

方式1：select * from t_user where username like ‘%${mohu}%’

方式2：select * from t_user where username like concat(‘%’,#{mohu},‘%’)

方式3：select * from t_user where username like “%”#{mohu}“%”

SpecialSQLMapper.java接口

package com.fan.mybatis.mapper;import com.fan.mybatis.pojo.User;
import org.apache.ibatis.annotations.Param;import java.util.List;public interface SpecialSQLMapper {/*** 通过用户名模糊查询用户信息* @param mohu* @return*/List<User> getUserByLike(@Param("mohu") String mohu);
}

SpecialSQLMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fan.mybatis.mapper.SpecialSQLMapper"><select id="getUserByLike" resultType="User"><!--select * from t_user where username like '%${mohu}%'--><!--select * from t_user where username like concat('%',#{mohu},'%')-->select * from t_user where username like "%"#{mohu}"%"</select>
</mapper>

SpecialSQLMapperTest.java

package com.fan.mybatis;import com.fan.mybatis.mapper.SpecialSQLMapper;
import com.fan.mybatis.pojo.User;
import com.fan.mybatis.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.List;/*** @Date: 2023/02/25* @Author: fan* @Description:*/
public class SpecialSQLMapperTest {@Testpublic void testGetUserByLike(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);List<User> list = mapper.getUserByLike("a");list.forEach(System.out::println);}
}

测试运行：

方式1：select * from t_user where username like ‘%${mohu}%’

方式二：select * from t_user where username like concat(‘%’,#{mohu},‘%’)

方式三：select * from t_user where username like “%”#{mohu}“%”

建议使用第三种方式。

7.2、批量删除

SpecialSQLMapper.java

/**
* 批量删除
* @param ids
*/
void deleteMoreUser(@Param("ids") String ids);

SpecialSQLMapper.xml

<delete id="deleteMoreUser">delete from t_user where id in (${ids})
</delete>

SpecialSQLMapperTest.java

@Test
public void testDeleteMoreUser(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);mapper.deleteMoreUser("9,10");
}

运行测试

DEBUG 02-25 21:54:42,920==> Preparing: delete from t_user where id in (9,10) (BaseJdbcLogger.java:137)

DEBUG 02-25 21:54:42,949==> Parameters: (BaseJdbcLogger.java:137)

DEBUG 02-25 21:54:42,954<== Updates: 2 (BaseJdbcLogger.java:137)

7.3、动态设置表名

表名不确定，有2张表，表里的字段是一样的，查询数据查询的表是一样的。用户表，vip表，用户表和vip表里的字段是一样的。查询普通用户查的是用户表，查询vip用户查的是vip表，这里就需要设置动态表名了。

SpecialSQLMapper.java

/**
* 动态设置表名，查询用户信息
* @param tableName
* @return
*/
List<User> getUserList(@Param("tableName") String tableName);

SpecialSQLMapper.xml

<select id="getUserList" resultType="User">select * from ${tableName}
</select>

SpecialSQLMapperTest.java

@Testpublic void testGetUserList(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);List<User> list = mapper.getUserList("t_user");list.forEach(System.out::println);
}

运行测试：

DEBUG 02-27 15:01:47,208==> Preparing: select * from t_user (BaseJdbcLogger.java:137)

DEBUG 02-27 15:01:47,232==> Parameters: (BaseJdbcLogger.java:137)

DEBUG 02-27 15:01:47,251<== Total: 8 (BaseJdbcLogger.java:137)

User{id=1, username=‘admin’, password=‘123456’, age=23, gender=‘男’, email=‘[e-mail]’}

User{id=2, username=‘root’, password=‘123456’, age=33, gender=‘女’, email=‘[e-mail]’}

User{id=3, username=‘zhangsan’, password=‘123’, age=23, gender=‘男’, email=‘[e-mail]’}

User{id=4, username=‘lisi’, password=‘123’, age=null, gender=‘null’, email=‘null’}

User{id=5, username=‘a’, password=‘null’, age=null, gender=‘null’, email=‘null’}

User{id=6, username=‘a’, password=‘null’, age=null, gender=‘null’, email=‘null’}

User{id=7, username=‘a’, password=‘null’, age=null, gender=‘null’, email=‘null’}

User{id=8, username=‘a’, password=‘null’, age=null, gender=‘null’, email=‘null’}

7.4、添加功能获取自增的主键

场景模拟：

t_clazz(clazz_id,clazz_name)

t_student(student_id,student_name,clazz_id)

1、添加功能获取自增的主键

2、获取新添加的班级的id

3、为班级分配学生，即将某学的班级id修改为新添加的班级的id

keyProperty就是指明此时自增的字段，而userGenerateKeys表示这里获取自动生成的主键

SpecialSQLMapper.java

/**
* 添加用户信息并获取自增的主键
* @param user
*/
void insertUser(User user);

SpecialSQLMapper.xml

<!--
useGeneratedKeys: 表示当前添加功能使用自增的主键
keyProperty: 将添加的数据的自增主键为实体类类型的参数的属性赋值
-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">insert into t_user values(null,#{username},#{password},#{age},#{gender},#{email})
</insert>

SpecialSQLMapperTest.java

@Testpublic void testInsertUser(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);User user = new User(null,"xiaoming","123456",23,"男","[e-mail]");mapper.insertUser(user);System.out.println(user);
}

运行测试：

DEBUG 02-27 15:22:07,979==> Preparing: insert into t_user values(null,?,?,?,?,?) (BaseJdbcLogger.java:137)

DEBUG 02-27 15:22:07,999==> Parameters: xiaoming(String), 123456(String), 23(Integer), 男(String), [e-mail](String) (BaseJdbcLogger.java:137)

DEBUG 02-27 15:22:08,005<== Updates: 1 (BaseJdbcLogger.java:137)

User{id=11, username=‘xiaoming’, password=‘123456’, age=23, gender=‘男’, email=‘[e-mail]’}