MyBatis（二）（1）+https://developer.aliyun.com/article/1556667
4. MyBatis的各种查询功能
4.1 代码结构
配置文件等延用第三章，这里对mapper/SelectMapper.java、resources/SelectMapper.xml、test/SelectMapperTest.java进行编写
4.1.1 mapper
SelectMapper.java
package com.atguigu.mybatis.mapper; import com.atguigu.mybatis.pojo.User; public interface SelectMapper { /** * 根据id查询用户信息 */ List<User> getUserById(@Param("id") Integer id); /** * 查询所有的用户信息 */ List<User> getAllUser(); /** * 查询用户信息的总记录数 */ Integer getCount(); /** * 根据id查询用户信息为一个map集合 */ Map<String, Object> getUserByIdToMap(@Param("id") Integer id); /** * 查询所有用户信息为map集合 */ //List<Map<String, Object>> getAllUserToMap(); @MapKey("id") Map<String, Object> getAllUserToMap(); }
4.2 映射文件和测试编写
SelectMapper.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.atguigu.mybatis.mapper.SelectMapper"> <!--User getUserById(@Param("id") Integer id);--> <select id="getUserById" resultType="User"> select * from t_user where id = #{id} </select> <!--List<User> getAllUser();--> <select id="getAllUser" resultType="User"> select * from t_user </select> <!--Integer getCount();--> <select id="getCount" resultType="_int"> select count(*) from t_user </select> <!--Map<String, Object> getUserByIdToMap(@Param("id") Integer id);--> <select id="getUserByIdToMap" resultType="map"> select * from t_user where id = #{id} </select> <!--Map<String, Object> getAllUserToMap();--> <select id="getAllUserToMap" resultType="map"> select * from t_user </select> </mapper>
SelectMapperTest.java
package com.atguigu.mybatis.test; import com.atguigu.mybatis.mapper.SelectMapper; import com.atguigu.mybatis.utils.SqlSessionUtils; public class SelectMapperTest { /** * MyBatis的各种查询功能： * 1、若查询出的数据只有一条 * a>可以通过实体类对象接收 * b>可以通过list集合接收 * c>可以通过map集合接收 * 结果：{password=123456, sex=男, id=3, age=23, email=12345@qq.com, username=admin} * 2、若查询出的数据有多条 * a>可以通过实体类类型的list集合接收 * b>可以通过map类型的list集合接收 * c>可以在mapper接口的方法上添加@MapKey注解，此时就可以将每条数据转换的map集合作为值，以某个字段的值作为键，放在同一个map集合中 * 注意：一定不能通过实体类对象接收，此时会抛异常TooManyResultsException * * MyBatis中设置了默认的类型别名 * java.lang.Integer-->int,integer * int-->_int,_integer * Map-->map * String-->string */ @Test public void testGetAllUserToMap(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SelectMapper mapper = sqlSession.getMapper(SelectMapper.class); System.out.println(mapper.getAllUserToMap()); } @Test public void testGetUserByIdToMap(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SelectMapper mapper = sqlSession.getMapper(SelectMapper.class); System.out.println(mapper.getUserByIdToMap(3)); } @Test public void testGetCount(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SelectMapper mapper = sqlSession.getMapper(SelectMapper.class); System.out.println(mapper.getCount()); } @Test public void testGetAllUser(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SelectMapper mapper = sqlSession.getMapper(SelectMapper.class); System.out.println(mapper.getAllUser()); } @Test public void testGetUserById(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SelectMapper mapper = sqlSession.getMapper(SelectMapper.class); System.out.println(mapper.getUserById(3)); } }
- 如果查询出的数据只有一条，可以通过
- 实体类对象接收
- List集合接收
- Map集合接收，结果
{password=123456, sex=男, id=1, age=23, username=admin}
- 如果查询出的数据有多条，一定不能用实体类对象接收，会抛异常TooManyResultsException，可以通过
- 实体类类型的LIst集合接收
- Map类型的LIst集合接收
- 在mapper接口的方法上添加@MapKey注解
4.2.1 查询一个实体类对象
/** * 根据用户id查询用户信息 * @param id * @return */ User getUserById(@Param("id") int id);
<!--User getUserById(@Param("id") int id);--> <select id="getUserById" resultType="User"> select * from t_user where id = #{id} </select>
@Test public void testGetUserById(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SelectMapper mapper = sqlSession.getMapper(SelectMapper.class); System.out.println(mapper.getUserById(3)); }
4.2.2 查询所有实体类对象
/** * 查询所有的用户信息 */ List<User> getAllUser();
<!--List<User> getAllUser();--> <select id="getAllUser" resultType="User"> select * from t_user </select>
@Test public void testGetAllUser(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SelectMapper mapper = sqlSession.getMapper(SelectMapper.class); System.out.println(mapper.getAllUser()); }
4.2.3 查询单个数据
/** * 查询用户的总记录数 * @return * 在MyBatis中，对于Java中常用的类型都设置了类型别名 * 例如：java.lang.Integer-->int|integer * 例如：int-->_int|_integer * 例如：Map-->map,List-->list */ int getCount();
<!--int getCount();--> <select id="getCount" resultType="_integer"> select count(id) from t_user </select>
@Test public void testGetCount(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SelectMapper mapper = sqlSession.getMapper(SelectMapper.class); System.out.println(mapper.getCount()); }
4.2.4 查询一条数据为map集合
Map<String, Object> getUserToMap(@Param("id") int id);
<!--Map<String, Object> getUserToMap(@Param("id") int id);--> <select id="getUserToMap" resultType="map"> select * from t_user where id = #{id} </select> <!--结果：{password=123456, sex=男, id=1, age=23, username=admin}-->
4.2.5 查询多条数据为map集合
方法一
/** * 查询所有用户信息为map集合 * @return * 将表中的数据以map集合的方式查询，一条数据对应一个map；若有多条数据，就会产生多个map集合，此时可以将这些map放在一个list集合中获取 */ List<Map<String, Object>> getAllUserToMap();
<!--Map<String, Object> getAllUserToMap();--> <select id="getAllUserToMap" resultType="map"> select * from t_user </select> <!-- 结果： [{password=123456, sex=男, id=1, age=23, username=admin}, {password=123456, sex=男, id=2, age=23, username=张三}, {password=123456, sex=男, id=3, age=23, username=张三}] -->
方法二
/** * 查询所有用户信息为map集合 * @return * 将表中的数据以map集合的方式查询，一条数据对应一个map；若有多条数据，就会产生多个map集合，并且最终要以一个map的方式返回数据，此时需要通过@MapKey注解设置map集合的键，值是每条数据所对应的map集合 */ @MapKey("id") Map<String, Object> getAllUserToMap();
<!--Map<String, Object> getAllUserToMap();--> <select id="getAllUserToMap" resultType="map"> select * from t_user </select> <!-- 结果： { 1={password=123456, sex=男, id=1, age=23, username=admin}, 2={password=123456, sex=男, id=2, age=23, username=张三}, 3={password=123456, sex=男, id=3, age=23, username=张三} } -->
5. 特殊SQL的执行
5.1 代码结构
对SQLMapper.java、SQLMapper.xml、SQLMapperTest.java进行编写 ，其他代码延用上面的
5.1.1 mapper
SQLMapper.java
package com.atguigu.mybatis.mapper; import com.atguigu.mybatis.pojo.User; public interface SQLMapper { /** * 根据用户名模糊查询用户信息 */ List<User> getUserByLike(@Param("username") String username); /** * 批量删除 */ int deleteMore(@Param("ids") String ids); /** * 查询指定表中的数据 */ List<User> getUserByTableName(@Param("tableName") String tableName); /** * 添加用户信息 */ void insertUser(User user); }
5.2 映射文件和测试编写
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.atguigu.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>
SQLMapperTest.java
package com.atguigu.mybatis.test; import com.atguigu.mybatis.mapper.SQLMapper; import com.atguigu.mybatis.pojo.User; import com.atguigu.mybatis.utils.SqlSessionUtils; public class SQLMapperTest { @Test public void testGetUserByLike(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SQLMapper mapper = sqlSession.getMapper(SQLMapper.class); List<User> list = mapper.getUserByLike("a"); System.out.println(list); } @Test public void testDeleteMore(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SQLMapper mapper = sqlSession.getMapper(SQLMapper.class); int result = mapper.deleteMore("1,2,3"); System.out.println(result); } @Test public void testGetUserByTableName(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SQLMapper mapper = sqlSession.getMapper(SQLMapper.class); List<User> list = mapper.getUserByTableName("t_user"); System.out.println(list); } @Test public void testInsertUser(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SQLMapper mapper = sqlSession.getMapper(SQLMapper.class); User user = new User(null, "王五", "123", 23, "男", "123@163.com"); mapper.insertUser(user); System.out.println(user); } @Test public void testJDBC() throws Exception { Class.forName(""); Connection connection = DriverManager.getConnection("", "", ""); PreparedStatement ps = connection.prepareStatement("insert", Statement.RETURN_GENERATED_KEYS); ps.executeUpdate(); ResultSet resultSet = ps.getGeneratedKeys(); } }
5.2.1 模糊查询like
/** * 根据用户名进行模糊查询 * @param username * @return java.util.List<com.atguigu.mybatis.pojo.User> * @date 2022/2/26 21:56 */ List<User> getUserByLike(@Param("username") String username);
<!--List<User> getUserByLike(@Param("username") String username);--> <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>
- 其中
select * from t_user where username like "%"#{mohu}"%"是最常用的
@Test public void testGetUserByLike(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SQLMapper mapper = sqlSession.getMapper(SQLMapper.class); List<User> list = mapper.getUserByLike("a"); System.out.println(list); }
5.2.2 批量删除
只能使用${}，如果使用#{}，则解析后的sql语句为delete from t_user where id in ('1,2,3')，这样是将1,2,3看做是一个整体，只有id为1,2,3的数据会被删除。正确的语句应该是delete from t_user where id in (1,2,3)，或者delete from t_user where id in ('1','2','3')
/** * 根据id批量删除 * @param ids * @return int * @date 2022/2/26 22:06 */ int deleteMore(@Param("ids") String ids);
<delete id="deleteMore"> delete from t_user where id in (${ids}) </delete>
//测试类 @Test public void deleteMore() { SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SQLMapper mapper = sqlSession.getMapper(SQLMapper.class); int result = mapper.deleteMore("1,2,3,8"); System.out.println(result); }
5.2.3 动态设置表名
只能使用**${}**，因为表名不能加单引号
/** * 查询指定表中的数据 * @param tableName * @return java.util.List<com.atguigu.mybatis.pojo.User> * @date 2022/2/27 14:41 */ List<User> getUserByTableName(@Param("tableName") String tableName);
<!--List<User> getUserByTableName(@Param("tableName") String tableName);--> <select id="getUserByTableName" resultType="User"> select * from ${tableName} </select>
@Test public void testGetUserByTableName(){ SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SQLMapper mapper = sqlSession.getMapper(SQLMapper.class); List<User> list = mapper.getUserByTableName("t_user"); System.out.println(list); }
5.2.4 添加功能获取自增的主键
使用场景
- t_clazz(clazz_id,clazz_name)
- t_student(student_id,student_name,clazz_id)
- 添加班级信息
- 获取新添加的班级的id
- 为班级分配学生，即将某学的班级id修改为新添加的班级的id
- 在mapper.xml中设置两个属性
- useGeneratedKeys：设置使用自增的主键
- keyProperty：因为增删改有统一的返回值是受影响的行数，因此只能将获取的自增的主键放在传输的参数user对象的某个属性中
/** * 添加用户信息 * @param user * @date 2022/2/27 15:04 */ void insertUser(User user);
<!--void insertUser(User user);--> <insert id="insertUser" useGeneratedKeys="true" keyProperty="id"> insert into t_user values (null,#{username},#{password},#{age},#{sex},#{email}) </insert>
//测试类 @Test public void insertUser() { SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SQLMapper mapper = sqlSession.getMapper(SQLMapper.class); User user = new User(null, "ton", "123", 23, "男", "123@321.com"); mapper.insertUser(user); System.out.println(user); //输出：user{id=10, username='ton', password='123', age=23, sex='男', email='123@321.com'}，自增主键存放到了user的id属性中 }
