清华大佬耗费三个月吐血整理的几百G的资源,免费分享!....>>>
1.SqlParameter.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | 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生成方法:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | /** * * @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.测试调用:
1 2 3 4 5 6 7 8 9 10 | 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输出:
1 | 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:
1 2 3 4 5 6 | 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:
1 | SELECT t.* FROM STUDENT_LOG t WHERE 1 = 1 AND t.stuName = '张三' AND t.stuAge = '18' |