Mybatis——查询数据

查询操作

根据用户id查询单条记录，在映射器接口(UserMapper)中定义如下方法：

package org.example.mapper;

import org.example.demo.User;

import java.util.List;

public interface UserMapper {
    //根据id查询User
    User selectUserById(Integer userId);
}

当实体类的属性名与数据库表的字段名一一对应时，映射代码如下所示：select元素用于映射查询语句：UserMapper.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="org.example.mapper.UserMapper">
    <select id="selectUserById" resultMap="userMap">
        select * from t_user where user_id = #{userId};
    </select>
</mapper>

编写测试代码 :

package org.example;

import org.apache.ibatis.session.SqlSession;
import org.example.demo.Order;
import org.example.demo.User;
import org.example.demo2.Employee;
import org.example.demo2.SalaryEmployee;
import org.example.mapper.EmployeeMapper;
import org.example.mapper.UserMapper;
import org.example.utils.MybatisUtil;

import java.util.List;

public class test {
    public static void main(String[] args) {
        test test1 = new test();
        test1.selectUserById();
    }

    public void selectUserById() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = mapper.selectUserById(3);
        System.out.println(user);

        sqlSession.commit();
        sqlSession.close();
    }
}

得到如下结果：

当实体类属性名与表字段不一致时，可以使用resultMap元素映射其对应关系 

resultMap是一种很重要的配置结果映射的方法

id属性：必填，是结果映射的唯一标识，与select元素中resultMap属性的值一致

type属性：必填，用于指定查询结果所映射到的Java对象类型

id子元素：配置id对应的column（字段名）和property（属性名）

result子元素：配置普通结果对应的字段名和属性名

<?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="org.example.mapper.UserMapper">

    <resultMap id="userMap" type="org.example.demo.User">
        <id property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="password" column="password"/>
        <association property="cart" column="user_id" select="findCartbyUserId"
        fetchType="lazy"/>
    </resultMap>

    <select id="selectUserById" resultMap="userMap">
        select * from t_user where user_id = #{userId};
    </select>
</mapper>

当实体类属性与表字段不一致时，也可以通过设置别名进行映射 

<select id="selectAllUsers" resultType="com.mybatis.entity.User">
    select id,
        user_name userName,
        password
    from USER
</select>

 模糊查询like

在接口UserMapper中：

package org.example.mapper;

import org.example.demo.User;

import java.util.List;

public interface UserMapper {

    List<User> selectAllUser(String userName);
}

在UserMapper.xml中 ：

 表达式: user_name like"%"#{name}"%" #起到占位符的作用

​
<?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="org.example.mapper.UserMapper">

    <resultMap id="userMap" type="org.example.demo.User">
        <id property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="password" column="password"/>
        <association property="cart" column="user_id" select="findCartbyUserId"
        fetchType="lazy"/>
    </resultMap>

    ​<select id="selectAllUser" resultMap="userMap">
        select * from t_user where user_name like "%"#{userName}"%";
    </select>
</mapper>

 测试类：

public void selectAllUser() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> users = mapper.selectAllUser("l");
        System.out.println(users);

        for (User user : users) {
            System.out.println(user);
        }

        sqlSession.commit();
        sqlSession.close();
    }