Java生成 sql查询语句 通用方法（带排序/分页）

2015/08/28 44016

清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>

1.SqlParameter.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
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生成方法：

?
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
/**
     *
     * @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.测试调用：

?
1
2
3
4
5
6
7
8
9
10
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输出：

?
1
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：

?
1
2
3
4
5
6
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：

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