X-Git-Url: http://erislabs.net/gitweb/?a=blobdiff_plain;f=source%2Fmir%2Fstorage%2FDatabase.java;h=43dcad90598398b9bdcecb11f9eefb26c8b5d7a7;hb=aa6c03501a1cca8714ce094d566769540045c1ab;hp=104ae7bd3b89e422376570a248c11517e458f4a6;hpb=77a14035319b72f572dbaacb207cf44ddc8419b6;p=mir.git diff --git a/source/mir/storage/Database.java b/source/mir/storage/Database.java index 104ae7bd..43dcad90 100755 --- a/source/mir/storage/Database.java +++ b/source/mir/storage/Database.java @@ -8,8 +8,11 @@ import java.lang.*; import java.io.*; import java.util.*; import freemarker.template.*; -import com.javaexchange.dbConnectionBroker.*; +import com.codestudio.sql.*; +import com.codestudio.util.*; + import mir.storage.StorageObject; +import mir.storage.store.*; import mir.entity.*; import mir.misc.*; @@ -27,27 +30,41 @@ import mir.misc.*; */ public class Database implements StorageObject { - protected DbConnectionBroker myBroker; protected String theTable; protected String theCoreTable=null; protected String thePKeyName="id"; - protected int thePKeyType; + protected int thePKeyType, thePKeyIndex; protected boolean evaluatedMetaData=false; - protected ArrayList metadataFields,metadataLabels,metadataNotNullFields; + protected ArrayList metadataFields,metadataLabels, + metadataNotNullFields; protected int[] metadataTypes; protected Class theEntityClass; protected StorageObject myselfDatabase; - protected HashMap cache; protected SimpleList popupCache=null; - protected boolean hasPopupCache = false; + protected boolean hasPopupCache = false; protected SimpleHash hashCache=null; protected boolean hasTimestamp=true; - private String database_driver; - private String database_url; + private String database_driver, database_url; private int defaultLimit; - private DatabaseAdaptor theAdaptor; + protected DatabaseAdaptor theAdaptor; protected Logfile theLog; - protected Connection con; + private static Class GENERIC_ENTITY_CLASS=null, + STORABLE_OBJECT_ENTITY_CLASS=null; + private static SimpleHash POPUP_EMTYLINE=new SimpleHash(); + protected static final ObjectStore o_store=ObjectStore.getInstance(); + + static { + // always same object saves a little space + POPUP_EMTYLINE.put("key", ""); POPUP_EMTYLINE.put("value", "--"); + try { + GENERIC_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity"); + STORABLE_OBJECT_ENTITY_CLASS = Class.forName("mir.entity.StorableObjectEntity"); + } + catch (Exception e) { + System.err.println("FATAL: Database.java could not initialize" + e.toString()); + } + } + /** * Kontruktor bekommt den Filenamen des Konfigurationsfiles übergeben. @@ -59,25 +76,32 @@ public class Database implements StorageObject { * * @param String confFilename Dateiname der Konfigurationsdatei */ - public Database() { - theLog = Logfile.getInstance(MirConfig.getProp("Home") + MirConfig.getProp("Database.Logfile")); - String database_username=MirConfig.getProp("Database.Username"); - String database_password=MirConfig.getProp("Database.Password"); - String database_host=MirConfig.getProp("Database.Host"); + public Database() throws StorageObjectException { + theLog = Logfile.getInstance(MirConfig.getProp("Home")+ + MirConfig.getProp("Database.Logfile")); String theAdaptorName=MirConfig.getProp("Database.Adaptor"); + defaultLimit = Integer.parseInt(MirConfig.getProp("Database.Limit")); try { - theEntityClass = Class.forName("mir.entity.GenericEntity"); + theEntityClass = GENERIC_ENTITY_CLASS; theAdaptor = (DatabaseAdaptor)Class.forName(theAdaptorName).newInstance(); - defaultLimit = Integer.parseInt(MirConfig.getProp("Database.Limit")); + } catch (Exception e){ + theLog.printError("Error in Database() constructor with "+ + theAdaptorName + " -- " +e.toString()); + throw new StorageObjectException("Error in Database() constructor with " + +e.toString()); + } + /*String database_username=MirConfig.getProp("Database.Username"); + String database_password=MirConfig.getProp("Database.Password"); + String database_host=MirConfig.getProp("Database.Host"); + try { database_driver=theAdaptor.getDriver(); - database_url=theAdaptor.getURL(database_username,database_password,database_host); - theLog.printDebugInfo("adding Broker with: " +database_driver+":"+database_url ); + database_url=theAdaptor.getURL(database_username,database_password, + database_host); + theLog.printDebugInfo("adding Broker with: " +database_driver+":"+ + database_url ); MirConfig.addBroker(database_driver,database_url); - myBroker=MirConfig.getBroker(); - } - catch (Exception e){ - theLog.printError("Bei Konstruktion von Database() with " + theAdaptorName + " -- " +e.toString()); - } + //myBroker=MirConfig.getBroker(); + }*/ } /** @@ -121,7 +145,8 @@ public class Database implements StorageObject { /* * Dient dazu vererbte Tabellen bei objectrelationalen DBMS - * zu speichern, wenn die id einer Tabelle in der parenttabelle verwaltet wird. + * zu speichern, wenn die id einer Tabelle in der parenttabelle verwaltet + * wird. * @return liefert theCoreTabel als String zurueck, wenn gesetzt, sonst * the Table */ @@ -166,7 +191,7 @@ public class Database implements StorageObject { /* - * Uebersetzt die Datenbankwerte in einen String. + * Gets value out of ResultSet according to type and converts to String * @param inValue Wert aus ResultSet. * @param aType Datenbanktyp. * @return liefert den Wert als String zurueck. Wenn keine Umwandlung moeglich @@ -186,6 +211,8 @@ public class Database implements StorageObject { outValue = new Integer(out).toString(); break; case java.sql.Types.NUMERIC: + /** @todo Numeric can be float or double depending upon + * metadata.getScale() / especially with oracle */ long outl = rs.getLong(valueIndex); if (!rs.wasNull()) outValue = new Long(outl).toString(); @@ -216,13 +243,13 @@ public class Database implements StorageObject { break; case java.sql.Types.CHAR:case java.sql.Types.VARCHAR:case java.sql.Types.LONGVARCHAR: outValue = rs.getString(valueIndex); - if (outValue != null) - outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue)); + //if (outValue != null) + //outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue)); break; case java.sql.Types.LONGVARBINARY: outValue = rs.getString(valueIndex); - if (outValue != null) - outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue)); + //if (outValue != null) + //outValue = StringUtil.encodeHtml(StringUtil.unquote(outValue)); break; case java.sql.Types.TIMESTAMP: Timestamp timestamp = (rs.getTimestamp(valueIndex)); @@ -248,18 +275,27 @@ public class Database implements StorageObject { * @param id Primaerschluessel des Datensatzes. * @return liefert EntityObject des gefundenen Datensatzes oder null. */ - public Entity selectById(String id) - throws StorageObjectException { - + public Entity selectById(String id) throws StorageObjectException + { if (id==null||id.equals("")) - throw new StorageObjectException("id war null"); - if (cache != null && cache.containsKey(id)) - return (Entity)cache.get(id); // wenn cache gesetzt, evtl. kein roundtrip zur Datenbank + throw new StorageObjectException("id war null"); + + // ask object store for object + if ( StoreUtil.implementsStorableObject(theEntityClass) ) { + String uniqueId = id; + if ( theEntityClass.equals(StorableObjectEntity.class) ) + uniqueId+="@"+theTable; + StoreIdentifier search_sid = new StoreIdentifier(theEntityClass, uniqueId); + theLog.printDebugInfo("CACHE: (dbg) looking for sid " + search_sid.toString()); + Entity hit = (Entity)o_store.use(search_sid); + if ( hit!=null ) return hit; + } Statement stmt=null;Connection con=getPooledCon(); Entity returnEntity=null; try { ResultSet rs; + /** @todo better prepared statement */ String selectSql = "select * from " + theTable + " where " + thePKeyName + "=" + id; stmt = con.createStatement(); rs = executeSql(stmt, selectSql); @@ -269,30 +305,33 @@ public class Database implements StorageObject { returnEntity = makeEntityFromResultSet(rs); else theLog.printDebugInfo("Keine daten fuer id: " + id + "in Tabelle" + theTable); rs.close(); - } else { - theLog.printDebugInfo("No Data for Id " + id + " in Table " + theTable); - } - } catch (SQLException sqe){ - throwSQLException(sqe,"selectById"); return null; - } catch (NumberFormatException e) { - theLog.printError("ID ist keine Zahl: " + id); - } finally { - freeConnection(con,stmt); - } + } + else { + theLog.printDebugInfo("No Data for Id " + id + " in Table " + theTable); + } + } + catch (SQLException sqe){ + throwSQLException(sqe,"selectById"); return null; + } + catch (NumberFormatException e) { + theLog.printError("ID ist keine Zahl: " + id); + } + finally { freeConnection(con,stmt); } + /** @todo OS: Entity should be saved in ostore */ return returnEntity; } + /** * select-Operator um Datensaetze zu bekommen, die key = value erfuellen. * @param key Datenbankfeld der Bedingung. * @param value Wert die der key anehmen muss. * @return EntityList mit den gematchten Entities */ - public EntityList selectByFieldValue(String aField, String aValue) - throws StorageObjectException { - + throws StorageObjectException + { return selectByFieldValue(aField, aValue, 0); } @@ -303,10 +342,9 @@ public class Database implements StorageObject { * @param offset Gibt an ab welchem Datensatz angezeigt werden soll. * @return EntityList mit den gematchten Entities */ - public EntityList selectByFieldValue(String aField, String aValue, int offset) - throws StorageObjectException { - + throws StorageObjectException + { return selectByWhereClause(aField + "=" + aValue, offset); } @@ -320,8 +358,8 @@ public class Database implements StorageObject { * @exception StorageObjectException */ public EntityList selectByWhereClause(String where) - throws StorageObjectException { - + throws StorageObjectException + { return selectByWhereClause(where, 0); } @@ -336,11 +374,12 @@ public class Database implements StorageObject { * @exception StorageObjectException */ public EntityList selectByWhereClause(String whereClause, int offset) - throws StorageObjectException { - + throws StorageObjectException + { return selectByWhereClause(whereClause, null, offset); } + /** * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück. * Also offset wird der erste Datensatz genommen. @@ -353,10 +392,11 @@ public class Database implements StorageObject { */ public EntityList selectByWhereClause(String where, String order) - throws StorageObjectException { - + throws StorageObjectException { return selectByWhereClause(where, order, 0); } + + /** * select-Operator liefert eine EntityListe mit den gematchten Datensätzen zurück. * Als maximale Anzahl wird das Limit auf der Konfiguration genommen. @@ -369,8 +409,7 @@ public class Database implements StorageObject { */ public EntityList selectByWhereClause(String whereClause, String orderBy, int offset) - throws StorageObjectException { - + throws StorageObjectException { return selectByWhereClause(whereClause, orderBy, offset, defaultLimit); } @@ -386,18 +425,33 @@ public class Database implements StorageObject { */ public EntityList selectByWhereClause(String wc, String ob, int offset, int limit) - throws StorageObjectException { + throws StorageObjectException + { + + // check o_store for entitylist + if ( StoreUtil.implementsStorableObject(theEntityClass) ) { + StoreIdentifier search_sid = + new StoreIdentifier( theEntityClass, + StoreContainerType.STOC_TYPE_ENTITYLIST, + StoreUtil.getEntityListUniqueIdentifierFor(theTable,wc,ob,offset,limit) ); + EntityList hit = (EntityList)o_store.use(search_sid); + if ( hit!=null ) { + theLog.printDebugInfo("CACHE (hit): " + search_sid.toString()); + return hit; + } + } // local EntityList theReturnList=null; - Connection con=null; - Statement stmt=null; + Connection con=null; Statement stmt=null; ResultSet rs; - int offsetCount = 0; - int count=0; - + int offsetCount = 0, count=0; // build sql-statement + + /** @todo count sql string should only be assembled if we really count + * see below at the end of method //rk */ + if (wc != null && wc.length() == 0) { wc = null; } @@ -422,28 +476,17 @@ public class Database implements StorageObject { } } - // execute sql + // execute sql try { con = getPooledCon(); stmt = con.createStatement(); - // counting rows - if (theAdaptor.hasLimit()) { - rs = executeSql(stmt, countSql.toString()); - if (rs != null) { - if (rs.next()) - count = rs.getInt(1); - rs.close(); - } - else - theLog.printError("Mh. Konnte nicht zaehlen: " + countSql); - } - // hier select + + // selecting... rs = executeSql(stmt, selectSql.toString()); if (rs != null) { + if (!evaluatedMetaData) evalMetaData(rs.getMetaData()); + theReturnList = new EntityList(); - if (evaluatedMetaData == false) { - evalMetaData(rs.getMetaData()); - } Entity theResultEntity; while (rs.next()) { theResultEntity = makeEntityFromResultSet(rs); @@ -452,37 +495,59 @@ public class Database implements StorageObject { } rs.close(); } - // making entitylist - if (!(theAdaptor.hasLimit())) - count = offsetCount; + + // making entitylist infos + if (!(theAdaptor.hasLimit())) count = offsetCount; + if (theReturnList != null) { + // now we decide if we have to know an overall count... + count=offsetCount; + if (limit > -1 && offset > -1) { + if (offsetCount==limit) { + /** @todo counting should be deffered to entitylist + * getSize() should be used */ + rs = executeSql(stmt, countSql.toString()); + if (rs != null) { + if ( rs.next() ) count = rs.getInt(1); + rs.close(); + } + else theLog.printError("Could not count: " + countSql); + } + } theReturnList.setCount(count); theReturnList.setOffset(offset); theReturnList.setWhere(wc); theReturnList.setOrder(ob); - if (offset >= limit) { + theReturnList.setStorage(this); + theReturnList.setLimit(limit); + if ( offset >= limit ) theReturnList.setPrevBatch(offset - limit); - } - if (offset + offsetCount < count) { + if ( offset+offsetCount < count ) theReturnList.setNextBatch(offset + limit); - } + if ( StoreUtil.implementsStorableObject(theEntityClass) ) { + StoreIdentifier sid=theReturnList.getStoreIdentifier(); + theLog.printDebugInfo("CACHE (add): " + sid.toString()); + o_store.add(sid); + } } - } catch (SQLException sqe) { - throwSQLException(sqe, "selectByWhereClause"); - } finally { - freeConnection(con, stmt); } + catch (SQLException sqe) { throwSQLException(sqe, "selectByWhereClause"); } + finally { freeConnection(con, stmt); } + return theReturnList; } + /** * Bastelt aus einer Zeile der Datenbank ein EntityObjekt. * * @param rs Das ResultSetObjekt. * @return Entity Die Entity. */ - - public Entity makeEntityFromResultSet (ResultSet rs) throws StorageObjectException { + private Entity makeEntityFromResultSet (ResultSet rs) + throws StorageObjectException + { + /** @todo OS: get Pkey from ResultSet and consult ObjectStore */ HashMap theResultHash = new HashMap(); String theResult = null; int theType; @@ -516,26 +581,18 @@ public class Database implements StorageObject { theResultHash.put(metadataFields.get(i), theResult); } } - if (cache != null && theResultHash.containsKey(thePKeyName) && cache.containsKey((String)theResultHash.get(thePKeyName))) { - //theLog.printDebugInfo("CACHE: (out) "+ theResultHash.get(thePKeyName)+ " :"+theTable); - returnEntity = (Entity)cache.get((String)theResultHash.get(thePKeyName)); - } - else { - if (theEntityClass != null) { - returnEntity = (Entity)theEntityClass.newInstance(); - returnEntity.setValues(theResultHash); - returnEntity.setStorage(myselfDatabase); - if (cache != null) { - //theLog.printDebugInfo("CACHE: ( in) " + returnEntity.getId() + " :"+theTable); - cache.put(returnEntity.getId(), returnEntity); - } - } - else { - throwStorageObjectException("Interner Fehler theEntityClass nicht gesetzt!"); - } - } - } // try - catch (IllegalAccessException e) { + if (theEntityClass != null) { + returnEntity = (Entity)theEntityClass.newInstance(); + returnEntity.setValues(theResultHash); + returnEntity.setStorage(myselfDatabase); + if ( returnEntity instanceof StorableObject ) { + theLog.printDebugInfo("CACHE: ( in) " + returnEntity.getId() + " :"+theTable); + o_store.add(((StorableObject)returnEntity).getStoreIdentifier()); + } + } else { + throwStorageObjectException("Internal Error: theEntityClass not set!"); + } + } catch (IllegalAccessException e) { throwStorageObjectException("Kein Zugriff! -- " + e.toString()); } catch (IOException e) { throwStorageObjectException("IOException! -- " + e.toString()); @@ -556,13 +613,21 @@ public class Database implements StorageObject { * @return der Wert des Primary-keys der eingefügten Entity */ public String insert (Entity theEntity) throws StorageObjectException { - String returnId = "0"; - Connection con = null; - PreparedStatement pstmt = null; //cache invalidatePopupCache(); - try { - HashMap theEntityValues = theEntity.getValues(); + + // invalidating all EntityLists corresponding with theEntityClass + if ( StoreUtil.implementsStorableObject(theEntityClass) ) { + StoreContainerType stoc_type = + StoreContainerType.valueOf( theEntityClass, + StoreContainerType.STOC_TYPE_ENTITYLIST); + o_store.invalidate(stoc_type); + } + + String returnId = null; + Connection con = null; PreparedStatement pstmt = null; + + try { ArrayList streamedInput = theEntity.streamedInput(); StringBuffer f = new StringBuffer(); StringBuffer v = new StringBuffer(); @@ -582,8 +647,8 @@ public class Database implements StorageObject { aValue = "?"; } else { - if (theEntityValues.containsKey(aField)) { - aValue = "'" + StringUtil.quote((String)theEntityValues.get(aField)) + if (theEntity.hasValueForField(aField)) { + aValue = "'" + StringUtil.quote((String)theEntity.getValue(aField)) + "'"; } } @@ -611,11 +676,15 @@ public class Database implements StorageObject { pstmt = con.prepareStatement(sql); if (streamedInput != null) { for (int i = 0; i < streamedInput.size(); i++) { - String inputString = (String)theEntityValues.get(streamedInput.get(i)); + String inputString = (String)theEntity.getValue((String)streamedInput.get(i)); pstmt.setBytes(i + 1, inputString.getBytes()); } } - pstmt.execute(); + int ret = pstmt.executeUpdate(); + if(ret == 0){ + //insert failed + return null; + } pstmt = con.prepareStatement(theAdaptor.getLastInsertSQL((Database)myselfDatabase)); ResultSet rs = pstmt.executeQuery(); rs.next(); @@ -631,6 +700,7 @@ public class Database implements StorageObject { } freeConnection(con, pstmt); } + /** @todo store entity in o_store */ return returnId; } @@ -640,11 +710,28 @@ public class Database implements StorageObject { * * @param theEntity */ - public void update (Entity theEntity) throws StorageObjectException { - Connection con = null; - PreparedStatement pstmt = null; + public void update (Entity theEntity) throws StorageObjectException + { + Connection con = null; PreparedStatement pstmt = null; + /** @todo this is stupid: why do we prepare statement, when we + * throw it away afterwards. should be regular statement + * update/insert could better be one routine called save() + * that chooses to either insert or update depending if we + * have a primary key in the entity. i don't know if we + * still need the streamed input fields. // rk */ + + /** @todo extension: check if Entity did change, otherwise we don't need + * the roundtrip to the database */ + + /** invalidating corresponding entitylists in o_store*/ + if ( StoreUtil.implementsStorableObject(theEntityClass) ) { + StoreContainerType stoc_type = + StoreContainerType.valueOf( theEntityClass, + StoreContainerType.STOC_TYPE_ENTITYLIST); + o_store.invalidate(stoc_type); + } + ArrayList streamedInput = theEntity.streamedInput(); - HashMap theEntityValues = theEntity.getValues(); String id = theEntity.getId(); String aField; StringBuffer fv = new StringBuffer(); @@ -657,14 +744,14 @@ public class Database implements StorageObject { // only normal cases if (!(aField.equals(thePKeyName) || aField.equals("webdb_create") || aField.equals("webdb_lastchange") || (streamedInput != null && streamedInput.contains(aField)))) { - if (theEntityValues.containsKey(aField)) { + if (theEntity.hasValueForField(aField)) { if (firstField == false) { fv.append(", "); } else { firstField = false; } - fv.append(aField).append("='").append(StringUtil.quote((String)theEntityValues.get(aField))).append("'"); + fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getValue(aField))).append("'"); } } } @@ -687,7 +774,7 @@ public class Database implements StorageObject { pstmt = con.prepareStatement(sql.toString()); if (streamedInput != null) { for (int i = 0; i < streamedInput.size(); i++) { - String inputString = (String)theEntityValues.get(streamedInput.get(i)); + String inputString = theEntity.getValue((String)streamedInput.get(i)); pstmt.setBytes(i + 1, inputString.getBytes()); } } @@ -710,28 +797,31 @@ public class Database implements StorageObject { * @return boolean liefert true zurueck, wenn loeschen erfolgreich war. */ public boolean delete (String id) throws StorageObjectException { - Statement stmt = null; - Connection con = null; - String sql; - int res = 0; - // loeschen des caches + invalidatePopupCache(); - sql = "delete from " + theTable + " where " + thePKeyName + "='" + id + - "'"; + // ostore send notification + if ( StoreUtil.implementsStorableObject(theEntityClass) ) { + String uniqueId = id; + if ( theEntityClass.equals(StorableObjectEntity.class) ) + uniqueId+="@"+theTable; + theLog.printInfo("CACHE: (del) " + id); + StoreIdentifier search_sid = + new StoreIdentifier(theEntityClass, StoreContainerType.STOC_TYPE_ENTITY, uniqueId); + o_store.invalidate(search_sid); + } + + /** @todo could be prepared Statement */ + Statement stmt = null; Connection con = null; + int res = 0; + String sql="delete from "+theTable+" where "+thePKeyName+"='"+id+"'"; theLog.printInfo("DELETE " + sql); try { - con = getPooledCon(); - stmt = con.createStatement(); + con = getPooledCon(); stmt = con.createStatement(); res = stmt.executeUpdate(sql); - } catch (SQLException sqe) { - throwSQLException(sqe, "delete"); - } finally { - freeConnection(con, stmt); - } - if (cache != null) { - theLog.printInfo("CACHE: deleted " + id); - cache.remove(id); } + catch (SQLException sqe) { throwSQLException(sqe, "delete"); } + finally { freeConnection(con, stmt); } + return (res > 0) ? true : false; } @@ -748,7 +838,7 @@ public class Database implements StorageObject { * eine SimpleList mit Standard-Popupdaten erzeugt werden koennen soll. * @return null */ - public SimpleList getPopupData () { + public SimpleList getPopupData () throws StorageObjectException { return null; } @@ -758,7 +848,8 @@ public class Database implements StorageObject { * @param hasNullValue Wenn true wird eine leerer Eintrag fuer die Popups erzeugt. * @return SimpleList Gibt freemarker.template.SimpleList zurueck. */ - public SimpleList getPopupData (String name, boolean hasNullValue) { + public SimpleList getPopupData (String name, boolean hasNullValue) + throws StorageObjectException { return getPopupData(name, hasNullValue, null); } @@ -769,8 +860,8 @@ public class Database implements StorageObject { * @param where Schraenkt die Selektion der Datensaetze ein. * @return SimpleList Gibt freemarker.template.SimpleList zurueck. */ - public SimpleList getPopupData (String name, boolean hasNullValue, String where) { - return getPopupData(name, hasNullValue, where, null); + public SimpleList getPopupData (String name, boolean hasNullValue, String where) throws StorageObjectException { + return getPopupData(name, hasNullValue, where, null); } /** @@ -781,8 +872,7 @@ public class Database implements StorageObject { * @param order Gibt ein Feld als Sortierkriterium an. * @return SimpleList Gibt freemarker.template.SimpleList zurueck. */ - public SimpleList getPopupData (String name, boolean hasNullValue, String where, - String order) { + public SimpleList getPopupData (String name, boolean hasNullValue, String where, String order) throws StorageObjectException { // caching if (hasPopupCache && popupCache != null) return popupCache; @@ -790,7 +880,9 @@ public class Database implements StorageObject { Connection con = null; Statement stmt = null; // build sql - StringBuffer sql = new StringBuffer("select ").append(thePKeyName).append(",").append(name).append(" from ").append(theTable); + StringBuffer sql = new StringBuffer("select ").append(thePKeyName) + .append(",").append(name).append(" from ") + .append(theTable); if (where != null && !(where.length() == 0)) sql.append(" where ").append(where); sql.append(" order by "); @@ -801,19 +893,20 @@ public class Database implements StorageObject { // execute sql try { con = getPooledCon(); + } catch (Exception e) { + throw new StorageObjectException(e.toString()); + } + try { stmt = con.createStatement(); ResultSet rs = executeSql(stmt, sql.toString()); + if (rs != null) { - if (evaluatedMetaData == false) - get_meta_data(); + if (!evaluatedMetaData) get_meta_data(); simpleList = new SimpleList(); + // if popup has null-selector + if (hasNullValue) simpleList.add(POPUP_EMTYLINE); + SimpleHash popupDict; - if (hasNullValue) { - popupDict = new SimpleHash(); - popupDict.put("key", ""); - popupDict.put("value", "--"); - simpleList.add(popupDict); - } while (rs.next()) { popupDict = new SimpleHash(); popupDict.put("key", getValueAsString(rs, 1, thePKeyType)); @@ -823,13 +916,13 @@ public class Database implements StorageObject { rs.close(); } } catch (Exception e) { - theLog.printDebugInfo(e.toString()); + theLog.printError("getPopupData: "+e.toString()); + throw new StorageObjectException(e.toString()); } finally { freeConnection(con, stmt); } - if (hasPopupCache) { - popupCache = simpleList; - } + + if (hasPopupCache) popupCache = simpleList; return simpleList; } @@ -840,6 +933,8 @@ public class Database implements StorageObject { * @return SimpleHash mit den Tabellezeilen. */ public SimpleHash getHashData () { + /** @todo dangerous! this should have a flag to be enabled, otherwise + * very big Hashes could be returned */ if (hashCache == null) { try { hashCache = HTMLTemplateProcessor.makeSimpleHash(selectByWhereClause("", @@ -853,8 +948,7 @@ public class Database implements StorageObject { /* invalidates the popupCache */ - private void invalidatePopupCache () { - + protected void invalidatePopupCache () { /** @todo invalidates toooo much */ popupCache = null; hashCache = null; @@ -865,14 +959,25 @@ public class Database implements StorageObject { * @param stmt Statemnt * @param sql Sql-String * @return ResultSet - * @exception StorageObjectException, SQLException + * @exception StorageObjectException */ - public ResultSet executeSql (Statement stmt, String sql) throws StorageObjectException, - SQLException { - long startTime = (new java.util.Date()).getTime(); - ResultSet rs = stmt.executeQuery(sql); - theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + public ResultSet executeSql (Statement stmt, String sql) + throws StorageObjectException, SQLException + { + long startTime = System.currentTimeMillis(); + ResultSet rs; + try { + rs = stmt.executeQuery(sql); + theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: " + sql); + } + catch (SQLException e) + { + theLog.printDebugInfo("Failed: " + (System.currentTimeMillis() + - startTime) + "ms. for: "+ sql); + throw e; + } + return rs; } @@ -883,23 +988,23 @@ public class Database implements StorageObject { * @return Liefert ResultSet des Statements zurueck. * @exception StorageObjectException, SQLException */ - public ResultSet executeSql (PreparedStatement stmt) throws StorageObjectException, - SQLException { + public ResultSet executeSql (PreparedStatement stmt) + throws StorageObjectException, SQLException { + long startTime = (new java.util.Date()).getTime(); ResultSet rs = stmt.executeQuery(); theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms."); return rs; } - /** + /** * returns the number of rows in the table */ public int getSize(String where) throws SQLException,StorageObjectException { - long startTime = (new java.util.Date()).getTime(); + long startTime = System.currentTimeMillis(); String sql = "SELECT count(*) FROM "+ theTable + " where " + where; - //theLog.printDebugInfo("trying: "+ sql); Connection con = null; Statement stmt = null; int result = 0; @@ -916,18 +1021,29 @@ public class Database implements StorageObject { } finally { freeConnection(con,stmt); } - theLog.printInfo(theTable + " has "+ result +" rows where " + where); - theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + sql); + //theLog.printInfo(theTable + " has "+ result +" rows where " + where); + theLog.printInfo((System.currentTimeMillis() - startTime) + "ms. for: " + + sql); return result; } public int executeUpdate(Statement stmt, String sql) throws StorageObjectException, SQLException { + int rs; long startTime = (new java.util.Date()).getTime(); - //theLog.printDebugInfo("trying: "+ sql); - int rs = stmt.executeUpdate(sql); - theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + sql); + try + { + rs = stmt.executeUpdate(sql); + theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + + sql); + } + catch (SQLException e) + { + theLog.printDebugInfo("Failed: " + (new java.util.Date().getTime() + - startTime) + "ms. for: "+ sql); + throw e; + } return rs; } @@ -942,9 +1058,13 @@ public class Database implements StorageObject { pstmt = con.prepareStatement(sql); result = pstmt.executeUpdate(); } - catch (Exception e) {theLog.printDebugInfo("settimage :: setImage gescheitert: "+e.toString());} - finally { freeConnection(con,pstmt); } - theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + sql); + catch (Exception e) { + theLog.printDebugInfo("settimage :: setImage gescheitert: "+e.toString()); + throw new StorageObjectException("executeUpdate failed: "+e.toString()); + } + finally { freeConnection(con,pstmt); } + theLog.printInfo((new java.util.Date().getTime() - startTime) + "ms. for: " + + sql); return result; } @@ -953,7 +1073,9 @@ public class Database implements StorageObject { * @param md ResultSetMetaData * @exception StorageObjectException */ - private void evalMetaData (ResultSetMetaData md) throws StorageObjectException { + private void evalMetaData (ResultSetMetaData md) + throws StorageObjectException { + this.evaluatedMetaData = true; this.metadataFields = new ArrayList(); this.metadataLabels = new ArrayList(); @@ -970,7 +1092,7 @@ public class Database implements StorageObject { aType = md.getColumnType(i); metadataTypes[i - 1] = aType; if (aField.equals(thePKeyName)) { - thePKeyType = aType; + thePKeyType = aType; thePKeyIndex = i; } if (md.isNullable(i) == md.columnNullable) { metadataNotNullFields.add(aField); @@ -1003,48 +1125,31 @@ public class Database implements StorageObject { } } - /** - * Datenbankverbindung wird geschlossen - */ - public void disconnectPool () { - try { - myBroker.destroy(100); - } catch (SQLException sqe) { - ; - } - } - /** - * Returns Connection-Object out of the PoolBroker. - * - * @return Connection Object. - */ - public Connection getPooledCon () throws StorageObjectException { - if (myBroker != null) { - Connection con = myBroker.getConnection(); - if (con != null) - return con; + public Connection getPooledCon() throws StorageObjectException { + /* @todo , doublecheck but I'm pretty sure that this is unnecessary. -mh + try{ + Class.forName("com.codestudio.sql.PoolMan").newInstance(); + } catch (Exception e){ + throw new StorageObjectException("Could not find the PoolMan Driver" + +e.toString()); + }*/ + Connection con = null; + try{ + con = SQLManager.getInstance().requestConnection(); + } catch(SQLException e){ + theLog.printError("could not connect to the database "+e.toString()); + System.err.println("could not connect to the database "+e.toString()); + throw new StorageObjectException("Could not connect to the database"+ + e.toString()); } - throw new StorageObjectException("No connection to database!"); + return con; } - /** - * Connection und StatementObjekt werden geschlossen und an den Connectionpool - * zurückgeben - * @param con Connection zur Datenbank - * @param stmt Statement-Objekt - */ - public void freeConnection (Connection con, Statement stmt) { - try { - if (stmt != null) - stmt.close(); - } catch (SQLException e1) { - theLog.printDebugInfo(e1.toString()); - } - if (con != null) - myBroker.freeConnection(con); - else - theLog.printDebugInfo("Con was null!"); + public void freeConnection (Connection con, Statement stmt) + throws StorageObjectException { + SQLManager.getInstance().closeStatement(stmt); + SQLManager.getInstance().returnConnection(con); } /** @@ -1053,7 +1158,8 @@ public class Database implements StorageObject { * @param wo Funktonsname, in der die SQLException geworfen wurde * @exception StorageObjectException */ - void throwSQLException (SQLException sqe, String wo) throws StorageObjectException { + protected void throwSQLException (SQLException sqe, String wo) + throws StorageObjectException { String state = ""; String message = ""; int vendor = 0; @@ -1068,15 +1174,29 @@ public class Database implements StorageObject { sqe.toString()); } + protected void _throwStorageObjectException (Exception e, String wo) + throws StorageObjectException { + if (e != null) { + theLog.printError(e.toString()+ wo); + throw new StorageObjectException(wo + e.toString()); + } else { + theLog.printError(wo); + throw new StorageObjectException(wo); + } + + } + /** - * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach eine StorageObjectException + * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach + * eine StorageObjectException * @param message Nachricht mit dem Fehler * @exception StorageObjectException */ - void throwStorageObjectException (String message) throws StorageObjectException { - theLog.printError(message); - throw new StorageObjectException(message); + void throwStorageObjectException (String message) + throws StorageObjectException { + _throwStorageObjectException(null, message); } + }