存储过程查询数据库字段的相似度

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

create or replace procedure p_compare(p_name    IN varchar2,         po_result in out SYS_REFCURSOR)         is begin   OPEN po_result FOR     select *       from (select t.*,                    UTL_MATCH.edit_distance_similarity(p_name, t.item_name) AS xsd               FROM T_CATALOG_MESSAGE t              where t.data_valid_flag = '1')      where 1 = 1        and xsd > 40      order by xsd desc; end p_compare;

　　java调用存储过程

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61

/**      * 根据事项名称查询所有复核条件的事项      * @param filterMap      * @param pageDesc      * @return      */     public JSONArray queryAllItemByitemName(Map<String, Object> filterMap, PageDesc pageDesc) {         String itemName=String.valueOf(filterMap.get("itemName"));         JSONArray jsonArray =new JSONArray();         //模糊查询         /*sql+=" select * from  t_catalog_message t where t.data_valid_flag  = '1'   "                 +" [:(like)itemName | and t.item_name like :itemName ] "                 +"[ :departmentLineId| and INSTR(:departmentLineId,T.department_line_id)>0 ] "                 +" order by t.ins_time desc ";*/         String sql ="";         /*sql+=" select * from ("                 + " select t.*, UTL_MATCH.edit_distance_similarity('1',t.item_name) AS xsd FROM T_CATALOG_MESSAGE t "                 +" where t.data_valid_flag  = '1' )"                 + " where 1=1 and  xsd>40 "                 +"[ :departmentLineId| and INSTR(:departmentLineId,T.department_line_id)>0 ] "                 +" order by xsd desc ";*/         /*QueryAndNamedParams params = QueryUtils.translateQuery(sql.toString(), filterMap);         JSONArray jsonArray = DatabaseOptUtils.listObjectsByNamedSqlAsJson(this, params.getQuery(), params.getParams(), pageDesc);*/                   CallableStatement cs=null;         Connection connection=null;         ResultSet rs = null;         ResultSetMetaData md=null;         try {             connection= this.jdbcTemplate.getDataSource().getConnection();             cs = connection.prepareCall("{call p_compare(?,?)}");             cs.setString(1, itemName);             cs.registerOutParameter(2, OracleTypes.CURSOR);             cs.execute();             rs = (ResultSet) cs.getObject(2);             while (rs.next()) {                 JSONObject jsonObject=new JSONObject();//                 System.out.println(rs.getString(1) + " :  " + rs.getString(2));                 jsonObject.put("itemName", rs.getString(3));                 jsonObject.put("baseCode", rs.getString(4));                 jsonArray.add(jsonObject);             }         } catch (SQLException throwables) {             throwables.printStackTrace();         }finally{             try {                 if(rs!=null){                     rs.close();                 }                 if (cs!=null) {                     cs.close();                 }                 if(connection!=null) {                     connection.close();                 }             } catch (SQLException e) {                 e.printStackTrace();             }         }         return jsonArray;     }