2 * Copyright (C) 2001, 2002 The Mir-coders group
4 * This file is part of Mir.
6 * Mir is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 2 of the License, or
9 * (at your option) any later version.
11 * Mir is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
16 * You should have received a copy of the GNU General Public License
17 * along with Mir; if not, write to the Free Software
18 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20 * In addition, as a special exception, The Mir-coders gives permission to link
21 * the code of this program with any library licensed under the Apache Software License,
22 * The Sun (tm) Java Advanced Imaging library (JAI), The Sun JIMI library
23 * (or with modified versions of the above that use the same license as the above),
24 * and distribute linked combinations including the two. You must obey the
25 * GNU General Public License in all respects for all of the code used other than
26 * the above mentioned libraries. If you modify this file, you may extend this
27 * exception to your version of the file, but you are not obligated to do so.
28 * If you do not wish to do so, delete this exception statement from your version.
32 import mir.config.MirPropertiesConfiguration;
33 import mir.entity.Entity;
34 import mir.entity.EntityList;
35 import mir.entity.StorableObjectEntity;
36 import mir.entity.AbstractEntity;
37 import mir.log.LoggerWrapper;
38 import mir.misc.StringUtil;
39 import mir.storage.store.*;
40 import mir.util.JDBCStringRoutines;
41 import mircoders.global.MirGlobal;
45 import java.text.ParseException;
46 import java.text.SimpleDateFormat;
49 import org.apache.commons.dbcp.DelegatingConnection;
50 import org.postgresql.PGConnection;
51 import org.postgresql.largeobject.LargeObjectManager;
52 import org.postgresql.largeobject.LargeObject;
55 * Implements database access.
57 * @version $Id: Database.java,v 1.44.2.26 2005/01/23 15:36:03 zapata Exp $
61 public class Database {
62 private static Class GENERIC_ENTITY_CLASS = mir.entity.StorableObjectEntity.class;
63 protected static final ObjectStore o_store = ObjectStore.getInstance();
64 private static final int _millisPerHour = 60 * 60 * 1000;
66 protected LoggerWrapper logger;
68 protected MirPropertiesConfiguration configuration;
69 protected String mainTable;
70 protected String primaryKeySequence = null;
71 protected String primaryKeyField = "id";
73 protected List fieldNames;
74 protected int[] fieldTypes;
75 protected Map fieldNameToType;
77 protected Class entityClass;
78 private int defaultLimit;
81 SimpleDateFormat internalDateFormat;
82 SimpleDateFormat userInputDateFormat;
85 * Kontruktor bekommt den Filenamen des Konfigurationsfiles ?bergeben.
86 * Aus diesem file werden <code>Database.Logfile</code>,
87 * <code>Database.Username</code>,<code>Database.Password</code>,
88 * <code>Database.Host</code> und <code>Database.Adaptor</code>
89 * ausgelesen und ein Broker f?r die Verbindugen zur Datenbank
92 public Database() throws StorageObjectFailure {
93 configuration = MirPropertiesConfiguration.instance();
94 logger = new LoggerWrapper("Database");
95 timezone = TimeZone.getTimeZone(configuration.getString("Mir.DefaultTimezone"));
96 internalDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
97 internalDateFormat.setTimeZone(timezone);
99 userInputDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
100 userInputDateFormat.setTimeZone(timezone);
102 String theAdaptorName = configuration.getString("Database.Adaptor");
103 defaultLimit = Integer.parseInt(configuration.getString("Database.Limit"));
106 entityClass = GENERIC_ENTITY_CLASS;
108 catch (Throwable e) {
109 logger.error("Error in Database() constructor with " + theAdaptorName + " -- " + e.getMessage());
110 throw new StorageObjectFailure("Error in Database() constructor.", e);
114 public java.lang.Class getEntityClass() {
118 public Entity createNewEntity() throws StorageObjectFailure {
120 AbstractEntity result = (AbstractEntity) entityClass.newInstance();
121 result.setStorage(this);
125 catch (Throwable t) {
126 throw new StorageObjectFailure(t);
131 * Liefert die Standardbeschr?nkung von select-Statements zur?ck, also
132 * wieviel Datens?tze per Default selektiert werden.
134 * @return Standard-Anzahl der Datens?tze
136 public int getLimit() {
140 public String getIdFieldName() {
141 return primaryKeyField;
145 * Liefert den Namen der Tabelle, auf das sich das Datenbankobjekt bezieht.
147 * @return Name der Tabelle
149 public String getTableName() {
154 * Returns the id that was most recently added to the database
156 private String getLatestInsertedId(Connection aConnection) throws SQLException {
157 if (primaryKeySequence==null)
158 primaryKeySequence = mainTable+"_id_seq";
160 PreparedStatement statement = aConnection.prepareStatement("select currval('" + primaryKeySequence + "')");
162 ResultSet rs = statement.executeQuery();
164 return rs.getString(1);
168 * Returns a list of field names for this <code>Database</code>
170 public List getFieldNames() throws StorageObjectFailure {
171 if (fieldNames == null) {
179 * Gets value out of ResultSet according to type and converts to String
180 * @param rs ResultSet.
181 * @param aType a type from java.sql.Types.*
182 * @param valueIndex index in ResultSet
183 * @return returns the value as String. If no conversion is possible
184 * /unsupported value/ is returned
186 private String getValueAsString(ResultSet rs, int valueIndex, int aType)
187 throws StorageObjectFailure {
188 String outValue = null;
193 case java.sql.Types.BIT:
194 outValue = (rs.getBoolean(valueIndex) == true) ? "1" : "0";
198 case java.sql.Types.INTEGER:
199 case java.sql.Types.SMALLINT:
200 case java.sql.Types.TINYINT:
201 case java.sql.Types.BIGINT:
203 int out = rs.getInt(valueIndex);
206 outValue = new Integer(out).toString();
211 case java.sql.Types.NUMERIC:
213 /** todo Numeric can be float or double depending upon
214 * metadata.getScale() / especially with oracle */
215 long outl = rs.getLong(valueIndex);
218 outValue = new Long(outl).toString();
223 case java.sql.Types.REAL:
225 float tempf = rs.getFloat(valueIndex);
231 int tempf_int = (int) tempf;
232 tempf = (float) tempf_int;
234 outValue = "" + tempf;
235 outValue = outValue.replace('.', ',');
240 case java.sql.Types.DOUBLE:
242 double tempd = rs.getDouble(valueIndex);
248 int tempd_int = (int) tempd;
249 tempd = (double) tempd_int;
251 outValue = "" + tempd;
252 outValue = outValue.replace('.', ',');
257 case java.sql.Types.CHAR:
258 case java.sql.Types.VARCHAR:
259 case java.sql.Types.LONGVARCHAR:
260 outValue = rs.getString(valueIndex);
264 case java.sql.Types.LONGVARBINARY:
265 outValue = rs.getString(valueIndex);
269 case java.sql.Types.TIMESTAMP:
271 // it's important to use Timestamp here as getting it
272 // as a string is undefined and is only there for debugging
273 // according to the API. we can make it a string through formatting.
275 Timestamp timestamp = (rs.getTimestamp(valueIndex));
278 java.util.Date date = new java.util.Date(timestamp.getTime());
280 Calendar calendar = new GregorianCalendar();
281 calendar.setTime(date);
282 calendar.setTimeZone(timezone);
283 outValue = internalDateFormat.format(date);
285 int offset = calendar.get(Calendar.ZONE_OFFSET) + calendar.get(Calendar.DST_OFFSET);
286 String tzOffset = StringUtil.zeroPaddingNumber(Math.abs(offset) / _millisPerHour, 2, 2);
289 outValue = outValue + "-";
291 outValue = outValue + "+";
292 outValue = outValue + tzOffset;
298 outValue = "<unsupported value>";
299 logger.warn("Unsupported Datatype: at " + valueIndex + " (" + aType + ")");
301 } catch (SQLException e) {
302 throw new StorageObjectFailure("Could not get Value out of Resultset -- ",
311 * select-Operator um einen Datensatz zu bekommen.
312 * @param id Primaerschluessel des Datensatzes.
313 * @return liefert EntityObject des gefundenen Datensatzes oder null.
315 public Entity selectById(String id) throws StorageObjectExc {
316 if ((id == null) || id.equals("")) {
317 throw new StorageObjectExc("Database.selectById: Missing id");
320 // ask object store for object
321 if (StoreUtil.extendsStorableEntity(entityClass)) {
322 String uniqueId = id;
324 if (entityClass.equals(StorableObjectEntity.class)) {
325 uniqueId += ("@" + mainTable);
328 StoreIdentifier search_sid = new StoreIdentifier(entityClass, uniqueId);
329 logger.debug("CACHE: (dbg) looking for sid " + search_sid.toString());
331 Entity hit = (Entity) o_store.use(search_sid);
338 Statement stmt = null;
339 Connection con = obtainConnection();
340 Entity returnEntity = null;
345 /** todo better prepared statement */
347 "select * from " + mainTable + " where " + primaryKeyField + "=" + id;
348 stmt = con.createStatement();
349 rs = executeSql(stmt, selectSql);
353 returnEntity = makeEntityFromResultSet(rs);
356 logger.warn("No data for id: " + id + " in table " + mainTable);
362 logger.warn("No Data for Id " + id + " in Table " + mainTable);
365 catch (SQLException sqe) {
366 throwSQLException(sqe, "selectById");
369 catch (NumberFormatException e) {
370 logger.error("ID is no number: " + id);
373 freeConnection(con, stmt);
380 * This method makes it possible to make selects across multiple tables
382 * @param mainTablePrefix prefix for the mainTable
383 * @param extraTables a vector of tables for relational select
384 * @param aWhereClause whereClause
385 * @return EntityList of selected Objects
386 * @throws StorageObjectFailure
389 public EntityList selectByWhereClauseWithExtraTables(String mainTablePrefix,
390 List extraTables, String aWhereClause )
391 throws StorageObjectFailure {
392 return selectByWhereClause( mainTablePrefix, extraTables, aWhereClause, "", 0, defaultLimit);
395 public EntityList selectByFieldValue(String aField, String aValue) throws StorageObjectFailure {
396 return selectByFieldValue(aField, aValue, 0);
399 public EntityList selectByFieldValue(String aField, String aValue, int offset) throws StorageObjectFailure {
400 return selectByWhereClause(aField + "='" + JDBCStringRoutines.escapeStringLiteral(aValue)+"'", offset);
404 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
405 * Also offset wird der erste Datensatz genommen.
407 * @param where where-Clause
408 * @return EntityList mit den gematchten Entities
409 * @exception StorageObjectFailure
411 public EntityList selectByWhereClause(String where) throws StorageObjectFailure {
412 return selectByWhereClause(where, 0);
416 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
417 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
419 * @param whereClause where-Clause
420 * @param offset ab welchem Datensatz.
421 * @return EntityList mit den gematchten Entities
422 * @exception StorageObjectFailure
424 public EntityList selectByWhereClause(String whereClause, int offset) throws StorageObjectFailure {
425 return selectByWhereClause(whereClause, null, offset);
429 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
430 * Also offset wird der erste Datensatz genommen.
431 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
433 * @param where where-Clause
434 * @param order orderBy-Clause
435 * @return EntityList mit den gematchten Entities
436 * @exception StorageObjectFailure
438 public EntityList selectByWhereClause(String where, String order) throws StorageObjectFailure {
439 return selectByWhereClause(where, order, 0);
442 public EntityList selectByWhereClause(String mainTablePrefix, List extraTables, String where, String order) throws StorageObjectFailure {
443 return selectByWhereClause(mainTablePrefix, extraTables, where, order, 0, defaultLimit);
447 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
448 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
450 * @param whereClause where-Clause
451 * @param orderBy orderBy-Clause
452 * @param offset ab welchem Datensatz
453 * @return EntityList mit den gematchten Entities
454 * @exception StorageObjectFailure
456 public EntityList selectByWhereClause(String whereClause, String orderBy, int offset) throws StorageObjectFailure {
457 return selectByWhereClause(whereClause, orderBy, offset, defaultLimit);
461 * select-Operator returns EntityList with matching rows in Database.
462 * @param aWhereClause where-Clause
463 * @param anOrderByClause orderBy-Clause
464 * @param offset ab welchem Datensatz
465 * @param limit wieviele Datens?tze
466 * @return EntityList mit den gematchten Entities
467 * @exception StorageObjectFailure
469 public EntityList selectByWhereClause(String aWhereClause, String anOrderByClause,
470 int offset, int limit) throws StorageObjectFailure {
471 return selectByWhereClause("", null, aWhereClause, anOrderByClause, offset, limit);
476 * select-Operator returns EntityList with matching rows in Database.
477 * @param aWhereClause where-Clause
478 * @param anOrderByClause orderBy-Clause
479 * @param anOffset ab welchem Datensatz
480 * @param aLimit wieviele Datens?tze
481 * @return EntityList mit den gematchten Entities
482 * @exception StorageObjectFailure
484 public EntityList selectByWhereClause(
485 String aMainTablePrefix, List anExtraTables,
486 String aWhereClause, String anOrderByClause,
487 int anOffset, int aLimit) throws StorageObjectFailure {
489 // TODO get rid of emtpy Strings in anExtraTables
490 // make anExtraTables null, if single empty String in it
491 // cause StringUtil.splitString puts in emptyString
493 if (anExtraTables!=null && ((String) anExtraTables.get(0)).trim().equals("")){
497 String useTable = mainTable;
498 String selection = "*";
500 if (aMainTablePrefix != null && aMainTablePrefix.trim().length() > 0) {
501 useTable += " " + aMainTablePrefix;
502 selection = aMainTablePrefix.trim() + ".*";
505 // check o_store for entitylist
506 // only if no relational select
507 if (anExtraTables==null) {
508 if (StoreUtil.extendsStorableEntity(entityClass)) {
509 StoreIdentifier searchSid = new StoreIdentifier(entityClass,
510 StoreContainerType.STOC_TYPE_ENTITYLIST,
511 StoreUtil.getEntityListUniqueIdentifierFor(mainTable,
512 aWhereClause, anOrderByClause, anOffset, aLimit));
513 EntityList hit = (EntityList) o_store.use(searchSid);
522 EntityList theReturnList = null;
523 Connection connection = null;
524 Statement statement = null;
527 // build sql-statement
529 if ((aWhereClause != null) && (aWhereClause.trim().length() == 0)) {
533 StringBuffer selectSql =
534 new StringBuffer("select "+selection+" from ").append(useTable);
536 // append extratables, if necessary
537 if (anExtraTables!=null) {
538 for (int i=0;i < anExtraTables.size();i++) {
539 if (!anExtraTables.get(i).equals("")) {
540 selectSql.append( ", " + anExtraTables.get(i));
545 if (aWhereClause != null) {
546 selectSql.append(" where ").append(aWhereClause);
549 if ((anOrderByClause != null) && !(anOrderByClause.trim().length() == 0)) {
550 selectSql.append(" order by ").append(anOrderByClause);
553 if ((aLimit > -1) && (anOffset > -1)) {
554 selectSql.append(" LIMIT ").append(aLimit+1).append(" OFFSET ").append(anOffset);
559 connection = obtainConnection();
560 statement = connection.createStatement();
561 boolean hasMore = false;
564 resultSet = executeSql(statement, selectSql.toString());
566 if (resultSet != null) {
567 theReturnList = new EntityList();
568 Entity theResultEntity;
570 while (((aLimit == -1) || (position<aLimit)) && resultSet.next()) {
571 theResultEntity = makeEntityFromResultSet(resultSet);
572 theReturnList.add(theResultEntity);
575 hasMore = resultSet.next();
579 if (theReturnList != null) {
580 // now we decide if we have to know an overall count...
581 theReturnList.setOffset(anOffset);
582 theReturnList.setWhere(aWhereClause);
583 theReturnList.setOrder(anOrderByClause);
584 theReturnList.setStorage(this);
585 theReturnList.setLimit(aLimit);
588 theReturnList.setNextBatch(anOffset + aLimit);
591 if (anExtraTables==null && StoreUtil.extendsStorableEntity(entityClass)) {
592 StoreIdentifier sid = theReturnList.getStoreIdentifier();
593 logger.debug("CACHE (add): " + sid.toString());
598 catch (SQLException sqe) {
599 throwSQLException(sqe, "selectByWhereClause");
603 if (connection != null) {
604 freeConnection(connection, statement);
606 } catch (Throwable t) {
610 return theReturnList;
613 private Entity makeEntityFromResultSet(ResultSet rs)
614 throws StorageObjectFailure {
615 Map theResultHash = new HashMap();
616 String theResult = null;
618 Entity returnEntity = null;
621 if (StoreUtil.extendsStorableEntity(entityClass)) {
622 StoreIdentifier searchSid = StorableObjectEntity.getStoreIdentifier(this,
624 Entity hit = (Entity) o_store.use(searchSid);
625 if (hit != null) return hit;
628 for (int i = 0; i < getFieldNames().size(); i++) {
629 // alle durchlaufen bis nix mehr da
630 type = fieldTypes[i];
632 if (type == java.sql.Types.LONGVARBINARY) {
633 InputStreamReader is =
634 (InputStreamReader) rs.getCharacterStream(i + 1);
637 char[] data = new char[32768];
638 StringBuffer theResultString = new StringBuffer();
641 while ((len = is.read(data)) > 0) {
642 theResultString.append(data, 0, len);
646 theResult = theResultString.toString();
653 theResult = getValueAsString(rs, (i + 1), type);
656 if (theResult != null) {
657 theResultHash.put(getFieldNames().get(i), theResult);
661 if (entityClass != null) {
662 returnEntity = createNewEntity();
663 returnEntity.setFieldValues(theResultHash);
665 if (returnEntity instanceof StorableObject) {
666 logger.debug("CACHE: ( in) " + returnEntity.getId() + " :" + mainTable);
667 o_store.add(((StorableObject) returnEntity).getStoreIdentifier());
670 throwStorageObjectException("Internal Error: entityClass not set!");
673 catch (IOException e) {
674 throwStorageObjectException("IOException! -- " + e.getMessage());
676 catch (SQLException sqe) {
677 throwSQLException(sqe, "makeEntityFromResultSet");
686 * Inserts an entity into the database.
689 * @return der Wert des Primary-keys der eingef?gten Entity
691 public String insert(Entity anEntity) throws StorageObjectFailure {
694 String returnId = null;
695 Connection con = null;
696 PreparedStatement pstmt = null;
699 StringBuffer f = new StringBuffer();
700 StringBuffer v = new StringBuffer();
703 boolean firstField = true;
706 for (int i = 0; i < getFieldNames().size(); i++) {
707 aField = (String) getFieldNames().get(i);
709 if (!aField.equals(primaryKeyField)) {
713 if (!anEntity.hasFieldValue(aField) && (
714 aField.equals("webdb_create") ||
715 aField.equals("webdb_lastchange"))) {
719 if (anEntity.hasFieldValue(aField)) {
722 JDBCStringRoutines.escapeStringLiteral(anEntity.getFieldValue(aField)) + "'";
726 // wenn Wert gegeben, dann einbauen
727 if (aValue != null) {
728 if (firstField == false) {
744 StringBuffer sqlBuf =
745 new StringBuffer("insert into ").append(mainTable).append("(").append(f)
746 .append(") values (").append(v).append(")");
747 String sql = sqlBuf.toString();
750 con = obtainConnection();
751 con.setAutoCommit(false);
752 pstmt = con.prepareStatement(sql);
754 int ret = pstmt.executeUpdate();
761 // pstmt = con.prepareStatement("select currval('" + + "_id_seq')");
763 returnId = getLatestInsertedId(con);
764 anEntity.setId(returnId);
766 catch (SQLException sqe) {
767 throwSQLException(sqe, "insert");
771 con.setAutoCommit(true);
773 catch (Exception e) {
776 freeConnection(con, pstmt);
779 /** todo store entity in o_store */
784 * Updates an entity in the database
788 public void update(Entity theEntity) throws StorageObjectFailure {
789 Connection con = null;
790 PreparedStatement pstmt = null;
792 /** todo this is stupid: why do we prepare statement, when we
793 * throw it away afterwards. should be regular statement
794 * update/insert could better be one routine called save()
795 * that chooses to either insert or update depending if we
796 * have a primary key in the entity. i don't know if we
797 * still need the streamed input fields. // rk */
799 /** todo extension: check if Entity did change, otherwise we don't need
800 * the roundtrip to the database */
801 /** invalidating corresponding entitylists in o_store*/
805 String id = theEntity.getId();
807 StringBuffer fv = new StringBuffer();
808 boolean firstField = true;
810 // build sql statement
811 for (int i = 0; i < getFieldNames().size(); i++) {
812 aField = (String) getFieldNames().get(i);
815 // todo if entity.hasFieldValue returns false, then the value should be stored as null
816 if (!(aField.equals(primaryKeyField) ||
817 aField.equals("webdb_create") ||
818 aField.equals("webdb_lastchange"))) {
819 if (theEntity.hasFieldValue(aField)) {
820 if (firstField == false) {
827 fv.append(aField).append("='").append(JDBCStringRoutines.escapeStringLiteral(theEntity.getFieldValue(aField))).append("'");
829 // fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getFieldValue(aField))).append("'");
835 new StringBuffer("update ").append(mainTable).append(" set ").append(fv);
838 if (getFieldNames().contains("webdb_lastchange")) {
839 sql.append(",webdb_lastchange=NOW()");
842 // special case: the webdb_create requires the field in yyyy-mm-dd HH:mm
843 // format so anything extra will be ignored. -mh
844 if (getFieldNames().contains("webdb_create") &&
845 theEntity.hasFieldValue("webdb_create")) {
846 // minimum of 10 (yyyy-mm-dd)...
847 if (theEntity.getFieldValue("webdb_create").length() >= 10) {
848 String dateString = theEntity.getFieldValue("webdb_create");
850 // if only 10, then add 00:00 so it doesn't throw a ParseException
851 if (dateString.length() == 10) {
852 dateString = dateString + " 00:00";
857 java.util.Date d = userInputDateFormat.parse(dateString);
858 // Timestamp tStamp = new Timestamp(d.getTime());
859 sql.append(",webdb_create='" + JDBCStringRoutines.formatDate(d) + "'");
861 catch (ParseException e) {
862 throw new StorageObjectFailure(e);
867 sql.append(" where id=").append(id);
868 logQueryBefore(sql.toString());
871 con = obtainConnection();
872 con.setAutoCommit(false);
873 pstmt = con.prepareStatement(sql.toString());
875 pstmt.executeUpdate();
877 catch (SQLException sqe) {
878 throwSQLException(sqe, "update");
882 con.setAutoCommit(true);
884 catch (Exception e) {
888 freeConnection(con, pstmt);
894 * @param id des zu loeschenden Datensatzes
895 * @return boolean liefert true zurueck, wenn loeschen erfolgreich war.
897 public boolean delete(String id) throws StorageObjectFailure {
898 // ostore send notification
899 if (StoreUtil.extendsStorableEntity(entityClass)) {
900 String uniqueId = id;
902 if (entityClass.equals(StorableObjectEntity.class)) {
903 uniqueId += ("@" + mainTable);
906 logger.debug("CACHE: (del) " + id);
908 StoreIdentifier search_sid =
909 new StoreIdentifier(entityClass,
910 StoreContainerType.STOC_TYPE_ENTITY, uniqueId);
911 o_store.invalidate(search_sid);
914 /** todo could be prepared Statement */
915 Statement stmt = null;
916 Connection con = null;
919 "delete from " + mainTable + " where " + primaryKeyField + "='" + id + "'";
923 con = obtainConnection();
924 stmt = con.createStatement();
925 res = stmt.executeUpdate(sql);
927 catch (SQLException sqe) {
928 throwSQLException(sqe, "delete");
931 freeConnection(con, stmt);
936 return (res > 0) ? true : false;
940 * Deletes entities based on a where clause
942 public int deleteByWhereClause(String aWhereClause) throws StorageObjectFailure {
945 Statement stmt = null;
946 Connection con = null;
949 "delete from " + mainTable + " where " + aWhereClause;
951 //theLog.printInfo("DELETE " + sql);
953 con = obtainConnection();
954 stmt = con.createStatement();
955 res = stmt.executeUpdate(sql);
957 catch (SQLException sqe) {
958 throwSQLException(sqe, "delete");
961 freeConnection(con, stmt);
967 /* noch nicht implementiert.
968 * @return immer false
970 public boolean delete(EntityList theEntityList) {
975 * Diese Methode fuehrt den Sqlstring <i>sql</i> aus und timed im Logfile.
976 * @param stmt Statemnt
977 * @param sql Sql-String
979 public ResultSet executeSql(Statement stmt, String sql)
980 throws StorageObjectFailure, SQLException {
983 long startTime = System.currentTimeMillis();
985 rs = stmt.executeQuery(sql);
987 logQueryAfter(sql, (System.currentTimeMillis() - startTime));
989 catch (SQLException e) {
990 logQueryError(sql, (System.currentTimeMillis() - startTime), e);
997 private Map processRow(ResultSet aResultSet) throws StorageObjectFailure {
999 Map result = new HashMap();
1000 ResultSetMetaData metaData = aResultSet.getMetaData();
1001 int nrColumns = metaData.getColumnCount();
1002 for (int i=0; i<nrColumns; i++) {
1003 result.put(metaData.getColumnName(i+1), getValueAsString(aResultSet, i+1, metaData.getColumnType(i+1)));
1008 catch (Throwable e) {
1009 throw new StorageObjectFailure(e);
1014 * Executes 1 sql statement and returns the results as a <code>List</code> of
1017 public List executeFreeSql(String sql, int aLimit) throws StorageObjectFailure, StorageObjectExc {
1018 Connection connection = null;
1019 Statement statement = null;
1021 List result = new ArrayList();
1022 connection = obtainConnection();
1023 statement = connection.createStatement();
1024 ResultSet resultset = executeSql(statement, sql);
1026 while (resultset.next() && result.size() < aLimit) {
1027 result.add(processRow(resultset));
1036 catch (Throwable e) {
1037 throw new StorageObjectFailure(e);
1040 if (connection!=null) {
1041 freeConnection(connection, statement);
1047 * Executes 1 sql statement and returns the first result row as a <code>Map</code>s
1048 * (<code>null</code> if there wasn't any row)
1050 public Map executeFreeSingleRowSql(String anSqlStatement) throws StorageObjectFailure, StorageObjectExc {
1052 List resultList = executeFreeSql(anSqlStatement, 1);
1054 if (resultList.size()>0)
1055 return (Map) resultList.get(0);
1062 catch (Throwable t) {
1063 throw new StorageObjectFailure(t);
1068 * Executes 1 sql statement and returns the first column of the first result row as a <code>String</code>s
1069 * (<code>null</code> if there wasn't any row)
1071 public String executeFreeSingleValueSql(String sql) throws StorageObjectFailure, StorageObjectExc {
1072 Map row = executeFreeSingleRowSql(sql);
1077 Iterator i = row.values().iterator();
1079 return (String) i.next();
1084 public int getSize(String where) throws SQLException, StorageObjectFailure {
1085 return getSize("", null, where);
1088 * returns the number of rows in the table
1090 public int getSize(String mainTablePrefix, List extraTables, String where) throws SQLException, StorageObjectFailure {
1092 String useTable = mainTable;
1093 if (mainTablePrefix!=null && mainTablePrefix.trim().length()>0) {
1094 useTable+=" "+mainTablePrefix;
1096 StringBuffer countSql =
1097 new StringBuffer("select count(*) from ").append(useTable);
1098 // append extratables, if necessary
1099 if (extraTables!=null) {
1100 for (int i=0;i < extraTables.size();i++) {
1101 if (!extraTables.get(i).equals("")) {
1102 countSql.append( ", " + extraTables.get(i));
1107 if ((where != null) && (where.length() != 0)) {
1108 countSql.append( " where " + where);
1111 Connection con = null;
1112 Statement stmt = null;
1114 logQueryBefore(countSql.toString());
1115 long startTime = System.currentTimeMillis();
1118 con = obtainConnection();
1119 stmt = con.createStatement();
1121 ResultSet rs = executeSql(stmt, countSql.toString());
1124 result = rs.getInt(1);
1127 catch (SQLException e) {
1128 logger.error("Database.getSize: " + e.getMessage());
1131 freeConnection(con, stmt);
1133 logQueryAfter(countSql.toString(), (System.currentTimeMillis() - startTime));
1138 public int executeUpdate(Statement stmt, String sql)
1139 throws StorageObjectFailure, SQLException {
1142 logQueryBefore(sql);
1143 long startTime = System.currentTimeMillis();
1146 rs = stmt.executeUpdate(sql);
1148 logQueryAfter(sql, (System.currentTimeMillis() - startTime));
1150 catch (SQLException e) {
1151 logQueryError(sql, (System.currentTimeMillis() - startTime), e);
1158 public int executeUpdate(String sql)
1159 throws StorageObjectFailure, SQLException {
1161 Connection con = null;
1162 PreparedStatement pstmt = null;
1164 logQueryBefore(sql);
1165 long startTime = System.currentTimeMillis();
1167 con = obtainConnection();
1168 pstmt = con.prepareStatement(sql);
1169 result = pstmt.executeUpdate();
1170 logQueryAfter(sql, System.currentTimeMillis() - startTime);
1172 catch (Throwable e) {
1173 logQueryError(sql, System.currentTimeMillis() - startTime, e);
1174 throw new StorageObjectFailure("Database.executeUpdate(" + sql + "): " + e.getMessage(), e);
1177 freeConnection(con, pstmt);
1183 * Processes the metadata for the table this Database object is responsible for.
1185 private void processMetaData(ResultSetMetaData aMetaData) throws StorageObjectFailure {
1186 fieldNames = new ArrayList();
1187 fieldNameToType = new HashMap();
1190 int numFields = aMetaData.getColumnCount();
1191 fieldTypes = new int[numFields];
1193 for (int i = 1; i <= numFields; i++) {
1194 fieldNames.add(aMetaData.getColumnName(i));
1195 fieldTypes[i - 1] = aMetaData.getColumnType(i);
1196 fieldNameToType.put(aMetaData.getColumnName(i), new Integer(aMetaData.getColumnType(i)));
1199 catch (SQLException e) {
1200 throwSQLException(e, "processMetaData");
1205 * Retrieves metadata from the table this Database object represents
1207 private void acquireMetaData() throws StorageObjectFailure {
1208 Connection connection = null;
1209 PreparedStatement statement = null;
1210 String sql = "select * from " + mainTable + " where 0=1";
1213 connection = obtainConnection();
1214 statement = connection.prepareStatement(sql);
1216 logger.debug("METADATA: " + sql);
1217 ResultSet resultSet = statement.executeQuery();
1219 processMetaData(resultSet.getMetaData());
1225 catch (SQLException e) {
1226 throwSQLException(e, "acquireMetaData");
1229 freeConnection(connection, statement);
1233 public Connection obtainConnection() throws StorageObjectFailure {
1235 return MirGlobal.getDatabaseEngine().obtainConnection();
1237 catch (Exception e) {
1238 throw new StorageObjectFailure(e);
1242 public void freeConnection(Connection aConnection, Statement aStatement) throws StorageObjectFailure {
1246 catch (Throwable t) {
1247 logger.warn("Can't close statemnet: " + t.toString());
1251 MirGlobal.getDatabaseEngine().releaseConnection(aConnection);
1253 catch (Throwable t) {
1254 logger.warn("Can't release connection: " + t.toString());
1259 * Wertet SQLException aus und wirft dannach eine StorageObjectException
1260 * @param sqe SQLException
1261 * @param aFunction Funktonsname, in der die SQLException geworfen wurde
1263 protected void throwSQLException(SQLException sqe, String aFunction) throws StorageObjectFailure {
1265 String message = "";
1269 state = sqe.getSQLState();
1270 message = sqe.getMessage();
1271 vendor = sqe.getErrorCode();
1274 String information =
1277 ", vendor= " + vendor +
1278 ", message=" + message +
1279 ", function= " + aFunction;
1281 logger.error(information);
1283 throw new StorageObjectFailure(information, sqe);
1286 protected void _throwStorageObjectException(Exception e, String aFunction)
1287 throws StorageObjectFailure {
1290 logger.error(e.getMessage() + aFunction);
1291 throw new StorageObjectFailure(aFunction, e);
1296 * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach
1297 * eine StorageObjectException
1298 * @param aMessage Nachricht mit dem Fehler
1299 * @exception StorageObjectFailure
1301 void throwStorageObjectException(String aMessage) throws StorageObjectFailure {
1302 logger.error(aMessage);
1303 throw new StorageObjectFailure(aMessage, null);
1307 * Invalidates any cached entity list
1309 private void invalidateStore() {
1310 // invalidating all EntityLists corresponding with entityClass
1311 if (StoreUtil.extendsStorableEntity(entityClass)) {
1312 StoreContainerType stoc_type =
1313 StoreContainerType.valueOf(entityClass, StoreContainerType.STOC_TYPE_ENTITYLIST);
1314 o_store.invalidate(stoc_type);
1319 * Retrieves a binary value
1321 public InputStream getBinaryField(String aQuery) throws StorageObjectFailure, SQLException {
1322 Connection connection=null;
1323 Statement statement=null;
1324 InputStream inputStream;
1325 InputStream imageInputStream = null;
1328 connection = obtainConnection();
1330 connection.setAutoCommit(false);
1331 statement = connection.createStatement();
1332 ResultSet resultSet = executeSql(statement, aQuery);
1334 if(resultSet!=null) {
1335 if (resultSet.next()) {
1336 if (resultSet.getMetaData().getColumnType(1) == java.sql.Types.BINARY) {
1337 byte[] data = resultSet.getBytes(1);
1338 imageInputStream = new ByteArrayInputStream(data);
1341 inputStream = resultSet.getBlob(1).getBinaryStream();
1342 imageInputStream = new BinaryFieldInputStream(inputStream, connection, statement);
1351 catch (Throwable t) {
1352 logger.error("EntityImages.getImage failed: " + t.toString());
1353 t.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1356 connection.setAutoCommit(true);
1358 catch (Throwable e) {
1359 logger.error("EntityImages.getImage resetting transaction mode failed: " + e.toString());
1360 e.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1364 freeConnection(connection, statement);
1366 catch (Throwable e) {
1367 logger.error("EntityImages.getImage freeing connection failed: " +e.toString());
1370 throw new StorageObjectFailure(t);
1373 return imageInputStream;
1377 * Sets a binary value for a particular field in a record specified by its identifier
1379 public void setBinaryField(String aFieldName, String anObjectId, byte aData[]) throws StorageObjectFailure, SQLException {
1380 PreparedStatement statement = null;
1381 Connection connection = obtainConnection();
1384 connection.setAutoCommit(false);
1386 // are we using bytea ?
1387 if (getFieldType(aFieldName) == java.sql.Types.BINARY) {
1388 statement = connection.prepareStatement(
1389 "update " + mainTable + " set " + aFieldName + " = ? where " + getIdFieldName() + "=" + Integer.parseInt(anObjectId));
1390 statement.setBytes(1, aData);
1391 statement.execute();
1392 connection.commit();
1396 PGConnection postgresqlConnection = (org.postgresql.PGConnection) ((DelegatingConnection) connection).getDelegate();
1397 LargeObjectManager lobManager = postgresqlConnection.getLargeObjectAPI();
1398 int oid = lobManager.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
1399 LargeObject obj = lobManager.open(oid, LargeObjectManager.WRITE); // Now open the file File file =
1402 statement = connection.prepareStatement(
1403 "update " + mainTable + " set " + aFieldName + " = ? where " + getIdFieldName() + "=" + Integer.parseInt(anObjectId));
1404 statement.setInt(1, oid);
1405 statement.execute();
1406 connection.commit();
1410 connection.setAutoCommit(true);
1414 freeConnection(connection, statement);
1418 private void logQueryBefore(String aQuery) {
1419 logger.debug("about to perform QUERY " + aQuery);
1420 // (new Throwable()).printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1423 private void logQueryAfter(String aQuery, long aTime) {
1424 logger.info("QUERY " + aQuery + " took " + aTime + "ms.");
1427 private void logQueryError(String aQuery, long aTime, Throwable anException) {
1428 logger.error("QUERY " + aQuery + " took " + aTime + "ms, but threw exception " + anException.toString());
1431 private int getFieldType(String aFieldName) {
1432 if (fieldNameToType == null) {
1436 return ((Integer) fieldNameToType.get(aFieldName)).intValue();
1441 * a small wrapper class that allows us to store the DB connection resources
1442 * that the BlobInputStream is using and free them upon closing of the stream
1444 private class BinaryFieldInputStream extends InputStream {
1445 InputStream inputStream;
1446 Connection connection;
1447 Statement statement;
1449 public BinaryFieldInputStream(InputStream aBlobInputStream, Connection aConnection, Statement aStatement ) {
1450 inputStream = aBlobInputStream;
1451 connection = aConnection;
1452 statement = aStatement;
1455 public void close () throws IOException {
1456 inputStream.close();
1458 connection.setAutoCommit(true);
1459 freeConnection(connection, statement);
1461 catch (Exception e) {
1462 throw new IOException("close(): "+e.toString());
1466 public int read() throws IOException {
1467 return inputStream.read();