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 any library licensed under the Apache Software License,
22 * The Sun (tm) Java Advanced Imaging library (JAI), The Sun JIMI library
23 * (or with modified versions of the above that use the same license as the above),
24 * and distribute linked combinations including the two. You must obey the
25 * GNU General Public License in all respects for all of the code used other than
26 * the above mentioned libraries. If you modify this file, you may extend this
27 * exception to your version of the file, but you are not obligated to do so.
28 * If you do not wish to do so, delete this exception statement from your version.
32 import java.io.IOException;
33 import java.io.InputStreamReader;
34 import java.sql.Connection;
35 import java.sql.PreparedStatement;
36 import java.sql.ResultSet;
37 import java.sql.ResultSetMetaData;
38 import java.sql.SQLException;
39 import java.sql.Statement;
40 import java.sql.Timestamp;
41 import java.text.ParseException;
42 import java.text.SimpleDateFormat;
43 import java.util.ArrayList;
44 import java.util.Calendar;
45 import java.util.GregorianCalendar;
46 import java.util.HashMap;
50 import com.codestudio.util.SQLManager;
52 import freemarker.template.SimpleHash;
53 import freemarker.template.SimpleList;
55 import mir.config.MirPropertiesConfiguration;
56 import mir.config.MirPropertiesConfiguration.PropertiesConfigExc;
57 import mir.entity.Entity;
58 import mir.entity.EntityList;
59 import mir.entity.StorableObjectEntity;
60 import mir.log.LoggerWrapper;
61 import mir.misc.HTMLTemplateProcessor;
62 import mir.misc.StringUtil;
63 import mir.storage.store.ObjectStore;
64 import mir.storage.store.StorableObject;
65 import mir.storage.store.StoreContainerType;
66 import mir.storage.store.StoreIdentifier;
67 import mir.storage.store.StoreUtil;
68 import mir.util.JDBCStringRoutines;
72 * Diese Klasse implementiert die Zugriffsschicht auf die Datenbank.
73 * Alle Projektspezifischen Datenbankklassen erben von dieser Klasse.
74 * In den Unterklassen wird im Minimalfall nur die Tabelle angegeben.
75 * Im Konfigurationsfile findet sich eine Verweis auf den verwendeten
76 * Treiber, Host, User und Passwort, ueber den der Zugriff auf die
79 * @version $Id: Database.java,v 1.44.2.4 2003/06/23 15:24:06 zapata Exp $
83 public class Database implements StorageObject {
84 private static Class GENERIC_ENTITY_CLASS = mir.entity.StorableObjectEntity.class;
85 private static Class STORABLE_OBJECT_ENTITY_CLASS = mir.entity.StorableObjectEntity.class;
88 private static SimpleHash POPUP_EMPTYLINE = new SimpleHash();
89 protected static final ObjectStore o_store = ObjectStore.getInstance();
90 private static final int _millisPerHour = 60 * 60 * 1000;
91 private static final int _millisPerMinute = 60 * 1000;
94 // always same object saves a little space
95 POPUP_EMPTYLINE.put("key", "");
96 POPUP_EMPTYLINE.put("value", "--");
99 protected LoggerWrapper logger;
100 protected MirPropertiesConfiguration configuration;
101 protected String theTable;
102 protected String theCoreTable = null;
103 protected String thePKeyName = "id";
104 protected int thePKeyType;
105 protected int thePKeyIndex;
106 protected boolean evaluatedMetaData = false;
107 protected ArrayList metadataFields;
108 protected ArrayList metadataLabels;
109 protected ArrayList metadataNotNullFields;
110 protected int[] metadataTypes;
111 protected Class theEntityClass;
112 protected SimpleList popupCache = null;
113 protected boolean hasPopupCache = false;
114 protected SimpleHash hashCache = null;
115 protected boolean hasTimestamp = true;
116 private String database_driver;
117 private String database_url;
118 private int defaultLimit;
119 protected DatabaseAdaptor theAdaptor;
121 SimpleDateFormat internalDateFormat;
122 SimpleDateFormat userInputDateFormat;
124 private SimpleDateFormat _dateFormatterOut;
125 private SimpleDateFormat _dateFormatterIn;
126 _dateFormatterOut = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
127 _dateFormatterIn = new SimpleDateFormat("yyyy-MM-dd HH:mm");
131 * Kontruktor bekommt den Filenamen des Konfigurationsfiles ?bergeben.
132 * Aus diesem file werden <code>Database.Logfile</code>,
133 * <code>Database.Username</code>,<code>Database.Password</code>,
134 * <code>Database.Host</code> und <code>Database.Adaptor</code>
135 * ausgelesen und ein Broker f?r die Verbindugen zur Datenbank
138 * @param String confFilename Dateiname der Konfigurationsdatei
140 public Database() throws StorageObjectFailure {
142 configuration = MirPropertiesConfiguration.instance();
144 catch (PropertiesConfigExc e) {
145 throw new StorageObjectFailure(e);
147 logger = new LoggerWrapper("Database");
148 timezone = TimeZone.getTimeZone(configuration.getString("Mir.DefaultTimezone"));
149 internalDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
150 internalDateFormat.setTimeZone(timezone);
152 userInputDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
153 userInputDateFormat.setTimeZone(timezone);
156 String theAdaptorName = configuration.getString("Database.Adaptor");
157 defaultLimit = Integer.parseInt(configuration.getString("Database.Limit"));
160 theEntityClass = GENERIC_ENTITY_CLASS;
161 theAdaptor = (DatabaseAdaptor) Class.forName(theAdaptorName).newInstance();
163 catch (Throwable e) {
164 logger.error("Error in Database() constructor with " + theAdaptorName + " -- " + e.getMessage());
165 throw new StorageObjectFailure("Error in Database() constructor.", e);
170 * Liefert die Entity-Klasse zur?ck, in der eine Datenbankzeile gewrappt
171 * wird. Wird die Entity-Klasse durch die erbende Klasse nicht ?berschrieben,
172 * wird eine mir.entity.GenericEntity erzeugt.
174 * @return Class-Objekt der Entity
176 public java.lang.Class getEntityClass() {
177 return theEntityClass;
181 * Liefert die Standardbeschr?nkung von select-Statements zur?ck, also
182 * wieviel Datens?tze per Default selektiert werden.
184 * @return Standard-Anzahl der Datens?tze
186 public int getLimit() {
191 * Liefert den Namen des Primary-Keys zur?ck. Wird die Variable nicht von
192 * der erbenden Klasse ?berschrieben, so ist der Wert <code>PKEY</code>
193 * @return Name des Primary-Keys
195 public String getIdName() {
200 * Liefert den Namen der Tabelle, auf das sich das Datenbankobjekt bezieht.
202 * @return Name der Tabelle
204 public String getTableName() {
209 * Dient dazu vererbte Tabellen bei objectrelationalen DBMS
210 * zu speichern, wenn die id einer Tabelle in der parenttabelle verwaltet
212 * @return liefert theCoreTabel als String zurueck, wenn gesetzt, sonst
215 public String getCoreTable() {
216 if (theCoreTable != null) {
225 * Liefert Feldtypen der Felder der Tabelle zurueck (s.a. java.sql.Types)
226 * @return int-Array mit den Typen der Felder
227 * @exception StorageObjectException
229 public int[] getTypes() throws StorageObjectFailure {
230 if (metadataTypes == null) {
234 return metadataTypes;
238 * Liefert eine Liste der Labels der Tabellenfelder
239 * @return ArrayListe mit Labeln
240 * @exception StorageObjectException
242 public List getLabels() throws StorageObjectFailure {
243 if (metadataLabels == null) {
247 return metadataLabels;
251 * Liefert eine Liste der Felder der Tabelle
252 * @return ArrayList mit Feldern
253 * @exception StorageObjectException
255 public List getFields() throws StorageObjectFailure {
256 if (metadataFields == null) {
260 return metadataFields;
264 * Gets value out of ResultSet according to type and converts to String
265 * @param inValue Wert aus ResultSet.
266 * @param aType Datenbanktyp.
267 * @return liefert den Wert als String zurueck. Wenn keine Umwandlung moeglich
268 * dann /unsupported value/
270 private String getValueAsString(ResultSet rs, int valueIndex, int aType)
271 throws StorageObjectFailure {
272 String outValue = null;
277 case java.sql.Types.BIT:
278 outValue = (rs.getBoolean(valueIndex) == true) ? "1" : "0";
282 case java.sql.Types.INTEGER:
283 case java.sql.Types.SMALLINT:
284 case java.sql.Types.TINYINT:
285 case java.sql.Types.BIGINT:
287 int out = rs.getInt(valueIndex);
290 outValue = new Integer(out).toString();
295 case java.sql.Types.NUMERIC:
297 /** @todo Numeric can be float or double depending upon
298 * metadata.getScale() / especially with oracle */
299 long outl = rs.getLong(valueIndex);
302 outValue = new Long(outl).toString();
307 case java.sql.Types.REAL:
309 float tempf = rs.getFloat(valueIndex);
315 int tempf_int = (int) tempf;
316 tempf = (float) tempf_int;
318 outValue = "" + tempf;
319 outValue = outValue.replace('.', ',');
324 case java.sql.Types.DOUBLE:
326 double tempd = rs.getDouble(valueIndex);
332 int tempd_int = (int) tempd;
333 tempd = (double) tempd_int;
335 outValue = "" + tempd;
336 outValue = outValue.replace('.', ',');
341 case java.sql.Types.CHAR:
342 case java.sql.Types.VARCHAR:
343 case java.sql.Types.LONGVARCHAR:
344 outValue = rs.getString(valueIndex);
348 case java.sql.Types.LONGVARBINARY:
349 outValue = rs.getString(valueIndex);
353 case java.sql.Types.TIMESTAMP:
355 // it's important to use Timestamp here as getting it
356 // as a string is undefined and is only there for debugging
357 // according to the API. we can make it a string through formatting.
359 Timestamp timestamp = (rs.getTimestamp(valueIndex));
362 java.util.Date date = new java.util.Date(timestamp.getTime());
364 Calendar calendar = new GregorianCalendar();
365 calendar.setTime(date);
366 calendar.setTimeZone(timezone);
367 outValue = internalDateFormat.format(date);
369 int offset = calendar.get(Calendar.ZONE_OFFSET) + calendar.get(Calendar.DST_OFFSET);
370 String tzOffset = StringUtil.zeroPaddingNumber(Math.abs(offset) / _millisPerHour, 2, 2);
373 outValue = outValue + "-";
375 outValue = outValue + "+";
376 outValue = outValue + tzOffset;
382 outValue = "<unsupported value>";
383 logger.warn("Unsupported Datatype: at " + valueIndex + " (" + aType + ")");
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("Database.selectById: Missing id");
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 = new StoreIdentifier(theEntityClass, uniqueId);
413 logger.debug("CACHE: (dbg) looking for sid " + search_sid.toString());
415 Entity hit = (Entity) o_store.use(search_sid);
422 Statement stmt = null;
423 Connection con = getPooledCon();
424 Entity returnEntity = null;
429 /** @todo better prepared statement */
431 "select * from " + theTable + " where " + thePKeyName + "=" + id;
432 stmt = con.createStatement();
433 rs = executeSql(stmt, selectSql);
436 if (evaluatedMetaData == false) {
437 evalMetaData(rs.getMetaData());
441 returnEntity = makeEntityFromResultSet(rs);
444 logger.debug("No data for id: " + id + " in table " + theTable);
450 logger.debug("No Data for Id " + id + " in Table " + theTable);
453 catch (SQLException sqe) {
454 throwSQLException(sqe, "selectById");
457 catch (NumberFormatException e) {
458 logger.error("ID is no number: " + id);
461 freeConnection(con, stmt);
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) throws StorageObjectFailure {
474 return selectByFieldValue(aField, aValue, 0);
478 * select-Operator um Datensaetze zu bekommen, die key = value erfuellen.
479 * @param key Datenbankfeld der Bedingung.
480 * @param value Wert die der key anehmen muss.
481 * @param offset Gibt an ab welchem Datensatz angezeigt werden soll.
482 * @return EntityList mit den gematchten Entities
484 public EntityList selectByFieldValue(String aField, String aValue, int offset) throws StorageObjectFailure {
485 return selectByWhereClause(aField + "=" + aValue, offset);
489 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
490 * Also offset wird der erste Datensatz genommen.
492 * @param wc where-Clause
493 * @return EntityList mit den gematchten Entities
494 * @exception StorageObjectException
496 public EntityList selectByWhereClause(String where) throws StorageObjectFailure {
497 return selectByWhereClause(where, 0);
501 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
502 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
504 * @param wc where-Clause
505 * @param offset ab welchem Datensatz.
506 * @return EntityList mit den gematchten Entities
507 * @exception StorageObjectException
509 public EntityList selectByWhereClause(String whereClause, int offset) throws StorageObjectFailure {
510 return selectByWhereClause(whereClause, null, offset);
514 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
515 * Also offset wird der erste Datensatz genommen.
516 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
518 * @param wc where-Clause
519 * @param ob orderBy-Clause
520 * @return EntityList mit den gematchten Entities
521 * @exception StorageObjectException
523 public EntityList selectByWhereClause(String where, String order) throws StorageObjectFailure {
524 return selectByWhereClause(where, order, 0);
528 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
529 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
531 * @param wc where-Clause
532 * @param ob orderBy-Clause
533 * @param offset ab welchem Datensatz
534 * @return EntityList mit den gematchten Entities
535 * @exception StorageObjectException
537 public EntityList selectByWhereClause(String whereClause, String orderBy, int offset) throws StorageObjectFailure {
538 return selectByWhereClause(whereClause, orderBy, offset, defaultLimit);
542 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
543 * @param aWhereClause where-Clause
544 * @param anOrderByClause orderBy-Clause
545 * @param offset ab welchem Datensatz
546 * @param limit wieviele Datens?tze
547 * @return EntityList mit den gematchten Entities
548 * @exception StorageObjectException
550 public EntityList selectByWhereClause(String aWhereClause, String anOrderByClause,
551 int offset, int limit) throws StorageObjectFailure {
553 // check o_store for entitylist
554 if (StoreUtil.implementsStorableObject(theEntityClass)) {
555 StoreIdentifier search_sid =
557 theEntityClass, StoreContainerType.STOC_TYPE_ENTITYLIST,
558 StoreUtil.getEntityListUniqueIdentifierFor(theTable, aWhereClause, anOrderByClause, offset, limit));
559 EntityList hit = (EntityList) o_store.use(search_sid);
562 logger.debug("CACHE (hit): " + search_sid.toString());
569 EntityList theReturnList = null;
570 Connection con = null;
571 Statement stmt = null;
576 // build sql-statement
578 /** @todo count sql string should only be assembled if we really count
579 * see below at the end of method //rk */
580 if ((aWhereClause != null) && (aWhereClause.trim().length() == 0)) {
584 StringBuffer countSql =
585 new StringBuffer("select count(*) from ").append(theTable);
586 StringBuffer selectSql =
587 new StringBuffer("select * from ").append(theTable);
589 if (aWhereClause != null) {
590 selectSql.append(" where ").append(aWhereClause);
591 countSql.append(" where ").append(aWhereClause);
594 if ((anOrderByClause != null) && !(anOrderByClause.trim().length() == 0)) {
595 selectSql.append(" order by ").append(anOrderByClause);
598 if (theAdaptor.hasLimit()) {
599 if ((limit > -1) && (offset > -1)) {
600 selectSql.append(" LIMIT ").append(limit).append(" OFFSET ").append(offset);
606 con = getPooledCon();
607 stmt = con.createStatement();
610 rs = executeSql(stmt, selectSql.toString());
613 if (!evaluatedMetaData) {
614 evalMetaData(rs.getMetaData());
617 theReturnList = new EntityList();
619 Entity theResultEntity;
622 theResultEntity = makeEntityFromResultSet(rs);
623 theReturnList.add(theResultEntity);
630 // making entitylist infos
631 if (!(theAdaptor.hasLimit())) {
635 if (theReturnList != null) {
636 // now we decide if we have to know an overall count...
639 if ((limit > -1) && (offset > -1)) {
640 if (offsetCount == limit) {
641 /** @todo counting should be deffered to entitylist
642 * getSize() should be used */
643 rs = executeSql(stmt, countSql.toString());
647 count = rs.getInt(1);
653 logger.error("Could not count: " + countSql);
658 theReturnList.setCount(count);
659 theReturnList.setOffset(offset);
660 theReturnList.setWhere(aWhereClause);
661 theReturnList.setOrder(anOrderByClause);
662 theReturnList.setStorage(this);
663 theReturnList.setLimit(limit);
665 if (offset >= limit) {
666 theReturnList.setPrevBatch(offset - limit);
669 if ((offset + offsetCount) < count) {
670 theReturnList.setNextBatch(offset + limit);
673 if (StoreUtil.implementsStorableObject(theEntityClass)) {
674 StoreIdentifier sid = theReturnList.getStoreIdentifier();
675 logger.debug("CACHE (add): " + sid.toString());
680 catch (SQLException sqe) {
681 throwSQLException(sqe, "selectByWhereClause");
686 freeConnection(con, stmt);
688 } catch (Throwable t) {
692 return theReturnList;
696 * Bastelt aus einer Zeile der Datenbank ein EntityObjekt.
698 * @param rs Das ResultSetObjekt.
699 * @return Entity Die Entity.
701 private Entity makeEntityFromResultSet(ResultSet rs)
702 throws StorageObjectFailure {
703 /** @todo OS: get Pkey from ResultSet and consult ObjectStore */
704 Map theResultHash = new HashMap();
705 String theResult = null;
707 Entity returnEntity = null;
710 int size = metadataFields.size();
712 for (int i = 0; i < size; i++) {
713 // alle durchlaufen bis nix mehr da
714 theType = metadataTypes[i];
716 if (theType == java.sql.Types.LONGVARBINARY) {
717 InputStreamReader is =
718 (InputStreamReader) rs.getCharacterStream(i + 1);
721 char[] data = new char[32768];
722 StringBuffer theResultString = new StringBuffer();
725 while ((len = is.read(data)) > 0) {
726 theResultString.append(data, 0, len);
730 theResult = theResultString.toString();
735 theResult = getValueAsString(rs, (i + 1), theType);
738 if (theResult != null) {
739 theResultHash.put(metadataFields.get(i), theResult);
743 if (theEntityClass != null) {
744 returnEntity = (Entity) theEntityClass.newInstance();
745 returnEntity.setStorage(this);
746 returnEntity.setValues(theResultHash);
748 if (returnEntity instanceof StorableObject) {
749 logger.debug("CACHE: ( in) " + returnEntity.getId() + " :" + theTable);
750 o_store.add(((StorableObject) returnEntity).getStoreIdentifier());
753 throwStorageObjectException("Internal Error: theEntityClass not set!");
756 catch (IllegalAccessException e) {
757 throwStorageObjectException("No access! -- " + e.getMessage());
759 catch (IOException e) {
760 throwStorageObjectException("IOException! -- " + e.getMessage());
762 catch (InstantiationException e) {
763 throwStorageObjectException("No Instatiation! -- " + e.getMessage());
765 catch (SQLException sqe) {
766 throwSQLException(sqe, "makeEntityFromResultSet");
775 * Inserts an entity into the database.
778 * @return der Wert des Primary-keys der eingef?gten Entity
780 public String insert(Entity theEntity) throws StorageObjectFailure {
782 invalidatePopupCache();
784 // invalidating all EntityLists corresponding with theEntityClass
785 if (StoreUtil.implementsStorableObject(theEntityClass)) {
786 StoreContainerType stoc_type =
787 StoreContainerType.valueOf(theEntityClass,
788 StoreContainerType.STOC_TYPE_ENTITYLIST);
789 o_store.invalidate(stoc_type);
792 String returnId = null;
793 Connection con = null;
794 PreparedStatement pstmt = null;
797 List streamedInput = theEntity.streamedInput();
798 StringBuffer f = new StringBuffer();
799 StringBuffer v = new StringBuffer();
802 boolean firstField = true;
805 for (int i = 0; i < getFields().size(); i++) {
806 aField = (String) getFields().get(i);
808 if (!aField.equals(thePKeyName)) {
812 if (!theEntity.hasValueForField(aField) && (
813 aField.equals("webdb_create") ||
814 aField.equals("webdb_lastchange"))) {
818 if ((streamedInput != null) && streamedInput.contains(aField)) {
822 if (theEntity.hasValueForField(aField)) {
825 JDBCStringRoutines.escapeStringLiteral((String) theEntity.getValue(aField)) + "'";
830 // wenn Wert gegeben, dann einbauen
831 if (aValue != null) {
832 if (firstField == false) {
848 StringBuffer sqlBuf =
849 new StringBuffer("insert into ").append(theTable).append("(").append(f)
850 .append(") values (").append(v).append(")");
851 String sql = sqlBuf.toString();
853 logger.debug("INSERT: " + sql);
854 con = getPooledCon();
855 con.setAutoCommit(false);
856 pstmt = con.prepareStatement(sql);
858 if (streamedInput != null) {
859 for (int i = 0; i < streamedInput.size(); i++) {
861 (String) theEntity.getValue((String) streamedInput.get(i));
862 pstmt.setBytes(i + 1, inputString.getBytes());
866 int ret = pstmt.executeUpdate();
873 pstmt = con.prepareStatement(theAdaptor.getLastInsertSQL(this));
875 ResultSet rs = pstmt.executeQuery();
877 returnId = rs.getString(1);
878 theEntity.setId(returnId);
880 catch (SQLException sqe) {
881 throwSQLException(sqe, "insert");
885 con.setAutoCommit(true);
887 catch (Exception e) {
890 freeConnection(con, pstmt);
893 /** @todo store entity in o_store */
898 * Updates an entity in the database
902 public void update(Entity theEntity) throws StorageObjectFailure {
903 Connection con = null;
904 PreparedStatement pstmt = null;
906 /** @todo this is stupid: why do we prepare statement, when we
907 * throw it away afterwards. should be regular statement
908 * update/insert could better be one routine called save()
909 * that chooses to either insert or update depending if we
910 * have a primary key in the entity. i don't know if we
911 * still need the streamed input fields. // rk */
912 /** @todo extension: check if Entity did change, otherwise we don't need
913 * the roundtrip to the database */
914 /** invalidating corresponding entitylists in o_store*/
915 if (StoreUtil.implementsStorableObject(theEntityClass)) {
916 StoreContainerType stoc_type =
917 StoreContainerType.valueOf(theEntityClass,
918 StoreContainerType.STOC_TYPE_ENTITYLIST);
919 o_store.invalidate(stoc_type);
922 List streamedInput = theEntity.streamedInput();
923 String id = theEntity.getId();
925 StringBuffer fv = new StringBuffer();
926 boolean firstField = true;
929 invalidatePopupCache();
931 // build sql statement
932 for (int i = 0; i < getFields().size(); i++) {
933 aField = (String) metadataFields.get(i);
936 if ( !(aField.equals(thePKeyName) ||
937 aField.equals("webdb_create") ||
938 aField.equals("webdb_lastchange") ||
939 ((streamedInput != null) && streamedInput.contains(aField)))) {
940 if (theEntity.hasValueForField(aField)) {
941 if (firstField == false) {
948 fv.append(aField).append("='").append(JDBCStringRoutines.escapeStringLiteral((String) theEntity.getValue(aField))).append("'");
950 // fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getValue(aField))).append("'");
956 new StringBuffer("update ").append(theTable).append(" set ").append(fv);
959 if (metadataFields.contains("webdb_lastchange")) {
960 sql.append(",webdb_lastchange=NOW()");
963 // special case: the webdb_create requires the field in yyyy-mm-dd HH:mm
964 // format so anything extra will be ignored. -mh
965 if (metadataFields.contains("webdb_create") &&
966 theEntity.hasValueForField("webdb_create")) {
967 // minimum of 10 (yyyy-mm-dd)...
968 if (theEntity.getValue("webdb_create").length() >= 10) {
969 String dateString = theEntity.getValue("webdb_create");
971 // if only 10, then add 00:00 so it doesn't throw a ParseException
972 if (dateString.length() == 10) {
973 dateString = dateString + " 00:00";
978 java.util.Date d = userInputDateFormat.parse(dateString);
979 // Timestamp tStamp = new Timestamp(d.getTime());
980 sql.append(",webdb_create='" + JDBCStringRoutines.formatDate(d) + "'");
982 catch (ParseException e) {
983 throw new StorageObjectFailure(e);
988 if (streamedInput != null) {
989 for (int i = 0; i < streamedInput.size(); i++) {
990 sql.append(",").append(streamedInput.get(i)).append("=?");
994 sql.append(" where id=").append(id);
995 logger.debug("UPDATE: " + sql);
998 con = getPooledCon();
999 con.setAutoCommit(false);
1000 pstmt = con.prepareStatement(sql.toString());
1002 if (streamedInput != null) {
1003 for (int i = 0; i < streamedInput.size(); i++) {
1004 String inputString =
1005 theEntity.getValue((String) streamedInput.get(i));
1006 pstmt.setBytes(i + 1, inputString.getBytes());
1010 pstmt.executeUpdate();
1012 catch (SQLException sqe) {
1013 throwSQLException(sqe, "update");
1017 con.setAutoCommit(true);
1019 catch (Exception e) {
1023 freeConnection(con, pstmt);
1029 * @param id des zu loeschenden Datensatzes
1030 * @return boolean liefert true zurueck, wenn loeschen erfolgreich war.
1032 public boolean delete(String id) throws StorageObjectFailure {
1033 invalidatePopupCache();
1035 // ostore send notification
1036 if (StoreUtil.implementsStorableObject(theEntityClass)) {
1037 String uniqueId = id;
1039 if (theEntityClass.equals(StorableObjectEntity.class)) {
1040 uniqueId += ("@" + theTable);
1043 logger.debug("CACHE: (del) " + id);
1045 StoreIdentifier search_sid =
1046 new StoreIdentifier(theEntityClass,
1047 StoreContainerType.STOC_TYPE_ENTITY, uniqueId);
1048 o_store.invalidate(search_sid);
1051 /** @todo could be prepared Statement */
1052 Statement stmt = null;
1053 Connection con = null;
1056 "delete from " + theTable + " where " + thePKeyName + "='" + id + "'";
1058 //theLog.printInfo("DELETE " + sql);
1060 con = getPooledCon();
1061 stmt = con.createStatement();
1062 res = stmt.executeUpdate(sql);
1063 } catch (SQLException sqe) {
1064 throwSQLException(sqe, "delete");
1066 freeConnection(con, stmt);
1069 return (res > 0) ? true : false;
1073 * Deletes entities based on a where clause
1075 * @param aWhereClause
1077 * @throws StorageObjectFailure
1079 public int deleteByWhereClause(String aWhereClause) throws StorageObjectFailure {
1080 invalidatePopupCache();
1081 if (StoreUtil.implementsStorableObject(theEntityClass)) {
1082 StoreContainerType stoc_type = StoreContainerType.valueOf(theEntityClass, StoreContainerType.STOC_TYPE_ENTITYLIST);
1083 o_store.invalidate(stoc_type);
1086 Statement stmt = null;
1087 Connection con = null;
1090 "delete from " + theTable + " where " + aWhereClause;
1092 //theLog.printInfo("DELETE " + sql);
1094 con = getPooledCon();
1095 stmt = con.createStatement();
1096 res = stmt.executeUpdate(sql);
1098 catch (SQLException sqe) {
1099 throwSQLException(sqe, "delete");
1102 freeConnection(con, stmt);
1108 /* noch nicht implementiert.
1109 * @return immer false
1111 public boolean delete(EntityList theEntityList) {
1112 invalidatePopupCache();
1118 * Diese Methode sollte ueberschrieben werden, wenn fuer die abgeleitete Database-Klasse
1119 * eine SimpleList mit Standard-Popupdaten erzeugt werden koennen soll.
1122 public SimpleList getPopupData() throws StorageObjectFailure {
1127 * Holt Daten fuer Popups.
1128 * @param name Name des Feldes.
1129 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
1130 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
1132 public SimpleList getPopupData(String name, boolean hasNullValue)
1133 throws StorageObjectFailure {
1134 return getPopupData(name, hasNullValue, null);
1138 * Holt Daten fuer Popups.
1139 * @param name Name des Feldes.
1140 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
1141 * @param where Schraenkt die Selektion der Datensaetze ein.
1142 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
1144 public SimpleList getPopupData(String name, boolean hasNullValue, String where)
1145 throws StorageObjectFailure {
1146 return getPopupData(name, hasNullValue, where, null);
1150 * Holt Daten fuer Popups.
1151 * @param name Name des Feldes.
1152 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
1153 * @param where Schraenkt die Selektion der Datensaetze ein.
1154 * @param order Gibt ein Feld als Sortierkriterium an.
1155 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
1157 public SimpleList getPopupData(String name, boolean hasNullValue,
1158 String where, String order) throws StorageObjectFailure {
1160 if (hasPopupCache && (popupCache != null)) {
1164 SimpleList simpleList = null;
1165 Connection con = null;
1166 Statement stmt = null;
1170 new StringBuffer("select ").append(thePKeyName).append(",").append(name)
1171 .append(" from ").append(theTable);
1173 if ((where != null) && !(where.length() == 0)) {
1174 sql.append(" where ").append(where);
1177 sql.append(" order by ");
1179 if ((order != null) && !(order.length() == 0)) {
1187 con = getPooledCon();
1188 } catch (Exception e) {
1189 throw new StorageObjectFailure(e);
1193 stmt = con.createStatement();
1195 ResultSet rs = executeSql(stmt, sql.toString());
1198 if (!evaluatedMetaData) {
1202 simpleList = new SimpleList();
1204 // if popup has null-selector
1206 simpleList.add(POPUP_EMPTYLINE);
1209 SimpleHash popupDict;
1212 popupDict = new SimpleHash();
1213 popupDict.put("key", getValueAsString(rs, 1, thePKeyType));
1214 popupDict.put("value", rs.getString(2));
1215 simpleList.add(popupDict);
1221 catch (Exception e) {
1222 logger.error("getPopupData: " + e.getMessage());
1223 throw new StorageObjectFailure(e);
1225 freeConnection(con, stmt);
1228 if (hasPopupCache) {
1229 popupCache = simpleList;
1236 * Liefert alle Daten der Tabelle als SimpleHash zurueck. Dies wird verwandt,
1237 * wenn in den Templates ein Lookup-Table benoetigt wird. Sollte nur bei kleinen
1238 * Tabellen Verwendung finden.
1239 * @return SimpleHash mit den Tabellezeilen.
1241 public SimpleHash getHashData() {
1242 /** @todo dangerous! this should have a flag to be enabled, otherwise
1243 * very big Hashes could be returned */
1244 if (hashCache == null) {
1247 HTMLTemplateProcessor.makeSimpleHash(selectByWhereClause("", -1));
1249 catch (StorageObjectFailure e) {
1250 logger.debug(e.getMessage());
1257 /* invalidates the popupCache
1259 protected void invalidatePopupCache() {
1260 /** @todo invalidates toooo much */
1266 * Diese Methode fuehrt den Sqlstring <i>sql</i> aus und timed im Logfile.
1267 * @param stmt Statemnt
1268 * @param sql Sql-String
1270 * @exception StorageObjectException
1272 public ResultSet executeSql(Statement stmt, String sql)
1273 throws StorageObjectFailure, SQLException {
1275 long startTime = System.currentTimeMillis();
1278 rs = stmt.executeQuery(sql);
1280 logger.debug((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1282 catch (SQLException e) {
1283 logger.error(e.getMessage() +"\n" + (System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1290 public ResultSet executeSql(String sql) throws StorageObjectFailure, SQLException {
1291 long startTime = System.currentTimeMillis();
1292 Connection connection = null;
1293 Statement statement = null;
1296 connection = getPooledCon();
1297 statement = connection.createStatement();
1300 result = statement.executeQuery(sql);
1302 logger.debug((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1305 catch (Throwable e) {
1306 logger.error(e.getMessage() +"\n" + (System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1307 throw new StorageObjectFailure(e);
1310 if (connection!=null) {
1311 freeConnection(connection, statement);
1316 private Map processRow(ResultSet aResultSet) throws StorageObjectFailure, StorageObjectExc {
1318 Map result = new HashMap();
1319 ResultSetMetaData metaData = aResultSet.getMetaData();
1320 int nrColumns = metaData.getColumnCount();
1321 for (int i=0; i<nrColumns; i++) {
1322 result.put(metaData.getColumnName(i+1), getValueAsString(aResultSet, i+1, metaData.getColumnType(i+1)));
1327 catch (Throwable e) {
1328 throw new StorageObjectFailure(e);
1332 public List executeFreeSql(String sql, int aLimit) throws StorageObjectFailure, StorageObjectExc {
1333 Connection connection = null;
1334 Statement statement = null;
1336 List result = new Vector();
1337 connection = getPooledCon();
1338 statement = connection.createStatement();
1339 ResultSet resultset = executeSql(statement, sql);
1341 while (resultset.next() && result.size() < aLimit) {
1342 result.add(processRow(resultset));
1351 catch (Throwable e) {
1352 throw new StorageObjectFailure(e);
1355 if (connection!=null) {
1356 freeConnection(connection, statement);
1361 public Map executeFreeSingleRowSql(String anSqlStatement) throws StorageObjectFailure, StorageObjectExc {
1363 List resultList = executeFreeSql(anSqlStatement, 1);
1365 if (resultList.size()>0)
1366 return (Map) resultList.get(0);
1373 catch (Throwable t) {
1374 throw new StorageObjectFailure(t);
1378 public String executeFreeSingleValueSql(String sql) throws StorageObjectFailure, StorageObjectExc {
1379 Map row = executeFreeSingleRowSql(sql);
1384 Iterator i = row.values().iterator();
1386 return (String) i.next();
1392 * returns the number of rows in the table
1394 public int getSize(String where) throws SQLException, StorageObjectFailure {
1395 long startTime = System.currentTimeMillis();
1396 String sql = "SELECT Count(*) FROM " + theTable;
1398 if ((where != null) && (where.length() != 0)) {
1399 sql = sql + " where " + where;
1402 Connection con = null;
1403 Statement stmt = null;
1407 con = getPooledCon();
1408 stmt = con.createStatement();
1410 ResultSet rs = executeSql(stmt, sql);
1413 result = rs.getInt(1);
1416 catch (SQLException e) {
1417 logger.error("Database.getSize: " + e.getMessage());
1420 freeConnection(con, stmt);
1423 //theLog.printInfo(theTable + " has "+ result +" rows where " + where);
1424 logger.debug((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1429 public int executeUpdate(Statement stmt, String sql)
1430 throws StorageObjectFailure, SQLException {
1432 long startTime = System.currentTimeMillis();
1435 rs = stmt.executeUpdate(sql);
1437 logger.debug((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1439 catch (SQLException e) {
1440 logger.error("Failed: " + (System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1447 public int executeUpdate(String sql)
1448 throws StorageObjectFailure, SQLException {
1450 long startTime = System.currentTimeMillis();
1451 Connection con = null;
1452 PreparedStatement pstmt = null;
1455 con = getPooledCon();
1456 pstmt = con.prepareStatement(sql);
1457 result = pstmt.executeUpdate();
1459 catch (Throwable e) {
1460 logger.error("Database.executeUpdate(" + sql + "): " + e.getMessage());
1461 throw new StorageObjectFailure("Database.executeUpdate(" + sql + "): " + e.getMessage(), e);
1464 freeConnection(con, pstmt);
1467 logger.debug((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1472 * Wertet ResultSetMetaData aus und setzt interne Daten entsprechend
1473 * @param md ResultSetMetaData
1474 * @exception StorageObjectException
1476 private void evalMetaData(ResultSetMetaData md) throws StorageObjectFailure {
1477 this.evaluatedMetaData = true;
1478 this.metadataFields = new ArrayList();
1479 this.metadataLabels = new ArrayList();
1480 this.metadataNotNullFields = new ArrayList();
1483 int numFields = md.getColumnCount();
1484 this.metadataTypes = new int[numFields];
1489 for (int i = 1; i <= numFields; i++) {
1490 aField = md.getColumnName(i);
1491 metadataFields.add(aField);
1492 metadataLabels.add(md.getColumnLabel(i));
1493 aType = md.getColumnType(i);
1494 metadataTypes[i - 1] = aType;
1496 if (aField.equals(thePKeyName)) {
1497 thePKeyType = aType;
1501 if (md.isNullable(i) == ResultSetMetaData.columnNullable) {
1502 metadataNotNullFields.add(aField);
1506 catch (SQLException e) {
1507 throwSQLException(e, "evalMetaData");
1512 * Wertet die Metadaten eines Resultsets fuer eine Tabelle aus,
1513 * um die alle Columns und Typen einer Tabelle zu ermitteln.
1515 private void get_meta_data() throws StorageObjectFailure {
1516 Connection con = null;
1517 PreparedStatement pstmt = null;
1518 String sql = "select * from " + theTable + " where 0=1";
1521 con = getPooledCon();
1522 pstmt = con.prepareStatement(sql);
1524 logger.debug("METADATA: " + sql);
1525 ResultSet rs = pstmt.executeQuery();
1526 evalMetaData(rs.getMetaData());
1529 catch (SQLException e) {
1530 throwSQLException(e, "get_meta_data");
1533 freeConnection(con, pstmt);
1537 public Connection getPooledCon() throws StorageObjectFailure {
1538 Connection con = null;
1541 con = SQLManager.getInstance().requestConnection();
1543 catch (SQLException e) {
1544 logger.error("could not connect to the database " + e.getMessage());
1546 throw new StorageObjectFailure("Could not connect to the database", e);
1552 public void freeConnection(Connection con, Statement stmt)
1553 throws StorageObjectFailure {
1554 SQLManager.closeStatement(stmt);
1555 SQLManager.getInstance().returnConnection(con);
1559 * Wertet SQLException aus und wirft dannach eine StorageObjectException
1560 * @param sqe SQLException
1561 * @param wo Funktonsname, in der die SQLException geworfen wurde
1562 * @exception StorageObjectException
1564 protected void throwSQLException(SQLException sqe, String aFunction) throws StorageObjectFailure {
1566 String message = "";
1570 state = sqe.getSQLState();
1571 message = sqe.getMessage();
1572 vendor = sqe.getErrorCode();
1575 String information =
1578 ", vendor= " + vendor +
1579 ", message=" + message +
1580 ", function= " + aFunction;
1582 logger.error(information);
1584 throw new StorageObjectFailure(information, sqe);
1587 protected void _throwStorageObjectException(Exception e, String aFunction)
1588 throws StorageObjectFailure {
1591 logger.error(e.getMessage() + aFunction);
1592 throw new StorageObjectFailure(aFunction, e);
1597 * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach
1598 * eine StorageObjectException
1599 * @param message Nachricht mit dem Fehler
1600 * @exception StorageObjectException
1602 void throwStorageObjectException(String aMessage) throws StorageObjectFailure {
1603 logger.error(aMessage);
1604 throw new StorageObjectFailure(aMessage, null);