Java生成 sql查询语句 通用方法（带排序/分页），javasql,1.SqlParamet

Java生成 sql查询语句 通用方法（带排序/分页），javasql,1.SqlParamet

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 &amp;&amp; !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) &amp;&amp; (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'