目标:实现通过注解自动生成SQL语句
要求:
- 用户表字段:用户名、年龄、手机号码
- 方便对每个字段的组合条件进行检索,打印SQL
- 模仿JPA
代码实现:
表注解:
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface Table {
String value();
}
字段注解:
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface Colunm {
String value();
}
领域对象使用注解:
@Table("user")
public class Filter {
@Colunm("name")
private String name;
@Colunm("age")
private int age;
@Colunm("mobile")
private String mobile;
//省略getter与setter方法
}
实现自动生成查询SQL语句:
public static String query(Object filter){
StringBuilder sb = new StringBuilder();
//1.获取到class
Class c = filter.getClass();
//2.获取到table的名字
boolean exists = c.isAnnotationPresent(Table.class);
if(!exists){
return null;
}
Table table = (Table) c.getAnnotation(Table.class);
//获取到表名
String tableName = table.value();
//开始组装SQL语句
sb.append("Select * from ").append(tableName).append(" where 1=1");
//3.遍历所有字段
Field[] fArray = c.getDeclaredFields();
for (Field field : fArray){
//4.处理内个字段对应的sql
//4.1拿到字段名
boolean fexists = field.isAnnotationPresent(Colunm.class);
if (!fexists){
continue;
}
Colunm colunm = field.getAnnotation(Colunm.class);
String columnName = colunm.value();
//4.2拿到字段的值,使用反射
String filedName = field.getName();
//拼装get方法
String getMethodName =
"get"
+ filedName.substring(0,1).toUpperCase() //首字母大写
+ filedName.substring(1); //获取首字母之后的信息
Object fieldValue = null;
try {
Method getMethod = c.getMethod(getMethodName);
//反射调用
fieldValue = getMethod.invoke(filter);
} catch (Exception e) {
e.printStackTrace();
}
//4.3拼装sql
if(fieldValue == null ||
(fieldValue instanceof Integer && (Integer)fieldValue == 0)){
continue;
}
sb.append(" and ").append(filedName);
if (fieldValue instanceof String){
if (((String)fieldValue).contains(",")){
String[] values = ((String)fieldValue).split(",");
sb.append("in(");
for (String v:values){
sb.append("'").append(v).append("'").append(",");
}
sb.deleteCharAt(sb.length() - 1);
sb.append(")");
}
sb.append("=").append("'").append(fieldValue).append("'");
}else if (fieldValue instanceof Integer){
sb.append("=").append(fieldValue);
}
}
return sb.toString();
}