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'