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'

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点！
本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。