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'
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值