1
2
3
4
5 package gboat2.base.dao.impl;
6
7 import gboat2.base.core.annotation.Relation;
8 import gboat2.base.core.annotation.RelationType;
9 import gboat2.base.core.annotation.Relations;
10 import gboat2.base.core.dao.QuerySupport;
11 import gboat2.base.dao.exception.TransientException;
12 import gboat2.base.dao.util.ClassHelper;
13 import gboat2.base.dao.util.GBoatDaoClassLoader;
14
15 import java.io.UnsupportedEncodingException;
16 import java.lang.annotation.Annotation;
17 import java.lang.reflect.Field;
18 import java.lang.reflect.Method;
19 import java.net.URLEncoder;
20 import java.util.HashMap;
21 import java.util.LinkedList;
22 import java.util.List;
23 import java.util.Map;
24 import java.util.Map.Entry;
25 import java.util.Set;
26 import java.util.regex.Matcher;
27 import java.util.regex.Pattern;
28
29 import javax.persistence.Column;
30 import javax.persistence.Entity;
31 import javax.persistence.Table;
32 import javax.persistence.Transient;
33
34 import org.apache.commons.lang3.StringUtils;
35 import org.hibernate.dialect.Dialect;
36 import org.slf4j.Logger;
37 import org.slf4j.LoggerFactory;
38
39 public class GenerateQL {
40
41 private static Map<String, String> tableAliases = new HashMap<String, String>();
42
43 private static Map<String, String> oracleDateFormats = new HashMap<String, String>();
44
45 private static Map<String, String> mysqlDateFormats = new HashMap<String, String>();
46
47 private static Map<String, String> operas = new HashMap<String, String>();
48
49 private Logger logger = LoggerFactory.getLogger(QuerySupport.class);
50
51 public static final String PARAM_TOP = "top";
52
53 static {
54 operas.put("like", "like");
55 operas.put("eq", "=");
56 operas.put("lt", "<");
57 operas.put("gt", ">");
58 operas.put("ne", "<>");
59 operas.put("le", "<=");
60 operas.put("ge", ">=");
61 operas.put("null", "is null");
62 operas.put("notnull", "is not null");
63 operas.put("in", "in");
64 operas.put("notin", "not in");
65 operas.put("or", "or");
66
67 }
68
69 static {
70 oracleDateFormats.put("^[0-9]{4}\\-[0-9]{1,2}\\-[0-9]{1,2}$", "YYYY-MM-DD");
71 oracleDateFormats.put("^[0-9]{4}\\-[0-9]{1,2}\\-[0-9]{1,2} [0-9]{1,2}\\:[0-9]{1,2}\\:[0-9]{1,2}$", "YYYY-MM-DD HH24:MI:SS");
72 oracleDateFormats.put("^[0-9]{4}\\-[0-9]{1,2}\\-[0-9]{1,2} [0-9]{1,2}\\:[0-9]{1,2}$", "YYYY-MM-DD HH24:MI");
73 oracleDateFormats.put("^[0-9]{4}\\-[0-9]{1,2}\\-[0-9]{1,2} [0-9]{1,2}$", "YYYY-MM-DD HH24");
74
75 mysqlDateFormats.put("^[0-9]{4}\\-[0-9]{1,2}\\-[0-9]{1,2}$", "%Y-%m-%d");
76 mysqlDateFormats.put("^[0-9]{4}\\-[0-9]{1,2}\\-[0-9]{1,2} [0-9]{1,2}\\:[0-9]{1,2}\\:[0-9]{1,2}$", "%Y-%m-%d %T");
77 mysqlDateFormats.put("^[0-9]{4}\\-[0-9]{1,2}\\-[0-9]{1,2} [0-9]{1,2}\\:[0-9]{1,2}$", "%Y-%m-%d %H:%i");
78 mysqlDateFormats.put("^[0-9]{4}\\-[0-9]{1,2}\\-[0-9]{1,2} [0-9]{1,2}$", "%Y-%m-%d %H");
79 }
80
81
82 private static String[] patternFilter = { "select", "insert", "delete", "from", "count\\(", "drop", "update", "truncate", "asc\\(", "mid\\(",
83 "char\\(", "xp_cmdshell", "exec master", "netlocalgroup administrators", "net user", "or", "and", "\\<", "\\>", "\\*", "\\-", "\\?",
84 "\\,", "\\/", "\\;", "\\*/", "\r\n", "\\'" };
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111 public String generateHQL(Map<String, Object> params, Dialect dialect) {
112 Class<?> poClass = getTableClass(params);
113
114 String hql = null;
115
116 if (poClass.isAnnotationPresent(Entity.class)) {
117 hql = generateHqlFromPO(params, poClass, dialect);
118 } else {
119 throw new RuntimeException(poClass.getName() + " isn't assigned with @Entity");
120 }
121
122 return hql;
123 }
124
125 public String generateSQL(Map<String, Object> params, Dialect dialect) {
126 Class<?> voClass = getTableClass(params);
127
128 String hql = null;
129
130 if (voClass.isAnnotationPresent(Relations.class)) {
131 hql = generateSqlFromVO(params, voClass, dialect);
132 } else {
133 throw new RuntimeException(voClass.getName() + " isn't assigned with @Relations");
134 }
135
136 return hql;
137 }
138
139 public Class<?> getTableClass(Map<String, Object> params) {
140 Object setted = params.get(QuerySupport.PARAM_TABLENAME);
141 if (setted instanceof java.lang.Class<?>) {
142 return (Class<?>) setted;
143 } else if (!(setted instanceof String)) {
144 throw new RuntimeException("QuerySupport.PARAM_TABLENAME should be setted by type String or type Class");
145 }
146
147 String table = (String) params.get(QuerySupport.PARAM_TABLENAME);
148
149 if (null == table || "".equals(table)) {
150 throw new RuntimeException("Table should be setted for Query. use QuerySupport.PARAM_TABLENAME as the key to set the param.");
151 }
152
153 Class<?> clazz = null;
154 if (table.indexOf(".") == -1) {
155 throw new RuntimeException("Class name should include the package");
156 } else {
157 try {
158 clazz = GBoatDaoClassLoader.getInstance().loadClass(table);
159
160 } catch (ClassNotFoundException e) {
161 clazz = null;
162 }
163 }
164 if (clazz == null) {
165 throw new RuntimeException("Class [" + table + "] isn't exist.");
166 }
167
168 return clazz;
169 }
170
171
172
173
174
175
176
177
178
179
180
181 private String generateSqlFromVO(Map<String, Object> params, Class<?> voClass, Dialect dialect) {
182 StringBuffer sql = new StringBuffer();
183 StringBuffer whereClause = new StringBuffer();
184 String orderby = (String) params.get(QuerySupport.PARAM_ORDERBY);
185 sql.append("select ");
186
187 if (params.containsKey(QuerySupport.PARAM_DISTINCT)) {
188 Object dis = params.get(QuerySupport.PARAM_DISTINCT);
189 if (dis instanceof Boolean && (Boolean) dis) {
190 sql.append(" distinct ");
191 } else if (dis instanceof String && Boolean.valueOf((String) dis)) {
192 sql.append(" distinct ");
193 }
194 }
195 if (params.containsKey(QuerySupport.PARAM_TOP)) {
196 Object top = params.get(QuerySupport.PARAM_TOP);
197 if (top instanceof Integer || (top instanceof String && !"".equals(top.toString()) && top.toString().trim().matches("[0-9]+"))) {
198 sql.append(" top "+top+" ");
199 }
200 }
201
202 dealSelectFields(voClass, sql);
203 sql.append(" from ");
204
205 dealTableRelations(voClass, sql, params);
206
207
208 List<String> voFields = getFieldsWithParents(voClass);
209 String key;
210 String opera, paramValue, operaSymble, paramKeys, fieldClause;
211 for (Entry<String, Object> entry : params.entrySet()) {
212
213 key = entry.getKey();
214 if (key.startsWith("_")) {
215 paramKeys = key.substring(1);
216 } else {
217 continue;
218 }
219 paramValue = getParamValue(params.get(key));
220 if (key.endsWith("_null") || key.endsWith("_notnull") || (null != paramValue && !"".equals(paramValue))) {
221 if (key.equals("_block")) {
222 if (whereClause.length() > 0) {
223 whereClause.append(" and ");
224 }
225 whereClause.append("(").append(getBlockValue(paramValue, voClass)).append(")");
226 continue;
227 }
228 opera = "=";
229 if (paramKeys.indexOf("_") != -1) {
230 operaSymble = paramKeys.substring(paramKeys.lastIndexOf("_") + 1);
231 opera = operas.get(operaSymble);
232 if (opera == null) {
233 throw new RuntimeException("invalid opera in '" + key + "'");
234 }
235 paramKeys = paramKeys.substring(0, paramKeys.lastIndexOf("_"));
236 }
237
238
239 if (("like".equals(opera) && paramValue.equals(""))
240 || (paramValue.indexOf("%") != -1 && (paramValue.equals("%%") || paramValue.equals("%") || paramValue.equals("%null%")))) {
241 continue;
242 }
243
244
245
246
247
248 fieldClause = dealFieldForVo(voFields, voClass, dialect, key, opera, paramValue, paramKeys);
249 if (!StringUtils.isEmpty(fieldClause)) {
250
251 if (whereClause.length() > 0) {
252 whereClause.append(" and ");
253 }
254
255 whereClause.append(fieldClause);
256 }
257 }
258 }
259 if (whereClause.length() > 0) {
260 sql.append(" where ").append(whereClause);
261 }
262
263 if (null != orderby && !"".equals(orderby)) {
264 dealOrderby(voClass, sql, orderby);
265 }
266
267 return sql.toString();
268 }
269
270 private String dealFieldForVo(List<String> voFields, Class<?> voClass, Dialect dialect, String key, String opera, String paramValue,
271 String paramKeys) {
272 StringBuilder fieldClause = new StringBuilder();
273 String[] splitedKeys = paramKeys.split("#");
274 String paramKey, field;
275 if (splitedKeys.length > 1) {
276 fieldClause.append("(");
277 }
278 Field f = null;
279 for (int i = 0; i < splitedKeys.length; i++) {
280 if (i > 0) {
281 fieldClause.append(" or ");
282 }
283 paramKey = splitedKeys[i];
284
285 if (voFields.contains(paramKey)) {
286 field = paramKey;
287 f = ClassHelper.getField(voClass, field);
288 } else {
289 field = ClassHelper.getFieldNameIgnoreCase(voClass, paramKey);
290 if (field != null) {
291 logger.warn("the filed[" + paramKey + "] should be '" + field + "',it's corrected by system");
292 f = ClassHelper.getField(voClass, field);
293 }
294 }
295
296 if (f == null) {
297 logger.warn("invalid field[" + key + "]. it's ignored. please check it.");
298 return "";
299 } else {
300 String columnName = getColumnName(f.getAnnotation(gboat2.base.core.annotation.Field.class), f);
301 Class<?> fieldType;
302 if ("like".equals(opera) || paramValue.startsWith("%") || paramValue.endsWith("%")) {
303 if (paramValue.indexOf("%") == -1) {
304 paramValue = "%" + paramValue + "%";
305 }
306 fieldClause.append(columnName).append(" like '").append(paramValue).append("'");
307 } else if ("or".equals(opera)) {
308 String[] values = paramValue.split("\\|\\|");
309 fieldClause.append("(");
310 int in = 0;
311 for (String singal : values) {
312 if (in++ > 0) {
313 fieldClause.append(" ").append(opera).append(" ");
314 }
315 if ("".equals(singal)) {
316 fieldClause.append(columnName).append(" is null");
317 } else if (("null").equalsIgnoreCase(singal) || ("not null").equalsIgnoreCase(singal) || ("notnull").equalsIgnoreCase(singal)) {
318 fieldClause.append(columnName).append(" is ").append(singal);
319 } else {
320 fieldClause.append(columnName).append(" = '").append(singal).append("'");
321 }
322 }
323 fieldClause.append(")");
324 } else if ("in".equals(opera) || "not in".equals(opera)) {
325 fieldClause.append(columnName).append(" ").append(opera).append(" (").append(paramValue).append(")");
326 } else if ("is null".equals(opera) || "is not null".equals(opera)) {
327 fieldClause.append(columnName).append(" ").append(opera);
328 } else {
329 Field fieldObj = ClassHelper.getField(voClass, field);
330 fieldType = fieldObj.getType();
331 if (fieldType == java.util.Date.class || fieldType == java.sql.Date.class) {
332
333 Map<?, ?> functions = dialect.getFunctions();
334 if (functions.containsKey("to_char")) {
335 fieldClause.append("to_char(").append(columnName).append(",'" + getDateFormatForOracle(paramValue) + "') ");
336 fieldClause.append(opera).append(" '").append(paramValue).append("'");
337 } else if (functions.containsKey("date_format")) {
338 fieldClause.append(columnName).append(opera).append("date_format('").append(paramValue).append(
339 "','" + getDateFormatForMysql(paramValue) + "')");
340 }
341 } else {
342 fieldClause.append(columnName).append(" ").append(opera).append(" :").append(key);
343 }
344 }
345 }
346 }
347 if (splitedKeys.length > 1) {
348 fieldClause.append(")");
349 }
350 return fieldClause.toString();
351 }
352
353 private String getDateFormatForOracle(String value) {
354 String format = null;
355 for (Entry<String, String> e : oracleDateFormats.entrySet()) {
356 if (Pattern.matches(e.getKey(), value)) {
357 format = e.getValue();
358 break;
359 }
360 }
361
362 if (format == null) {
363 throw new RuntimeException("invalid value format for Date : " + value);
364 } else {
365 return format;
366 }
367 }
368
369 private String getDateFormatForMysql(String value) {
370 String format = null;
371 for (Entry<String, String> e : mysqlDateFormats.entrySet()) {
372 if (Pattern.matches(e.getKey(), value)) {
373 format = e.getValue();
374 break;
375 }
376 }
377
378 if (format == null) {
379 throw new RuntimeException("invalid value format for Date : " + value);
380 } else {
381 return format;
382 }
383 }
384
385 private List<String> getFieldsWithParents(Class<?> voClass) {
386 List<String> voFields = ClassHelper.getFieldNames(voClass);
387 Class<?> superClass = voClass.getSuperclass();
388 while (!superClass.getSimpleName().equals("Object")) {
389 voFields.addAll(ClassHelper.getFieldNames(superClass));
390 superClass = superClass.getSuperclass();
391 }
392 return voFields;
393 }
394
395 private String getParamValue(Object valueObject) {
396 String value;
397 if (valueObject instanceof String) {
398 value = (String) valueObject;
399 } else if (valueObject instanceof String[] && ((String[]) valueObject).length == 1) {
400 value = ((String[]) valueObject)[0];
401 } else if (valueObject == null) {
402 value = "";
403 } else {
404 value = valueObject.toString();
405 }
406 return value;
407 }
408
409 private void dealSelectFields(Class<?> voClass, StringBuffer sql) {
410
411 Field[] fields = voClass.getDeclaredFields();
412 int index = 0;
413 gboat2.base.core.annotation.Field fieldAnnoted;
414 List<String> voFieldNames = new LinkedList<String>();
415 for (Field fieldOfClass : fields) {
416 fieldAnnoted = fieldOfClass.getAnnotation(gboat2.base.core.annotation.Field.class);
417 if (fieldAnnoted != null && fieldAnnoted.query()) {
418 if (index++ != 0) {
419 sql.append(",");
420 }
421 sql.append(getColumnName(fieldAnnoted, fieldOfClass));
422 sql.append(" as ").append(fieldOfClass.getName().toLowerCase());
423 }
424 voFieldNames.add(fieldOfClass.getName());
425 }
426
427 Class<?> superClass = voClass.getSuperclass();
428 if (!superClass.getSimpleName().equals("Object")) {
429 fields = superClass.getDeclaredFields();
430 String columnName = null;
431 for (Field fieldOfSuperClass : fields) {
432 if (!voFieldNames.contains(fieldOfSuperClass.getName())) {
433 try {
434 columnName = getColumnName(superClass, fieldOfSuperClass.getName());
435 if (index++ != 0) {
436 sql.append(",");
437 }
438 sql.append(columnName);
439 sql.append(" as ").append(fieldOfSuperClass.getName().toLowerCase());
440 } catch (TransientException e) {
441 logger.debug(columnName + " is @Transient.");
442 }
443 }
444 }
445 }
446 }
447
448 private void dealOrderby(Class<?> voClass, StringBuffer sql, String orderby) {
449 String[] orderItems = orderby.split(",");
450 StringBuffer convertedOrderby = new StringBuffer();
451 for (String orderItem : orderItems) {
452 if (convertedOrderby.length() > 1) {
453 convertedOrderby.append(",");
454 }
455 if (orderItem.indexOf(" ") != -1) {
456 String[] spliteds = orderItem.split(" ");
457 for (String splited : spliteds) {
458 if (splited.equalsIgnoreCase("desc") || splited.equalsIgnoreCase("asc")) {
459 convertedOrderby.append(" ").append(splited);
460 } else if (splited.indexOf(".") != -1) {
461 convertedOrderby.append(convertFieldToDbColumn(splited, voClass));
462 } else {
463 convertedOrderby.append(splited);
464 convertedOrderby.append(" ");
465 }
466 }
467 } else if (orderItem.indexOf(".") != -1) {
468 convertedOrderby.append(convertFieldToDbColumn(orderItem, voClass));
469 } else {
470 convertedOrderby.append(orderItem);
471 }
472 }
473 sql.append(" order by ").append(convertedOrderby);
474 }
475
476 private void dealTableRelations(Class<?> voClass, StringBuffer sql, Map<String,Object> params) {
477 Annotation[] annots = voClass.getDeclaredAnnotations();
478 Class<?> base;
479 for (Annotation annot : annots) {
480 if (annot.annotationType() == Relations.class) {
481 Relations relAnnot = (Relations) annot;
482 Relation[] relations = ((Relations) annot).value();
483 sql.append(getTableName(relAnnot.base())).append(" ").append(getTableAlias(relAnnot.base()));
484 for (Relation rel : relations) {
485 String join = " inner join ";
486 if (rel.type() == RelationType.LEFT) {
487 join = " left join ";
488 } else if (rel.type() == RelationType.RIGHT) {
489 join = " right join ";
490 } else if (rel.type() == RelationType.FULL) {
491 join = " full join ";
492 }
493 sql.append(join).append(getTableName(rel.refer())).append(" ").append(getTableAlias(rel.refer())).append(" on ");
494
495 if (rel.base() != Relation.DefaultBase.class) {
496 base = rel.base();
497 } else {
498 base = relAnnot.base();
499 }
500 if(StringUtils.isEmpty(rel.on())){
501 String[] baseColumns = rel.baseColumn();
502 String[] referColumns = rel.referColumn();
503 if (baseColumns.length != referColumns.length) {
504 throw new RuntimeException("baseColumns's size should the same with referColumns's size in " + voClass.getName());
505 } else {
506 for (int i = 0; i < baseColumns.length; i++) {
507 if (i > 0) {
508 sql.append(" and ");
509 }
510 sql.append(getColumnName(base, baseColumns[i]));
511 sql.append("=");
512 sql.append(getColumnName(rel.refer(), referColumns[i]));
513 }
514 }
515 }else{
516 sql.append(dealRelationByOn(rel.on(),base,rel.refer(), params));
517 }
518 }
519 }
520 }
521 }
522
523 private String dealRelationByOn(String on,Class<?> base,Class<?> refer,Map<String,Object> params){
524 String result = on;
525
526
527 Pattern pattern = Pattern.compile("\\[[\\w\\.]+\\]");
528 Matcher matcher = pattern.matcher(on);
529 String matched, fieldName, columnName;
530 while (matcher.find()) {
531 matched = matcher.group();
532 fieldName = matched.substring(1).substring(0, matched.length() - 2);
533 if(fieldName.indexOf(".")!=-1){
534 String[] splited = fieldName.split("\\.");
535 if(splited[0].equals(base.getSimpleName())){
536 columnName = getColumnName(base,splited[1]);
537 }else if(splited[0].equals(refer.getSimpleName())){
538 columnName = getColumnName(refer,splited[1]);
539 }else{
540 throw new RuntimeException("invalid PO["+splited[0]+"] in Relation");
541 }
542 }else{
543 columnName = getColumnName(refer,fieldName);
544 }
545 result = result.replace(matched, columnName);
546 }
547 pattern = Pattern.compile("\\{\\w+\\}");
548 matcher = pattern.matcher(on);
549 String param,value;
550 while(matcher.find()){
551 matched = matcher.group();
552 param = matched.substring(1,matched.length()-1);
553 if(params.containsKey(param)){
554 value = String.valueOf(params.get(param));
555 result = result.replace(matched, value);
556 }
557 }
558 return result;
559 }
560
561 private Object getBlockValue(String value, Class<?> voClass) {
562
563 Pattern pattern = Pattern.compile("\\[\\w+\\]");
564 Matcher matcher = pattern.matcher(value);
565 String matched, fieldName, columnName;
566 Field field;
567 while (matcher.find()) {
568 matched = matcher.group();
569 try {
570 fieldName = matched.substring(1).substring(0, matched.length() - 2);
571 try {
572 field = voClass.getDeclaredField(fieldName);
573 gboat2.base.core.annotation.Field referField = field.getAnnotation(gboat2.base.core.annotation.Field.class);
574 columnName = getColumnName(referField.clazz(), referField.column());
575 } catch (NoSuchFieldException e) {
576 field = voClass.getSuperclass().getDeclaredField(fieldName);
577 if (field == null) {
578 throw new RuntimeException("failed to parse " + fieldName + " in block");
579 }
580 columnName = getColumnName(voClass.getSuperclass(), fieldName);
581 }
582 value = value.replace(matched, columnName);
583 } catch (SecurityException e) {
584 throw new RuntimeException("failed to parse " + matched + " in block");
585 } catch (NoSuchFieldException e) {
586 throw new RuntimeException("failed to parse " + matched + " in block");
587 }
588 }
589 return value;
590 }
591
592
593
594
595
596
597
598 private String convertFieldToDbColumn(String orderbyField, Class<?> voClass) {
599 String[] splited = orderbyField.split("\\.");
600 if (splited.length != 2) {
601 throw new RuntimeException("invalid orderby item : " + orderbyField);
602 } else {
603 if (voClass.getName().endsWith(splited[0])) {
604 try {
605 Field field = voClass.getDeclaredField(splited[1]);
606 gboat2.base.core.annotation.Field referField = field.getAnnotation(gboat2.base.core.annotation.Field.class);
607 return getColumnName(referField.clazz(), referField.column());
608 } catch (Exception e) {
609 logger.debug(e.getMessage());
610 }
611 } else {
612 Relations anrs = voClass.getAnnotation(Relations.class);
613 if (anrs.base().getName().endsWith(splited[0])) {
614 return getColumnName(anrs.base(), splited[1]);
615 } else {
616
617 Relation[] rses = anrs.value();
618 for (Relation rs : rses) {
619 if (rs.refer().getName().endsWith(splited[0])) {
620 return getColumnName(rs.refer(), splited[1]);
621 }
622 }
623 }
624 }
625 throw new RuntimeException("invalid orderby item : " + orderbyField);
626 }
627 }
628
629 private String getTableName(Class<?> tableClass) {
630 Table table = tableClass.getAnnotation(Table.class);
631 StringBuffer tablename = new StringBuffer();
632 if (null != table.catalog() && !table.catalog().equals("")) {
633 tablename.append(table.catalog()).append(".");
634 }
635 if (null != table.schema() && !table.schema().equals("")) {
636 tablename.append(table.schema()).append(".");
637 }
638 tablename.append(table.name());
639 return tablename.toString();
640 }
641
642 private String getColumnName(gboat2.base.core.annotation.Field field, java.lang.reflect.Field fieldOfClass) {
643 String columnName;
644 if (field == null) {
645 columnName = getColumnName(fieldOfClass.getDeclaringClass(), fieldOfClass.getName());
646 if (null == columnName || columnName.equals("")) {
647 throw new RuntimeException(fieldOfClass.getName() + " is not setted with @Field,can't used to query.");
648 }
649 } else {
650 Class<?> poClass = field.clazz();
651 String fieldName = field.column();
652 if ("".equals(fieldName)) {
653 fieldName = fieldOfClass.getName();
654 }
655
656 columnName = getColumnName(poClass, fieldName);
657 }
658 return columnName;
659 }
660
661 private String getColumnName(Class<?> poClass, String fieldName) {
662 String dbColumn = null;
663 boolean isTransient = false;
664
665 try {
666 String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
667 Method getMethod = poClass.getMethod(getMethodName, new Class<?>[] {});
668 if (getMethod != null && getMethod.isAnnotationPresent(Column.class)) {
669 Column col = getMethod.getAnnotation(Column.class);
670 dbColumn = col.name();
671 } else if (getMethod != null && getMethod.isAnnotationPresent(Transient.class)) {
672 isTransient = true;
673 }
674 } catch (Exception e) {
675 logger.warn(e.getMessage());
676 }
677 if (!isTransient && dbColumn == null) {
678 try {
679
680 java.lang.reflect.Field classField = poClass.getDeclaredField(fieldName);
681 if (classField != null && classField.isAnnotationPresent(Column.class)) {
682 Column col = classField.getAnnotation(Column.class);
683 dbColumn = col.name();
684 } else if (classField != null && classField.isAnnotationPresent(Transient.class)) {
685 isTransient = true;
686 }
687 } catch (Exception e) {
688 logger.warn(e.getMessage());
689 }
690 }
691 if (!isTransient && dbColumn == null) {
692
693 java.lang.reflect.Field classField = null;
694 try {
695 try {
696 classField = poClass.getDeclaredField(fieldName);
697 } catch (Exception e) {
698 classField = poClass.getField(fieldName);
699 }
700 String setMethodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
701 Method setMethod = poClass.getMethod(setMethodName, new Class<?>[] { classField.getType() });
702 if (setMethod != null && setMethod.isAnnotationPresent(Column.class)) {
703 Column col = setMethod.getAnnotation(Column.class);
704 dbColumn = col.name();
705 } else if (setMethod != null && setMethod.isAnnotationPresent(Transient.class)) {
706 isTransient = true;
707 }
708 } catch (Exception e) {
709 e.printStackTrace();
710 }
711 }
712
713 if (dbColumn != null) {
714 return getTableAlias(poClass) + "." + dbColumn;
715 } else if (isTransient) {
716 throw new TransientException(poClass.getName() + "is setted @Transient");
717 } else {
718 throw new RuntimeException("can't parse database column name for " + poClass.getName() + "'s " + fieldName);
719 }
720 }
721
722 private String getTableAlias(Class<?> table) {
723 String alias = null;
724 String key = table.getName();
725 if (tableAliases.containsKey(key)) {
726 alias = tableAliases.get(key);
727 } else {
728 alias = ClassHelper.getNameWithoutPackage(table) + "0_";
729 tableAliases.put(key, alias);
730 }
731
732 return alias;
733 }
734
735 private String generateHqlFromPO(Map<String, Object> params, Class<?> poClass, Dialect dialect) {
736 String orderby = (String) params.get(QuerySupport.PARAM_ORDERBY);
737 StringBuffer hql = new StringBuffer();
738 hql.append(" from " + poClass.getName());
739
740 List<String> fields = ClassHelper.getFieldNames(poClass);
741 Set<String> keys = params.keySet();
742 String opera, value, operaSymble, passedKeys;
743 Object valueObject;
744 StringBuffer whereClause = new StringBuffer();
745 for (String key : keys) {
746
747 if (key.startsWith("_")) {
748 passedKeys = key.substring(1);
749 } else {
750 continue;
751 }
752 valueObject = params.get(key);
753 value = getParamValue(valueObject);
754
755 if (key.endsWith("_null") || key.endsWith("_notnull") || null != value && !"".equals(value)) {
756 opera = "=";
757
758 if (key.equals("_block")) {
759 if (whereClause.length() > 0) {
760 whereClause.append(" and ");
761 }
762 whereClause.append("(").append(getParamValue(params.get(key))).append(")");
763 continue;
764 }
765
766 if (passedKeys.indexOf("_") != -1) {
767 operaSymble = passedKeys.substring(passedKeys.lastIndexOf("_") + 1);
768 opera = operas.get(operaSymble);
769 if (opera == null) {
770 throw new RuntimeException("invalid opera in '" + key + "'");
771 }
772 passedKeys = passedKeys.substring(0, passedKeys.lastIndexOf("_"));
773 }
774 if (("like".equals(opera) && value.equals(""))
775 || (value.indexOf("%") != -1 && (value.equals("%%") || value.equals("%") || value.equals("%null%")))) {
776 continue;
777 }
778
779
780
781
782
783 String fieldClause = dealFieldForPo(fields, poClass, dialect, opera, value, key, passedKeys);
784 if (!StringUtils.isEmpty(fieldClause)) {
785
786 if (whereClause.length() > 0) {
787 whereClause.append(" and ");
788 }
789 whereClause.append(fieldClause);
790 }
791
792 }
793 }
794 if (whereClause.length() > 0) {
795 hql.append(" where ").append(whereClause);
796 }
797
798
799 if (null != orderby && !"".equals(orderby)) {
800 hql.append(" order by ").append(orderby);
801 }
802 return hql.toString();
803 }
804
805 private String dealFieldForPo(List<String> fields, Class<?> poClass, Dialect dialect, String opera, String value, String key, String passedKeys) {
806 StringBuilder whereClause = new StringBuilder();
807 Field fieldObj;
808 Class<?> fieldType;
809 String field, passedKey;
810 String[] splitedKeys = passedKeys.split("#");
811 if (splitedKeys.length > 1) {
812 whereClause.append("(");
813 }
814 for (int i = 0; i < splitedKeys.length; i++) {
815 if (i > 0) {
816 whereClause.append(" or ");
817 }
818 passedKey = splitedKeys[i];
819 if (fields.contains(passedKey)) {
820 field = passedKey;
821 } else {
822 field = ClassHelper.getFieldNameIgnoreCase(poClass, passedKey);
823 if (field != null) {
824 logger.info("the filed[" + passedKey + "] should be '" + field + "',it's corrected by system");
825 } else {
826 logger.warn("invalid field[" + passedKey + "]. it's ignored. please check it.");
827 return "";
828 }
829 }
830 if ("like".equals(opera) || value.startsWith("%") || value.endsWith("%")) {
831 if (!opera.equals("like")) {
832 opera = "like";
833 }
834 if (value.indexOf("%") == -1) {
835 value = "%" + value + "%";
836 }
837 whereClause.append(field).append(" ").append(opera).append(" '").append(value).append("'");
838 } else if ("or".equals(opera)) {
839 String[] values = value.split("\\|\\|");
840 whereClause.append("(");
841 int in = 0;
842 for (String singal : values) {
843 if (in++ > 0) {
844 whereClause.append(" ").append(opera).append(" ");
845 }
846 if ("".equals(singal)) {
847 whereClause.append(field).append(" is null");
848 } else if (("null").equalsIgnoreCase(singal) || ("not null").equalsIgnoreCase(singal)) {
849 whereClause.append(field).append(" is ").append(singal);
850 } else {
851 whereClause.append(field).append(" = ").append(singal);
852 }
853 }
854 whereClause.append(")");
855 } else if ("in".equals(opera) || "not in".equals(opera)) {
856 whereClause.append(field).append(" ").append(opera).append(" (").append(value).append(")");
857 } else if ("is null".equals(opera) || "is not null".equals(opera)) {
858 whereClause.append(field).append(" ").append(opera);
859 } else {
860 fieldObj = ClassHelper.getField(poClass, field);
861 fieldType = fieldObj.getType();
862 if (fieldType == java.util.Date.class || fieldType == java.sql.Date.class) {
863 Map<?, ?> functions = dialect.getFunctions();
864 if (functions.containsKey("to_char")) {
865 whereClause.append("to_char(").append(field).append(",'" + getDateFormatForOracle(value) + "') ");
866 whereClause.append(opera).append(" '").append(value).append("'");
867 } else if (functions.containsKey("date_format")) {
868 whereClause.append(field).append(opera).append("date_format('").append(value).append(
869 "','" + getDateFormatForMysql(value) + "')");
870 }
871 } else {
872 whereClause.append(field).append(" ").append(opera).append(" :").append(key);
873 }
874 }
875 }
876 if (splitedKeys.length > 1) {
877 whereClause.append(")");
878 }
879 return whereClause.toString();
880 }
881
882
883
884
885
886
887 public static String getQueryParamsAsUrlString(Map<String, Object> queryParams) {
888 StringBuffer urlStr = new StringBuffer();
889 if (queryParams.containsKey(QuerySupport.PARAM_SOURCE_MAP)) {
890 Map<String, Object> temp = (Map<String, Object>) queryParams.get(QuerySupport.PARAM_SOURCE_MAP);
891 String key;
892 for (Entry<String, Object> entry : temp.entrySet()) {
893 key = entry.getKey();
894 if (key.startsWith(QuerySupport.PARAM_PAGE) || key.equals(QuerySupport.PARAM_PAGESIZE)) {
895 continue;
896 }else{
897 trancateValueForUrl(entry, urlStr);
898 }
899 }
900 } else {
901 String key;
902 for (Entry<String, Object> entry : queryParams.entrySet()) {
903 key = entry.getKey();
904 if (!key.startsWith("_") && !key.equals(QuerySupport.PARAM_ORDERBY)) {
905 continue;
906 }else{
907 trancateValueForUrl(entry, urlStr);
908 }
909 }
910 }
911
912 return urlStr.toString();
913 }
914
915 private static void trancateValueForUrl(Entry<String, Object> entry, StringBuffer urlStr) {
916 String key = entry.getKey();
917 Object value = entry.getValue();
918
919 if (urlStr.length() > 0) {
920 urlStr.append("&");
921 }
922
923 try {
924 if (value instanceof String) {
925 urlStr.append(key).append("=").append(URLEncoder.encode((String) value, "utf-8"));
926 } else if (value instanceof String[]) {
927 String[] values = (String[]) value;
928 for (int i = 0; i < values.length; i++) {
929 if (urlStr.length() > 0) {
930 urlStr.append("&");
931 }
932 urlStr.append(key).append("=").append(URLEncoder.encode(values[i], "utf-8"));
933 }
934 } else if (null != value) {
935 urlStr.append(key).append("=");
936 urlStr.append(URLEncoder.encode(value.toString(), "utf-8"));
937 }
938 } catch (UnsupportedEncodingException e) {
939 urlStr.append(value.toString());
940 } catch (Exception e) {
941 e.printStackTrace();
942 }
943 }
944
945
946
947
948 private String checkBadWord(String str) {
949 if (StringUtils.isEmpty(str))
950 return str;
951 for (int i = 0; i < patternFilter.length; i++) {
952 str = str.replaceAll("(?i)" + patternFilter[i].toString(), "");
953 }
954 return str;
955 }
956 }