001: /*
002: * SqlTemplate.java
003: *
004: * Brazil project web application Framework,
005: * export version: 1.1
006: * Copyright (c) 2000 Sun Microsystems, Inc.
007: *
008: * Sun Public License Notice
009: *
010: * The contents of this file are subject to the Sun Public License Version
011: * 1.0 (the "License"). You may not use this file except in compliance with
012: * the License. A copy of the License is included as the file "license.terms",
013: * and also available at http://www.sun.com/
014: *
015: * The Original Code is from:
016: * Brazil project web application Framework release 1.1.
017: * The Initial Developer of the Original Code is: suhler.
018: * Portions created by suhler are Copyright (C) Sun Microsystems, Inc.
019: * All Rights Reserved.
020: *
021: * Contributor(s): cstevens, suhler.
022: *
023: * Version: 1.9
024: * Created by suhler on 00/05/08
025: * Last modified by suhler on 00/12/11 13:32:37
026: */
027:
028: package sunlabs.brazil.template;
029:
030: import java.sql.Connection;
031: import java.sql.DriverManager;
032: import java.sql.ResultSet;
033: import java.sql.ResultSetMetaData;
034: import java.sql.SQLException;
035: import java.sql.Statement;
036: import java.util.Enumeration;
037: import java.util.Properties;
038:
039: import sunlabs.brazil.util.StringMap;
040: import sunlabs.brazil.util.Format;
041: import sunlabs.brazil.server.Server;
042:
043: /**
044: * Sample Template class for running SQL queries via jdbc and
045: * placing the results into the request properties for further processing.
046: * <p>
047: * Foreach session, a connection is made to an sql database via jdbc.
048: * Session reconnection is attempted if the server connection breaks.
049: * An SQL query is issued, with the results populating the request properties.
050: * The following server properties are used:
051: * <dl class=props>
052: * <dt>driver <dd>The name of the jdbc driver class for the desired database.
053: * Currently, only one driver may be specified.
054: * (e.g. <code><i>prefix</i>.driver=org.gjt.mm.mysql.Driver</code>).
055: * <dt>url <dd>The jdbc url used to establish a connection with the
056: * database. (e.g.
057: * <code><i>prefix</i>.url=jdbc:mysql://host/db?user=xxx&password=yyy</code>).
058: * <dt>sqlPrefix<dd>The properties prefix for any additional parameters
059: * that are required for this connection. For example:
060: * <pre>
061: * <i>prefix</i>.sqlPrefix=params
062: * params.user=my_name
063: * params.password=xxx
064: * </pre>
065: * All of the parameters are supplied to the jdbc connection
066: * at connection time.
067: * </dl>
068: * The <code>driver</code> and <code>url</code> parameters are required.
069: * All of the code between <code><sql>...</sql></code>
070: * is taken to be an SQL query, and sent to the appropriate database
071: * for execution. The result of the query is placed into the request
072: * properties for use by other templates, such as the
073: * {@link sunlabs.brazil.template.BSLTemplate BSLTemplate} or
074: * {@link sunlabs.brazil.template.PropsTemplate PropsTemplate}.
075: * <p>
076: * For a discussion of how the results map to properties,
077: * {@link #tag_sql see below}.
078: *
079: * @author Stephen Uhler
080: * @version %V% SqlTemplate.java
081: */
082:
083: public class SqlTemplate extends Template {
084: Connection con = null; // our connection to the database;
085: String prefix; // our properties prefix
086: Properties sqlProps = null; // extra properties to hand to connection
087: boolean initialized = false;
088: String url; // the jdbc url to connect to
089:
090: public boolean
091: init(RewriteContext hr) {
092: hr.lex.getClosingTags().addElement("sql");
093: if (initialized) {
094: return (con != null);
095: }
096: initialized = true;
097: prefix = hr.prefix;
098: Properties props = hr.request.props;
099: String driver = props.getProperty(hr.prefix + "driver");
100: url = props.getProperty(hr.prefix + "url");
101:
102: if (driver == null || url == null) {
103: hr.request.log(Server.LOG_WARNING, hr.prefix,
104: " needs url and driver parameters");
105: return false;
106: }
107:
108: // System.out.println(prefix + "url=" + url + " driver=" + driver);
109:
110: /*
111: * Get the extra properties passed to each sql connection
112: */
113:
114: String pre = props.getProperty(hr.prefix + "sqlPrefix");
115: if (pre != null) {
116: Enumeration enum = props.propertyNames();
117: int len= pre.length();
118: sqlProps = new Properties();
119: while(enum.hasMoreElements()) {
120: String key = (String) enum.nextElement();
121: if (key.startsWith(pre)) {
122: sqlProps.put(key.substring(len), props.getProperty(key));
123: }
124: }
125: // System.out.println("Con props: " + sqlProps);
126: }
127:
128: /*
129: * Load the jdbc driver and create a connection.
130: */
131:
132: try {
133: Class.forName(driver);
134: } catch (ClassNotFoundException e) {
135: hr.request.log(Server.LOG_WARNING, hr.prefix, e.getMessage());
136: return false;
137: }
138: con = setupSql(url, sqlProps);
139: hr.request.log(Server.LOG_DIAGNOSTIC, hr.prefix,
140: "Got sql connection: " + con);
141: return (con != null);
142: }
143:
144: /**
145: * Replace the SQL query with the appropriate request properties.
146: * Look for the following parameters:
147: * <i>(NOTE - This interface is preliminary, and subject to change)</i>.
148: * <dl>
149: * <dt>debug <dd>Include diagnostics in html comments
150: * <dt>prefix <dd>prefix to prepend to all results.
151: * Defaults to template prefix
152: * <dt>max <dd>The max # of rows returned (default=100)
153: * <dt>eval <dd>If present, do ${...} to entire query. (see
154: * {@link sunlabs.brazil.util.Format#getProperty getProperty}).
155: * <dt>index <dd>If present, use column 1 as part of the name.
156: * Otherwise, an index name is invented.
157: * </dl>
158: * For all queries, the following properties (with the prefix prepended)
159: * are set:
160: * <dl>
161: * <dt>columns <dd>The number of columns returned
162: * <dt>row.count <dd>The number of rows returned
163: * </dl>
164: * Foreach entry in the resultant table, its property is:
165: * <code>${prefix}.${table_name}.${columname}.${key}</code>. If
166: * the <code>index</code> parameter is set, the key is the value of
167: * the first column returned. Otherwise the key is the row number,
168: * and the additional property <code>${prefix}.rows</code> contains a
169: * list of all the row numbers returned.
170: */
171:
172: public void tag_sql(RewriteContext hr) {
173: StringMap args = hr.lex.getAttributes();
174: boolean debug = (args.get("debug") != null);
175: boolean eval = (args.get("eval") != null);
176: boolean useIndex = (args.get("index") != null);
177:
178: String pre = args.get("prefix");
179: if (pre == null) {
180: pre = prefix;
181: }
182:
183: int max = 100;
184: try {
185: max = Integer.decode(args.get("max")).intValue();
186: } catch (Exception e) {
187: }
188:
189: hr.accumulate(false);
190: hr.nextToken();
191: String query = hr.getBody();
192: // System.out.println("Got sql: (" + args + ") " + query);
193: hr.accumulate(true);
194:
195: Properties props = hr.request.props;
196: if (eval) {
197: query = Format.subst(props, query);
198: }
199: message(hr, debug, args.toString());
200: message(hr, debug, query);
201:
202: /*
203: * The connection to the server might have timed out. If so
204: * the connection will fail. If that happens, try to re-open the
205: * connection.
206: */
207:
208: Statement stmt = null;
209: ResultSet result = null;
210: ResultSetMetaData meta = null;
211: boolean retry = false;
212:
213: try {
214: stmt = con.createStatement();
215: result = stmt.executeQuery(query);
216: meta = result.getMetaData();
217: } catch (SQLException e) {
218: message(hr, debug, "Connection failed, will retry: "
219: + e.getMessage());
220: retry = true;
221: }
222:
223: /*
224: * Now run the query, stuffing the results into the properties.
225: * This is pretty stupid right now. The first column is used as
226: * the "index" if useIndex is set. Otherwise a counter is used.
227: */
228:
229: try {
230: if (retry) {
231: con = setupSql(url, sqlProps);
232: stmt = con.createStatement();
233: result = stmt.executeQuery(query);
234: meta = result.getMetaData();
235: }
236: int count = meta.getColumnCount();
237:
238: props.put(pre + ".columns", "" + count);
239: int rows = 0;
240: StringBuffer list = null;
241: if (!useIndex) {
242: list = new StringBuffer();
243: }
244: while (result.next() && rows++ < max) {
245: String first; // name of property row
246: if (useIndex) {
247: first = result.getString(1);
248: } else {
249: first = "" + rows;
250: list.append(first).append(" ");
251: }
252: for (int i = (useIndex ? 2 : 1); i <= count; i++) {
253: String name = deriveName(pre, meta, first, i);
254: String value = result.getString(i);
255: if (value == null) {
256: value = "n/a";
257: }
258: props.put(name, value);
259: message(hr, debug, name + "->" + value);
260: // System.out.println(" setting: " + name + "->" + value);
261: }
262: }
263: if (list != null) {
264: props.put(pre + ".rows", list.toString());
265: }
266: props.put(pre + "row.count", "" + (rows - 1));
267: } catch (SQLException e) {
268: props.put(pre + "error", e.getMessage());
269: message(hr, debug, "Failed: " + e.getMessage());
270: hr.request.log(Server.LOG_DIAGNOSTIC, hr.prefix, e
271: .getMessage());
272: }
273: }
274:
275: /**
276: * convenience method for emitting diagnostics
277: */
278:
279: void message(RewriteContext hr, boolean on, String message) {
280: if (on) {
281: hr.append("<!-- sql: " + message + " -->\n");
282: }
283: }
284:
285: /**
286: * Convenience method for deriving props names
287: */
288:
289: String deriveName(String prefix, ResultSetMetaData meta,
290: String suffix, int i) {
291: String table = "n/a";
292: String column = "n/a";
293: try {
294: table = meta.getTableName(i);
295: } catch (SQLException e) {
296: }
297: try {
298: column = meta.getColumnName(i);
299: } catch (SQLException e) {
300: }
301: String result = prefix + "." + table + "." + column + "."
302: + suffix;
303: // System.out.println("Getting name: " + result);
304: return result;
305: }
306:
307: public void tag_slash_sql(RewriteContext hr) {
308: // System.out.println("Got /sql tag");
309: }
310:
311: /**
312: * Setup a connection to an SQL server.
313: * Assume driver is already registered.
314: */
315:
316: public Connection setupSql(String url, Properties props) {
317: Connection con = null;
318: try {
319: con = DriverManager.getConnection(url, props);
320: } catch (SQLException e) {
321: System.out.println(e.getMessage());
322: } catch (Exception e) {
323: System.out.println(e.getMessage());
324: }
325: return con;
326: }
327: }
|