一、项目环境搭建
- sql语句准备(本测试采用mysql8.x数据库)
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pass_word` varchar(12) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '密码',
`user_name` varchar(5) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
`other` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
- maven配置
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Spring Boot Mybatis 依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
<!--lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
<scope>provided</scope>
</dependency>
<!-- MySQL 连接驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
<!--整合测试开始-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- junit 5 -->
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>5.5.2</version>
<scope>test</scope>
</dependency>
<!--整合测试结束-->
<!--日志整合开始-->
<!--logback-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-to-slf4j</artifactId>
<version>2.10.0</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>jul-to-slf4j</artifactId>
<version>1.7.25</version>
<scope>compile</scope>
</dependency>
<!--日志整合结束-->
</dependencies>
- yml配置文件
#配置数据源
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/exam?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&allowMultiQueries=true
username: root
password: 123
driver-class-name: com.mysql.cj.jdbc.Driver
#指定mybatis映射文件的地址
mybatis:
mapper-locations: classpath:mapper/*.xml
注意：如果使用到批量更新功能的时候，记得在mysql连接url后面加上：&allowMultiQueries=true
二、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="mybatisLearn.xml.dao.IUserMapper">
<resultMap id="BaseResultMap" type="mybatisLearn.xml.entity.User">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="pass_word" jdbcType="VARCHAR" property="passWord"/>
<result column="user_name" jdbcType="VARCHAR" property="userName"/>
<result column="birthday" jdbcType="TIMESTAMP" property="birthday"/>
<result column="address" jdbcType="VARCHAR" property="address"/>
<result column="other" jdbcType="VARCHAR" property="other"/>
</resultMap>
<!--mybatis模糊查询-->
<sql id="COMMON_SEARCH">
<where>
<if test="id!=null">
and id = #{id }
</if>
<if test="userName!=null">
<!--模糊查询，使用bind标签，防止sql注入-->
<bind name="names" value="'%' + userName + '%'"/>
and user_name like #{names}
</if>
<if test="passWord!=null">
and pass_word = #{passWord}
</if>
<if test="birthday!=null">
and birthday = #{birthday}
</if>
<if test="address!=null">
and address = #{address}
</if>
<if test="other!=null">
and other = #{other}
</if>
</where>
</sql>
<!--按照条件查询所有-->
<select id="getAllByCondition" parameterType="mybatisLearn.xml.entity.User" resultMap="BaseResultMap">
SELECT * FROM user
<include refid="COMMON_SEARCH"/>
</select>
<!--按照条件查询所有-->
<select id="getByCondition" parameterType="mybatisLearn.xml.entity.User" resultMap="BaseResultMap">
SELECT * FROM user
<include refid="COMMON_SEARCH"/>
</select>
<!--新增单个用户-->
<insert id="saveOne" parameterType="mybatisLearn.xml.entity.User">
insert into user
(pass_word,user_name,birthday,address,other)
values (
#{passWord,jdbcType= VARCHAR},
#{userName,jdbcType= VARCHAR},
#{birthday,jdbcType= TIMESTAMP},
#{address,jdbcType= VARCHAR},
#{other,jdbcType= VARCHAR}
)
</insert>
<!--批量新增-->
<insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="true">
<selectKey resultType="int" keyProperty="id" order="AFTER">
SELECT
LAST_INSERT_ID()
</selectKey>
insert into user
(pass_word,user_name,birthday,address,other)
values
<foreach collection="list" item="item" index="index"
separator=",">
(
#{item.passWord},
#{item.userName},
#{item.birthday},
#{item.address},
#{item.other})
</foreach>
</insert>
<!--根据id更新用户-->
<update id="updateById" parameterType="mybatisLearn.xml.entity.User">
UPDATE user
<set>
<if test="passWord!=null">
, pass_word = #{passWord}
</if>
<if test="userName!=null">
, user_name = #{userName}
</if>
<if test="birthday!=null">
, birthday = #{birthday}
</if>
<if test="address!=null">
, address = #{address}
</if>
<if test="other!=null">
, other = #{other}
</if>
</set>
where
id = #{id,jdbcType=INTEGER}
</update>
<!--根据id批量更新-->
<update id="updateBatchById" parameterType="java.util.List" keyProperty="id" useGeneratedKeys="true">
<foreach collection="list" index="index" item="item" open="" separator=";" close="">
update user
<set>
pass_word = #{item.passWord},
user_name = #{item.userName},
birthday = #{item.birthday},
address = #{item.address},
other = #{item.other}
</set>
WHERE id = #{item.id,jdbcType=INTEGER}
</foreach>
</update>
<!--根据条件删除-->
<delete id="deleteByCondition" parameterType="mybatisLearn.xml.entity.User">
delete from user
<where>
<if test="id!=null">
and id = #{id }
</if>
<if test="userName!=null">
and user_name = #{userName}
</if>
<if test="passWord!=null">
and pass_word = #{passWord}
</if>
<if test="birthday!=null">
and birthday = #{birthday}
</if>
<if test="address!=null">
and address = #{address}
</if>
<if test="other!=null">
and other = #{other}
</if>
</where>
</delete>
<!--批量删除-->
<delete id="deleteBatch" parameterType="mybatisLearn.xml.entity.User">
delete from user where id in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.id}
</foreach>
</delete>
</mapper>
普通的诸如CRUD都包含在中间了，可以选择查看学习了解。
网友评论