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.20 $ $Date: 2002/07/21 22:32:25 $
31 * @author $Author: mh $
33 * $Log: Database.java,v $
34 * Revision 1.20 2002/07/21 22:32:25 mh
35 * on insert, the "webdb_lastchange" field should get a value
37 * Revision 1.19 2002/06/29 15:44:46 mh
38 * make the webdb_create update be called webdb_create_update. it breaks things otherwise. a fixme case I know..
40 * Revision 1.18 2002/06/28 20:42:13 mh
41 * added necessary bits in templates and Database.java to make webdb_create modifiable. make the conversion from sql/Timestamp to String more robust
45 public class Database implements StorageObject {
47 protected String theTable;
48 protected String theCoreTable=null;
49 protected String thePKeyName="id";
50 protected int thePKeyType, thePKeyIndex;
51 protected boolean evaluatedMetaData=false;
52 protected ArrayList metadataFields,metadataLabels,
53 metadataNotNullFields;
54 protected int[] metadataTypes;
55 protected Class theEntityClass;
56 protected StorageObject myselfDatabase;
57 protected SimpleList popupCache=null;
58 protected boolean hasPopupCache = false;
59 protected SimpleHash hashCache=null;
60 protected boolean hasTimestamp=true;
61 private String database_driver, database_url;
62 private int defaultLimit;
63 protected DatabaseAdaptor theAdaptor;
64 protected Logfile theLog;
65 private static Class GENERIC_ENTITY_CLASS=null,
66 STORABLE_OBJECT_ENTITY_CLASS=null;
67 private static SimpleHash POPUP_EMTYLINE=new SimpleHash();
68 protected static final ObjectStore o_store=ObjectStore.getInstance();
69 private SimpleDateFormat _dateFormatterOut =
70 new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
71 private SimpleDateFormat _dateFormatterIn =
72 new SimpleDateFormat("yyyy-MM-dd");
73 private Calendar _cal = new GregorianCalendar();
75 private static final int _millisPerHour = 60 * 60 * 1000;
76 private static final int _millisPerMinute = 60 * 1000;
79 // always same object saves a little space
80 POPUP_EMTYLINE.put("key", ""); POPUP_EMTYLINE.put("value", "--");
82 GENERIC_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity");
83 STORABLE_OBJECT_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity");
86 System.err.println("FATAL: Database.java could not initialize" + e.toString());
92 * Kontruktor bekommt den Filenamen des Konfigurationsfiles übergeben.
93 * Aus diesem file werden <code>Database.Logfile</code>,
94 * <code>Database.Username</code>,<code>Database.Password</code>,
95 * <code>Database.Host</code> und <code>Database.Adaptor</code>
96 * ausgelesen und ein Broker für die Verbindugen zur Datenbank
99 * @param String confFilename Dateiname der Konfigurationsdatei
101 public Database() throws StorageObjectException {
102 theLog = Logfile.getInstance(MirConfig.getProp("Home")+
103 MirConfig.getProp("Database.Logfile"));
104 String theAdaptorName=MirConfig.getProp("Database.Adaptor");
105 defaultLimit = Integer.parseInt(MirConfig.getProp("Database.Limit"));
107 theEntityClass = GENERIC_ENTITY_CLASS;
108 theAdaptor = (DatabaseAdaptor)Class.forName(theAdaptorName).newInstance();
109 } catch (Exception e){
110 theLog.printError("Error in Database() constructor with "+
111 theAdaptorName + " -- " +e.toString());
112 throw new StorageObjectException("Error in Database() constructor with "
115 /*String database_username=MirConfig.getProp("Database.Username");
116 String database_password=MirConfig.getProp("Database.Password");
117 String database_host=MirConfig.getProp("Database.Host");
119 database_driver=theAdaptor.getDriver();
120 database_url=theAdaptor.getURL(database_username,database_password,
122 theLog.printDebugInfo("adding Broker with: " +database_driver+":"+
124 MirConfig.addBroker(database_driver,database_url);
125 //myBroker=MirConfig.getBroker();
130 * Liefert die Entity-Klasse zurück, in der eine Datenbankzeile gewrappt
131 * wird. Wird die Entity-Klasse durch die erbende Klasse nicht überschrieben,
132 * wird eine mir.entity.GenericEntity erzeugt.
134 * @return Class-Objekt der Entity
136 public java.lang.Class getEntityClass () {
137 return theEntityClass;
141 * Liefert die Standardbeschränkung von select-Statements zurück, also
142 * wieviel Datensätze per Default selektiert werden.
144 * @return Standard-Anzahl der Datensätze
146 public int getLimit () {
151 * Liefert den Namen des Primary-Keys zurück. Wird die Variable nicht von
152 * der erbenden Klasse überschrieben, so ist der Wert <code>PKEY</code>
153 * @return Name des Primary-Keys
155 public String getIdName () {
160 * Liefert den Namen der Tabelle, auf das sich das Datenbankobjekt bezieht.
162 * @return Name der Tabelle
164 public String getTableName () {
169 * Dient dazu vererbte Tabellen bei objectrelationalen DBMS
170 * zu speichern, wenn die id einer Tabelle in der parenttabelle verwaltet
172 * @return liefert theCoreTabel als String zurueck, wenn gesetzt, sonst
176 public String getCoreTable(){
177 if (theCoreTable!=null) return theCoreTable;
178 else return theTable;
182 * Liefert Feldtypen der Felder der Tabelle zurueck (s.a. java.sql.Types)
183 * @return int-Array mit den Typen der Felder
184 * @exception StorageObjectException
186 public int[] getTypes () throws StorageObjectException {
187 if (metadataTypes == null)
189 return metadataTypes;
193 * Liefert eine Liste der Labels der Tabellenfelder
194 * @return ArrayListe mit Labeln
195 * @exception StorageObjectException
197 public ArrayList getLabels () throws StorageObjectException {
198 if (metadataLabels == null)
200 return metadataLabels;
204 * Liefert eine Liste der Felder der Tabelle
205 * @return ArrayList mit Feldern
206 * @exception StorageObjectException
208 public ArrayList getFields () throws StorageObjectException {
209 if (metadataFields == null)
211 return metadataFields;
216 * Gets value out of ResultSet according to type and converts to String
217 * @param inValue Wert aus ResultSet.
218 * @param aType Datenbanktyp.
219 * @return liefert den Wert als String zurueck. Wenn keine Umwandlung moeglich
220 * dann /unsupported value/
222 private String getValueAsString (ResultSet rs, int valueIndex, int aType) throws StorageObjectException {
223 String outValue = null;
227 case java.sql.Types.BIT:
228 outValue = (rs.getBoolean(valueIndex) == true) ? "1" : "0";
230 case java.sql.Types.INTEGER:case java.sql.Types.SMALLINT:case java.sql.Types.TINYINT:case java.sql.Types.BIGINT:
231 int out = rs.getInt(valueIndex);
233 outValue = new Integer(out).toString();
235 case java.sql.Types.NUMERIC:
236 /** @todo Numeric can be float or double depending upon
237 * metadata.getScale() / especially with oracle */
238 long outl = rs.getLong(valueIndex);
240 outValue = new Long(outl).toString();
242 case java.sql.Types.REAL:
243 float tempf = rs.getFloat(valueIndex);
247 int tempf_int = (int)tempf;
248 tempf = (float)tempf_int;
250 outValue = "" + tempf;
251 outValue = outValue.replace('.', ',');
254 case java.sql.Types.DOUBLE:
255 double tempd = rs.getDouble(valueIndex);
259 int tempd_int = (int)tempd;
260 tempd = (double)tempd_int;
262 outValue = "" + tempd;
263 outValue = outValue.replace('.', ',');
266 case java.sql.Types.CHAR:case java.sql.Types.VARCHAR:case java.sql.Types.LONGVARCHAR:
267 outValue = rs.getString(valueIndex);
268 //if (outValue != null)
269 //outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue));
271 case java.sql.Types.LONGVARBINARY:
272 outValue = rs.getString(valueIndex);
273 //if (outValue != null)
274 //outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue));
276 case java.sql.Types.TIMESTAMP:
277 // it's important to use Timestamp here as getting it
278 // as a string is undefined and is only there for debugging
279 // according to the API. we can make it a string through formatting.
281 Timestamp timestamp = (rs.getTimestamp(valueIndex));
283 java.util.Date date = new java.util.Date(timestamp.getTime());
284 outValue = _dateFormatterOut.format(date);
286 int offset = _cal.get(Calendar.ZONE_OFFSET)+
287 _cal.get(Calendar.DST_OFFSET);
288 String tzOffset = StringUtil.zeroPaddingNumber(
289 offset/_millisPerHour,2,2);
290 outValue = outValue+"+"+tzOffset;
294 outValue = "<unsupported value>";
295 theLog.printWarning("Unsupported Datatype: at " + valueIndex +
298 } catch (SQLException e) {
299 throw new StorageObjectException("Could not get Value out of Resultset -- "
307 * select-Operator um einen Datensatz zu bekommen.
308 * @param id Primaerschluessel des Datensatzes.
309 * @return liefert EntityObject des gefundenen Datensatzes oder null.
311 public Entity selectById(String id) throws StorageObjectException
313 if (id==null||id.equals(""))
314 throw new StorageObjectException("id war null");
316 // ask object store for object
317 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
318 String uniqueId = id;
319 if ( theEntityClass.equals(StorableObjectEntity.class) )
320 uniqueId+="@"+theTable;
321 StoreIdentifier search_sid = new StoreIdentifier(theEntityClass, uniqueId);
322 theLog.printDebugInfo("CACHE: (dbg) looking for sid " + search_sid.toString());
323 Entity hit = (Entity)o_store.use(search_sid);
324 if ( hit!=null ) return hit;
327 Statement stmt=null;Connection con=getPooledCon();
328 Entity returnEntity=null;
331 /** @todo better prepared statement */
332 String selectSql = "select * from " + theTable + " where " + thePKeyName + "=" + id;
333 stmt = con.createStatement();
334 rs = executeSql(stmt, selectSql);
336 if (evaluatedMetaData==false) evalMetaData(rs.getMetaData());
338 returnEntity = makeEntityFromResultSet(rs);
339 else theLog.printDebugInfo("Keine daten fuer id: " + id + "in Tabelle" + theTable);
343 theLog.printDebugInfo("No Data for Id " + id + " in Table " + theTable);
346 catch (SQLException sqe){
347 throwSQLException(sqe,"selectById"); return null;
349 catch (NumberFormatException e) {
350 theLog.printError("ID ist keine Zahl: " + id);
352 finally { freeConnection(con,stmt); }
354 /** @todo OS: Entity should be saved in ostore */
360 * select-Operator um Datensaetze zu bekommen, die key = value erfuellen.
361 * @param key Datenbankfeld der Bedingung.
362 * @param value Wert die der key anehmen muss.
363 * @return EntityList mit den gematchten Entities
365 public EntityList selectByFieldValue(String aField, String aValue)
366 throws StorageObjectException
368 return selectByFieldValue(aField, aValue, 0);
372 * select-Operator um Datensaetze zu bekommen, die key = value erfuellen.
373 * @param key Datenbankfeld der Bedingung.
374 * @param value Wert die der key anehmen muss.
375 * @param offset Gibt an ab welchem Datensatz angezeigt werden soll.
376 * @return EntityList mit den gematchten Entities
378 public EntityList selectByFieldValue(String aField, String aValue, int offset)
379 throws StorageObjectException
381 return selectByWhereClause(aField + "=" + aValue, offset);
386 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
387 * Also offset wird der erste Datensatz genommen.
389 * @param wc where-Clause
390 * @return EntityList mit den gematchten Entities
391 * @exception StorageObjectException
393 public EntityList selectByWhereClause(String where)
394 throws StorageObjectException
396 return selectByWhereClause(where, 0);
401 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
402 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
404 * @param wc where-Clause
405 * @param offset ab welchem Datensatz.
406 * @return EntityList mit den gematchten Entities
407 * @exception StorageObjectException
409 public EntityList selectByWhereClause(String whereClause, int offset)
410 throws StorageObjectException
412 return selectByWhereClause(whereClause, null, offset);
417 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
418 * Also offset wird der erste Datensatz genommen.
419 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
421 * @param wc where-Clause
422 * @param ob orderBy-Clause
423 * @return EntityList mit den gematchten Entities
424 * @exception StorageObjectException
427 public EntityList selectByWhereClause(String where, String order)
428 throws StorageObjectException {
429 return selectByWhereClause(where, order, 0);
434 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
435 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
437 * @param wc where-Clause
438 * @param ob orderBy-Clause
439 * @param offset ab welchem Datensatz
440 * @return EntityList mit den gematchten Entities
441 * @exception StorageObjectException
444 public EntityList selectByWhereClause(String whereClause, String orderBy, int offset)
445 throws StorageObjectException {
446 return selectByWhereClause(whereClause, orderBy, offset, defaultLimit);
451 * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück.
452 * @param wc where-Clause
453 * @param ob orderBy-Clause
454 * @param offset ab welchem Datensatz
455 * @param limit wieviele Datensätze
456 * @return EntityList mit den gematchten Entities
457 * @exception StorageObjectException
460 public EntityList selectByWhereClause(String wc, String ob, int offset, int limit)
461 throws StorageObjectException
464 // check o_store for entitylist
465 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
466 StoreIdentifier search_sid =
467 new StoreIdentifier( theEntityClass,
468 StoreContainerType.STOC_TYPE_ENTITYLIST,
469 StoreUtil.getEntityListUniqueIdentifierFor(theTable,wc,ob,offset,limit) );
470 EntityList hit = (EntityList)o_store.use(search_sid);
472 theLog.printDebugInfo("CACHE (hit): " + search_sid.toString());
478 EntityList theReturnList=null;
479 Connection con=null; Statement stmt=null;
481 int offsetCount = 0, count=0;
483 // build sql-statement
485 /** @todo count sql string should only be assembled if we really count
486 * see below at the end of method //rk */
488 if (wc != null && wc.length() == 0) {
491 StringBuffer countSql = new StringBuffer("select count(*) from ").append(theTable);
492 StringBuffer selectSql = new StringBuffer("select * from ").append(theTable);
494 selectSql.append(" where ").append(wc);
495 countSql.append(" where ").append(wc);
497 if (ob != null && !(ob.length() == 0)) {
498 selectSql.append(" order by ").append(ob);
500 if (theAdaptor.hasLimit()) {
501 if (limit > -1 && offset > -1) {
502 selectSql.append(" limit ");
503 if (theAdaptor.reverseLimit()) {
504 selectSql.append(limit).append(",").append(offset);
507 selectSql.append(offset).append(",").append(limit);
514 con = getPooledCon();
515 stmt = con.createStatement();
518 rs = executeSql(stmt, selectSql.toString());
520 if (!evaluatedMetaData) evalMetaData(rs.getMetaData());
522 theReturnList = new EntityList();
523 Entity theResultEntity;
525 theResultEntity = makeEntityFromResultSet(rs);
526 theReturnList.add(theResultEntity);
532 // making entitylist infos
533 if (!(theAdaptor.hasLimit())) count = offsetCount;
535 if (theReturnList != null) {
536 // now we decide if we have to know an overall count...
538 if (limit > -1 && offset > -1) {
539 if (offsetCount==limit) {
540 /** @todo counting should be deffered to entitylist
541 * getSize() should be used */
542 rs = executeSql(stmt, countSql.toString());
544 if ( rs.next() ) count = rs.getInt(1);
547 else theLog.printError("Could not count: " + countSql);
550 theReturnList.setCount(count);
551 theReturnList.setOffset(offset);
552 theReturnList.setWhere(wc);
553 theReturnList.setOrder(ob);
554 theReturnList.setStorage(this);
555 theReturnList.setLimit(limit);
556 if ( offset >= limit )
557 theReturnList.setPrevBatch(offset - limit);
558 if ( offset+offsetCount < count )
559 theReturnList.setNextBatch(offset + limit);
560 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
561 StoreIdentifier sid=theReturnList.getStoreIdentifier();
562 theLog.printDebugInfo("CACHE (add): " + sid.toString());
567 catch (SQLException sqe) { throwSQLException(sqe, "selectByWhereClause"); }
568 finally { freeConnection(con, stmt); }
570 return theReturnList;
575 * Bastelt aus einer Zeile der Datenbank ein EntityObjekt.
577 * @param rs Das ResultSetObjekt.
578 * @return Entity Die Entity.
580 private Entity makeEntityFromResultSet (ResultSet rs)
581 throws StorageObjectException
583 /** @todo OS: get Pkey from ResultSet and consult ObjectStore */
584 HashMap theResultHash = new HashMap();
585 String theResult = null;
587 Entity returnEntity = null;
589 int size = metadataFields.size();
590 for (int i = 0; i < size; i++) {
591 // alle durchlaufen bis nix mehr da
592 theType = metadataTypes[i];
593 if (theType == java.sql.Types.LONGVARBINARY) {
594 InputStreamReader is = (InputStreamReader)rs.getCharacterStream(i + 1);
596 char[] data = new char[32768];
597 StringBuffer theResultString = new StringBuffer();
599 while ((len = is.read(data)) > 0) {
600 theResultString.append(data, 0, len);
603 theResult = theResultString.toString();
610 theResult = getValueAsString(rs, (i + 1), theType);
612 if (theResult != null) {
613 theResultHash.put(metadataFields.get(i), theResult);
616 if (theEntityClass != null) {
617 returnEntity = (Entity)theEntityClass.newInstance();
618 returnEntity.setValues(theResultHash);
619 returnEntity.setStorage(myselfDatabase);
620 if ( returnEntity instanceof StorableObject ) {
621 theLog.printDebugInfo("CACHE: ( in) " + returnEntity.getId() + " :"+theTable);
622 o_store.add(((StorableObject)returnEntity).getStoreIdentifier());
625 throwStorageObjectException("Internal Error: theEntityClass not set!");
627 } catch (IllegalAccessException e) {
628 throwStorageObjectException("Kein Zugriff! -- " + e.toString());
629 } catch (IOException e) {
630 throwStorageObjectException("IOException! -- " + e.toString());
631 } catch (InstantiationException e) {
632 throwStorageObjectException("Keine Instantiiierung! -- " + e.toString());
633 } catch (SQLException sqe) {
634 throwSQLException(sqe, "makeEntityFromResultSet");
641 * insert-Operator: fügt eine Entity in die Tabelle ein. Eine Spalte WEBDB_CREATE
642 * wird automatisch mit dem aktuellen Datum gefuellt.
645 * @return der Wert des Primary-keys der eingefügten Entity
647 public String insert (Entity theEntity) throws StorageObjectException {
649 invalidatePopupCache();
651 // invalidating all EntityLists corresponding with theEntityClass
652 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
653 StoreContainerType stoc_type =
654 StoreContainerType.valueOf( theEntityClass,
655 StoreContainerType.STOC_TYPE_ENTITYLIST);
656 o_store.invalidate(stoc_type);
659 String returnId = null;
660 Connection con = null; PreparedStatement pstmt = null;
663 ArrayList streamedInput = theEntity.streamedInput();
664 StringBuffer f = new StringBuffer();
665 StringBuffer v = new StringBuffer();
666 String aField, aValue;
667 boolean firstField = true;
669 for (int i = 0; i < getFields().size(); i++) {
670 aField = (String)getFields().get(i);
671 if (!aField.equals(thePKeyName)) {
674 if (aField.equals("webdb_create") ||
675 aField.equals("webdb_lastchange")) {
679 if (streamedInput != null && streamedInput.contains(aField)) {
683 if (theEntity.hasValueForField(aField)) {
684 aValue = "'" + StringUtil.quote((String)theEntity.getValue(aField))
689 // wenn Wert gegeben, dann einbauen
690 if (aValue != null) {
691 if (firstField == false) {
704 StringBuffer sqlBuf = new StringBuffer("insert into ").append(theTable).append("(").append(f).append(") values (").append(v).append(")");
705 String sql = sqlBuf.toString();
706 theLog.printInfo("INSERT: " + sql);
707 con = getPooledCon();
708 con.setAutoCommit(false);
709 pstmt = con.prepareStatement(sql);
710 if (streamedInput != null) {
711 for (int i = 0; i < streamedInput.size(); i++) {
712 String inputString = (String)theEntity.getValue((String)streamedInput.get(i));
713 pstmt.setBytes(i + 1, inputString.getBytes());
716 int ret = pstmt.executeUpdate();
721 pstmt = con.prepareStatement(theAdaptor.getLastInsertSQL((Database)myselfDatabase));
722 ResultSet rs = pstmt.executeQuery();
724 returnId = rs.getString(1);
725 theEntity.setId(returnId);
726 } catch (SQLException sqe) {
727 throwSQLException(sqe, "insert");
730 con.setAutoCommit(true);
731 } catch (Exception e) {
734 freeConnection(con, pstmt);
736 /** @todo store entity in o_store */
741 * update-Operator: aktualisiert eine Entity. Eine Spalte WEBDB_LASTCHANGE
742 * wird automatisch mit dem aktuellen Datum gefuellt.
746 public void update (Entity theEntity) throws StorageObjectException
748 Connection con = null; PreparedStatement pstmt = null;
749 /** @todo this is stupid: why do we prepare statement, when we
750 * throw it away afterwards. should be regular statement
751 * update/insert could better be one routine called save()
752 * that chooses to either insert or update depending if we
753 * have a primary key in the entity. i don't know if we
754 * still need the streamed input fields. // rk */
756 /** @todo extension: check if Entity did change, otherwise we don't need
757 * the roundtrip to the database */
759 /** invalidating corresponding entitylists in o_store*/
760 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
761 StoreContainerType stoc_type =
762 StoreContainerType.valueOf( theEntityClass,
763 StoreContainerType.STOC_TYPE_ENTITYLIST);
764 o_store.invalidate(stoc_type);
767 ArrayList streamedInput = theEntity.streamedInput();
768 String id = theEntity.getId();
770 StringBuffer fv = new StringBuffer();
771 boolean firstField = true;
773 invalidatePopupCache();
774 // build sql statement
775 for (int i = 0; i < getFields().size(); i++) {
776 aField = (String)metadataFields.get(i);
778 if (!(aField.equals(thePKeyName) || aField.equals("webdb_create") ||
779 aField.equals("webdb_lastchange") || (streamedInput != null && streamedInput.contains(aField)))) {
780 if (theEntity.hasValueForField(aField)) {
781 if (firstField == false) {
787 fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getValue(aField))).append("'");
791 StringBuffer sql = new StringBuffer("update ").append(theTable).append(" set ").append(fv);
793 if (metadataFields.contains("webdb_lastchange")) {
794 sql.append(",webdb_lastchange=NOW()");
796 // special case: the webdb_create requires the field in yyyy-mm-dd format
797 // so anything extra will be ignored. which breaks actual updating when a
798 // a change in date is not desired but the values hash has the correct and
799 // full "webdb_create" field and value in it. solution make it so the update
800 // must be called webdb_create_update. a hack I know.. hopefully
801 // we can replace this whole layer soon. -mh
802 if (metadataFields.contains("webdb_create") &&
803 theEntity.hasValueForField("webdb_create_update")) {
806 java.util.Date d = _dateFormatterIn.parse(
807 theEntity.getValue("webdb_create"));
808 Timestamp tStamp = new Timestamp(d.getTime());
809 sql.append(",webdb_create='"+tStamp.toString()+"'");
810 } catch (ParseException e) {
811 throw new StorageObjectException(e.toString());
814 if (streamedInput != null) {
815 for (int i = 0; i < streamedInput.size(); i++) {
816 sql.append(",").append(streamedInput.get(i)).append("=?");
819 sql.append(" where id=").append(id);
820 theLog.printInfo("UPDATE: " + sql);
823 con = getPooledCon();
824 con.setAutoCommit(false);
825 pstmt = con.prepareStatement(sql.toString());
826 if (streamedInput != null) {
827 for (int i = 0; i < streamedInput.size(); i++) {
828 String inputString = theEntity.getValue((String)streamedInput.get(i));
829 pstmt.setBytes(i + 1, inputString.getBytes());
832 pstmt.executeUpdate();
833 } catch (SQLException sqe) {
834 throwSQLException(sqe, "update");
837 con.setAutoCommit(true);
838 } catch (Exception e) {
841 freeConnection(con, pstmt);
847 * @param id des zu loeschenden Datensatzes
848 * @return boolean liefert true zurueck, wenn loeschen erfolgreich war.
850 public boolean delete (String id) throws StorageObjectException {
852 invalidatePopupCache();
853 // ostore send notification
854 if ( StoreUtil.implementsStorableObject(theEntityClass) ) {
855 String uniqueId = id;
856 if ( theEntityClass.equals(StorableObjectEntity.class) )
857 uniqueId+="@"+theTable;
858 theLog.printInfo("CACHE: (del) " + id);
859 StoreIdentifier search_sid =
860 new StoreIdentifier(theEntityClass, StoreContainerType.STOC_TYPE_ENTITY, uniqueId);
861 o_store.invalidate(search_sid);
864 /** @todo could be prepared Statement */
865 Statement stmt = null; Connection con = null;
867 String sql="delete from "+theTable+" where "+thePKeyName+"='"+id+"'";
868 theLog.printInfo("DELETE " + sql);
870 con = getPooledCon(); stmt = con.createStatement();
871 res = stmt.executeUpdate(sql);
873 catch (SQLException sqe) { throwSQLException(sqe, "delete"); }
874 finally { freeConnection(con, stmt); }
876 return (res > 0) ? true : false;
879 /* noch nicht implementiert.
880 * @return immer false
882 public boolean delete (EntityList theEntityList) {
883 invalidatePopupCache();
888 * Diese Methode sollte ueberschrieben werden, wenn fuer die abgeleitete Database-Klasse
889 * eine SimpleList mit Standard-Popupdaten erzeugt werden koennen soll.
892 public SimpleList getPopupData () throws StorageObjectException {
897 * Holt Daten fuer Popups.
898 * @param name Name des Feldes.
899 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
900 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
902 public SimpleList getPopupData (String name, boolean hasNullValue)
903 throws StorageObjectException {
904 return getPopupData(name, hasNullValue, null);
908 * Holt Daten fuer Popups.
909 * @param name Name des Feldes.
910 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
911 * @param where Schraenkt die Selektion der Datensaetze ein.
912 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
914 public SimpleList getPopupData (String name, boolean hasNullValue, String where) throws StorageObjectException {
915 return getPopupData(name, hasNullValue, where, null);
919 * Holt Daten fuer Popups.
920 * @param name Name des Feldes.
921 * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt.
922 * @param where Schraenkt die Selektion der Datensaetze ein.
923 * @param order Gibt ein Feld als Sortierkriterium an.
924 * @return SimpleList Gibt freemarker.template.SimpleList zurueck.
926 public SimpleList getPopupData (String name, boolean hasNullValue, String where, String order) throws StorageObjectException {
928 if (hasPopupCache && popupCache != null)
930 SimpleList simpleList = null;
931 Connection con = null;
932 Statement stmt = null;
934 StringBuffer sql = new StringBuffer("select ").append(thePKeyName)
935 .append(",").append(name).append(" from ")
937 if (where != null && !(where.length() == 0))
938 sql.append(" where ").append(where);
939 sql.append(" order by ");
940 if (order != null && !(order.length() == 0))
946 con = getPooledCon();
947 } catch (Exception e) {
948 throw new StorageObjectException(e.toString());
951 stmt = con.createStatement();
952 ResultSet rs = executeSql(stmt, sql.toString());
955 if (!evaluatedMetaData) get_meta_data();
956 simpleList = new SimpleList();
957 // if popup has null-selector
958 if (hasNullValue) simpleList.add(POPUP_EMTYLINE);
960 SimpleHash popupDict;
962 popupDict = new SimpleHash();
963 popupDict.put("key", getValueAsString(rs, 1, thePKeyType));
964 popupDict.put("value", rs.getString(2));
965 simpleList.add(popupDict);
969 } catch (Exception e) {
970 theLog.printError("getPopupData: "+e.toString());
971 throw new StorageObjectException(e.toString());
973 freeConnection(con, stmt);
976 if (hasPopupCache) popupCache = simpleList;
981 * Liefert alle Daten der Tabelle als SimpleHash zurueck. Dies wird verwandt,
982 * wenn in den Templates ein Lookup-Table benoetigt wird. Sollte nur bei kleinen
983 * Tabellen Verwendung finden.
984 * @return SimpleHash mit den Tabellezeilen.
986 public SimpleHash getHashData () {
987 /** @todo dangerous! this should have a flag to be enabled, otherwise
988 * very big Hashes could be returned */
989 if (hashCache == null) {
991 hashCache = HTMLTemplateProcessor.makeSimpleHash(selectByWhereClause("",
993 } catch (StorageObjectException e) {
994 theLog.printDebugInfo(e.toString());
1000 /* invalidates the popupCache
1002 protected void invalidatePopupCache () {
1003 /** @todo invalidates toooo much */
1009 * Diese Methode fuehrt den Sqlstring <i>sql</i> aus und timed im Logfile.
1010 * @param stmt Statemnt
1011 * @param sql Sql-String
1013 * @exception StorageObjectException
1015 public ResultSet executeSql (Statement stmt, String sql)
1016 throws StorageObjectException, SQLException
1018 long startTime = System.currentTimeMillis();
1021 rs = stmt.executeQuery(sql);
1022 theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: "
1025 catch (SQLException e)
1027 theLog.printDebugInfo("Failed: " + (System.currentTimeMillis()
1028 - startTime) + "ms. for: "+ sql);
1036 * Fuehrt Statement stmt aus und liefert Resultset zurueck. Das SQL-Statment wird
1037 * getimed und geloggt.
1038 * @param stmt PreparedStatement mit der SQL-Anweisung
1039 * @return Liefert ResultSet des Statements zurueck.
1040 * @exception StorageObjectException, SQLException
1042 public ResultSet executeSql (PreparedStatement stmt)
1043 throws StorageObjectException, SQLException {
1045 long startTime = (new java.util.Date()).getTime();
1046 ResultSet rs = stmt.executeQuery();
1047 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms.");
1052 * returns the number of rows in the table
1054 public int getSize(String where)
1055 throws SQLException,StorageObjectException
1057 long startTime = System.currentTimeMillis();
1058 String sql = "SELECT count(*) FROM "+ theTable + " where " + where;
1059 Connection con = null;
1060 Statement stmt = null;
1064 con = getPooledCon();
1065 stmt = con.createStatement();
1066 ResultSet rs = executeSql(stmt,sql);
1068 result = rs.getInt(1);
1070 } catch (SQLException e) {
1071 theLog.printError(e.toString());
1073 freeConnection(con,stmt);
1075 //theLog.printInfo(theTable + " has "+ result +" rows where " + where);
1076 theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: "
1081 public int executeUpdate(Statement stmt, String sql)
1082 throws StorageObjectException, SQLException
1085 long startTime = (new java.util.Date()).getTime();
1088 rs = stmt.executeUpdate(sql);
1089 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: "
1092 catch (SQLException e)
1094 theLog.printDebugInfo("Failed: " + (new java.util.Date().getTime()
1095 - startTime) + "ms. for: "+ sql);
1101 public int executeUpdate(String sql)
1102 throws StorageObjectException, SQLException
1105 long startTime = (new java.util.Date()).getTime();
1106 Connection con=null;PreparedStatement pstmt=null;
1109 pstmt = con.prepareStatement(sql);
1110 result = pstmt.executeUpdate();
1112 catch (Exception e) {
1113 theLog.printDebugInfo("settimage :: setImage gescheitert: "+e.toString());
1114 throw new StorageObjectException("executeUpdate failed: "+e.toString());
1116 finally { freeConnection(con,pstmt); }
1117 theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: "
1123 * Wertet ResultSetMetaData aus und setzt interne Daten entsprechend
1124 * @param md ResultSetMetaData
1125 * @exception StorageObjectException
1127 private void evalMetaData (ResultSetMetaData md)
1128 throws StorageObjectException {
1130 this.evaluatedMetaData = true;
1131 this.metadataFields = new ArrayList();
1132 this.metadataLabels = new ArrayList();
1133 this.metadataNotNullFields = new ArrayList();
1135 int numFields = md.getColumnCount();
1136 this.metadataTypes = new int[numFields];
1139 for (int i = 1; i <= numFields; i++) {
1140 aField = md.getColumnName(i);
1141 metadataFields.add(aField);
1142 metadataLabels.add(md.getColumnLabel(i));
1143 aType = md.getColumnType(i);
1144 metadataTypes[i - 1] = aType;
1145 if (aField.equals(thePKeyName)) {
1146 thePKeyType = aType; thePKeyIndex = i;
1148 if (md.isNullable(i) == md.columnNullable) {
1149 metadataNotNullFields.add(aField);
1152 } catch (SQLException e) {
1153 throwSQLException(e, "evalMetaData");
1158 * Wertet die Metadaten eines Resultsets fuer eine Tabelle aus,
1159 * um die alle Columns und Typen einer Tabelle zu ermitteln.
1161 private void get_meta_data () throws StorageObjectException {
1162 Connection con = null;
1163 PreparedStatement pstmt = null;
1164 String sql = "select * from " + theTable + " where 0=1";
1166 con = getPooledCon();
1167 pstmt = con.prepareStatement(sql);
1168 theLog.printInfo("METADATA: " + sql);
1169 ResultSet rs = pstmt.executeQuery();
1170 evalMetaData(rs.getMetaData());
1172 } catch (SQLException e) {
1173 throwSQLException(e, "get_meta_data");
1175 freeConnection(con, pstmt);
1180 public Connection getPooledCon() throws StorageObjectException {
1181 /* @todo , doublecheck but I'm pretty sure that this is unnecessary. -mh
1183 Class.forName("com.codestudio.sql.PoolMan").newInstance();
1184 } catch (Exception e){
1185 throw new StorageObjectException("Could not find the PoolMan Driver"
1188 Connection con = null;
1190 con = SQLManager.getInstance().requestConnection();
1191 } catch(SQLException e){
1192 theLog.printError("could not connect to the database "+e.toString());
1193 System.err.println("could not connect to the database "+e.toString());
1194 throw new StorageObjectException("Could not connect to the database"+
1200 public void freeConnection (Connection con, Statement stmt)
1201 throws StorageObjectException {
1202 SQLManager.getInstance().closeStatement(stmt);
1203 SQLManager.getInstance().returnConnection(con);
1207 * Wertet SQLException aus und wirft dannach eine StorageObjectException
1208 * @param sqe SQLException
1209 * @param wo Funktonsname, in der die SQLException geworfen wurde
1210 * @exception StorageObjectException
1212 protected void throwSQLException (SQLException sqe, String wo)
1213 throws StorageObjectException {
1215 String message = "";
1218 state = sqe.getSQLState();
1219 message = sqe.getMessage();
1220 vendor = sqe.getErrorCode();
1222 theLog.printError(state + ": " + vendor + " : " + message + " Funktion: "
1224 throw new StorageObjectException((sqe == null) ? "undefined sql exception" :
1228 protected void _throwStorageObjectException (Exception e, String wo)
1229 throws StorageObjectException {
1231 theLog.printError(e.toString()+ wo);
1232 throw new StorageObjectException(wo + e.toString());
1234 theLog.printError(wo);
1235 throw new StorageObjectException(wo);
1241 * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach
1242 * eine StorageObjectException
1243 * @param message Nachricht mit dem Fehler
1244 * @exception StorageObjectException
1246 void throwStorageObjectException (String message)
1247 throws StorageObjectException {
1248 _throwStorageObjectException(null, message);