Java生成sqlクエリ文汎用メソッド(ソート/ページング付き)
3108 ワード
1.SqlParameter.java
2.sql生成方法:
3.テストコール:
sql出力:
テスト2:
sql出力2:
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'