|
1
55 package org.lateralnz.c3d.util;
56
57 import java.sql.Date  ;
58 import java.sql.PreparedStatement ;
59 import java.sql.ResultSet ;
60 import java.sql.SQLException ;
61 import java.sql.Time ;
62 import java.sql.Timestamp ;
63 import java.sql.Types ;
64 import java.math.BigDecimal ;
65 import java.util.regex.*;
66 import java.util.HashMap ;
67 import java.util.Iterator ;
68 import java.util.ArrayList ;
69 import java.util.List ;
70 import java.util.Map ;
71 import java.util.NoSuchElementException ;
72 import java.util.StringTokenizer ;
73
74 import org.lateralnz.common.util.Constants;
75 import org.lateralnz.common.util.StringUtils;
76
77
80 public class DBUtils implements Constants {
81
82 public static final int DELETE_STATEMENT = 1;
83 public static final int INSERT_STATEMENT = 2;
84 public static final int SELECT_STATEMENT = 3;
85 public static final int UPDATE_STATEMENT = 4;
86 public static final int CLEAR_CACHE_STATEMENT = 5;
87 public static final int QUERY_CACHE_STATEMENT = 6;
88 public static final int QUERY_CACHE_DUMP_STATEMENT = 7;
89 public static final int QUERY_STATS_STATEMENT = 8;
90
91 public static final String SEP = "::";
92
93 private static final String IGNORE_SPACES_TO_END = "\\s*$";
94 private static final String SET_CLAUSE_DELIMS = ",=";
95
96 private static final Pattern SPLIT_PATTERN = Pattern.compile("\\G(?:^|;)([^';]*+(?:'(?:[^'\\\\]|\\\\')*+'[^';]*+)*+)");
97 private static final Pattern CACHE_NAME_PATTERN = Pattern.compile("clear|query\\s*cache\\s*([^\\s]*).*", Pattern.DOTALL);
98 private static final Pattern DUMP_NAME_PATTERN = Pattern.compile("query\\s*cache\\s.*\\sdump\\s*'(.*)'$", Pattern.DOTALL);
99 private static final Pattern WHERE_CLAUSE_PATTERN = Pattern.compile("\\swhere\\s+(.*?)$", Pattern.DOTALL);
100 private static final Pattern INSERT_TABLE_PATTERN = Pattern.compile("insert\\s*into\\s*([^\\s]*).*", Pattern.DOTALL);
101 private static final Pattern UPDATE_TABLE_PATTERN = Pattern.compile("update\\s*([^\\s]*).*", Pattern.DOTALL);
102 private static final Pattern DELETE_TABLE_PATTERN = Pattern.compile("delete\\s*from\\s*([^\\s]*).*", Pattern.DOTALL);
103
104 private static final Pattern CLEAR_CACHE_PATTERN = Pattern.compile("^\\s*clear\\s*cache.*", Pattern.DOTALL);
105 private static final Pattern DELETE_PATTERN = Pattern.compile("^\\s*delete\\s*from.+", Pattern.DOTALL);
106 private static final Pattern INSERT_PATTERN = Pattern.compile("^\\s*insert\\s*into.+", Pattern.DOTALL);
107 private static final Pattern QUERY_CACHE_PATTERN = Pattern.compile("^\\s*query\\s*cache.*", Pattern.DOTALL);
108 private static final Pattern QUERY_CACHE_DUMP_PATTERN = Pattern.compile("^\\s*query\\s*cache.*\\sdump\\s.*", Pattern.DOTALL);
109 private static final Pattern QUERY_STATS_PATTERN = Pattern.compile("^\\s*query\\s*stats.*", Pattern.DOTALL);
110 private static final Pattern SELECT_PATTERN = Pattern.compile("^(/\\*.*\\*/)*\\s*select\\s.+", Pattern.DOTALL);
111 private static final Pattern UPDATE_PATTERN = Pattern.compile("^\\s*update\\s.+", Pattern.DOTALL);
112
113 private DBUtils() {
114 }
115
116
119 public static final int countSelects(List l) {
120 Iterator iter = l.iterator();
121 int count = 0;
122 while (iter.hasNext()) {
123 String s = (String )iter.next();
124 if (isStatementType(s, SELECT_STATEMENT)) {
125 count++;
126 }
127 }
128 return count;
129 }
130
131
135 public static final Column createColumn(ResultSet rs, int[] types, int idx) throws SQLException {
136 Column col = new Column(rs.getBytes(idx), types[idx-1]);
137 col.setNull(rs.wasNull());
138 return col;
139 }
140
141 public static final Column createColumn(String value) {
142 Column col = new Column(value.getBytes(), Types.VARCHAR);
143 col.setNull(false);
144 return col;
145 }
146
147 public static final Column[] createRow(String [] values) {
148 Column[] cols = new Column[values.length];
149 for (int i = 0; i < values.length; i++) {
150 if (values[i] == null) {
151 cols[i] = createColumn(EMPTY);
152 }
153 else {
154 cols[i] = createColumn(values[i]);
155 }
156 }
157 return cols;
158 }
159
160
161
164 public static final String flatten(Column[] cols, String delim) {
165 if (cols == null) {
166 return EMPTY;
167 }
168 StringBuffer sb = new StringBuffer ();
169 int max = cols.length - 1;
170 for (int i = 0; i < cols.length; i++) {
171 Object obj = cols[i].getObjectValue();
172 if (obj != null) {
173 sb.append(cols[i].getObjectValue().toString());
174 if (i < max) {
175 sb.append(delim);
176 }
177 }
178 }
179
180 return sb.toString();
181 }
182
183
184 public static final String getCacheNameFromKey(String key) {
185 String [] s = key.split(SEP);
186 if (s == null) {
187 return EMPTY;
188 }
189 else {
190 return s[0];
191 }
192 }
193
194
195
200 public static final String [] getKeyColumnData(int[] keyColumns, ResultSet rs) throws SQLException {
201 String [] keyData = new String [keyColumns.length];
202 for (int i = 0; i < keyColumns.length; i++) {
203 keyData[i] = rs.getString(keyColumns[i]);
204 }
205 return keyData;
206 }
207
208 public static final String getInsertTableName(String sql) {
209 Matcher matcher = INSERT_TABLE_PATTERN.matcher(sql);
210 String table = null;
211 if (matcher.find()) {
212 return matcher.group(1);
213 }
214 else {
215 return null;
216 }
217 }
218
219 public static final Object getObjectFromMap(Object key, Map m, Class newObjClass) throws SQLException {
220 if (!m.containsKey(key)) {
221 synchronized (m) {
222 if (!m.containsKey(key)) {
223 try {
224 m.put(key, newObjClass.newInstance());
225 }
226 catch (Exception e) {
227 throw new SQLException ("system error: " + e.getMessage());
228 }
229 }
230 }
231 }
232
233 return m.get(key);
234 }
235
236 public static final String getResultSetDataKey(String cache, String [] keyColumns) {
237 StringBuffer sb = new StringBuffer ();
238 sb.append(cache).append(SEP);
239 int len = keyColumns.length - 1;
240 for (int i = 0; i < keyColumns.length; i++) {
241 sb.append(keyColumns[i]);
242 if (i < len) {
243 sb.append(PIPE);
244 }
245 }
246 return sb.toString();
247 }
248
249 public static final String getTargetName(String sql, int type) {
250 Pattern pat;
251 switch (type) {
252 case INSERT_STATEMENT:
253 pat = INSERT_TABLE_PATTERN;
254 break;
255 case DELETE_STATEMENT:
256 pat = DELETE_TABLE_PATTERN;
257 break;
258 case UPDATE_STATEMENT:
259 pat = UPDATE_TABLE_PATTERN;
260 break;
261 case CLEAR_CACHE_STATEMENT:
262 case QUERY_CACHE_STATEMENT:
263 pat = CACHE_NAME_PATTERN;
264 break;
265 case QUERY_CACHE_DUMP_STATEMENT:
266 pat = DUMP_NAME_PATTERN;
267 break;
268 default:
269 return null;
270 }
271
272 Matcher matcher = pat.matcher(sql);
273 String table = null;
274 if (matcher.find()) {
275 return matcher.group(1);
276 }
277 else {
278 return null;
279 }
280 }
281
282
285 public static final String getWhereClause(String sql) {
286 Matcher matcher = WHERE_CLAUSE_PATTERN.matcher(sql);
287 if (matcher.find()) {
288 return matcher.group(1);
289 }
290 else {
291 return EMPTY;
292 }
293 }
294
295
296 public static final boolean isStatementType(String s, int type) {
297 Pattern pat;
298 switch (type) {
299 case DELETE_STATEMENT:
300 pat = DELETE_PATTERN;
301 break;
302 case INSERT_STATEMENT:
303 pat = INSERT_PATTERN;
304 break;
305 case SELECT_STATEMENT:
306 pat = SELECT_PATTERN;
307 break;
308 case UPDATE_STATEMENT:
309 pat = UPDATE_PATTERN;
310 break;
311 case CLEAR_CACHE_STATEMENT:
312 pat = CLEAR_CACHE_PATTERN;
313 break;
314 case QUERY_CACHE_DUMP_STATEMENT:
315 pat = QUERY_CACHE_DUMP_PATTERN;
316 break;
317 case QUERY_CACHE_STATEMENT:
318 pat = QUERY_CACHE_PATTERN;
319 break;
320 case QUERY_STATS_STATEMENT:
321 pat = QUERY_STATS_PATTERN;
322 break;
323
324 default:
325 return false;
326 }
327 s = s.trim();
328 Matcher m = pat.matcher(s);
329 return m.matches();
330 }
331
332
336 public static final void setParams(PreparedStatement ps, Column[] cols, int offset, int length) throws SQLException {
337 for (int i = offset, j = 1; i < cols.length && j <= length; i++, j++) {
338 if (cols[i].getNull()) {
339 if (cols[i].getObjectValue() == null) {
340 ps.setNull(j, cols[i].getType());
341 }
342 else {
343 ps.setNull(j, cols[i].getType(), (String )cols[i].getObjectValue());
344 }
345 continue;
346 }
347
348 switch (cols[i].getType()) {
349 case Types.BIGINT:
350 ps.setLong(j, ((Long )cols[i].getObjectValue()).longValue());
351 break;
352 case Types.BOOLEAN:
353 ps.setBoolean(j, Boolean.valueOf((String )cols[i].getObjectValue()).booleanValue());
354 break;
355 case Types.DATE:
356 ps.setDate(j, (Date )cols[i].getObjectValue());
357 break;
358 case Types.DOUBLE:
359 ps.setDouble(j, ((Double )cols[i].getObjectValue()).doubleValue());
360 break;
361 case Types.FLOAT:
362 ps.setFloat(j, ((Float )cols[i].getObjectValue()).floatValue());
363 break;
364 case Types.INTEGER:
365 ps.setInt(j, ((Integer )cols[i].getObjectValue()).intValue());
366 break;
367 case Types.NUMERIC:
368 ps.setBigDecimal(j, (BigDecimal )cols[i].getObjectValue());
369 break;
370 case Types.SMALLINT:
371 ps.setShort(j, ((Short )cols[i].getObjectValue()).shortValue());
372 break;
373 case Types.TIME:
374 ps.setTime(j, (Time )cols[i].getObjectValue());
375 break;
376 case Types.TIMESTAMP:
377 ps.setTimestamp(j, (Timestamp )cols[i].getObjectValue());
378 break;
379 case Types.TINYINT:
380 ps.setByte(j, ((Byte )cols[i].getObjectValue()).byteValue());
381 break;
382 case Types.VARBINARY:
383 ps.setBytes(j, cols[i].getValue());
384 break;
385 case Types.VARCHAR:
386 ps.setString(j, (String )cols[i].getObjectValue());
387 break;
388 }
389 }
390 }
391
392
397 public static final List splitSQL(String s) {
398 ArrayList ll = new ArrayList ();
399
400 Matcher matcher = SPLIT_PATTERN.matcher(s);
401 while(matcher.find()) {
402 String field = matcher.group(1);
403
404 field = field.trim();
405 if (!StringUtils.isEmpty(field)) {
406 ll.add(field);
407 }
408 }
409 return ll;
410 }
411
412
415 public static final Map splitSet(String setSQL) throws SQLException {
416 StringTokenizer st = new StringTokenizer (setSQL, SET_CLAUSE_DELIMS, true);
417 try {
418 HashMap rtn = new HashMap ();
419 while (st.hasMoreTokens()) {
420 String col = st.nextToken();
421 if (!st.nextToken().equals(EQUALS)) {
422 throw new SQLException ("unexpected token in update columns");
423 }
424 String tmp;
425 StringBuffer val = new StringBuffer ();
426 while (st.hasMoreTokens()) {
427 tmp = st.nextToken();
428 if (tmp.equals(COMMA) && (StringUtils.countOccurrences(val, '\'') % 2) == 0) {
429 break;
430 }
431 val.append(tmp);
432 }
433 rtn.put(col, val.toString());
434 }
435 return rtn;
436 }
437 catch (SQLException se) {
438 throw se;
439 }
440 catch (NoSuchElementException nsee) {
441 throw new SQLException ("error parsing columns in update");
442 }
443 }
444
445 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.