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.18 $ $Date: 2002/06/28 20:42:13 $
31 * @author $Author: mh $
33 * $Log: Database.java,v $
34 * Revision 1.18 2002/06/28 20:42:13 mh
35 * added necessary bits in templates and Database.java to make webdb_create modifiable. make the conversion from sql/Timestamp to String more robust
39 public class Database implements StorageObject {
41 protected String theTable;
42 protected String theCoreTable=null;
43 protected String thePKeyName="id";
44 protected int thePKeyType, thePKeyIndex;
45 protected boolean evaluatedMetaData=false;
46 protected ArrayList metadataFields,metadataLabels,
47 metadataNotNullFields;
48 protected int[] metadataTypes;
49 protected Class theEntityClass;
50 protected StorageObject myselfDatabase;
51 protected SimpleList popupCache=null;
52 protected boolean hasPopupCache = false;
53 protected SimpleHash hashCache=null;
54 protected boolean hasTimestamp=true;
55 private String database_driver, database_url;
56 private int defaultLimit;
57 protected DatabaseAdaptor theAdaptor;
58 protected Logfile theLog;
59 private static Class GENERIC_ENTITY_CLASS=null,
60 STORABLE_OBJECT_ENTITY_CLASS=null;
61 private static SimpleHash POPUP_EMTYLINE=new SimpleHash();
62 protected static final ObjectStore o_store=ObjectStore.getInstance();
63 private SimpleDateFormat _dateFormatterOut =
64 new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
65 private SimpleDateFormat _dateFormatterIn =
66 new SimpleDateFormat("yyyy-MM-dd");
67 private Calendar _cal = new GregorianCalendar();
69 private static final int _millisPerHour = 60 * 60 * 1000;
70 private static final int _millisPerMinute = 60 * 1000;
73 // always same object saves a little space
74 POPUP_EMTYLINE.put("key", ""); POPUP_EMTYLINE.put("value", "--");
76 GENERIC_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity");
77 STORABLE_OBJECT_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity");
80 System.err.println("FATAL: Database.java could not initialize" + e.toString());
86 * Kontruktor bekommt den Filenamen des Konfigurationsfiles übergeben.
87 * Aus diesem file werden <code>Database.Logfile</code>,
88 * <code>Database.Username</code>,<code>Database.Password</code>,
89 * <code>Database.Host</code> und <code>Database.Adaptor</code>
90 * ausgelesen und ein Broker für die Verbindugen zur Datenbank
93 * @param String confFilename Dateiname der Konfigurationsdatei
95 public Database() throws StorageObjectException {
96 theLog = Logfile.getInstance(MirConfig.getProp("Home")+
97 MirConfig.getProp("Database.Logfile"));
98 String theAdaptorName=MirConfig.getProp("Database.Adaptor");
99 defaultLimit = Integer.parseInt(MirConfig.getProp("Database.Limit"));
101 theEntityClass = GENERIC_ENTITY_CLASS;
102 theAdaptor = (DatabaseAdaptor)Class.forName(theAdaptorName).newInstance();
103 } catch (Exception e){
104 theLog.printError("Error in Database() constructor with "+
105 theAdaptorName + " -- " +e.toString());
106 throw new StorageObjectException("Error in Database() constructor with "
109 /*String database_username=MirConfig.getProp("Database.Username");
110 String database_password=MirConfig.getProp("Database.Password");
111 String database_host=MirConfig.getProp("Database.Host");
113 database_driver=theAdaptor.getDriver();
114 database_url=theAdaptor.getURL(database_username,database_password,
116 theLog.printDebugInfo("adding Broker with: " +database_driver+":"+
118 MirConfig.addBroker(database_driver,database_url);
119 //myBroker=MirConfig.getBroker();
124 * Liefert die Entity-Klasse zurück, in der eine Datenbankzeile gewrappt
125 * wird. Wird die Entity-Klasse durch die erbende Klasse nicht überschrieben,
126 * wird eine mir.entity.GenericEntity erzeugt.
128 * @return Class-Objekt der Entity
130 public java.lang.Class getEntityClass () {
131 return theEntityClass;
135 * Liefert die Standardbeschränkung von select-Statements zurück, also
136 * wieviel Datensätze per Default selektiert werden.
138 * @return Standard-Anzahl der Datensätze
140 public int getLimit () {
145 * Liefert den Namen des Primary-Keys zurück. Wird die Variable nicht von
146 * der erbenden Klasse überschrieben, so ist der Wert <code>PKEY</code>
147 * @return Name des Primary-Keys
149 public String getIdName () {
154 * Liefert den Namen der Tabelle, auf das sich das Datenbankobjekt bezieht.
156 * @return Name der Tabelle
158 public String getTableName () {
163 * Dient dazu vererbte Tabellen bei objectrelationalen DBMS
164 * zu speichern, wenn die id einer Tabelle in der parenttabelle verwaltet
166 * @return liefert theCoreTabel als String zurueck, wenn gesetzt, sonst
170 public String getCoreTable(){
171 if (theCoreTable!=null) return theCoreTable;
172 else return theTable;
176 * Liefert Feldtypen der Felder der Tabelle zurueck (s.a. java.sql.Types)
177 * @return int-Array mit den Typen der Felder
178 * @exception StorageObjectException
180 public int[] getTypes () throws StorageObjectException {
181 if (metadataTypes == null)
183 return metadataTypes;
187 * Liefert eine Liste der Labels der Tabellenfelder
188 * @return ArrayListe mit Labeln
189 * @exception StorageObjectException
191 public ArrayList getLabels () throws StorageObjectException {
192 if (metadataLabels == null)
194 return metadataLabels;
198 * Liefert eine Liste der Felder der Tabelle
199 * @return ArrayList mit Feldern
200 * @exception StorageObjectException
202 public ArrayList getFields () throws StorageObjectException {
203 if (metadataFields == null)
205 return metadataFields;
210 * Gets value out of ResultSet according to type and converts to String
211 * @param inValue Wert aus ResultSet.
212 * @param aType Datenbanktyp.
213 * @return liefert den Wert als String zurueck. Wenn keine Umwandlung moeglich
214 * dann /unsupported value/
216 private String getValueAsString (ResultSet rs, int valueIndex, int aType) throws StorageObjectException {
217 String outValue = null;
221 case java.sql.Types.BIT:
222 outValue = (rs.getBoolean(valueIndex) == true) ? "1" : "0";
224 case java.sql.Types.INTEGER:case java.sql.Types.SMALLINT:case java.sql.Types.TINYINT:case java.sql.Types.BIGINT:
225 int out = rs.getInt(valueIndex);
227 outValue = new Integer(out).toString();
229 case java.sql.Types.NUMERIC:
230 /** @todo Numeric can be float or double depending upon
231 * metadata.getScale() / especially with oracle */
232 long outl = rs.getLong(valueIndex);
234 outValue = new Long(outl).toString();
236 case java.sql.Types.REAL:
237 float tempf = rs.getFloat(valueIndex);
241 int tempf_int = (int)tempf;
242 tempf = (float)tempf_int;
244 outValue = "" + tempf;
245 outValue = outValue.replace('.', ',');
248 case java.sql.Types.DOUBLE:
249 double tempd = rs.getDouble(valueIndex);
253 int tempd_int = (int)tempd;
254 tempd = (double)tempd_int;
256 outValue = "" + tempd;
257 outValue = outValue.replace('.', ',');
260 case java.sql.Types.CHAR:case java.sql.Types.VARCHAR:case java.sql.Types.LONGVARCHAR:
261 outValue = rs.getString(valueIndex);
262 //if (outValue != null)
263 //outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue));
265 case java.sql.Types.LONGVARBINARY:
266 outValue = rs.getString(valueIndex);
267 //if (outValue != null)
268 //outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue));
270 case java.sql.Types.TIMESTAMP:
271 // it's important to use Timestamp here as getting it
272 // as a string is undefined and is only there for debugging
273 // according to the API. we can make it a string through formatting.
275 Timestamp timestamp = (rs.getTimestamp(valueIndex));
277 java.util.Date date = new java.util.Date(timestamp.getTime());
278 outValue = _dateFormatterOut.format(date);
280 int offset = _cal.get(Calendar.ZONE_OFFSET)+
281 _cal.get(Calendar.DST_OFFSET);
282 String tzOffset = StringUtil.zeroPaddingNumber(
283 offset/_millisPerHour,2,2);
284 outValue = outValue+"+"+tzOffset;
288 outValue = "<unsupported value>";
289 theLog.printWarning("Unsupported Datatype: at " + valueIndex +
292 } catch (SQLException e) {
293 throw new StorageObjectException("Could not get Value out of Resultset -- "
301 * select-Operator um einen Datensatz zu bekommen.
302 * @param id Primaerschluessel des Datensatzes.
303 * @return liefert EntityObject des gefundenen Datensatzes oder null.
305 public Entity selectById(String id) throws StorageObjectException
307 if (id==null||id.equals(""))
308 throw new StorageObjectException("id war null");
310 // ask object store for object
311 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
312 String uniqueId = id;
313 if ( theEntityClass.equals(StorableObjectEntity.class) )
314 uniqueId+="@"+theTable;
315 StoreIdentifier search_sid = new StoreIdentifier(theEntityClass, uniqueId);
316 theLog.printDebugInfo("CACHE: (dbg) looking for sid " + search_sid.toString());
317 Entity hit = (Entity)o_store.use(search_sid);
318 if ( hit!=null ) return hit;
321 Statement stmt=null;Connection con=getPooledCon();
322 Entity returnEntity=null;
325 /** @todo better prepared statement */
326 String selectSql = "select * from " + theTable + " where " + thePKeyName + "=" + id;
327 stmt = con.createStatement();
328 rs = executeSql(stmt, selectSql);
330 if (evaluatedMetaData==false) evalMetaData(rs.getMetaData());
332 returnEntity = makeEntityFromResultSet(rs);
333 else theLog.printDebugInfo("Keine daten fuer id: " + id + "in Tabelle" + theTable);
337 theLog.printDebugInfo("No Data for Id " + id + " in Table " + theTable);
340 catch (SQLException sqe){
341 throwSQLException(sqe,"selectById"); return null;
343 catch (NumberFormatException e) {
344 theLog.printError("ID ist keine Zahl: " + id);
346 finally { freeConnection(con,stmt); }
348 /** @todo OS: Entity should be saved in ostore */
354 * select-Operator um Datensaetze zu bekommen, die key = value erfuellen.
355 * @param key Datenbankfeld der Bedingung.
356 * @param value Wert die der key anehmen muss.
357 * @return EntityList mit den gematchten Entities
359 public EntityList selectByFieldValue(String aField, String aValue)
360 throws StorageObjectException
362 return selectByFieldValue(aField, aValue, 0);
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 * @param offset Gibt an ab welchem Datensatz angezeigt werden soll.
370 * @return EntityList mit den gematchten Entities
372 public EntityList selectByFieldValue(String aField, String aValue, int offset)
373 throws StorageObjectException
375 return selectByWhereClause(aField + "=" + aValue, offset);
380 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
381 * Also offset wird der erste Datensatz genommen.
383 * @param wc where-Clause
384 * @return EntityList mit den gematchten Entities
385 * @exception StorageObjectException
387 public EntityList selectByWhereClause(String where)
388 throws StorageObjectException
390 return selectByWhereClause(where, 0);
395 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
396 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
398 * @param wc where-Clause
399 * @param offset ab welchem Datensatz.
400 * @return EntityList mit den gematchten Entities
401 * @exception StorageObjectException
403 public EntityList selectByWhereClause(String whereClause, int offset)
404 throws StorageObjectException
406 return selectByWhereClause(whereClause, null, offset);
411 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
412 * Also offset wird der erste Datensatz genommen.
413 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
415 * @param wc where-Clause
416 * @param ob orderBy-Clause
417 * @return EntityList mit den gematchten Entities
418 * @exception StorageObjectException
421 public EntityList selectByWhereClause(String where, String order)
422 throws StorageObjectException {
423 return selectByWhereClause(where, order, 0);
428 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
429 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
431 * @param wc where-Clause
432 * @param ob orderBy-Clause
433 * @param offset ab welchem Datensatz
434 * @return EntityList mit den gematchten Entities
435 * @exception StorageObjectException
438 public EntityList selectByWhereClause(String whereClause, String orderBy, int offset)
439 throws StorageObjectException {
440 return selectByWhereClause(whereClause, orderBy, offset, defaultLimit);
445 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
446 * @param wc where-Clause
447 * @param ob orderBy-Clause
448 * @param offset ab welchem Datensatz
449 * @param limit wieviele Datensätze
450 * @return EntityList mit den gematchten Entities
451 * @exception StorageObjectException
454 public EntityList selectByWhereClause(String wc, String ob, int offset, int limit)
455 throws StorageObjectException
458 // check o_store for entitylist
459 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
460 StoreIdentifier search_sid =
461 new StoreIdentifier( theEntityClass,
462 StoreContainerType.STOC_TYPE_ENTITYLIST,
463 StoreUtil.getEntityListUniqueIdentifierFor(theTable,wc,ob,offset,limit) );
464 EntityList hit = (EntityList)o_store.use(search_sid);
466 theLog.printDebugInfo("CACHE (hit): " + search_sid.toString());
472 EntityList theReturnList=null;
473 Connection con=null; Statement stmt=null;
475 int offsetCount = 0, count=0;
477 // build sql-statement
479 /** @todo count sql string should only be assembled if we really count
480 * see below at the end of method //rk */
482 if (wc != null && wc.length() == 0) {
485 StringBuffer countSql = new StringBuffer("select count(*) from ").append(theTable);
486 StringBuffer selectSql = new StringBuffer("select * from ").append(theTable);
488 selectSql.append(" where ").append(wc);
489 countSql.append(" where ").append(wc);
491 if (ob != null && !(ob.length() == 0)) {
492 selectSql.append(" order by ").append(ob);
494 if (theAdaptor.hasLimit()) {
495 if (limit > -1 && offset > -1) {
496 selectSql.append(" limit ");
497 if (theAdaptor.reverseLimit()) {
498 selectSql.append(limit).append(",").append(offset);
501 selectSql.append(offset).append(",").append(limit);
508 con = getPooledCon();
509 stmt = con.createStatement();
512 rs = executeSql(stmt, selectSql.toString());
514 if (!evaluatedMetaData) evalMetaData(rs.getMetaData());
516 theReturnList = new EntityList();
517 Entity theResultEntity;
519 theResultEntity = makeEntityFromResultSet(rs);
520 theReturnList.add(theResultEntity);
526 // making entitylist infos
527 if (!(theAdaptor.hasLimit())) count = offsetCount;
529 if (theReturnList != null) {
530 // now we decide if we have to know an overall count...
532 if (limit > -1 && offset > -1) {
533 if (offsetCount==limit) {
534 /** @todo counting should be deffered to entitylist
535 * getSize() should be used */
536 rs = executeSql(stmt, countSql.toString());
538 if ( rs.next() ) count = rs.getInt(1);
541 else theLog.printError("Could not count: " + countSql);
544 theReturnList.setCount(count);
545 theReturnList.setOffset(offset);
546 theReturnList.setWhere(wc);
547 theReturnList.setOrder(ob);
548 theReturnList.setStorage(this);
549 theReturnList.setLimit(limit);
550 if ( offset >= limit )
551 theReturnList.setPrevBatch(offset - limit);
552 if ( offset+offsetCount < count )
553 theReturnList.setNextBatch(offset + limit);
554 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
555 StoreIdentifier sid=theReturnList.getStoreIdentifier();
556 theLog.printDebugInfo("CACHE (add): " + sid.toString());
561 catch (SQLException sqe) { throwSQLException(sqe, "selectByWhereClause"); }
562 finally { freeConnection(con, stmt); }
564 return theReturnList;
569 * Bastelt aus einer Zeile der Datenbank ein EntityObjekt.
571 * @param rs Das ResultSetObjekt.
572 * @return Entity Die Entity.
574 private Entity makeEntityFromResultSet (ResultSet rs)
575 throws StorageObjectException
577 /** @todo OS: get Pkey from ResultSet and consult ObjectStore */
578 HashMap theResultHash = new HashMap();
579 String theResult = null;
581 Entity returnEntity = null;
583 int size = metadataFields.size();
584 for (int i = 0; i < size; i++) {
585 // alle durchlaufen bis nix mehr da
586 theType = metadataTypes[i];
587 if (theType == java.sql.Types.LONGVARBINARY) {
588 InputStreamReader is = (InputStreamReader)rs.getCharacterStream(i + 1);
590 char[] data = new char[32768];
591 StringBuffer theResultString = new StringBuffer();
593 while ((len = is.read(data)) > 0) {
594 theResultString.append(data, 0, len);
597 theResult = theResultString.toString();
604 theResult = getValueAsString(rs, (i + 1), theType);
606 if (theResult != null) {
607 theResultHash.put(metadataFields.get(i), theResult);
610 if (theEntityClass != null) {
611 returnEntity = (Entity)theEntityClass.newInstance();
612 returnEntity.setValues(theResultHash);
613 returnEntity.setStorage(myselfDatabase);
614 if ( returnEntity instanceof StorableObject ) {
615 theLog.printDebugInfo("CACHE: ( in) " + returnEntity.getId() + " :"+theTable);
616 o_store.add(((StorableObject)returnEntity).getStoreIdentifier());
619 throwStorageObjectException("Internal Error: theEntityClass not set!");
621 } catch (IllegalAccessException e) {
622 throwStorageObjectException("Kein Zugriff! -- " + e.toString());
623 } catch (IOException e) {
624 throwStorageObjectException("IOException! -- " + e.toString());
625 } catch (InstantiationException e) {
626 throwStorageObjectException("Keine Instantiiierung! -- " + e.toString());
627 } catch (SQLException sqe) {
628 throwSQLException(sqe, "makeEntityFromResultSet");
635 * insert-Operator: fügt eine Entity in die Tabelle ein. Eine Spalte WEBDB_CREATE
636 * wird automatisch mit dem aktuellen Datum gefuellt.
639 * @return der Wert des Primary-keys der eingefügten Entity
641 public String insert (Entity theEntity) throws StorageObjectException {
643 invalidatePopupCache();
645 // invalidating all EntityLists corresponding with theEntityClass
646 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
647 StoreContainerType stoc_type =
648 StoreContainerType.valueOf( theEntityClass,
649 StoreContainerType.STOC_TYPE_ENTITYLIST);
650 o_store.invalidate(stoc_type);
653 String returnId = null;
654 Connection con = null; PreparedStatement pstmt = null;
657 ArrayList streamedInput = theEntity.streamedInput();
658 StringBuffer f = new StringBuffer();
659 StringBuffer v = new StringBuffer();
660 String aField, aValue;
661 boolean firstField = true;
663 for (int i = 0; i < getFields().size(); i++) {
664 aField = (String)getFields().get(i);
665 if (!aField.equals(thePKeyName)) {
668 if (aField.equals("webdb_create")) {
672 if (streamedInput != null && streamedInput.contains(aField)) {
676 if (theEntity.hasValueForField(aField)) {
677 aValue = "'" + StringUtil.quote((String)theEntity.getValue(aField))
682 // wenn Wert gegeben, dann einbauen
683 if (aValue != null) {
684 if (firstField == false) {
697 StringBuffer sqlBuf = new StringBuffer("insert into ").append(theTable).append("(").append(f).append(") values (").append(v).append(")");
698 String sql = sqlBuf.toString();
699 theLog.printInfo("INSERT: " + sql);
700 con = getPooledCon();
701 con.setAutoCommit(false);
702 pstmt = con.prepareStatement(sql);
703 if (streamedInput != null) {
704 for (int i = 0; i < streamedInput.size(); i++) {
705 String inputString = (String)theEntity.getValue((String)streamedInput.get(i));
706 pstmt.setBytes(i + 1, inputString.getBytes());
709 int ret = pstmt.executeUpdate();
714 pstmt = con.prepareStatement(theAdaptor.getLastInsertSQL((Database)myselfDatabase));
715 ResultSet rs = pstmt.executeQuery();
717 returnId = rs.getString(1);
718 theEntity.setId(returnId);
719 } catch (SQLException sqe) {
720 throwSQLException(sqe, "insert");
723 con.setAutoCommit(true);
724 } catch (Exception e) {
727 freeConnection(con, pstmt);
729 /** @todo store entity in o_store */
734 * update-Operator: aktualisiert eine Entity. Eine Spalte WEBDB_LASTCHANGE
735 * wird automatisch mit dem aktuellen Datum gefuellt.
739 public void update (Entity theEntity) throws StorageObjectException
741 Connection con = null; PreparedStatement pstmt = null;
742 /** @todo this is stupid: why do we prepare statement, when we
743 * throw it away afterwards. should be regular statement
744 * update/insert could better be one routine called save()
745 * that chooses to either insert or update depending if we
746 * have a primary key in the entity. i don't know if we
747 * still need the streamed input fields. // rk */
749 /** @todo extension: check if Entity did change, otherwise we don't need
750 * the roundtrip to the database */
752 /** invalidating corresponding entitylists in o_store*/
753 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
754 StoreContainerType stoc_type =
755 StoreContainerType.valueOf( theEntityClass,
756 StoreContainerType.STOC_TYPE_ENTITYLIST);
757 o_store.invalidate(stoc_type);
760 ArrayList streamedInput = theEntity.streamedInput();
761 String id = theEntity.getId();
763 StringBuffer fv = new StringBuffer();
764 boolean firstField = true;
766 invalidatePopupCache();
767 // build sql statement
768 for (int i = 0; i < getFields().size(); i++) {
769 aField = (String)metadataFields.get(i);
771 if (!(aField.equals(thePKeyName) || aField.equals("webdb_create") ||
772 aField.equals("webdb_lastchange") || (streamedInput != null && streamedInput.contains(aField)))) {
773 if (theEntity.hasValueForField(aField)) {
774 if (firstField == false) {
780 fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getValue(aField))).append("'");
784 StringBuffer sql = new StringBuffer("update ").append(theTable).append(" set ").append(fv);
786 if (metadataFields.contains("webdb_lastchange")) {
787 sql.append(",webdb_lastchange=NOW()");
789 if (metadataFields.contains("webdb_create") &&
790 theEntity.hasValueForField("webdb_create")) {
793 java.util.Date d = _dateFormatterIn.parse(
794 theEntity.getValue("webdb_create"));
795 Timestamp tStamp = new Timestamp(d.getTime());
796 sql.append(",webdb_create='"+tStamp.toString()+"'");
797 } catch (ParseException e) {
798 throw new StorageObjectException(e.toString());
801 if (streamedInput != null) {
802 for (int i = 0; i < streamedInput.size(); i++) {
803 sql.append(",").append(streamedInput.get(i)).append("=?");
806 sql.append(" where id=").append(id);
807 theLog.printInfo("UPDATE: " + sql);
810 con = getPooledCon();
811 con.setAutoCommit(false);
812 pstmt = con.prepareStatement(sql.toString());
813 if (streamedInput != null) {
814 for (int i = 0; i < streamedInput.size(); i++) {
815 String inputString = theEntity.getValue((String)streamedInput.get(i));
816 pstmt.setBytes(i + 1, inputString.getBytes());
819 pstmt.executeUpdate();
820 } catch (SQLException sqe) {
821 throwSQLException(sqe, "update");
824 con.setAutoCommit(true);
825 } catch (Exception e) {
828 freeConnection(con, pstmt);
834 * @param id des zu loeschenden Datensatzes
835 * @return boolean liefert true zurueck, wenn loeschen erfolgreich war.
837 public boolean delete (String id) throws StorageObjectException {
839 invalidatePopupCache();
840 // ostore send notification
841 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
842 String uniqueId = id;
843 if ( theEntityClass.equals(StorableObjectEntity.class) )
844 uniqueId+="@"+theTable;
845 theLog.printInfo("CACHE: (del) " + id);
846 StoreIdentifier search_sid =
847 new StoreIdentifier(theEntityClass, StoreContainerType.STOC_TYPE_ENTITY, uniqueId);
848 o_store.invalidate(search_sid);
851 /** @todo could be prepared Statement */
852 Statement stmt = null; Connection con = null;
854 String sql="delete from "+theTable+" where "+thePKeyName+"='"+id+"'";
855 theLog.printInfo("DELETE " + sql);
857 con = getPooledCon(); stmt = con.createStatement();
858 res = stmt.executeUpdate(sql);
860 catch (SQLException sqe) { throwSQLException(sqe, "delete"); }
861 finally { freeConnection(con, stmt); }
863 return (res > 0) ? true : false;
866 /* noch nicht implementiert.
867 * @return immer false
869 public boolean delete (EntityList theEntityList) {
870 invalidatePopupCache();
875 * Diese Methode sollte ueberschrieben werden, wenn fuer die abgeleitete Database-Klasse
876 * eine SimpleList mit Standard-Popupdaten erzeugt werden koennen soll.
879 public SimpleList getPopupData () throws StorageObjectException {
884 * Holt Daten fuer Popups.
885 * @param name Name des Feldes.
886 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
887 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
889 public SimpleList getPopupData (String name, boolean hasNullValue)
890 throws StorageObjectException {
891 return getPopupData(name, hasNullValue, null);
895 * Holt Daten fuer Popups.
896 * @param name Name des Feldes.
897 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
898 * @param where Schraenkt die Selektion der Datensaetze ein.
899 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
901 public SimpleList getPopupData (String name, boolean hasNullValue, String where) throws StorageObjectException {
902 return getPopupData(name, hasNullValue, where, null);
906 * Holt Daten fuer Popups.
907 * @param name Name des Feldes.
908 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
909 * @param where Schraenkt die Selektion der Datensaetze ein.
910 * @param order Gibt ein Feld als Sortierkriterium an.
911 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
913 public SimpleList getPopupData (String name, boolean hasNullValue, String where, String order) throws StorageObjectException {
915 if (hasPopupCache && popupCache != null)
917 SimpleList simpleList = null;
918 Connection con = null;
919 Statement stmt = null;
921 StringBuffer sql = new StringBuffer("select ").append(thePKeyName)
922 .append(",").append(name).append(" from ")
924 if (where != null && !(where.length() == 0))
925 sql.append(" where ").append(where);
926 sql.append(" order by ");
927 if (order != null && !(order.length() == 0))
933 con = getPooledCon();
934 } catch (Exception e) {
935 throw new StorageObjectException(e.toString());
938 stmt = con.createStatement();
939 ResultSet rs = executeSql(stmt, sql.toString());
942 if (!evaluatedMetaData) get_meta_data();
943 simpleList = new SimpleList();
944 // if popup has null-selector
945 if (hasNullValue) simpleList.add(POPUP_EMTYLINE);
947 SimpleHash popupDict;
949 popupDict = new SimpleHash();
950 popupDict.put("key", getValueAsString(rs, 1, thePKeyType));
951 popupDict.put("value", rs.getString(2));
952 simpleList.add(popupDict);
956 } catch (Exception e) {
957 theLog.printError("getPopupData: "+e.toString());
958 throw new StorageObjectException(e.toString());
960 freeConnection(con, stmt);
963 if (hasPopupCache) popupCache = simpleList;
968 * Liefert alle Daten der Tabelle als SimpleHash zurueck. Dies wird verwandt,
969 * wenn in den Templates ein Lookup-Table benoetigt wird. Sollte nur bei kleinen
970 * Tabellen Verwendung finden.
971 * @return SimpleHash mit den Tabellezeilen.
973 public SimpleHash getHashData () {
974 /** @todo dangerous! this should have a flag to be enabled, otherwise
975 * very big Hashes could be returned */
976 if (hashCache == null) {
978 hashCache = HTMLTemplateProcessor.makeSimpleHash(selectByWhereClause("",
980 } catch (StorageObjectException e) {
981 theLog.printDebugInfo(e.toString());
987 /* invalidates the popupCache
989 protected void invalidatePopupCache () {
990 /** @todo invalidates toooo much */
996 * Diese Methode fuehrt den Sqlstring <i>sql</i> aus und timed im Logfile.
997 * @param stmt Statemnt
998 * @param sql Sql-String
1000 * @exception StorageObjectException
1002 public ResultSet executeSql (Statement stmt, String sql)
1003 throws StorageObjectException, SQLException
1005 long startTime = System.currentTimeMillis();
1008 rs = stmt.executeQuery(sql);
1009 theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: "
1012 catch (SQLException e)
1014 theLog.printDebugInfo("Failed: " + (System.currentTimeMillis()
1015 - startTime) + "ms. for: "+ sql);
1023 * Fuehrt Statement stmt aus und liefert Resultset zurueck. Das SQL-Statment wird
1024 * getimed und geloggt.
1025 * @param stmt PreparedStatement mit der SQL-Anweisung
1026 * @return Liefert ResultSet des Statements zurueck.
1027 * @exception StorageObjectException, SQLException
1029 public ResultSet executeSql (PreparedStatement stmt)
1030 throws StorageObjectException, SQLException {
1032 long startTime = (new java.util.Date()).getTime();
1033 ResultSet rs = stmt.executeQuery();
1034 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms.");
1039 * returns the number of rows in the table
1041 public int getSize(String where)
1042 throws SQLException,StorageObjectException
1044 long startTime = System.currentTimeMillis();
1045 String sql = "SELECT count(*) FROM "+ theTable + " where " + where;
1046 Connection con = null;
1047 Statement stmt = null;
1051 con = getPooledCon();
1052 stmt = con.createStatement();
1053 ResultSet rs = executeSql(stmt,sql);
1055 result = rs.getInt(1);
1057 } catch (SQLException e) {
1058 theLog.printError(e.toString());
1060 freeConnection(con,stmt);
1062 //theLog.printInfo(theTable + " has "+ result +" rows where " + where);
1063 theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: "
1068 public int executeUpdate(Statement stmt, String sql)
1069 throws StorageObjectException, SQLException
1072 long startTime = (new java.util.Date()).getTime();
1075 rs = stmt.executeUpdate(sql);
1076 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: "
1079 catch (SQLException e)
1081 theLog.printDebugInfo("Failed: " + (new java.util.Date().getTime()
1082 - startTime) + "ms. for: "+ sql);
1088 public int executeUpdate(String sql)
1089 throws StorageObjectException, SQLException
1092 long startTime = (new java.util.Date()).getTime();
1093 Connection con=null;PreparedStatement pstmt=null;
1096 pstmt = con.prepareStatement(sql);
1097 result = pstmt.executeUpdate();
1099 catch (Exception e) {
1100 theLog.printDebugInfo("settimage :: setImage gescheitert: "+e.toString());
1101 throw new StorageObjectException("executeUpdate failed: "+e.toString());
1103 finally { freeConnection(con,pstmt); }
1104 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: "
1110 * Wertet ResultSetMetaData aus und setzt interne Daten entsprechend
1111 * @param md ResultSetMetaData
1112 * @exception StorageObjectException
1114 private void evalMetaData (ResultSetMetaData md)
1115 throws StorageObjectException {
1117 this.evaluatedMetaData = true;
1118 this.metadataFields = new ArrayList();
1119 this.metadataLabels = new ArrayList();
1120 this.metadataNotNullFields = new ArrayList();
1122 int numFields = md.getColumnCount();
1123 this.metadataTypes = new int[numFields];
1126 for (int i = 1; i <= numFields; i++) {
1127 aField = md.getColumnName(i);
1128 metadataFields.add(aField);
1129 metadataLabels.add(md.getColumnLabel(i));
1130 aType = md.getColumnType(i);
1131 metadataTypes[i - 1] = aType;
1132 if (aField.equals(thePKeyName)) {
1133 thePKeyType = aType; thePKeyIndex = i;
1135 if (md.isNullable(i) == md.columnNullable) {
1136 metadataNotNullFields.add(aField);
1139 } catch (SQLException e) {
1140 throwSQLException(e, "evalMetaData");
1145 * Wertet die Metadaten eines Resultsets fuer eine Tabelle aus,
1146 * um die alle Columns und Typen einer Tabelle zu ermitteln.
1148 private void get_meta_data () throws StorageObjectException {
1149 Connection con = null;
1150 PreparedStatement pstmt = null;
1151 String sql = "select * from " + theTable + " where 0=1";
1153 con = getPooledCon();
1154 pstmt = con.prepareStatement(sql);
1155 theLog.printInfo("METADATA: " + sql);
1156 ResultSet rs = pstmt.executeQuery();
1157 evalMetaData(rs.getMetaData());
1159 } catch (SQLException e) {
1160 throwSQLException(e, "get_meta_data");
1162 freeConnection(con, pstmt);
1167 public Connection getPooledCon() throws StorageObjectException {
1168 /* @todo , doublecheck but I'm pretty sure that this is unnecessary. -mh
1170 Class.forName("com.codestudio.sql.PoolMan").newInstance();
1171 } catch (Exception e){
1172 throw new StorageObjectException("Could not find the PoolMan Driver"
1175 Connection con = null;
1177 con = SQLManager.getInstance().requestConnection();
1178 } catch(SQLException e){
1179 theLog.printError("could not connect to the database "+e.toString());
1180 System.err.println("could not connect to the database "+e.toString());
1181 throw new StorageObjectException("Could not connect to the database"+
1187 public void freeConnection (Connection con, Statement stmt)
1188 throws StorageObjectException {
1189 SQLManager.getInstance().closeStatement(stmt);
1190 SQLManager.getInstance().returnConnection(con);
1194 * Wertet SQLException aus und wirft dannach eine StorageObjectException
1195 * @param sqe SQLException
1196 * @param wo Funktonsname, in der die SQLException geworfen wurde
1197 * @exception StorageObjectException
1199 protected void throwSQLException (SQLException sqe, String wo)
1200 throws StorageObjectException {
1202 String message = "";
1205 state = sqe.getSQLState();
1206 message = sqe.getMessage();
1207 vendor = sqe.getErrorCode();
1209 theLog.printError(state + ": " + vendor + " : " + message + " Funktion: "
1211 throw new StorageObjectException((sqe == null) ? "undefined sql exception" :
1215 protected void _throwStorageObjectException (Exception e, String wo)
1216 throws StorageObjectException {
1218 theLog.printError(e.toString()+ wo);
1219 throw new StorageObjectException(wo + e.toString());
1221 theLog.printError(wo);
1222 throw new StorageObjectException(wo);
1228 * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach
1229 * eine StorageObjectException
1230 * @param message Nachricht mit dem Fehler
1231 * @exception StorageObjectException
1233 void throwStorageObjectException (String message)
1234 throws StorageObjectException {
1235 _throwStorageObjectException(null, message);