Java生成 sql查詢語句 通用方法（帶排序/分頁）

1.SqlParameter.java

package com.wuhx.util;

public class SqlParameter {
    private String tableName;     //物理表名
    private Integer minrow = 1;  //分頁最小行[默認1]
    private Integer maxrow;  //分頁最大行
    private String[] orderBy; //排序eg: {"columnA","columnB DESC"}


    public Integer getMinrow() {
        return minrow;
    }
    public void setMinrow(Integer minrow) {
        this.minrow = minrow;
    }
    public Integer getMaxrow() {
        return maxrow;
    }
    public void setMaxrow(Integer maxrow) {
        this.maxrow = maxrow;
    }
    public String[] getOrderBy() {
        return orderBy;
    }
    public void setOrderBy(String[] orderBy) {
        this.orderBy = orderBy;
    }
    public String getTableName() {
        return tableName;
    }
    public void setTableName(String tableName) {
        this.tableName = tableName;
    }
}

2.sql生成方法：

/**
     * 
     * @param obj     SQL參數
     * @param param   分頁/排序參數
     * @return        
     * @throws Exception
     */
    public static String createSQL(Object obj, SqlParameter param) throws Exception {
        StringBuilder sb = new StringBuilder("SELECT t.* FROM "+param.getTableName()+" t WHERE 1=1 ");
        Field[] fields = obj.getClass().getDeclaredFields();
        for(Field f: fields){
            f.setAccessible(true);
            Object fName = f.getName();
            Object fValue = f.get(obj);
            if(fValue != null && !fValue.equals("")){
                sb.append(" AND t."+fName+" = '"+fValue+"'");
            }
            f.setAccessible(false);
        }
        if(param.getOrderBy() != null){
            String orderStr = " ORDER BY ";
            for(String str:param.getOrderBy()){
                orderStr += " "+str+",";
            }
            orderStr = orderStr.substring(0,orderStr.length()-1);
            sb.append(orderStr);
        }
        if((param.getMinrow() != null) && (param.getMaxrow() != null)){
            StringBuilder sb2 = new StringBuilder("SELECT * FROM ( SELECT A.*, ROWNUM  RN FROM  ( ");
            sb2.append(sb.toString());
            sb2.append( " ) A WHERE ROWNUM <= "+param.getMaxrow()+" ) WHERE RN >= "+param.getMinrow() );
            sb = sb2;
        }
        return sb.toString();
    }

3.測試調用：

Teacher t = new Teacher();
        t.setTeaId(123456789);
        //t.setTeaLevel("副教授");
        //t.setTeaName("王老師");
        SqlParameter p = new SqlParameter();
        p.setTableName("PROPAGANDA_LOG");
        p.setOrderBy(new String[]{"columnA","columnB DESC"});
        p.setMaxrow(10);
        p.setMinrow(5);
        System.out.println(createSQL(t,p));

sql輸出：

SELECT * FROM ( SELECT A.*, ROWNUM  RN FROM  ( SELECT t.* FROM PROPAGANDA_LOG t WHERE 1=1  AND t.teaId = '123456789' ORDER BY  columnA, columnB DESC ) A WHERE ROWNUM <= 10 ) WHERE RN >= 5

 測試2：

 Student s = new Student();
        s.setStuAge("18");
        s.setStuName("張三");
        SqlParameter p = new SqlParameter();
        p.setTableName("STUDENT_LOG");
        System.out.println(createSQL(s,p));

sql輸出2：

SELECT t.* FROM STUDENT_LOG t WHERE 1=1  AND t.stuName = '張三' AND t.stuAge = '18'