Java tutorial
/** * This class controls the execution of an SQL query. * * Copyright (C) 2007 Stephen Harding * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. * * Please send inquiries to; steve@inverse2.com * * @version $Revision: 1.4 $ * * $Log: Query.java,v $ * Revision 1.4 2008/07/01 17:26:47 stevewdh * Changed logging so that user can specify the type they want (Java, Log4J or HTML). * * Revision 1.3 2008/03/08 11:25:23 stevewdh * Use ResultSetMetaData.getColumnLabel() instead of getColumnName... * This works around an issue where the MySQL JDBC driver getColumnName returns the DML used in the select statement... which is the JDBC compliant behaviour. * * Revision 1.2 2008/03/03 10:26:00 stevewdh * *** empty log message *** * * Revision 1.1 2007/10/04 11:06:24 stevewdh * *** empty log message *** * * Revision 1.2 2007/09/30 13:09:05 stephen harding * Added contact details to license header. * * Revision 1.1 2007/09/15 16:09:06 stephen harding * Added header. * * Revision 1.5 2007/09/15 15:20:11 stephen harding * Improved building of xml structure - null values do not count when deciding if a throw new should be actioned. * */ package com.init.octo.query; import java.io.File; import java.io.FileInputStream; import java.sql.Connection; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.HashMap; import org.jdom2.Document; import org.jdom2.output.XMLOutputter; import com.init.octo.util.Logger; import com.init.octo.util.StringUtils; public class Query { private static Logger log = Logger.getLogger(Query.class.getName()); private Connection connection; // connection to a database private StringBuffer queryText; // the raw query text private String expandedQuery; // the query text with any parameters expanded // in it private Statement statement; // a database statement object private ResultSet rs; // a select result set private HashMap<String, Column> resultColumnData; // list of the selected // column data private int nextSQL; // pointer to after the first select statement in the // SQL private SimpleDateFormat dateFormat; // format for date columns /** * Constructor * * @param queryRef * - the reference number of the query (this will be used to look * up in xmlCollection) * @param xmlCollection * - a collection of cached XML structures * @param connectionPool * - a database connection pool */ public Query() { } public void setConnection(Connection connection) { this.connection = connection; } public void setResultSet(ResultSet rs) { this.rs = rs; // this.firstFetch = true; } public void setDateFormat(SimpleDateFormat dateFormat) { this.dateFormat = dateFormat; } public void setQueryText(StringBuffer queryText) { this.queryText = queryText; } public void setQueryFile(String queryFileName) throws Exception { // this.firstFetch = true; this.rs = null; /** * The query is actually not XML any more... so just read the query text * from the file queryRef + ".sql".... * */ log.debug("Finding the query file [" + queryFileName + "] in the runtime environment"); File queryFile = new File(queryFileName); // cache file in queryText stringbuffer queryText = new StringBuffer(); FileInputStream fis = new FileInputStream(queryFile); byte[] buf = new byte[1024]; int ret; while ((ret = fis.read(buf)) != -1) { queryText.append(new String(buf, 0, ret)); } fis.close(); log.debug("QUERY [" + queryText.toString() + "]"); } /** * This method initialises the object with a parameters object. This will * replace any parameters in the query text with values from the parameters * object. * * @param parameters * - an XML structure containing current definitions of * parameters * @throws SQLException */ public void expandParameters(Document parameters) throws SQLException { XMLOutputter xout = new XMLOutputter(); // Format.getPrettyFormat()); log.debug("parameters XML: " + xout.outputString(parameters)); expandedQuery = StringUtils.expand(queryText.toString(), parameters.getRootElement()); log.debug("EXPANDED QUERY [" + expandedQuery + "]"); /** Close everything down... * */ // firstFetch = true; if (rs != null) { rs.close(); rs = null; } if (statement != null) { statement.close(); statement = null; } } // end runtime() /** * This method runs the SQL, which may be mutliple statements. When an SQL * select is performed the script is ended and the method terminates, having * set-up the statement and rs variables. */ public ResultSet runSQLScript() throws Exception { ResultSet rs = null; int start = 0; int end = 0; String sql = null; boolean complete = false; while (!complete) { end = expandedQuery.indexOf("#go#", start); // find next stmt // separator if (end == -1) { // statement separator not found if (start < expandedQuery.length()) { sql = expandedQuery.substring(start, expandedQuery.length()); if (sql.matches("\\s+") != true) { // its not just // whitespace, so // run it... start = expandedQuery.length(); } else { // just whitespace, so we're done...! log.warn("No SQL select statement found in the script...?!"); throw new Exception("The SQL script did not contain a select statement."); } } } else { // statement separator found, so pull out the SQL sql = expandedQuery.substring(start, end); start = end + 4; // move start to after statement // seperator if (sql.matches("\\s+") == true) { /* Just whitspace so ignore... */ continue; } } sql = filterOutComments(sql); if (statement == null) { statement = connection.createStatement(); } if (statement.execute(sql) == true) { log.debug("Select statement executed - thats the end of the script..."); rs = statement.getResultSet(); // firstFetch = false; nextSQL = start; // Save where we are... then we can process the // rest later complete = true; } log.debug("SQL update count = " + statement.getUpdateCount()); } return rs; } public void finishSQLScript() throws Exception { int start; int end; String sql; try { start = nextSQL; end = 0; sql = null; while (true) { end = expandedQuery.indexOf("#go#", start); // find the next // statement // separator if (end == -1) { // statement separator not found if (start < expandedQuery.length()) { // there is more SQL // in the expanded // query string... // so check if it is // just // whitespace... sql = expandedQuery.substring(start, expandedQuery.length()); if (sql.matches("\\s+") != true) { // its not just // whitespace, so // run it... start = expandedQuery.length(); } else { // just whitespace, so we're done... return; } } else { /* end of sql script... */ return; } } else { // statement separator found, so pull out the SQL sql = expandedQuery.substring(start, end); start = end + 4; // move start to after statement // seperator if (sql.matches("\\s+") == true) { /* Just whitspace so ignore... */ continue; } } /** Filter out comments and closing semi-colon from SQL... * */ sql = filterOutComments(sql); /** Run the SQL statement... * */ log.debug("Running the SQL: " + sql); if (statement == null) { statement = connection.createStatement(); } if (statement.execute(sql) == true) { // select statement // executed log.warn("Select statement executed whilst finishing SQL - does not make sense!"); } log.debug("SQL update count = " + statement.getUpdateCount()); } } catch (Exception ex) { log.fatal("Exception finishing the SQL script", ex); throw ex; } } /** * This method filters out comments and closing semi-colons from the SQL */ public static String filterOutComments(String sql) { StringBuffer out = new StringBuffer(); char c; char c1; char cm1; boolean deref = false; boolean comment = false; sql = sql + " "; cm1 = ' '; for (int i = 0; i < sql.length() - 1; i++) { c = sql.charAt(i); c1 = sql.charAt(i + 1); if (i > 0) { cm1 = sql.charAt(i - 1); } if (c == '\'' && deref == false && comment == false) { deref = true; } else if (c == '\'' && deref == true) { deref = false; } if (deref == false && c == '/' && c1 == '*') { comment = true; } if (deref == false && c == '/' && cm1 == '*') { comment = false; c = ' '; } if (comment == true) { c = ' '; } if (c == ';' && deref == false) { c = ' '; } out.append(c); } return (out.toString()); } /** * Fetches a row from the result set. * * @returns true if a row was selected, or false if not row was selected */ public Row fetchRow(ResultSet rs, MetaData metadata) throws Exception { Row row = null; if (rs.next()) { row = new Row(); for (int i = 0; i < metadata.size(); i++) { Column c = new Column(); c.setValue(rs.getObject(i)); // c.setColumnName(metadata.get(i).getColumnName()); c.setMetaData(metadata.get(i)); row.add(c); } } return row; } /** * Reads the column metadata for a resultset and returns it as a MetaData * object. * * @param rs * @return populated MetaData object * @throws Exception */ public MetaData getColumnMetaData() throws Exception { MetaData metadata = new MetaData(); ResultSetMetaData rsMetaData = rs.getMetaData(); for (int i = 1; i <= rsMetaData.getColumnCount(); i++) { ColumnMetaData c = new ColumnMetaData(rsMetaData.getColumnLabel(i), rsMetaData.getColumnType(i), rsMetaData.isCurrency(i), (rsMetaData.isNullable(i) != 1), dateFormat); metadata.add(c); } return metadata; } /** * Returns the value of a column as a string * * @param columnName * - the name of the column * @returns the value of the column as a string */ public String getColumnValueAsString(String columnName) { Column col = (Column) resultColumnData.get(columnName.toLowerCase()); if (col != null) { return (col.getColumnValueAsString()); } else { return (""); } } public boolean columnValueIsNull(String columnName) { Column col = (Column) resultColumnData.get(columnName.toLowerCase()); if (col != null) { return (col.columnValueIsNull()); } else { return (true); } } } // end class Query