Mybatis03-Mybatis之动态sql
为了更好的处理复杂的sql语句,Mybatis提供了动态的sql,而不需要使用java程序去做sql字符串的拼接,既达到了解耦的作用,也让我们对于sql的书写更加清晰,方便!
1.基于XML 标签配置(重点)
使用xml配置,如同我上篇博客中的谈到的Mybatis使用加载xml的方式进行增删查改,只是sql语句不同!
if 标签
<sql id="account_columns">
id, aname, type, money, user_id, create_time, update_time, remark
</sql>
<!--
动态sql-if 条件判断标签
使用 if 标签就是加一个 test 属性作为判断, 如果有多个条件组合判断的话用 and, or连接
-->
<select id="queryByParams" parameterType="com.shsxt.taojin.query.AccountQuery" resultType="com.shsxt.taojin.vo.Account">
select <include refid="account_columns"/> from account
where 1=1
<if test="null !=userId">
and user_id=#{userId}
</if>
<if test="null !=aname and aname !=''">
and aname like concat('%',#{aname},'%')
</if>
<if test="null !=type and type !=''">
and type=#{type}
</if>
<if test="null !=time and time !=''">
and create_time >= #{time}
</if>
</select>
where 记录过滤标签
<!--
动态sql-where 标签 结果过滤
如果where 后第一个过滤条件出现 and | or mybatis 自动忽略and|or,但不能省略其后连接的and/or
-->
-->
<select id="queryByParams" parameterType="com.shsxt.taojin.query.AccountQuery" resultType="com.shsxt.taojin.vo.Account">
select <include refid="account_columns"/> from account
<where>
<if test="null !=userId">
and user_id=#{userId}
</if>
<if test="null !=aname and aname !=''">
and aname like concat('%',#{aname},'%')
</if>
<if test="null !=type and type !=''">
and type=#{type}
</if>
<if test="null !=time and time !=''">
and create_time >= #{time}
</if>
</where>
</select>
choose when otherwise 类似if
<!--
动态sql03-choose when otherwise 单条件切换,每个when都是独立的,java中的switch
-->
<select id="queryByParams" parameterType="com.shsxt.taojin.query.AccountQuery" resultType="com.shsxt.taojin.vo.Account">
select <include refid="account_columns"/> from account
<where>
<choose>
<when test="null !=userId">
user_id=#{userId} and type ='0'
</when>
<when test="null !=aname">
and aname like concat('%',#{aname},'%')
</when>
<otherwise>
type='1'
</otherwise>
</choose>
</where>
</select>
<select id="queryByParams" parameterType="com.shsxt.taojin.query.AccountQuery" resultType="com.shsxt.taojin.vo.Account">
select
id, aname, user_id, create_time, update_time,type,
<choose>
<when test="null !=type and type !=''">
remark
</when>
<otherwise>
money
</otherwise>
</choose>
from account
<where>
<choose>
<when test="null !=userId">
user_id=#{userId} and type ='0'
</when>
<when test="null !=aname">
and aname like concat('%',#{aname},'%')
</when>
<otherwise>
type='1'
</otherwise>
</choose>
</where>
</select>
trim 标签
<!--
动态sql04-trim 可以替换where set
-->
<select id="queryByParams" parameterType="com.shsxt.taojin.query.AccountQuery" resultType="com.shsxt.taojin.vo.Account">
select <include refid="account_columns"/>
from account
<trim prefix="where" prefixOverrides="and |or" >
<if test="null !=userId">
and user_id=#{userId}
</if>
<if test="null !=aname and aname !=''">
and aname like concat('%',#{aname},'%')
</if>
<if test="null !=type and type !=''">
and type=#{type}
</if>
<if test="null !=time and time !=''">
and create_time >= #{time}
</if>
</trim>
</select>
<update id="update" parameterType="com.shsxt.taojin.vo.Account">
update account
<trim prefix="set" suffixOverrides="," >
<if test="aname !=null and aname !=''">
aname =#{aname},
</if>
<if test="type !=null and type !=''">
type=#{type},
</if>
<if test="remark !=null and remark !=''">
remark=#{remark},
</if>
</trim>
where id=#{id}
</update>
Set 标签 更新标签
<!--
动态sql05-set 标签 自动忽略,
-->
<update id="update" parameterType="com.shsxt.taojin.vo.Account">
update account
<set>
<if test="aname !=null and aname !=''">
aname =#{aname},
</if>
<if test="type !=null and type !=''">
type=#{type},
</if>
<if test="remark !=null and remark !=''">
remark=#{remark},
</if>
</set>
where id=#{id}
</update>
foreach 批量操作标签
<insert id="saveAccountBatch">
insert into account(aname, type, money, user_id, create_time, update_time, remark) values
<foreach collection="list" item="item" separator="," >
(#{item.aname},#{item.type},#{item.money},#{item.userId},#{item.createTime},#{item.updateTime},#{item.remark})
</foreach>
</insert>
2.基于注解
常用注解
* @Insert @Select @Update @Delete
MyBatis 提 供 了 各 种 注 解 如来帮助构建动态 SQL 语句,然后让 MyBatis 执行这些 SQL 语句。
* @InsertProvider @SelectProvider @UpdateProvider @DeleteProvider
1.常用注解实现CRUD
上一篇博客,我们已经接触到基于注解实现CRUD,和基于接口和使用xml的形式对比,就是将sql语句放置在对应接口的注解方法上,省略了配置sql映射配置文件,但需要在mybatis.xml全局配置文件中开启注解的映射器如下:
开启注解的映射器:
<!-- mapper 配置文件指定 文件数量可配置多个-->
<mappers>
<!--接口列表配置形式注解sql-->
<mapper class="com.mage.dao.AccountDao"/>
</mappers>
接口列表配置形式注解sql方式:
public interface AccountDao {
@Insert("insert into account(aname,type,money,user_id,create_time,update_time,remark) " +
"values(#{aname},#{type},#{money},#{userId},#{createTime},#{updateTime},#{remark})")
public Integer saveAccount(Account account);
测试:
@Test
public void test1() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = factory.openSession(true);
/**
* 获取接口的代理对象 运行期动态为AccountDao 创建代理对象
*/
IAccountDao iaccountDaoProxy = session.getMapper(IAccountDao.class);
Account account =new Account("qzp","1", BigDecimal.valueOf(20000L),new Date(),new Date(),"奖励",10);
System.out.println(iaccountDaoProxy.saveAccount(account));
session.close();
}
2.各种注解构建动态 SQL并执行CRUD(了解即可)
开启注解的映射器:
<!-- mapper 配置文件指定 文件数量可配置多个-->
<mappers>
<!--接口列表配置形式注解sql-->
<mapper class="com.mage.dao.IAccountDao"/>
</mappers>
1.接口
public interface IAccountDao extends BaseDao<Account,Integer> {
@InsertProvider(type = AccountProvider.class,method = "getInsertAccountSql01")
public Integer saveAccount01(Account account);
@InsertProvider(type = AccountProvider.class,method = "getInsertAccountSql02")
public Integer saveAccount02(Account account);
@Select("select id,aname,type,user_id,money,remark,create_time,update_time from account where id=#{id}")
public Account queryAccountById(Integer id);
@SelectProvider(type = AccountProvider.class,method = "getSelectAccountSQL01")
public Account queryAccountById01(Integer id);
@SelectProvider(type = AccountProvider.class,method = "getSelectAccountSQL02")
public Account queryAccountById02(Integer id);
@SelectProvider(type = AccountProvider.class,method = "getQueryAccountsByParams")
public List<Account> queryAccountsByParams(AccountQuery accountQuery);
2**.注解type对应的实现类,书写需要执行的sql的CRUD**
public class AccountProvider {
public String getInsertAccountSql01(Account account) {
return "insert into account(aname,type,money,user_id,create_time,update_time,remark) " +
"values(#{aname},#{type},#{money},#{userId},#{createTime},#{updateTime},#{remark})";
}
public String getInsertAccountSql02(Account account) {
return new SQL() {{
INSERT_INTO("account");
if (StringUtils.isNotBlank(account.getAname()) & StringUtils.isNotBlank(account.getType()) &
StringUtils.isNotBlank(account.getMoney().toString())) {
VALUES("aname,type,money", "#{aname},#{type},#{money}");
}
/*VALUES("type","#{type}");*/
}}.toString();
}
public String getSelectAccountSQL01(Integer id) {
return "select id,aname,type,user_id,money,remark,create_time,update_time from account where id=#{id}";
}
public String getSelectAccountSQL02(Integer id) {
/* return new SQL() {
{
SELECT(" id,aname,type,user_id,money,remark,create_time,update_time");
FROM("account");
WHERE("id=#{id}");
}
}.toString();*/
return new SQL().SELECT("id,aname,type,user_id,money,remark,create_time,update_time").FROM("account").WHERE("id=#{id}").toString();
}
public String getQueryAccountsByParams(AccountQuery accountQuery) {
return new SQL() {
{
SELECT(" id,aname,type,user_id,money,remark,create_time,update_time");
FROM("account");
WHERE("1=1");
if (null != accountQuery.getUserId()) {
WHERE("user_id=#{userId}");
}
if (StringUtils.isNotBlank(accountQuery.getAname())) {
WHERE("aname like concat('%',#{aname},'%')");
}
if (StringUtils.isNotBlank(accountQuery.getType())) {
WHERE("type=#{type}");
}
if (StringUtils.isNotBlank(accountQuery.getTime())) {
WHERE("create_time>=#{time}");
}
}
}.toString();
}
}
3.测试
和之情的注解测试代码一样,正常测试
@Test
public void test2() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = factory.openSession(true);
/**
* 获取接口的代理对象 运行期动态为AccountDao 创建代理对象
*/
IAccountDao iaccountDaoProxy = session.getMapper(IAccountDao.class);
Account account =new Account("wjw","1", BigDecimal.valueOf(20000L),new Date(),new Date(),"奖励",10);
System.out.println(iaccountDaoProxy.saveAccount01(account));
session.close();
本片博客分享就到这,如果有什么小问题,希望你在评论区留言,如果本片博客对你有帮助的话,希望你能收藏,想要学习更多,就多多关注我,文章持续更新中,别忘了点赞哦!谢谢!