MyBatis--特殊SQL的执行

在上一个项目进行测试，项目初始化参考：开始准备工作

1、准备工作

创建mapper接口，路径在src/main/java/com/mybatis/mapper/SQLMapper.java

package com.mybatis.mapper;

public interface SQLMapper {
    // 接口
}

创建mapper映射，路径在src/main/resources/com/mybatis/mapper/SQLMapper.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.mybatis.mapper.SQLMapper">
    <!--待添加-->
</mapper>

创建测试类，路径在src/test/java/com/mybatis/test/SQLMapperTest.java

package com.mybatis.test;

public class SQLMapperTest {
    // 待测试方法
}

2、模糊查询

a>在SQLMapper接口添加方法

/**
* 根据用户名模糊查询用户信息
*/
List<User> getUserByLike(@Param("username") String username);

b>在SQLMapper映射添加查询

<select id="getUserByLike" resultType="User">
	SELECT * FROM t_user WHERE username LIKE concat('%', #{username}, '%')
</select>

第二种方式，使用${}，如下：

<select id="getUserByLike" resultType="User">
	SELECT * FROM t_user WHERE username LIKE '%${username}%'
</select>

第三种方式（推荐使用）：

<select id="getUserByLike" resultType="User">
	SELECT * FROM t_user WHERE username LIKE "%"#{username}"%"
</select>

c>在SQLMapperTest添加测试方法

@Test
public void testGetUserByLike() {
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
    System.out.println(mapper.getUserByLike("张"));
}

d>运行结果

[
	User(id=1, username=张玉, password=123, age=23, sex=女, email=123@qq.com), 
	User(id=5, username=张飞, password=777, age=26, sex=男, email=ttt@gmail.com)
]

3、批量删除

a>在SQLMapper接口添加方法

/**
* 批量删除用户
*/
int deleteUserByIds(@Param("ids") String ids);

b>在SQLMapper映射添加删除

<delete id="deleteUserByIds">
	DELETE FROM t_user WHERE id IN (${ids})
</delete>

此时不能使用#{}

c>在SQLMapperTest添加测试方法

@Test
public void testDeleteUserByIds() {
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
    System.out.println(mapper.deleteUserByIds("5,6,7"));
}

d>运行结果

也可以通过动态SQL来进行批量删除，参考动态SQL–批量删除或添加

4、动态设置表名

a>在SQLMapper接口添加方法

/**
* 动态设置表名，查询所有的用户信息
*/
List<User> getUserByTableName(@Param("tableName") String tableName);

b>在SQLMapper映射添加查询

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

由于表名不能加单引号，故此处只能用${}，而不能用#{}

c>在SQLMapperTest添加测试方法

@Test
public void testGetUserByTableName() {
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
    System.out.println(mapper.getUserByTableName("t_user"));
}

d>运行结果

[
    User(id=1, username=张玉, password=123, age=23, sex=女, email=123@qq.com), 
    User(id=2, username=雷利, password=577, age=25, sex=男, email=666@qq.com), 
    User(id=3, username=柳丰, password=999, age=24, sex=男, email=999@qq.com), 
    User(id=4, username=高敏, password=333, age=22, sex=女, email=111@163.com)
]

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

a>在SQLMapper接口添加方法

/**
* 添加用户信息
*/
int insertUser(User user);

b>在SQLMapper映射添加插入

<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
	INSERT INTO t_user VALUES(#{id},#{username},#{password},#{age},#{sex},#{email})
</insert>

useGeneratedKeys：设置使用自增的主键

keyProperty：因为增删改有统一的返回值是受影响的行数，因此只能将获取的自增的主键放在传输的参数user对象的某个属性中，此处放在id中

c>在SQLMapperTest添加测试方法

@Test
public void testInsertUser() {
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
    User user = new User(null,"许嵩","666",30,"男","100@163.com");	// id在数据库中自增
    System.out.println(mapper.insertUser(user));
    System.out.println(user);   // 此时User对象的id属性有值
}

d>运行结果

数据库