001: // @@
002: // @@
003: /*
004: * Wi.Ser Framework
005: *
006: * LGPL Version: 1.8.1, 20-September-2007
007: * Copyright (C) 2005-2006 Dirk von der Weiden <dvdw@imail.de>
008: *
009: * This library is free software; you can redistribute it and/or
010: * modify it under the terms of the GNU Lesser General Public
011: * License as published by the Free Software Foundation; either
012: * version 2 of the License, or (at your option) any later version.
013: *
014: * This library is distributed in the hope that it will be useful,
015: * but WITHOUT ANY WARRANTY; without even the implied warranty of
016: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
017: * Lesser General Public License for more details.
018: *
019: * You should have received a copy of the GNU Lesser General Public
020: * License along with this library located in LGPL.txt in the
021: * license directory; if not, write to the
022: * Free Software Foundation, Inc., 59 Temple Place - Suite 330,
023: * Boston, MA 02111-1307, USA.
024: *
025: * If this agreement does not cover your requirements, please contact us
026: * via email to get detailed information about the commercial license
027: * or our service offerings!
028: *
029: */
030: // @@
031: package de.ug2t.connector;
032:
033: import java.sql.*;
034: import java.util.*;
035:
036: import de.ug2t.channel.ho.*;
037: import de.ug2t.kernel.*;
038: import de.ug2t.model.values.*;
039:
040: class CoSqlField extends KeRegisteredObject {
041: private Object pem_value = null;
042:
043: public CoSqlField() {
044: super (false);
045: }
046:
047: public void pcmf_setValue(Object xValue) {
048: this .pem_value = xValue;
049:
050: return;
051: };
052:
053: public Object pcmf_getValue() {
054: return (this .pem_value);
055: };
056:
057: public String toString() {
058: if (pem_value == null)
059: return ("");
060: else
061: return (this .pem_value.toString());
062: };
063: };
064:
065: class CoSqlTableJoin {
066: private CoSqlTable pem_target = null;
067: private CoSqlTable pem_source = null;
068: private String pem_keyFldT = null;
069: private String pem_keyFldS = null;
070: private static String pem_whereTpl = "where $KEYFLD='$VALUE'";
071:
072: public CoSqlTableJoin(CoSqlTable xSource, CoSqlTable xTarget,
073: String xKeyFieldS, String xKeyFieldT) {
074: this .pem_target = xTarget;
075: this .pem_source = xSource;
076: this .pem_keyFldT = xKeyFieldT;
077: this .pem_keyFldS = xKeyFieldS;
078: };
079:
080: public void pcmf_doPosition() throws Exception {
081: Object l_sVal = pem_source.pcmf_getField(this .pem_keyFldS)
082: .pcmf_getValue();
083: String l_where = KeTools.pcmf_stringSubst(
084: CoSqlTableJoin.pem_whereTpl, "$KEYFLD",
085: this .pem_keyFldT);
086: l_where = KeTools.pcmf_stringSubst(l_where, "$VALUE", l_sVal
087: .toString());
088:
089: this .pem_target.pcmf_setWhere(l_where);
090: }
091:
092: public void pcmf_doDelete() throws Exception {
093: this .pem_target.pcmf_deleteRow();
094: }
095:
096: public void pcmf_doInsert() throws Exception {
097: this .pem_target.pcmf_insert();
098: }
099:
100: public void pcmf_doUpdate() throws Exception {
101: this .pem_target.pcmf_update();
102: }
103: }
104:
105: public class CoSqlTable extends HoGenericTreeNode {
106: private Connection pem_con = null;
107: private Statement pem_stmt = null;
108: private ResultSet pem_res = null;
109: private PreparedStatement pem_upstmt = null;
110: private PreparedStatement pem_ipstmt = null;
111: private LinkedHashMap pem_cols = new LinkedHashMap();
112: private String pem_sql = "SELECT $FIELDS FROM $TAB";
113: private String pem_ipsql = "INSERT INTO $TAB ($ROW) VALUES ($SET)";
114: private String pem_upsql = "UPDATE $TAB SET $SET WHERE $WHERE";
115: private String pem_keyFld = null;
116: private ResultSetMetaData pem_metadata = null;
117: private int pem_colanz = 0;
118: private boolean pem_ownCon = true;
119: private ArrayList pem_joins = null;
120: private int pem_cPos = 1;
121:
122: public CoSqlTable(String xDriver, String xJdbcUrl, String xTable,
123: String xFields, String xKeyFld, String xWhere)
124: throws Exception {
125: super (xTable);
126: Class.forName(xDriver);
127: this .pcmf_construct(DriverManager.getConnection(xJdbcUrl),
128: xTable, xFields, xKeyFld, xWhere);
129: };
130:
131: public CoSqlTable(Connection xCon, String xTable, String xFields,
132: String xKeyFld, String xWhere) throws Exception {
133: super (xTable);
134: this .pem_ownCon = false;
135: pcmf_construct(xCon, xTable, xFields, xKeyFld, xWhere);
136: }
137:
138: public void pcmf_construct(Connection xCon, String xTable,
139: String xFields, String xKeyFld, String xWhere)
140: throws Exception {
141: this .pem_con = xCon;
142: this .pem_keyFld = xKeyFld;
143:
144: this .pcmf_register(xTable);
145: if (xFields == null)
146: xFields = "*";
147:
148: this .pem_sql = KeTools.pcmf_stringSingleSubst(pem_sql,
149: "$FIELDS", xFields);
150: this .pem_sql = KeTools.pcmf_stringSingleSubst(pem_sql, "$TAB",
151: xTable);
152: this .pem_ipsql = KeTools.pcmf_stringSingleSubst(pem_ipsql,
153: "$TAB", xTable);
154: this .pem_upsql = KeTools.pcmf_stringSingleSubst(pem_upsql,
155: "$TAB", xTable);
156:
157: if (xWhere != null)
158: this .pem_sql = this .pem_sql + " " + xWhere;
159: else
160: this .pem_sql = this .pem_sql + "1=0";
161:
162: this .pem_stmt = this .pem_con.createStatement();
163: this .pem_res = this .pem_stmt.executeQuery(pem_sql);
164:
165: this .pem_metadata = this .pem_res.getMetaData();
166:
167: pem_colanz = this .pem_metadata.getColumnCount();
168: for (int i = 1; i <= pem_colanz; i++) {
169: String l_colname = null;
170: CoSqlField l_fld = null;
171:
172: l_colname = this .pem_metadata.getColumnName(i);
173: l_fld = new CoSqlField();
174:
175: l_fld.pcmf_register(l_colname + "@"
176: + this .pcmf_getObjName());
177: this .pem_cols.put(l_colname, l_fld);
178:
179: if (i < pem_colanz) {
180: this .pem_ipsql = KeTools.pcmf_stringSingleSubst(
181: pem_ipsql, "$SET", "?,$SET");
182: this .pem_ipsql = KeTools.pcmf_stringSingleSubst(
183: pem_ipsql, "$ROW", l_colname + ",$ROW");
184: this .pem_upsql = KeTools.pcmf_stringSingleSubst(
185: pem_upsql, "$SET", l_colname + "=?, $SET");
186: } else {
187: this .pem_ipsql = KeTools.pcmf_stringSingleSubst(
188: pem_ipsql, "$SET", "?");
189: this .pem_ipsql = KeTools.pcmf_stringSingleSubst(
190: pem_ipsql, "$ROW", l_colname);
191: this .pem_upsql = KeTools.pcmf_stringSingleSubst(
192: pem_upsql, "$SET", l_colname + "=?");
193: this .pem_upsql = KeTools.pcmf_stringSingleSubst(
194: pem_upsql, "$WHERE", this .pem_keyFld + "=?");
195: }
196: ;
197: }
198: ;
199:
200: this .pem_ipstmt = this .pem_con.prepareStatement(pem_ipsql);
201: this .pem_upstmt = this .pem_con.prepareStatement(pem_upsql);
202: this .pcmf_setCurser(1);
203: return;
204: };
205:
206: public void pcmf_setWhere(String xWhere) throws Exception {
207: pem_sql = "SELECT * FROM $TAB";
208:
209: this .pem_sql = KeTools.pcmf_stringSingleSubst(pem_sql, "$TAB",
210: this .pcmf_getObjName());
211: this .pem_ipsql = KeTools.pcmf_stringSingleSubst(pem_ipsql,
212: "$TAB", this .pcmf_getObjName());
213: this .pem_upsql = KeTools.pcmf_stringSingleSubst(pem_upsql,
214: "$TAB", this .pcmf_getObjName());
215:
216: if (xWhere != null)
217: this .pem_sql = this .pem_sql + " " + xWhere;
218: else
219: this .pem_sql = this .pem_sql + "1=0";
220:
221: this .pem_stmt = this .pem_con.createStatement();
222: this .pem_res = this .pem_stmt.executeQuery(pem_sql);
223:
224: this .pcmf_setCurser(1);
225:
226: return;
227: }
228:
229: public void pcmf_deleteRow() throws Exception {
230: this .pcmf_setRefresh();
231: this .pem_res.deleteRow();
232:
233: return;
234: };
235:
236: public void pcmf_deleteJoined() throws Exception {
237: this .pem_res.deleteRow();
238:
239: if (this .pem_joins != null) {
240: Iterator l_it2 = this .pem_joins.iterator();
241: while (l_it2.hasNext())
242: ((CoSqlTableJoin) l_it2.next()).pcmf_doDelete();
243: }
244:
245: return;
246: };
247:
248: public void pcmf_deleteRow(int xIdx) throws Exception {
249: this .pcmf_setRefresh();
250: this .pcmf_setCurser(xIdx);
251: this .pem_res.deleteRow();
252:
253: return;
254: };
255:
256: public int pcmf_getCurserPos() {
257: return (this .pem_cPos);
258: }
259:
260: public boolean pcmf_setCurser(int xIdx) throws Exception {
261: Iterator l_it = this .pem_cols.values().iterator();
262:
263: boolean l_ret = this .pem_res.absolute(xIdx);
264: if (l_ret == false)
265: return (l_ret);
266:
267: this .pem_cPos = xIdx;
268:
269: for (int i = 1; i <= this .pem_colanz; i++) {
270: Object l_col = null;
271: Object l_fld = null;
272:
273: l_col = this .pem_res.getObject(i);
274: l_fld = l_it.next();
275:
276: if (l_fld instanceof KeRegisteredObject)
277: ((KeRegisteredObject) l_fld).pcmf_setValue(l_col);
278: else if (l_fld instanceof IMoSingleValue) {
279: ((IMoSingleValue) l_fld).pcmf_setValue(l_col);
280: if (((IMoSingleValue) l_fld).pcmf_validate() == false)
281: KeLog
282: .pcmf_log(
283: "ug2t",
284: "transfer from db- to modelvalue failed due to validation",
285: this , KeLog.ERROR);
286: } else
287: KeLog.pcmf_log("ug2t",
288: "sql-field connector no valid class:"
289: + l_fld.getClass().toString(), this ,
290: KeLog.ERROR);
291: }
292: ;
293:
294: if (this .pem_joins != null) {
295: Iterator l_it2 = this .pem_joins.iterator();
296: while (l_it2.hasNext())
297: ((CoSqlTableJoin) l_it2.next()).pcmf_doPosition();
298: }
299:
300: return (l_ret);
301: };
302:
303: public int pcmf_update() throws Exception {
304: Iterator l_it = this .pem_cols.values().iterator();
305:
306: for (int i = 1; i <= this .pem_colanz; i++) {
307: Object l_fld = null;
308:
309: l_fld = l_it.next();
310: if (l_fld instanceof KeRegisteredObject) {
311: this .pem_upstmt.setObject(i,
312: ((KeRegisteredObject) l_fld).pcmf_getValue());
313: if (((KeRegisteredObject) l_fld).pcmf_getObjName()
314: .equals(
315: this .pem_keyFld + "@"
316: + this .pcmf_getObjName()))
317: this .pem_upstmt.setObject(this .pem_colanz + 1,
318: ((KeRegisteredObject) l_fld)
319: .pcmf_getValue());
320: } else if (l_fld instanceof IMoSingleValue) {
321: this .pem_upstmt.setObject(i, ((IMoSingleValue) l_fld)
322: .pcmf_getValue());
323: if (((IMoSingleValue) l_fld).pcmf_getMyTemplate() != null
324: && ((IMoSingleValue) l_fld)
325: .pcmf_getMyTemplate().equals(
326: this .pem_keyFld))
327: this .pem_upstmt.setObject(this .pem_colanz + 1,
328: ((IMoSingleValue) l_fld).pcmf_getValue());
329: } else
330: KeLog.pcmf_log("ug2t",
331: "sql-field connector no valid class:"
332: + l_fld.getClass().toString(), this ,
333: KeLog.ERROR);
334: }
335: ;
336: this .pcmf_setRefresh();
337: return (this .pem_upstmt.executeUpdate());
338: };
339:
340: public int pcmf_updateJoined() throws Exception {
341: int l_ret = this .pcmf_update();
342:
343: if (this .pem_joins != null) {
344: Iterator l_it2 = this .pem_joins.iterator();
345: while (l_it2.hasNext())
346: ((CoSqlTableJoin) l_it2.next()).pcmf_doUpdate();
347: }
348: return (l_ret);
349: }
350:
351: public int pcmf_insert() throws Exception {
352: Iterator l_it = this .pem_cols.values().iterator();
353:
354: for (int i = 1; i <= this .pem_colanz; i++) {
355: Object l_fld = null;
356:
357: l_fld = l_it.next();
358: if (l_fld instanceof KeRegisteredObject)
359: this .pem_ipstmt.setObject(i,
360: ((KeRegisteredObject) l_fld).pcmf_getValue());
361: else if (l_fld instanceof IMoSingleValue)
362: this .pem_ipstmt.setObject(i, ((IMoSingleValue) l_fld)
363: .pcmf_getValue());
364: else
365: KeLog.pcmf_log("ug2t",
366: "sql-field connector no valid class:"
367: + l_fld.getClass().toString(), this ,
368: KeLog.ERROR);
369: }
370: ;
371: this .pcmf_setRefresh();
372: return (this .pem_ipstmt.executeUpdate());
373: };
374:
375: public int pcmf_insertJoined() throws Exception {
376: int l_ret = this .pcmf_insert();
377:
378: if (this .pem_joins != null) {
379: Iterator l_it2 = this .pem_joins.iterator();
380: while (l_it2.hasNext())
381: ((CoSqlTableJoin) l_it2.next()).pcmf_doInsert();
382: }
383: ;
384:
385: return (l_ret);
386: }
387:
388: public ArrayList pcmf_getRowset() {
389: return (new ArrayList(this .pem_cols.values()));
390: };
391:
392: public ArrayList pcmf_getRowsetValues() {
393: ArrayList l_ret = new ArrayList();
394: Iterator l_it = this .pem_cols.values().iterator();
395:
396: while (l_it.hasNext())
397: l_ret.add(((IKeValueContainer) l_it.next()).pcmf_getValue()
398: .toString());
399:
400: return (l_ret);
401: };
402:
403: public Connection pcmf_getCon() {
404: return (this .pem_con);
405: };
406:
407: public IKeValueContainer pcmf_getField(String xName) {
408: return ((IKeValueContainer) this .pem_cols.get(xName));
409: };
410:
411: public void pcmf_setField(String xName, Object xValue) {
412: this .pcmf_getField(xName).pcmf_setValueValidate(xValue);
413: };
414:
415: public void pcmf_setConnectedField(String xName,
416: IKeValueContainer xNew) {
417: Object l_old = this .pem_cols.put(xName, xNew);
418: if (l_old instanceof KeRegisteredObject) {
419: try {
420: xNew.pcmf_setValueValidate(((KeRegisteredObject) l_old)
421: .pcmf_getValue());
422: ((KeRegisteredObject) l_old).pcmf_delete();
423: } catch (Exception e) {
424: KeLog.pcmf_logException("ug2t", this , e);
425: }
426: ;
427: }
428: };
429:
430: public void pcmf_setConnectedFieldValue(String xName,
431: IKeValueContainer xNew) {
432: Object l_old = this .pem_cols.put(xName, xNew);
433: if (l_old instanceof KeRegisteredObject) {
434: try {
435: ((KeRegisteredObject) l_old).pcmf_delete();
436: } catch (Exception e) {
437: KeLog.pcmf_logException("ug2t", this , e);
438: }
439: ;
440: }
441: };
442:
443: public void pcmf_delete() throws Exception {
444: if (this .pdm_deleted == true)
445: return;
446:
447: if (this .pem_ownCon == true)
448: this .pem_con.close();
449:
450: Iterator l_it = this .pem_cols.values().iterator();
451: Object l_obj = null;
452: while (l_it.hasNext()) {
453: l_obj = l_it.next();
454: if (l_obj instanceof CoSqlField)
455: ((CoSqlField) l_obj).pcmf_delete();
456: }
457: ;
458:
459: super .pcmf_delete();
460: }
461:
462: public void pcmf_addJoin(CoSqlTable xTarget, String xKeyFieldS,
463: String xKeyFieldT) {
464: CoSqlTableJoin l_join = new CoSqlTableJoin(this , xTarget,
465: xKeyFieldS, xKeyFieldT);
466: if (this .pem_joins == null)
467: this .pem_joins = new ArrayList();
468:
469: this .pem_joins.add(l_join);
470: }
471:
472: public ArrayList pcmf_getHeader() {
473: return new ArrayList(this .pem_cols.keySet());
474: }
475:
476: public KeTable pcmf_getComplete() {
477: KeTable l_tab = new KeTable();
478: int l_curser = this .pcmf_getCurserPos();
479:
480: try {
481: l_tab.pcmf_setHeader(this .pcmf_getHeader());
482: for (int i = 1;; i++) {
483: if (this .pcmf_setCurser(i) == false)
484: break;
485:
486: l_tab.pcmf_addRowArrayList(this .pcmf_getRowsetValues());
487: }
488: ;
489: this .pcmf_setCurser(l_curser);
490: } catch (Exception e) {
491: KeLog.pcmf_logException("ug2t", this, e);
492: }
493:
494: return (l_tab);
495: };
496: };
|