工具类分页工具类PageParamBean

自己编写的分页工具类，根据不同的数据库类型，生成对应的分页sql信息，分享给大家，希望大家共勉，工具类有些地方，大家可能不需要，请根绝自己的需要进行修改使用，核心逻辑都在，如果大家觉得有什么不妥，欢迎大家随时指正
  1 package com.ideal.ieai.server.util;
  2 
  3 import com.ideal.ieai.core.JudgeDB;
  4 
  5 /**   
  6  * @ClassName:  PageParamBean   
  7  * @Description: 根据数据库类型不同生成不同的分页方法的工具类  
  8  * @author: yue_sun 
  9  * @date:   2019年1月3日 下午12:53:09   
 10  *     
 11  * @Copyright: 2019-2027 www.idealinfo.com Inc. All rights reserved. 
 12  * 
 13  */
 14 public class PageParamBean
 15 {
 16     private String sql;
 17     private int    pagePara1 = 0;
 18     private int    pagePara2 = 0;
 19 
 20     private static String        mysql     = "mysql";
 21     private static String        oracle    = "oracle";
 22     private static String        db2       = "db2";
 23 
 24     private static PageParamBean instance  = null;
 25 
 26     private PageParamBean()
 27     {
 28 
 29     }
 30 
 31     public static PageParamBean getInstance ()
 32     {
 33         if (null == instance)
 34         {
 35             instance = new PageParamBean();
 36         }
 37         return instance;
 38     }
 39 
 40     public String getSql ()
 41     {
 42         return sql;
 43     }
 44 
 45     public void setSql ( String sql )
 46     {
 47         this.sql = sql;
 48     }
 49 
 50     public int getPagePara1 ()
 51     {
 52         return pagePara1;
 53     }
 54 
 55     public void setPagePara1 ( int pagePara1 )
 56     {
 57         this.pagePara1 = pagePara1;
 58     }
 59 
 60     public int getPagePara2 ()
 61     {
 62         return pagePara2;
 63     }
 64 
 65     public void setPagePara2 ( int pagePara2 )
 66     {
 67         this.pagePara2 = pagePara2;
 68     }
 69 
 70     /**   
 71      * @Title: getParamPageBean   
 72      * @Description: 获取分页sql及分页参数方法
 73      * @param sql
 74      * @param start
 75      * @param limit
 76      * @return      
 77      * @author: yue_sun 
 78      * @date:   2019年1月2日 上午10:09:25   
 79      */
 80     public PageParamBean getParamPageBean ( String sql, int start, int limit )
 81     {
 82         PageParamBean paramBean = new PageParamBean();
 83         int pageParam1 = 0;
 84         int pageParam2 = 0;
 85         switch (JudgeDB.IEAI_DB_TYPE)
 86         {
 87             case 1:
 88                 sql = getQueryPageSQL(oracle, sql);
 89                 pageParam1 = start + limit;
 90                 pageParam2 = start;
 91                 break;
 92             case 2:
 93                 sql = getQueryPageSQL(db2, sql);
 94                 pageParam1 = start + 1;
 95                 pageParam2 = start + limit;
 96                 break;
 97             case 3:
 98                 sql = getQueryPageSQL(mysql, sql);
 99                 pageParam1 = start;
100                 pageParam2 = limit;
101                 break;
102             default:
103                 break;
104         }
105         paramBean.setSql(sql);
106         paramBean.setPagePara1(pageParam1);
107         paramBean.setPagePara2(pageParam2);
108         return paramBean;
109     }
110 
111     public PageParamBean getParamPageBean ( String sql, String order, int start, int limit )
112     {
113         PageParamBean paramBean = new PageParamBean();
114         int pageParam1 = 0;
115         int pageParam2 = 0;
116         switch (JudgeDB.IEAI_DB_TYPE)
117         {
118             case 1:
119                 sql = getQueryPageSQLNew(oracle, order, sql);
120                 pageParam1 = start + limit;
121                 pageParam2 = start;
122                 break;
123             case 2:
124                 sql = getQueryPageSQLNew(db2, order, sql);
125                 pageParam1 = start;
126                 pageParam2 = start + limit;
127                 break;
128             case 3:
129                 sql = getQueryPageSQLNew(mysql, order, sql);
130                 pageParam1 = start;
131                 pageParam2 = limit;
132                 break;
133             default:
134                 break;
135         }
136         paramBean.setSql(sql);
137         paramBean.setPagePara1(pageParam1);
138         paramBean.setPagePara2(pageParam2);
139         return paramBean;
140     }
141 
142     /**   
143      * @Title: getQueryPageSQL   
144      * @Description: 组织各种数据库的分页查询sql  
145      * @param dbType
146      * @param sql
147      * @return      
148       * @author: yue_sun 
149      * @date:   2019年1月3日 上午9:40:40   
150      */
151     public static String getQueryPageSQL ( String dbType, String sql )
152     {
153         StringBuilder stringBuffer = new StringBuilder();
154         if (dbType.equals("mysql"))
155         {
156             stringBuffer.append(sql);
157             stringBuffer.append(" limit ?,?");
158         }
159         if (dbType.equals("oracle"))
160         {
161             stringBuffer.append("select * from ( select row_.*, rownum rownum_ from ( ");
162             stringBuffer.append(sql);
163             stringBuffer.append(" ) row_ where rownum <= ?) where rownum_ > ?");
164         }
165         if (dbType.equals("microsoft sql server"))
166         {
167             stringBuffer.append("select top ? ");
168             if (sql.indexOf("order by") != -1)
169             {
170                 stringBuffer.append(sql.substring(sql.indexOf("select") + 7, sql.indexOf("order by")));
171 
172             } else if (sql.indexOf("group by") != -1)
173             {
174 
175             } else
176             {
177                 stringBuffer.append(sql.substring(sql.indexOf("select") + 7, sql.length()));
178             }
179             stringBuffer.append("  where 1 not in (select top ? 1 ");
180             stringBuffer.append(sql.substring(sql.indexOf("from"), sql.length()));
181             stringBuffer.append(" )");
182         }
183         if (dbType.equals("postgresql"))
184         {
185             stringBuffer.append(sql);
186             stringBuffer.append(" limit ? offset ?");
187         }
188         if (dbType.indexOf("db2") != -1)
189         {
190             stringBuffer.append("select * from( select rownumber() over() as rownum_ ,a1.* from ( ");
191 
192             stringBuffer.append(sql);
193 
194             stringBuffer.append(" ) a1) a2 where a2.rownum_ between ? and ? ");
195         }
196 
197         if (dbType.indexOf("hsql") != -1)
198         {
199             stringBuffer.append("select limit ? ? * from (");
200             stringBuffer.append(sql);
201             stringBuffer.append(" )");
202         }
203 
204         return stringBuffer.toString();
205     }
206 
207     /**   
208      * @Title: getQueryPageSQLNew   
209      * @Description:   重载该方法，oracle 和 DB2 都用 > 和 <= 来做分页（加入order排序条件）
210      * @param dbType
211      * @param order
212      * @param sql
213      * @return      
214      * @author: yue_sun 
215      * @date:   2019年1月3日 上午9:40:40   
216      */
217     public static String getQueryPageSQLNew ( String dbType, String order, String sql )
218     {
219         StringBuilder stringBuffer = new StringBuilder();
220         if (dbType.equals("mysql"))
221         {
222             stringBuffer.append(sql);
223             stringBuffer.append(" " + order + " ");
224             stringBuffer.append(" limit ?,?");
225         }
226         if (dbType.equals("oracle"))
227         {
228             stringBuffer.append("SELECT * FROM  ( SELECT  row_number() over(" + order + ") AS RN ,  A.* FROM ( ");
229             stringBuffer.append(sql);
230             stringBuffer.append(" )  A  ) B WHERE  B.RN <= ? and B.RN > ?");
231         }
232         if (dbType.equals("microsoft sql server"))
233         {
234             stringBuffer.append("select top ? ");
235             if (sql.indexOf("order by") != -1)
236             {
237                 stringBuffer.append(sql.substring(sql.indexOf("select") + 7, sql.indexOf("order by")));
238 
239             } else
240             {
241                 stringBuffer.append(sql.substring(sql.indexOf("select") + 7, sql.length()));
242             }
243             stringBuffer.append("  where 1 not in (select top ? 1 ");
244             stringBuffer.append(sql.substring(sql.indexOf("from"), sql.length()));
245             stringBuffer.append(" )");
246         }
247         if (dbType.equals("postgresql"))
248         {
249             stringBuffer.append(sql);
250             stringBuffer.append(" limit ? offset ?");
251         }
252         if (dbType.indexOf("db2") != -1)
253         {
254             stringBuffer.append("SELECT * FROM  ( SELECT  ROW_NUMBER() OVER(" + order + ") AS RN ,  A.* FROM (");
255 
256             stringBuffer.append(sql);
257 
258             stringBuffer.append(" )  A  ) B WHERE  B.RN > ? and B.RN <= ?");
259         }
260 
261         if (dbType.indexOf("hsql") != -1)
262         {
263             stringBuffer.append("select limit ? ? * from (");
264             stringBuffer.append(sql);
265             stringBuffer.append(" )");
266         }
267 
268         return stringBuffer.toString();
269     }
270 
271     /**   
272      * @Title: toCount   
273      * @Description: 生成查询总数的sql的方法  
274      * @param sql
275      * @return      
276      * @author: yue_sun 
277      * @date:   2019年1月2日 上午10:12:25   
278      */
279     public String toCount ( String sql )
280     {
281         StringBuilder stringBuilder = new StringBuilder();
282         stringBuilder.append("SELECT COUNT(1) AS COUNT FROM ( ");
283         stringBuilder.append(sql);
284         stringBuilder.append(" ) TOTAL");
285         return stringBuilder.toString();
286     }
287 
288 }
posted on 2019-11-26 08:49 书未来阅读(1393) 评论(0) 编辑收藏举报