001/*
002 * Copyright (c) 2004-2010 The Regents of the University of California.
003 * All rights reserved.
004 *
005 * '$Author: welker $'
006 * '$Date: 2010-05-06 05:21:26 +0000 (Thu, 06 May 2010) $' 
007 * '$Revision: 24234 $'
008 * 
009 * Permission is hereby granted, without written agreement and without
010 * license or royalty fees, to use, copy, modify, and distribute this
011 * software and its documentation for any purpose, provided that the above
012 * copyright notice and the following two paragraphs appear in all copies
013 * of this software.
014 *
015 * IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
016 * FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
017 * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
018 * THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
019 * SUCH DAMAGE.
020 *
021 * THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
022 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
023 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
024 * PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
025 * CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
026 * ENHANCEMENTS, OR MODIFICATIONS.
027 *
028 */
029
030package org.ecoinformatics.seek.dataquery;
031
032import java.awt.event.ActionEvent;
033import java.sql.Connection;
034import java.sql.ResultSet;
035import java.sql.ResultSetMetaData;
036import java.sql.SQLException;
037import java.sql.Statement;
038
039import org.apache.commons.logging.Log;
040import org.apache.commons.logging.LogFactory;
041import org.kepler.util.sql.DatabaseFactory;
042
043/**
044 * This class will handle data query action base on Hsql sql engine
045 * 
046 * @author Jing Tao
047 * 
048 */
049public class HsqlDataQueryAction extends DataQueryAction {
050        private Connection conn = null;
051        private Statement stmt = null;
052        private String sql = null;
053        private ResultSet rs = null;
054        private boolean printResultset = false;
055
056        private static Log log;
057        private static boolean isDebugging;
058
059        static {
060                log = LogFactory.getLog("org.ecoinformatics.seek.dataquery");
061                isDebugging = log.isDebugEnabled();
062        }
063
064        /**
065         * Default constructor. In this conctructor, it will set up connection to db
066         */
067        public HsqlDataQueryAction() throws SQLException, ClassNotFoundException {
068                conn = DatabaseFactory.getDBConnection();
069        }
070
071        /**
072         * Get sql command
073         * 
074         * @return String
075         */
076        public String getSQL() {
077                return sql;
078        }
079
080        /**
081         * Set sql command
082         * 
083         * @param mysql
084         *            String
085         */
086        public void setSQL(String mysql) {
087                sql = mysql;
088        }
089
090        /**
091         * Method to get result set
092         * 
093         * @return ResultSet
094         */
095        public ResultSet getResultSet() {
096                return rs;
097        }
098
099        /**
100         * Close the underlying database resources and invalidate object.
101         * 
102         */
103        public void close() {
104                try {
105                        rs.close();
106                        stmt.close();
107                        conn.close();
108                } catch (SQLException e) {
109                        log.debug("Unable to close objects ", e);
110                }
111                rs = null;
112        }
113
114        /**
115         * Method to print out the result set. This is for debug.
116         * 
117         * @param printResultset
118         *            boolean
119         */
120        public void setPrintResultset(boolean printResultset) {
121                this.printResultset = printResultset;
122        }
123
124        /**
125         * This method will handle such staff: Load data into db(create text tables
126         * and set source file location to table) Run sql query against the db
127         * 
128         * @param event
129         *            ActionEvent
130         */
131        public void actionPerformed(ActionEvent event) {
132                if (sql == null) {
133                        log.warn("Sql query is null and user should specify it");
134                        return;
135                }
136                try {
137                        stmt = conn.createStatement();
138                } catch (SQLException sqle) {
139                        log.warn("Couldn't create a statement", sqle);
140                        return;
141                }
142                // Run the sql query and get resultset
143                try {
144                        rs = stmt.executeQuery(sql);
145                        // this part is only for debug
146                        if (printResultset) {
147                                handleResultSet(rs);
148                        }
149                } catch (SQLException e) {
150                        if (isDebugging) {
151                                log.debug("The error to run sql command " + sql + " is ", e);
152                        }
153                        return;
154                }
155
156        }// actionPerformed
157
158        /*
159         * This method will transfer resultset to some output We need to discuss it
160         */
161        private void handleResultSet(ResultSet result) throws SQLException {
162                if (result == null) {
163                        log.debug("result is null");
164                        return;
165                }
166                ResultSetMetaData meta = result.getMetaData();
167                int colmax = meta.getColumnCount();
168                Object obj = null;
169
170                // the result set is a cursor into the data. You can only
171                // point to one row at a time
172                // assume we are pointing to BEFORE the first row
173                // rs.next() points to next row and returns true
174                // or false if there is no next row, which breaks the loop
175                while (result.next()) {
176                        for (int i = 0; i < colmax; i++) {
177                                obj = result.getObject(i + 1); // Is SQL the first column is
178                                                                                                // indexed
179                                // with 1 not 0
180                                if (isDebugging) {
181                                        log.debug(obj.toString());
182                                }
183                        }// for
184                }// while
185
186        }// handleResultSet
187}