1
2
3
4
5 package pt.digitalis.dif.model.sql;
6
7 import java.sql.Connection;
8 import java.sql.ResultSet;
9 import java.sql.ResultSetMetaData;
10 import java.sql.SQLException;
11 import java.sql.Statement;
12 import java.text.ParseException;
13 import java.util.ArrayList;
14 import java.util.Date;
15 import java.util.List;
16 import java.util.Map;
17
18 import org.hibernate.Session;
19 import org.hibernate.pretty.Formatter;
20
21 import pt.digitalis.dif.model.dataset.AbstractDataSet;
22 import pt.digitalis.dif.model.dataset.AttributeDefinition;
23 import pt.digitalis.dif.model.dataset.DataSetException;
24 import pt.digitalis.dif.model.dataset.Filter;
25 import pt.digitalis.dif.model.dataset.FilterType;
26 import pt.digitalis.dif.model.dataset.IDataSet;
27 import pt.digitalis.dif.model.dataset.IListProcessor;
28 import pt.digitalis.dif.model.dataset.IQueryPart;
29 import pt.digitalis.dif.model.dataset.Query;
30 import pt.digitalis.dif.model.dataset.Sort;
31 import pt.digitalis.dif.model.dataset.SortMode;
32 import pt.digitalis.dif.utils.logging.DIFLogger;
33 import pt.digitalis.dif.utils.logging.LoggingConfiguration;
34 import pt.digitalis.utils.common.BeanInspector;
35 import pt.digitalis.utils.common.CollectionUtils;
36 import pt.digitalis.utils.common.DateUtils;
37 import pt.digitalis.utils.common.IBeanAttributes;
38 import pt.digitalis.utils.common.StringUtils;
39 import pt.digitalis.utils.common.collections.CaseInsensitiveHashMap;
40
41
42
43
44
45
46
47
48 public class SQLDataSet extends AbstractDataSet<GenericBeanAttributes> {
49
50
51 protected final static String DML_NOT_ALLOWED = "DML operations are not supported in SQL Datasets";
52
53
54 private boolean closeTransaction = false;
55
56
57 private String[] compositeFieldNames = null;
58
59
60 private final boolean compositeID = false;
61
62
63 protected Connection connection;
64
65
66 protected Session hibernateSession = null;
67
68
69 private String queryBaseSQL;
70
71
72 private SQLDialect sqlDialect;
73
74
75
76
77
78
79
80
81
82
83
84 public SQLDataSet(Connection connection, String queryBaseSQL, SQLDialect sqlDialect)
85 {
86 super(GenericBeanAttributes.class);
87 initialize(connection, queryBaseSQL, sqlDialect);
88 }
89
90
91
92
93
94
95
96
97
98
99
100 public SQLDataSet(Session session, String queryBaseSQL, SQLDialect sqlDialect)
101 {
102 super(GenericBeanAttributes.class);
103 initialize(session, queryBaseSQL, sqlDialect);
104 }
105
106
107
108
109 protected void checkTransactionCommit()
110 {
111 if (closeTransaction && hibernateSession != null)
112 hibernateSession.getTransaction().commit();
113
114 closeTransaction = false;
115 }
116
117
118
119
120
121
122 protected void checkTransactionRollback() throws DataSetException
123 {
124 closeTransaction = false;
125
126 if (hibernateSession != null)
127 hibernateSession.getTransaction().rollback();
128 else
129 try
130 {
131 getConnection().rollback();
132 }
133 catch (SQLException e)
134 {
135 throw new DataSetException(e);
136 }
137 }
138
139
140
141
142 protected void checkTransactionStart()
143 {
144
145 if (hibernateSession != null)
146 {
147 if (!hibernateSession.isOpen())
148 hibernateSession = hibernateSession.getSessionFactory().getCurrentSession();
149
150 if (hibernateSession.getTransaction() == null || !hibernateSession.getTransaction().isActive())
151 {
152 closeTransaction = true;
153 hibernateSession.beginTransaction();
154 }
155 }
156 }
157
158
159
160
161 public long countQueryRecords(Query<GenericBeanAttributes> query) throws DataSetException
162 {
163 return countSQLQueryRecords(getSQLNoOrder(query, false, true));
164 }
165
166
167
168
169
170
171
172
173
174
175 protected long countSQLQueryRecords(String queryStr) throws DataSetException
176 {
177 try
178 {
179 checkTransactionStart();
180
181 Statement stmt = getConnection().createStatement();
182
183 if (queryStr.toLowerCase().startsWith("select "))
184 queryStr = "(" + queryStr + ") recs";
185
186 ResultSet rs = stmt.executeQuery("select count(*) from " + queryStr);
187 Long count;
188
189 rs.next();
190 count = rs.getLong(1);
191
192 rs.getStatement().close();
193 rs.close();
194
195 checkTransactionCommit();
196
197 return count;
198 }
199 catch (SQLException e)
200 {
201 checkTransactionRollback();
202 throw new DataSetException(e);
203 }
204 }
205
206
207
208
209 @Override
210 public boolean deleteSpecific(String id) throws DataSetException
211 {
212 throwUnsuportedOperationException(DML_NOT_ALLOWED);
213
214
215
216
217 return false;
218 }
219
220
221
222
223 @Override
224 protected void detectAttributeDefinition()
225 {
226 try
227 {
228 query().filterResults(0, -1).asList();
229 }
230 catch (DataSetException e)
231 {
232 e.printStackTrace();
233 }
234 }
235
236
237
238
239
240
241 protected void detectAttributeDefinition(ResultSet resultSet) throws SQLException
242 {
243 CaseInsensitiveHashMap<AttributeDefinition> tempAttributesDef = new CaseInsensitiveHashMap<AttributeDefinition>();
244
245 ResultSetMetaData rsMetaData = resultSet.getMetaData();
246
247 for (int i = 1; i <= rsMetaData.getColumnCount(); i++)
248 {
249 if (!rsMetaData.getColumnName(i).equalsIgnoreCase("rownum_"))
250 {
251 String columnID = rsMetaData.getColumnName(i);
252 String columnName = columnID.equals(columnID.toUpperCase()) ? columnID.toLowerCase() : columnID;
253
254 AttributeDefinition def = new AttributeDefinition(columnID, StringUtils.camelCaseToString(columnName));
255 try
256 {
257 def.setType(Class.forName(rsMetaData.getColumnClassName(i)));
258 }
259 catch (ClassNotFoundException e)
260 {
261 def.setType(Object.class);
262 }
263
264 tempAttributesDef.put(rsMetaData.getColumnName(i), def);
265 }
266
267 }
268
269 attributesDefinition = tempAttributesDef;
270 }
271
272
273
274
275
276
277
278
279
280
281
282 protected void executeJDBCQuery(Query<GenericBeanAttributes> query, String queryString,
283 IListProcessor<?> listProcessor) throws DataSetException
284 {
285 if (LoggingConfiguration.getInstance().getDebugModel())
286 DIFLogger.getLogger().info(
287 "Executing DataSet Query: \n " + query.toString().replaceAll("\n", "\n "));
288
289 if (LoggingConfiguration.getInstance().getDebugModel())
290 DIFLogger.getLogger().info(
291 "Executing SQLDataSet Query:\n "
292 + new Formatter(queryString).format().replaceAll("\n", "\n "));
293
294 Statement stmt = null;
295 ResultSet rs = null;
296
297 try
298 {
299 checkTransactionStart();
300
301 stmt = getConnection().createStatement();
302
303 if (listProcessor.getFetchPageSize() != null)
304 stmt.setFetchSize(listProcessor.getFetchPageSize());
305
306 rs = stmt.executeQuery(queryString);
307
308
309 if (!isAttributeDefinitionInitialized())
310 detectAttributeDefinition(rs);
311
312
313 while (rs.next())
314 {
315 GenericBeanAttributes bean = resultSetToBean(
316 rs,
317 (query.isDistinct() || (query.isReturnOnlySelectedFields() && !query.getFields().isEmpty())) ? query
318 .getFields() : null);
319
320 try
321 {
322 if (listProcessor.needsToConvertBeansToObjectArray())
323 listProcessor.processRecord(bean.toObjectArray(query.getFields()));
324 else
325 listProcessor.processRecord(bean);
326 }
327 catch (Exception e)
328 {
329 throw new DataSetException(e);
330 }
331 }
332
333 stmt.close();
334 rs.close();
335
336 checkTransactionCommit();
337 }
338 catch (SQLException e)
339 {
340 try
341 {
342 if (stmt != null)
343 stmt.close();
344 if (rs != null)
345 rs.close();
346 }
347 catch (SQLException e1)
348 {
349 e1.printStackTrace();
350 }
351
352 checkTransactionRollback();
353
354 throw new DataSetException(e);
355 }
356 }
357
358
359
360
361
362 public void executeQuery(Query<GenericBeanAttributes> query, IListProcessor<?> listProcessor)
363 throws DataSetException
364 {
365 StringBuffer sqlQuery = new StringBuffer(getSQLNoOrder(query, true, true));
366
367
368 if (!query.getSorts().isEmpty())
369 {
370 String sortClause = null;
371
372 for (Sort sort: query.getSorts())
373 {
374 if (sortClause == null)
375 sortClause = "\nORDER BY ";
376 else
377 sortClause += ", ";
378
379 if (sort.getMode().equals(SortMode.ASCENDING))
380 sortClause += sort.getAttribute();
381 else
382 sortClause += sort.getAttribute() + " desc";
383 }
384
385 sqlQuery.append(sortClause);
386 }
387
388
389 sqlQuery = getSqlDialect().getRecordPagingFilter(sqlQuery, query.getFirstRecord(), query.getLastRecord(),
390 !query.getFilters().isEmpty());
391
392 executeJDBCQuery(query, sqlQuery.toString(), listProcessor);
393 }
394
395
396
397
398 public GenericBeanAttributes get(String id) throws DataSetException
399 {
400
401 Query<GenericBeanAttributes> query = query();
402 query.addFilter(new Filter("id", FilterType.EQUALS, id));
403
404 return query.singleValue();
405 }
406
407
408
409
410 public String[] getCompositeKeys()
411 {
412 if (compositeFieldNames == null)
413 compositeFieldNames = super.idAttribute.split(",");
414
415 return compositeFieldNames;
416 }
417
418
419
420
421
422
423 @SuppressWarnings("deprecation")
424 public Connection getConnection()
425 {
426 if (hibernateSession == null)
427
428 return connection;
429 else
430 return hibernateSession.connection();
431 }
432
433
434
435
436
437
438
439
440
441
442
443
444
445 private String getParameterValueAsString(Filter filter, String value,
446 Map<String, AttributeDefinition> attrDefinition) throws DataSetException
447 {
448 AttributeDefinition attrDef = attrDefinition.get(filter.getAttributeName());
449
450 if (String.class.isAssignableFrom(attrDef.getType()))
451
452 return "'" + value + "'";
453
454 else if (Date.class.isAssignableFrom(attrDef.getType()))
455 try
456 {
457 return this.getSqlDialect().getDateValueExpression(DateUtils.stringToSimpleDate(value));
458 }
459 catch (ParseException e)
460 {
461 throw new DataSetException(e);
462 }
463 else
464 return value;
465 }
466
467
468
469
470
471
472 protected String getQueryBaseSQL()
473 {
474 return queryBaseSQL;
475 }
476
477
478
479
480
481
482 public SQLDialect getSqlDialect()
483 {
484 return sqlDialect;
485 }
486
487
488
489
490
491
492
493
494
495
496
497
498
499 private String getSQLNoOrder(Query<GenericBeanAttributes> query, boolean filterLines, boolean processFields)
500 throws DataSetException
501 {
502 StringBuffer sqlQuery = new StringBuffer(processQueryPart(query));
503 String fieldList;
504
505
506 if (processFields && (query.isReturnOnlySelectedFields() || query.isDistinct()) && !query.getFields().isEmpty())
507 fieldList = CollectionUtils.listToCommaSeparatedString(query.getFields());
508 else
509 fieldList = "*";
510
511 if (query.isDistinct())
512 fieldList = "distinct " + fieldList;
513
514 sqlQuery.insert(0, "select " + fieldList + " from (\n");
515
516 if (query.getFilters().isEmpty())
517 sqlQuery.append(") main");
518
519 return sqlQuery.toString();
520 }
521
522
523
524
525
526
527
528
529 protected void initialize(Connection connection, String queryBaseSQL, SQLDialect sqlDialect)
530 {
531 this.connection = connection;
532 this.queryBaseSQL = queryBaseSQL;
533 this.sqlDialect = sqlDialect;
534 }
535
536
537
538
539
540
541
542
543 @SuppressWarnings("deprecation")
544 protected void initialize(Session session, String queryBaseSQL, SQLDialect sqlDialect)
545 {
546 this.hibernateSession = session;
547
548 checkTransactionStart();
549
550 initialize(session.connection(), queryBaseSQL, sqlDialect);
551 }
552
553
554
555
556 @Override
557 public GenericBeanAttributes insertSpecific(GenericBeanAttributes instance) throws DataSetException
558 {
559 throwUnsuportedOperationException(DML_NOT_ALLOWED);
560
561
562
563
564 return null;
565 }
566
567
568
569
570 protected boolean isAttributeDefinitionInitialized()
571 {
572 return attributesDefinition != null;
573 }
574
575
576
577
578
579
580 @Override
581 public boolean isCompositeID()
582 {
583 return compositeID;
584 }
585
586
587
588
589
590
591
592 protected String processQueryPart(IQueryPart<GenericBeanAttributes> queryPart) throws DataSetException
593 {
594
595 StringBuffer sqlQuery = new StringBuffer(((QuerySQLDataSet) queryPart).getQuerySQL());
596 boolean isFirst = true;
597
598
599 if (!queryPart.getFilters().isEmpty())
600 sqlQuery.append(") main");
601
602
603 for (Filter filter: queryPart.getFilters())
604 {
605 if (isFirst)
606 {
607 sqlQuery.append("\nWHERE ");
608 isFirst = false;
609 }
610 else
611 sqlQuery.append("\nAND ");
612
613 if (filter.getType().equals(FilterType.SQL))
614 sqlQuery.append(filter.getValue());
615
616 else if (filter.getType().equals(FilterType.EQUALS))
617 sqlQuery.append(filter.getAttributeName() + " = "
618 + getParameterValueAsString(filter, filter.getValue(), queryPart.getAttributesDefinition()));
619 else if (filter.getType().equals(FilterType.NOT_EQUALS))
620 sqlQuery.append(filter.getAttributeName() + " <> "
621 + getParameterValueAsString(filter, filter.getValue(), queryPart.getAttributesDefinition()));
622
623 else if (filter.getType().equals(FilterType.IS_NULL))
624 sqlQuery.append(filter.getAttributeName() + " is null");
625 else if (filter.getType().equals(FilterType.IS_NOT_NULL))
626 sqlQuery.append(filter.getAttributeName() + " is not null");
627
628 else if (filter.getType().equals(FilterType.GRATER_THAN))
629 sqlQuery.append(filter.getAttributeName() + " > "
630 + getParameterValueAsString(filter, filter.getValue(), queryPart.getAttributesDefinition()));
631 else if (filter.getType().equals(FilterType.GRATER_OR_EQUALS_THAN))
632 sqlQuery.append(filter.getAttributeName() + " >= "
633 + getParameterValueAsString(filter, filter.getValue(), queryPart.getAttributesDefinition()));
634
635 else if (filter.getType().equals(FilterType.LESSER_THAN))
636 sqlQuery.append(filter.getAttributeName() + " < "
637 + getParameterValueAsString(filter, filter.getValue(), queryPart.getAttributesDefinition()));
638 else if (filter.getType().equals(FilterType.LESSER_OR_EQUALS_THAN))
639 sqlQuery.append(filter.getAttributeName() + " <= "
640 + getParameterValueAsString(filter, filter.getValue(), queryPart.getAttributesDefinition()));
641
642 else if (filter.getType().equals(FilterType.BETWEEN))
643 sqlQuery.append(filter.getAttributeName() + " between "
644 + getParameterValueAsString(filter, filter.getValue(), queryPart.getAttributesDefinition())
645 + " and "
646 + getParameterValueAsString(filter, filter.getValue2(), queryPart.getAttributesDefinition()));
647 else if (filter.getType().equals(FilterType.NOT_BETWEEN))
648 sqlQuery.append(filter.getAttributeName() + " not between "
649 + getParameterValueAsString(filter, filter.getValue(), queryPart.getAttributesDefinition())
650 + " and "
651 + getParameterValueAsString(filter, filter.getValue2(), queryPart.getAttributesDefinition()));
652
653 else if (filter.getType().equals(FilterType.LIKE))
654 sqlQuery.append(sqlDialect.getLikeExpression(filter.getAttributeName(), filter.getValue()));
655 else if (filter.getType().equals(FilterType.NOT_LIKE))
656 sqlQuery.append(sqlDialect.getNotLikeExpression(filter.getAttributeName(), filter.getValue()));
657 }
658
659
660 return sqlQuery.toString();
661 }
662
663
664
665
666 @Override
667 public QuerySQLDataSet query()
668 {
669 return new QuerySQLDataSet(this);
670 }
671
672
673
674
675
676
677
678
679
680
681
682
683 protected GenericBeanAttributes resultSetToBean(ResultSet resultSet, List<String> attributes) throws SQLException
684 {
685
686 if (attributes == null)
687 attributes = new ArrayList<String>(getAttributeList());
688
689 GenericBeanAttributes bean = new GenericBeanAttributes();
690
691 for (String fieldName: attributes)
692 bean.setAttribute(fieldName, resultSet.getObject(fieldName));
693
694 return bean;
695
696 }
697
698
699
700
701
702
703
704
705
706
707 protected GenericBeanAttributes setCompositeIdValues(GenericBeanAttributes instance, String compositeId)
708 {
709 if (isCompositeID())
710 {
711 String[] compositeKeyValues = compositeId.split(":");
712
713 if (instance.getAttribute("id") == null)
714 instance = (GenericBeanAttributes) BeanInspector.instantiateAttributePath("id", instance);
715
716 IBeanAttributes beanID = (IBeanAttributes) instance.getAttribute("id");
717
718 for (int i = 0; i < getCompositeKeys().length; i++)
719 beanID.setAttributeFromString(getCompositeKeys()[i], compositeKeyValues[i]);
720
721 instance.setAttribute("id", beanID);
722 }
723 else
724 instance.setAttributeFromString(idAttribute, compositeId);
725
726 return instance;
727 }
728
729
730
731
732
733
734
735 protected void setConnection(Connection connection)
736 {
737 this.connection = connection;
738 }
739
740
741
742
743
744
745
746 protected void setQueryBaseSQL(String queryBaseSQL)
747 {
748 this.queryBaseSQL = queryBaseSQL;
749 }
750
751
752
753
754 public long size() throws DataSetException
755 {
756 return countSQLQueryRecords(getQueryBaseSQL());
757 }
758
759
760
761
762 @Override
763 public GenericBeanAttributes updateSpecific(GenericBeanAttributes instance) throws DataSetException
764 {
765 throwUnsuportedOperationException(DML_NOT_ALLOWED);
766
767
768
769
770 return null;
771 }
772 }