java 注解动态生成sql

- 日理万妓 2022-05-24 07:16 311阅读 0赞

## 合理的利用java 注解 可以有效的解决动态sql 的问题 下面直接上我的源码 ##

> 1 自定义的注解类 Column 和 Table

/** * 实体类的属性注解-跟@table 配合使用 * @author zhangh * @date 2018年5月22日下午2:59:18 */
    @Target({ElementType.FIELD})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface Column { 
        String value();
        boolean lookUp() default false;//模糊查询标识 默认是false
    }

/** * 实体类 的表名注解 * @author zhangh * @date 2018年5月22日下午2:58:07 */
    @Target({ElementType.TYPE})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface Table { 
        String value();
    }

> 实体类

/** * 对外暴露的接口实体类 * @author zhangh * @date 2018年5月22日下午2:31:39 */
    @Table("clouds_open_api")
    public class OpenApi extends BaseObject implements Serializable{ 
    
        /** * */
        private static final long serialVersionUID = 1490626332112676475L;
    
        @Column(value="id" )
        private Integer id;//主键
    
        @Column(value="api_name",lookUp=true)
        private String api_name;//接口名称
    
        @Column(value="api_address")
        private String api_address;//接口访问地址
    
        @Column(value="api_type")
        private String api_type;//接口访问类型
    
        @Column(value="api_param")
        private String api_param;//接口参数
    
        @Column(value="api_result")
        private String api_result;//接口返回数据
    
        @Column(value="api_status")
        private String api_status;//接口状态 1 可用 0禁用
    
        @Column(value="api_charge")
        private String api_charge;//是否收费（1收费 0免费）默认是免费
    
        public Integer getId() {
            return id;
        }
        public void setId(Integer id) {
            this.id = id;
        }
        public String getApi_name() {
            return api_name;
        }
        public void setApi_name(String api_name) {
            this.api_name = api_name;
        }
        public String getApi_address() {
            return api_address;
        }
        public void setApi_address(String api_address) {
            this.api_address = api_address;
        }
        public String getApi_type() {
            return api_type;
        }
        public void setApi_type(String api_type) {
            this.api_type = api_type;
        }
        public String getApi_param() {
            return api_param;
        }
        public void setApi_param(String api_param) {
            this.api_param = api_param;
        }
        public String getApi_result() {
            return api_result;
        }
        public void setApi_result(String api_result) {
            this.api_result = api_result;
        }
        public String getApi_status() {
            return api_status;
        }
        public void setApi_status(String api_status) {
            this.api_status = api_status;
        }
        public String getApi_charge() {
            return api_charge;
        }
        public void setApi_charge(String api_charge) {
            this.api_charge = api_charge;
        }
    
        public OpenApi() {
            super();
        }
        public OpenApi(String api_name) {
            super();
            this.api_name = api_name;
        }   
    }

> dao 层

public class OpenApiDAO extends BaseDAO<OpenApi> { 
    
        public List<Map<String, Object>> findList(OpenApi openApi) {
            return getJdbcTemplate().queryForList(querySQL(openApi));
        }
    
        /** * 根据反射动态生成sql * @author zhangh * @date 2018年5月22日下午6:14:37 * @param object 具体的domain * @return */
        private static String querySQL(Object object) {
            StringBuilder sb = new StringBuilder();
            Class<?> mClass = object.getClass();
            boolean isExist = mClass.isAnnotationPresent(Table.class);
            if (!isExist)
                return null;
            Table table = (Table) mClass.getAnnotation(Table.class);
            String tabName = table.value();
            Field[] fields = mClass.getDeclaredFields();
            // 拼装SQL表名
            sb.append("select "+getSelect(fields)+" from ").append(tabName).append(" where 1=1");
            // 获取类的所有字段
            sb.append(getWhere(object));
            return sb.toString();
        }
    
        /** * 获取注解中的value 组装成sql 的select 对象 不要使用select * 查询 提高效率 * @author zhangh * @date 2018年5月23日上午8:37:37 * @param fields * @return */
        public static String getSelect(Field[] fields){
            StringBuilder sb = new StringBuilder("");
            for (Field f : fields) {
                boolean isFExist = f.isAnnotationPresent(Column.class);
                if (!isFExist) {
                    continue;
                }
                sb.append(f.getAnnotation(Column.class).value()).append(",");
            }
            if(sb.equals("")){
                return " 1 ";
            }else{
                return sb.toString().substring(0, sb.toString().length()-1);
            }
        }
    
        /** * 动态获取sql 的where 条件 * @author zhangh * @date 2018年5月23日上午8:48:55 * @param object * @return */
        public static String getWhere(Object object){
            StringBuilder sb = new StringBuilder();
            for (Field f : object.getClass().getDeclaredFields()) {
                // 下面代码获取字段注解
                boolean isFExist = f.isAnnotationPresent(Column.class);
                if (!isFExist) {
                    continue;
                }
                String columnName = f.getAnnotation(Column.class).value();
                boolean lookup = f.getAnnotation(Column.class).lookUp();//模糊查询标识
                // 下面代码获取字段值
                Object filedValue = null;
                try {
                    PropertyDescriptor pd = new PropertyDescriptor(f.getName(), object.getClass());// PropertyDescriptor 类表示JavaBean类通过存储器导出一个属性
                    Method method = pd.getReadMethod();// getReadMethod() 获得用于读取属性值的方法,即getter方法
                    filedValue = method.invoke(object);// 通过反射调用getter方法
                } catch (Exception e) {
                    e.printStackTrace();
                }
                // 拼装SQL条件
                if (filedValue == null) {
                    continue;
                }
                sb.append(" and ").append(columnName);
                if (filedValue instanceof String) {
                    // 包含多段
                    if (((String) filedValue).contains(",")) {
                        String[] arr = ((String) filedValue).split(",");
                        sb.append(" in(");
                        for (String str : arr) {
                            sb.append("'").append(str).append("'").append(",");
                        }
                        sb.deleteCharAt(sb.length() - 1).append(")");
                    } else {
                        if(lookup){
       //api_name 需要模糊查询
                            sb.append(" like ").append("'").append(filedValue).append("%'");
                        }else{                      
                            sb.append("=").append("'").append(filedValue).append("'");
                        }
                    }
                } else if (filedValue instanceof Boolean) {
                    sb.append("=").append("'").append(filedValue).append("'");
                } else if (filedValue instanceof Integer) {
                    sb.append("=").append(filedValue);
                }
            }
            return sb.toString();
        }
    }

> service 层以及实现层

@Service
    public class OpenApiServiceImpl implements OpenApiService{ 
    
        @Autowired
        private OpenApiDAO openApiDAO;
    
        public OpenApi findById(Integer id){
            return openApiDAO.findById(id);
        }
        public List<Map<String, Object>> findList(OpenApi openApi){
            return openApiDAO.findList(openApi);
        }
    }
    
    public interface OpenApiService { 
        public OpenApi findById(Integer id);
    
        public List<Map<String, Object>> findList(OpenApi openApi);
    }

> controller 核心代码

@RequestMapping(value="/getRegisterOpenApi1",method=RequestMethod.POST)
        @ResponseBody
        public List<Map<String, Object>> getRegisteOpenApi1(@RequestBody(required=false) OpenApi openApi) throws Exception{
            return openApiService.findList(null==openApi?new OpenApi():openApi);
        }

##  ##

> 请求结果  
> 参数一：\{“api\_name”:”so”,”api\_address”:”[http://localhost:8080/store/api/fingById/1][http_localhost_8080_store_api_fingById_1]“\}  
> 打印sql：select id,api\_name,api\_address,api\_type,api\_param,api\_result,api\_status,api\_charge from clouds\_open\_api where 1=1 and api\_name like ‘so%’ and api\_address=’[http://localhost:8080/store/api/fingById/1][http_localhost_8080_store_api_fingById_1]’

[http_localhost_8080_store_api_fingById_1]: http://localhost:8080/store/api/fingById/1