|
1
16 package org.apache.cocoon.transformation;
17
18 import java.io.IOException  ;
19 import java.io.InputStream ;
20 import java.io.StringReader ;
21 import java.lang.reflect.Field ;
22 import java.sql.CallableStatement ;
23 import java.sql.Clob ;
24 import java.sql.Connection ;
25 import java.sql.DriverManager ;
26 import java.sql.PreparedStatement ;
27 import java.sql.ResultSet ;
28 import java.sql.ResultSetMetaData ;
29 import java.sql.SQLException ;
30 import java.util.HashMap ;
31 import java.util.Iterator ;
32 import java.util.Map ;
33 import java.util.TreeMap ;
34 import java.util.List ;
35 import java.util.ArrayList ;
36
37 import org.apache.avalon.excalibur.datasource.DataSourceComponent;
38 import org.apache.avalon.framework.activity.Disposable;
39 import org.apache.avalon.framework.configuration.Configurable;
40 import org.apache.avalon.framework.configuration.Configuration;
41 import org.apache.avalon.framework.configuration.ConfigurationException;
42 import org.apache.avalon.framework.logger.AbstractLogEnabled;
43 import org.apache.avalon.framework.parameters.Parameters;
44 import org.apache.avalon.framework.service.ServiceException;
45 import org.apache.avalon.framework.service.ServiceManager;
46 import org.apache.avalon.framework.service.ServiceSelector;
47 import org.apache.cocoon.ProcessingException;
48 import org.apache.cocoon.components.sax.XMLDeserializer;
49 import org.apache.cocoon.components.sax.XMLSerializer;
50 import org.apache.cocoon.environment.SourceResolver;
51 import org.apache.cocoon.xml.IncludeXMLConsumer;
52
53 import org.apache.cocoon.transformation.helpers.TextRecorder;
54
55 import org.apache.commons.lang.StringEscapeUtils;
56 import org.apache.commons.lang.StringUtils;
57 import org.apache.excalibur.xml.sax.SAXParser;
58 import org.xml.sax.Attributes ;
59 import org.xml.sax.InputSource ;
60 import org.xml.sax.SAXException ;
61 import org.xml.sax.helpers.AttributesImpl ;
62
63
153 public class SQLTransformer extends AbstractSAXTransformer
154 implements Disposable, Configurable {
155
156
157 public static final String NAMESPACE = "http://apache.org/cocoon/SQL/2.0";
158
159 public static final String MAGIC_EXECUTE_QUERY = "execute-query";
161 private static final String MAGIC_OWN_CONNECTION = "own-connection";
162 public static final String MAGIC_CONNECTION = "use-connection";
163 public static final String MAGIC_DBURL = "dburl";
164 public static final String MAGIC_USERNAME = "username";
165 public static final String MAGIC_PASSWORD = "password";
166 public static final String MAGIC_NR_OF_ROWS = "show-nr-of-rows";
167 public static final String MAGIC_QUERY = "query";
168 public static final String MAGIC_VALUE = "value";
169 public static final String MAGIC_COLUMN_CASE = "column-case";
170 public static final String MAGIC_DOC_ELEMENT = "doc-element";
171 public static final String MAGIC_ROW_ELEMENT = "row-element";
172 public static final String MAGIC_IN_PARAMETER = "in-parameter";
173 public static final String MAGIC_IN_PARAMETER_NR_ATTRIBUTE = "nr";
174 public static final String MAGIC_IN_PARAMETER_VALUE_ATTRIBUTE = "value";
175 public static final String MAGIC_OUT_PARAMETER = "out-parameter";
176 public static final String MAGIC_OUT_PARAMETER_NAME_ATTRIBUTE = "name";
177 public static final String MAGIC_OUT_PARAMETER_NR_ATTRIBUTE = "nr";
178 public static final String MAGIC_OUT_PARAMETER_TYPE_ATTRIBUTE = "type";
179 public static final String MAGIC_ESCAPE_STRING = "escape-string";
180 public static final String MAGIC_ERROR = "error";
181
182 public static final String MAGIC_NS_URI_ELEMENT = "namespace-uri";
183 public static final String MAGIC_NS_PREFIX_ELEMENT = "namespace-prefix";
184
185 public static final String MAGIC_ANCESTOR_VALUE = "ancestor-value";
186 public static final String MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE = "level";
187 public static final String MAGIC_ANCESTOR_VALUE_NAME_ATTRIBUTE = "name";
188 public static final String MAGIC_SUBSTITUTE_VALUE = "substitute-value";
189 public static final String MAGIC_SUBSTITUTE_VALUE_NAME_ATTRIBUTE = "name";
190 public static final String MAGIC_NAME_ATTRIBUTE = "name";
191 public static final String MAGIC_STORED_PROCEDURE_ATTRIBUTE = "isstoredprocedure";
192 public static final String MAGIC_UPDATE_ATTRIBUTE = "isupdate";
193 public static final String CLOB_ENCODING = "clob-encoding";
194
195 protected static final int STATE_OUTSIDE = 0;
197 protected static final int STATE_INSIDE_EXECUTE_QUERY_ELEMENT = 1;
198 protected static final int STATE_INSIDE_VALUE_ELEMENT = 2;
199 protected static final int STATE_INSIDE_QUERY_ELEMENT = 3;
200 protected static final int STATE_INSIDE_ANCESTOR_VALUE_ELEMENT = 4;
201 protected static final int STATE_INSIDE_SUBSTITUTE_VALUE_ELEMENT = 5;
202 protected static final int STATE_INSIDE_IN_PARAMETER_ELEMENT = 6;
203 protected static final int STATE_INSIDE_OUT_PARAMETER_ELEMENT = 7;
204 protected static final int STATE_INSIDE_ESCAPE_STRING = 8;
205
206
210
211 protected boolean oldDriver;
212
213
214 protected int connectAttempts;
215
216
217 protected int connectWaittime;
218
219
223
224 protected Query query;
225
226
227 protected int state;
228
229
230 protected ServiceSelector datasources;
231
232
233 protected String connName;
234
235
236 protected Connection conn;
237
238 protected XMLSerializer compiler;
240 protected XMLDeserializer interpreter;
241 protected SAXParser parser;
242
243
246 public SQLTransformer() {
247 super.defaultNamespaceURI = NAMESPACE;
248 }
249
250
254
257 public void service(ServiceManager manager) throws ServiceException {
258 super.service(manager);
259 try {
260 this.datasources = (ServiceSelector) manager.lookup(DataSourceComponent.ROLE + "Selector");
261 } catch (ServiceException e) {
262 getLogger().warn("DataSource component selector is not available.", e);
263 }
264 }
265
266
274 public void configure(Configuration conf) throws ConfigurationException {
275 super.configure(conf);
276
277 this.oldDriver = conf.getChild("old-driver").getValueAsBoolean(false);
278 if (getLogger().isDebugEnabled()) {
279 getLogger().debug("Value for old-driver is " + this.oldDriver);
280 }
281
282 this.connectAttempts = conf.getChild("connect-attempts").getValueAsInteger(5);
283 this.connectWaittime = conf.getChild("connect-waittime").getValueAsInteger(5000);
284 }
285
286
289 public void setup(SourceResolver resolver, Map objectModel,
290 String source, Parameters parameters)
291 throws ProcessingException, SAXException , IOException {
292 super.setup(resolver, objectModel, source, parameters);
293
294 this.state = SQLTransformer.STATE_OUTSIDE;
296 this.connName = name(super.parameters);
297 }
298
299
302 public void recycle() {
303 this.query = null;
304 try {
305 if (this.conn != null) {
307 this.conn.close();
308 this.conn = null;
309 }
310 } catch (SQLException e) {
311 getLogger().info("Could not close connection", e);
312 }
313 this.connName = null;
314
315 this.manager.release(this.parser);
316 this.parser = null;
317 this.manager.release(this.compiler);
318 this.compiler = null;
319 this.manager.release(this.interpreter);
320 this.interpreter = null;
321
322 super.recycle();
323 }
324
325
328 public void dispose() {
329 if (this.datasources != null) {
330 this.manager.release(this.datasources);
331 this.datasources = null;
332 }
333 super.dispose();
334 }
335
336
341 private String getAttributeValue(Attributes attr, String name) {
342 String value = attr.getValue("", name);
343 if (value == null) {
344 value = attr.getValue(this.namespaceURI, name);
345 }
346
347 return value;
348 }
349
350
354 protected static void throwIllegalStateException(String message) {
355 throw new IllegalStateException ("Illegal state: " + message);
356 }
357
358
359 protected void startExecuteQueryElement() {
360 switch (state) {
361 case SQLTransformer.STATE_OUTSIDE:
362 case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
363 this.query = new Query(this.query);
365 this.query.enableLogging(getLogger().getChildLogger("query"));
366 state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
367 break;
368
369 default:
370 throwIllegalStateException("Not expecting a start execute query element");
371 }
372 }
373
374
375 protected void startValueElement(String name)
376 throws SAXException {
377 switch (state) {
378 case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
379 this.stack.push(name);
380 startTextRecording();
381 state = SQLTransformer.STATE_INSIDE_VALUE_ELEMENT;
382 break;
383
384 default:
385 throwIllegalStateException("Not expecting a start value element: " + name);
386 }
387 }
388
389
390 protected void startQueryElement(Attributes attributes)
391 throws SAXException {
392 switch (state) {
393 case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
394 startTextRecording();
395 state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
396
397 String isUpdate = attributes.getValue("", SQLTransformer.MAGIC_UPDATE_ATTRIBUTE);
398 if (isUpdate != null && !isUpdate.equalsIgnoreCase("false")) {
399 query.setUpdate(true);
400 }
401
402 String isProcedure = attributes.getValue("", SQLTransformer.MAGIC_STORED_PROCEDURE_ATTRIBUTE);
403 if (isProcedure != null && !isProcedure.equalsIgnoreCase("false")) {
404 query.setStoredProcedure(true);
405 }
406
407 String name = attributes.getValue("", SQLTransformer.MAGIC_NAME_ATTRIBUTE);
408 if (name != null) {
409 query.setName(name);
410 }
411 break;
412
413 default:
414 throwIllegalStateException("Not expecting a start query element");
415 }
416 }
417
418
419 protected void endQueryElement()
420 throws ProcessingException, SAXException {
421 switch (state) {
422 case SQLTransformer.STATE_INSIDE_QUERY_ELEMENT:
423 final String value = endTextRecording();
424 if (value.length() > 0) {
425 query.addQueryPart(value);
426 }
427 state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
428 break;
429
430 default:
431 throwIllegalStateException("Not expecting a stop query element");
432 }
433 }
434
435
436 protected void endValueElement()
437 throws SAXException {
438 switch (state) {
439 case SQLTransformer.STATE_INSIDE_VALUE_ELEMENT:
440 final String name = (String ) this.stack.pop();
441 final String value = endTextRecording();
442 query.setParameter(name, value);
443 this.state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
444 break;
445
446 default:
447 throwIllegalStateException("Not expecting an end value element");
448 }
449 }
450
451
452 protected void endExecuteQueryElement() throws SAXException {
453 switch (state) {
454 case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
455 if (query.parent == null) {
456 query.executeQuery();
457 query = null;
458 state = SQLTransformer.STATE_OUTSIDE;
459 } else {
460 query.parent.addNestedQuery(query);
461 query = query.parent;
462 state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
463 }
464 break;
465
466 default:
467 throwIllegalStateException("Not expecting an end execute query element");
468 }
469 }
470
471
472 protected void startAncestorValueElement(Attributes attributes)
473 throws ProcessingException, SAXException {
474 switch (state) {
475 case SQLTransformer.STATE_INSIDE_QUERY_ELEMENT:
476 int level = 0;
477 try {
478 level = Integer.parseInt(getAttributeValue(attributes, SQLTransformer.MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE));
479 } catch (Exception e) {
480 getLogger().debug("Invalid or missing value for " + SQLTransformer.MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE + " attribute", e);
481 throwIllegalStateException("Ancestor value elements must have a " +
482 SQLTransformer.MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE + " attribute");
483 }
484
485 String name = getAttributeValue(attributes, SQLTransformer.MAGIC_ANCESTOR_VALUE_NAME_ATTRIBUTE);
486 if (name == null) {
487 throwIllegalStateException("Ancestor value elements must have a " +
488 SQLTransformer.MAGIC_ANCESTOR_VALUE_NAME_ATTRIBUTE + " attribute");
489 }
490
491 final String value = endTextRecording();
492 if (value.length() > 0) {
493 query.addQueryPart(value);
494 }
495 query.addQueryPart(new AncestorValue(level, name));
496 startTextRecording();
497
498 state = SQLTransformer.STATE_INSIDE_ANCESTOR_VALUE_ELEMENT;
499 break;
500 default:
501 throwIllegalStateException("Not expecting a start ancestor value element");
502 }
503 }
504
505
506 protected void endAncestorValueElement() {
507 state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
508 }
509
510
511 protected void startSubstituteValueElement(Attributes attributes)
512 throws ProcessingException, SAXException {
513 switch (state) {
514 case SQLTransformer.STATE_INSIDE_QUERY_ELEMENT:
515 String name = getAttributeValue(attributes, SQLTransformer.MAGIC_SUBSTITUTE_VALUE_NAME_ATTRIBUTE);
516 if (name == null) {
517 throwIllegalStateException("Substitute value elements must have a " +
518 SQLTransformer.MAGIC_SUBSTITUTE_VALUE_NAME_ATTRIBUTE + " attribute");
519 }
520 String substitute = parameters.getParameter(name, null);
521 substitute = StringEscapeUtils.escapeSql(substitute);
523
524 final String value = endTextRecording();
525 if (value.length() > 0) {
526 query.addQueryPart(value);
527 }
528 query.addQueryPart(substitute);
529 startTextRecording();
530
531 state = SQLTransformer.STATE_INSIDE_SUBSTITUTE_VALUE_ELEMENT;
532 break;
533
534 default:
535 throwIllegalStateException("Not expecting a start substitute value element");
536 }
537 }
538
539
540 protected void endSubstituteValueElement() {
541 state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
542 }
543
544
545 protected void startEscapeStringElement(Attributes attributes)
546 throws ProcessingException, SAXException {
547 switch (state) {
548 case SQLTransformer.STATE_INSIDE_QUERY_ELEMENT:
549 final String value = endTextRecording();
550 if (value.length() > 0) {
551 query.addQueryPart(value);
552 }
553 startTextRecording();
554
555 state = SQLTransformer.STATE_INSIDE_ESCAPE_STRING;
556 break;
557
558 default:
559 throwIllegalStateException("Not expecting a start escape-string element");
560 }
561 }
562
563
564 protected void endEscapeStringElement()
565 throws SAXException {
566 switch (state) {
567 case SQLTransformer.STATE_INSIDE_ESCAPE_STRING:
568 String value = endTextRecording();
569 if (value.length() > 0) {
570 value = StringEscapeUtils.escapeSql(value);
571 value = StringUtils.replace(value, "\\", "\\\\");
572 query.addQueryPart(value);
573 }
574 startTextRecording();
575 state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
576 break;
577
578 default:
579 throwIllegalStateException("Not expecting a end escape-string element");
580 }
581 }
582
583
584 protected void startInParameterElement(Attributes attributes) {
585 switch (state) {
586 case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
587 String nr = getAttributeValue(attributes, SQLTransformer.MAGIC_IN_PARAMETER_NR_ATTRIBUTE);
588 String value = getAttributeValue(attributes, SQLTransformer.MAGIC_IN_PARAMETER_VALUE_ATTRIBUTE);
589 if (getLogger().isDebugEnabled()) {
590 getLogger().debug("IN PARAMETER NR " + nr + "; VALUE " + value);
591 }
592
593 int position = Integer.parseInt(nr);
594 query.setInParameter(position, value);
595 state = SQLTransformer.STATE_INSIDE_IN_PARAMETER_ELEMENT;
596 break;
597
598 default:
599 throwIllegalStateException("Not expecting an in-parameter element");
600 }
601 }
602
603
604 protected void endInParameterElement() {
605 state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
606 }
607
608
609 protected void startOutParameterElement(Attributes attributes) {
610 switch (state) {
611 case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
612 String name = getAttributeValue(attributes, SQLTransformer.MAGIC_OUT_PARAMETER_NAME_ATTRIBUTE);
613 String nr = getAttributeValue(attributes, SQLTransformer.MAGIC_OUT_PARAMETER_NR_ATTRIBUTE);
614 String type = getAttributeValue(attributes, SQLTransformer.MAGIC_OUT_PARAMETER_TYPE_ATTRIBUTE);
615 if (getLogger().isDebugEnabled()) {
616 getLogger().debug("OUT PARAMETER NAME" + name + ";NR " + nr + "; TYPE " + type);
617 }
618
619 int position = Integer.parseInt(nr);
620 query.setOutParameter(position, type, name);
621 state = SQLTransformer.STATE_INSIDE_OUT_PARAMETER_ELEMENT;
622 break;
623
624 default:
625 throwIllegalStateException("Not expecting an out-parameter element");
626 }
627 }
628
629
630 protected void endOutParameterElement() {
631 state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
632 }
633
634
637 public void startTransformingElement(String uri, String name, String raw, Attributes attributes)
638 throws ProcessingException, SAXException {
639 if (name.equals(SQLTransformer.MAGIC_EXECUTE_QUERY)) {
640 startExecuteQueryElement();
641 } else if (name.equals(SQLTransformer.MAGIC_QUERY)) {
642 startQueryElement(attributes);
643 } else if (name.equals(SQLTransformer.MAGIC_ANCESTOR_VALUE)) {
644 startAncestorValueElement(attributes);
645 } else if (name.equals(SQLTransformer.MAGIC_SUBSTITUTE_VALUE)) {
646 startSubstituteValueElement(attributes);
647 } else if (name.equals(SQLTransformer.MAGIC_IN_PARAMETER)) {
648 startInParameterElement(attributes);
649 } else if (name.equals(SQLTransformer.MAGIC_OUT_PARAMETER)) {
650 startOutParameterElement(attributes);
651 } else if (name.equals(SQLTransformer.MAGIC_ESCAPE_STRING)) {
652 startEscapeStringElement(attributes);
653 } else {
654 startValueElement(name);
655 }
656 }
657
658
661 public void endTransformingElement(String uri, String name, String raw)
662 throws ProcessingException, IOException , SAXException {
663 if (name.equals(SQLTransformer.MAGIC_EXECUTE_QUERY)) {
664 endExecuteQueryElement();
665 } else if (name.equals(SQLTransformer.MAGIC_QUERY)) {
666 endQueryElement();
667 } else if (name.equals(SQLTransformer.MAGIC_ANCESTOR_VALUE)) {
668 endAncestorValueElement();
669 } else if (name.equals(SQLTransformer.MAGIC_SUBSTITUTE_VALUE)) {
670 endSubstituteValueElement();
671 } else if (name.equals(SQLTransformer.MAGIC_IN_PARAMETER)) {
672 endInParameterElement();
673 } else if (name.equals(SQLTransformer.MAGIC_OUT_PARAMETER)) {
674 endOutParameterElement();
675 } else if (name.equals(SQLTransformer.MAGIC_ESCAPE_STRING)) {
676 endEscapeStringElement();
677 } else {
678 endValueElement();
679 }
680 }
681
682
686
692 protected String nsQualify(String name, String prefix) {
693 if (StringUtils.isEmpty(name)) {
694 return name;
695 }
696
697 if (StringUtils.isNotEmpty(prefix)) {
698 return prefix + ":" + name;
699 }
700
701 return name;
702 }
703
704
707 protected void start(String uri, String prefix, String name, Attributes attr)
708 throws SAXException {
709 try {
710 super.startTransformingElement(uri, name, nsQualify(name, prefix), attr);
711 } catch (IOException e) {
712 throw new SAXException (e);
713 } catch (ProcessingException e) {
714 throw new SAXException (e);
715 }
716 }
717
718
721 protected void end(String uri, String prefix, String name) throws SAXException {
722 try {
723 super.endTransformingElement(uri, name, nsQualify(name, prefix));
724 } catch (IOException e) {
725 throw new SAXException (e);
726 } catch (ProcessingException e) {
727 throw new SAXException (e);
728 }
729 }
730
731
734 protected void data(String data) throws SAXException {
735 if (data != null) {
736 super.characters(data.toCharArray(), 0, data.length());
737 }
738 }
739
740
744 private String name(Parameters params) {
745 final boolean ownConnection = params.getParameterAsBoolean(SQLTransformer.MAGIC_OWN_CONNECTION, false);
746 if (ownConnection) {
747 return null;
748 }
749
750 final String datasourceName = params.getParameter(SQLTransformer.MAGIC_CONNECTION, null);
751 if (datasourceName != null) {
752 return "ds:" + datasourceName;
753 }
754
755 final String dburl = params.getParameter(SQLTransformer.MAGIC_DBURL, null);
756 if (dburl != null) {
757 final String username = params.getParameter(SQLTransformer.MAGIC_USERNAME, null);
758 final String password = params.getParameter(SQLTransformer.MAGIC_PASSWORD, null);
759
760 if (username == null || password == null) {
761 return "db:@" + dburl;
762 } else {
763 return "db:" + username + ":" + password + "@" + dburl;
764 }
765 }
766
767 return "";
769 }
770
771
775 private Connection open(Parameters params) throws SQLException {
776 Connection result = null;
777
778 final String datasourceName = params.getParameter(SQLTransformer.MAGIC_CONNECTION, null);
780 if (datasourceName != null) {
781 if (this.datasources == null) {
783 throw new SQLException ("Unable to get connection from datasource '" + datasourceName + "': " +
784 "No datasources configured in cocoon.xconf.");
785 }
786
787 DataSourceComponent datasource = null;
788 try {
789 datasource = (DataSourceComponent) this.datasources.select(datasourceName);
790 for (int i = 0; i < this.connectAttempts && result == null; i++) {
791 try {
792 result = datasource.getConnection();
793 } catch (SQLException e) {
794 if (i + 1 < this.connectAttempts) {
795 final long waittime = this.connectWaittime;
796 if (getLogger().isDebugEnabled()) {
798 getLogger().info("Unable to get connection; waiting " +
799 waittime + "ms to try again.", e);
800 } else {
801 getLogger().info("Unable to get connection; waiting " +
802 waittime + "ms to try again.");
803 }
804 try {
805 Thread.sleep(waittime);
806 } catch (InterruptedException ex) {
807
808 }
809 }
810 }
811 }
812 } catch (ServiceException e) {
813 throw new SQLException ("Unable to get connection from datasource '" + datasourceName + "': " +
814 "No such datasource.");
815 } finally {
816 if (datasource != null) {
817 this.datasources.release(datasource);
818 }
819 }
820
821 if (result == null) {
822 throw new SQLException ("Failed to obtain connection from datasource '" + datasourceName + "'. " +
823 "Made " + this.connectAttempts + " attempts with "
824 + this.connectWaittime + "ms interval");
825 }
826 } else {
827 final String dburl = params.getParameter(SQLTransformer.MAGIC_DBURL, null);
829 if (dburl != null) {
830 final String username = params.getParameter(SQLTransformer.MAGIC_USERNAME, null);
831 final String password = params.getParameter(SQLTransformer.MAGIC_PASSWORD, null);
832
833 if (username == null || password == null) {
834 result = DriverManager.getConnection(dburl);
835 } else {
836 result = DriverManager.getConnection(dburl, username, password);
837 }
838 } else {
839 }
841 }
842
843 return result;
844 }
845
846
849 private void stream(String value) throws ServiceException, SAXException , IOException {
850 try {
851 if (value.startsWith("<?xml ")) {
853 value = value.substring(value.indexOf("?>") + 2);
854 }
855
856 if (this.parser == null) {
858 this.parser = (SAXParser) manager.lookup(SAXParser.ROLE);
859 }
860 if (this.compiler == null) {
861 this.compiler = (XMLSerializer) manager.lookup(XMLSerializer.ROLE);
862 }
863 if (this.interpreter == null) {
864 this.interpreter = (XMLDeserializer) manager.lookup(XMLDeserializer.ROLE);
865 }
866
867 this.parser.parse(new InputSource (new StringReader ("<root>" + value + "</root>")),
868 this.compiler);
869
870 IncludeXMLConsumer filter = new IncludeXMLConsumer(this, this);
871 filter.setIgnoreRootElement(true);
872
873 this.interpreter.setConsumer(filter);
874 this.interpreter.deserialize(this.compiler.getSAXFragment());
875 } finally {
876 if (this.compiler != null) {
878 manager.release(this.compiler);
879 this.compiler = null;
880 }
881 }
882 }
883
884
887 private class Query extends AbstractLogEnabled {
888
889
890 protected Query parent;
891
892
893 protected final List nested = new ArrayList ();
894
895
896 protected final List parts = new ArrayList ();
897
898
902
903 protected String name;
904
905
906 protected boolean isUpdate;
907
908
909 protected boolean isStoredProcedure;
910
911
912 protected Parameters params;
913
914
915 protected String outUri;
916
917
918 protected String outPrefix;
919
920
921 protected String rowsetElement;
922
923
924 protected String rowElement;
925
926
927 protected String nrOfRowsAttr = "nrofrows";
928
929
930 protected String nameAttr = "name";
931
932
933 protected int columnCase;
934
935
936 protected HashMap inParameters;
937
938
939 protected HashMap outParameters;
940
941
942 protected HashMap outParametersNames;
943
944
945 protected boolean showNrOfRows;
946
947
948 protected String clobEncoding;
949
950
954
955 protected Connection conn;
956
957
958 protected String connName;
959
960
961 protected boolean ownConn;
962
963
964 protected PreparedStatement pst;
965
966
967 protected CallableStatement cst;
968
969
970 protected ResultSet rs;
971
972
973 protected ResultSetMetaData md;
974
975
976 protected int rv = -1;
977
978
979 protected Query(Query parent) {
980 this.parent = parent;
981 this.params = new Parameters();
982 this.params.merge(SQLTransformer.this.parameters);
983 }
984
985
986 protected void addNestedQuery(Query query) {
987 nested.add(query);
988 }
989
990 protected void addQueryPart(Object value) {
991 if (getLogger().isDebugEnabled()) {
992 getLogger().debug("Adding query part \"" + value + "\"");
993 }
994 parts.add(value);
995 }
996
997 protected String getName() {
998 return name;
999 }
1000
1001 protected void setName(String name) {
1002 this.name = name;
1003 }
1004
1005 protected void setParameter(String name, String value) {
1006 if (getLogger().isDebugEnabled()) {
1007 getLogger().debug("Adding parameter name {" + name + "} value {" + value + "}");
1008 }
1009 params.setParameter(name, value);
1010 }
1011
1012 protected void setUpdate(boolean flag) {
1013 isUpdate = flag;
1014 }
1015
1016 protected void setStoredProcedure(boolean flag) {
1017 isStoredProcedure = flag;
1018 }
1019
1020 protected void setInParameter(int pos, String val) {
1021 if (inParameters == null) {
1022 inParameters = new HashMap ();
1023 }
1024 inParameters.put(new Integer (pos), val);
1025 }
1026
1027 protected void setOutParameter(int pos, String type, String name) {
1028 if (outParameters == null) {
1029 outParameters = new HashMap ();
1030 outParametersNames = new HashMap ();
1031 }
1032 outParameters.put(new Integer (pos), type);
1033 outParametersNames.put(new Integer (pos), name);
1034 }
1035
1036 private void setColumnCase(String columnCase) {
1037 if (columnCase.equals("lowercase")) {
1038 this.columnCase = -1;
1039 } else if (columnCase.equals("uppercase")) {
1040 this.columnCase = +1;
1041 } else if (columnCase.equals("preserve")) {
1042 this.columnCase = 0;
1044 } else {
1045 getLogger().warn("[" + columnCase + "] is not a valid value for <column-case>. " +
1046 "Column name retrieved from database will be used.");
1047 }
1048 }
1049
1050 private void registerInParameters() throws SQLException {
1051 if (inParameters == null) {
1052 return;
1053 }
1054
1055 Iterator i = inParameters.keySet().iterator();
1056 while (i.hasNext()) {
1057 Integer counter = (Integer ) i.next();
1058 String value = (String ) inParameters.get(counter);
1059 try {
1060 pst.setObject(counter.intValue(), value);
1061 } catch (SQLException e) {
1062 getLogger().error("Caught a SQLException", e);
1063 throw e;
1064 }
1065 }
1066 }
1067
1068 private void registerOutParameters(CallableStatement cst) throws SQLException {
1069 if (outParameters == null) {
1070 return;
1071 }
1072
1073 Iterator i = outParameters.keySet().iterator();
1074 while (i.hasNext()) {
1075 Integer counter = (Integer ) i.next();
1076 String type = (String ) outParameters.get(counter);
1077 int index = type.lastIndexOf(".");
1078
1079 String className, fieldName;
1080 if (index > -1) {
1081 className = type.substring(0, index);
1082 fieldName = type.substring(index + 1, type.length());
1083 } else {
1084 getLogger().error("Invalid SQLType: " + type, null);
1085 throw new SQLException ("Invalid SQLType: " + type);
1086 }
1087 try {
1088 Class clss = Class.forName(className);
1089 Field fld = clss.getField(fieldName);
1090 cst.registerOutParameter(counter.intValue(), fld.getInt(fieldName));
1091 } catch (Exception e) {
1092 getLogger().error("Invalid SQLType: " + className + "." + fieldName, e);
1094 }
1095 }
1096 }
1097
1098
1101 private void open() throws SQLException {
1102 this.connName = SQLTransformer.this.name(this.params);
1103
1104 if (this.connName == null) {
1106 this.conn = SQLTransformer.this.open(this.params);
1107 this.ownConn = true;
1108 return;
1109 }
1110
1111 Query query = this.parent;
1113 while (query != null) {
1114 if (this.connName.equals(query.connName)) {
1115 this.conn = query.conn;
1116 this.ownConn = false;
1117 return;
1118 }
1119 query = query.parent;
1120 }
1121
1122 if (this.connName.equals(SQLTransformer.this.connName)) {
1124 if (SQLTransformer.this.conn == null) {
1126 SQLTransformer.this.conn = SQLTransformer.this.open(SQLTransformer.this.parameters);
1127 }
1128
1129 this.conn = SQLTransformer.this.conn;
1130 this.ownConn = false;
1131 return;
1132 }
1133
1134 this.conn = SQLTransformer.this.open(this.params);
1136 this.ownConn = true;
1137 }
1138
1139
1142 protected void executeQuery() throws SAXException {
1143 if (getLogger().isDebugEnabled()) {
1144 getLogger().debug("Executing query " + this);
1145 }
1146
1147 this.outUri = this.params.getParameter(SQLTransformer.MAGIC_NS_URI_ELEMENT, SQLTransformer.this.namespaceURI);
1148 this.outPrefix = this.params.getParameter(SQLTransformer.MAGIC_NS_PREFIX_ELEMENT, "sql");
1149
1150 this.showNrOfRows = parameters.getParameterAsBoolean(SQLTransformer.MAGIC_NR_OF_ROWS, false);
1151 this.clobEncoding = parameters.getParameter(SQLTransformer.CLOB_ENCODING, "");
1152
1153 final String prefix = SQLTransformer.this.findPrefixMapping(this.outUri);
1155 if (prefix == null) {
1156 SQLTransformer.this.startPrefixMapping(this.outPrefix, this.outUri);
1157 } else {
1158 this.outPrefix = prefix;
1159 }
1160
1161 boolean success = false;
1162 try {
1163 try {
1164 open();
1165 execute();
1166 success = true;
1167 } catch (SQLException e) {
1168 getLogger().info("Failed to execute query " + this, e);
1169 start(this.rowsetElement, EMPTY_ATTRIBUTES);
1170 start(MAGIC_ERROR, EMPTY_ATTRIBUTES);
1171 data(e.getMessage());
1172 end(MAGIC_ERROR);
1173 end(this.rowsetElement);
1174 }
1175
1176 if (success) {
1177 AttributesImpl attr = new AttributesImpl ();
1178 if (showNrOfRows) {
1179 attr.addAttribute("", this.nrOfRowsAttr, this.nrOfRowsAttr, "CDATA", String.valueOf(getNrOfRows()));
1180 }
1181 String name = getName();
1182 if (name != null) {
1183 attr.addAttribute("", this.nameAttr, this.nameAttr, "CDATA", name);
1184 }
1185 start(this.rowsetElement, attr);
1186
1187 if (isStoredProcedure) {
1189 serializeStoredProcedure();
1190 }
1191
1192 while (next()) {
1194 start(this.rowElement, EMPTY_ATTRIBUTES);
1195 serializeRow();
1196 for (Iterator i = this.nested.iterator(); i.hasNext();) {
1197 ((Query) i.next()).executeQuery();
1198 }
1199 end(this.rowElement);
1200 }
1201
1202 end(this.rowsetElement);
1203 }
1204 } catch (SQLException e) {
1205 getLogger().debug("Exception in executeQuery()", e);
1206 throw new SAXException (e);
1207 } finally {
1208 close();
1209 }
1210
1211 if (prefix == null) {
1212 SQLTransformer.this.endPrefixMapping(this.outPrefix);
1213 }
1214 }
1215
1216
1219 private void execute() throws SQLException {
1220 this.rowsetElement = params.getParameter(SQLTransformer.MAGIC_DOC_ELEMENT, "rowset");
1221 this.rowElement = params.getParameter(SQLTransformer.MAGIC_ROW_ELEMENT, "row");
1222 setColumnCase(params.getParameter(SQLTransformer.MAGIC_COLUMN_CASE, "lowercase"));
1223
1224 StringBuffer sb = new StringBuffer ();
1226 for (Iterator i = parts.iterator(); i.hasNext();) {
1227 Object object = i.next();
1228 if (object instanceof String ) {
1229 sb.append((String ) object);
1230 } else if (object instanceof AncestorValue) {
1231 AncestorValue av = (AncestorValue) object;
1233 Query query = this;
1234 for (int k = av.level; k > 0; k--) {
1235 query = query.parent;
1236 }
1237 sb.append(query.getColumnValue(av.name));
1238 }
1239 }
1240
1241 String query = StringUtils.replace(sb.toString().trim(), "\r", " ", -1);
1242 if (!isStoredProcedure && !isUpdate) {
1244 if (query.length() > 6 && !query.substring(0, 6).equalsIgnoreCase("SELECT")) {
1245 isUpdate = true;
1246 }
1247 }
1248
1249 if (getLogger().isDebugEnabled()) {
1250 getLogger().debug("Executing " + query);
1251 }
1252 if (!isStoredProcedure) {
1253 if (oldDriver) {
1254 pst = conn.prepareStatement(query);
1255 } else {
1256 pst = conn.prepareStatement(query,
1257 ResultSet.TYPE_SCROLL_INSENSITIVE,
1258 ResultSet.CONCUR_READ_ONLY);
1259 }
1260 } else {
1261 if (oldDriver) {
1262 cst = conn.prepareCall(query);
1263 } else {
1264 cst = conn.prepareCall(query,
1265 ResultSet.TYPE_SCROLL_INSENSITIVE,
1266 ResultSet.CONCUR_READ_ONLY);
1267 }
1268 registerOutParameters(cst);
1269 pst = cst;
1270 }
1271
1272 registerInParameters();
1273 boolean result = pst.execute();
1274 if (result) {
1275 rs = pst.getResultSet();
1276 md = rs.getMetaData();
1277 } else {
1278 rv = pst.getUpdateCount();
1279 }
1280 }
1281
1282 protected int getNrOfRows() throws SQLException {
1283 int nr = 0;
1284
1285 if (rs != null) {
1286 if (oldDriver) {
1287 nr = -1;
1288 } else {
1289 try {
1290 rs.last();
1291 nr = rs.getRow();
1292 rs.beforeFirst();
1293 } catch (NullPointerException e) {
1294 getLogger().error("NPE while getting the nr of rows", e);
1297 }
1298 }
1299 } else {
1300 if (outParameters != null) {
1301 nr = outParameters.size();
1302 }
1303 }
1304 return nr;
1305 }
1306
1307 protected String getColumnValue(int i) throws SQLException {
1308 int numberOfChar = 1024;
1309 String retval;
1310
1311 if (rs.getMetaData().getColumnType(i) == java.sql.Types.DOUBLE) {
1312 retval = getStringValue(rs.getBigDecimal(i));
1313 } else if (rs.getMetaData().getColumnType(i) == java.sql.Types.CLOB) {
1314 Clob clob = rs.getClob(i);
1315 InputStream inputStream = clob.getAsciiStream();
1316 byte[] readByte = new byte[numberOfChar];
1317 StringBuffer buffer = new StringBuffer ();
1318 try {
1319 while (inputStream.read(readByte) > -1) {
1320 String string = new String (readByte, this.clobEncoding);
1321 buffer.append(string);
1322 }
1323 } catch (IOException e) {
1324 throw new SQLException ("Error reading stream from CLOB");
1325 }
1326 retval = buffer.toString();
1327 } else {
1328 retval = getStringValue(rs.getObject(i));
1329 }
1330 return retval;
1331 }
1332
1333 protected String getColumnValue(String name) throws SQLException {
1337 String retval = getStringValue(rs.getObject(name));
1338 return retval;
1341 }
1342
1343 protected boolean next() throws SQLException {
1344 if (rv != -1) {
1347 return true;
1349 }
1350
1351 if (rs == null || !rs.next()) {
1352 return false;
1354 }
1355
1356 return true;
1357 }
1358
1359
1362 protected void close() {
1363 if (rs != null) {
1364 try {
1365 rs.close();
1366 } catch (SQLException e) {
1367 getLogger().info("Unable to close the result set.", e);
1368 }
1369 rs = null;
1371 }
1372
1373 if (pst != null && pst != cst) {
1374 try {
1375 pst.close();
1376 } catch (SQLException e) {
1377 getLogger().info("Unable to close the statement.", e);
1378 }
1379 }
1380 pst = null;
1382
1383 if (cst != null) {
1384 try {
1385 cst.close();
1386 } catch (SQLException e) {
1387 getLogger().info("Unable to close the statement.", e);
1388 }
1389 cst = null;
1391 }
1392
1393 try {
1394 if (ownConn && conn != null) {
1395 conn.close();
1396 }
1397 } catch (SQLException e) {
1398 getLogger().info("Unable to close the connection", e);
1399 }
1400 conn = null;
1402 }
1403
1404 protected void serializeData(String value)
1405 throws SQLException , SAXException {
1406 if (value != null) {
1407 value = value.trim();
1408 if (value.length() > 0 && value.charAt(0) == '<') {
1410 try {
1411 stream(value);
1412 } catch (Exception ignored) {
1413 data(value);
1416 }
1417 } else {
1418 data(value);
1419 }
1420 }
1421 }
1422
1423 protected void serializeRow()
1424 throws SQLException , SAXException {
1425 if (rv != -1) {
1426 start("returncode", EMPTY_ATTRIBUTES);
1427 serializeData(String.valueOf(rv));
1428 end("returncode");
1429 rv = -1;
1432 } else {
1433 for (int i = 1; i <= md.getColumnCount(); i++) {
1434 String columnName = getColumnName(md.getColumnName(i));
1435 start(columnName, EMPTY_ATTRIBUTES);
1436 serializeData(getColumnValue(i));
1437 end(columnName);
1438 }
1439 }
1440 }
1441
1442 protected void serializeStoredProcedure()
1443 throws SQLException , SAXException {
1444 if (outParametersNames == null || cst == null) {
1445 return;
1446 }
1447
1448 Iterator itOutKeys = new TreeMap (outParameters).keySet().iterator();
1450 while (itOutKeys.hasNext()) {
1451 final Integer counter = (Integer ) itOutKeys.next();
1452 try {
1453 final Object obj = cst.getObject(counter.intValue());
1454 final String name = (String ) outParametersNames.get(counter);
1455 start(name, EMPTY_ATTRIBUTES);
1456
1457 if (!(obj instanceof ResultSet )) {
1458 serializeData(getStringValue(obj));
1459 } else {
1460 final ResultSet rs = (ResultSet ) obj;
1461 try {
1462 ResultSetMetaData md = rs.getMetaData();
1463 while (rs.next()) {
1464 start(this.rowElement, EMPTY_ATTRIBUTES);
1465 for (int i = 1; i <= md.getColumnCount(); i++) {
1466 String columnName = getColumnName(md.getColumnName(i));
1467 start(columnName, EMPTY_ATTRIBUTES);
1468 if (md.getColumnType(i) == 8) { serializeData(getStringValue(rs.getBigDecimal(i)));
1470 } else {
1471 serializeData(getStringValue(rs.getObject(i)));
1472 }
1473 end(columnName);
1474 }
1475 end(this.rowElement);
1476 }
1477 } finally {
1478 try {
1479 rs.close();
1480 } catch (SQLException e) {
1481
1482 }
1483 }
1484 }
1485
1486 end(name);
1487 } catch (SQLException e) {
1488 getLogger().error("Caught a SQLException", e);
1489 throw e;
1490 }
1491 }
1492 }
1493
1494 private String getColumnName(String columnName) {
1495 switch (this.columnCase) {
1496 case -1:
1497 columnName = columnName.toLowerCase();
1498 break;
1499 case +1:
1500 columnName = columnName.toUpperCase();
1501 break;
1502 default:
1503 }
1505 return columnName;
1506 }
1507
1508
1511 private String getStringValue(Object object) {
1512 if (object instanceof byte[]) {
1513 return new String ((byte[]) object);
1515 } else if (object instanceof char[]) {
1516 return new String ((char[]) object);
1517 } else if (object != null) {
1518 return object.toString();
1519 }
1520
1521 return "";
1522 }
1523
1524 private void start(String name, Attributes attr)
1525 throws SAXException {
1526 SQLTransformer.this.start(this.outUri, this.outPrefix, name, attr);
1527 }
1528
1529 private void end(String name) throws SAXException {
1530 SQLTransformer.this.end(this.outUri, this.outPrefix, name);
1531 }
1532
1533 private void data(String data) throws SAXException {
1534 SQLTransformer.this.data(data);
1535 }
1536 }
1537
1538 private static class AncestorValue {
1539 protected int level;
1540 protected String name;
1541
1542 protected AncestorValue(int level, String name) {
1543 this.level = level;
1544 this.name = name;
1545 }
1546
1547 public String toString() {
1548 return "<ancestor level " + level + ", name " + name + ">";
1549 }
1550 }
1551
1552
1553
1561 public String endTextRecording()
1562 throws SAXException {
1563 sendEndPrefixMapping();
1564
1565 TextRecorder recorder = (TextRecorder) removeRecorder();
1566 String text = recorder.getAllText();
1567 if (getLogger().isDebugEnabled()) {
1568 getLogger().debug("End text recording. Text=" + text);
1569 }
1570 return text;
1571 }
1572
1573}
1574 |
Popular Tags |
Java API By Example, From Geeks To Geeks.