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.22 $ $Date: 2002/08/25 19:00:09 $
31 * @author $Author: mh $
33 * $Log: Database.java,v $
34 * Revision 1.22 2002/08/25 19:00:09 mh
35 * merge of localization branch into HEAD. mh and zap
37 * Revision 1.21 2002/08/04 23:38:22 mh
38 * fix up the webdb_create update stuff
40 * Revision 1.20 2002/07/21 22:32:25 mh
41 * on insert, the "webdb_lastchange" field should get a value
43 * Revision 1.19 2002/06/29 15:44:46 mh
44 * make the webdb_create update be called webdb_create_update. it breaks things otherwise. a fixme case I know..
46 * Revision 1.18 2002/06/28 20:42:13 mh
47 * added necessary bits in templates and Database.java to make webdb_create modifiable. make the conversion from sql/Timestamp to String more robust
51 public class Database implements StorageObject {
53 protected String theTable;
54 protected String theCoreTable=null;
55 protected String thePKeyName="id";
56 protected int thePKeyType, thePKeyIndex;
57 protected boolean evaluatedMetaData=false;
58 protected ArrayList metadataFields,metadataLabels,
59 metadataNotNullFields;
60 protected int[] metadataTypes;
61 protected Class theEntityClass;
62 protected StorageObject myselfDatabase;
63 protected SimpleList popupCache=null;
64 protected boolean hasPopupCache = false;
65 protected SimpleHash hashCache=null;
66 protected boolean hasTimestamp=true;
67 private String database_driver, database_url;
68 private int defaultLimit;
69 protected DatabaseAdaptor theAdaptor;
70 protected Logfile theLog;
71 private static Class GENERIC_ENTITY_CLASS=null,
72 STORABLE_OBJECT_ENTITY_CLASS=null;
73 private static SimpleHash POPUP_EMTYLINE=new SimpleHash();
74 protected static final ObjectStore o_store=ObjectStore.getInstance();
75 private SimpleDateFormat _dateFormatterOut =
76 new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
77 private SimpleDateFormat _dateFormatterIn =
78 new SimpleDateFormat("yyyy-MM-dd HH:mm");
79 private Calendar _cal = new GregorianCalendar();
81 private static final int _millisPerHour = 60 * 60 * 1000;
82 private static final int _millisPerMinute = 60 * 1000;
85 // always same object saves a little space
86 POPUP_EMTYLINE.put("key", ""); POPUP_EMTYLINE.put("value", "--");
88 GENERIC_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity");
89 STORABLE_OBJECT_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity");
92 System.err.println("FATAL: Database.java could not initialize" + e.toString());
98 * Kontruktor bekommt den Filenamen des Konfigurationsfiles übergeben.
99 * Aus diesem file werden <code>Database.Logfile</code>,
100 * <code>Database.Username</code>,<code>Database.Password</code>,
101 * <code>Database.Host</code> und <code>Database.Adaptor</code>
102 * ausgelesen und ein Broker für die Verbindugen zur Datenbank
105 * @param String confFilename Dateiname der Konfigurationsdatei
107 public Database() throws StorageObjectException {
108 theLog = Logfile.getInstance(MirConfig.getProp("Home")+
109 MirConfig.getProp("Database.Logfile"));
110 String theAdaptorName=MirConfig.getProp("Database.Adaptor");
111 defaultLimit = Integer.parseInt(MirConfig.getProp("Database.Limit"));
113 theEntityClass = GENERIC_ENTITY_CLASS;
114 theAdaptor = (DatabaseAdaptor)Class.forName(theAdaptorName).newInstance();
115 } catch (Exception e){
116 theLog.printError("Error in Database() constructor with "+
117 theAdaptorName + " -- " +e.toString());
118 throw new StorageObjectException("Error in Database() constructor with "
121 /*String database_username=MirConfig.getProp("Database.Username");
122 String database_password=MirConfig.getProp("Database.Password");
123 String database_host=MirConfig.getProp("Database.Host");
125 database_driver=theAdaptor.getDriver();
126 database_url=theAdaptor.getURL(database_username,database_password,
128 theLog.printDebugInfo("adding Broker with: " +database_driver+":"+
130 MirConfig.addBroker(database_driver,database_url);
131 //myBroker=MirConfig.getBroker();
136 * Liefert die Entity-Klasse zurück, in der eine Datenbankzeile gewrappt
137 * wird. Wird die Entity-Klasse durch die erbende Klasse nicht überschrieben,
138 * wird eine mir.entity.GenericEntity erzeugt.
140 * @return Class-Objekt der Entity
142 public java.lang.Class getEntityClass () {
143 return theEntityClass;
147 * Liefert die Standardbeschränkung von select-Statements zurück, also
148 * wieviel Datensätze per Default selektiert werden.
150 * @return Standard-Anzahl der Datensätze
152 public int getLimit () {
157 * Liefert den Namen des Primary-Keys zurück. Wird die Variable nicht von
158 * der erbenden Klasse überschrieben, so ist der Wert <code>PKEY</code>
159 * @return Name des Primary-Keys
161 public String getIdName () {
166 * Liefert den Namen der Tabelle, auf das sich das Datenbankobjekt bezieht.
168 * @return Name der Tabelle
170 public String getTableName () {
175 * Dient dazu vererbte Tabellen bei objectrelationalen DBMS
176 * zu speichern, wenn die id einer Tabelle in der parenttabelle verwaltet
178 * @return liefert theCoreTabel als String zurueck, wenn gesetzt, sonst
182 public String getCoreTable(){
183 if (theCoreTable!=null) return theCoreTable;
184 else return theTable;
188 * Liefert Feldtypen der Felder der Tabelle zurueck (s.a. java.sql.Types)
189 * @return int-Array mit den Typen der Felder
190 * @exception StorageObjectException
192 public int[] getTypes () throws StorageObjectException {
193 if (metadataTypes == null)
195 return metadataTypes;
199 * Liefert eine Liste der Labels der Tabellenfelder
200 * @return ArrayListe mit Labeln
201 * @exception StorageObjectException
203 public ArrayList getLabels () throws StorageObjectException {
204 if (metadataLabels == null)
206 return metadataLabels;
210 * Liefert eine Liste der Felder der Tabelle
211 * @return ArrayList mit Feldern
212 * @exception StorageObjectException
214 public ArrayList getFields () throws StorageObjectException {
215 if (metadataFields == null)
217 return metadataFields;
222 * Gets value out of ResultSet according to type and converts to String
223 * @param inValue Wert aus ResultSet.
224 * @param aType Datenbanktyp.
225 * @return liefert den Wert als String zurueck. Wenn keine Umwandlung moeglich
226 * dann /unsupported value/
228 private String getValueAsString (ResultSet rs, int valueIndex, int aType) throws StorageObjectException {
229 String outValue = null;
233 case java.sql.Types.BIT:
234 outValue = (rs.getBoolean(valueIndex) == true) ? "1" : "0";
236 case java.sql.Types.INTEGER:case java.sql.Types.SMALLINT:case java.sql.Types.TINYINT:case java.sql.Types.BIGINT:
237 int out = rs.getInt(valueIndex);
239 outValue = new Integer(out).toString();
241 case java.sql.Types.NUMERIC:
242 /** @todo Numeric can be float or double depending upon
243 * metadata.getScale() / especially with oracle */
244 long outl = rs.getLong(valueIndex);
246 outValue = new Long(outl).toString();
248 case java.sql.Types.REAL:
249 float tempf = rs.getFloat(valueIndex);
253 int tempf_int = (int)tempf;
254 tempf = (float)tempf_int;
256 outValue = "" + tempf;
257 outValue = outValue.replace('.', ',');
260 case java.sql.Types.DOUBLE:
261 double tempd = rs.getDouble(valueIndex);
265 int tempd_int = (int)tempd;
266 tempd = (double)tempd_int;
268 outValue = "" + tempd;
269 outValue = outValue.replace('.', ',');
272 case java.sql.Types.CHAR:case java.sql.Types.VARCHAR:case java.sql.Types.LONGVARCHAR:
273 outValue = rs.getString(valueIndex);
274 //if (outValue != null)
275 //outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue));
277 case java.sql.Types.LONGVARBINARY:
278 outValue = rs.getString(valueIndex);
279 //if (outValue != null)
280 //outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue));
282 case java.sql.Types.TIMESTAMP:
283 // it's important to use Timestamp here as getting it
284 // as a string is undefined and is only there for debugging
285 // according to the API. we can make it a string through formatting.
287 Timestamp timestamp = (rs.getTimestamp(valueIndex));
289 java.util.Date date = new java.util.Date(timestamp.getTime());
290 outValue = _dateFormatterOut.format(date);
292 int offset = _cal.get(Calendar.ZONE_OFFSET)+
293 _cal.get(Calendar.DST_OFFSET);
294 String tzOffset = StringUtil.zeroPaddingNumber(
295 offset/_millisPerHour,2,2);
296 outValue = outValue+"+"+tzOffset;
300 outValue = "<unsupported value>";
301 theLog.printWarning("Unsupported Datatype: at " + valueIndex +
304 } catch (SQLException e) {
305 throw new StorageObjectException("Could not get Value out of Resultset -- "
313 * select-Operator um einen Datensatz zu bekommen.
314 * @param id Primaerschluessel des Datensatzes.
315 * @return liefert EntityObject des gefundenen Datensatzes oder null.
317 public Entity selectById(String id) throws StorageObjectException
319 if (id==null||id.equals(""))
320 throw new StorageObjectException("id war null");
322 // ask object store for object
323 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
324 String uniqueId = id;
325 if ( theEntityClass.equals(StorableObjectEntity.class) )
326 uniqueId+="@"+theTable;
327 StoreIdentifier search_sid = new StoreIdentifier(theEntityClass, uniqueId);
328 theLog.printDebugInfo("CACHE: (dbg) looking for sid " + search_sid.toString());
329 Entity hit = (Entity)o_store.use(search_sid);
330 if ( hit!=null ) return hit;
333 Statement stmt=null;Connection con=getPooledCon();
334 Entity returnEntity=null;
337 /** @todo better prepared statement */
338 String selectSql = "select * from " + theTable + " where " + thePKeyName + "=" + id;
339 stmt = con.createStatement();
340 rs = executeSql(stmt, selectSql);
342 if (evaluatedMetaData==false) evalMetaData(rs.getMetaData());
344 returnEntity = makeEntityFromResultSet(rs);
345 else theLog.printDebugInfo("Keine daten fuer id: " + id + "in Tabelle" + theTable);
349 theLog.printDebugInfo("No Data for Id " + id + " in Table " + theTable);
352 catch (SQLException sqe){
353 throwSQLException(sqe,"selectById"); return null;
355 catch (NumberFormatException e) {
356 theLog.printError("ID ist keine Zahl: " + id);
358 finally { freeConnection(con,stmt); }
360 /** @todo OS: Entity should be saved in ostore */
366 * select-Operator um Datensaetze zu bekommen, die key = value erfuellen.
367 * @param key Datenbankfeld der Bedingung.
368 * @param value Wert die der key anehmen muss.
369 * @return EntityList mit den gematchten Entities
371 public EntityList selectByFieldValue(String aField, String aValue)
372 throws StorageObjectException
374 return selectByFieldValue(aField, aValue, 0);
378 * select-Operator um Datensaetze zu bekommen, die key = value erfuellen.
379 * @param key Datenbankfeld der Bedingung.
380 * @param value Wert die der key anehmen muss.
381 * @param offset Gibt an ab welchem Datensatz angezeigt werden soll.
382 * @return EntityList mit den gematchten Entities
384 public EntityList selectByFieldValue(String aField, String aValue, int offset)
385 throws StorageObjectException
387 return selectByWhereClause(aField + "=" + aValue, offset);
392 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
393 * Also offset wird der erste Datensatz genommen.
395 * @param wc where-Clause
396 * @return EntityList mit den gematchten Entities
397 * @exception StorageObjectException
399 public EntityList selectByWhereClause(String where)
400 throws StorageObjectException
402 return selectByWhereClause(where, 0);
407 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
408 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
410 * @param wc where-Clause
411 * @param offset ab welchem Datensatz.
412 * @return EntityList mit den gematchten Entities
413 * @exception StorageObjectException
415 public EntityList selectByWhereClause(String whereClause, int offset)
416 throws StorageObjectException
418 return selectByWhereClause(whereClause, null, offset);
423 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
424 * Also offset wird der erste Datensatz genommen.
425 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
427 * @param wc where-Clause
428 * @param ob orderBy-Clause
429 * @return EntityList mit den gematchten Entities
430 * @exception StorageObjectException
433 public EntityList selectByWhereClause(String where, String order)
434 throws StorageObjectException {
435 return selectByWhereClause(where, order, 0);
440 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
441 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
443 * @param wc where-Clause
444 * @param ob orderBy-Clause
445 * @param offset ab welchem Datensatz
446 * @return EntityList mit den gematchten Entities
447 * @exception StorageObjectException
450 public EntityList selectByWhereClause(String whereClause, String orderBy, int offset)
451 throws StorageObjectException {
452 return selectByWhereClause(whereClause, orderBy, offset, defaultLimit);
457 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
458 * @param wc where-Clause
459 * @param ob orderBy-Clause
460 * @param offset ab welchem Datensatz
461 * @param limit wieviele Datensätze
462 * @return EntityList mit den gematchten Entities
463 * @exception StorageObjectException
466 public EntityList selectByWhereClause(String wc, String ob, int offset, int limit)
467 throws StorageObjectException
470 // check o_store for entitylist
471 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
472 StoreIdentifier search_sid =
473 new StoreIdentifier( theEntityClass,
474 StoreContainerType.STOC_TYPE_ENTITYLIST,
475 StoreUtil.getEntityListUniqueIdentifierFor(theTable,wc,ob,offset,limit) );
476 EntityList hit = (EntityList)o_store.use(search_sid);
478 theLog.printDebugInfo("CACHE (hit): " + search_sid.toString());
484 EntityList theReturnList=null;
485 Connection con=null; Statement stmt=null;
487 int offsetCount = 0, count=0;
489 // build sql-statement
491 /** @todo count sql string should only be assembled if we really count
492 * see below at the end of method //rk */
494 if (wc != null && wc.length() == 0) {
497 StringBuffer countSql = new StringBuffer("select count(*) from ").append(theTable);
498 StringBuffer selectSql = new StringBuffer("select * from ").append(theTable);
500 selectSql.append(" where ").append(wc);
501 countSql.append(" where ").append(wc);
503 if (ob != null && !(ob.length() == 0)) {
504 selectSql.append(" order by ").append(ob);
506 if (theAdaptor.hasLimit()) {
507 if (limit > -1 && offset > -1) {
508 selectSql.append(" limit ");
509 if (theAdaptor.reverseLimit()) {
510 selectSql.append(limit).append(",").append(offset);
513 selectSql.append(offset).append(",").append(limit);
520 con = getPooledCon();
521 stmt = con.createStatement();
524 rs = executeSql(stmt, selectSql.toString());
526 if (!evaluatedMetaData) evalMetaData(rs.getMetaData());
528 theReturnList = new EntityList();
529 Entity theResultEntity;
531 theResultEntity = makeEntityFromResultSet(rs);
532 theReturnList.add(theResultEntity);
538 // making entitylist infos
539 if (!(theAdaptor.hasLimit())) count = offsetCount;
541 if (theReturnList != null) {
542 // now we decide if we have to know an overall count...
544 if (limit > -1 && offset > -1) {
545 if (offsetCount==limit) {
546 /** @todo counting should be deffered to entitylist
547 * getSize() should be used */
548 rs = executeSql(stmt, countSql.toString());
550 if ( rs.next() ) count = rs.getInt(1);
553 else theLog.printError("Could not count: " + countSql);
556 theReturnList.setCount(count);
557 theReturnList.setOffset(offset);
558 theReturnList.setWhere(wc);
559 theReturnList.setOrder(ob);
560 theReturnList.setStorage(this);
561 theReturnList.setLimit(limit);
562 if ( offset >= limit )
563 theReturnList.setPrevBatch(offset - limit);
564 if ( offset+offsetCount < count )
565 theReturnList.setNextBatch(offset + limit);
566 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
567 StoreIdentifier sid=theReturnList.getStoreIdentifier();
568 theLog.printDebugInfo("CACHE (add): " + sid.toString());
573 catch (SQLException sqe) { throwSQLException(sqe, "selectByWhereClause"); }
574 finally { freeConnection(con, stmt); }
576 return theReturnList;
581 * Bastelt aus einer Zeile der Datenbank ein EntityObjekt.
583 * @param rs Das ResultSetObjekt.
584 * @return Entity Die Entity.
586 private Entity makeEntityFromResultSet (ResultSet rs)
587 throws StorageObjectException
589 /** @todo OS: get Pkey from ResultSet and consult ObjectStore */
590 HashMap theResultHash = new HashMap();
591 String theResult = null;
593 Entity returnEntity = null;
595 int size = metadataFields.size();
596 for (int i = 0; i < size; i++) {
597 // alle durchlaufen bis nix mehr da
599 theType = metadataTypes[i];
600 if (theType == java.sql.Types.LONGVARBINARY) {
601 InputStreamReader is = (InputStreamReader)rs.getCharacterStream(i + 1);
603 char[] data = new char[32768];
604 StringBuffer theResultString = new StringBuffer();
606 while ((len = is.read(data)) > 0) {
607 theResultString.append(data, 0, len);
610 theResult = theResultString.toString();
617 theResult = getValueAsString(rs, (i + 1), theType);
619 if (theResult != null) {
620 theResultHash.put(metadataFields.get(i), theResult);
623 if (theEntityClass != null) {
624 returnEntity = (Entity)theEntityClass.newInstance();
625 returnEntity.setValues(theResultHash);
626 returnEntity.setStorage(myselfDatabase);
627 if ( returnEntity instanceof StorableObject ) {
628 theLog.printDebugInfo("CACHE: ( in) " + returnEntity.getId() + " :"+theTable);
629 o_store.add(((StorableObject)returnEntity).getStoreIdentifier());
632 throwStorageObjectException("Internal Error: theEntityClass not set!");
634 } catch (IllegalAccessException e) {
635 throwStorageObjectException("Kein Zugriff! -- " + e.toString());
636 } catch (IOException e) {
637 throwStorageObjectException("IOException! -- " + e.toString());
638 } catch (InstantiationException e) {
639 throwStorageObjectException("Keine Instantiiierung! -- " + e.toString());
640 } catch (SQLException sqe) {
641 throwSQLException(sqe, "makeEntityFromResultSet");
648 * insert-Operator: fügt eine Entity in die Tabelle ein. Eine Spalte WEBDB_CREATE
649 * wird automatisch mit dem aktuellen Datum gefuellt.
652 * @return der Wert des Primary-keys der eingefügten Entity
654 public String insert (Entity theEntity) throws StorageObjectException {
656 invalidatePopupCache();
658 // invalidating all EntityLists corresponding with theEntityClass
659 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
660 StoreContainerType stoc_type =
661 StoreContainerType.valueOf( theEntityClass,
662 StoreContainerType.STOC_TYPE_ENTITYLIST);
663 o_store.invalidate(stoc_type);
666 String returnId = null;
667 Connection con = null; PreparedStatement pstmt = null;
670 ArrayList streamedInput = theEntity.streamedInput();
671 StringBuffer f = new StringBuffer();
672 StringBuffer v = new StringBuffer();
673 String aField, aValue;
674 boolean firstField = true;
676 for (int i = 0; i < getFields().size(); i++) {
677 aField = (String)getFields().get(i);
678 if (!aField.equals(thePKeyName)) {
681 if (aField.equals("webdb_create") ||
682 aField.equals("webdb_lastchange")) {
686 if (streamedInput != null && streamedInput.contains(aField)) {
690 if (theEntity.hasValueForField(aField)) {
691 aValue = "'" + StringUtil.quote((String)theEntity.getValue(aField))
696 // wenn Wert gegeben, dann einbauen
697 if (aValue != null) {
698 if (firstField == false) {
711 StringBuffer sqlBuf = new StringBuffer("insert into ").append(theTable).append("(").append(f).append(") values (").append(v).append(")");
712 String sql = sqlBuf.toString();
713 theLog.printInfo("INSERT: " + sql);
714 con = getPooledCon();
715 con.setAutoCommit(false);
716 pstmt = con.prepareStatement(sql);
717 if (streamedInput != null) {
718 for (int i = 0; i < streamedInput.size(); i++) {
719 String inputString = (String)theEntity.getValue((String)streamedInput.get(i));
720 pstmt.setBytes(i + 1, inputString.getBytes());
723 int ret = pstmt.executeUpdate();
728 pstmt = con.prepareStatement(theAdaptor.getLastInsertSQL((Database)myselfDatabase));
729 ResultSet rs = pstmt.executeQuery();
731 returnId = rs.getString(1);
732 theEntity.setId(returnId);
733 } catch (SQLException sqe) {
734 throwSQLException(sqe, "insert");
737 con.setAutoCommit(true);
738 } catch (Exception e) {
741 freeConnection(con, pstmt);
743 /** @todo store entity in o_store */
748 * update-Operator: aktualisiert eine Entity. Eine Spalte WEBDB_LASTCHANGE
749 * wird automatisch mit dem aktuellen Datum gefuellt.
753 public void update (Entity theEntity) throws StorageObjectException
755 Connection con = null; PreparedStatement pstmt = null;
756 /** @todo this is stupid: why do we prepare statement, when we
757 * throw it away afterwards. should be regular statement
758 * update/insert could better be one routine called save()
759 * that chooses to either insert or update depending if we
760 * have a primary key in the entity. i don't know if we
761 * still need the streamed input fields. // rk */
763 /** @todo extension: check if Entity did change, otherwise we don't need
764 * the roundtrip to the database */
766 /** invalidating corresponding entitylists in o_store*/
767 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
768 StoreContainerType stoc_type =
769 StoreContainerType.valueOf( theEntityClass,
770 StoreContainerType.STOC_TYPE_ENTITYLIST);
771 o_store.invalidate(stoc_type);
774 ArrayList streamedInput = theEntity.streamedInput();
775 String id = theEntity.getId();
777 StringBuffer fv = new StringBuffer();
778 boolean firstField = true;
780 invalidatePopupCache();
781 // build sql statement
782 for (int i = 0; i < getFields().size(); i++) {
783 aField = (String)metadataFields.get(i);
785 if (!(aField.equals(thePKeyName) || aField.equals("webdb_create") ||
786 aField.equals("webdb_lastchange") || (streamedInput != null && streamedInput.contains(aField)))) {
787 if (theEntity.hasValueForField(aField)) {
788 if (firstField == false) {
794 fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getValue(aField))).append("'");
798 StringBuffer sql = new StringBuffer("update ").append(theTable).append(" set ").append(fv);
800 if (metadataFields.contains("webdb_lastchange")) {
801 sql.append(",webdb_lastchange=NOW()");
803 // special case: the webdb_create requires the field in yyyy-mm-dd HH:mm
804 // format so anything extra will be ignored. -mh
805 if (metadataFields.contains("webdb_create") &&
806 theEntity.hasValueForField("webdb_create")) {
807 // minimum of 10 (yyyy-mm-dd)...
808 if (theEntity.getValue("webdb_create").length() >= 10) {
809 String dateString = theEntity.getValue("webdb_create");
810 // if only 10, then add 00:00 so it doesn't throw a ParseException
811 if (dateString.length() == 10)
812 dateString=dateString+" 00:00";
816 java.util.Date d = _dateFormatterIn.parse(dateString);
817 Timestamp tStamp = new Timestamp(d.getTime());
818 sql.append(",webdb_create='"+tStamp.toString()+"'");
819 } catch (ParseException e) {
820 throw new StorageObjectException(e.toString());
824 if (streamedInput != null) {
825 for (int i = 0; i < streamedInput.size(); i++) {
826 sql.append(",").append(streamedInput.get(i)).append("=?");
829 sql.append(" where id=").append(id);
830 theLog.printInfo("UPDATE: " + sql);
833 con = getPooledCon();
834 con.setAutoCommit(false);
835 pstmt = con.prepareStatement(sql.toString());
836 if (streamedInput != null) {
837 for (int i = 0; i < streamedInput.size(); i++) {
838 String inputString = theEntity.getValue((String)streamedInput.get(i));
839 pstmt.setBytes(i + 1, inputString.getBytes());
842 pstmt.executeUpdate();
843 } catch (SQLException sqe) {
844 throwSQLException(sqe, "update");
847 con.setAutoCommit(true);
848 } catch (Exception e) {
851 freeConnection(con, pstmt);
857 * @param id des zu loeschenden Datensatzes
858 * @return boolean liefert true zurueck, wenn loeschen erfolgreich war.
860 public boolean delete (String id) throws StorageObjectException {
862 invalidatePopupCache();
863 // ostore send notification
864 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
865 String uniqueId = id;
866 if ( theEntityClass.equals(StorableObjectEntity.class) )
867 uniqueId+="@"+theTable;
868 theLog.printInfo("CACHE: (del) " + id);
869 StoreIdentifier search_sid =
870 new StoreIdentifier(theEntityClass, StoreContainerType.STOC_TYPE_ENTITY, uniqueId);
871 o_store.invalidate(search_sid);
874 /** @todo could be prepared Statement */
875 Statement stmt = null; Connection con = null;
877 String sql="delete from "+theTable+" where "+thePKeyName+"='"+id+"'";
878 theLog.printInfo("DELETE " + sql);
880 con = getPooledCon(); stmt = con.createStatement();
881 res = stmt.executeUpdate(sql);
883 catch (SQLException sqe) { throwSQLException(sqe, "delete"); }
884 finally { freeConnection(con, stmt); }
886 return (res > 0) ? true : false;
889 /* noch nicht implementiert.
890 * @return immer false
892 public boolean delete (EntityList theEntityList) {
893 invalidatePopupCache();
898 * Diese Methode sollte ueberschrieben werden, wenn fuer die abgeleitete Database-Klasse
899 * eine SimpleList mit Standard-Popupdaten erzeugt werden koennen soll.
902 public SimpleList getPopupData () throws StorageObjectException {
907 * Holt Daten fuer Popups.
908 * @param name Name des Feldes.
909 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
910 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
912 public SimpleList getPopupData (String name, boolean hasNullValue)
913 throws StorageObjectException {
914 return getPopupData(name, hasNullValue, null);
918 * Holt Daten fuer Popups.
919 * @param name Name des Feldes.
920 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
921 * @param where Schraenkt die Selektion der Datensaetze ein.
922 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
924 public SimpleList getPopupData (String name, boolean hasNullValue, String where) throws StorageObjectException {
925 return getPopupData(name, hasNullValue, where, null);
929 * Holt Daten fuer Popups.
930 * @param name Name des Feldes.
931 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
932 * @param where Schraenkt die Selektion der Datensaetze ein.
933 * @param order Gibt ein Feld als Sortierkriterium an.
934 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
936 public SimpleList getPopupData (String name, boolean hasNullValue, String where, String order) throws StorageObjectException {
938 if (hasPopupCache && popupCache != null)
940 SimpleList simpleList = null;
941 Connection con = null;
942 Statement stmt = null;
944 StringBuffer sql = new StringBuffer("select ").append(thePKeyName)
945 .append(",").append(name).append(" from ")
947 if (where != null && !(where.length() == 0))
948 sql.append(" where ").append(where);
949 sql.append(" order by ");
950 if (order != null && !(order.length() == 0))
956 con = getPooledCon();
957 } catch (Exception e) {
958 throw new StorageObjectException(e.toString());
961 stmt = con.createStatement();
962 ResultSet rs = executeSql(stmt, sql.toString());
965 if (!evaluatedMetaData) get_meta_data();
966 simpleList = new SimpleList();
967 // if popup has null-selector
968 if (hasNullValue) simpleList.add(POPUP_EMTYLINE);
970 SimpleHash popupDict;
972 popupDict = new SimpleHash();
973 popupDict.put("key", getValueAsString(rs, 1, thePKeyType));
974 popupDict.put("value", rs.getString(2));
975 simpleList.add(popupDict);
979 } catch (Exception e) {
980 theLog.printError("getPopupData: "+e.toString());
981 throw new StorageObjectException(e.toString());
983 freeConnection(con, stmt);
986 if (hasPopupCache) popupCache = simpleList;
991 * Liefert alle Daten der Tabelle als SimpleHash zurueck. Dies wird verwandt,
992 * wenn in den Templates ein Lookup-Table benoetigt wird. Sollte nur bei kleinen
993 * Tabellen Verwendung finden.
994 * @return SimpleHash mit den Tabellezeilen.
996 public SimpleHash getHashData () {
997 /** @todo dangerous! this should have a flag to be enabled, otherwise
998 * very big Hashes could be returned */
999 if (hashCache == null) {
1001 hashCache = HTMLTemplateProcessor.makeSimpleHash(selectByWhereClause("",
1003 } catch (StorageObjectException e) {
1004 theLog.printDebugInfo(e.toString());
1010 /* invalidates the popupCache
1012 protected void invalidatePopupCache () {
1013 /** @todo invalidates toooo much */
1019 * Diese Methode fuehrt den Sqlstring <i>sql</i> aus und timed im Logfile.
1020 * @param stmt Statemnt
1021 * @param sql Sql-String
1023 * @exception StorageObjectException
1025 public ResultSet executeSql (Statement stmt, String sql)
1026 throws StorageObjectException, SQLException
1028 long startTime = System.currentTimeMillis();
1031 rs = stmt.executeQuery(sql);
1032 theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: "
1035 catch (SQLException e)
1037 theLog.printDebugInfo("Failed: " + (System.currentTimeMillis()
1038 - startTime) + "ms. for: "+ sql);
1046 * Fuehrt Statement stmt aus und liefert Resultset zurueck. Das SQL-Statment wird
1047 * getimed und geloggt.
1048 * @param stmt PreparedStatement mit der SQL-Anweisung
1049 * @return Liefert ResultSet des Statements zurueck.
1050 * @exception StorageObjectException, SQLException
1052 public ResultSet executeSql (PreparedStatement stmt)
1053 throws StorageObjectException, SQLException {
1055 long startTime = (new java.util.Date()).getTime();
1056 ResultSet rs = stmt.executeQuery();
1057 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms.");
1062 * returns the number of rows in the table
1064 public int getSize(String where)
1065 throws SQLException,StorageObjectException
1067 long startTime = System.currentTimeMillis();
1068 String sql = "SELECT Count(*) FROM "+ theTable;
1069 if (where != null && !(where.length() == 0))
1070 sql = sql + " where " + where;
1071 Connection con = null;
1072 Statement stmt = null;
1076 con = getPooledCon();
1077 stmt = con.createStatement();
1078 ResultSet rs = executeSql(stmt,sql);
1080 result = rs.getInt(1);
1082 } catch (SQLException e) {
1083 theLog.printError(e.toString());
1085 freeConnection(con,stmt);
1087 //theLog.printInfo(theTable + " has "+ result +" rows where " + where);
1088 theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: "
1093 public int executeUpdate(Statement stmt, String sql)
1094 throws StorageObjectException, SQLException
1097 long startTime = (new java.util.Date()).getTime();
1100 rs = stmt.executeUpdate(sql);
1101 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: "
1104 catch (SQLException e)
1106 theLog.printDebugInfo("Failed: " + (new java.util.Date().getTime()
1107 - startTime) + "ms. for: "+ sql);
1113 public int executeUpdate(String sql)
1114 throws StorageObjectException, SQLException
1117 long startTime = (new java.util.Date()).getTime();
1118 Connection con=null;PreparedStatement pstmt=null;
1121 pstmt = con.prepareStatement(sql);
1122 result = pstmt.executeUpdate();
1124 catch (Exception e) {
1125 theLog.printDebugInfo("settimage :: setImage gescheitert: "+e.toString());
1126 throw new StorageObjectException("executeUpdate failed: "+e.toString());
1128 finally { freeConnection(con,pstmt); }
1129 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: "
1135 * Wertet ResultSetMetaData aus und setzt interne Daten entsprechend
1136 * @param md ResultSetMetaData
1137 * @exception StorageObjectException
1139 private void evalMetaData (ResultSetMetaData md)
1140 throws StorageObjectException {
1142 this.evaluatedMetaData = true;
1143 this.metadataFields = new ArrayList();
1144 this.metadataLabels = new ArrayList();
1145 this.metadataNotNullFields = new ArrayList();
1147 int numFields = md.getColumnCount();
1148 this.metadataTypes = new int[numFields];
1151 for (int i = 1; i <= numFields; i++) {
1152 aField = md.getColumnName(i);
1153 metadataFields.add(aField);
1154 metadataLabels.add(md.getColumnLabel(i));
1155 aType = md.getColumnType(i);
1156 metadataTypes[i - 1] = aType;
1157 if (aField.equals(thePKeyName)) {
1158 thePKeyType = aType; thePKeyIndex = i;
1160 if (md.isNullable(i) == md.columnNullable) {
1161 metadataNotNullFields.add(aField);
1164 } catch (SQLException e) {
1165 throwSQLException(e, "evalMetaData");
1170 * Wertet die Metadaten eines Resultsets fuer eine Tabelle aus,
1171 * um die alle Columns und Typen einer Tabelle zu ermitteln.
1173 private void get_meta_data () throws StorageObjectException {
1174 Connection con = null;
1175 PreparedStatement pstmt = null;
1176 String sql = "select * from " + theTable + " where 0=1";
1178 con = getPooledCon();
1179 pstmt = con.prepareStatement(sql);
1180 theLog.printInfo("METADATA: " + sql);
1181 ResultSet rs = pstmt.executeQuery();
1182 evalMetaData(rs.getMetaData());
1184 } catch (SQLException e) {
1185 throwSQLException(e, "get_meta_data");
1187 freeConnection(con, pstmt);
1192 public Connection getPooledCon() throws StorageObjectException {
1193 /* @todo , doublecheck but I'm pretty sure that this is unnecessary. -mh
1195 Class.forName("com.codestudio.sql.PoolMan").newInstance();
1196 } catch (Exception e){
1197 throw new StorageObjectException("Could not find the PoolMan Driver"
1200 Connection con = null;
1202 con = SQLManager.getInstance().requestConnection();
1203 } catch(SQLException e){
1204 theLog.printError("could not connect to the database "+e.toString());
1205 System.err.println("could not connect to the database "+e.toString());
1206 throw new StorageObjectException("Could not connect to the database"+
1212 public void freeConnection (Connection con, Statement stmt)
1213 throws StorageObjectException {
1214 SQLManager.getInstance().closeStatement(stmt);
1215 SQLManager.getInstance().returnConnection(con);
1219 * Wertet SQLException aus und wirft dannach eine StorageObjectException
1220 * @param sqe SQLException
1221 * @param wo Funktonsname, in der die SQLException geworfen wurde
1222 * @exception StorageObjectException
1224 protected void throwSQLException (SQLException sqe, String wo)
1225 throws StorageObjectException {
1227 String message = "";
1230 state = sqe.getSQLState();
1231 message = sqe.getMessage();
1232 vendor = sqe.getErrorCode();
1234 theLog.printError(state + ": " + vendor + " : " + message + " Funktion: "
1236 throw new StorageObjectException((sqe == null) ? "undefined sql exception" :
1240 protected void _throwStorageObjectException (Exception e, String wo)
1241 throws StorageObjectException {
1243 theLog.printError(e.toString()+ wo);
1244 throw new StorageObjectException(wo + e.toString());
1246 theLog.printError(wo);
1247 throw new StorageObjectException(wo);
1253 * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach
1254 * eine StorageObjectException
1255 * @param message Nachricht mit dem Fehler
1256 * @exception StorageObjectException
1258 void throwStorageObjectException (String message)
1259 throws StorageObjectException {
1260 _throwStorageObjectException(null, message);