Java学习-MyBatis学习(三)

MyBatis中各种查询

查询一个实体类对象

查询一个list集合

查询单个数据

查询一条数据为map集合

package com.lotus.mybatis.mapper;
import com.lotus.mybatis.pojo.User;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;import java.util.List;
import java.util.Map;public interface SelectMapper {/*** 根据id查询用户信息*/User getUserById(@Param("id") Integer id);/*** 查询所有数据*/List<User> getAllUsers();/*** 查询用户信息的总记录数*/Integer getCount();/*** 根据id查询用户信息为一个map集合*/Map<String,Object> getUserByIdToMap(@Param("id") Integer id);/*** 查询所有用户信息为map集合* 将唯一字段做为键*/@MapKey("id")
//    List<Map<String,Object>> getAllUsersToMap();Map<String,Object> getAllUsersToMap();
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lotus.mybatis.mapper.SelectMapper"><!--User getUserById(@Param("id") Integer id);--><select id="getUserById" resultType="User">select * from t_user where id = #{id}</select><!--List<User> getAllUsers();--><select id="getAllUsers" resultType="User">select * from t_user</select><!--Integer getCount();--><select id="getCount" resultType="java.lang.Integer">select count(*) from t_user;</select><!--Map<String,Object> getUserByIdToMap(Integer id);--><select id="getUserByIdToMap" resultType="map">select * from t_user where id=#{id}</select><!--Map<String,Object> getAllUsersToMap();--><select id="getAllUsersToMap" resultType="map">select * from t_user</select>
</mapper>

MyBatis设置了默认的类别名称

package com.lotus.mybatis.mapper;import com.lotus.mybatis.pojo.User;
import com.lotus.mybatis.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.List;
import java.util.Map;
public class SelectMapperTest {/*** 若查询出的数据只有一条，* ①则通过实体类对象* ②集合接收* ③map接收---字段为键，值为值* 若查询出的数据有多条，* ①可以通过集合接收，不能通过实体类接收，否则会抛出异常TooManyResultsException* ②通过map集合接收List<map<String,Object>>* ③可以在mapper接口的方法上添加@MapKey注解，此时就可以将每条数据转换的map集合帮为值，以某个字段作为键放在同一个map集合中，键需唯一*/@Testpublic void testGetUserById() {SqlSession sqlSession = SqlSessionUtils.getSqlSession();SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);User user = mapper.getUserById(4);System.out.println(user);}@Testpublic void testGetAllUsers() {SqlSession sqlSession = SqlSessionUtils.getSqlSession();SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);List<User> allUsers = mapper.getAllUsers();System.out.println(allUsers);}@Testpublic void testGetCount() {SqlSession sqlSession = SqlSessionUtils.getSqlSession();SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);Integer count = mapper.getCount();System.out.println(count);}@Testpublic void testGetUserByIdToMap() {SqlSession sqlSession = SqlSessionUtils.getSqlSession();SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);Map<String, Object> map = mapper.getUserByIdToMap(4);System.out.println(map.keySet());}@Testpublic void testGetUsersToMap() {SqlSession sqlSession = SqlSessionUtils.getSqlSession();SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);//List<Map<String, Object>> map = mapper.getAllUsersToMap();Map<String, Object> map = mapper.getAllUsersToMap();System.out.println(map);}
}

模糊查询

批量删除

动态设置表名

public interface SQLMapper {/*** 根据用户名模糊查询用户信息*/List<User> getUserByLike(@Param("username") String username);/*** 批量删除*/void deleteMore(@Param("ids") String ids);/*** 查询指定表中数据*/List<User> getUserByTableName(@Param("tableName") String tableName);/*** 添加用户信息*/void insertUser(User user);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lotus.mybatis.mapper.SQLMapper">
<!--List<User> getUserByLike(@Param("username") String username);--><select id="getUserByLike" resultType="User"><!-- ①必须使用${} --><!-- ①select * from t_user where username like '%${username}%'--><!-- ②select * from t_user where username like concat('%',#{username},'%')-->select * from t_user where username like "%"#{username}"%"</select><!--int deleteMore(@Param("ids") String ids);--><delete id="deleteMore"><!-- 此处必须使用${} -->delete from t_user where id in (${ids})</delete><!--List<User> getUserByTableName(@Param("tableName") String tableName);--><select id="getUserByTableName" resultType="User"><!-- 此处必须使用${} -->select * from ${tableName}</select><!--void insertUser(User user);--><!--useGeneratedKeys：设置当前标签中的SQL使用了自增的主键keyProperty：将自增的主键的值赋值给传输到映射文件中参数的某个属性--><insert id="insertUser" useGeneratedKeys="true" keyProperty="id">insert into t_user values(null,#{username},#{password},#{age},#{sex},#{email})</insert>
</mapper>

public class SQLMapperTest {@Testpublic void getUserByLike(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);List<User> list = mapper.getUserByLike("d");System.out.println(list);}@Testpublic void testDeleteMore(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);mapper.deleteMore("1,2,3,4,5");//System.out.println(res);}@Testpublic void testGetUserByTableName(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);List<User> users = mapper.getUserByTableName("t_user");System.out.println(users);}@Testpublic void testInsertUser(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);User user = new User(null, "edison", "123", 15, "男", "edison@qq.com");System.out.println(user.getId());mapper.insertUser(user);System.out.println(user.getId());}
}