X-Git-Url: http://erislabs.net/gitweb/?a=blobdiff_plain;f=source%2Fmir%2Fstorage%2FDatabase.java;h=f86f043d1209013cf82369e87446bf90f3163ac1;hb=42680c1f9fe3250bcbd0f9ed5d9dee6188333b15;hp=2c8f7d612693d1e9f9eaa744beb94eea2af9c00a;hpb=55d0f77df79a52d553e93800d730aed1c7f0d485;p=mir.git diff --git a/source/mir/storage/Database.java b/source/mir/storage/Database.java index 2c8f7d61..f86f043d 100755 --- a/source/mir/storage/Database.java +++ b/source/mir/storage/Database.java @@ -1,5 +1,5 @@ /* - * Copyright (C) 2001, 2002 The Mir-coders group + * Copyright (C) 2001-2006 The Mir-coders group * * This file is part of Mir. * @@ -19,8 +19,6 @@ * * In addition, as a special exception, The Mir-coders gives permission to link * the code of this program with any library licensed under the Apache Software License, - * The Sun (tm) Java Advanced Imaging library (JAI), The Sun JIMI library - * (or with modified versions of the above that use the same license as the above), * and distribute linked combinations including the two. You must obey the * GNU General Public License in all respects for all of the code used other than * the above mentioned libraries. If you modify this file, you may extend this @@ -29,252 +27,137 @@ */ package mir.storage; -import java.io.IOException; -import java.io.InputStreamReader; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.ResultSetMetaData; -import java.sql.SQLException; -import java.sql.Statement; -import java.sql.Timestamp; -import java.text.ParseException; -import java.text.SimpleDateFormat; -import java.util.ArrayList; -import java.util.Calendar; -import java.util.GregorianCalendar; -import java.util.HashMap; -import java.util.Iterator; -import java.util.List; -import java.util.Map; -import java.util.TimeZone; -import java.util.Vector; - import mir.config.MirPropertiesConfiguration; -import mir.config.MirPropertiesConfiguration.PropertiesConfigExc; +import mir.entity.AbstractEntity; import mir.entity.Entity; import mir.entity.EntityList; import mir.entity.StorableObjectEntity; import mir.log.LoggerWrapper; -import mir.misc.StringUtil; -import mir.storage.store.ObjectStore; -import mir.storage.store.StorableObject; -import mir.storage.store.StoreContainerType; -import mir.storage.store.StoreIdentifier; -import mir.storage.store.StoreUtil; +import mir.storage.store.*; import mir.util.JDBCStringRoutines; - -import com.codestudio.util.SQLManager; - +import mir.util.StreamCopier; +import mircoders.global.MirGlobal; +import org.apache.commons.dbcp.DelegatingConnection; +import org.postgresql.PGConnection; +import org.postgresql.largeobject.LargeObject; +import org.postgresql.largeobject.LargeObjectManager; + +import java.io.ByteArrayOutputStream; +import java.io.InputStream; +import java.io.InputStreamReader; +import java.sql.*; +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.*; /** - * Diese Klasse implementiert die Zugriffsschicht auf die Datenbank. - * Alle Projektspezifischen Datenbankklassen erben von dieser Klasse. - * In den Unterklassen wird im Minimalfall nur die Tabelle angegeben. - * Im Konfigurationsfile findet sich eine Verweis auf den verwendeten - * Treiber, Host, User und Passwort, ueber den der Zugriff auf die - * Datenbank erfolgt. + * Implements database access. * - * @version $Id: Database.java,v 1.44.2.12 2003/11/24 23:54:06 rk Exp $ + * @version $Id: Database.java,v 1.44.2.37 2006/12/25 20:10:22 zapata Exp $ * @author rk + * @author Zapata * */ -public class Database implements StorageObject { - private static Class GENERIC_ENTITY_CLASS = mir.entity.StorableObjectEntity.class; - private static Class STORABLE_OBJECT_ENTITY_CLASS = mir.entity.StorableObjectEntity.class; +public class Database { + private static final int DEFAULT_LIMIT = 20; + private static final Class GENERIC_ENTITY_CLASS = StorableObjectEntity.class; + protected static final ObjectStore o_store = ObjectStore.getInstance(); + protected LoggerWrapper logger; - private static Map POPUP_EMPTYLINE = new HashMap(); - protected static final ObjectStore o_store = ObjectStore.getInstance(); - private static final int _millisPerHour = 60 * 60 * 1000; - private static final int _millisPerMinute = 60 * 1000; + protected String mainTable; + protected String primaryKeyField = "id"; - static { - // always same object saves a little space - POPUP_EMPTYLINE.put("key", ""); - POPUP_EMPTYLINE.put("value", "--"); - } + private List fieldNames; + private int[] fieldTypes; + private Map fieldNameToType; - protected LoggerWrapper logger; - protected MirPropertiesConfiguration configuration; - protected String theTable; - protected String theCoreTable = null; - protected String thePKeyName = "id"; - protected int thePKeyType; - protected int thePKeyIndex; - protected boolean evaluatedMetaData = false; - protected ArrayList metadataFields; - protected ArrayList metadataLabels; - protected ArrayList metadataNotNullFields; - protected int[] metadataTypes; - protected Class theEntityClass; - protected List popupCache = null; - protected boolean hasPopupCache = false; - protected Map hashCache = null; - protected boolean hasTimestamp = true; - private String database_driver; - private String database_url; - private int defaultLimit; - - TimeZone timezone; - SimpleDateFormat internalDateFormat; - SimpleDateFormat userInputDateFormat; -/* - private SimpleDateFormat _dateFormatterOut; - private SimpleDateFormat _dateFormatterIn; - _dateFormatterOut = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - _dateFormatterIn = new SimpleDateFormat("yyyy-MM-dd HH:mm"); -*/ + protected Class entityClass; - /** - * Kontruktor bekommt den Filenamen des Konfigurationsfiles ?bergeben. - * Aus diesem file werden Database.Logfile, - * Database.Username,Database.Password, - * Database.Host und Database.Adaptor - * ausgelesen und ein Broker f?r die Verbindugen zur Datenbank - * erzeugt. - * - * @param String confFilename Dateiname der Konfigurationsdatei - */ - public Database() throws StorageObjectFailure { - try { - configuration = MirPropertiesConfiguration.instance(); - } - catch (PropertiesConfigExc e) { - throw new StorageObjectFailure(e); - } + // + private Set binaryFields; + + private TimeZone timezone; + private SimpleDateFormat userInputDateFormat; + + public Database() throws DatabaseFailure { + MirPropertiesConfiguration configuration = MirPropertiesConfiguration.instance(); logger = new LoggerWrapper("Database"); timezone = TimeZone.getTimeZone(configuration.getString("Mir.DefaultTimezone")); - internalDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - internalDateFormat.setTimeZone(timezone); userInputDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm"); userInputDateFormat.setTimeZone(timezone); + binaryFields = new HashSet(); - String theAdaptorName = configuration.getString("Database.Adaptor"); - defaultLimit = Integer.parseInt(configuration.getString("Database.Limit")); + String adapterName = configuration.getString("Database.Adaptor"); try { - theEntityClass = GENERIC_ENTITY_CLASS; + entityClass = GENERIC_ENTITY_CLASS; } catch (Throwable e) { - logger.error("Error in Database() constructor with " + theAdaptorName + " -- " + e.getMessage()); - throw new StorageObjectFailure("Error in Database() constructor.", e); + logger.error("Error in Database() constructor with " + adapterName + " -- " + e.getMessage()); + throw new DatabaseFailure("Error in Database() constructor.", e); } } - /** - * Liefert die Entity-Klasse zur?ck, in der eine Datenbankzeile gewrappt - * wird. Wird die Entity-Klasse durch die erbende Klasse nicht ?berschrieben, - * wird eine mir.entity.GenericEntity erzeugt. - * - * @return Class-Objekt der Entity - */ - public java.lang.Class getEntityClass() { - return theEntityClass; - } - - /** - * Liefert die Standardbeschr?nkung von select-Statements zur?ck, also - * wieviel Datens?tze per Default selektiert werden. - * - * @return Standard-Anzahl der Datens?tze - */ - public int getLimit() { - return defaultLimit; - } - - /** - * Liefert den Namen des Primary-Keys zur?ck. Wird die Variable nicht von - * der erbenden Klasse ?berschrieben, so ist der Wert PKEY - * @return Name des Primary-Keys - */ - public String getIdName() { - return thePKeyName; + public Class getEntityClass() { + return entityClass; } - /** - * Liefert den Namen der Tabelle, auf das sich das Datenbankobjekt bezieht. - * - * @return Name der Tabelle - */ - public String getTableName() { - return theTable; - } + public Entity createNewEntity() throws DatabaseFailure { + try { + AbstractEntity result = (AbstractEntity) entityClass.newInstance(); + result.setStorage(this); - /* - * Dient dazu vererbte Tabellen bei objectrelationalen DBMS - * zu speichern, wenn die id einer Tabelle in der parenttabelle verwaltet - * wird. - * @return liefert theCoreTabel als String zurueck, wenn gesetzt, sonst - * the Table - */ - public String getCoreTable() { - if (theCoreTable != null) { - return theCoreTable; + return result; } - else { - return theTable; + catch (Throwable t) { + throw new DatabaseFailure(t); } } - /** - * Liefert Feldtypen der Felder der Tabelle zurueck (s.a. java.sql.Types) - * @return int-Array mit den Typen der Felder - * @exception StorageObjectException - */ - public int[] getTypes() throws StorageObjectFailure { - if (metadataTypes == null) { - get_meta_data(); - } + public String getIdFieldName() { + return primaryKeyField; + } - return metadataTypes; + public String getTableName() { + return mainTable; } /** - * Liefert eine Liste der Labels der Tabellenfelder - * @return ArrayListe mit Labeln - * @exception StorageObjectException + * Returns a list of field names for this Database */ - public List getLabels() throws StorageObjectFailure { - if (metadataLabels == null) { - get_meta_data(); + public List getFieldNames() throws DatabaseFailure { + if (fieldNames == null) { + acquireMetaData(); } - return metadataLabels; + return fieldNames; } - /** - * Liefert eine Liste der Felder der Tabelle - * @return ArrayList mit Feldern - * @exception StorageObjectException - */ - public List getFields() throws StorageObjectFailure { - if (metadataFields == null) { - get_meta_data(); - } - - return metadataFields; + public boolean hasField(String aFieldName) { + return getFieldNames().contains(aFieldName); } /** * Gets value out of ResultSet according to type and converts to String - * @param rs ResultSet. + * + * @param aResultSet ResultSet. * @param aType a type from java.sql.Types.* - * @param index index in ResultSet + * @param aFieldIndex index in ResultSet * @return returns the value as String. If no conversion is possible * /unsupported value/ is returned */ - private String getValueAsString(ResultSet rs, int valueIndex, int aType) - throws StorageObjectFailure { + private String getValueAsString(ResultSet aResultSet, int aFieldIndex, int aType) + throws DatabaseFailure { String outValue = null; - if (rs != null) { + if (aResultSet != null) { try { switch (aType) { case java.sql.Types.BIT: - outValue = (rs.getBoolean(valueIndex) == true) ? "1" : "0"; + outValue = (aResultSet.getBoolean(aFieldIndex) == true) ? "1" : "0"; break; @@ -283,21 +166,18 @@ public class Database implements StorageObject { case java.sql.Types.TINYINT: case java.sql.Types.BIGINT: - int out = rs.getInt(valueIndex); + int out = aResultSet.getInt(aFieldIndex); - if (!rs.wasNull()) { + if (!aResultSet.wasNull()) { outValue = new Integer(out).toString(); } break; case java.sql.Types.NUMERIC: + long outl = aResultSet.getLong(aFieldIndex); - /** @todo Numeric can be float or double depending upon - * metadata.getScale() / especially with oracle */ - long outl = rs.getLong(valueIndex); - - if (!rs.wasNull()) { + if (!aResultSet.wasNull()) { outValue = new Long(outl).toString(); } @@ -305,9 +185,9 @@ public class Database implements StorageObject { case java.sql.Types.REAL: - float tempf = rs.getFloat(valueIndex); + float tempf = aResultSet.getFloat(aFieldIndex); - if (!rs.wasNull()) { + if (!aResultSet.wasNull()) { tempf *= 10; tempf += 0.5; @@ -322,9 +202,9 @@ public class Database implements StorageObject { case java.sql.Types.DOUBLE: - double tempd = rs.getDouble(valueIndex); + double tempd = aResultSet.getDouble(aFieldIndex); - if (!rs.wasNull()) { + if (!aResultSet.wasNull()) { tempd *= 10; tempd += 0.5; @@ -340,12 +220,12 @@ public class Database implements StorageObject { case java.sql.Types.CHAR: case java.sql.Types.VARCHAR: case java.sql.Types.LONGVARCHAR: - outValue = rs.getString(valueIndex); + outValue = aResultSet.getString(aFieldIndex); break; case java.sql.Types.LONGVARBINARY: - outValue = rs.getString(valueIndex); + outValue = aResultSet.getString(aFieldIndex); break; @@ -355,34 +235,22 @@ public class Database implements StorageObject { // as a string is undefined and is only there for debugging // according to the API. we can make it a string through formatting. // -mh - Timestamp timestamp = (rs.getTimestamp(valueIndex)); + Timestamp timestamp = (aResultSet.getTimestamp(aFieldIndex)); - if (!rs.wasNull()) { + if (!aResultSet.wasNull()) { java.util.Date date = new java.util.Date(timestamp.getTime()); - - Calendar calendar = new GregorianCalendar(); - calendar.setTime(date); - calendar.setTimeZone(timezone); - outValue = internalDateFormat.format(date); - - int offset = calendar.get(Calendar.ZONE_OFFSET) + calendar.get(Calendar.DST_OFFSET); - String tzOffset = StringUtil.zeroPaddingNumber(Math.abs(offset) / _millisPerHour, 2, 2); - - if (offset<0) - outValue = outValue + "-"; - else - outValue = outValue + "+"; - outValue = outValue + tzOffset; + outValue = DatabaseHelper.convertDateToInternalRepresenation(date); } break; default: outValue = ""; - logger.warn("Unsupported Datatype: at " + valueIndex + " (" + aType + ")"); + logger.warn("Unsupported Datatype: at " + aFieldIndex + " (" + aType + ")"); } - } catch (SQLException e) { - throw new StorageObjectFailure("Could not get Value out of Resultset -- ", + } + catch (SQLException e) { + throw new DatabaseFailure("Could not get Value out of Resultset -- ", e); } } @@ -391,24 +259,23 @@ public class Database implements StorageObject { } /** - * select-Operator um einen Datensatz zu bekommen. - * @param id Primaerschluessel des Datensatzes. - * @return liefert EntityObject des gefundenen Datensatzes oder null. + * Return an entity specified by id, or null if no such + * entity exists. */ - public Entity selectById(String id) throws StorageObjectExc { - if ((id == null) || id.equals("")) { - throw new StorageObjectExc("Database.selectById: Missing id"); + public Entity selectById(String anId) throws DatabaseExc { + if ((anId == null) || anId.equals("")) { + throw new DatabaseExc("Database.selectById: Missing id"); } // ask object store for object - if (StoreUtil.implementsStorableObject(theEntityClass)) { - String uniqueId = id; + if (StoreUtil.extendsStorableEntity(entityClass)) { + String uniqueId = anId; - if (theEntityClass.equals(StorableObjectEntity.class)) { - uniqueId += ("@" + theTable); + if (entityClass.equals(StorableObjectEntity.class)) { + uniqueId += ("@" + mainTable); } - StoreIdentifier search_sid = new StoreIdentifier(theEntityClass, uniqueId); + StoreIdentifier search_sid = new StoreIdentifier(entityClass, uniqueId); logger.debug("CACHE: (dbg) looking for sid " + search_sid.toString()); Entity hit = (Entity) o_store.use(search_sid); @@ -418,342 +285,218 @@ public class Database implements StorageObject { } } - Statement stmt = null; - Connection con = getPooledCon(); + Connection con = obtainConnection(); Entity returnEntity = null; + PreparedStatement statement = null; try { ResultSet rs; + String query = "select * from " + mainTable + " where " + primaryKeyField + " = ?"; - /** @todo better prepared statement */ - String selectSql = - "select * from " + theTable + " where " + thePKeyName + "=" + id; - stmt = con.createStatement(); - rs = executeSql(stmt, selectSql); + statement = con.prepareStatement(query); + statement.setString(1, anId); - if (rs != null) { - if (evaluatedMetaData == false) { - evalMetaData(rs.getMetaData()); - } + logQueryBefore(query); + + long startTime = System.currentTimeMillis(); + try { + rs = statement.executeQuery(); + logQueryAfter(query, (System.currentTimeMillis() - startTime)); + } + catch (SQLException e) { + logQueryError(query, (System.currentTimeMillis() - startTime), e); + throw e; + } + + if (rs != null) { if (rs.next()) { returnEntity = makeEntityFromResultSet(rs); } else { - logger.debug("No data for id: " + id + " in table " + theTable); + logger.warn("No data for id: " + anId + " in table " + mainTable); } rs.close(); } else { - logger.debug("No Data for Id " + id + " in Table " + theTable); + logger.warn("No Data for Id " + anId + " in Table " + mainTable); } } - catch (SQLException sqe) { - throwSQLException(sqe, "selectById"); - return null; - } - catch (NumberFormatException e) { - logger.error("ID is no number: " + id); + catch (Throwable e) { + throw new DatabaseFailure(e); } finally { - freeConnection(con, stmt); + freeConnection(con, statement); } return returnEntity; } - /** - * This method makes it possible to make selects across multiple tables - * - * @param mainTablePrefix prefix for the mainTable - * @param extraTables a vector of tables for relational select - * @param aWhereClause whereClause - * @return EntityList of selected Objects - * @throws StorageObjectFailure - */ - - public EntityList selectByWhereClauseWithExtraTables(String mainTablePrefix, - List extraTables, String aWhereClause ) - throws StorageObjectFailure { - return selectByWhereClause( mainTablePrefix, extraTables, aWhereClause, "", 0, -1); + public EntityList selectByWhereClauseWithExtraTables(String mainTablePrefix, List extraTables, String aWhereClause) throws DatabaseExc, DatabaseFailure { + return selectByWhereClause( mainTablePrefix, extraTables, aWhereClause, "", 0, DEFAULT_LIMIT); } - /** - * 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 StorageObjectFailure { + public EntityList selectByFieldValue(String aField, String aValue) throws DatabaseExc, DatabaseFailure { return selectByFieldValue(aField, aValue, 0); } - /** - * select-Operator um Datensaetze zu bekommen, die key = value erfuellen. - * @param key Datenbankfeld der Bedingung. - * @param value Wert die der key anehmen muss. - * @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 StorageObjectFailure { - return selectByWhereClause(aField + "=" + aValue, offset); + public EntityList selectByFieldValue(String aField, String aValue, int offset) throws DatabaseExc, DatabaseFailure { + return selectByWhereClause(aField + "='" + JDBCStringRoutines.escapeStringLiteral(aValue)+"'", offset); } - /** - * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck. - * Also offset wird der erste Datensatz genommen. - * - * @param wc where-Clause - * @return EntityList mit den gematchten Entities - * @exception StorageObjectException - */ - public EntityList selectByWhereClause(String where) throws StorageObjectFailure { + public EntityList selectByWhereClause(String where) throws DatabaseExc, DatabaseFailure { return selectByWhereClause(where, 0); } - /** - * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck. - * Als maximale Anzahl wird das Limit auf der Konfiguration genommen. - * - * @param wc where-Clause - * @param offset ab welchem Datensatz. - * @return EntityList mit den gematchten Entities - * @exception StorageObjectException - */ - public EntityList selectByWhereClause(String whereClause, int offset) throws StorageObjectFailure { + public EntityList selectByWhereClause(String whereClause, int offset) throws DatabaseExc, DatabaseFailure { return selectByWhereClause(whereClause, null, offset); } - /** - * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck. - * Also offset wird der erste Datensatz genommen. - * Als maximale Anzahl wird das Limit auf der Konfiguration genommen. - * - * @param wc where-Clause - * @param ob orderBy-Clause - * @return EntityList mit den gematchten Entities - * @exception StorageObjectException - */ - public EntityList selectByWhereClause(String where, String order) throws StorageObjectFailure { - return selectByWhereClause(where, order, 0); + public EntityList selectByWhereClause(String mainTablePrefix, List extraTables, String where, String order) throws DatabaseExc, DatabaseFailure { + return selectByWhereClause(mainTablePrefix, extraTables, where, order, 0, DEFAULT_LIMIT); } - /** - * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck. - * Als maximale Anzahl wird das Limit auf der Konfiguration genommen. - * - * @param wc where-Clause - * @param ob orderBy-Clause - * @param offset ab welchem Datensatz - * @return EntityList mit den gematchten Entities - * @exception StorageObjectException - */ - public EntityList selectByWhereClause(String whereClause, String orderBy, int offset) throws StorageObjectFailure { - return selectByWhereClause(whereClause, orderBy, offset, defaultLimit); + public EntityList selectByWhereClause(String whereClause, String orderBy, int offset) throws DatabaseExc, DatabaseFailure { + return selectByWhereClause(whereClause, orderBy, offset, DEFAULT_LIMIT); } - /** - * select-Operator returns EntityList with matching rows in Database. - * @param aWhereClause where-Clause - * @param anOrderByClause orderBy-Clause - * @param offset ab welchem Datensatz - * @param limit wieviele Datens?tze - * @return EntityList mit den gematchten Entities - * @exception StorageObjectException - */ public EntityList selectByWhereClause(String aWhereClause, String anOrderByClause, - int offset, int limit) throws StorageObjectFailure { - return selectByWhereClause("", null, aWhereClause, anOrderByClause, offset, limit); + int offset, int limit) throws DatabaseExc, DatabaseFailure { + return selectByWhereClause("", null, aWhereClause, anOrderByClause, offset, limit); } - /** - * select-Operator returns EntityList with matching rows in Database. - * @param aWhereClause where-Clause - * @param anOrderByClause orderBy-Clause - * @param offset ab welchem Datensatz - * @param limit wieviele Datens?tze - * @return EntityList mit den gematchten Entities - * @exception StorageObjectException - */ - public EntityList selectByWhereClause(String mainTablePrefix, List extraTables, + public EntityList selectByWhereClause( + String aMainTablePrefix, List anExtraTables, String aWhereClause, String anOrderByClause, - int offset, int limit) throws StorageObjectFailure { - - - String useTable = theTable; - String selectStar = "*"; - if (mainTablePrefix!=null && mainTablePrefix.trim().length()>0) { - useTable+=" "+mainTablePrefix; - selectStar=mainTablePrefix.trim() + ".*"; + int anOffset, int aLimit) throws DatabaseExc, DatabaseFailure { + + if (anExtraTables!=null && ((String) anExtraTables.get(0)).trim().equals("")){ + anExtraTables=null; } - + // check o_store for entitylist // only if no relational select - if (extraTables==null) { - if (StoreUtil.implementsStorableObject(theEntityClass)) { - StoreIdentifier search_sid = - new StoreIdentifier( - theEntityClass, StoreContainerType.STOC_TYPE_ENTITYLIST, - StoreUtil.getEntityListUniqueIdentifierFor(useTable, aWhereClause, anOrderByClause, offset, limit)); - EntityList hit = (EntityList) o_store.use(search_sid); - - if (hit != null) { - logger.debug("CACHE (hit): " + search_sid.toString()); - - return hit; - } + if (anExtraTables==null) { + if (StoreUtil.extendsStorableEntity(entityClass)) { + StoreIdentifier searchSid = new StoreIdentifier(entityClass, + StoreContainerType.STOC_TYPE_ENTITYLIST, + StoreUtil.getEntityListUniqueIdentifierFor(mainTable, + aWhereClause, anOrderByClause, anOffset, aLimit)); + EntityList hit = (EntityList) o_store.use(searchSid); + + if (hit != null) { + return hit; + } } } - // local - EntityList theReturnList = null; - Connection con = null; - Statement stmt = null; - ResultSet rs; - int offsetCount = 0; - int count = 0; - - // build sql-statement + RecordRetriever retriever = new RecordRetriever(mainTable, aMainTablePrefix); - if ((aWhereClause != null) && (aWhereClause.trim().length() == 0)) { - aWhereClause = null; - } + EntityList result = null; + Connection connection = null; - StringBuffer countSql = - new StringBuffer("select count(*) from ").append(useTable); - StringBuffer selectSql = - new StringBuffer("select "+selectStar+" from ").append(useTable); - - // append extratables, if necessary - if (extraTables!=null) { - for (int i=0;i < extraTables.size();i++) { - if (!extraTables.get(i).equals("")) { - countSql.append( ", " + extraTables.get(i)); - selectSql.append( ", " + extraTables.get(i)); + if (anExtraTables!=null) { + Iterator i = anExtraTables.iterator(); + while (i.hasNext()) { + String table = (String) i.next(); + if (!"".equals(table)) { + retriever.addExtraTable(table); } } } - + if (aWhereClause != null) { - selectSql.append(" where ").append(aWhereClause); - countSql.append(" where ").append(aWhereClause); + retriever.appendWhereClause(aWhereClause); } if ((anOrderByClause != null) && !(anOrderByClause.trim().length() == 0)) { - selectSql.append(" order by ").append(anOrderByClause); + retriever.appendOrderByClause(anOrderByClause); } - if ((limit > -1) && (offset > -1)) { - selectSql.append(" LIMIT ").append(limit).append(" OFFSET ").append(offset); + if (anOffset>-1 && aLimit>-1) { + retriever.setLimit(aLimit+1); + retriever.setOffset(anOffset); + } + + Iterator i = getFieldNames().iterator(); + while (i.hasNext()) { + retriever.addField((String) i.next()); } // execute sql try { - con = getPooledCon(); - stmt = con.createStatement(); - - // selecting... - rs = executeSql(stmt, selectSql.toString()); - - if (rs != null) { - if (!evaluatedMetaData) { - evalMetaData(rs.getMetaData()); - } - - theReturnList = new EntityList(); - Entity theResultEntity; - while (rs.next()) { - theResultEntity = makeEntityFromResultSet(rs); - theReturnList.add(theResultEntity); - offsetCount++; - } - rs.close(); - } - - // making entitylist infos - count = offsetCount; + connection = obtainConnection(); + ResultSet resultSet = retriever.execute(connection); - if (theReturnList != null) { - // now we decide if we have to know an overall count... - count = offsetCount; + boolean hasMore = false; - if ((limit > -1) && (offset > -1)) { - if (offsetCount == limit) { - rs = executeSql(stmt, countSql.toString()); + if (resultSet != null) { + result = new EntityList(); + Entity entity; + int position = 0; - if (rs != null) { - if (rs.next()) { - count = rs.getInt(1); - } - - rs.close(); - } - else { - logger.error("Could not count: " + countSql); - } - } + while (((aLimit == -1) || (position= limit) { - theReturnList.setPrevBatch(offset - limit); - } + if (result != null) { + result.setOffset(anOffset); + result.setWhere(aWhereClause); + result.setOrder(anOrderByClause); + result.setStorage(this); + result.setLimit(aLimit); - if ((offset + offsetCount) < count) { - theReturnList.setNextBatch(offset + limit); + if (hasMore) { + result.setNextBatch(anOffset + aLimit); } - if (extraTables==null && StoreUtil.implementsStorableObject(theEntityClass)) { - StoreIdentifier sid = theReturnList.getStoreIdentifier(); + if (anExtraTables==null && StoreUtil.extendsStorableEntity(entityClass)) { + StoreIdentifier sid = result.getStoreIdentifier(); logger.debug("CACHE (add): " + sid.toString()); o_store.add(sid); } } } - catch (SQLException sqe) { - throwSQLException(sqe, "selectByWhereClause"); + catch (Throwable e) { + throw new DatabaseFailure(e); } finally { try { - if (con != null) { - freeConnection(con, stmt); + if (connection != null) { + freeConnection(connection); } } catch (Throwable t) { } } - return theReturnList; + return result; } - /** - * Bastelt aus einer Zeile der Datenbank ein EntityObjekt. - * - * @param rs Das ResultSetObjekt. - * @return Entity Die Entity. - */ - private Entity makeEntityFromResultSet(ResultSet rs) - throws StorageObjectFailure { - /** @todo OS: get Pkey from ResultSet and consult ObjectStore */ - Map theResultHash = new HashMap(); + private Entity makeEntityFromResultSet(ResultSet rs) { + Map fields = new HashMap(); String theResult = null; - int theType; + int type; Entity returnEntity = null; try { - int size = metadataFields.size(); + if (StoreUtil.extendsStorableEntity(entityClass)) { + StoreIdentifier searchSid = StorableObjectEntity.getStoreIdentifier(this, + entityClass, rs); + Entity hit = (Entity) o_store.use(searchSid); + if (hit != null) return hit; + } - for (int i = 0; i < size; i++) { - // alle durchlaufen bis nix mehr da - theType = metadataTypes[i]; + for (int i = 0; i < getFieldNames().size(); i++) { + type = fieldTypes[i]; - if (theType == java.sql.Types.LONGVARBINARY) { + if (type == java.sql.Types.LONGVARBINARY) { InputStreamReader is = (InputStreamReader) rs.getCharacterStream(i + 1); @@ -768,44 +511,35 @@ public class Database implements StorageObject { is.close(); theResult = theResultString.toString(); - } else { + } + else { theResult = null; } - } else { - theResult = getValueAsString(rs, (i + 1), theType); + } + else { + theResult = getValueAsString(rs, (i + 1), type); } if (theResult != null) { - theResultHash.put(metadataFields.get(i), theResult); + fields.put(getFieldNames().get(i), theResult); } } - if (theEntityClass != null) { - returnEntity = (Entity) theEntityClass.newInstance(); - returnEntity.setStorage(this); - returnEntity.setValues(theResultHash); + if (entityClass != null) { + returnEntity = createNewEntity(); + returnEntity.setFieldValues(fields); if (returnEntity instanceof StorableObject) { - logger.debug("CACHE: ( in) " + returnEntity.getId() + " :" + theTable); + logger.debug("CACHE: ( in) " + returnEntity.getId() + " :" + mainTable); o_store.add(((StorableObject) returnEntity).getStoreIdentifier()); } - } else { - throwStorageObjectException("Internal Error: theEntityClass not set!"); + } + else { + throw new DatabaseExc("Internal Error: entityClass not set!"); } } - catch (IllegalAccessException e) { - throwStorageObjectException("No access! -- " + e.getMessage()); - } - catch (IOException e) { - throwStorageObjectException("IOException! -- " + e.getMessage()); - } - catch (InstantiationException e) { - throwStorageObjectException("No Instatiation! -- " + e.getMessage()); - } - catch (SQLException sqe) { - throwSQLException(sqe, "makeEntityFromResultSet"); - - return null; + catch (Throwable e) { + throw new DatabaseFailure(e); } return returnEntity; @@ -814,123 +548,49 @@ public class Database implements StorageObject { /** * Inserts an entity into the database. * - * @param theEntity - * @return der Wert des Primary-keys der eingef?gten Entity + * @param anEntity + * @return the value of the primary key of the inserted record */ - public String insert(Entity theEntity) throws StorageObjectFailure { - //cache - invalidatePopupCache(); + public String insert(Entity anEntity) throws DatabaseFailure { + invalidateStore(); - // 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); - } + RecordInserter inserter = + new RecordInserter(mainTable, getPrimaryKeySequence()); String returnId = null; Connection con = null; - PreparedStatement pstmt = null; try { - List streamedInput = theEntity.streamedInput(); - StringBuffer f = new StringBuffer(); - StringBuffer v = new StringBuffer(); - String aField; - String aValue; - boolean firstField = true; + String fieldName; // make sql-string - for (int i = 0; i < getFields().size(); i++) { - aField = (String) getFields().get(i); - - if (!aField.equals(thePKeyName)) { - aValue = null; + for (int i = 0; i < getFieldNames().size(); i++) { + fieldName = (String) getFieldNames().get(i); + if (!fieldName.equals(primaryKeyField)) { // exceptions - if (!theEntity.hasValueForField(aField) && ( - aField.equals("webdb_create") || - aField.equals("webdb_lastchange"))) { - aValue = "NOW()"; + if (!anEntity.hasFieldValue(fieldName) && ( + fieldName.equals("webdb_create") || + fieldName.equals("webdb_lastchange"))) { + inserter.assignVerbatim(fieldName, "now()"); } else { - if ((streamedInput != null) && streamedInput.contains(aField)) { - aValue = "?"; - } - else { - if (theEntity.hasValueForField(aField)) { - aValue = - "'" + - JDBCStringRoutines.escapeStringLiteral((String) theEntity.getValue(aField)) + "'"; - } - } - } - - // wenn Wert gegeben, dann einbauen - if (aValue != null) { - if (firstField == false) { - f.append(","); - v.append(","); + if (anEntity.hasFieldValue(fieldName)) { + inserter.assignString(fieldName, anEntity.getFieldValue(fieldName)); } - else { - firstField = false; - } - - f.append(aField); - v.append(aValue); } } } - // end for - // insert into db - StringBuffer sqlBuf = - new StringBuffer("insert into ").append(theTable).append("(").append(f) - .append(") values (").append(v).append(")"); - String sql = sqlBuf.toString(); + con = obtainConnection(); + returnId = inserter.execute(con); - logger.debug("INSERT: " + sql); - con = getPooledCon(); - con.setAutoCommit(false); - pstmt = con.prepareStatement(sql); - - if (streamedInput != null) { - for (int i = 0; i < streamedInput.size(); i++) { - String inputString = - (String) theEntity.getValue((String) streamedInput.get(i)); - pstmt.setBytes(i + 1, inputString.getBytes()); - } - } - - int ret = pstmt.executeUpdate(); - - if (ret == 0) { - //insert failed - return null; - } - - pstmt = con.prepareStatement("select currval('" + getCoreTable() + "_id_seq')"); - - ResultSet rs = pstmt.executeQuery(); - rs.next(); - returnId = rs.getString(1); - theEntity.setId(returnId); - } - catch (SQLException sqe) { - throwSQLException(sqe, "insert"); + anEntity.setId(returnId); } finally { - try { - con.setAutoCommit(true); - } - catch (Exception e) { - } - - freeConnection(con, pstmt); + freeConnection(con); } - /** @todo store entity in o_store */ return returnId; } @@ -939,74 +599,38 @@ public class Database implements StorageObject { * * @param theEntity */ - public void update(Entity theEntity) throws StorageObjectFailure { - 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); - } + public void update(Entity theEntity) throws DatabaseFailure { + invalidateStore(); - List streamedInput = theEntity.streamedInput(); - String id = theEntity.getId(); - String aField; - StringBuffer fv = new StringBuffer(); - boolean firstField = true; - - //cache - invalidatePopupCache(); + RecordUpdater generator = new RecordUpdater(getTableName(), theEntity.getId()); // build sql statement - for (int i = 0; i < getFields().size(); i++) { - aField = (String) metadataFields.get(i); - - // only normal cases - if ( !(aField.equals(thePKeyName) || - aField.equals("webdb_create") || - aField.equals("webdb_lastchange") || - ((streamedInput != null) && streamedInput.contains(aField)))) { - if (theEntity.hasValueForField(aField)) { - if (firstField == false) { - fv.append(", "); - } - else { - firstField = false; - } + for (int i = 0; i < getFieldNames().size(); i++) { + String field = (String) getFieldNames().get(i); - fv.append(aField).append("='").append(JDBCStringRoutines.escapeStringLiteral((String) theEntity.getValue(aField))).append("'"); + if (!(field.equals(primaryKeyField) || + "webdb_create".equals(field) || + "webdb_lastchange".equals(field) || + binaryFields.contains(field))) { - // fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getValue(aField))).append("'"); + if (theEntity.hasFieldValue(field)) { + generator.assignString(field, theEntity.getFieldValue(field)); } } } - StringBuffer sql = - new StringBuffer("update ").append(theTable).append(" set ").append(fv); - // exceptions - if (metadataFields.contains("webdb_lastchange")) { - sql.append(",webdb_lastchange=NOW()"); + if (hasField("webdb_lastchange")) { + generator.assignVerbatim("webdb_lastchange", "now()"); } // special case: the webdb_create requires the field in yyyy-mm-dd HH:mm // format so anything extra will be ignored. -mh - if (metadataFields.contains("webdb_create") && - theEntity.hasValueForField("webdb_create")) { + if (hasField("webdb_create") && + theEntity.hasFieldValue("webdb_create")) { // minimum of 10 (yyyy-mm-dd)... - if (theEntity.getValue("webdb_create").length() >= 10) { - String dateString = theEntity.getValue("webdb_create"); + if (theEntity.getFieldValue("webdb_create").length() >= 10) { + String dateString = theEntity.getFieldValue("webdb_create"); // if only 10, then add 00:00 so it doesn't throw a ParseException if (dateString.length() == 10) { @@ -1016,127 +640,92 @@ public class Database implements StorageObject { // TimeStamp stuff try { java.util.Date d = userInputDateFormat.parse(dateString); -// Timestamp tStamp = new Timestamp(d.getTime()); - sql.append(",webdb_create='" + JDBCStringRoutines.formatDate(d) + "'"); + generator.assignDateTime("webdb_create", d); } catch (ParseException e) { - throw new StorageObjectFailure(e); + throw new DatabaseFailure(e); } } } - - if (streamedInput != null) { - for (int i = 0; i < streamedInput.size(); i++) { - sql.append(",").append(streamedInput.get(i)).append("=?"); - } - } - - sql.append(" where id=").append(id); - logger.debug("UPDATE: " + sql); + Connection connection = null; try { - con = getPooledCon(); - con.setAutoCommit(false); - pstmt = con.prepareStatement(sql.toString()); - - if (streamedInput != null) { - for (int i = 0; i < streamedInput.size(); i++) { - String inputString = - theEntity.getValue((String) streamedInput.get(i)); - pstmt.setBytes(i + 1, inputString.getBytes()); - } - } - - pstmt.executeUpdate(); - } - catch (SQLException sqe) { - throwSQLException(sqe, "update"); + connection = obtainConnection(); + generator.execute(connection); } finally { - try { - con.setAutoCommit(true); - } - catch (Exception e) { - ; - } - - freeConnection(con, pstmt); + freeConnection(connection); } } - - /* - * delete-Operator - * @param id des zu loeschenden Datensatzes - * @return boolean liefert true zurueck, wenn loeschen erfolgreich war. - */ - public boolean delete(String id) throws StorageObjectFailure { - invalidatePopupCache(); - + + private void invalidateObject(String anId) { // ostore send notification - if (StoreUtil.implementsStorableObject(theEntityClass)) { - String uniqueId = id; + if (StoreUtil.extendsStorableEntity(entityClass)) { + String uniqueId = anId; - if (theEntityClass.equals(StorableObjectEntity.class)) { - uniqueId += ("@" + theTable); + if (entityClass.equals(StorableObjectEntity.class)) { + uniqueId += ("@" + mainTable); } - logger.debug("CACHE: (del) " + id); + logger.debug("CACHE: (del) " + anId); StoreIdentifier search_sid = - new StoreIdentifier(theEntityClass, + new StoreIdentifier(entityClass, 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 + "'"; + /* + * delete-Operator + * @param id des zu loeschenden Datensatzes + * @return boolean liefert true zurueck, wenn loeschen erfolgreich war. + */ + public boolean delete(String id) throws DatabaseFailure { + invalidateObject(id); + + int resultCode = 0; + Connection connection = obtainConnection(); + PreparedStatement statement = null; - //theLog.printInfo("DELETE " + sql); try { - con = getPooledCon(); - stmt = con.createStatement(); - res = stmt.executeUpdate(sql); - } catch (SQLException sqe) { - throwSQLException(sqe, "delete"); - } finally { - freeConnection(con, stmt); + statement = connection.prepareStatement("delete from " + mainTable + " where " + primaryKeyField + "=?"); + statement.setInt(1, Integer.parseInt(id)); + logQueryBefore("delete from " + mainTable + " where " + primaryKeyField + "=" + id + ""); + resultCode = statement.executeUpdate(); } + catch (SQLException e) { + logger.warn("Can't delete record", e); + } + finally { + freeConnection(connection, statement); + } + + invalidateStore(); - return (res > 0) ? true : false; + return (resultCode > 0) ? true : false; } /** * Deletes entities based on a where clause - * - * @param aWhereClause - * @return - * @throws StorageObjectFailure */ - public int deleteByWhereClause(String aWhereClause) throws StorageObjectFailure { - invalidatePopupCache(); - if (StoreUtil.implementsStorableObject(theEntityClass)) { - StoreContainerType stoc_type = StoreContainerType.valueOf(theEntityClass, StoreContainerType.STOC_TYPE_ENTITYLIST); - o_store.invalidate(stoc_type); - } + public int deleteByWhereClause(String aWhereClause) throws DatabaseFailure { + invalidateStore(); Statement stmt = null; Connection con = null; int res = 0; String sql = - "delete from " + theTable + " where " + aWhereClause; + "delete from " + mainTable + " where " + aWhereClause; //theLog.printInfo("DELETE " + sql); try { - con = getPooledCon(); + con = obtainConnection(); stmt = con.createStatement(); res = stmt.executeUpdate(sql); } - catch (SQLException sqe) { - throwSQLException(sqe, "delete"); + catch (Throwable e) { + throw new DatabaseFailure(e); } finally { freeConnection(con, stmt); @@ -1149,71 +738,28 @@ public class Database implements StorageObject { * @return immer false */ public boolean delete(EntityList theEntityList) { - invalidatePopupCache(); - return false; } - /* invalidates the popupCache - */ - protected void invalidatePopupCache() { - /** @todo invalidates toooo much */ - popupCache = null; - hashCache = null; - } - - /** - * Diese Methode fuehrt den Sqlstring sql aus und timed im Logfile. - * @param stmt Statemnt - * @param sql Sql-String - * @return ResultSet - * @exception StorageObjectException - */ public ResultSet executeSql(Statement stmt, String sql) - throws StorageObjectFailure, SQLException { + throws DatabaseFailure, SQLException { ResultSet rs; + logQueryBefore(sql); long startTime = System.currentTimeMillis(); - try { rs = stmt.executeQuery(sql); - logger.debug((System.currentTimeMillis() - startTime) + "ms. for: " + sql); + logQueryAfter(sql, (System.currentTimeMillis() - startTime)); } catch (SQLException e) { - logger.error(e.getMessage() +"\n" + (System.currentTimeMillis() - startTime) + "ms. for: " + sql); + logQueryError(sql, (System.currentTimeMillis() - startTime), e); throw e; } return rs; } -/* - public ResultSet executeSql(String sql) throws StorageObjectFailure, SQLException { - long startTime = System.currentTimeMillis(); - Connection connection = null; - Statement statement = null; - - try { - connection = getPooledCon(); - statement = connection.createStatement(); - ResultSet result; - - result = statement.executeQuery(sql); - logger.debug((System.currentTimeMillis() - startTime) + "ms. for: " + sql); - return result; - } - catch (Throwable e) { - logger.error(e.getMessage() +"\n" + (System.currentTimeMillis() - startTime) + "ms. for: " + sql); - throw new StorageObjectFailure(e); - } - finally { - if (connection!=null) { - freeConnection(connection, statement); - } - } - } -*/ - private Map processRow(ResultSet aResultSet) throws StorageObjectFailure, StorageObjectExc { + private Map processRow(ResultSet aResultSet) throws DatabaseFailure { try { Map result = new HashMap(); ResultSetMetaData metaData = aResultSet.getMetaData(); @@ -1225,16 +771,20 @@ public class Database implements StorageObject { return result; } catch (Throwable e) { - throw new StorageObjectFailure(e); + throw new DatabaseFailure(e); } } - public List executeFreeSql(String sql, int aLimit) throws StorageObjectFailure, StorageObjectExc { + /** + * Executes 1 sql statement and returns the results as a List of + * Maps + */ + public List executeFreeSql(String sql, int aLimit) throws DatabaseFailure, DatabaseExc { Connection connection = null; Statement statement = null; try { - List result = new Vector(); - connection = getPooledCon(); + List result = new ArrayList(); + connection = obtainConnection(); statement = connection.createStatement(); ResultSet resultset = executeSql(statement, sql); try { @@ -1249,33 +799,40 @@ public class Database implements StorageObject { return result; } catch (Throwable e) { - throw new StorageObjectFailure(e); + throw new DatabaseFailure(e); } finally { if (connection!=null) { freeConnection(connection, statement); } } - }; + } - public Map executeFreeSingleRowSql(String anSqlStatement) throws StorageObjectFailure, StorageObjectExc { + /** + * Executes 1 sql statement and returns the first result row as a Maps + * (null if there wasn't any row) + */ + public Map executeFreeSingleRowSql(String anSqlStatement) throws DatabaseFailure, DatabaseExc { try { List resultList = executeFreeSql(anSqlStatement, 1); try { if (resultList.size()>0) return (Map) resultList.get(0); - else - return null; + return null; } finally { } } catch (Throwable t) { - throw new StorageObjectFailure(t); + throw new DatabaseFailure(t); } - }; + } - public String executeFreeSingleValueSql(String sql) throws StorageObjectFailure, StorageObjectExc { + /** + * Executes 1 sql statement and returns the first column of the first result row as a Strings + * (null if there wasn't any row) + */ + public String executeFreeSingleValueSql(String sql) throws DatabaseFailure, DatabaseExc { Map row = executeFreeSingleRowSql(sql); if (row==null) @@ -1284,30 +841,47 @@ public class Database implements StorageObject { Iterator i = row.values().iterator(); if (i.hasNext()) return (String) i.next(); - else - return null; - }; + return null; + } + public int getSize(String where) throws SQLException, DatabaseFailure { + return getSize("", null, where); + } /** * returns the number of rows in the table */ - public int getSize(String where) throws SQLException, StorageObjectFailure { - long startTime = System.currentTimeMillis(); - String sql = "SELECT Count(*) FROM " + theTable; + public int getSize(String mainTablePrefix, List extraTables, String where) throws SQLException, DatabaseFailure { + + String useTable = mainTable; + if (mainTablePrefix!=null && mainTablePrefix.trim().length()>0) { + useTable+=" "+mainTablePrefix; + } + StringBuffer countSql = + new StringBuffer("select count(*) from ").append(useTable); + // append extratables, if necessary + if (extraTables!=null) { + for (int i=0;i < extraTables.size();i++) { + if (!extraTables.get(i).equals("")) { + countSql.append( ", " + extraTables.get(i)); + } + } + } if ((where != null) && (where.length() != 0)) { - sql = sql + " where " + where; + countSql.append( " where " + where); } Connection con = null; Statement stmt = null; int result = 0; + logQueryBefore(countSql.toString()); + long startTime = System.currentTimeMillis(); try { - con = getPooledCon(); + con = obtainConnection(); stmt = con.createStatement(); - ResultSet rs = executeSql(stmt, sql); + ResultSet rs = executeSql(stmt, countSql.toString()); while (rs.next()) { result = rs.getInt(1); @@ -1319,25 +893,25 @@ public class Database implements StorageObject { finally { freeConnection(con, stmt); } - - //theLog.printInfo(theTable + " has "+ result +" rows where " + where); - logger.debug((System.currentTimeMillis() - startTime) + "ms. for: " + sql); + logQueryAfter(countSql.toString(), (System.currentTimeMillis() - startTime)); return result; } public int executeUpdate(Statement stmt, String sql) - throws StorageObjectFailure, SQLException { + throws DatabaseFailure, SQLException { int rs; + + logQueryBefore(sql); long startTime = System.currentTimeMillis(); try { rs = stmt.executeUpdate(sql); - logger.debug((System.currentTimeMillis() - startTime) + "ms. for: " + sql); + logQueryAfter(sql, (System.currentTimeMillis() - startTime)); } catch (SQLException e) { - logger.error("Failed: " + (System.currentTimeMillis() - startTime) + "ms. for: " + sql); + logQueryError(sql, (System.currentTimeMillis() - startTime), e); throw e; } @@ -1345,162 +919,267 @@ public class Database implements StorageObject { } public int executeUpdate(String sql) - throws StorageObjectFailure, SQLException { + throws DatabaseFailure, SQLException { int result = -1; - long startTime = System.currentTimeMillis(); Connection con = null; PreparedStatement pstmt = null; + logQueryBefore(sql); + long startTime = System.currentTimeMillis(); try { - con = getPooledCon(); + con = obtainConnection(); pstmt = con.prepareStatement(sql); result = pstmt.executeUpdate(); + logQueryAfter(sql, System.currentTimeMillis() - startTime); } catch (Throwable e) { - logger.error("Database.executeUpdate(" + sql + "): " + e.getMessage()); - throw new StorageObjectFailure("Database.executeUpdate(" + sql + "): " + e.getMessage(), e); + logQueryError(sql, System.currentTimeMillis() - startTime, e); + throw new DatabaseFailure("Database.executeUpdate(" + sql + "): " + e.getMessage(), e); } finally { freeConnection(con, pstmt); } - - logger.debug((System.currentTimeMillis() - startTime) + "ms. for: " + sql); return result; } /** - * Wertet ResultSetMetaData aus und setzt interne Daten entsprechend - * @param md ResultSetMetaData - * @exception StorageObjectException + * Processes the metadata for the table this Database object is responsible for. */ - private void evalMetaData(ResultSetMetaData md) throws StorageObjectFailure { - this.evaluatedMetaData = true; - this.metadataFields = new ArrayList(); - this.metadataLabels = new ArrayList(); - this.metadataNotNullFields = new ArrayList(); + private void processMetaData(ResultSetMetaData aMetaData) throws DatabaseFailure { + fieldNames = new ArrayList(); + fieldNameToType = new HashMap(); try { - int numFields = md.getColumnCount(); - this.metadataTypes = new int[numFields]; - - String aField; - int aType; + int numFields = aMetaData.getColumnCount(); + fieldTypes = new int[numFields]; for (int i = 1; i <= numFields; i++) { - aField = md.getColumnName(i); - metadataFields.add(aField); - metadataLabels.add(md.getColumnLabel(i)); - aType = md.getColumnType(i); - metadataTypes[i - 1] = aType; - - if (aField.equals(thePKeyName)) { - thePKeyType = aType; - thePKeyIndex = i; - } - - if (md.isNullable(i) == ResultSetMetaData.columnNullable) { - metadataNotNullFields.add(aField); - } + fieldNames.add(aMetaData.getColumnName(i)); + fieldTypes[i - 1] = aMetaData.getColumnType(i); + fieldNameToType.put(aMetaData.getColumnName(i), new Integer(aMetaData.getColumnType(i))); } } - catch (SQLException e) { - throwSQLException(e, "evalMetaData"); + catch (Throwable e) { + throw new DatabaseFailure(e); } } /** - * Wertet die Metadaten eines Resultsets fuer eine Tabelle aus, - * um die alle Columns und Typen einer Tabelle zu ermitteln. + * Retrieves metadata from the table this Database object represents */ - private void get_meta_data() throws StorageObjectFailure { - Connection con = null; - PreparedStatement pstmt = null; - String sql = "select * from " + theTable + " where 0=1"; + private void acquireMetaData() throws DatabaseFailure { + Connection connection = null; + PreparedStatement statement = null; + String sql = "select * from " + mainTable + " where 0=1"; try { - con = getPooledCon(); - pstmt = con.prepareStatement(sql); + connection = obtainConnection(); + statement = connection.prepareStatement(sql); logger.debug("METADATA: " + sql); - ResultSet rs = pstmt.executeQuery(); - evalMetaData(rs.getMetaData()); - rs.close(); + ResultSet resultSet = statement.executeQuery(); + try { + processMetaData(resultSet.getMetaData()); + } + finally { + resultSet.close(); + } } - catch (SQLException e) { - throwSQLException(e, "get_meta_data"); + catch (Throwable e) { + throw new DatabaseFailure(e); } finally { - freeConnection(con, pstmt); + freeConnection(connection, statement); } } - public Connection getPooledCon() throws StorageObjectFailure { - Connection con = null; + public Connection obtainConnection() throws DatabaseFailure { + try { + return MirGlobal.getDatabaseEngine().obtainConnection(); + } + catch (Exception e) { + throw new DatabaseFailure(e); + } + } + public void freeConnection(Connection aConnection) throws DatabaseFailure { try { - con = SQLManager.getInstance().requestConnection(); + MirGlobal.getDatabaseEngine().releaseConnection(aConnection); } - catch (SQLException e) { - logger.error("could not connect to the database " + e.getMessage()); + catch (Throwable t) { + logger.warn("Can't release connection: " + t.toString()); + } + } - throw new StorageObjectFailure("Could not connect to the database", e); + public void freeConnection(Connection aConnection, Statement aStatement) throws DatabaseFailure { + try { + aStatement.close(); + } + catch (Throwable t) { + logger.warn("Can't close statement", t); } - return con; + freeConnection(aConnection); } - public void freeConnection(Connection con, Statement stmt) - throws StorageObjectFailure { - SQLManager.closeStatement(stmt); - SQLManager.getInstance().returnConnection(con); + protected void _throwStorageObjectException(Exception e, String aFunction) + throws DatabaseFailure { + + if (e != null) { + logger.error(e.getMessage() + aFunction); + throw new DatabaseFailure(aFunction, e); + } } + /** - * Wertet SQLException aus und wirft dannach eine StorageObjectException - * @param sqe SQLException - * @param wo Funktonsname, in der die SQLException geworfen wurde - * @exception StorageObjectException + * Invalidates any cached entity list */ - protected void throwSQLException(SQLException sqe, String aFunction) throws StorageObjectFailure { - String state = ""; - String message = ""; - int vendor = 0; - - if (sqe != null) { - state = sqe.getSQLState(); - message = sqe.getMessage(); - vendor = sqe.getErrorCode(); + private void invalidateStore() { + // invalidating all EntityLists corresponding with entityClass + if (StoreUtil.extendsStorableEntity(entityClass)) { + StoreContainerType stoc_type = + StoreContainerType.valueOf(entityClass, StoreContainerType.STOC_TYPE_ENTITYLIST); + o_store.invalidate(stoc_type); } + } - String information = - "SQL Error: " + - "state= " + state + - ", vendor= " + vendor + - ", message=" + message + - ", function= " + aFunction; + /** + * Retrieves a binary value + */ + public byte[] getBinaryField(String aQuery) throws DatabaseFailure, SQLException { + Connection connection=null; + Statement statement=null; + InputStream inputStream; - logger.error(information); + try { + connection = obtainConnection(); + try { + connection.setAutoCommit(false); + statement = connection.createStatement(); + ResultSet resultSet = executeSql(statement, aQuery); + + if(resultSet!=null) { + if (resultSet.next()) { + if (resultSet.getMetaData().getColumnType(1) == java.sql.Types.BINARY) { + return resultSet.getBytes(1); + } + else { + inputStream = resultSet.getBlob(1).getBinaryStream(); + ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); + StreamCopier.copy(inputStream, outputStream); + return outputStream.toByteArray(); + } + } + resultSet.close(); + } + } + finally { + try { + connection.setAutoCommit(true); + } + catch (Throwable e) { + logger.error("EntityImages.getImage resetting transaction mode failed: " + e.toString()); + e.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE)); + } + + try { + freeConnection(connection, statement); + } + catch (Throwable e) { + logger.error("EntityImages.getImage freeing connection failed: " +e.toString()); + } + + } + } + catch (Throwable t) { + logger.error("EntityImages.getImage failed: " + t.toString()); + t.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE)); - throw new StorageObjectFailure(information, sqe); + throw new DatabaseFailure(t); + } + + return new byte[0]; } - protected void _throwStorageObjectException(Exception e, String aFunction) - throws StorageObjectFailure { + /** + * Sets a binary value for a particular field in a record specified by its identifier + */ + public void setBinaryField(String aFieldName, String anObjectId, byte aData[]) throws DatabaseFailure, SQLException { + PreparedStatement statement = null; + Connection connection = obtainConnection(); - if (e != null) { - logger.error(e.getMessage() + aFunction); - throw new StorageObjectFailure(aFunction, e); + try { + connection.setAutoCommit(false); + try { + // are we using bytea ? + if (getFieldType(aFieldName) == java.sql.Types.BINARY) { + statement = connection.prepareStatement( + "update " + mainTable + " set " + aFieldName + " = ? where " + getIdFieldName() + "=" + Integer.parseInt(anObjectId)); + statement.setBytes(1, aData); + statement.execute(); + connection.commit(); + } + // or the old oid's + else { + PGConnection postgresqlConnection = (org.postgresql.PGConnection) ((DelegatingConnection) connection).getDelegate(); + LargeObjectManager lobManager = postgresqlConnection.getLargeObjectAPI(); + int oid = lobManager.create(LargeObjectManager.READ | LargeObjectManager.WRITE); + LargeObject obj = lobManager.open(oid, LargeObjectManager.WRITE); // Now open the file File file = + obj.write(aData); + obj.close(); + statement = connection.prepareStatement( + "update " + mainTable + " set " + aFieldName + " = ? where " + getIdFieldName() + "=" + Integer.parseInt(anObjectId)); + statement.setInt(1, oid); + statement.execute(); + connection.commit(); + } + } + finally { + connection.setAutoCommit(true); + } + } + finally { + freeConnection(connection, statement); } } /** - * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach - * eine StorageObjectException - * @param message Nachricht mit dem Fehler - * @exception StorageObjectException + * Can be overridden to specify a primary key sequence name not named according to + * the convention (tablename _id_seq) */ - void throwStorageObjectException(String aMessage) throws StorageObjectFailure { - logger.error(aMessage); - throw new StorageObjectFailure(aMessage, null); + protected String getPrimaryKeySequence() { + return mainTable+"_id_seq"; + } + + /** + * Can be called by subclasses to specify fields that are binary, and that shouldn't + * be updated outside of {@link #setBinaryField} + * + * @param aBinaryField The field name of the binary field + */ + protected void markBinaryField(String aBinaryField) { + binaryFields.add(aBinaryField); + } + + private void logQueryBefore(String aQuery) { + logger.debug("about to perform QUERY " + aQuery); +// (new Throwable()).printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE)); + } + + private void logQueryAfter(String aQuery, long aTime) { + logger.info("QUERY " + aQuery + " took " + aTime + "ms."); + } + + private void logQueryError(String aQuery, long aTime, Throwable anException) { + logger.error("QUERY " + aQuery + " took " + aTime + "ms, but threw exception " + anException.toString()); + } + + private int getFieldType(String aFieldName) { + if (fieldNameToType == null) { + acquireMetaData(); + } + + return ((Integer) fieldNameToType.get(aFieldName)).intValue(); } }