SqlUtils

 

 动态拼接sql

 

 

 

/**
 * *************************************************************************
 * <PRE>
 *  @ClassName:    : SqlUtils 
 *
 *  @Description:    : dynamic  sql
 *
 *  @Creation Date   : Jun 15, 2021 1:41:48 PM
 *
 *  @Author          :  Sea
 *  
 *
 * </PRE>
 **************************************************************************
 */
public class SqlUtils {
    
    
//    @Test
//    public void testSql() throws Exception {
//        JSONObject IncriteriaMap = new JSONObject();
//        IncriteriaMap.put("sea","aa,bb,cc,dd");
//        JSONObject mycriteriaMap = new JSONObject();
//        mycriteriaMap.put("in", IncriteriaMap);
//        String inlude="name,age,total";
//        String sql="select " +inlude+ " from user where "+ mapToWhere(mycriteriaMap) +" order by sold desc";
//        System.err.println(sql);
//    }
    
    
/**
 *         JSONObject andcriteriaMap = new JSONObject();
            andcriteriaMap.put("sea", "sea");
            andcriteriaMap.put("number", 123);
            andcriteriaMap.put("double", 12.31);
        JSONObject orcriteriaMap = new JSONObject();
            orcriteriaMap.put("sea", "sea");
            orcriteriaMap.put("double", 12.31);
        JSONObject IncriteriaMap = new JSONObject();
            IncriteriaMap.put("sea","aa,bb,cc,dd");
        JSONObject mycriteriaMap = new JSONObject();
            mycriteriaMap.put("and", andcriteriaMap);
            mycriteriaMap.put("or", andcriteriaMap);
            mycriteriaMap.put("in", IncriteriaMap);
 * @param mycriteriaMap
 * @return
 * @throws Exception
 */
    public static String mapToWhere(JSONObject mycriteriaMap) throws Exception {
        String criteria="";
        int criteriaMapsize = mycriteriaMap.size();
        for (Entry<String, Object> okv : mycriteriaMap.entrySet()) {
            String option = okv.getKey();
            Map<String, Object> criteriaMap=(Map<String, Object>) okv.getValue();;
            int msize=criteriaMap.size();
            for (Entry<String, Object> kv : criteriaMap.entrySet()) 
            {
                String key = kv.getKey();
                Object value = kv.getValue();
                if(StringUtils.isBlank(key)||StringUtils.isBlank(value+"")) 
                {
                    continue;
                }
                //if option is in 
                if("in".equalsIgnoreCase(option)) 
                {
                    criteria+=" " +key+" in( " +convert2SqlIn(Arrays.asList((value+"").split(","))) +" )";
                }else //option is and  | or 
                {
                    if(String.class.isInstance(value)) {
                        criteria+=" " +key+"='" +value +"' ";
                    }else 
                    {
                      criteria+=" " +key+"=" +value +" ";
                    }
                    msize--;
                    if(msize!=0) {
                        criteria+=" "+option+" ";
                    }
                }
            }
            criteriaMapsize--;
            if(criteriaMapsize!=0) {
                criteria+=" and ";
            }
        };
        Assert.notNull(criteria==""?null:criteria, "criteria can't be null");
        return criteria;
    }
    
    
    
    
     /**
      * @Desc list<String> to sql in  
      * @param list<String>   
      * @return
      */
      public static String convert2SqlIn(List<String> list){
            StringBuilder sb = new StringBuilder();
            if(list != null && list.size()>0){
                for(int i=0,len=list.size();i<len;i++){
                    sb.append("'"+ list.get(i) + "'");
                    if(i < len-1){
                        sb.append(",");
                    }
                }
            }
            return sb.toString();
        }
    
    

}