2 * put your module comment here
10 import java.text.SimpleDateFormat;
11 import java.text.ParseException;
12 import freemarker.template.*;
13 import com.codestudio.sql.*;
14 import com.codestudio.util.*;
16 import mir.storage.StorageObject;
17 import mir.storage.store.*;
23 * Diese Klasse implementiert die Zugriffsschicht auf die Datenbank.
24 * Alle Projektspezifischen Datenbankklassen erben von dieser Klasse.
25 * In den Unterklassen wird im Minimalfall nur die Tabelle angegeben.
26 * Im Konfigurationsfile findet sich eine Verweis auf den verwendeten
27 * Treiber, Host, User und Passwort, ueber den der Zugriff auf die
30 * @version $Revision: 1.21 $ $Date: 2002/08/04 23:38:22 $
31 * @author $Author: mh $
33 * $Log: Database.java,v $
34 * Revision 1.21 2002/08/04 23:38:22 mh
35 * fix up the webdb_create update stuff
37 * Revision 1.20 2002/07/21 22:32:25 mh
38 * on insert, the "webdb_lastchange" field should get a value
40 * Revision 1.19 2002/06/29 15:44:46 mh
41 * make the webdb_create update be called webdb_create_update. it breaks things otherwise. a fixme case I know..
43 * Revision 1.18 2002/06/28 20:42:13 mh
44 * added necessary bits in templates and Database.java to make webdb_create modifiable. make the conversion from sql/Timestamp to String more robust
48 public class Database implements StorageObject {
50 protected String theTable;
51 protected String theCoreTable=null;
52 protected String thePKeyName="id";
53 protected int thePKeyType, thePKeyIndex;
54 protected boolean evaluatedMetaData=false;
55 protected ArrayList metadataFields,metadataLabels,
56 metadataNotNullFields;
57 protected int[] metadataTypes;
58 protected Class theEntityClass;
59 protected StorageObject myselfDatabase;
60 protected SimpleList popupCache=null;
61 protected boolean hasPopupCache = false;
62 protected SimpleHash hashCache=null;
63 protected boolean hasTimestamp=true;
64 private String database_driver, database_url;
65 private int defaultLimit;
66 protected DatabaseAdaptor theAdaptor;
67 protected Logfile theLog;
68 private static Class GENERIC_ENTITY_CLASS=null,
69 STORABLE_OBJECT_ENTITY_CLASS=null;
70 private static SimpleHash POPUP_EMTYLINE=new SimpleHash();
71 protected static final ObjectStore o_store=ObjectStore.getInstance();
72 private SimpleDateFormat _dateFormatterOut =
73 new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
74 private SimpleDateFormat _dateFormatterIn =
75 new SimpleDateFormat("yyyy-MM-dd HH:mm");
76 private Calendar _cal = new GregorianCalendar();
78 private static final int _millisPerHour = 60 * 60 * 1000;
79 private static final int _millisPerMinute = 60 * 1000;
82 // always same object saves a little space
83 POPUP_EMTYLINE.put("key", ""); POPUP_EMTYLINE.put("value", "--");
85 GENERIC_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity");
86 STORABLE_OBJECT_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity");
89 System.err.println("FATAL: Database.java could not initialize" + e.toString());
95 * Kontruktor bekommt den Filenamen des Konfigurationsfiles übergeben.
96 * Aus diesem file werden <code>Database.Logfile</code>,
97 * <code>Database.Username</code>,<code>Database.Password</code>,
98 * <code>Database.Host</code> und <code>Database.Adaptor</code>
99 * ausgelesen und ein Broker für die Verbindugen zur Datenbank
102 * @param String confFilename Dateiname der Konfigurationsdatei
104 public Database() throws StorageObjectException {
105 theLog = Logfile.getInstance(MirConfig.getProp("Home")+
106 MirConfig.getProp("Database.Logfile"));
107 String theAdaptorName=MirConfig.getProp("Database.Adaptor");
108 defaultLimit = Integer.parseInt(MirConfig.getProp("Database.Limit"));
110 theEntityClass = GENERIC_ENTITY_CLASS;
111 theAdaptor = (DatabaseAdaptor)Class.forName(theAdaptorName).newInstance();
112 } catch (Exception e){
113 theLog.printError("Error in Database() constructor with "+
114 theAdaptorName + " -- " +e.toString());
115 throw new StorageObjectException("Error in Database() constructor with "
118 /*String database_username=MirConfig.getProp("Database.Username");
119 String database_password=MirConfig.getProp("Database.Password");
120 String database_host=MirConfig.getProp("Database.Host");
122 database_driver=theAdaptor.getDriver();
123 database_url=theAdaptor.getURL(database_username,database_password,
125 theLog.printDebugInfo("adding Broker with: " +database_driver+":"+
127 MirConfig.addBroker(database_driver,database_url);
128 //myBroker=MirConfig.getBroker();
133 * Liefert die Entity-Klasse zurück, in der eine Datenbankzeile gewrappt
134 * wird. Wird die Entity-Klasse durch die erbende Klasse nicht überschrieben,
135 * wird eine mir.entity.GenericEntity erzeugt.
137 * @return Class-Objekt der Entity
139 public java.lang.Class getEntityClass () {
140 return theEntityClass;
144 * Liefert die Standardbeschränkung von select-Statements zurück, also
145 * wieviel Datensätze per Default selektiert werden.
147 * @return Standard-Anzahl der Datensätze
149 public int getLimit () {
154 * Liefert den Namen des Primary-Keys zurück. Wird die Variable nicht von
155 * der erbenden Klasse überschrieben, so ist der Wert <code>PKEY</code>
156 * @return Name des Primary-Keys
158 public String getIdName () {
163 * Liefert den Namen der Tabelle, auf das sich das Datenbankobjekt bezieht.
165 * @return Name der Tabelle
167 public String getTableName () {
172 * Dient dazu vererbte Tabellen bei objectrelationalen DBMS
173 * zu speichern, wenn die id einer Tabelle in der parenttabelle verwaltet
175 * @return liefert theCoreTabel als String zurueck, wenn gesetzt, sonst
179 public String getCoreTable(){
180 if (theCoreTable!=null) return theCoreTable;
181 else return theTable;
185 * Liefert Feldtypen der Felder der Tabelle zurueck (s.a. java.sql.Types)
186 * @return int-Array mit den Typen der Felder
187 * @exception StorageObjectException
189 public int[] getTypes () throws StorageObjectException {
190 if (metadataTypes == null)
192 return metadataTypes;
196 * Liefert eine Liste der Labels der Tabellenfelder
197 * @return ArrayListe mit Labeln
198 * @exception StorageObjectException
200 public ArrayList getLabels () throws StorageObjectException {
201 if (metadataLabels == null)
203 return metadataLabels;
207 * Liefert eine Liste der Felder der Tabelle
208 * @return ArrayList mit Feldern
209 * @exception StorageObjectException
211 public ArrayList getFields () throws StorageObjectException {
212 if (metadataFields == null)
214 return metadataFields;
219 * Gets value out of ResultSet according to type and converts to String
220 * @param inValue Wert aus ResultSet.
221 * @param aType Datenbanktyp.
222 * @return liefert den Wert als String zurueck. Wenn keine Umwandlung moeglich
223 * dann /unsupported value/
225 private String getValueAsString (ResultSet rs, int valueIndex, int aType) throws StorageObjectException {
226 String outValue = null;
230 case java.sql.Types.BIT:
231 outValue = (rs.getBoolean(valueIndex) == true) ? "1" : "0";
233 case java.sql.Types.INTEGER:case java.sql.Types.SMALLINT:case java.sql.Types.TINYINT:case java.sql.Types.BIGINT:
234 int out = rs.getInt(valueIndex);
236 outValue = new Integer(out).toString();
238 case java.sql.Types.NUMERIC:
239 /** @todo Numeric can be float or double depending upon
240 * metadata.getScale() / especially with oracle */
241 long outl = rs.getLong(valueIndex);
243 outValue = new Long(outl).toString();
245 case java.sql.Types.REAL:
246 float tempf = rs.getFloat(valueIndex);
250 int tempf_int = (int)tempf;
251 tempf = (float)tempf_int;
253 outValue = "" + tempf;
254 outValue = outValue.replace('.', ',');
257 case java.sql.Types.DOUBLE:
258 double tempd = rs.getDouble(valueIndex);
262 int tempd_int = (int)tempd;
263 tempd = (double)tempd_int;
265 outValue = "" + tempd;
266 outValue = outValue.replace('.', ',');
269 case java.sql.Types.CHAR:case java.sql.Types.VARCHAR:case java.sql.Types.LONGVARCHAR:
270 outValue = rs.getString(valueIndex);
271 //if (outValue != null)
272 //outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue));
274 case java.sql.Types.LONGVARBINARY:
275 outValue = rs.getString(valueIndex);
276 //if (outValue != null)
277 //outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue));
279 case java.sql.Types.TIMESTAMP:
280 // it's important to use Timestamp here as getting it
281 // as a string is undefined and is only there for debugging
282 // according to the API. we can make it a string through formatting.
284 Timestamp timestamp = (rs.getTimestamp(valueIndex));
286 java.util.Date date = new java.util.Date(timestamp.getTime());
287 outValue = _dateFormatterOut.format(date);
289 int offset = _cal.get(Calendar.ZONE_OFFSET)+
290 _cal.get(Calendar.DST_OFFSET);
291 String tzOffset = StringUtil.zeroPaddingNumber(
292 offset/_millisPerHour,2,2);
293 outValue = outValue+"+"+tzOffset;
297 outValue = "<unsupported value>";
298 theLog.printWarning("Unsupported Datatype: at " + valueIndex +
301 } catch (SQLException e) {
302 throw new StorageObjectException("Could not get Value out of Resultset -- "
310 * select-Operator um einen Datensatz zu bekommen.
311 * @param id Primaerschluessel des Datensatzes.
312 * @return liefert EntityObject des gefundenen Datensatzes oder null.
314 public Entity selectById(String id) throws StorageObjectException
316 if (id==null||id.equals(""))
317 throw new StorageObjectException("id war null");
319 // ask object store for object
320 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
321 String uniqueId = id;
322 if ( theEntityClass.equals(StorableObjectEntity.class) )
323 uniqueId+="@"+theTable;
324 StoreIdentifier search_sid = new StoreIdentifier(theEntityClass, uniqueId);
325 theLog.printDebugInfo("CACHE: (dbg) looking for sid " + search_sid.toString());
326 Entity hit = (Entity)o_store.use(search_sid);
327 if ( hit!=null ) return hit;
330 Statement stmt=null;Connection con=getPooledCon();
331 Entity returnEntity=null;
334 /** @todo better prepared statement */
335 String selectSql = "select * from " + theTable + " where " + thePKeyName + "=" + id;
336 stmt = con.createStatement();
337 rs = executeSql(stmt, selectSql);
339 if (evaluatedMetaData==false) evalMetaData(rs.getMetaData());
341 returnEntity = makeEntityFromResultSet(rs);
342 else theLog.printDebugInfo("Keine daten fuer id: " + id + "in Tabelle" + theTable);
346 theLog.printDebugInfo("No Data for Id " + id + " in Table " + theTable);
349 catch (SQLException sqe){
350 throwSQLException(sqe,"selectById"); return null;
352 catch (NumberFormatException e) {
353 theLog.printError("ID ist keine Zahl: " + id);
355 finally { freeConnection(con,stmt); }
357 /** @todo OS: Entity should be saved in ostore */
363 * select-Operator um Datensaetze zu bekommen, die key = value erfuellen.
364 * @param key Datenbankfeld der Bedingung.
365 * @param value Wert die der key anehmen muss.
366 * @return EntityList mit den gematchten Entities
368 public EntityList selectByFieldValue(String aField, String aValue)
369 throws StorageObjectException
371 return selectByFieldValue(aField, aValue, 0);
375 * select-Operator um Datensaetze zu bekommen, die key = value erfuellen.
376 * @param key Datenbankfeld der Bedingung.
377 * @param value Wert die der key anehmen muss.
378 * @param offset Gibt an ab welchem Datensatz angezeigt werden soll.
379 * @return EntityList mit den gematchten Entities
381 public EntityList selectByFieldValue(String aField, String aValue, int offset)
382 throws StorageObjectException
384 return selectByWhereClause(aField + "=" + aValue, offset);
389 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
390 * Also offset wird der erste Datensatz genommen.
392 * @param wc where-Clause
393 * @return EntityList mit den gematchten Entities
394 * @exception StorageObjectException
396 public EntityList selectByWhereClause(String where)
397 throws StorageObjectException
399 return selectByWhereClause(where, 0);
404 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
405 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
407 * @param wc where-Clause
408 * @param offset ab welchem Datensatz.
409 * @return EntityList mit den gematchten Entities
410 * @exception StorageObjectException
412 public EntityList selectByWhereClause(String whereClause, int offset)
413 throws StorageObjectException
415 return selectByWhereClause(whereClause, null, offset);
420 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
421 * Also offset wird der erste Datensatz genommen.
422 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
424 * @param wc where-Clause
425 * @param ob orderBy-Clause
426 * @return EntityList mit den gematchten Entities
427 * @exception StorageObjectException
430 public EntityList selectByWhereClause(String where, String order)
431 throws StorageObjectException {
432 return selectByWhereClause(where, order, 0);
437 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
438 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
440 * @param wc where-Clause
441 * @param ob orderBy-Clause
442 * @param offset ab welchem Datensatz
443 * @return EntityList mit den gematchten Entities
444 * @exception StorageObjectException
447 public EntityList selectByWhereClause(String whereClause, String orderBy, int offset)
448 throws StorageObjectException {
449 return selectByWhereClause(whereClause, orderBy, offset, defaultLimit);
454 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
455 * @param wc where-Clause
456 * @param ob orderBy-Clause
457 * @param offset ab welchem Datensatz
458 * @param limit wieviele Datensätze
459 * @return EntityList mit den gematchten Entities
460 * @exception StorageObjectException
463 public EntityList selectByWhereClause(String wc, String ob, int offset, int limit)
464 throws StorageObjectException
467 // check o_store for entitylist
468 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
469 StoreIdentifier search_sid =
470 new StoreIdentifier( theEntityClass,
471 StoreContainerType.STOC_TYPE_ENTITYLIST,
472 StoreUtil.getEntityListUniqueIdentifierFor(theTable,wc,ob,offset,limit) );
473 EntityList hit = (EntityList)o_store.use(search_sid);
475 theLog.printDebugInfo("CACHE (hit): " + search_sid.toString());
481 EntityList theReturnList=null;
482 Connection con=null; Statement stmt=null;
484 int offsetCount = 0, count=0;
486 // build sql-statement
488 /** @todo count sql string should only be assembled if we really count
489 * see below at the end of method //rk */
491 if (wc != null && wc.length() == 0) {
494 StringBuffer countSql = new StringBuffer("select count(*) from ").append(theTable);
495 StringBuffer selectSql = new StringBuffer("select * from ").append(theTable);
497 selectSql.append(" where ").append(wc);
498 countSql.append(" where ").append(wc);
500 if (ob != null && !(ob.length() == 0)) {
501 selectSql.append(" order by ").append(ob);
503 if (theAdaptor.hasLimit()) {
504 if (limit > -1 && offset > -1) {
505 selectSql.append(" limit ");
506 if (theAdaptor.reverseLimit()) {
507 selectSql.append(limit).append(",").append(offset);
510 selectSql.append(offset).append(",").append(limit);
517 con = getPooledCon();
518 stmt = con.createStatement();
521 rs = executeSql(stmt, selectSql.toString());
523 if (!evaluatedMetaData) evalMetaData(rs.getMetaData());
525 theReturnList = new EntityList();
526 Entity theResultEntity;
528 theResultEntity = makeEntityFromResultSet(rs);
529 theReturnList.add(theResultEntity);
535 // making entitylist infos
536 if (!(theAdaptor.hasLimit())) count = offsetCount;
538 if (theReturnList != null) {
539 // now we decide if we have to know an overall count...
541 if (limit > -1 && offset > -1) {
542 if (offsetCount==limit) {
543 /** @todo counting should be deffered to entitylist
544 * getSize() should be used */
545 rs = executeSql(stmt, countSql.toString());
547 if ( rs.next() ) count = rs.getInt(1);
550 else theLog.printError("Could not count: " + countSql);
553 theReturnList.setCount(count);
554 theReturnList.setOffset(offset);
555 theReturnList.setWhere(wc);
556 theReturnList.setOrder(ob);
557 theReturnList.setStorage(this);
558 theReturnList.setLimit(limit);
559 if ( offset >= limit )
560 theReturnList.setPrevBatch(offset - limit);
561 if ( offset+offsetCount < count )
562 theReturnList.setNextBatch(offset + limit);
563 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
564 StoreIdentifier sid=theReturnList.getStoreIdentifier();
565 theLog.printDebugInfo("CACHE (add): " + sid.toString());
570 catch (SQLException sqe) { throwSQLException(sqe, "selectByWhereClause"); }
571 finally { freeConnection(con, stmt); }
573 return theReturnList;
578 * Bastelt aus einer Zeile der Datenbank ein EntityObjekt.
580 * @param rs Das ResultSetObjekt.
581 * @return Entity Die Entity.
583 private Entity makeEntityFromResultSet (ResultSet rs)
584 throws StorageObjectException
586 /** @todo OS: get Pkey from ResultSet and consult ObjectStore */
587 HashMap theResultHash = new HashMap();
588 String theResult = null;
590 Entity returnEntity = null;
592 int size = metadataFields.size();
593 for (int i = 0; i < size; i++) {
594 // alle durchlaufen bis nix mehr da
595 theType = metadataTypes[i];
596 if (theType == java.sql.Types.LONGVARBINARY) {
597 InputStreamReader is = (InputStreamReader)rs.getCharacterStream(i + 1);
599 char[] data = new char[32768];
600 StringBuffer theResultString = new StringBuffer();
602 while ((len = is.read(data)) > 0) {
603 theResultString.append(data, 0, len);
606 theResult = theResultString.toString();
613 theResult = getValueAsString(rs, (i + 1), theType);
615 if (theResult != null) {
616 theResultHash.put(metadataFields.get(i), theResult);
619 if (theEntityClass != null) {
620 returnEntity = (Entity)theEntityClass.newInstance();
621 returnEntity.setValues(theResultHash);
622 returnEntity.setStorage(myselfDatabase);
623 if ( returnEntity instanceof StorableObject ) {
624 theLog.printDebugInfo("CACHE: ( in) " + returnEntity.getId() + " :"+theTable);
625 o_store.add(((StorableObject)returnEntity).getStoreIdentifier());
628 throwStorageObjectException("Internal Error: theEntityClass not set!");
630 } catch (IllegalAccessException e) {
631 throwStorageObjectException("Kein Zugriff! -- " + e.toString());
632 } catch (IOException e) {
633 throwStorageObjectException("IOException! -- " + e.toString());
634 } catch (InstantiationException e) {
635 throwStorageObjectException("Keine Instantiiierung! -- " + e.toString());
636 } catch (SQLException sqe) {
637 throwSQLException(sqe, "makeEntityFromResultSet");
644 * insert-Operator: fügt eine Entity in die Tabelle ein. Eine Spalte WEBDB_CREATE
645 * wird automatisch mit dem aktuellen Datum gefuellt.
648 * @return der Wert des Primary-keys der eingefügten Entity
650 public String insert (Entity theEntity) throws StorageObjectException {
652 invalidatePopupCache();
654 // invalidating all EntityLists corresponding with theEntityClass
655 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
656 StoreContainerType stoc_type =
657 StoreContainerType.valueOf( theEntityClass,
658 StoreContainerType.STOC_TYPE_ENTITYLIST);
659 o_store.invalidate(stoc_type);
662 String returnId = null;
663 Connection con = null; PreparedStatement pstmt = null;
666 ArrayList streamedInput = theEntity.streamedInput();
667 StringBuffer f = new StringBuffer();
668 StringBuffer v = new StringBuffer();
669 String aField, aValue;
670 boolean firstField = true;
672 for (int i = 0; i < getFields().size(); i++) {
673 aField = (String)getFields().get(i);
674 if (!aField.equals(thePKeyName)) {
677 if (aField.equals("webdb_create") ||
678 aField.equals("webdb_lastchange")) {
682 if (streamedInput != null && streamedInput.contains(aField)) {
686 if (theEntity.hasValueForField(aField)) {
687 aValue = "'" + StringUtil.quote((String)theEntity.getValue(aField))
692 // wenn Wert gegeben, dann einbauen
693 if (aValue != null) {
694 if (firstField == false) {
707 StringBuffer sqlBuf = new StringBuffer("insert into ").append(theTable).append("(").append(f).append(") values (").append(v).append(")");
708 String sql = sqlBuf.toString();
709 theLog.printInfo("INSERT: " + sql);
710 con = getPooledCon();
711 con.setAutoCommit(false);
712 pstmt = con.prepareStatement(sql);
713 if (streamedInput != null) {
714 for (int i = 0; i < streamedInput.size(); i++) {
715 String inputString = (String)theEntity.getValue((String)streamedInput.get(i));
716 pstmt.setBytes(i + 1, inputString.getBytes());
719 int ret = pstmt.executeUpdate();
724 pstmt = con.prepareStatement(theAdaptor.getLastInsertSQL((Database)myselfDatabase));
725 ResultSet rs = pstmt.executeQuery();
727 returnId = rs.getString(1);
728 theEntity.setId(returnId);
729 } catch (SQLException sqe) {
730 throwSQLException(sqe, "insert");
733 con.setAutoCommit(true);
734 } catch (Exception e) {
737 freeConnection(con, pstmt);
739 /** @todo store entity in o_store */
744 * update-Operator: aktualisiert eine Entity. Eine Spalte WEBDB_LASTCHANGE
745 * wird automatisch mit dem aktuellen Datum gefuellt.
749 public void update (Entity theEntity) throws StorageObjectException
751 Connection con = null; PreparedStatement pstmt = null;
752 /** @todo this is stupid: why do we prepare statement, when we
753 * throw it away afterwards. should be regular statement
754 * update/insert could better be one routine called save()
755 * that chooses to either insert or update depending if we
756 * have a primary key in the entity. i don't know if we
757 * still need the streamed input fields. // rk */
759 /** @todo extension: check if Entity did change, otherwise we don't need
760 * the roundtrip to the database */
762 /** invalidating corresponding entitylists in o_store*/
763 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
764 StoreContainerType stoc_type =
765 StoreContainerType.valueOf( theEntityClass,
766 StoreContainerType.STOC_TYPE_ENTITYLIST);
767 o_store.invalidate(stoc_type);
770 ArrayList streamedInput = theEntity.streamedInput();
771 String id = theEntity.getId();
773 StringBuffer fv = new StringBuffer();
774 boolean firstField = true;
776 invalidatePopupCache();
777 // build sql statement
778 for (int i = 0; i < getFields().size(); i++) {
779 aField = (String)metadataFields.get(i);
781 if (!(aField.equals(thePKeyName) || aField.equals("webdb_create") ||
782 aField.equals("webdb_lastchange") || (streamedInput != null && streamedInput.contains(aField)))) {
783 if (theEntity.hasValueForField(aField)) {
784 if (firstField == false) {
790 fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getValue(aField))).append("'");
794 StringBuffer sql = new StringBuffer("update ").append(theTable).append(" set ").append(fv);
796 if (metadataFields.contains("webdb_lastchange")) {
797 sql.append(",webdb_lastchange=NOW()");
799 // special case: the webdb_create requires the field in yyyy-mm-dd HH:mm
800 // format so anything extra will be ignored. -mh
801 if (metadataFields.contains("webdb_create") &&
802 theEntity.hasValueForField("webdb_create")) {
803 // minimum of 10 (yyyy-mm-dd)...
804 if (theEntity.getValue("webdb_create").length() >= 10) {
805 String dateString = theEntity.getValue("webdb_create");
806 // if only 10, then add 00:00 so it doesn't throw a ParseException
807 if (dateString.length() == 10)
808 dateString=dateString+" 00:00";
812 java.util.Date d = _dateFormatterIn.parse(dateString);
813 Timestamp tStamp = new Timestamp(d.getTime());
814 sql.append(",webdb_create='"+tStamp.toString()+"'");
815 } catch (ParseException e) {
816 throw new StorageObjectException(e.toString());
820 if (streamedInput != null) {
821 for (int i = 0; i < streamedInput.size(); i++) {
822 sql.append(",").append(streamedInput.get(i)).append("=?");
825 sql.append(" where id=").append(id);
826 theLog.printInfo("UPDATE: " + sql);
829 con = getPooledCon();
830 con.setAutoCommit(false);
831 pstmt = con.prepareStatement(sql.toString());
832 if (streamedInput != null) {
833 for (int i = 0; i < streamedInput.size(); i++) {
834 String inputString = theEntity.getValue((String)streamedInput.get(i));
835 pstmt.setBytes(i + 1, inputString.getBytes());
838 pstmt.executeUpdate();
839 } catch (SQLException sqe) {
840 throwSQLException(sqe, "update");
843 con.setAutoCommit(true);
844 } catch (Exception e) {
847 freeConnection(con, pstmt);
853 * @param id des zu loeschenden Datensatzes
854 * @return boolean liefert true zurueck, wenn loeschen erfolgreich war.
856 public boolean delete (String id) throws StorageObjectException {
858 invalidatePopupCache();
859 // ostore send notification
860 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
861 String uniqueId = id;
862 if ( theEntityClass.equals(StorableObjectEntity.class) )
863 uniqueId+="@"+theTable;
864 theLog.printInfo("CACHE: (del) " + id);
865 StoreIdentifier search_sid =
866 new StoreIdentifier(theEntityClass, StoreContainerType.STOC_TYPE_ENTITY, uniqueId);
867 o_store.invalidate(search_sid);
870 /** @todo could be prepared Statement */
871 Statement stmt = null; Connection con = null;
873 String sql="delete from "+theTable+" where "+thePKeyName+"='"+id+"'";
874 theLog.printInfo("DELETE " + sql);
876 con = getPooledCon(); stmt = con.createStatement();
877 res = stmt.executeUpdate(sql);
879 catch (SQLException sqe) { throwSQLException(sqe, "delete"); }
880 finally { freeConnection(con, stmt); }
882 return (res > 0) ? true : false;
885 /* noch nicht implementiert.
886 * @return immer false
888 public boolean delete (EntityList theEntityList) {
889 invalidatePopupCache();
894 * Diese Methode sollte ueberschrieben werden, wenn fuer die abgeleitete Database-Klasse
895 * eine SimpleList mit Standard-Popupdaten erzeugt werden koennen soll.
898 public SimpleList getPopupData () throws StorageObjectException {
903 * Holt Daten fuer Popups.
904 * @param name Name des Feldes.
905 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
906 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
908 public SimpleList getPopupData (String name, boolean hasNullValue)
909 throws StorageObjectException {
910 return getPopupData(name, hasNullValue, null);
914 * Holt Daten fuer Popups.
915 * @param name Name des Feldes.
916 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
917 * @param where Schraenkt die Selektion der Datensaetze ein.
918 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
920 public SimpleList getPopupData (String name, boolean hasNullValue, String where) throws StorageObjectException {
921 return getPopupData(name, hasNullValue, where, null);
925 * Holt Daten fuer Popups.
926 * @param name Name des Feldes.
927 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
928 * @param where Schraenkt die Selektion der Datensaetze ein.
929 * @param order Gibt ein Feld als Sortierkriterium an.
930 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
932 public SimpleList getPopupData (String name, boolean hasNullValue, String where, String order) throws StorageObjectException {
934 if (hasPopupCache && popupCache != null)
936 SimpleList simpleList = null;
937 Connection con = null;
938 Statement stmt = null;
940 StringBuffer sql = new StringBuffer("select ").append(thePKeyName)
941 .append(",").append(name).append(" from ")
943 if (where != null && !(where.length() == 0))
944 sql.append(" where ").append(where);
945 sql.append(" order by ");
946 if (order != null && !(order.length() == 0))
952 con = getPooledCon();
953 } catch (Exception e) {
954 throw new StorageObjectException(e.toString());
957 stmt = con.createStatement();
958 ResultSet rs = executeSql(stmt, sql.toString());
961 if (!evaluatedMetaData) get_meta_data();
962 simpleList = new SimpleList();
963 // if popup has null-selector
964 if (hasNullValue) simpleList.add(POPUP_EMTYLINE);
966 SimpleHash popupDict;
968 popupDict = new SimpleHash();
969 popupDict.put("key", getValueAsString(rs, 1, thePKeyType));
970 popupDict.put("value", rs.getString(2));
971 simpleList.add(popupDict);
975 } catch (Exception e) {
976 theLog.printError("getPopupData: "+e.toString());
977 throw new StorageObjectException(e.toString());
979 freeConnection(con, stmt);
982 if (hasPopupCache) popupCache = simpleList;
987 * Liefert alle Daten der Tabelle als SimpleHash zurueck. Dies wird verwandt,
988 * wenn in den Templates ein Lookup-Table benoetigt wird. Sollte nur bei kleinen
989 * Tabellen Verwendung finden.
990 * @return SimpleHash mit den Tabellezeilen.
992 public SimpleHash getHashData () {
993 /** @todo dangerous! this should have a flag to be enabled, otherwise
994 * very big Hashes could be returned */
995 if (hashCache == null) {
997 hashCache = HTMLTemplateProcessor.makeSimpleHash(selectByWhereClause("",
999 } catch (StorageObjectException e) {
1000 theLog.printDebugInfo(e.toString());
1006 /* invalidates the popupCache
1008 protected void invalidatePopupCache () {
1009 /** @todo invalidates toooo much */
1015 * Diese Methode fuehrt den Sqlstring <i>sql</i> aus und timed im Logfile.
1016 * @param stmt Statemnt
1017 * @param sql Sql-String
1019 * @exception StorageObjectException
1021 public ResultSet executeSql (Statement stmt, String sql)
1022 throws StorageObjectException, SQLException
1024 long startTime = System.currentTimeMillis();
1027 rs = stmt.executeQuery(sql);
1028 theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: "
1031 catch (SQLException e)
1033 theLog.printDebugInfo("Failed: " + (System.currentTimeMillis()
1034 - startTime) + "ms. for: "+ sql);
1042 * Fuehrt Statement stmt aus und liefert Resultset zurueck. Das SQL-Statment wird
1043 * getimed und geloggt.
1044 * @param stmt PreparedStatement mit der SQL-Anweisung
1045 * @return Liefert ResultSet des Statements zurueck.
1046 * @exception StorageObjectException, SQLException
1048 public ResultSet executeSql (PreparedStatement stmt)
1049 throws StorageObjectException, SQLException {
1051 long startTime = (new java.util.Date()).getTime();
1052 ResultSet rs = stmt.executeQuery();
1053 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms.");
1058 * returns the number of rows in the table
1060 public int getSize(String where)
1061 throws SQLException,StorageObjectException
1063 long startTime = System.currentTimeMillis();
1064 String sql = "SELECT count(*) FROM "+ theTable + " where " + where;
1065 Connection con = null;
1066 Statement stmt = null;
1070 con = getPooledCon();
1071 stmt = con.createStatement();
1072 ResultSet rs = executeSql(stmt,sql);
1074 result = rs.getInt(1);
1076 } catch (SQLException e) {
1077 theLog.printError(e.toString());
1079 freeConnection(con,stmt);
1081 //theLog.printInfo(theTable + " has "+ result +" rows where " + where);
1082 theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: "
1087 public int executeUpdate(Statement stmt, String sql)
1088 throws StorageObjectException, SQLException
1091 long startTime = (new java.util.Date()).getTime();
1094 rs = stmt.executeUpdate(sql);
1095 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: "
1098 catch (SQLException e)
1100 theLog.printDebugInfo("Failed: " + (new java.util.Date().getTime()
1101 - startTime) + "ms. for: "+ sql);
1107 public int executeUpdate(String sql)
1108 throws StorageObjectException, SQLException
1111 long startTime = (new java.util.Date()).getTime();
1112 Connection con=null;PreparedStatement pstmt=null;
1115 pstmt = con.prepareStatement(sql);
1116 result = pstmt.executeUpdate();
1118 catch (Exception e) {
1119 theLog.printDebugInfo("settimage :: setImage gescheitert: "+e.toString());
1120 throw new StorageObjectException("executeUpdate failed: "+e.toString());
1122 finally { freeConnection(con,pstmt); }
1123 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: "
1129 * Wertet ResultSetMetaData aus und setzt interne Daten entsprechend
1130 * @param md ResultSetMetaData
1131 * @exception StorageObjectException
1133 private void evalMetaData (ResultSetMetaData md)
1134 throws StorageObjectException {
1136 this.evaluatedMetaData = true;
1137 this.metadataFields = new ArrayList();
1138 this.metadataLabels = new ArrayList();
1139 this.metadataNotNullFields = new ArrayList();
1141 int numFields = md.getColumnCount();
1142 this.metadataTypes = new int[numFields];
1145 for (int i = 1; i <= numFields; i++) {
1146 aField = md.getColumnName(i);
1147 metadataFields.add(aField);
1148 metadataLabels.add(md.getColumnLabel(i));
1149 aType = md.getColumnType(i);
1150 metadataTypes[i - 1] = aType;
1151 if (aField.equals(thePKeyName)) {
1152 thePKeyType = aType; thePKeyIndex = i;
1154 if (md.isNullable(i) == md.columnNullable) {
1155 metadataNotNullFields.add(aField);
1158 } catch (SQLException e) {
1159 throwSQLException(e, "evalMetaData");
1164 * Wertet die Metadaten eines Resultsets fuer eine Tabelle aus,
1165 * um die alle Columns und Typen einer Tabelle zu ermitteln.
1167 private void get_meta_data () throws StorageObjectException {
1168 Connection con = null;
1169 PreparedStatement pstmt = null;
1170 String sql = "select * from " + theTable + " where 0=1";
1172 con = getPooledCon();
1173 pstmt = con.prepareStatement(sql);
1174 theLog.printInfo("METADATA: " + sql);
1175 ResultSet rs = pstmt.executeQuery();
1176 evalMetaData(rs.getMetaData());
1178 } catch (SQLException e) {
1179 throwSQLException(e, "get_meta_data");
1181 freeConnection(con, pstmt);
1186 public Connection getPooledCon() throws StorageObjectException {
1187 /* @todo , doublecheck but I'm pretty sure that this is unnecessary. -mh
1189 Class.forName("com.codestudio.sql.PoolMan").newInstance();
1190 } catch (Exception e){
1191 throw new StorageObjectException("Could not find the PoolMan Driver"
1194 Connection con = null;
1196 con = SQLManager.getInstance().requestConnection();
1197 } catch(SQLException e){
1198 theLog.printError("could not connect to the database "+e.toString());
1199 System.err.println("could not connect to the database "+e.toString());
1200 throw new StorageObjectException("Could not connect to the database"+
1206 public void freeConnection (Connection con, Statement stmt)
1207 throws StorageObjectException {
1208 SQLManager.getInstance().closeStatement(stmt);
1209 SQLManager.getInstance().returnConnection(con);
1213 * Wertet SQLException aus und wirft dannach eine StorageObjectException
1214 * @param sqe SQLException
1215 * @param wo Funktonsname, in der die SQLException geworfen wurde
1216 * @exception StorageObjectException
1218 protected void throwSQLException (SQLException sqe, String wo)
1219 throws StorageObjectException {
1221 String message = "";
1224 state = sqe.getSQLState();
1225 message = sqe.getMessage();
1226 vendor = sqe.getErrorCode();
1228 theLog.printError(state + ": " + vendor + " : " + message + " Funktion: "
1230 throw new StorageObjectException((sqe == null) ? "undefined sql exception" :
1234 protected void _throwStorageObjectException (Exception e, String wo)
1235 throws StorageObjectException {
1237 theLog.printError(e.toString()+ wo);
1238 throw new StorageObjectException(wo + e.toString());
1240 theLog.printError(wo);
1241 throw new StorageObjectException(wo);
1247 * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach
1248 * eine StorageObjectException
1249 * @param message Nachricht mit dem Fehler
1250 * @exception StorageObjectException
1252 void throwStorageObjectException (String message)
1253 throws StorageObjectException {
1254 _throwStorageObjectException(null, message);