mybatis-plus @select 动态查询
@Select({"<script> select cor.risk_id,cor.create_by as leader,cor.create_time as put_forward_time," +
"cor.correct_user_name as handle,cor.update_time as handle_time," +
"cor.correct_end_time,cor.correct_status,risk.risk_name,rt.risk_name as risk_type,pro.pro_code," +
"pro.pro_name,pro.pro_leader_name as pro_leader,pt.type_name as pro_type from data_risk_correct cor " +
"left join data_project_risk risk on cor.risk_id=risk.risk_id " +
"left join data_risk_type rt on risk.risk_type_id=rt.risk_id " +
"left join data_project pro on risk.pro_id=pro.pro_id " +
"left join data_project_type pt on pro.pro_type_id=pt.type_id " +
"<where>"+
"<if test='riskCenterVo.riskName != null and riskCenterVo.riskName !=\"\"'>" +
" and risk.risk_name like concat('%',#{riskCenterVo.riskName},'%') " +
"</if>" +
"<if test='riskCenterVo.proName != null and riskCenterVo.proName !=\"\"'>" +
" and pro.pro_name like concat('%',#{riskCenterVo.proName},'%') " +
"</if>" +
"<if test='riskCenterVo.proCode != null and riskCenterVo.proCode !=\"\"'>" +
" and pro.pro_code =#{riskCenterVo.proCode} " +
"</if>" +
"<if test='riskCenterVo.correctStatus != null '>" +
" and cor.correct_status=#{riskCenterVo.correctStatus} " +
"</if>" +
"</where>" +
"and cor.is_delete=0 and cor.correct_user_id=#{userId}" +
"</script>"})
List<RiskCenterVo> selectRiskCenterList(@Param("riskCenterVo") RiskCenterVo riskCenterVo,@Param("userId") String userId);
SpringBoot+MyBatis动态查询支持的通用方法
这几天研究使用了一下 springBoot + MyBatis动态注解.
看了好多人说myBatis不支持动态,其实不然, 我个人不喜欢太多配置, 所以一惯喜欢使用注解模式, 但spring体系当中注解实在太多了, 其实常用的也就那么几个.
呵呵又跑题了.回来
package cn.miw.rpc.batis.comm;
import java.util.List;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;
/**
* 通用Mapper基础接口,使用范型,其他Mapper继承即可
* @author mrzhou
*
* @param <T>
*/
public interface GeneralMapper<T> {
@InsertProvider(method="insert",type=SQLGen.class)
@Options(useGeneratedKeys=true,keyProperty="id")
int save(T t);
@DeleteProvider(method="del",type=SQLGen.class)
int del(T t);
@UpdateProvider(method="update",type=SQLGen.class)
int update(T t);
@SelectProvider(method="select",type=SQLGen.class)
List<T> list(T t);
}
我个常用的也就是CRUD这4个方法, 其他的Mapper方法你可以在继承中再继续写吧, 那些就是大家常用的可以写在继承接口当中.
这里我写了一个通用的SQLProvider类SQLGen.java
package cn.miw.rpc.batis.comm;
import java.lang.reflect.Field;
import org.apache.ibatis.jdbc.SQL;
/**
* 常规CRUD四个方法
* @author mrzhou
*
* @param <T>
*/
public class SQLGen<T> {
public String select(T object) {
return new SQL() {
{
SELECT("*");
FROM(object.getClass().getSimpleName());
try {
Field[] fields = object.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
Object v = field.get(object);
if (v != null) {
String fieldName = field.getName();
if (v instanceof String && ((String)v).contains("%")) {
WHERE(fieldName + " like '"+v+"'" );
} else {
WHERE(fieldName + "=#{" + fieldName + "}");
}
}
}
} catch (Exception e) {
}
}
}.toString();
}
public String update(T object) {
return new SQL() {
{
UPDATE(object.getClass().getSimpleName());
try {
Field[] fields = object.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
Object v = field.get(object);
if (v != null) {
String fieldName = field.getName();
SET(fieldName + "=#{" + fieldName + "}");
}
}
} catch (Exception e) {
}
WHERE("id=#{id}");
}
}.toString();
}
public String insert(T object) {
return new SQL() {
{
INSERT_INTO(object.getClass().getSimpleName());
try {
Field[] fields = object.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
Object v = field.get(object);
if (v != null) {
String fieldName = field.getName();
VALUES(fieldName,"#{"+fieldName+"}");
}
}
} catch (Exception e) {
}
}
}.toString();
}
public String del(T object) {
return new SQL() {
{
DELETE_FROM(object.getClass().getSimpleName());
try {
Field[] fields = object.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
Object v = field.get(object);
if (v != null) {
String fieldName = field.getName();
if (v instanceof String && ((String)v).contains("%")) {
WHERE(fieldName + " like '"+v+"'" );
} else {
WHERE(fieldName + "=#{" + fieldName + "}");
}
}
}
} catch (Exception e) {
}
}
}.toString();
}
}
在调用Mapper方法时传入相应的实体, 如果字段类型为String且包含%, 将使用like 进行查询, 该操作仅对select和delete操作有效. insert,update则不受此限制, '%'百分号将作为内容被保存进数据库
在对应的Service中我们只需要这样使用
User user = new User();
user.setName("张%");// 或者user.setName("%赵%");
List<User> list = userMapper.list(user);
是不是很方便呢?
当然你的其他方法可以继续在相应的Mapper中继续描述
package cn.miw.rpc.batis.mapper;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import cn.miw.rpc.batis.comm.GeneralMapper;
import cn.miw.rpc.model.User;
/**
* 用户Mapper,定义其他常规的方便方法
* @author mrzhou
*
*/
@Mapper
public interface UserMapper extends GeneralMapper<User> {
@Insert("insert into User(name,age) values(#{name},#{age})")
int addUser(@Param("name") String name, @Param("age") int age);
@Select("select * from User where id =#{id}")
User findById(@Param("id") int id);
@Update("update User set name=#{name} where id=#{id}")
void updataById(@Param("id") int id, @Param("name") String name);
@Delete("delete from User where id=#{id}")
void deleteById(@Param("id") int id);
}
各位看包名, 其实我这个假期是在研究一些rpc的东西, 顺带折腾了一下mybatis.
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
