前言
项目中虽然有ORM映射框架来帮我们拼写SQL，简化开发过程，降低开发难度。但难免会出现需要自己拼写SQL的情况，这里分享一个利用反射跟自定义注解拼接实体对象的查询SQL的方法。
代码
自定义注解：
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Like { } @Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Between { /**
* 最小值的实体属性名
*/
String min(); /**
* 最大值的实体属性名
*/
String max();
} @Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface In { /**
* in的具体集合的属性名
*/
String values();
}
实体对象：
@Data
@Entity
@Table(name = "RES_LOG")
public class ResLog {
@Id
private String logId;
private String resourceType;
private String resourceId;
@Like //开启模糊查询
private String resourceName;
private String resourceCode;
@In(values = "operationTypeList")//in查询
private String operationType;
@Between(min = "operationTimeStart", max = "operationTimeEnd")//开启区间查询
private Date operationTime;
private String operatorId;
private String operator; @Transient
private Date operationTimeStart;
@Transient
private Date operationTimeEnd;
@Transient
private List<String> operationTypeList; }
拼接SQL方法：
/**
* 自动拼接原生SQL的“and”查询条件,支持自定义注解：@Like @Between @In
*
* @param entity 实体对象
* @param sql 待拼接SQL
* @param ignoreProperties 忽略属性
*/
public static void appendQueryColumns(Object entity, StringBuilder sql, String... ignoreProperties) { try {
//忽略属性
List<String> ignoreList1 = Arrays.asList(ignoreProperties);
//默认忽略分页参数
List<String> ignoreList2 = Arrays.asList("class", "pageable", "page", "rows", "sidx", "sord"); //反射获取Class的属性（Field表示类中的成员变量）
for (Field field : entity.getClass().getDeclaredFields()) {
//获取授权
field.setAccessible(true);
//属性名称
String fieldName = field.getName();
//属性的值
Object fieldValue = field.get(entity);
//检查Transient注解，是否忽略拼接
if (!field.isAnnotationPresent(Transient.class)) {
String column = new PropertyNamingStrategy.SnakeCaseStrategy().translate(fieldName).toLowerCase();
//值是否为空
if (!StringUtils.isEmpty(fieldValue)) {
//映射关系：对象属性(驼峰)->数据库字段(下划线)
if (!ignoreList1.contains(fieldName) && !ignoreList2.contains(fieldName)) {
//开启模糊查询
if (field.isAnnotationPresent(Like.class)) {
sql.append(" and " + column + " like '%" + escapeSql(fieldValue) + "%'");
}
//开启等值查询
else {
sql.append(" and " + column + " = '" + escapeSql(fieldValue) + "'");
}
}
} else {
//开启区间查询
if (field.isAnnotationPresent(Between.class)) {
//获取最小值
Field minField = entity.getClass().getDeclaredField(field.getAnnotation(Between.class).min());
minField.setAccessible(true);
Object minVal = minField.get(entity);
//获取最大值
Field maxField = entity.getClass().getDeclaredField(field.getAnnotation(Between.class).max());
maxField.setAccessible(true);
Object maxVal = maxField.get(entity);
//开启区间查询
if (field.getType().getName().equals("java.util.Date")) {
if (!StringUtils.isEmpty(minVal)) {
sql.append(" and " + column + " > to_date( '" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) minVal) + "','yyyy-mm-dd hh24:mi:ss')");
}
if (!StringUtils.isEmpty(maxVal)) {
sql.append(" and " + column + " < to_date( '" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) maxVal) + "','yyyy-mm-dd hh24:mi:ss')");
}
}
} //开启in查询
if (field.isAnnotationPresent(In.class)) {
//获取要in的值
Field values = entity.getClass().getDeclaredField(field.getAnnotation(In.class).values());
values.setAccessible(true);
List<String> valuesList = (List<String>) values.get(entity);
if (valuesList != null && valuesList.size() > 0) {
String inValues = "";
for (String value : valuesList) {
inValues = inValues + "'" + value + "'";
}
sql.append(" and " + column + " in (" + escapeSql(inValues) + ")");
}
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
2019-10-24补充：注意！我们这属于动态拼写SQL，需要进行转义防范SQL注入！
/**
* sql转义
*/
public static String escapeSql(String str) {
if (str == null) {
return null;
}
StringBuilder sb = new StringBuilder();
for (int i = 0; i < str.length(); i++) {
char src = str.charAt(i);
switch (src) {
case '\'':
sb.append("''");// hibernate转义多个单引号必须用两个单引号
break;
case '\"':
case '\\':
sb.append('\\');
default:
sb.append(src);
break;
}
}
return sb.toString();
}
测试与效果
public static void main(String[] args) {
ResLog resLog = new ResLog();
resLog.setLogId("id1");//等值查询
resLog.setResourceName("name1");//like查询
resLog.setOperationTimeStart(new Date());//日期区间查询
resLog.setOperationTimeEnd(new Date());
ArrayList<String> list = new ArrayList<>();
list.add("type1");
list.add("type2");
resLog.setOperationTypeList(list);//in查询
//在外面拼写select * from 是为了多表联查时的情况
StringBuilder sql = new StringBuilder("select * from res_log where '1' = '1'");
appendQueryColumns(resLog,sql);
System.out.println(sql.toString());
}
拼接结果：
select *
from res_log
where '' = ''
and log_id = 'id1'
and resource_name like '%name1%'
and operation_type in ('type1''type2')
and operation_time >
to_date('2018-10-08 15:00:40', 'yyyy-mm-dd hh24:mi:ss')
and operation_time <
to_date('2018-10-08 15:00:40', 'yyyy-mm-dd hh24:mi:ss')
后记
甚至我们可以直接获取实体对象对应的表名，直接在方法里面拼出 select * from ，这样就不需要在外面拼接这一句
//获取实体对象对应的表名
String TableName = entity.getClass().getAnnotation(Table.class).name();
System.out.println(TableName);
为了优化SQL，一般我们不建议select * from，而是需要查询那些字段就拼出那些字段，例如：select log_id from
但是如果数据表有一百个字段呢？一个个手动拼接就太傻了，因此写了一个自动拼接字段的方法，支持配置忽略拼接的字段
/**
*
* @param entity 实体对象
* @param ignoreProperties 动态参数 忽略拼接的字段
* @return sql
*/
public static StringBuilder appendFields(Object entity, String... ignoreProperties) {
StringBuilder sql = new StringBuilder();
List<String> ignoreList = Arrays.asList(ignoreProperties);
try {
sql.append("select "); for (Field field : entity.getClass().getDeclaredFields()) {
//获取授权
field.setAccessible(true);
String fieldName = field.getName();//属性名称
Object fieldValue = field.get(entity);//属性的值
//非临时字段、非忽略字段
if (!field.isAnnotationPresent(Transient.class) && !ignoreList.contains(fieldName)) {
//拼接查询字段 驼峰属性转下划线
sql.append(new PropertyNamingStrategy.SnakeCaseStrategy().translate(fieldName).toLowerCase()).append(" ").append(",");
}
}
//处理逗号（删除最后一个字符）
sql.deleteCharAt(sql.length() - 1); String tableName = entity.getClass().getAnnotation(Table.class).name();
sql.append("from ").append(tableName).append(" where '1' = '1' ");
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return sql;
}
接着上面的main测试
public static void main(String[] args) {
ResLog resLog = new ResLog();
resLog.setLogId("id1");//等值查询
resLog.setResourceName("name1");//like查询
resLog.setOperationTimeStart(new Date());//日期区间查询
resLog.setOperationTimeEnd(new Date());
ArrayList<String> list = new ArrayList<>();
list.add("type1");
list.add("type2");
resLog.setOperationTypeList(list);//in查询
//动态拼接查询字段
StringBuilder sql = appendFields(resLog,"remark","operator");
appendQueryColumns(resLog,sql);
System.out.println(sql.toString());
}
结果
select log_id,
resource_type,
resource_id,
resource_name,
resource_code,
operation_type,
operation_time,
operator_id
from RES_LOG
where '' = ''
and log_id = 'id1'
and resource_name like '%name1%'
and operation_type in ('type1''type2')
and operation_time >
to_date('2018-12-13 10:34:33', 'yyyy-MM-dd hh24:mi:ss')
and operation_time <
to_date('2018-12-13 10:34:33', 'yyyy-MM-dd hh24:mi:ss')