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 java.io.ByteArrayInputStream;
33 import java.io.IOException;
34 import java.io.InputStream;
35 import java.io.InputStreamReader;
36 import java.sql.Connection;
37 import java.sql.PreparedStatement;
38 import java.sql.ResultSet;
39 import java.sql.ResultSetMetaData;
40 import java.sql.SQLException;
41 import java.sql.Statement;
42 import java.sql.Timestamp;
43 import java.text.ParseException;
44 import java.text.SimpleDateFormat;
45 import java.util.ArrayList;
46 import java.util.Calendar;
47 import java.util.GregorianCalendar;
48 import java.util.HashMap;
49 import java.util.Iterator;
50 import java.util.List;
52 import java.util.TimeZone;
54 import mir.config.MirPropertiesConfiguration;
55 import mir.entity.AbstractEntity;
56 import mir.entity.Entity;
57 import mir.entity.EntityList;
58 import mir.entity.StorableObjectEntity;
59 import mir.log.LoggerWrapper;
60 import mir.misc.StringUtil;
61 import mir.storage.store.ObjectStore;
62 import mir.storage.store.StorableObject;
63 import mir.storage.store.StoreContainerType;
64 import mir.storage.store.StoreIdentifier;
65 import mir.storage.store.StoreUtil;
66 import mir.util.JDBCStringRoutines;
67 import mircoders.global.MirGlobal;
69 import org.apache.commons.dbcp.DelegatingConnection;
70 import org.postgresql.PGConnection;
71 import org.postgresql.largeobject.LargeObject;
72 import org.postgresql.largeobject.LargeObjectManager;
75 * Implements database access.
77 * @version $Id: Database.java,v 1.44.2.28 2005/02/11 14:21:46 rhindes Exp $
81 public class Database {
82 private static Class GENERIC_ENTITY_CLASS = mir.entity.StorableObjectEntity.class;
83 protected static final ObjectStore o_store = ObjectStore.getInstance();
84 private static final int _millisPerHour = 60 * 60 * 1000;
86 protected LoggerWrapper logger;
88 protected MirPropertiesConfiguration configuration;
89 protected String mainTable;
90 protected String primaryKeySequence = null;
91 protected String primaryKeyField = "id";
93 protected List fieldNames;
94 protected int[] fieldTypes;
95 protected Map fieldNameToType;
97 protected Class entityClass;
98 private int defaultLimit;
101 SimpleDateFormat internalDateFormat;
102 SimpleDateFormat userInputDateFormat;
105 * Kontruktor bekommt den Filenamen des Konfigurationsfiles ?bergeben.
106 * Aus diesem file werden <code>Database.Logfile</code>,
107 * <code>Database.Username</code>,<code>Database.Password</code>,
108 * <code>Database.Host</code> und <code>Database.Adaptor</code>
109 * ausgelesen und ein Broker f?r die Verbindugen zur Datenbank
112 public Database() throws StorageObjectFailure {
113 configuration = MirPropertiesConfiguration.instance();
114 logger = new LoggerWrapper("Database");
115 timezone = TimeZone.getTimeZone(configuration.getString("Mir.DefaultTimezone"));
116 internalDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
117 internalDateFormat.setTimeZone(timezone);
119 userInputDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
120 userInputDateFormat.setTimeZone(timezone);
122 String theAdaptorName = configuration.getString("Database.Adaptor");
123 defaultLimit = Integer.parseInt(configuration.getString("Database.Limit"));
126 entityClass = GENERIC_ENTITY_CLASS;
128 catch (Throwable e) {
129 logger.error("Error in Database() constructor with " + theAdaptorName + " -- " + e.getMessage());
130 throw new StorageObjectFailure("Error in Database() constructor.", e);
134 public java.lang.Class getEntityClass() {
138 public Entity createNewEntity() throws StorageObjectFailure {
140 AbstractEntity result = (AbstractEntity) entityClass.newInstance();
141 result.setStorage(this);
145 catch (Throwable t) {
146 throw new StorageObjectFailure(t);
151 * Liefert die Standardbeschr?nkung von select-Statements zur?ck, also
152 * wieviel Datens?tze per Default selektiert werden.
154 * @return Standard-Anzahl der Datens?tze
156 public int getLimit() {
160 public String getIdFieldName() {
161 return primaryKeyField;
165 * Liefert den Namen der Tabelle, auf das sich das Datenbankobjekt bezieht.
167 * @return Name der Tabelle
169 public String getTableName() {
174 * Returns the id that was most recently added to the database
176 private String getLatestInsertedId(Connection aConnection) throws SQLException {
177 if (primaryKeySequence==null)
178 primaryKeySequence = mainTable+"_id_seq";
180 PreparedStatement statement = aConnection.prepareStatement("select currval('" + primaryKeySequence + "')");
182 ResultSet rs = statement.executeQuery();
184 return rs.getString(1);
188 * Returns a list of field names for this <code>Database</code>
190 public List getFieldNames() throws StorageObjectFailure {
191 if (fieldNames == null) {
199 * Gets value out of ResultSet according to type and converts to String
200 * @param rs ResultSet.
201 * @param aType a type from java.sql.Types.*
202 * @param valueIndex index in ResultSet
203 * @return returns the value as String. If no conversion is possible
204 * /unsupported value/ is returned
206 private String getValueAsString(ResultSet rs, int valueIndex, int aType)
207 throws StorageObjectFailure {
208 String outValue = null;
213 case java.sql.Types.BIT:
214 outValue = (rs.getBoolean(valueIndex) == true) ? "1" : "0";
218 case java.sql.Types.INTEGER:
219 case java.sql.Types.SMALLINT:
220 case java.sql.Types.TINYINT:
221 case java.sql.Types.BIGINT:
223 int out = rs.getInt(valueIndex);
226 outValue = new Integer(out).toString();
231 case java.sql.Types.NUMERIC:
233 /** todo Numeric can be float or double depending upon
234 * metadata.getScale() / especially with oracle */
235 long outl = rs.getLong(valueIndex);
238 outValue = new Long(outl).toString();
243 case java.sql.Types.REAL:
245 float tempf = rs.getFloat(valueIndex);
251 int tempf_int = (int) tempf;
252 tempf = (float) tempf_int;
254 outValue = "" + tempf;
255 outValue = outValue.replace('.', ',');
260 case java.sql.Types.DOUBLE:
262 double tempd = rs.getDouble(valueIndex);
268 int tempd_int = (int) tempd;
269 tempd = (double) tempd_int;
271 outValue = "" + tempd;
272 outValue = outValue.replace('.', ',');
277 case java.sql.Types.CHAR:
278 case java.sql.Types.VARCHAR:
279 case java.sql.Types.LONGVARCHAR:
280 outValue = rs.getString(valueIndex);
284 case java.sql.Types.LONGVARBINARY:
285 outValue = rs.getString(valueIndex);
289 case java.sql.Types.TIMESTAMP:
291 // it's important to use Timestamp here as getting it
292 // as a string is undefined and is only there for debugging
293 // according to the API. we can make it a string through formatting.
295 Timestamp timestamp = (rs.getTimestamp(valueIndex));
298 java.util.Date date = new java.util.Date(timestamp.getTime());
300 Calendar calendar = new GregorianCalendar();
301 calendar.setTime(date);
302 calendar.setTimeZone(timezone);
303 outValue = internalDateFormat.format(date);
305 int offset = calendar.get(Calendar.ZONE_OFFSET) + calendar.get(Calendar.DST_OFFSET);
306 String tzOffset = StringUtil.zeroPaddingNumber(Math.abs(offset) / _millisPerHour, 2, 2);
309 outValue = outValue + "-";
311 outValue = outValue + "+";
312 outValue = outValue + tzOffset;
318 outValue = "<unsupported value>";
319 logger.warn("Unsupported Datatype: at " + valueIndex + " (" + aType + ")");
321 } catch (SQLException e) {
322 throw new StorageObjectFailure("Could not get Value out of Resultset -- ",
331 * select-Operator um einen Datensatz zu bekommen.
332 * @param id Primaerschluessel des Datensatzes.
333 * @return liefert EntityObject des gefundenen Datensatzes oder null.
335 public Entity selectById(String id) throws StorageObjectExc {
336 if ((id == null) || id.equals("")) {
337 throw new StorageObjectExc("Database.selectById: Missing id");
340 // ask object store for object
341 if (StoreUtil.extendsStorableEntity(entityClass)) {
342 String uniqueId = id;
344 if (entityClass.equals(StorableObjectEntity.class)) {
345 uniqueId += ("@" + mainTable);
348 StoreIdentifier search_sid = new StoreIdentifier(entityClass, uniqueId);
349 logger.debug("CACHE: (dbg) looking for sid " + search_sid.toString());
351 Entity hit = (Entity) o_store.use(search_sid);
358 Statement stmt = null;
359 Connection con = obtainConnection();
360 Entity returnEntity = null;
365 /** todo better prepared statement */
367 "select * from " + mainTable + " where " + primaryKeyField + "=" + id;
368 stmt = con.createStatement();
369 rs = executeSql(stmt, selectSql);
373 returnEntity = makeEntityFromResultSet(rs);
376 logger.warn("No data for id: " + id + " in table " + mainTable);
382 logger.warn("No Data for Id " + id + " in Table " + mainTable);
385 catch (SQLException sqe) {
386 throwSQLException(sqe, "selectById");
389 catch (NumberFormatException e) {
390 logger.error("ID is no number: " + id);
393 freeConnection(con, stmt);
400 * This method makes it possible to make selects across multiple tables
402 * @param mainTablePrefix prefix for the mainTable
403 * @param extraTables a vector of tables for relational select
404 * @param aWhereClause whereClause
405 * @return EntityList of selected Objects
406 * @throws StorageObjectFailure
409 public EntityList selectByWhereClauseWithExtraTables(String mainTablePrefix,
410 List extraTables, String aWhereClause )
411 throws StorageObjectFailure {
412 return selectByWhereClause( mainTablePrefix, extraTables, aWhereClause, "", 0, defaultLimit);
415 public EntityList selectByFieldValue(String aField, String aValue) throws StorageObjectFailure {
416 return selectByFieldValue(aField, aValue, 0);
419 public EntityList selectByFieldValue(String aField, String aValue, int offset) throws StorageObjectFailure {
420 return selectByWhereClause(aField + "='" + JDBCStringRoutines.escapeStringLiteral(aValue)+"'", offset);
424 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
425 * Also offset wird der erste Datensatz genommen.
427 * @param where where-Clause
428 * @return EntityList mit den gematchten Entities
429 * @exception StorageObjectFailure
431 public EntityList selectByWhereClause(String where) throws StorageObjectFailure {
432 return selectByWhereClause(where, 0);
436 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
437 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
439 * @param whereClause where-Clause
440 * @param offset ab welchem Datensatz.
441 * @return EntityList mit den gematchten Entities
442 * @exception StorageObjectFailure
444 public EntityList selectByWhereClause(String whereClause, int offset) throws StorageObjectFailure {
445 return selectByWhereClause(whereClause, null, offset);
449 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
450 * Also offset wird der erste Datensatz genommen.
451 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
453 * @param where where-Clause
454 * @param order orderBy-Clause
455 * @return EntityList mit den gematchten Entities
456 * @exception StorageObjectFailure
458 public EntityList selectByWhereClause(String where, String order) throws StorageObjectFailure {
459 return selectByWhereClause(where, order, 0);
462 public EntityList selectByWhereClause(String mainTablePrefix, List extraTables, String where, String order) throws StorageObjectFailure {
463 return selectByWhereClause(mainTablePrefix, extraTables, where, order, 0, defaultLimit);
467 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
468 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
470 * @param whereClause where-Clause
471 * @param orderBy orderBy-Clause
472 * @param offset ab welchem Datensatz
473 * @return EntityList mit den gematchten Entities
474 * @exception StorageObjectFailure
476 public EntityList selectByWhereClause(String whereClause, String orderBy, int offset) throws StorageObjectFailure {
477 return selectByWhereClause(whereClause, orderBy, offset, defaultLimit);
481 * select-Operator returns EntityList with matching rows in Database.
482 * @param aWhereClause where-Clause
483 * @param anOrderByClause orderBy-Clause
484 * @param offset ab welchem Datensatz
485 * @param limit wieviele Datens?tze
486 * @return EntityList mit den gematchten Entities
487 * @exception StorageObjectFailure
489 public EntityList selectByWhereClause(String aWhereClause, String anOrderByClause,
490 int offset, int limit) throws StorageObjectFailure {
491 return selectByWhereClause("", null, aWhereClause, anOrderByClause, offset, limit);
496 * select-Operator returns EntityList with matching rows in Database.
497 * @param aWhereClause where-Clause
498 * @param anOrderByClause orderBy-Clause
499 * @param anOffset ab welchem Datensatz
500 * @param aLimit wieviele Datens?tze
501 * @return EntityList mit den gematchten Entities
502 * @exception StorageObjectFailure
504 public EntityList selectByWhereClause(
505 String aMainTablePrefix, List anExtraTables,
506 String aWhereClause, String anOrderByClause,
507 int anOffset, int aLimit) throws StorageObjectFailure {
509 // TODO get rid of emtpy Strings in anExtraTables
510 // make anExtraTables null, if single empty String in it
511 // cause StringUtil.splitString puts in emptyString
513 if (anExtraTables!=null && ((String) anExtraTables.get(0)).trim().equals("")){
517 String useTable = mainTable;
518 String selection = "*";
520 if (aMainTablePrefix != null && aMainTablePrefix.trim().length() > 0) {
521 useTable += " " + aMainTablePrefix;
522 selection = aMainTablePrefix.trim() + ".*";
525 // check o_store for entitylist
526 // only if no relational select
527 if (anExtraTables==null) {
528 if (StoreUtil.extendsStorableEntity(entityClass)) {
529 StoreIdentifier searchSid = new StoreIdentifier(entityClass,
530 StoreContainerType.STOC_TYPE_ENTITYLIST,
531 StoreUtil.getEntityListUniqueIdentifierFor(mainTable,
532 aWhereClause, anOrderByClause, anOffset, aLimit));
533 EntityList hit = (EntityList) o_store.use(searchSid);
542 EntityList theReturnList = null;
543 Connection connection = null;
544 Statement statement = null;
547 // build sql-statement
549 if ((aWhereClause != null) && (aWhereClause.trim().length() == 0)) {
553 StringBuffer selectSql =
554 new StringBuffer("select "+selection+" from ").append(useTable);
556 // append extratables, if necessary
557 if (anExtraTables!=null) {
558 for (int i=0;i < anExtraTables.size();i++) {
559 if (!anExtraTables.get(i).equals("")) {
560 selectSql.append( ", " + anExtraTables.get(i));
565 if (aWhereClause != null) {
566 selectSql.append(" where ").append(aWhereClause);
569 if ((anOrderByClause != null) && !(anOrderByClause.trim().length() == 0)) {
570 selectSql.append(" order by ").append(anOrderByClause);
573 if ((aLimit > -1) && (anOffset > -1)) {
574 selectSql.append(" LIMIT ").append(aLimit+1).append(" OFFSET ").append(anOffset);
579 connection = obtainConnection();
580 statement = connection.createStatement();
581 boolean hasMore = false;
584 resultSet = executeSql(statement, selectSql.toString());
586 if (resultSet != null) {
587 theReturnList = new EntityList();
588 Entity theResultEntity;
590 while (((aLimit == -1) || (position<aLimit)) && resultSet.next()) {
591 theResultEntity = makeEntityFromResultSet(resultSet);
592 theReturnList.add(theResultEntity);
595 hasMore = resultSet.next();
599 if (theReturnList != null) {
600 // now we decide if we have to know an overall count...
601 theReturnList.setOffset(anOffset);
602 theReturnList.setWhere(aWhereClause);
603 theReturnList.setOrder(anOrderByClause);
604 theReturnList.setStorage(this);
605 theReturnList.setLimit(aLimit);
608 theReturnList.setNextBatch(anOffset + aLimit);
611 if (anExtraTables==null && StoreUtil.extendsStorableEntity(entityClass)) {
612 StoreIdentifier sid = theReturnList.getStoreIdentifier();
613 logger.debug("CACHE (add): " + sid.toString());
618 catch (SQLException sqe) {
619 throwSQLException(sqe, "selectByWhereClause");
623 if (connection != null) {
624 freeConnection(connection, statement);
626 } catch (Throwable t) {
630 return theReturnList;
633 private Entity makeEntityFromResultSet(ResultSet rs)
634 throws StorageObjectFailure {
635 Map theResultHash = new HashMap();
636 String theResult = null;
638 Entity returnEntity = null;
641 if (StoreUtil.extendsStorableEntity(entityClass)) {
642 StoreIdentifier searchSid = StorableObjectEntity.getStoreIdentifier(this,
644 Entity hit = (Entity) o_store.use(searchSid);
645 if (hit != null) return hit;
648 for (int i = 0; i < getFieldNames().size(); i++) {
649 // alle durchlaufen bis nix mehr da
650 type = fieldTypes[i];
652 if (type == java.sql.Types.LONGVARBINARY) {
653 InputStreamReader is =
654 (InputStreamReader) rs.getCharacterStream(i + 1);
657 char[] data = new char[32768];
658 StringBuffer theResultString = new StringBuffer();
661 while ((len = is.read(data)) > 0) {
662 theResultString.append(data, 0, len);
666 theResult = theResultString.toString();
673 theResult = getValueAsString(rs, (i + 1), type);
676 if (theResult != null) {
677 theResultHash.put(getFieldNames().get(i), theResult);
681 if (entityClass != null) {
682 returnEntity = createNewEntity();
683 returnEntity.setFieldValues(theResultHash);
685 if (returnEntity instanceof StorableObject) {
686 logger.debug("CACHE: ( in) " + returnEntity.getId() + " :" + mainTable);
687 o_store.add(((StorableObject) returnEntity).getStoreIdentifier());
690 throwStorageObjectException("Internal Error: entityClass not set!");
693 catch (IOException e) {
694 throwStorageObjectException("IOException! -- " + e.getMessage());
696 catch (SQLException sqe) {
697 throwSQLException(sqe, "makeEntityFromResultSet");
706 * Inserts an entity into the database.
709 * @return der Wert des Primary-keys der eingef?gten Entity
711 public String insert(Entity anEntity) throws StorageObjectFailure {
714 String returnId = null;
715 Connection con = null;
716 PreparedStatement pstmt = null;
719 StringBuffer f = new StringBuffer();
720 StringBuffer v = new StringBuffer();
723 boolean firstField = true;
726 for (int i = 0; i < getFieldNames().size(); i++) {
727 aField = (String) getFieldNames().get(i);
729 if (!aField.equals(primaryKeyField)) {
733 if (!anEntity.hasFieldValue(aField) && (
734 aField.equals("webdb_create") ||
735 aField.equals("webdb_lastchange"))) {
739 if (anEntity.hasFieldValue(aField)) {
742 JDBCStringRoutines.escapeStringLiteral(anEntity.getFieldValue(aField)) + "'";
746 // wenn Wert gegeben, dann einbauen
747 if (aValue != null) {
748 if (firstField == false) {
764 StringBuffer sqlBuf =
765 new StringBuffer("insert into ").append(mainTable).append("(").append(f)
766 .append(") values (").append(v).append(")");
767 String sql = sqlBuf.toString();
770 con = obtainConnection();
771 con.setAutoCommit(false);
772 pstmt = con.prepareStatement(sql);
774 int ret = pstmt.executeUpdate();
781 // pstmt = con.prepareStatement("select currval('" + + "_id_seq')");
783 returnId = getLatestInsertedId(con);
784 anEntity.setId(returnId);
786 catch (SQLException sqe) {
787 throwSQLException(sqe, "insert");
791 con.setAutoCommit(true);
793 catch (Exception e) {
796 freeConnection(con, pstmt);
799 /** todo store entity in o_store */
804 * Updates an entity in the database
808 public void update(Entity theEntity) throws StorageObjectFailure {
809 Connection con = null;
810 PreparedStatement pstmt = null;
812 /** todo this is stupid: why do we prepare statement, when we
813 * throw it away afterwards. should be regular statement
814 * update/insert could better be one routine called save()
815 * that chooses to either insert or update depending if we
816 * have a primary key in the entity. i don't know if we
817 * still need the streamed input fields. // rk */
819 /** todo extension: check if Entity did change, otherwise we don't need
820 * the roundtrip to the database */
821 /** invalidating corresponding entitylists in o_store*/
825 String id = theEntity.getId();
827 StringBuffer fv = new StringBuffer();
828 boolean firstField = true;
830 // build sql statement
831 for (int i = 0; i < getFieldNames().size(); i++) {
832 aField = (String) getFieldNames().get(i);
835 // todo if entity.hasFieldValue returns false, then the value should be stored as null
836 if (!(aField.equals(primaryKeyField) ||
837 aField.equals("webdb_create") ||
838 aField.equals("webdb_lastchange"))) {
839 if (theEntity.hasFieldValue(aField)) {
840 if (firstField == false) {
847 fv.append(aField).append("='").append(JDBCStringRoutines.escapeStringLiteral(theEntity.getFieldValue(aField))).append("'");
849 // fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getFieldValue(aField))).append("'");
855 new StringBuffer("update ").append(mainTable).append(" set ").append(fv);
858 if (getFieldNames().contains("webdb_lastchange")) {
859 sql.append(",webdb_lastchange=NOW()");
862 // special case: the webdb_create requires the field in yyyy-mm-dd HH:mm
863 // format so anything extra will be ignored. -mh
864 if (getFieldNames().contains("webdb_create") &&
865 theEntity.hasFieldValue("webdb_create")) {
866 // minimum of 10 (yyyy-mm-dd)...
867 if (theEntity.getFieldValue("webdb_create").length() >= 10) {
868 String dateString = theEntity.getFieldValue("webdb_create");
870 // if only 10, then add 00:00 so it doesn't throw a ParseException
871 if (dateString.length() == 10) {
872 dateString = dateString + " 00:00";
877 java.util.Date d = userInputDateFormat.parse(dateString);
878 // Timestamp tStamp = new Timestamp(d.getTime());
879 sql.append(",webdb_create='" + JDBCStringRoutines.formatDate(d) + "'");
881 catch (ParseException e) {
882 throw new StorageObjectFailure(e);
887 sql.append(" where id=").append(id);
888 logQueryBefore(sql.toString());
891 con = obtainConnection();
892 con.setAutoCommit(false);
893 pstmt = con.prepareStatement(sql.toString());
895 pstmt.executeUpdate();
897 catch (SQLException sqe) {
898 throwSQLException(sqe, "update");
902 con.setAutoCommit(true);
904 catch (Exception e) {
909 freeConnection(con, pstmt);
915 * @param id des zu loeschenden Datensatzes
916 * @return boolean liefert true zurueck, wenn loeschen erfolgreich war.
918 public boolean delete(String id) throws StorageObjectFailure {
919 // ostore send notification
920 if (StoreUtil.extendsStorableEntity(entityClass)) {
921 String uniqueId = id;
923 if (entityClass.equals(StorableObjectEntity.class)) {
924 uniqueId += ("@" + mainTable);
927 logger.debug("CACHE: (del) " + id);
929 StoreIdentifier search_sid =
930 new StoreIdentifier(entityClass,
931 StoreContainerType.STOC_TYPE_ENTITY, uniqueId);
932 o_store.invalidate(search_sid);
935 /** todo could be prepared Statement */
936 Statement stmt = null;
937 Connection con = null;
940 "delete from " + mainTable + " where " + primaryKeyField + "='" + id + "'";
944 con = obtainConnection();
945 stmt = con.createStatement();
946 res = stmt.executeUpdate(sql);
948 catch (SQLException sqe) {
949 throwSQLException(sqe, "delete");
952 freeConnection(con, stmt);
957 return (res > 0) ? true : false;
961 * Deletes entities based on a where clause
963 public int deleteByWhereClause(String aWhereClause) throws StorageObjectFailure {
966 Statement stmt = null;
967 Connection con = null;
970 "delete from " + mainTable + " where " + aWhereClause;
972 //theLog.printInfo("DELETE " + sql);
974 con = obtainConnection();
975 stmt = con.createStatement();
976 res = stmt.executeUpdate(sql);
978 catch (SQLException sqe) {
979 throwSQLException(sqe, "delete");
982 freeConnection(con, stmt);
988 /* noch nicht implementiert.
989 * @return immer false
991 public boolean delete(EntityList theEntityList) {
996 * Diese Methode fuehrt den Sqlstring <i>sql</i> aus und timed im Logfile.
997 * @param stmt Statemnt
998 * @param sql Sql-String
1000 public ResultSet executeSql(Statement stmt, String sql)
1001 throws StorageObjectFailure, SQLException {
1003 logQueryBefore(sql);
1004 long startTime = System.currentTimeMillis();
1006 rs = stmt.executeQuery(sql);
1008 logQueryAfter(sql, (System.currentTimeMillis() - startTime));
1010 catch (SQLException e) {
1011 logQueryError(sql, (System.currentTimeMillis() - startTime), e);
1018 private Map processRow(ResultSet aResultSet) throws StorageObjectFailure {
1020 Map result = new HashMap();
1021 ResultSetMetaData metaData = aResultSet.getMetaData();
1022 int nrColumns = metaData.getColumnCount();
1023 for (int i=0; i<nrColumns; i++) {
1024 result.put(metaData.getColumnName(i+1), getValueAsString(aResultSet, i+1, metaData.getColumnType(i+1)));
1029 catch (Throwable e) {
1030 throw new StorageObjectFailure(e);
1035 * Executes 1 sql statement and returns the results as a <code>List</code> of
1038 public List executeFreeSql(String sql, int aLimit) throws StorageObjectFailure, StorageObjectExc {
1039 Connection connection = null;
1040 Statement statement = null;
1042 List result = new ArrayList();
1043 connection = obtainConnection();
1044 statement = connection.createStatement();
1045 ResultSet resultset = executeSql(statement, sql);
1047 while (resultset.next() && result.size() < aLimit) {
1048 result.add(processRow(resultset));
1057 catch (Throwable e) {
1058 throw new StorageObjectFailure(e);
1061 if (connection!=null) {
1062 freeConnection(connection, statement);
1068 * Executes 1 sql statement and returns the first result row as a <code>Map</code>s
1069 * (<code>null</code> if there wasn't any row)
1071 public Map executeFreeSingleRowSql(String anSqlStatement) throws StorageObjectFailure, StorageObjectExc {
1073 List resultList = executeFreeSql(anSqlStatement, 1);
1075 if (resultList.size()>0)
1076 return (Map) resultList.get(0);
1082 catch (Throwable t) {
1083 throw new StorageObjectFailure(t);
1088 * Executes 1 sql statement and returns the first column of the first result row as a <code>String</code>s
1089 * (<code>null</code> if there wasn't any row)
1091 public String executeFreeSingleValueSql(String sql) throws StorageObjectFailure, StorageObjectExc {
1092 Map row = executeFreeSingleRowSql(sql);
1097 Iterator i = row.values().iterator();
1099 return (String) i.next();
1103 public int getSize(String where) throws SQLException, StorageObjectFailure {
1104 return getSize("", null, where);
1107 * returns the number of rows in the table
1109 public int getSize(String mainTablePrefix, List extraTables, String where) throws SQLException, StorageObjectFailure {
1111 String useTable = mainTable;
1112 if (mainTablePrefix!=null && mainTablePrefix.trim().length()>0) {
1113 useTable+=" "+mainTablePrefix;
1115 StringBuffer countSql =
1116 new StringBuffer("select count(*) from ").append(useTable);
1117 // append extratables, if necessary
1118 if (extraTables!=null) {
1119 for (int i=0;i < extraTables.size();i++) {
1120 if (!extraTables.get(i).equals("")) {
1121 countSql.append( ", " + extraTables.get(i));
1126 if ((where != null) && (where.length() != 0)) {
1127 countSql.append( " where " + where);
1130 Connection con = null;
1131 Statement stmt = null;
1133 logQueryBefore(countSql.toString());
1134 long startTime = System.currentTimeMillis();
1137 con = obtainConnection();
1138 stmt = con.createStatement();
1140 ResultSet rs = executeSql(stmt, countSql.toString());
1143 result = rs.getInt(1);
1146 catch (SQLException e) {
1147 logger.error("Database.getSize: " + e.getMessage());
1150 freeConnection(con, stmt);
1152 logQueryAfter(countSql.toString(), (System.currentTimeMillis() - startTime));
1157 public int executeUpdate(Statement stmt, String sql)
1158 throws StorageObjectFailure, SQLException {
1161 logQueryBefore(sql);
1162 long startTime = System.currentTimeMillis();
1165 rs = stmt.executeUpdate(sql);
1167 logQueryAfter(sql, (System.currentTimeMillis() - startTime));
1169 catch (SQLException e) {
1170 logQueryError(sql, (System.currentTimeMillis() - startTime), e);
1177 public int executeUpdate(String sql)
1178 throws StorageObjectFailure, SQLException {
1180 Connection con = null;
1181 PreparedStatement pstmt = null;
1183 logQueryBefore(sql);
1184 long startTime = System.currentTimeMillis();
1186 con = obtainConnection();
1187 pstmt = con.prepareStatement(sql);
1188 result = pstmt.executeUpdate();
1189 logQueryAfter(sql, System.currentTimeMillis() - startTime);
1191 catch (Throwable e) {
1192 logQueryError(sql, System.currentTimeMillis() - startTime, e);
1193 throw new StorageObjectFailure("Database.executeUpdate(" + sql + "): " + e.getMessage(), e);
1196 freeConnection(con, pstmt);
1202 * Processes the metadata for the table this Database object is responsible for.
1204 private void processMetaData(ResultSetMetaData aMetaData) throws StorageObjectFailure {
1205 fieldNames = new ArrayList();
1206 fieldNameToType = new HashMap();
1209 int numFields = aMetaData.getColumnCount();
1210 fieldTypes = new int[numFields];
1212 for (int i = 1; i <= numFields; i++) {
1213 fieldNames.add(aMetaData.getColumnName(i));
1214 fieldTypes[i - 1] = aMetaData.getColumnType(i);
1215 fieldNameToType.put(aMetaData.getColumnName(i), new Integer(aMetaData.getColumnType(i)));
1218 catch (SQLException e) {
1219 throwSQLException(e, "processMetaData");
1224 * Retrieves metadata from the table this Database object represents
1226 private void acquireMetaData() throws StorageObjectFailure {
1227 Connection connection = null;
1228 PreparedStatement statement = null;
1229 String sql = "select * from " + mainTable + " where 0=1";
1232 connection = obtainConnection();
1233 statement = connection.prepareStatement(sql);
1235 logger.debug("METADATA: " + sql);
1236 ResultSet resultSet = statement.executeQuery();
1238 processMetaData(resultSet.getMetaData());
1244 catch (SQLException e) {
1245 throwSQLException(e, "acquireMetaData");
1248 freeConnection(connection, statement);
1252 public Connection obtainConnection() throws StorageObjectFailure {
1254 return MirGlobal.getDatabaseEngine().obtainConnection();
1256 catch (Exception e) {
1257 throw new StorageObjectFailure(e);
1261 public void freeConnection(Connection aConnection, Statement aStatement) throws StorageObjectFailure {
1265 catch (Throwable t) {
1266 logger.warn("Can't close statemnet: " + t.toString());
1270 MirGlobal.getDatabaseEngine().releaseConnection(aConnection);
1272 catch (Throwable t) {
1273 logger.warn("Can't release connection: " + t.toString());
1278 * Wertet SQLException aus und wirft dannach eine StorageObjectException
1279 * @param sqe SQLException
1280 * @param aFunction Funktonsname, in der die SQLException geworfen wurde
1282 protected void throwSQLException(SQLException sqe, String aFunction) throws StorageObjectFailure {
1284 String message = "";
1288 state = sqe.getSQLState();
1289 message = sqe.getMessage();
1290 vendor = sqe.getErrorCode();
1293 String information =
1296 ", vendor= " + vendor +
1297 ", message=" + message +
1298 ", function= " + aFunction;
1300 logger.error(information);
1302 throw new StorageObjectFailure(information, sqe);
1305 protected void _throwStorageObjectException(Exception e, String aFunction)
1306 throws StorageObjectFailure {
1309 logger.error(e.getMessage() + aFunction);
1310 throw new StorageObjectFailure(aFunction, e);
1315 * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach
1316 * eine StorageObjectException
1317 * @param aMessage Nachricht mit dem Fehler
1318 * @exception StorageObjectFailure
1320 void throwStorageObjectException(String aMessage) throws StorageObjectFailure {
1321 logger.error(aMessage);
1322 throw new StorageObjectFailure(aMessage, null);
1326 * Invalidates any cached entity list
1328 private void invalidateStore() {
1329 // invalidating all EntityLists corresponding with entityClass
1330 if (StoreUtil.extendsStorableEntity(entityClass)) {
1331 StoreContainerType stoc_type =
1332 StoreContainerType.valueOf(entityClass, StoreContainerType.STOC_TYPE_ENTITYLIST);
1333 o_store.invalidate(stoc_type);
1338 * Retrieves a binary value
1340 public InputStream getBinaryField(String aQuery) throws StorageObjectFailure, SQLException {
1341 Connection connection=null;
1342 Statement statement=null;
1343 InputStream inputStream;
1344 InputStream imageInputStream = null;
1347 connection = obtainConnection();
1349 connection.setAutoCommit(false);
1350 statement = connection.createStatement();
1351 ResultSet resultSet = executeSql(statement, aQuery);
1353 if(resultSet!=null) {
1354 if (resultSet.next()) {
1355 if (resultSet.getMetaData().getColumnType(1) == java.sql.Types.BINARY) {
1356 byte[] data = resultSet.getBytes(1);
1357 imageInputStream = new ByteArrayInputStream(data);
1360 inputStream = resultSet.getBlob(1).getBinaryStream();
1361 imageInputStream = new BinaryFieldInputStream(inputStream, connection, statement);
1370 catch (Throwable t) {
1371 logger.error("EntityImages.getImage failed: " + t.toString());
1372 t.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1375 connection.setAutoCommit(true);
1377 catch (Throwable e) {
1378 logger.error("EntityImages.getImage resetting transaction mode failed: " + e.toString());
1379 e.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1383 freeConnection(connection, statement);
1385 catch (Throwable e) {
1386 logger.error("EntityImages.getImage freeing connection failed: " +e.toString());
1389 throw new StorageObjectFailure(t);
1392 freeConnection(connection, statement);
1395 return imageInputStream;
1399 * Sets a binary value for a particular field in a record specified by its identifier
1401 public void setBinaryField(String aFieldName, String anObjectId, byte aData[]) throws StorageObjectFailure, SQLException {
1402 PreparedStatement statement = null;
1403 Connection connection = obtainConnection();
1406 connection.setAutoCommit(false);
1408 // are we using bytea ?
1409 if (getFieldType(aFieldName) == java.sql.Types.BINARY) {
1410 statement = connection.prepareStatement(
1411 "update " + mainTable + " set " + aFieldName + " = ? where " + getIdFieldName() + "=" + Integer.parseInt(anObjectId));
1412 statement.setBytes(1, aData);
1413 statement.execute();
1414 connection.commit();
1418 PGConnection postgresqlConnection = (org.postgresql.PGConnection) ((DelegatingConnection) connection).getDelegate();
1419 LargeObjectManager lobManager = postgresqlConnection.getLargeObjectAPI();
1420 int oid = lobManager.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
1421 LargeObject obj = lobManager.open(oid, LargeObjectManager.WRITE); // Now open the file File file =
1424 statement = connection.prepareStatement(
1425 "update " + mainTable + " set " + aFieldName + " = ? where " + getIdFieldName() + "=" + Integer.parseInt(anObjectId));
1426 statement.setInt(1, oid);
1427 statement.execute();
1428 connection.commit();
1432 connection.setAutoCommit(true);
1436 freeConnection(connection, statement);
1440 private void logQueryBefore(String aQuery) {
1441 logger.debug("about to perform QUERY " + aQuery);
1442 // (new Throwable()).printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1445 private void logQueryAfter(String aQuery, long aTime) {
1446 logger.info("QUERY " + aQuery + " took " + aTime + "ms.");
1449 private void logQueryError(String aQuery, long aTime, Throwable anException) {
1450 logger.error("QUERY " + aQuery + " took " + aTime + "ms, but threw exception " + anException.toString());
1453 private int getFieldType(String aFieldName) {
1454 if (fieldNameToType == null) {
1458 return ((Integer) fieldNameToType.get(aFieldName)).intValue();
1463 * a small wrapper class that allows us to store the DB connection resources
1464 * that the BlobInputStream is using and free them upon closing of the stream
1466 private class BinaryFieldInputStream extends InputStream {
1467 InputStream inputStream;
1468 Connection connection;
1469 Statement statement;
1471 public BinaryFieldInputStream(InputStream aBlobInputStream, Connection aConnection, Statement aStatement ) {
1472 inputStream = aBlobInputStream;
1473 connection = aConnection;
1474 statement = aStatement;
1477 public void close () throws IOException {
1478 inputStream.close();
1480 connection.setAutoCommit(true);
1481 freeConnection(connection, statement);
1483 catch (Exception e) {
1484 throw new IOException("close(): "+e.toString());
1488 public int read() throws IOException {
1489 return inputStream.read();