mybatis相关操作理解_list<student> findallstudents-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_48913290/article/details/125978583

本文介绍了使用MyBatis进行单表查询和多表条件查询的示例，包括XML映射文件中的SQL语句和Java实体类定义。在多表查询中展示了如何根据条件动态拼接SQL。此外，还提到了一个关于插入语句的常见错误，并分享了一些实用的SQL技巧，如当前时间函数和条件判断表达式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

单表查询

xml:


    <select id="findAll" resultMap="BaseResultMap">
        select *
        from student
        where delete_flag=0
    </select>

<mapper namespace="tech.zjs.admin.student.mapper.StudentMapper">
    <resultMap id="BaseResultMap" type="tech.niua.admin.student.domain.Student">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <id column="name" jdbcType="VARCHAR" property="name"/>
        <id column="sex" jdbcType="VARCHAR" property="sex"/>
        <id column="age" jdbcType="INTEGER" property="age"/>
        <id column="birthday" jdbcType="TIME" property="birthday"/>
        <id column="classes_id" jdbcType="INTEGER" property="classesId"/>
        <id column="picture_url" jdbcType="VARCHAR" property="pictureUrl"/>
    </resultMap>

domain:

@Builder
@Data
@TableName("t_student")
public class Student {

        @TableId(type = IdType.AUTO)
       
        private Integer id;

        private String name;

        private String sex;

        private Integer age;

        private LocalDateTime birthday;
  
        private Integer classesId;
       
        private String pictureUrl;

    

}

mapper:

public interface StudentMapper extends BaseMapper<Student> {


    
    List<Student> findAll(Student student);
    
}

service:

public interface IStudentService extends IService<Student> {
    List<Student> findAll(Student student);

}

实现service接口:

@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements IStudentService {
    @Autowired
    private StudentMapper studentMapper;


    @Override
    public List<Student> findAll(Student student) {
        return this.studentMapper.findAll(student);
    }

   

}

controller:

@RestController

@RequestMapping("/student")
public class StudentController {

    @Autowired
    private IStudentService studentService;
 


    /**
    * 查询列表
    *
    * @param currentPage
    * @param pageSize
    * @param student
    * @return
    */
    @PostMapping("/list/{currentPage}/{pageSize}")
    public ResultJson index(@PathVariable Integer currentPage, @PathVariable Integer pageSize,@RequestBody  Student student) {
        PageHelper.startPage(currentPage, pageSize);
        List<Student> students = this.studentService.findAll(student);
        PageInfo<Student> info = new PageInfo<>(students);
        return ResultJson.ok(info);
    }
}

多表查询条件查询：（只是xml的sql语句有所改变）

<select id="pageList" resultMap="BaseResultMap" parameterType="tech.zjs.admin.student.domain.Student">
        select t_student.*, t_classes.class_name
        from t_student
        left join t_classes on
        t_student.classes_id = t_classes.id
        where 1=1
        <if test="name!=null and name!=''">
            and t_student.name=#{name}
        </if>

        <if test="sex!=null and sex!=''">
            and sex=#{sex}
        </if>

        <if test="age!=null and age!=''">
            and age=#{age}
        </if>

        <if test="classesId!=null and classesId!=''">
            and classes_id=#{classesId}
        </if>
    </select>

这里有一个再插入语句中的坑：

正确写法：

 <!--添加某个企业的分类-->
    <insert id="insert" parameterType="tech.niua.admin.enterprise.domain.Student">
        insert into t_student (name,sex) values(#{name},#{sex})
    </insert>

错误写法（插入的都是空）

 <!--添加某个企业的分类-->
    <insert id="insert" parameterType="tech.niua.admin.enterprise.domain.Student">
        insert into t_student (name,sex) values(name = #{name,jdbcType=VARCHAR},
         sex = #{sex,jdbcType=VARCHAR})
    </insert>

总结一些小的sql技巧

now()

获取当前时间

 (case when t_enterprise.audit_status=0 then 0 else 2 end) remark

先输出0再输出2 ，顺序输出

模糊查询

like concat('%','${name}','%')