前提说明
表结构
数据
正题
Q1:查询一条数据为map集合
mapper
Map<String, Object> getUserToMap(@Param("id") Integer id);
xml
<select id="getUserToMap" resultType="java.util.Map">
SELECT * FROM t_user WHERE id = #{id}
</select>
test
@Test
public void test1() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> userMap = userMapper.getUserToMap(5);
for (Map.Entry<String, Object> entry : userMap.entrySet()) {
System.out.println("key:" + entry.getKey() + ", value:" + entry.getValue());
}
}
结果
key为列名,value为对应的key列名
Q2:查询多条数据为map集合
方式一
mapper
List<Map<String, Object>> getAllUserToMap1();
xml
<select id="getAllUserToMap1" resultType="java.util.Map">
SELECT * FROM t_user
</select>
test
@Test
public void test2() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<Map<String, Object>> mapList = userMapper.getAllUserToMap1();
for (Map<String, Object> entry : mapList) {
System.out.println(entry);
}
}
结果
方式二
mapper
@MapKey("id")
Map<String, Object> getAllUserToMap2();
xml
<select id="getAllUserToMap2" resultType="java.util.Map">
SELECT * FROM t_user
</select>
test
@Test
public void test3() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> userMap = userMapper.getAllUserToMap2();
for (Map.Entry<String, Object> entry : userMap.entrySet()) {
System.out.println(entry);
}
}
结果
Q3:模糊查询
前提说明:
MyBatis获取参数值的两种方式:${}和#{}
${}的本质就是字符串拼接,#{}的本质就是占位符赋值
${}使用字符串拼接的方式拼接sql,若为字符串类型或日期类型的字段进行赋值时,需要手动加单引号;但是#{}使用占位符赋值的方式拼接sql,此时为字符串类型或日期类型的字段进行赋值时,可以自动添加单引号
但个人建议能用#{}就用#{},为防止SQL注入
mapper
List<User> getByNameLike(@Param("name") String name);
xml
<select id="getByNameLike" resultType="com.copa.entity.User">
<!--select * from t_user where name like '%${name}%'-->
<!--select * from t_user where name like concat('%',#{name},'%')-->
select * from t_user where name like "%"#{name}"%"
</select>
这三种都可以,建议用后面两种,我个人习惯用第三种
test
@Test
public void test4() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.getByNameLike("张");
for (User user : userList) {
System.out.println(user);
}
}
结果
Q4:批量删除(等会再说动态SQL怎么弄)
mapper
int deleteByIds1(@Param("ids") String ids);
xml
<delete id="deleteByIds1">
delete from t_user where id in (${ids})
</delete>
这里就是用了字符串拼接的方式,但不建议这样弄
test
@Test
public void test5() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int delete = userMapper.deleteByIds1("8, 9, 10, 11");
System.out.println(delete);
}
结果
Q5:动态SQL
if
if标签可通过test属性的表达式进行判断,若表达式的结果为true,则标签中的内容会执行;反之标签中的内容不会执行
mapper
List<User> getUserByUserCondition1(@Param("user") User user);
xml
<select id="getUserByUserCondition1" resultType="com.copa.entity.User">
select * from t_user where 1=1
<if test="user.name != '' and user.name != null">
and name = #{user.name}
</if>
<if test="user.age != '' and user.age != null">
and age = #{user.age}
</if>
<if test="user.sex != '' and user.sex != null">
and sex = #{user.sex}
</if>
</select>
test
@Test
public void test6() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User userEntity = User.builder().age(69)
.name("李四")
.sex("男").build();
List<User> userList = userMapper.getUserByUserCondition1(userEntity);
for (User user : userList) {
System.out.println(user);
}
}
结果
where
where和if一般结合使用:
a>若where标签中的if条件都不满足,则where标签没有任何功能,即不会添加where关键字
b>若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件最前方多余的and去掉
注意:where标签不能去掉条件最后多余的and
mapper
List<User> getUserByUserCondition2(@Param("user") User user);
xml
<select id="getUserByUserCondition2" resultType="com.copa.entity.User">
select * from t_user
<where>
<if test="user.name != '' and user.name != null">
name = #{user.name}
</if>
<if test="user.age != '' and user.age != null">
and age = #{user.age}
</if>
<if test="user.sex != '' and user.sex != null">
and sex = #{user.sex}
</if>
</where>
</select>
test
@Test
public void test7() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User userEntity = User.builder().age(69)
.name("李四")
.sex("男").build();
List<User> userList = userMapper.getUserByUserCondition2(userEntity);
for (User user : userList) {
System.out.println(user);
}
}
结果
trim
trim用于去掉或添加标签中的内容
常用属性:
prefix:在trim标签中的内容的前面添加某些内容
prefixOverrides:在trim标签中的内容的前面去掉某些内容
suffix:在trim标签中的内容的后面添加某些内容
suffixOverrides:在trim标签中的内容的后面去掉某些内容
mapper
List<User> getUserByUserCondition3(@Param("user") User user);
xml
<select id="getUserByUserCondition3" resultType="com.copa.entity.User">
select * from t_user
<trim prefix="where" suffixOverrides="and">
<if test="user.name != '' and user.name != null">
name = #{user.name} and
</if>
<if test="user.age != '' and user.age != null">
age = #{user.age} and
</if>
<if test="user.sex != '' and user.sex != null">
sex = #{user.sex}
</if>
</trim>
</select>
test
@Test
public void test8() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User userEntity = User.builder().age(69)
.name("李四")
.sex("男").build();
List<User> userList = userMapper.getUserByUserCondition3(userEntity);
for (User user : userList) {
System.out.println(user);
}
}
结果
choose、when、otherwise 和 SQL片段
sql片段我去面试的时候笔试题还真考过,直接叫你写怎么调用SQL片段(简答题)
sql片段,可以记录一段公共sql片段,在使用的地方通过include标签进行引入
choose、when、otherwise相当于if…else if…else
mapper
List<User> getUserByUserCondition4(@Param("user") User user);
xml
<sql id="empColumns">
*
</sql>
<select id="getUserByUserCondition4" resultType="com.copa.entity.User">
select <include refid="empColumns"></include> from t_user
<where>
<choose>
<when test="user.name != '' and user.name != null">
name = #{user.name}
</when>
<when test="user.age != '' and user.age != null">
age = #{user.age}
</when>
<when test="user.sex != '' and user.sex != null">
sex = #{user.sex}
</when>
</choose>
</where>
</select>
test
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User userEntity = User.builder().age(69)
.name("李四")
.sex("男").build();
List<User> userList = userMapper.getUserByUserCondition4(userEntity);
for (User user : userList) {
System.out.println(user);
}
测试
foreach
属性:
collection:设置要循环的数组或集合
item:表示集合或数组中的每一个数据
separator:设置循环体之间的分隔符
open:设置foreach标签中的内容的开始符
close:设置foreach标签中的内容的结束符
案例1(批量添加)
mapper
int insertMoreUser(@Param("userList") List<User> userList);
xml
<insert id="insertMoreUser">
insert into t_user values
<foreach collection="userList" item="user" separator=",">
(null,#{user.name},#{user.remark},#{user.age},#{user.sex})
</foreach>
</insert>
test
@Test
public void test10() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = new ArrayList<>();
userList.add(new User(null, "copa1", "11", 12, "男"));
userList.add(new User(null, "copa2", "22", 24, "男"));
userList.add(new User(null, "copa3", "33", 36, "男"));
int result = userMapper.insertMoreUser(userList);
System.out.println(result);
}
结果
案例2(批量删除)
mapper
int deleteByIds2(@Param("ids") int[] ids);
xml
<delete id="deleteByIds2">
delete from t_user where
<foreach collection="ids" item="id" separator="or">
id = #{id}
</foreach>
</delete>
test
@Test
public void test11() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int[] ids = new int[]{11, 12, 13};
int result = userMapper.deleteByIds2(ids);
System.out.println(result);
}
结果
案例3(批量删除,对应Q4)
mapper
int deleteByIds3(@Param("ids") int[] ids);
xml
<delete id="deleteByIds3">
delete from t_user where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
test
@Test
public void test12() throws IOException {
SqlSession sqlSession = getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int[] ids = new int[]{12, 13, 14};
int result = userMapper.deleteByIds3(ids);
System.out.println(result);
}
结果
Q6:关于自增id获取
这里我就不写测试类了,直接看mapper和xml
mapper
/**
* 添加用户信息
* @param user
* @return
* useGeneratedKeys:设置使用自增的主键
* keyProperty:因为增删改有统一的返回值是受影响的行数,因此只能将获取的自增的主键放在传输的参数user对象的某个属性中
*/
int insertUser(User user);
mapper
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user values(null,#{username},#{password},#{age},#{sex})
</insert>
Q7:动态设置表名
这里我就不写测试类了,直接看mapper和xml
mapper
/**
* 动态设置表名,查询所有的用户信息
* @param tableName
* @return
*/
List<User> getAllUser(@Param("tableName") String tableName);
xml
<select id="getAllUser" resultType="com.copa.entity.User">
select * from ${tableName}
</select>