Mybatis
Mybatis
MyBatis 本是apache的一个开源项目iBatis 2010年这个项目由apache software foundation 迁移到了google code,并且改名为MyBatis 。2013年11月迁移到Github。iBATIS一词来源于“internet”和“abatis”的组合,是一个基于Java的持久层框架。iBATIS提供的持久层框架包括SQL Maps和Data Access Objects(DAOs)
MyBatis 是一款优秀的持久层框架,它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息,将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。
简介使用
<!--全局配置文件 mybatis-config.xml-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="org/mybatis/example/BlogMapper.xml"/>
<!-- 对应配置文件路径 * -->
</mappers>
</configuration>
<!-- * org/mybatis/example/BlogMapper.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.mybatis.example.dao.BlogMapper">
<!-- 对应接口全类名位置 ## -->
<!-- 对应接口方法 ### -->
<select id="selectBlog" resultType="org.mybatis.example.bean.Blog">select * from Blog where id = #{id}</select>
</mapper>
//## org.mybatis.example.dao.BlogMapper
public interface BlogMapper{
// 对应mapper中方法 ###
public Blog selectBlog(Integer id);
}
Mybatis底层与数据库交互是使用SqlSession完成的,SqlSession非线程安全,每次使用应当获取新对象,mapper接口的实现由mybatis生成一个代理对象。
public void test(){
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = mapper.selectBlog(1);
} finally { session.close(); }
}
全局配置文件
properties标签
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties" url="网络路径/本地路径"></properties>
<!--resource:类路径 url:网络路径/本地路径 -->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
</configuration>
#db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mydql://localhost:3306/db
jdbc.username=username
jdbc.password=password
settings标签
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="mapUnderscoreToCamelCaseEnables " value="true" />
<!-- 驼峰命名适配 -->
<setting name="jdbcTypeForNull" value="null"/>
<!-- 空值映射为null -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 懒加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
<!-- 按需加载 -->
</settings>
</configuration>
typeAliases标签
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<typeAlias type="org.mybatis.example.bean.Blog" alias="blog"></typeAlias>
<!-- mapper中就不用写全类名了,不写alias即默认为类名-->
<package name="org.mybatis.example.bean"/>
<!--当前包及其子包自动别名-->
</typeAliases>
</configuration>
<?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.mybatis.example.dao.BlogMapper">
<!-- blog 别名使用-->
<select id="selectBlog" resultType="blog">select * from Blog where id = #{id}</select>
</mapper>
//通过@Alias注解使用别名
//批量扫描注解优先
@Alias("blog")
public class Blog{
private Integer id;
...
}
Alias(默认别名) | Mapped Type |
---|---|
_long | long |
_short | short |
_int | int |
_integer | int |
_double | double |
_float | float |
_boolean | boolean |
string | String |
byte | Byte |
long | Long |
short | Short |
int | Integer |
integer | Integer |
double | Double |
float | Float |
boolean | Boolean |
date | Date |
decimal | BigDecimal |
bigdecimal | BigDecimal |
map | Map |
plugins插件
在四大对象执行前后进行拦截,原理为动态代理。
- Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
- ParameterHandler (getParameterObject, setParameters)
- ResultSetHandler (handleResultSets, handleOutputParameters)
- StatementHandler (prepare, parameterize, batch, update, query)
environments标签
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development"> <!--指定默认使用环境来快速切换环境-->
<environment id="env">
<transactionManager type="JDBC"/> <!-- 事务管理器 type: JDBC|MANAGED 自定义 -->
<dataSource type="POOLED"> <!-- 数据源配置 type:UNPOOLED|POOLED|JNDI 自定义-->
<property name="driver" value="${oracle.driver}"/>
<property name="url" value="${oracle.url}"/>
<property name="username" value="${oracle.username}"/>
<property name="password" value="${oracle.password}"/>
</dataSource>
</environment>
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
</configuration>
databaseIdProvider标签
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="env">
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="oracle"/>
<property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>
</configuration>
<?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.mybatis.example.dao.BlogMapper">
<!-- 发送带oracle的查询语句 -->
<select id="selectBlog" resultType="org.mybatis.example.bean.Blog">
select * from Blog where id = #{id}</select>
<select id="selectBlog" resultType="org.mybatis.example.bean.Blog"
databsesId="oracle">select * from Blog where id = #{id}</select>
<select id="selectBlog" resultType="org.mybatis.example.bean.Blog"
databsesId="mysql">select * from Blog where id = #{id}</select>
</mapper>
mapper标签
<!--全局配置文件 mybatis-config.xml-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<mappers>
<mapper url=""></mapper>
<!-- 磁盘路径 -->
<mapper resource="org/mybatis/example/BlogMapper.xml"/>
<!-- 类路径 -->
<mapper class="org.mybatis.example.dao.BlogMapperAnnotation"/>
<!--1. mapper和接口 同名同路径-->
<!--2. 添加注解方法的接口-->
<package name="org.mybatis.example.dao"/>
<!-- 批量扫描 xml 接口 同包 -->
</mappers>
</configuration>
//org.mybatis.example.dao.BlogMapperAnnotation
public interface BlogMapperAnnotation{
// 对应mapper中方法 ###
@Select("select * from Blog where id = #{id}")
public Blog selectBlog(Integer id);
}
映射文件
CRUD
//## org.mybatis.example.dao.BlogMapper
public interface BlogMapper{
// 对应mapper中方法 ###
public Blog selectBlog(Integer id);
public boolean insertdBlog(Blog blog);
public Integer updateBlog(Blog blog);
public boolean deleteBlog(Integer id);
//自动返回 Integer 、Long、Boolean 类型
}
<!-- * org/mybatis/example/BlogMapper.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.mybatis.example.dao.BlogMapper">
<select id="selectBlog" resultType="org.mybatis.example.bean.Blog">
select * from Blog where id = #{id}
</select>
<insert id="insertdBlog" parameterType ="org.mybatis.example.bean.Blog">
insert into Blog...values(#{属性名},#{属性名})
</insert>
<update id="updateBlog" parameterType ="org.mybatis.example.bean.Blog">
update Blog set ... 字段 = #{属性名} where 字段 = #{属性名}
</update>
<delete id="deleteBlog">
delete from Blog where id = #{id}
</delete>
</mapper>
自增主键
<!-- * org/mybatis/example/BlogMapper.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.mybatis.example.dao.BlogMapper">
<!-- mysql 等支持 自增主键 数据库-->
<insert id="insertdBlog" parameterType ="org.mybatis.example.bean.Blog"
useGeneratedKeys="true" keyProperty="#{id}" databaseId="mysql">
<!-- 使用自增主键策略 插入数据库操作完成后原插入对象的id属性将被赋值-->
insert into Blog...values(#{属性名},#{属性名})
</insert>
<!-- Oracle 从序列中获取 111-->
<insert id="insertdBlog" parameterType ="org.mybatis.example.bean.Blog"
databaseId="oracle">
<selectKey KeyProperty="#{id}" order="BEFORE" resultTyoe"Integer">
<!-- 使用查询序列 插入数据库操作完成后原插入对象的id属性将被赋值-->
select Blog_sql.nextval from dual
</selectKey>
insert into Blog...values(#{id},#{属性名},#{属性名})
</insert>
<!-- Oracle 从序列中获取 222 多条问题 -->
<insert id="insertdBlog" parameterType ="org.mybatis.example.bean.Blog"
databaseId="oracle">
<selectKey KeyProperty="#{id}" order="AFTER" resultTyoe"Integer">
<!-- 使用查询序列 插入数据库操作完成后原插入对象的id属性将被赋值-->
select Blog_sql.currval from dual
</selectKey>
insert into Blog...values(Blog_sql.nextval,#{属性名},#{属性名})
</insert>
</mapper>
参数处理
多参数时,参数会封装成map,可以使用注解指定参数的关键字(key)。
//## org.mybatis.example.dao.BlogMapper
public interface BlogMapper{
// 对应mapper中方法 ###
public Blog selectBlogById(Integer id);
public Blog selectBlogByIdAndName(@Param("id")Integer id,@Param("name")String name);
public Blog selectBlogByPojo(Blog blog);
public Blog selectBlogByMap(Map<String,Object> map);
public Blog selectBlogByCollection(List<Integer> list);
}
Page{
int index;
int size;
List<Blog> list;
}
<!-- * org/mybatis/example/BlogMapper.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.mybatis.example.dao.BlogMapper">
<!-- 单个参数 -->
<!-- 当只有单个参数时,不会有任何处理 #{任意填写} 都可以取出参数-->
<select id="selectBlogById" resultType="org.mybatis.example.bean.Blog">
select * from Blog where id = #{id}
</select>
<!-- 多个参数 -->
<!-- 未指定参数名 -->
<!-- 使用多个参数时,入参会被处理,参数会被封装为一个map-->
<!-- 取值所用的key为 [ param1 ... paramN 0... n ]-->
<select id="selectBlogByIdAndName" resultType="org.mybatis.example.bean.Blog">
select * from Blog where id = #{param1} and name = #{param2}
</select>
<!-- 使用@Param("") 注解指明接口中的参数后可以直接取用-->
<!-- 原取参数规则仍旧适用 -->
<select id="selectBlogByIdAndName" resultType="org.mybatis.example.bean.Blog">
select * from Blog where id = #{id} and name = #{name}
</select>
<!-- 传入pojo -->
<!-- 直接取用即可 -->
<!-- 多参数包含对象时 param2.id 或注解后 blog.id -->
<select id="selectBlogByPojo" resultType="org.mybatis.example.bean.Blog">
select * from Blog where id = #{id} and name = #{name}
</select>
<!-- 传入 map -->
<!-- 直接取用 按照存入map时的key 即取用的 key 和 存入map时 key 相同 -->
<select id="selectBlogByMap" resultType="org.mybatis.example.bean.Blog">
select * from Blog where id = #{id} and name = #{name}
</select>
<!-- 传入集合类型 -->
<!-- 取出第一个参数 集合类型取参数使用 collection[0] 数组类型取参数使用 list[0] -->
<select id="selectBlogByMap" resultType="org.mybatis.example.bean.Blog">
select * from Blog where id = #{list[0]}
</select>
</mapper>
$与#区别
<?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.mybatis.example.dao.BlogMapper">
<select id="selectBlog" resultType="org.mybatis.example.bean.Blog">
select * from Blog where id = #{id} and name = ${name}</select>
<!-- 执行Sql:select * from Blog where id = ? and name = blog_01 -->
<!-- #{} 采用预编译形式 可以防止sql注入发生 ${} 采用字符串拼串方式 可能被注入 -->
select * from ${year}_Blog where id =#{id} //分表
select * from Blog order by ${name} ${order} where id = #{id} //排序
</mapper>
#{} 取值时可以对参数进行限定javaType、jdbcType、mode、numbericScale、resultMap、typeHandler、jdbcTypeName
<!-- 部分数据库在字段为null 可能无法识别mybatis传入的null值 -->
<insert id="insertdBlog" parameterType ="org.mybatis.example.bean.Blog">
insert into Blog values(#{id},#{name,jdbcType=Null})
<!-- name 为空 Oracle 数据库正常 默认映射为 jdbcType.OTHER -->
</insert>
返回值处理
public interface BlogMapper{
public List<Blog> selectBlogByName(String name);
public Map<Stirng,Object> selectBlogMap(Integer id);
@MapKey("id")
public Map<Stirng,Blog> selectBlogMaps(String 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.mybatis.example.dao.BlogMapper">
<select id="selectBlogByName" resultType="org.mybatis.example.bean.Blog">
<!-- 返回为列表 返回类型为集合元素类型 Blog-->
select * from Blog where name like #{name}
</select>
<select id="selectBlogMap" resultType="map">
<!-- 返回值为map 返回类型为 map -->
select * from Blog where id = #{id}
</select>
<select id="selectBlogByName" resultType="org.mybatis.example.bean.Blog">
<!-- 返回为map<String,Blog> 返回类型为集合元素类型 Blog-->
<!-- 接口使用注解修饰 @MapKey("id") 标注map的key使用哪个字段 -->
select * from Blog where name like #{name}
</select>
</mapper>
resultMap
自定义结果集
<!-- * org/mybatis/example/BlogMapper.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.mybatis.example.dao.BlogMapper">
<!--自定义结果规则 resultMap -->
<resultMap type="org.mybatis.example.bean.Blog" id="CustomizeBlog">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result colume="regist_time" property="reistTime"/>
</resultMap>
<select id="selectBlog" resultMap="CustomizeBlog">
select * from Blog where id = #{id}
</select>
</mapper>
级联查询
<!-- * org/mybatis/example/BlogMapper.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.mybatis.example.dao.BlogMapper">
<!--级联查 属性 定义结果规则 resultMap -->
<resultMap type="org.mybatis.example.bean.Blog" id="CustomizeBlog">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="group_id" property="group.id">
<result column="group_name" property="group.name">
</resultMap>
<select id="selectBlog" resultMap="CustomizeBlog">
select * from Blog b,Group g where b.id = g.id and b.id = #{id}
</select>
</mapper>
association
<!-- * org/mybatis/example/BlogMapper.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.mybatis.example.dao.BlogMapper">
<!--级联查 联合 定义结果规则 resultMap -->
<resultMap type="org.mybatis.example.bean.Blog" id="CustomizeBlog">
<id column="id" property="id"/>
<result column="name" property="name"/>
<association property="org.mybatis.example.bean.Group">
<id column "group_id" property="id"/>
<result column="group_name" property="name">
</association>
</resultMap>
<select id="selectBlog" resultMap="CustomizeBlog">
select (distinct)* from Blog b,Group g where b.id = g.id and b.id = #{id}
</select>
</mapper>
分步查询
<!-- * org/mybatis/example/BlogMapper.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.mybatis.example.dao.BlogMapper">
<!-- 联合 分步查询 定义结果规则 resultMap -->
<resultMap type="org.mybatis.example.bean.Blog" id="CustomizeBlog">
<id column="id" property="id"/>
<result column="name" property="name"/>
<association property="group"
select="org.mybatis.example.dao.GroupMapper.getGroupById"
column="group_id">
</association>
</resultMap>
<select id="selectBlog" resultMap="CustomizeBlog">
select * from Blog
</select>
</mapper>
懒加载
<!-- 分步查询的条件下 在全局配置文件中开启懒加载 按需加载等功能 即可-->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 懒加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
<!-- 按需加载 -->
collection
<?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.mybatis.example.dao.GroupMapper">
<resultMap type="org.mybatis.example.bean.Group" id="CustomizeGroup">
<id column="group_id" property="id"/>
<result column="group_name" property="name"/>
<collection property="blogs" ofType="org.mybatis.example.bean.Blog">
<id column="id" property="id"/>
<result column="name" property="name"/>
</collection>
</resultMap>
<!-- 一个组中包含多个Blog -->
<select id="selectBlog" resultMap="CustomizeGroup">
select * from Group g left join Blog b on g.id = b.id where g.id = #{id}
</select>
</mapper>
分步查询
<?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.mybatis.example.dao.GroupMapper">
<resultMap type="org.mybatis.example.bean.Group" id="CustomizeGroup">
<id column="group_id" property="id"/>
<result column="group_name" property="name"/>
<collection property="blogs" ofType="org.mybatis.example.bean.Blog"
select="org.mybatis.example.dao.GroupMapper.selectBlogByGroupId"
column="id">
</collection>
</resultMap>
<!-- 一个组中包含多个Blog -->
<select id="selectBlog" resultMap="CustomizeGroup">
select * from Group
</select>
</mapper>
懒加载
<!-- 同上一个示例 -->
<!-- 分步查询的条件下 在全局配置文件中开启懒加载 按需加载等功能 即可-->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 懒加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
<!-- 按需加载 -->
传递多参 手动懒加载
<collection property="blogs" ofType="org.mybatis.example.bean.Blog"
select="org.mybatis.example.dao.GroupMapper.selectBlogByGroupId"
column="{group_id = id}" fetchType="lazy">
<!-- column="{key1=column1,key2=column2} fetchType="lazy|eager"-->
</collection>
discriminator
<!-- * org/mybatis/example/BlogMapper.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.mybatis.example.dao.BlogMapper">
<!-- 联合 分步查询 定义结果规则 resultMap -->
<resultMap type="org.mybatis.example.bean.Blog" id="CustomizeBlog">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="sex" property="sex"/>
<discriminator javaType="sex" column="string">
<case value="0" resultType="">
<association property="group"
select="org.mybatis.example.dao.GroupMapper.getGroupById"
column="group_id">
</association>
</case>
<case value="1" resultType="org.mybatis.example.bean.Blog">
<result column="sex" property="name"/>
</case>
</discriminator>
</resultMap>
<select id="selectBlog" resultMap="CustomizeBlog">
select * from Blog
</select>
</mapper>
动态SQL
IF标签
public class User{
private int id;
private String name;
private String sex;
private String email;
private Dept dept;
}
public class UserMapper{
public List<User> getUsersbyIf(User user);
}
<!-- * org/mybatis/example/BlogMapper.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.wang.test.dao.BlogMapper">
<select id="getUsersbyIf" resultMap="com.wang.test.bean.User">
select * from user where<!-- 1=1 -->
<if test="id!=null">
id=#{id}<!-- and id=#{id} -->
</if>
<if test="sex != null and sex != '' ">
and sex=#{sex}
</if>
<if test="email!=null and email.trim()!='' ">
and email=#{email}
</if>
</select>
</mapper>
<!-- 当没有id时 sql语句会出现问题 -->
WHERE标签
<?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.wang.test.dao.BlogMapper">
<select id="getUsersbyIf" resultMap="com.wang.test.bean.User">
select * from user
<where>
<if test="id!=null">
id=#{id} <!-- id=#{id} and -->
</if>
<if test="sex != null and sex != '' ">
and sex=#{sex} <!-- id=#{id} and-->
</if>
<if test="email!=null and email.trim()!='' ">
and email=#{email} <!-- email=#{email} -->
</if>
</where>
</select>
</mapper>
<!-- and后置不好使 -->
TRIM标签
<?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.wang.test.dao.BlogMapper">
<select id="getUsersbyIf" resultMap="com.wang.test.bean.User">
select * from user
<trim prefix="where" prefixOverrides="xxx" suffix="" suffixOverrides="and">
<!-- 添加 where 前缀 覆盖xxx 添加后缀 覆盖and -->
<if test="id!=null">
xxx id=#{id} and
</if>
<if test="sex != null and sex != '' ">
sex=#{sex} and
</if>
<if test="email!=null and email.trim()!='' ">
email=#{email}
</if>
</trim>
</select>
</mapper>
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库