public class SQLHelper {

    /**
     * 给定表名tableName和字段数组colNames，将其拼接为一条Insert语句。
     *
     * @param tableName
     * @param colNames
     * @return
     */
    public static String insertSql(String tableName, String[] colNames) {
        StringBuilder sb = new StringBuilder();
        StringBuilder params = new StringBuilder();
        sb.append("INSERT INTO ");
        sb.append(tableName);
        sb.append("(");
        for(String colName : colNames) {
            sb.append(colName); 
            sb.append(", ");    
            params.append("?, ");
        }
        sb.delete(sb.length() - 2, sb.length());
        params.delete(params.length() - 2, params.length());
        sb.append(") VALUES (");
        sb.append(params);
        sb.append(")");     
        return sb.toString();
    }


    /**
     * 给定表名tableName和需要更新的字段数组colNames，
     * 将其拼接为一条带有where查询条件（where参数中需要自带where关键字）的Update语句。
     *
     * @param tableName
     * @param colNames
     * @param where
     * @return
     */
    public static String updateSqlWhereParas(String tableName, String[] colNames, String where) {
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE ");
        sb.append(tableName);
        sb.append(" SET ");
        for(String colName : colNames) {
            sb.append(colName);
            sb.append(" = ");
            sb.append("?, ");
        }
        sb.delete(sb.length() - 2, sb.length());
        if(StringUtils.isNotBlank(where)) { 
            sb.append(" ");
            sb.append(where);
        }       
        return sb.toString();
    }

    /**
     * 给定表名tableName和需要更新的字段数组colNames，将其拼接为一条Update语句。
     *
     * @param tableName
     * @param colNames
     * @return
     */
    public static String updateSql(String tableName, String[] colNames) {
        return updateSqlWhereParas(tableName, colNames, null);
    }

    /**
     * 给定表名tableName，将其拼接为一条带有where查询条件（where参数中需要自带where关键字）的delete语句。
     *
     * @param tableName
     * @param where
     * @return
     */
    public static String deleteSqlWhereParas(String tableName, String where) {
        StringBuilder sb = new StringBuilder();
        sb.append("DELETE FROM ");
        sb.append(tableName);
        if(where != null && where.length() > 0) {
            sb.append(" ");
            sb.append(where);
        }       
        return sb.toString();
    }

    /**
     * 给定表名tableName，将其拼接为一条带有ID查询条件的delete语句。
     *
     * @param tableName
     * @return
     */
    public static String deleteSql(String tableName) {
        return deleteSqlWhereParas(tableName, null);
    }

    /**
     * 给定表名tableName和需要查询的字段数组colNames，
     * 将其拼接为一条带有where查询条件（where参数中需要自带where关键字）的select语句。
     *
     * @param tableName
     * @param colNames
     * @param where
     * @return
     */
    public static String selectSqlWhereParas(String tableName, String[] colNames, String where) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ");
        for(String colName : colNames) {
            sb.append(colName);
            sb.append(" , ");
        }
        sb.delete(sb.length() - 2, sb.length());
        sb.append("FROM ");
        sb.append(tableName);
        if(StringUtils.isNotBlank(where)) { 
            sb.append(" ");
            sb.append(where);
        }       
        return sb.toString();
    }
    
    /**
     * 给定表名tableName和需要查询的字段数组colNames，
     * 将其拼接为一条带有where查询条件（where参数中需要自带where关键字）的select语句。
     * 去重查询，窗口非空查询
     * @param tableName
     * @param colNames
     * @param where
     * @return
     */
    public static String selectSqlWhereParasRepeat(String tableName, String[] colNames, String where) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ");
        for(String colName : colNames) {
            sb.append("DISTINCT("+(colName)+")");
        }
        sb.append(" FROM ");
        sb.append(tableName);
        if(StringUtils.isNotBlank(where)) { 
            sb.append(" ");
            sb.append(where);
        }       
        return sb.toString();
    }

    /**
     * 给定表名tableName和需要查询的字段数组colNames，
     * 将其拼接为一条select语句。（没有where条件，则查询全表所有记录）。
     *
     * @param tableName
     * @param colNames
     * @return
     */
    public static String selectSql(String tableName, String[] colNames) {
        return selectSqlWhereParas(tableName, colNames, null);
    }

    /**
     * 给定表名和该表的id字段名称，拼成一条含有where条件的查询结果数量语句。
     *
     * @param tableName
     * @param field
     * @param where
     * @return
     */
    public static String countSqlWhereParas(String tableName, String field, String where) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT COUNT(" + field + ") FROM " + tableName + " ");
        if(StringUtils.isNotBlank(where)) { 
            sb.append(" ");
            sb.append(where);
        }
        return sb.toString();
    }   
    
    
    public static void main(String[] args) {
        /*String tableName = "PERSON";
        String[] obj = new String[]{"SEQ_ID", "NAME"};
        System.out.println(insertSql(tableName, obj));*/
        
        /*String tableName = "PERSON";
        String[] obj = new String[]{"NAME"};
        String where = "WHERE SEQ_ID = ?";
        System.out.println(updateSqlWhereParas(tableName, obj, where));*/
        
/*      String tableName = "PERSON";
        String[] obj = new String[]{"NAME"};
        System.out.println(updateSqlWhereId(tableName, obj));*/
        
/*      String tableName = "PERSON";
        String[] obj = new String[]{"NAME"};
        System.out.println(updateSql(tableName, obj));*/
        
/*      String tableName = "PERSON";
        String where = "WHERE SEQ_ID = ?";
        System.out.println(deleteSqlWhereParas(tableName, where));  */  
        
/*      String tableName = "PERSON";
        System.out.println(deleteSqlWhereId(tableName));    */      
        
/*      String tableName = "PERSON";
        System.out.println(deleteSql(tableName));   */  
                
        /*String tableName = "person p, department d";
        String[] obj = new String[]{"p.seq_id, p.user_name, p.user_priv, d.dept_name"};
        String where = "where p.dept_id = d.seq_id and p.seq_id = ?";
        System.out.println(selectSqlWhereParas(tableName, obj, where));     */
        
/*      String tableName = "PERSON";
        String[] obj = new String[]{"NAME, AGE"};
        System.out.println(selectSqlWhereId(tableName, obj));*/ 
        
/*      String tableName = "PERSON";
        String[] obj = new String[]{"NAME, AGE"};
        System.out.println(selectSql(tableName, obj));*/
        
//      String tableName = "PERSON";
//      String where = "WHERE SEQ_ID = ?";
//      System.out.println(countSqlWhereParas(tableName, "id", where));
        
        
    }

}