001: /*
002: * argun 1.0
003: * Web 2.0 delivery framework
004: * Copyright (C) 2007 Hammurapi Group
005: *
006: * This program is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public
008: * License as published by the Free Software Foundation; either
009: * version 2 of the License, or (at your option) any later version.
010: *
011: * This program is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014: * Lesser General Public License for more details.
015: *
016: * You should have received a copy of the GNU Lesser General Public
017: * License along with this library; if not, write to the Free Software
018: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
019: *
020: * URL: http://www.hammurapi.biz
021: * e-Mail: support@hammurapi.biz
022: */
023: package biz.hammurapi.web.util;
024:
025: import java.lang.reflect.InvocationHandler;
026: import java.lang.reflect.Method;
027: import java.lang.reflect.Proxy;
028: import java.sql.Connection;
029: import java.sql.PreparedStatement;
030: import java.sql.ResultSet;
031: import java.sql.ResultSetMetaData;
032: import java.sql.SQLException;
033: import java.util.ArrayList;
034: import java.util.Collection;
035: import java.util.HashMap;
036: import java.util.Iterator;
037: import java.util.List;
038: import java.util.Map;
039:
040: import javax.sql.DataSource;
041:
042: import biz.hammurapi.codegen.GenerationException;
043: import biz.hammurapi.codegen.InjectingClassLoader;
044: import biz.hammurapi.codegen.Interface;
045: import biz.hammurapi.codegen.InterfacePool;
046: import biz.hammurapi.codegen.InterfacePool.InterfaceDescriptor;
047: import biz.hammurapi.config.Context;
048: import biz.hammurapi.config.PropertyParser;
049: import biz.hammurapi.sql.MetadataAwareRowProcessor;
050: import biz.hammurapi.sql.Parameterizer;
051: import biz.hammurapi.sql.SQLExceptionEx;
052: import biz.hammurapi.sql.SQLProcessor;
053: import biz.hammurapi.sql.columns.ObjectColumn;
054: import biz.hammurapi.sql.metadata.DefaultGenerationPolicy;
055: import biz.hammurapi.sql.metadata.GenerationPolicy;
056: import biz.hammurapi.wrap.WrapperHandler;
057:
058: public class DynaSQLProcessor extends SQLProcessor implements Context {
059: private InterfacePool interfacePool = new InterfacePool();
060: private InjectingClassLoader injectingClassLoader = new InjectingClassLoader(
061: getClass().getClassLoader());
062: private int counter;
063:
064: public DynaSQLProcessor(DataSource dataSource, Context nameMap) {
065: super (dataSource, nameMap);
066: }
067:
068: public DynaSQLProcessor(Connection connection, Context nameMap) {
069: super (connection, nameMap);
070: }
071:
072: /**
073: * Executes SQL query, projects results to dynamically generated interface
074: * implementation. Results are put to ArrayList. If result set has one column then
075: * string value of this column is put to resulting collection.
076: * @param sql
077: * @return ArrayList of dynamic proxies.
078: * @throws SQLException
079: */
080: public Collection select(String sql) throws SQLException {
081: return select(sql, (List) null);
082: }
083:
084: private class DynaParameterizer implements Parameterizer {
085:
086: private Object[] args;
087:
088: public DynaParameterizer(Object[] args) {
089: this .args = args;
090: }
091:
092: public void parameterize(PreparedStatement ps)
093: throws SQLException {
094: for (int i = 0; args != null && i < args.length; ++i) {
095: ObjectColumn.parameterize(ps, args[i], i + 1);
096: }
097: }
098: }
099:
100: private class GeneratingRowProcessor implements
101: MetadataAwareRowProcessor {
102:
103: private Collection target;
104: private Object singleObject;
105: private Class resultInterface;
106:
107: private GenerationPolicy gp = new DefaultGenerationPolicy();
108:
109: private Map columnMap = new HashMap();
110:
111: public GeneratingRowProcessor(Collection target) {
112: this .target = target;
113: }
114:
115: public void processMetadata(ResultSetMetaData rs)
116: throws SQLException {
117: if (rs == null) {
118: throw new SQLException(
119: "Could not retrieve result set metadata");
120: }
121:
122: for (int i = 1, j = rs.getColumnCount(); i <= j; ++i) {
123: String columnName = rs.getColumnName(i);
124: columnMap.put(gp.generateColumnName(columnName),
125: columnName);
126: }
127:
128: if (columnMap.size() > 1) {
129: try {
130: resultInterface = generateResultSetInterface(
131: DynaSQLProcessor.class.getName()
132: + "$DynaInterface_" + ++counter,
133: columnMap, interfacePool,
134: injectingClassLoader);
135: } catch (GenerationException e) {
136: throw new SQLExceptionEx(
137: "Could not generate dynamic interface: "
138: + e, e);
139: } catch (ClassNotFoundException e) {
140: throw new SQLExceptionEx(
141: "Could not load dynamic interface: " + e, e);
142: }
143: }
144: }
145:
146: public void onEmptyResultSet() throws SQLException {
147: // Nothing
148:
149: }
150:
151: private Object project(ResultSet rs) throws SQLException {
152: if (columnMap.size() == 1) {
153: return rs.getString(1);
154: }
155: return DynaSQLProcessor.project(rs, resultInterface,
156: columnMap);
157: }
158:
159: public boolean process(ResultSet rs) throws SQLException {
160: if (target == null) {
161: singleObject = project(rs);
162: return false;
163: }
164:
165: target.add(project(rs));
166: return true;
167: }
168:
169: Object getSingleObject() {
170: return singleObject;
171: }
172:
173: }
174:
175: /**
176: * Executes SQL query, projects results to dynamically generated interface
177: * implementation. Results are put to ArrayList. If result set has one column then
178: * string value of this column is put to resulting collection.
179: * @param sql
180: * @param params Array of query parameters.
181: * @return ArrayList of dynamic proxies.
182: * @throws SQLException
183: */
184: public Collection select(String sql, List params)
185: throws SQLException {
186: ArrayList ret = new ArrayList();
187: processSelect(sql, new DynaParameterizer(params == null ? null
188: : params.toArray()), new GeneratingRowProcessor(ret));
189: return ret;
190: }
191:
192: private class SqlAndParams {
193: List params;
194: String sql;
195:
196: public SqlAndParams(String originalSql, final Context cParams) {
197: params = new ArrayList();
198: PropertyParser pp = new PropertyParser(new Context() {
199:
200: public Object get(String name) {
201: params.add(cParams.get(name));
202: return "?";
203: }
204:
205: }, false);
206: sql = pp.parse(originalSql);
207: }
208: }
209:
210: /**
211: * Executes SQL query, projects results to dynamically generated interface
212: * implementation. Results are put to ArrayList. If result set has one column then
213: * string value of this column is put to resulting collection.
214: * @param sql
215: * @param params Context to retrieve parameters from. In SQL parameters shall be present in a form ${<i>param name</i>}. This call
216: * preprocesses SQL and replaces ${...} with a question mark. At the same time it retrieves parameter value from the
217: * context
218: * @return ArrayList of dynamic proxies.
219: * @throws SQLException
220: */
221: public Collection select(String sql, final Context params)
222: throws SQLException {
223: SqlAndParams sap = new SqlAndParams(sql, params);
224: return select(sap.sql, sap.params);
225: }
226:
227: /**
228: * Executes SQL query, projects first row to dynamically generated interface
229: * implementation. If result set has one column then
230: * string value of this column is returned.
231: * @param sql
232: * @return Dynamic proxy representing the first row in result set or null.
233: * @throws SQLException
234: */
235: public Object selectSingleObject(String sql) throws SQLException {
236: return selectSingleObject(sql, (List) null);
237: }
238:
239: private static Class[] databaseObjectInterfaces = WrapperHandler
240: .getClassInterfaces(DynaDatabaseObject.class);
241:
242: /**
243: * Executes SQL query, projects first row to dynamically generated interface
244: * implementation. If result set has one column then
245: * string value of this column is returned.
246: * @param sql
247: * @return Dynamic proxy representing the first row in result set or null.
248: * @throws SQLException
249: */
250: public Object selectSingleObject(String sql, List params)
251: throws SQLException {
252: GeneratingRowProcessor generatingRowProcessor = new GeneratingRowProcessor(
253: null);
254: processSelect(sql, new DynaParameterizer(params == null ? null
255: : params.toArray()), generatingRowProcessor);
256: return generatingRowProcessor.getSingleObject();
257: }
258:
259: /**
260: * Executes SQL query, projects first row to dynamically generated interface
261: * implementation. If result set has one column then
262: * string value of this column is returned.
263: * @param sql
264: * @return Dynamic proxy representing the first row in result set or null.
265: * @throws SQLException
266: */
267: public Object selectSingleObject(String sql, final Context params)
268: throws SQLException {
269: SqlAndParams sap = new SqlAndParams(sql, params);
270: return selectSingleObject(sap.sql, sap.params);
271: }
272:
273: /**
274: * This method generates dynamic interface from result set metadata and projects
275: * result set to this collection
276: * @param rsmd
277: * @return
278: */
279: public List project(ResultSet rs) throws SQLException {
280: ResultSetMetaData rsmd = rs.getMetaData();
281:
282: if (rsmd == null) {
283: throw new SQLException(
284: "Could not retrieve result set metadata");
285: }
286:
287: Map columnMap = new HashMap();
288: GenerationPolicy gp = new DefaultGenerationPolicy();
289:
290: for (int i = 1, j = rsmd.getColumnCount(); i <= j; ++i) {
291: String columnName = rsmd.getColumnName(i);
292: columnMap
293: .put(gp.generateColumnName(columnName), columnName);
294: }
295:
296: Class resultInterface = null;
297:
298: if (columnMap.size() > 1) {
299: try {
300: resultInterface = generateResultSetInterface(
301: DynaSQLProcessor.class.getName()
302: + "$DynaInterface_" + ++counter,
303: columnMap, interfacePool, injectingClassLoader);
304: } catch (GenerationException e) {
305: throw new SQLExceptionEx(
306: "Could not generate dynamic interface: " + e, e);
307: } catch (ClassNotFoundException e) {
308: throw new SQLExceptionEx(
309: "Could not load dynamic interface: " + e, e);
310: }
311: }
312:
313: List ret = new ArrayList();
314: while (rs.next()) {
315: if (resultInterface == null) {
316: ret.add(rs.getString(1));
317: } else {
318: ret.add(project(rs, resultInterface, columnMap));
319: }
320: }
321: rs.close();
322: return ret;
323: }
324:
325: public static Object project(ResultSet rs,
326: final Class resultInterface, Map columnMap)
327: throws SQLException {
328: final Map cMap = new HashMap(columnMap); // To avoid side effects
329: final DynaDatabaseObject dataObject = new DynaDatabaseObject(
330: cMap.values(), rs);
331:
332: InvocationHandler ih = new InvocationHandler() {
333:
334: public Object invoke(Object proxy, Method method,
335: Object[] args) throws Throwable {
336: Class[] parameterTypes = method.getParameterTypes();
337: String methodName = method.getName();
338: if (parameterTypes.length == 0
339: && methodName.startsWith("get")) {
340: String fieldName = methodName.substring(3);
341: if (cMap.containsKey(fieldName)) {
342: return dataObject.get((String) cMap
343: .get(fieldName));
344: }
345: } else if (parameterTypes.length == 1
346: && methodName.startsWith("set")) {
347: String fieldName = methodName.substring(3);
348: if (cMap.containsKey(fieldName)) {
349: dataObject.set((String) cMap.get(fieldName),
350: (String) args[0]);
351: }
352: }
353:
354: return method.invoke(dataObject, args);
355: }
356:
357: };
358:
359: Class[] interfaces = new Class[databaseObjectInterfaces.length + 1];
360: interfaces[0] = resultInterface;
361: System.arraycopy(databaseObjectInterfaces, 0, interfaces, 1,
362: databaseObjectInterfaces.length);
363: return Proxy.newProxyInstance(resultInterface.getClassLoader(),
364: interfaces, ih);
365: }
366:
367: public static Class generateResultSetInterface(
368: String interfaceName, Map columnMap,
369: InterfacePool interfacePool,
370: InjectingClassLoader injectingClassLoader)
371: throws GenerationException, ClassNotFoundException {
372: synchronized (interfacePool) {
373: InterfaceDescriptor id = interfacePool.addInterface(
374: interfaceName, null);
375: Iterator it = columnMap.keySet().iterator();
376: while (it.hasNext()) {
377: Object fieldName = it.next();
378: id.addMethod("public java.lang.String get" + fieldName
379: + "()");
380: id.addMethod("public void set" + fieldName
381: + "(java.lang.String " + fieldName + ")");
382: }
383:
384: if (id.isMaster()) {
385: Collection super Interfaces = id
386: .getSuperInterfaces(null);
387: StringBuffer iName = new StringBuffer(
388: "public interface ");
389: iName.append(id.getName());
390: if (!super Interfaces.isEmpty()) {
391: iName.append(" extends ");
392: Iterator eit = super Interfaces.iterator();
393: while (eit.hasNext()) {
394: iName.append(eit.next());
395: if (eit.hasNext()) {
396: iName.append(", ");
397: }
398: }
399: }
400: Interface rsInterface = new Interface(iName.toString(),
401: "Result set interface", null);
402: it = id.getOwnMethods().iterator();
403: while (it.hasNext()) {
404: rsInterface.addMethod((String) it.next(), null,
405: "Column accessor", null);
406: }
407:
408: injectingClassLoader.consume(rsInterface);
409: }
410: return injectingClassLoader.loadClass(id.getMaster()
411: .getName());
412: }
413: }
414:
415: /**
416: * Processes database metadata.
417: * @param processor
418: * @throws SQLException
419: */
420: public void processDatabaseMetaData(
421: DatabaseMetaDataProcessor processor) throws SQLException {
422: Connection con = getConnection();
423: try {
424: processor.process(con.getMetaData());
425: } finally {
426: releaseConnection(con);
427: }
428: }
429:
430: /**
431: * Executes SQL and returns single object.
432: * Typical usage for this method retrieving scalar values
433: * from the database.
434: */
435: public Object get(String sql) {
436: try {
437: return selectSingleObject(sql);
438: } catch (SQLException e) {
439: return e.toString();
440: }
441: }
442:
443: /**
444: * Inserts data into table. Values are taken from context.
445: * @param tableName
446: * @param ctx
447: */
448: public void insert(String tableName, Context ctx)
449: throws SQLException {
450: Collection columnNames = getTableColumns(tableName);
451: Iterator it = columnNames.iterator();
452: Map vmap = new HashMap();
453: while (it.hasNext()) {
454: String columnName = (String) it.next();
455: Object value = ctx.get(columnName);
456: if (value != null) {
457: vmap.put(columnName, value);
458: }
459: }
460:
461: StringBuffer buf = new StringBuffer("INSERT INTO ");
462: buf.append(tableName);
463: buf.append(" (");
464: Collection cn = new ArrayList(vmap.keySet());
465: it = cn.iterator();
466: while (it.hasNext()) {
467: buf.append(it.next());
468: if (it.hasNext()) {
469: buf.append(", ");
470: }
471: }
472:
473: buf.append(") VALUES (");
474:
475: for (int i = 0, l = cn.size(); i < l; ++i) {
476: buf.append("?");
477: if (i < l - 1) {
478: buf.append(", ");
479: }
480: }
481:
482: buf.append(")");
483:
484: Connection con = getConnection();
485: try {
486: PreparedStatement ps = con.prepareStatement(buf.toString());
487: try {
488: it = cn.iterator();
489: for (int i = 1; it.hasNext(); ++i) {
490: Object o = vmap.get(it.next());
491: ps.setObject(i, o);
492: }
493: ps.executeUpdate();
494: } finally {
495: ps.close();
496: }
497: } finally {
498: con.close();
499: }
500: }
501:
502: /**
503: * Updated table data. WHERE clause is built from criteriaCtx
504: * @param tableName
505: * @param ctx
506: * @param criteriaCtx
507: */
508: public int update(String tableName, Context ctx, Context criteriaCtx)
509: throws SQLException {
510: Collection columnNames = getTableColumns(tableName);
511: Iterator it = columnNames.iterator();
512: Map vmap = new HashMap();
513: while (it.hasNext()) {
514: String columnName = (String) it.next();
515: Object value = ctx.get(columnName);
516: if (value != null) {
517: vmap.put(columnName, value);
518: }
519: }
520:
521: StringBuffer buf = new StringBuffer("UPDATE ");
522: buf.append(tableName);
523: buf.append(" SET ");
524: Collection cn = new ArrayList(vmap.keySet());
525: it = cn.iterator();
526: while (it.hasNext()) {
527: buf.append(it.next());
528: buf.append("=?");
529: if (it.hasNext()) {
530: buf.append(", ");
531: }
532: }
533:
534: Map wmap = new HashMap();
535: Collection wcn = new ArrayList();
536: if (criteriaCtx != null) {
537: buf.append(" WHERE ");
538:
539: it = columnNames.iterator();
540: while (it.hasNext()) {
541: String columnName = (String) it.next();
542: Object value = criteriaCtx.get(columnName);
543: if (value != null) {
544: wmap.put(columnName, value);
545: }
546: }
547:
548: wcn.addAll(wmap.keySet());
549: it = wcn.iterator();
550: for (int i = 0, l = wcn.size(); i < l; ++i) {
551: buf.append(it.next());
552: buf.append("=?");
553: if (i < l - 1) {
554: buf.append(" AND ");
555: }
556: }
557: }
558:
559: Connection con = getConnection();
560: try {
561: PreparedStatement ps = con.prepareStatement(buf.toString());
562: try {
563: it = cn.iterator();
564: for (int i = 1; it.hasNext(); ++i) {
565: Object o = vmap.get(it.next());
566: ps.setObject(i, o);
567: }
568: it = wcn.iterator();
569: for (int i = cn.size() + 1; it.hasNext(); ++i) {
570: Object o = wmap.get(it.next());
571: ps.setObject(i, o);
572: }
573: return ps.executeUpdate();
574: } finally {
575: ps.close();
576: }
577: } finally {
578: con.close();
579: }
580: }
581:
582: /**
583: * Deletes data. WHERE clause is built from criteriaCtx
584: * @param tableName
585: * @param criteriaCtx
586: */
587: public int delete(String tableName, Context criteriaCtx)
588: throws SQLException {
589: Collection columnNames = getTableColumns(tableName);
590:
591: StringBuffer buf = new StringBuffer("DELETE FROM ");
592: buf.append(tableName);
593:
594: Map wmap = new HashMap();
595: Collection wcn = new ArrayList();
596: if (criteriaCtx != null) {
597: buf.append(" WHERE ");
598:
599: Iterator it = columnNames.iterator();
600: while (it.hasNext()) {
601: String columnName = (String) it.next();
602: Object value = criteriaCtx.get(columnName);
603: if (value != null) {
604: wmap.put(columnName, value);
605: }
606: }
607:
608: wcn.addAll(wmap.keySet());
609: it = wcn.iterator();
610: for (int i = 0, l = wcn.size(); i < l; ++i) {
611: buf.append(it.next());
612: buf.append("=?");
613: if (i < l - 1) {
614: buf.append(" AND ");
615: }
616: }
617: }
618:
619: Connection con = getConnection();
620: try {
621: PreparedStatement ps = con.prepareStatement(buf.toString());
622: try {
623: Iterator it = wcn.iterator();
624: for (int i = 1; it.hasNext(); ++i) {
625: Object o = wmap.get(it.next());
626: ps.setObject(i, o);
627: }
628: return ps.executeUpdate();
629: } finally {
630: ps.close();
631: }
632: } finally {
633: con.close();
634: }
635: }
636:
637: public int update(String sql, List parameters) throws SQLException {
638: Connection con = getConnection();
639: try {
640: PreparedStatement ps = con.prepareStatement(sql);
641: try {
642: if (parameters != null) {
643: Iterator it = parameters.iterator();
644: for (int i = 1; it.hasNext(); ++i) {
645: ps.setObject(i, it.next());
646: }
647: }
648: return ps.executeUpdate();
649: } finally {
650: ps.close();
651: }
652: } finally {
653: con.close();
654: }
655: }
656:
657: public int update(String sql, final Context params)
658: throws SQLException {
659: SqlAndParams sap = new SqlAndParams(sql, params);
660: return update(sap.sql, sap.params);
661: }
662:
663: private Map tableColumns = new HashMap();
664:
665: private Collection getTableColumns(String tableName)
666: throws SQLException {
667: synchronized (tableColumns) {
668: Collection ret = (Collection) tableColumns.get(tableName);
669: if (ret == null) {
670: Connection con = getConnection();
671: try {
672: PreparedStatement ps = con
673: .prepareStatement("SELECT * FROM "
674: + tableName);
675: ResultSetMetaData smd = ps.getMetaData();
676: ret = new ArrayList();
677: for (int i = 1, l = smd.getColumnCount(); i <= l; ++i) {
678: ret.add(smd.getColumnName(i));
679: }
680: tableColumns.put(tableName, ret);
681: } finally {
682: con.close();
683: }
684: }
685: return ret;
686: }
687: }
688: }
|