2 * Copyright (C) 2001, 2002 The Mir-coders group
4 * This file is part of Mir.
6 * Mir is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 2 of the License, or
9 * (at your option) any later version.
11 * Mir is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
16 * You should have received a copy of the GNU General Public License
17 * along with Mir; if not, write to the Free Software
18 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20 * In addition, as a special exception, The Mir-coders gives permission to link
21 * the code of this program with the com.oreilly.servlet library, any library
22 * licensed under the Apache Software License, The Sun (tm) Java Advanced
23 * Imaging library (JAI), The Sun JIMI library (or with modified versions of
24 * the above that use the same license as the above), and distribute linked
25 * combinations including the two. You must obey the GNU General Public
26 * License in all respects for all of the code used other than the above
27 * mentioned libraries. If you modify this file, you may extend this exception
28 * to your version of the file, but you are not obligated to do so. If you do
29 * not wish to do so, delete this exception statement from your version.
33 import com.codestudio.util.SQLManager;
35 import freemarker.template.SimpleHash;
36 import freemarker.template.SimpleList;
38 import mir.config.MirPropertiesConfiguration;
40 import mir.config.MirPropertiesConfiguration.PropertiesConfigExc;
42 import mir.entity.Entity;
43 import mir.entity.EntityList;
44 import mir.entity.StorableObjectEntity;
46 import mir.misc.HTMLTemplateProcessor;
47 import mir.misc.Logfile;
48 import mir.misc.StringUtil;
50 import mir.storage.store.ObjectStore;
51 import mir.storage.store.StorableObject;
52 import mir.storage.store.StoreContainerType;
53 import mir.storage.store.StoreIdentifier;
54 import mir.storage.store.StoreUtil;
56 import mir.util.JDBCStringRoutines;
58 import java.io.IOException;
59 import java.io.InputStreamReader;
61 import java.sql.Connection;
62 import java.sql.PreparedStatement;
63 import java.sql.ResultSet;
64 import java.sql.ResultSetMetaData;
65 import java.sql.SQLException;
66 import java.sql.Statement;
67 import java.sql.Timestamp;
69 import java.text.ParseException;
70 import java.text.SimpleDateFormat;
72 import java.util.ArrayList;
73 import java.util.Calendar;
74 import java.util.GregorianCalendar;
75 import java.util.HashMap;
79 * Diese Klasse implementiert die Zugriffsschicht auf die Datenbank.
80 * Alle Projektspezifischen Datenbankklassen erben von dieser Klasse.
81 * In den Unterklassen wird im Minimalfall nur die Tabelle angegeben.
82 * Im Konfigurationsfile findet sich eine Verweis auf den verwendeten
83 * Treiber, Host, User und Passwort, ueber den der Zugriff auf die
86 * @version $Id: Database.java,v 1.32 2003/01/28 21:47:42 idfx Exp $
90 public class Database implements StorageObject {
91 private static Class GENERIC_ENTITY_CLASS = null;
92 private static Class STORABLE_OBJECT_ENTITY_CLASS = null;
93 private static SimpleHash POPUP_EMTYLINE = new SimpleHash();
94 protected static final ObjectStore o_store = ObjectStore.getInstance();
95 private static final int _millisPerHour = 60 * 60 * 1000;
96 private static final int _millisPerMinute = 60 * 1000;
99 // always same object saves a little space
100 POPUP_EMTYLINE.put("key", "");
101 POPUP_EMTYLINE.put("value", "--");
104 GENERIC_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity");
105 STORABLE_OBJECT_ENTITY_CLASS =
106 Class.forName("mir.entity.StorableObjectEntity");
107 } catch (Exception e) {
108 System.err.println("FATAL: Database.java could not initialize" +
113 protected MirPropertiesConfiguration configuration;
114 protected String theTable;
115 protected String theCoreTable = null;
116 protected String thePKeyName = "id";
117 protected int thePKeyType;
118 protected int thePKeyIndex;
119 protected boolean evaluatedMetaData = false;
120 protected ArrayList metadataFields;
121 protected ArrayList metadataLabels;
122 protected ArrayList metadataNotNullFields;
123 protected int[] metadataTypes;
124 protected Class theEntityClass;
125 protected StorageObject myselfDatabase;
126 protected SimpleList popupCache = null;
127 protected boolean hasPopupCache = false;
128 protected SimpleHash hashCache = null;
129 protected boolean hasTimestamp = true;
130 private String database_driver;
131 private String database_url;
132 private int defaultLimit;
133 protected DatabaseAdaptor theAdaptor;
134 protected Logfile theLog;
135 private SimpleDateFormat _dateFormatterOut =
136 new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
137 private SimpleDateFormat _dateFormatterIn =
138 new SimpleDateFormat("yyyy-MM-dd HH:mm");
139 private Calendar _cal = new GregorianCalendar();
142 * Kontruktor bekommt den Filenamen des Konfigurationsfiles ?bergeben.
143 * Aus diesem file werden <code>Database.Logfile</code>,
144 * <code>Database.Username</code>,<code>Database.Password</code>,
145 * <code>Database.Host</code> und <code>Database.Adaptor</code>
146 * ausgelesen und ein Broker f?r die Verbindugen zur Datenbank
149 * @param String confFilename Dateiname der Konfigurationsdatei
151 public Database() throws StorageObjectFailure {
153 configuration = MirPropertiesConfiguration.instance();
154 } catch (PropertiesConfigExc e) {
155 throw new StorageObjectFailure(e);
159 Logfile.getInstance(configuration.getStringWithHome("Database.Logfile"));
161 String theAdaptorName = configuration.getString("Database.Adaptor");
162 defaultLimit = Integer.parseInt(configuration.getString("Database.Limit"));
165 theEntityClass = GENERIC_ENTITY_CLASS;
167 (DatabaseAdaptor) Class.forName(theAdaptorName).newInstance();
168 } catch (Exception e) {
169 theLog.printError("Error in Database() constructor with " +
170 theAdaptorName + " -- " + e.getMessage());
171 throw new StorageObjectFailure("Error in Database() constructor.", e);
176 * Liefert die Entity-Klasse zur?ck, in der eine Datenbankzeile gewrappt
177 * wird. Wird die Entity-Klasse durch die erbende Klasse nicht ?berschrieben,
178 * wird eine mir.entity.GenericEntity erzeugt.
180 * @return Class-Objekt der Entity
182 public java.lang.Class getEntityClass() {
183 return theEntityClass;
187 * Liefert die Standardbeschr?nkung von select-Statements zur?ck, also
188 * wieviel Datens?tze per Default selektiert werden.
190 * @return Standard-Anzahl der Datens?tze
192 public int getLimit() {
197 * Liefert den Namen des Primary-Keys zur?ck. Wird die Variable nicht von
198 * der erbenden Klasse ?berschrieben, so ist der Wert <code>PKEY</code>
199 * @return Name des Primary-Keys
201 public String getIdName() {
206 * Liefert den Namen der Tabelle, auf das sich das Datenbankobjekt bezieht.
208 * @return Name der Tabelle
210 public String getTableName() {
215 * Dient dazu vererbte Tabellen bei objectrelationalen DBMS
216 * zu speichern, wenn die id einer Tabelle in der parenttabelle verwaltet
218 * @return liefert theCoreTabel als String zurueck, wenn gesetzt, sonst
221 public String getCoreTable() {
222 if (theCoreTable != null) {
230 * Liefert Feldtypen der Felder der Tabelle zurueck (s.a. java.sql.Types)
231 * @return int-Array mit den Typen der Felder
232 * @exception StorageObjectException
234 public int[] getTypes() throws StorageObjectFailure {
235 if (metadataTypes == null) {
239 return metadataTypes;
243 * Liefert eine Liste der Labels der Tabellenfelder
244 * @return ArrayListe mit Labeln
245 * @exception StorageObjectException
247 public ArrayList getLabels() throws StorageObjectFailure {
248 if (metadataLabels == null) {
252 return metadataLabels;
256 * Liefert eine Liste der Felder der Tabelle
257 * @return ArrayList mit Feldern
258 * @exception StorageObjectException
260 public ArrayList getFields() throws StorageObjectFailure {
261 if (metadataFields == null) {
265 return metadataFields;
269 * Gets value out of ResultSet according to type and converts to String
270 * @param inValue Wert aus ResultSet.
271 * @param aType Datenbanktyp.
272 * @return liefert den Wert als String zurueck. Wenn keine Umwandlung moeglich
273 * dann /unsupported value/
275 private String getValueAsString(ResultSet rs, int valueIndex, int aType)
276 throws StorageObjectFailure {
277 String outValue = null;
282 case java.sql.Types.BIT:
283 outValue = (rs.getBoolean(valueIndex) == true) ? "1" : "0";
287 case java.sql.Types.INTEGER:
288 case java.sql.Types.SMALLINT:
289 case java.sql.Types.TINYINT:
290 case java.sql.Types.BIGINT:
292 int out = rs.getInt(valueIndex);
295 outValue = new Integer(out).toString();
300 case java.sql.Types.NUMERIC:
302 /** @todo Numeric can be float or double depending upon
303 * metadata.getScale() / especially with oracle */
304 long outl = rs.getLong(valueIndex);
307 outValue = new Long(outl).toString();
312 case java.sql.Types.REAL:
314 float tempf = rs.getFloat(valueIndex);
320 int tempf_int = (int) tempf;
321 tempf = (float) tempf_int;
323 outValue = "" + tempf;
324 outValue = outValue.replace('.', ',');
329 case java.sql.Types.DOUBLE:
331 double tempd = rs.getDouble(valueIndex);
337 int tempd_int = (int) tempd;
338 tempd = (double) tempd_int;
340 outValue = "" + tempd;
341 outValue = outValue.replace('.', ',');
346 case java.sql.Types.CHAR:
347 case java.sql.Types.VARCHAR:
348 case java.sql.Types.LONGVARCHAR:
349 outValue = rs.getString(valueIndex);
353 case java.sql.Types.LONGVARBINARY:
354 outValue = rs.getString(valueIndex);
358 case java.sql.Types.TIMESTAMP:
360 // it's important to use Timestamp here as getting it
361 // as a string is undefined and is only there for debugging
362 // according to the API. we can make it a string through formatting.
364 Timestamp timestamp = (rs.getTimestamp(valueIndex));
367 java.util.Date date = new java.util.Date(timestamp.getTime());
368 outValue = _dateFormatterOut.format(date);
372 _cal.get(Calendar.ZONE_OFFSET) + _cal.get(Calendar.DST_OFFSET);
374 StringUtil.zeroPaddingNumber(offset / _millisPerHour, 2, 2);
375 outValue = outValue + "+" + tzOffset;
381 outValue = "<unsupported value>";
382 theLog.printWarning("Unsupported Datatype: at " + valueIndex + " (" +
385 } catch (SQLException e) {
386 throw new StorageObjectFailure("Could not get Value out of Resultset -- ",
395 * select-Operator um einen Datensatz zu bekommen.
396 * @param id Primaerschluessel des Datensatzes.
397 * @return liefert EntityObject des gefundenen Datensatzes oder null.
399 public Entity selectById(String id) throws StorageObjectExc {
400 if ((id == null) || id.equals("")) {
401 throw new StorageObjectExc("id war null");
404 // ask object store for object
405 if (StoreUtil.implementsStorableObject(theEntityClass)) {
406 String uniqueId = id;
408 if (theEntityClass.equals(StorableObjectEntity.class)) {
409 uniqueId += ("@" + theTable);
412 StoreIdentifier search_sid =
413 new StoreIdentifier(theEntityClass, uniqueId);
414 theLog.printDebugInfo("CACHE: (dbg) looking for sid " +
415 search_sid.toString());
417 Entity hit = (Entity) o_store.use(search_sid);
424 Statement stmt = null;
425 Connection con = getPooledCon();
426 Entity returnEntity = null;
431 /** @todo better prepared statement */
433 "select * from " + theTable + " where " + thePKeyName + "=" + id;
434 stmt = con.createStatement();
435 rs = executeSql(stmt, selectSql);
438 if (evaluatedMetaData == false) {
439 evalMetaData(rs.getMetaData());
443 returnEntity = makeEntityFromResultSet(rs);
445 theLog.printDebugInfo("Keine daten fuer id: " + id + "in Tabelle" +
451 theLog.printDebugInfo("No Data for Id " + id + " in Table " + theTable);
453 } catch (SQLException sqe) {
454 throwSQLException(sqe, "selectById");
457 } catch (NumberFormatException e) {
458 theLog.printError("ID ist keine Zahl: " + id);
460 freeConnection(con, stmt);
463 /** @todo OS: Entity should be saved in ostore */
468 * select-Operator um Datensaetze zu bekommen, die key = value erfuellen.
469 * @param key Datenbankfeld der Bedingung.
470 * @param value Wert die der key anehmen muss.
471 * @return EntityList mit den gematchten Entities
473 public EntityList selectByFieldValue(String aField, String aValue)
474 throws StorageObjectFailure {
475 return selectByFieldValue(aField, aValue, 0);
479 * select-Operator um Datensaetze zu bekommen, die key = value erfuellen.
480 * @param key Datenbankfeld der Bedingung.
481 * @param value Wert die der key anehmen muss.
482 * @param offset Gibt an ab welchem Datensatz angezeigt werden soll.
483 * @return EntityList mit den gematchten Entities
485 public EntityList selectByFieldValue(String aField, String aValue, int offset)
486 throws StorageObjectFailure {
487 return selectByWhereClause(aField + "=" + aValue, offset);
491 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
492 * Also offset wird der erste Datensatz genommen.
494 * @param wc where-Clause
495 * @return EntityList mit den gematchten Entities
496 * @exception StorageObjectException
498 public EntityList selectByWhereClause(String where)
499 throws StorageObjectFailure {
500 return selectByWhereClause(where, 0);
504 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
505 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
507 * @param wc where-Clause
508 * @param offset ab welchem Datensatz.
509 * @return EntityList mit den gematchten Entities
510 * @exception StorageObjectException
512 public EntityList selectByWhereClause(String whereClause, int offset)
513 throws StorageObjectFailure {
514 return selectByWhereClause(whereClause, null, offset);
518 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
519 * Also offset wird der erste Datensatz genommen.
520 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
522 * @param wc where-Clause
523 * @param ob orderBy-Clause
524 * @return EntityList mit den gematchten Entities
525 * @exception StorageObjectException
527 public EntityList selectByWhereClause(String where, String order)
528 throws StorageObjectFailure {
529 return selectByWhereClause(where, order, 0);
533 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
534 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
536 * @param wc where-Clause
537 * @param ob orderBy-Clause
538 * @param offset ab welchem Datensatz
539 * @return EntityList mit den gematchten Entities
540 * @exception StorageObjectException
542 public EntityList selectByWhereClause(String whereClause, String orderBy,
543 int offset) throws StorageObjectFailure {
544 return selectByWhereClause(whereClause, orderBy, offset, defaultLimit);
548 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
549 * @param wc where-Clause
550 * @param ob orderBy-Clause
551 * @param offset ab welchem Datensatz
552 * @param limit wieviele Datens?tze
553 * @return EntityList mit den gematchten Entities
554 * @exception StorageObjectException
556 public EntityList selectByWhereClause(String wc, String ob, int offset,
557 int limit) throws StorageObjectFailure {
558 // check o_store for entitylist
559 if (StoreUtil.implementsStorableObject(theEntityClass)) {
560 StoreIdentifier search_sid =
561 new StoreIdentifier(theEntityClass,
562 StoreContainerType.STOC_TYPE_ENTITYLIST,
563 StoreUtil.getEntityListUniqueIdentifierFor(theTable, wc, ob, offset,
565 EntityList hit = (EntityList) o_store.use(search_sid);
568 theLog.printDebugInfo("CACHE (hit): " + search_sid.toString());
575 EntityList theReturnList = null;
576 Connection con = null;
577 Statement stmt = null;
582 // build sql-statement
584 /** @todo count sql string should only be assembled if we really count
585 * see below at the end of method //rk */
586 if ((wc != null) && (wc.length() == 0)) {
590 StringBuffer countSql =
591 new StringBuffer("select count(*) from ").append(theTable);
592 StringBuffer selectSql =
593 new StringBuffer("select * from ").append(theTable);
596 selectSql.append(" where ").append(wc);
597 countSql.append(" where ").append(wc);
600 if ((ob != null) && !(ob.length() == 0)) {
601 selectSql.append(" order by ").append(ob);
604 if (theAdaptor.hasLimit()) {
605 if ((limit > -1) && (offset > -1)) {
606 selectSql.append(" LIMIT ").append(limit).append(" OFFSET ").append(offset);
612 con = getPooledCon();
613 stmt = con.createStatement();
616 rs = executeSql(stmt, selectSql.toString());
619 if (!evaluatedMetaData) {
620 evalMetaData(rs.getMetaData());
623 theReturnList = new EntityList();
625 Entity theResultEntity;
628 theResultEntity = makeEntityFromResultSet(rs);
629 theReturnList.add(theResultEntity);
636 // making entitylist infos
637 if (!(theAdaptor.hasLimit())) {
641 if (theReturnList != null) {
642 // now we decide if we have to know an overall count...
645 if ((limit > -1) && (offset > -1)) {
646 if (offsetCount == limit) {
647 /** @todo counting should be deffered to entitylist
648 * getSize() should be used */
649 rs = executeSql(stmt, countSql.toString());
653 count = rs.getInt(1);
658 theLog.printError("Could not count: " + countSql);
663 theReturnList.setCount(count);
664 theReturnList.setOffset(offset);
665 theReturnList.setWhere(wc);
666 theReturnList.setOrder(ob);
667 theReturnList.setStorage(this);
668 theReturnList.setLimit(limit);
670 if (offset >= limit) {
671 theReturnList.setPrevBatch(offset - limit);
674 if ((offset + offsetCount) < count) {
675 theReturnList.setNextBatch(offset + limit);
678 if (StoreUtil.implementsStorableObject(theEntityClass)) {
679 StoreIdentifier sid = theReturnList.getStoreIdentifier();
680 theLog.printDebugInfo("CACHE (add): " + sid.toString());
684 } catch (SQLException sqe) {
685 throwSQLException(sqe, "selectByWhereClause");
689 freeConnection(con, stmt);
691 } catch (Throwable t) {
695 return theReturnList;
699 * Bastelt aus einer Zeile der Datenbank ein EntityObjekt.
701 * @param rs Das ResultSetObjekt.
702 * @return Entity Die Entity.
704 private Entity makeEntityFromResultSet(ResultSet rs)
705 throws StorageObjectFailure {
706 /** @todo OS: get Pkey from ResultSet and consult ObjectStore */
707 HashMap theResultHash = new HashMap();
708 String theResult = null;
710 Entity returnEntity = null;
713 int size = metadataFields.size();
715 for (int i = 0; i < size; i++) {
716 // alle durchlaufen bis nix mehr da
717 theType = metadataTypes[i];
719 if (theType == java.sql.Types.LONGVARBINARY) {
720 InputStreamReader is =
721 (InputStreamReader) rs.getCharacterStream(i + 1);
724 char[] data = new char[32768];
725 StringBuffer theResultString = new StringBuffer();
728 while ((len = is.read(data)) > 0) {
729 theResultString.append(data, 0, len);
733 theResult = theResultString.toString();
738 theResult = getValueAsString(rs, (i + 1), theType);
741 if (theResult != null) {
742 theResultHash.put(metadataFields.get(i), theResult);
746 if (theEntityClass != null) {
747 returnEntity = (Entity) theEntityClass.newInstance();
748 returnEntity.setValues(theResultHash);
749 returnEntity.setStorage(myselfDatabase);
751 if (returnEntity instanceof StorableObject) {
752 theLog.printDebugInfo("CACHE: ( in) " + returnEntity.getId() + " :" +
754 o_store.add(((StorableObject) returnEntity).getStoreIdentifier());
757 throwStorageObjectException("Internal Error: theEntityClass not set!");
759 } catch (IllegalAccessException e) {
760 throwStorageObjectException("No access! -- " + e.getMessage());
761 } catch (IOException e) {
762 throwStorageObjectException("IOException! -- " + e.getMessage());
763 } catch (InstantiationException e) {
764 throwStorageObjectException("No Instatiation! -- " + e.getMessage());
765 } catch (SQLException sqe) {
766 throwSQLException(sqe, "makeEntityFromResultSet");
775 * insert-Operator: f?gt eine Entity in die Tabelle ein. Eine Spalte WEBDB_CREATE
776 * wird automatisch mit dem aktuellen Datum gefuellt.
779 * @return der Wert des Primary-keys der eingef?gten Entity
781 public String insert(Entity theEntity) throws StorageObjectFailure {
783 invalidatePopupCache();
785 // invalidating all EntityLists corresponding with theEntityClass
786 if (StoreUtil.implementsStorableObject(theEntityClass)) {
787 StoreContainerType stoc_type =
788 StoreContainerType.valueOf(theEntityClass,
789 StoreContainerType.STOC_TYPE_ENTITYLIST);
790 o_store.invalidate(stoc_type);
793 String returnId = null;
794 Connection con = null;
795 PreparedStatement pstmt = null;
798 ArrayList streamedInput = theEntity.streamedInput();
799 StringBuffer f = new StringBuffer();
800 StringBuffer v = new StringBuffer();
803 boolean firstField = true;
806 for (int i = 0; i < getFields().size(); i++) {
807 aField = (String) getFields().get(i);
809 if (!aField.equals(thePKeyName)) {
813 if (aField.equals("webdb_create") ||
814 aField.equals("webdb_lastchange")) {
817 if ((streamedInput != null) && streamedInput.contains(aField)) {
820 if (theEntity.hasValueForField(aField)) {
823 JDBCStringRoutines.escapeStringLiteral((String) theEntity.getValue(
829 // wenn Wert gegeben, dann einbauen
830 if (aValue != null) {
831 if (firstField == false) {
846 StringBuffer sqlBuf =
847 new StringBuffer("insert into ").append(theTable).append("(").append(f)
848 .append(") values (").append(v).append(")");
849 String sql = sqlBuf.toString();
851 //theLog.printInfo("INSERT: " + sql);
852 con = getPooledCon();
853 con.setAutoCommit(false);
854 pstmt = con.prepareStatement(sql);
856 if (streamedInput != null) {
857 for (int i = 0; i < streamedInput.size(); i++) {
859 (String) theEntity.getValue((String) streamedInput.get(i));
860 pstmt.setBytes(i + 1, inputString.getBytes());
864 int ret = pstmt.executeUpdate();
872 con.prepareStatement(theAdaptor.getLastInsertSQL(
873 (Database) myselfDatabase));
875 ResultSet rs = pstmt.executeQuery();
877 returnId = rs.getString(1);
878 theEntity.setId(returnId);
879 } catch (SQLException sqe) {
880 throwSQLException(sqe, "insert");
883 con.setAutoCommit(true);
884 } catch (Exception e) {
888 freeConnection(con, pstmt);
891 /** @todo store entity in o_store */
896 * update-Operator: aktualisiert eine Entity. Eine Spalte WEBDB_LASTCHANGE
897 * wird automatisch mit dem aktuellen Datum gefuellt.
901 public void update(Entity theEntity) throws StorageObjectFailure {
902 Connection con = null;
903 PreparedStatement pstmt = null;
905 /** @todo this is stupid: why do we prepare statement, when we
906 * throw it away afterwards. should be regular statement
907 * update/insert could better be one routine called save()
908 * that chooses to either insert or update depending if we
909 * have a primary key in the entity. i don't know if we
910 * still need the streamed input fields. // rk */
911 /** @todo extension: check if Entity did change, otherwise we don't need
912 * the roundtrip to the database */
913 /** invalidating corresponding entitylists in o_store*/
914 if (StoreUtil.implementsStorableObject(theEntityClass)) {
915 StoreContainerType stoc_type =
916 StoreContainerType.valueOf(theEntityClass,
917 StoreContainerType.STOC_TYPE_ENTITYLIST);
918 o_store.invalidate(stoc_type);
921 ArrayList streamedInput = theEntity.streamedInput();
922 String id = theEntity.getId();
924 StringBuffer fv = new StringBuffer();
925 boolean firstField = true;
928 invalidatePopupCache();
930 // build sql statement
931 for (int i = 0; i < getFields().size(); i++) {
932 aField = (String) metadataFields.get(i);
935 if (!(aField.equals(thePKeyName) || aField.equals("webdb_create") ||
936 aField.equals("webdb_lastchange") ||
937 ((streamedInput != null) && streamedInput.contains(aField)))) {
938 if (theEntity.hasValueForField(aField)) {
939 if (firstField == false) {
945 fv.append(aField).append("='")
946 .append(JDBCStringRoutines.escapeStringLiteral(
947 (String) theEntity.getValue(aField))).append("'");
949 // fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getValue(aField))).append("'");
955 new StringBuffer("update ").append(theTable).append(" set ").append(fv);
958 if (metadataFields.contains("webdb_lastchange")) {
959 sql.append(",webdb_lastchange=NOW()");
962 // special case: the webdb_create requires the field in yyyy-mm-dd HH:mm
963 // format so anything extra will be ignored. -mh
964 if (metadataFields.contains("webdb_create") &&
965 theEntity.hasValueForField("webdb_create")) {
966 // minimum of 10 (yyyy-mm-dd)...
967 if (theEntity.getValue("webdb_create").length() >= 10) {
968 String dateString = theEntity.getValue("webdb_create");
970 // if only 10, then add 00:00 so it doesn't throw a ParseException
971 if (dateString.length() == 10) {
972 dateString = dateString + " 00:00";
977 java.util.Date d = _dateFormatterIn.parse(dateString);
978 Timestamp tStamp = new Timestamp(d.getTime());
979 sql.append(",webdb_create='" + tStamp.toString() + "'");
980 } catch (ParseException e) {
981 throw new StorageObjectFailure(e);
986 if (streamedInput != null) {
987 for (int i = 0; i < streamedInput.size(); i++) {
988 sql.append(",").append(streamedInput.get(i)).append("=?");
992 sql.append(" where id=").append(id);
994 //theLog.printInfo("UPDATE: " + sql);
997 con = getPooledCon();
998 con.setAutoCommit(false);
999 pstmt = con.prepareStatement(sql.toString());
1001 if (streamedInput != null) {
1002 for (int i = 0; i < streamedInput.size(); i++) {
1003 String inputString =
1004 theEntity.getValue((String) streamedInput.get(i));
1005 pstmt.setBytes(i + 1, inputString.getBytes());
1009 pstmt.executeUpdate();
1010 } catch (SQLException sqe) {
1011 throwSQLException(sqe, "update");
1014 con.setAutoCommit(true);
1015 } catch (Exception e) {
1019 freeConnection(con, pstmt);
1025 * @param id des zu loeschenden Datensatzes
1026 * @return boolean liefert true zurueck, wenn loeschen erfolgreich war.
1028 public boolean delete(String id) throws StorageObjectFailure {
1029 invalidatePopupCache();
1031 // ostore send notification
1032 if (StoreUtil.implementsStorableObject(theEntityClass)) {
1033 String uniqueId = id;
1035 if (theEntityClass.equals(StorableObjectEntity.class)) {
1036 uniqueId += ("@" + theTable);
1039 theLog.printInfo("CACHE: (del) " + id);
1041 StoreIdentifier search_sid =
1042 new StoreIdentifier(theEntityClass,
1043 StoreContainerType.STOC_TYPE_ENTITY, uniqueId);
1044 o_store.invalidate(search_sid);
1047 /** @todo could be prepared Statement */
1048 Statement stmt = null;
1049 Connection con = null;
1052 "delete from " + theTable + " where " + thePKeyName + "='" + id + "'";
1054 //theLog.printInfo("DELETE " + sql);
1056 con = getPooledCon();
1057 stmt = con.createStatement();
1058 res = stmt.executeUpdate(sql);
1059 } catch (SQLException sqe) {
1060 throwSQLException(sqe, "delete");
1062 freeConnection(con, stmt);
1065 return (res > 0) ? true : false;
1068 /* noch nicht implementiert.
1069 * @return immer false
1071 public boolean delete(EntityList theEntityList) {
1072 invalidatePopupCache();
1078 * Diese Methode sollte ueberschrieben werden, wenn fuer die abgeleitete Database-Klasse
1079 * eine SimpleList mit Standard-Popupdaten erzeugt werden koennen soll.
1082 public SimpleList getPopupData() throws StorageObjectFailure {
1087 * Holt Daten fuer Popups.
1088 * @param name Name des Feldes.
1089 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
1090 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
1092 public SimpleList getPopupData(String name, boolean hasNullValue)
1093 throws StorageObjectFailure {
1094 return getPopupData(name, hasNullValue, null);
1098 * Holt Daten fuer Popups.
1099 * @param name Name des Feldes.
1100 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
1101 * @param where Schraenkt die Selektion der Datensaetze ein.
1102 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
1104 public SimpleList getPopupData(String name, boolean hasNullValue, String where)
1105 throws StorageObjectFailure {
1106 return getPopupData(name, hasNullValue, where, null);
1110 * Holt Daten fuer Popups.
1111 * @param name Name des Feldes.
1112 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
1113 * @param where Schraenkt die Selektion der Datensaetze ein.
1114 * @param order Gibt ein Feld als Sortierkriterium an.
1115 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
1117 public SimpleList getPopupData(String name, boolean hasNullValue,
1118 String where, String order) throws StorageObjectFailure {
1120 if (hasPopupCache && (popupCache != null)) {
1124 SimpleList simpleList = null;
1125 Connection con = null;
1126 Statement stmt = null;
1130 new StringBuffer("select ").append(thePKeyName).append(",").append(name)
1131 .append(" from ").append(theTable);
1133 if ((where != null) && !(where.length() == 0)) {
1134 sql.append(" where ").append(where);
1137 sql.append(" order by ");
1139 if ((order != null) && !(order.length() == 0)) {
1147 con = getPooledCon();
1148 } catch (Exception e) {
1149 throw new StorageObjectFailure(e);
1153 stmt = con.createStatement();
1155 ResultSet rs = executeSql(stmt, sql.toString());
1158 if (!evaluatedMetaData) {
1162 simpleList = new SimpleList();
1164 // if popup has null-selector
1166 simpleList.add(POPUP_EMTYLINE);
1169 SimpleHash popupDict;
1172 popupDict = new SimpleHash();
1173 popupDict.put("key", getValueAsString(rs, 1, thePKeyType));
1174 popupDict.put("value", rs.getString(2));
1175 simpleList.add(popupDict);
1180 } catch (Exception e) {
1181 theLog.printError("getPopupData: " + e.getMessage());
1182 throw new StorageObjectFailure(e);
1184 freeConnection(con, stmt);
1187 if (hasPopupCache) {
1188 popupCache = simpleList;
1195 * Liefert alle Daten der Tabelle als SimpleHash zurueck. Dies wird verwandt,
1196 * wenn in den Templates ein Lookup-Table benoetigt wird. Sollte nur bei kleinen
1197 * Tabellen Verwendung finden.
1198 * @return SimpleHash mit den Tabellezeilen.
1200 public SimpleHash getHashData() {
1201 /** @todo dangerous! this should have a flag to be enabled, otherwise
1202 * very big Hashes could be returned */
1203 if (hashCache == null) {
1206 HTMLTemplateProcessor.makeSimpleHash(selectByWhereClause("", -1));
1207 } catch (StorageObjectFailure e) {
1208 theLog.printDebugInfo(e.getMessage());
1215 /* invalidates the popupCache
1217 protected void invalidatePopupCache() {
1218 /** @todo invalidates toooo much */
1224 * Diese Methode fuehrt den Sqlstring <i>sql</i> aus und timed im Logfile.
1225 * @param stmt Statemnt
1226 * @param sql Sql-String
1228 * @exception StorageObjectException
1230 public ResultSet executeSql(Statement stmt, String sql)
1231 throws StorageObjectFailure, SQLException {
1232 long startTime = System.currentTimeMillis();
1236 rs = stmt.executeQuery(sql);
1238 //theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1239 } catch (SQLException e) {
1240 theLog.printDebugInfo("Failed: " +
1241 (System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1249 * Fuehrt Statement stmt aus und liefert Resultset zurueck. Das SQL-Statment wird
1250 * getimed und geloggt.
1251 * @param stmt PreparedStatement mit der SQL-Anweisung
1252 * @return Liefert ResultSet des Statements zurueck.
1253 * @exception StorageObjectException, SQLException
1255 public ResultSet executeSql(PreparedStatement stmt)
1256 throws StorageObjectFailure, SQLException {
1257 long startTime = (new java.util.Date()).getTime();
1258 ResultSet rs = stmt.executeQuery();
1259 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms.");
1265 * returns the number of rows in the table
1267 public int getSize(String where) throws SQLException, StorageObjectFailure {
1268 long startTime = System.currentTimeMillis();
1269 String sql = "SELECT Count(*) FROM " + theTable;
1271 if ((where != null) && !(where.length() == 0)) {
1272 sql = sql + " where " + where;
1275 Connection con = null;
1276 Statement stmt = null;
1280 con = getPooledCon();
1281 stmt = con.createStatement();
1283 ResultSet rs = executeSql(stmt, sql);
1286 result = rs.getInt(1);
1288 } catch (SQLException e) {
1289 theLog.printError(e.getMessage());
1291 freeConnection(con, stmt);
1294 //theLog.printInfo(theTable + " has "+ result +" rows where " + where);
1295 //theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1299 public int executeUpdate(Statement stmt, String sql)
1300 throws StorageObjectFailure, SQLException {
1302 long startTime = (new java.util.Date()).getTime();
1305 rs = stmt.executeUpdate(sql);
1307 //theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + sql);
1308 } catch (SQLException e) {
1309 theLog.printDebugInfo("Failed: " +
1310 (new java.util.Date().getTime() - startTime) + "ms. for: " + sql);
1317 public int executeUpdate(String sql)
1318 throws StorageObjectFailure, SQLException {
1320 long startTime = (new java.util.Date()).getTime();
1321 Connection con = null;
1322 PreparedStatement pstmt = null;
1325 con = getPooledCon();
1326 pstmt = con.prepareStatement(sql);
1327 result = pstmt.executeUpdate();
1328 } catch (Exception e) {
1329 theLog.printDebugInfo("executeUpdate failed: " + e.getMessage());
1330 throw new StorageObjectFailure("executeUpdate failed", e);
1332 freeConnection(con, pstmt);
1335 //theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + sql);
1340 * Wertet ResultSetMetaData aus und setzt interne Daten entsprechend
1341 * @param md ResultSetMetaData
1342 * @exception StorageObjectException
1344 private void evalMetaData(ResultSetMetaData md) throws StorageObjectFailure {
1345 this.evaluatedMetaData = true;
1346 this.metadataFields = new ArrayList();
1347 this.metadataLabels = new ArrayList();
1348 this.metadataNotNullFields = new ArrayList();
1351 int numFields = md.getColumnCount();
1352 this.metadataTypes = new int[numFields];
1357 for (int i = 1; i <= numFields; i++) {
1358 aField = md.getColumnName(i);
1359 metadataFields.add(aField);
1360 metadataLabels.add(md.getColumnLabel(i));
1361 aType = md.getColumnType(i);
1362 metadataTypes[i - 1] = aType;
1364 if (aField.equals(thePKeyName)) {
1365 thePKeyType = aType;
1369 if (md.isNullable(i) == ResultSetMetaData.columnNullable) {
1370 metadataNotNullFields.add(aField);
1373 } catch (SQLException e) {
1374 throwSQLException(e, "evalMetaData");
1379 * Wertet die Metadaten eines Resultsets fuer eine Tabelle aus,
1380 * um die alle Columns und Typen einer Tabelle zu ermitteln.
1382 private void get_meta_data() throws StorageObjectFailure {
1383 Connection con = null;
1384 PreparedStatement pstmt = null;
1385 String sql = "select * from " + theTable + " where 0=1";
1388 con = getPooledCon();
1389 pstmt = con.prepareStatement(sql);
1391 //theLog.printInfo("METADATA: " + sql);
1392 ResultSet rs = pstmt.executeQuery();
1393 evalMetaData(rs.getMetaData());
1395 } catch (SQLException e) {
1396 throwSQLException(e, "get_meta_data");
1398 freeConnection(con, pstmt);
1402 public Connection getPooledCon() throws StorageObjectFailure {
1403 /* @todo , doublecheck but I'm pretty sure that this is unnecessary. -mh
1405 Class.forName("com.codestudio.sql.PoolMan").newInstance();
1406 } catch (Exception e){
1407 throw new StorageObjectException("Could not find the PoolMan Driver"
1410 Connection con = null;
1413 con = SQLManager.getInstance().requestConnection();
1414 } catch (SQLException e) {
1415 theLog.printError("could not connect to the database " + e.getMessage());
1416 System.err.println("could not connect to the database " + e.getMessage());
1417 throw new StorageObjectFailure("Could not connect to the database", e);
1423 public void freeConnection(Connection con, Statement stmt)
1424 throws StorageObjectFailure {
1425 SQLManager.closeStatement(stmt);
1426 SQLManager.getInstance().returnConnection(con);
1430 * Wertet SQLException aus und wirft dannach eine StorageObjectException
1431 * @param sqe SQLException
1432 * @param wo Funktonsname, in der die SQLException geworfen wurde
1433 * @exception StorageObjectException
1435 protected void throwSQLException(SQLException sqe, String wo)
1436 throws StorageObjectFailure {
1438 String message = "";
1442 state = sqe.getSQLState();
1443 message = sqe.getMessage();
1444 vendor = sqe.getErrorCode();
1447 theLog.printError(state + ": " + vendor + " : " + message + " Funktion: " +
1449 throw new StorageObjectFailure((sqe == null) ? "undefined sql exception"
1450 : sqe.getMessage(), sqe);
1453 protected void _throwStorageObjectException(Exception e, String wo)
1454 throws StorageObjectFailure {
1456 theLog.printError(e.getMessage() + wo);
1457 throw new StorageObjectFailure(wo, e);
1459 theLog.printError(wo);
1460 throw new StorageObjectFailure(wo, null);
1465 * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach
1466 * eine StorageObjectException
1467 * @param message Nachricht mit dem Fehler
1468 * @exception StorageObjectException
1470 void throwStorageObjectException(String message) throws StorageObjectFailure {
1471 _throwStorageObjectException(null, message);