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'