MyBatis

MyBatis

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL 、存储过程以及高级映射。 MyBatis 免除了几乎 所有的 JDBC 代码以及设置参数和获取结果集的工作。 MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO （ Plain Old Java Objects ，普通老式 Java 对象）为数据库中的记录。

开发步骤

1 、添加依赖 pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns = "http://maven.apache.org/POM/4.0.0"

xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation = "http://maven.apache.org/POM/4.0.0

http://maven.apache.org/xsd/maven-4.0.0.xsd" >

<modelVersion> 4.0.0 </modelVersion>

<groupId> com.yan </groupId>

<artifactId> 20221201 </artifactId>

<version> 1.0-SNAPSHOT </version>

<packaging> war </packaging>

<description> 雇员管理系统 </description>

<properties>

<maven.compiler.source> 8 </maven.compiler.source>

<maven.compiler.target> 8 </maven.compiler.target>

</properties>

<dependencies>

<!-- MyBatis 框架 -->

<dependency>

<groupId> org.mybatis </groupId>

<artifactId> mybatis </artifactId>

<version> 3.5.11 </version>

</dependency>

<!-- 数据库驱动 -->

<dependency>

<groupId> mysql </groupId>

<artifactId> mysql-connector-java </artifactId>

<version> 8.0.31 </version>

</dependency>

<!-- Servlet 开发所需要的依赖 -->

<dependency>

<groupId> javax.servlet </groupId>

<artifactId> javax.servlet-api </artifactId>

<version> 4.0.1 </version>

<scope> provided </scope>

</dependency>

<!-- jsp 开发所需要的依赖 -->

<dependency>

<groupId> javax.servlet.jsp </groupId>

<artifactId> javax.servlet.jsp-api </artifactId>

<version> 2.3.3 </version>

<scope> provided </scope>

</dependency>

2 、定义数据库表

3 、根据表结构定义对应的实体类

<!-- JSTL 标准标签库 -->

<dependency>

<groupId> javax.servlet </groupId>

<artifactId> jstl </artifactId>

<version> 1.2 </version>

</dependency>

<!-- 为了简化 javabean 的编写所引入的工具 -->

<dependency>

<groupId> org.projectlombok </groupId>

<artifactId> lombok </artifactId>

<version> 1.18.24 </version>

<scope> provided </scope>

</dependency>

<!-- 用于实现单元测试的支持 -->

<dependency>

<groupId> junit </groupId>

<artifactId> junit </artifactId>

<version> 4.13.2 </version>

<scope> test </scope>

</dependency>

</dependencies>

<build>

<finalName> emp </finalName>

<plugins>

<plugin>

<groupId> org.eclipse.jetty </groupId>

<artifactId> jetty-maven-plugin </artifactId>

<version> 9.4.11.v20180605 </version>

<configuration>

<scanIntervalSeconds> 10 </scanIntervalSeconds>

<httpConnector>

<port> 8080 </port>

</httpConnector>

<webApp>

<contextPath> /emp </contextPath>

</webApp>

</configuration>

</plugin>

</plugins>

</build>

</project>

create table if not exists t_dept(

id bigint primary key auto_increment,

dept_name varchar ( 20 ) not null ,

location varchar ( 50 )

)engine=innodb default charset utf8mb4;

4 、定义映射元文件，用于指定对应关系和对应的 SQL 语句

@Data

public class Dept implements Serializable {

private Long id ;

private String deptName ;

private String location ;

}

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace = "com.yan.dao.DeptMapper" >

<!-- 结果集映射 resultSet 转换为 bean 对象 -->

<resultMap id = "baseMapper" type = "com.yan.entity.Dept" >

<id property = "id" column = "id" jdbcType = "BIGINT" />

<result property = "deptName" column = "dept_name" jdbcType = "VARCHAR" />

<result property = "location" column = "location" jdbcType = "VARCHAR" />

</resultMap>

<!-- 命名块 -->

<sql id = "columns" >

id,dept_name,location

</sql>

<!-- 查询对应的 sql 语句 -->

<select id = "selectByExample" parameterType = "DeptBean"

resultMap = "baseMapper" >

select <include refid = "columns" /> from t_dept where 1=1

<!-- 动态 sql 语句 -->

<if test = "id!=null" >

and id=#{id}

</if>

<if test = "deptName!=null and deptName!=''" >

and dept_name = #{deptName}

</if>

<if test = "location!=null and location!=''" >

and location like #{location}

</if>

</select>

<select id = "selectByMap" parameterType = "map" resultMap = "baseMapper" >

select <include refid = "columns" /> from t_dept where 1=1

<if test = "noId!=null" >

and id != #{noId}

</if>

<if test = "deptName!=null" >

and dept_name=#{deptName}

</if>

</select>

<select id = "loadById" parameterType = "long" resultMap = "baseMapper" >

select <include refid = "columns" /> from t_dept where id=#{id}

</select>

<!-- 插入对应的 SQL 语句 -->

<insert id = "insertDept" parameterType = "DeptBean" useGeneratedKeys = "true"

keyProperty = "id" >

insert into t_dept(dept_name

<if test = "location!=null" >

trim 标签的用法

如果 state 、 title 和 author 属性均为 null ，则生成的 SQL 语句语法错误

,location

</if>

) values(#{deptName}

<if test = "location!=null" >

,#{location}

</if>

)

</insert>

<delete id = "deleteById" parameterType = "long" >

delete from t_dept where id=#{id}

</delete>

<update id = "updateById" parameterType = "DeptBean" >

update t_dept set id=#{id}

<if test = "deptName!=null" >

,dept_name=#{deptName}

</if>

<if test = "location!=null" >

,location=#{location}

</if>

where id=#{id}

</update>

</mapper>

<select id = "findActiveBlogLike" resultType = "Blog" >

SELECT * FROM BLOG

WHERE

<if test = "state != null" >

state = #{state}

</if>

<if test = "title != null" >

AND title like #{title}

</if>

<if test = "author != null and author.name != null" >

AND author_name like #{author.name}

</if>

</select>

<select id = "findActiveBlogLike" resultType = "Blog" >

SELECT * FROM BLOG

<where>

<if test = "state != null" >

state = #{state}

</if>

<if test = "title != null" >

AND title like #{title}

</if>

<if test = "author != null and author.name != null" >

AND author_name like #{author.name}

</if>

</where>

</select>

where 元素只会在子元素返回任何内容的情况下才插入 WHERE 子句。如果 state 、 title 和 author 均

为 null 时，则不会生成 where 子句 where标签另外提供功能，就是子句的开头为 AND 或 OR ， where 元素也会将它们去除。例如 state 为null ，但是 title 不为空

choose 、 when 、 otherwise 类似于 java 中的开关分支语句

set 标签用于动态生成 update 语句中的 set 部分

set 元素可以用于动态包含需要更新的列，忽略其它不更新的列

<select id = "findActiveBlogLike" resultType = "Blog" >

SELECT * FROM BLOG

<trim prefix = "WHERE" prefixOverrides = "AND |OR " > 如果 trim 标签体不为空，则自动在

标签体内容的前部添加前缀 WHERE, 如果标签体内容以 and 或者 or 开头，则自动剔除开头的 and 或者 or 。 另外处理尾部的suffix 或者 suffixOverrides

<if test = "state != null" >

state = #{state}

</if>

<if test = "title != null" >

AND title like #{title}

</if>

<if test = "author != null and author.name != null" >

AND author_name like #{author.name}

</if>

</trim>

</select>

<select id = "selectByExample" parameterType = "DeptBean"

resultMap = "baseMapper" >

select <include refid = "columns" /> from t_dept

<where>

<choose>

<when test = "id != null" >

AND id like #{id}

</when>

<when test = "deptName != null" >

AND dept_name like #{deptName}

</when>

<otherwise>

AND location like ' 中国 %'

</otherwise>

</choose>

</where>

</select>

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）

foreach

对集合进行遍历

批量删除操作 delete from t_dept WHERE id in( 1 , 2 , 3 , 4 )

批量插入操作 insert into t_dept values (?,?,?) , (?,?,?) , (?,?,?) , (?,?,?) ,

(?,?,?)

<update id = "updateAuthorIfNecessary" >

update Author

<set>

<if test = "username != null" > username=#{username}, </if>

<if test = "password != null" > password=#{password}, </if>

<if test = "email != null" > email=#{email}, </if>

<if test = "bio != null" > bio=#{bio} </if>

</set>

where id=#{id}

</update>

<delete id = "deleteByIds" parameterType = "list" >

delete from t_dept

<where>

<foreach collection = "list" item = "kk" open = "id in(" close = ")"

separator = "," nullable = "true" >

${kk}

</foreach>

</where>

</delete>

<insert id = "insertCollection" parameterType = "list" >

insert into t_dept

<foreach collection = "list" open = "values" separator = "," item = "dd" >

(#{dd.id},#{dd.deptName},#{dd.location})

</foreach>

</insert>