MyBatis笔记2（查询）

最新推荐文章于 2024-04-23 15:40:53 发布

ㅤ 踢足球的程序员

最新推荐文章于 2024-04-23 15:40:53 发布

阅读量362

点赞数

文章标签： mybatis java mysql

本文链接：https://blog.csdn.net/asskjj/article/details/127683320

版权

查询一个实体类对象

<select id="getUserById" resultType="UserInfo">
     select * from user_info where user_id = #{id}
    </select>

public UserInfo getUserById(@Param("id") String id);

查询一个List集合

当查询记录为多条时不能使用实体类接收，否则会抛出TooManyResultsException异常

但是当查询记录为单条时可以使用List集合。（杀鸡可以用牛刀）

  <select id="getAllUser" resultType="UserInfo">
        select * from user_info
    </select>

  public List<UserInfo> getAllUser();

查询单个数据

在Mybatis中，对于java常用类型都设置了别名。不区分大小写Interger,INT.....

    <select id="getCount" resultType="java.lang.Integer">
        select count(*) from user_info
    </select>

public Integer getCount();

查询一条数据为map集合

将查询结果封装为Map

    <select id="getUserById2Map" resultType="map">
        select * from user_info where user_id = #{id}
    </select>

    public Map<String,Object> getUserById2Map(@Param("id") String id);

查询结果为键值对（null忽略）

DEBUG 11-04 10:25:11,017 ==>  Preparing: select * from user_info where user_id = ? (BaseJdbcLogger.java:137) 
DEBUG 11-04 10:25:11,018 ==> Parameters: 1(String) (BaseJdbcLogger.java:137) 
DEBUG 11-04 10:25:11,020 <==      Total: 1 (BaseJdbcLogger.java:137) 
{update_time=2022-08-31 17:26:41.0, create_time=2022-08-31 17:26:37.0, user_id=1, use_password=1, usename=1}

将多条记录查询为Map

情况1：

    <select id="getAllUser2Map" resultType="map">
        select * from user_info
    </select>

  public List<Map<String,Object>> getAllUser2Map();

将每一条记录封装成map，再将多个map装入List中。

情况2：

将表中的数据以map集合的方式查询，一条数据对应一个map；若有多条数据，就会产生多个map集合，并且最终要以一个map的方式返回数据，此时需要通过@MapKey注解设置map集合的键，值是每条数据所对应的 map集合

    <select id="getAll2Map" resultType="map">
        select * from user_info
    </select>

    @MapKey("user_id")
    public Map<String,Object> getAll2Map();

查询结果

DEBUG 11-04 10:47:14,918 ==>  Preparing: select * from user_info (BaseJdbcLogger.java:137) 
DEBUG 11-04 10:47:14,919 ==> Parameters:  (BaseJdbcLogger.java:137) 
DEBUG 11-04 10:47:14,930 <==      Total: 43 (BaseJdbcLogger.java:137) 
{1={update_time=2022-08-31 17:26:41.0, create_time=2022-08-31 17:26:37.0, user_id=1, use_password=1, usename=1}, 66={user_id=66, usename=111}, 67={update_time=2022-11-03 00:24:36.0, create_time=2022-11-03 00:24:36.0, user_id=67, use_password=111, usename=wyf}, 70={user_id=70, usename=111}, 71={update_time=2022-11-03 00:28:04.0, create_time=2022-11-03 00:28:04.0, user_id=71, use_password=111, usename=wyf}, 12={update_time=2022-10-29 22:10:14.0, user_id=12, use_password=123, usename=ybc}, 13={update_time=2022-09-03 09:26:50.0, create_time=2022-09-03 09:26:48.0, user_id=13, use_password=1, usename=dsd}, 14={update_time=2022-09-03 09:30:48.0, create_time=2022-09-03 09:30:48.0, user_id=14, usename=dss}, 15={update_time=2022-09-03 09:31:35.0, create_time=2022-09-03 09:31:35.0, user_id=15, usename=ds}, 16={update_time=2022-09-03 09:31:43.0, create_time=2022-09-03 09:31:43.0, user_id=16, usename=ds}, 17={update_time=2022-09-03 09:31:43.0, create_time=2022-09-03 09:31:43.0, user_id=17, usename=ds}, 18={update_time=2022-09-03 09:31:43.0, create_time=2022-09-03 09:31:43.0, user_id=18, usename=ds}, 19={update_time=2022-09-03 09:31:43.0, create_time=2022-09-03 09:31:43.0, user_id=19, usename=ds}, 20={update_time=2022-09-03 09:31:43.0, create_time=2022-09-03 09:31:43.0, user_id=20, usename=ds}, 22={update_time=2022-09-03 09:58:14.0, create_time=2022-09-03 09:33:32.0, user_id=22, use_password=null, usename=ww}, 23={update_time=2022-09-03 09:34:00.0, create_time=2022-09-03 09:34:00.0, user_id=23, use_password=dd, usename=ww}, 24={update_time=2022-09-03 09:59:09.0, create_time=2022-09-03 09:59:09.0, user_id=24, use_password=, usename=ww}, 25={update_time=2022-09-03 10:00:09.0, create_time=2022-09-03 10:00:09.0, user_id=25, usename=ww}, 27={user_id=27, usename=111}, 28={user_id=28, usename=111}, 29={user_id=29, usename=111}, 32={user_id=32, usename=111}, 33={user_id=33, usename=111}, 34={user_id=34, usename=111}, 35={user_id=35, usename=111}, 37={user_id=37, usename=111}, 39={user_id=39, usename=111}, 41={user_id=41, usename=111}, 42={user_id=42, usename=111}, 43={user_id=43, usename=111}, 44={user_id=44, usename=111}, 45={user_id=45, usename=111}, 46={user_id=46, usename=111}, 50={user_id=50, usename=111}, 51={update_time=2022-11-03 00:08:59.0, create_time=2022-11-03 00:08:59.0, user_id=51, use_password=111, usename=wyf}, 54={user_id=54, usename=111}, 55={update_time=2022-11-03 00:19:30.0, create_time=2022-11-03 00:19:30.0, user_id=55, use_password=111, usename=wyf}, 58={user_id=58, usename=111}, 59={update_time=2022-11-03 00:20:23.0, create_time=2022-11-03 00:20:23.0, user_id=59, use_password=111, usename=wyf}, 60={user_id=60, usename=111}, 61={update_time=2022-11-03 00:21:44.0, create_time=2022-11-03 00:21:44.0, user_id=61, use_password=111, usename=wyf}, 62={user_id=62, usename=111}, 63={update_time=2022-11-03 00:22:22.0, create_time=2022-11-03 00:22:22.0, user_id=63, use_password=111, usename=wyf}}

数据库字段

模糊查询

   <select id="getUserByLike" resultType="UserInfo">
        select * from user_info where usename like '%${mohu}%'
    </select>

    <select id="getUserByLike" resultType="UserInfo">
select  * from user_info where usename like concat('%',#{mohu},'%')
    </select>

  <select id="getUserByLike" resultType="UserInfo">
        select * from user_info where usename like "%"#{mohu}"%"
    </select>