mybatis单表增删改查方法

一、dao

package com.qiu.dao;

import com.qiu.entity.Student;

import java.util.List;

/**
* 描述：学生数据访问接口
*
* @author: qxd
* @date: 2025/11/29 22:11
* @version: 1.0.0
*/
public interface StudentDao{

/**
* 查询所有学生
* @return 学生列表
*/
List<Student> selectAll();

/**
* 根据ID查询学生
* @param id 学生ID
* @return 学生对象
*/
Student selectById(Integer id);

/**
* 根据邮箱查询学生
* @param email 学生邮箱
* @return 学生对象
*/
Student selectByEmail(String email);

/**
* 插入学生记录
* @param student 学生对象
* @return 影响行数
*/
int insert(Student student);

/**
* 更新学生记录
* @param student 学生对象
* @return 影响行数
*/
int update(Student student);

/**
* 根据ID删除学生记录
* @param id 学生ID
* @return 影响行数
*/
int deleteById(Integer id);

/**
* 根据姓名查询学生
* @param name 学生姓名
* @return 学生列表
*/
List<Student> selectByName(String name);
/**
* 批量插入学生数据
* @param students 学生列表
* @return 插入记录数
*/
int batchInsert(List<Student> students);

/**
* 根据性别查询学生
* @param name 学生性别
* @return 学生列表
*/
List<Student> selectByGender(String name);
}

二、mapper.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.qiu.dao.StudentDao">

<!– 结果映射 –>
<resultMap id="StudentResultMap" type="com.qiu.entity.Student">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<result column="phone" property="phone"/>
<result column="address" property="address"/>
<result column="create_time" property="createTime"/>
<result column="update_time" property="updateTime"/>
</resultMap>

<!– 表名 –>
<sql id="tableName">student</sql>

<!– 字段列表 –>
<sql id="baseColumns">
id, name, age, gender, email, phone, address, create_time, update_time
</sql>

<!– 查询所有学生 –>
<select id="selectAll" resultMap="StudentResultMap">
SELECT <include refid="baseColumns"/>
FROM <include refid="tableName"/>
</select>

<!– 根据ID查询学生 –>
<select id="selectById" parameterType="int" resultMap="StudentResultMap">
SELECT <include refid="baseColumns"/>
FROM <include refid="tableName"/>
WHERE id = #{id}
</select>

<!– 根据邮箱查询学生 –>
<select id="selectByEmail" parameterType="string" resultMap="StudentResultMap">
SELECT <include refid="baseColumns"/>
FROM <include refid="tableName"/>
WHERE email = #{email}
</select>

<!– 插入学生记录 –>
<insert id="insert" parameterType="com.qiu.entity.Student">
INSERT INTO <include refid="tableName"/>
(name, age, gender, email, phone, address)
VALUES (#{name}, #{age}, #{gender}, #{email}, #{phone}, #{address})
</insert>

<!– 更新学生记录 –>
<update id="update" parameterType="com.qiu.entity.Student">
UPDATE <include refid="tableName"/>
<set>
<if test="name != null and name != ''">name = #{name},</if>
<if test="age != null">age = #{age},</if>
<if test="gender != null and gender != ''">gender = #{gender},</if>
<if test="email != null and email != ''">email = #{email},</if>
<if test="phone != null and phone != ''">phone = #{phone},</if>
<if test="address != null and address != ''">address = #{address},</if>
update_time = CURRENT_TIMESTAMP
</set>
WHERE id = #{id}
</update>

<!– 根据ID删除学生记录 –>
<delete id="deleteById" parameterType="int">
DELETE FROM <include refid="tableName"/>
WHERE id = #{id}
</delete>

<!– 根据姓名查询学生 –>
<select id="selectByName" parameterType="string" resultMap="StudentResultMap">
SELECT <include refid="baseColumns"/>
FROM <include refid="tableName"/>
WHERE 1 = 1
<if test="name != null and name != ''">
AND name = #{name}
</if>
</select>

<insert id="batchInsert">
INSERT INTO student (name, age, gender, email, phone, address, create_time, update_time)
VALUES
<foreach collection="list" item="student" separator=",">
(#{student.name}, #{student.age}, #{student.gender}, #{student.email},
#{student.phone}, #{student.address}, #{student.createTime}, #{student.updateTime})
</foreach>
</insert>

<!– 根据性别查询学生 –>
<select id="selectByGender" parameterType="string" resultMap="StudentResultMap">
SELECT <include refid="baseColumns"/>
FROM <include refid="tableName"/>
WHERE 1 = 1
<if test="gender != null and gender == 0">
AND gender = '男'
</if>
<if test="gender != null and gender != ''">
AND gender = '女'
</if>
</select>
</mapper>

三、详细代码见仓库项目 demo-springboot-mybatis

地址：https://gitee.com/qiuxiaodong/demo.git