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.log.LoggerWrapper;
37 import mir.misc.StringUtil;
38 import mir.storage.store.*;
39 import mir.util.JDBCStringRoutines;
40 import mircoders.global.MirGlobal;
42 import java.io.ByteArrayInputStream;
43 import java.io.IOException;
44 import java.io.InputStream;
45 import java.io.InputStreamReader;
47 import java.text.ParseException;
48 import java.text.SimpleDateFormat;
52 * Implements database access.
54 * @version $Id: Database.java,v 1.44.2.23 2004/11/21 22:07:13 zapata Exp $
58 public class Database implements StorageObject {
59 private static Class GENERIC_ENTITY_CLASS = mir.entity.StorableObjectEntity.class;
60 protected static final ObjectStore o_store = ObjectStore.getInstance();
61 private static final int _millisPerHour = 60 * 60 * 1000;
63 protected LoggerWrapper logger;
65 protected MirPropertiesConfiguration configuration;
66 protected String mainTable;
67 protected String primaryKeySequence = null;
68 protected String primaryKeyField = "id";
70 protected List fieldNames;
71 protected int[] fieldTypes;
73 protected Class theEntityClass;
74 protected boolean hasTimestamp = true;
75 private int defaultLimit;
78 SimpleDateFormat internalDateFormat;
79 SimpleDateFormat userInputDateFormat;
82 * Kontruktor bekommt den Filenamen des Konfigurationsfiles ?bergeben.
83 * Aus diesem file werden <code>Database.Logfile</code>,
84 * <code>Database.Username</code>,<code>Database.Password</code>,
85 * <code>Database.Host</code> und <code>Database.Adaptor</code>
86 * ausgelesen und ein Broker f?r die Verbindugen zur Datenbank
89 public Database() throws StorageObjectFailure {
90 configuration = MirPropertiesConfiguration.instance();
91 logger = new LoggerWrapper("Database");
92 timezone = TimeZone.getTimeZone(configuration.getString("Mir.DefaultTimezone"));
93 internalDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
94 internalDateFormat.setTimeZone(timezone);
96 userInputDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
97 userInputDateFormat.setTimeZone(timezone);
99 String theAdaptorName = configuration.getString("Database.Adaptor");
100 defaultLimit = Integer.parseInt(configuration.getString("Database.Limit"));
103 theEntityClass = GENERIC_ENTITY_CLASS;
105 catch (Throwable e) {
106 logger.error("Error in Database() constructor with " + theAdaptorName + " -- " + e.getMessage());
107 throw new StorageObjectFailure("Error in Database() constructor.", e);
112 * Liefert die Entity-Klasse zur?ck, in der eine Datenbankzeile gewrappt
113 * wird. Wird die Entity-Klasse durch die erbende Klasse nicht ?berschrieben,
114 * wird eine mir.entity.GenericEntity erzeugt.
116 * @return Class-Objekt der Entity
118 public java.lang.Class getEntityClass() {
119 return theEntityClass;
123 * Liefert die Standardbeschr?nkung von select-Statements zur?ck, also
124 * wieviel Datens?tze per Default selektiert werden.
126 * @return Standard-Anzahl der Datens?tze
128 public int getLimit() {
133 * Liefert den Namen des Primary-Keys zur?ck. Wird die Variable nicht von
134 * der erbenden Klasse ?berschrieben, so ist der Wert <code>PKEY</code>
135 * @return Name des Primary-Keys
137 public String getIdName() {
138 return primaryKeyField;
142 * Liefert den Namen der Tabelle, auf das sich das Datenbankobjekt bezieht.
144 * @return Name der Tabelle
146 public String getTableName() {
151 * Returns the id that was most recently added to the database
153 private String getLatestInsertedId(Connection aConnection) throws SQLException {
154 if (primaryKeySequence==null)
155 primaryKeySequence = mainTable+"_id_seq";
157 PreparedStatement statement = aConnection.prepareStatement("select currval('" + primaryKeySequence + "')");
159 ResultSet rs = statement.executeQuery();
161 return rs.getString(1);
167 public List getFieldNames() throws StorageObjectFailure {
168 if (fieldNames == null) {
176 * Gets value out of ResultSet according to type and converts to String
177 * @param rs ResultSet.
178 * @param aType a type from java.sql.Types.*
179 * @param valueIndex index in ResultSet
180 * @return returns the value as String. If no conversion is possible
181 * /unsupported value/ is returned
183 private String getValueAsString(ResultSet rs, int valueIndex, int aType)
184 throws StorageObjectFailure {
185 String outValue = null;
190 case java.sql.Types.BIT:
191 outValue = (rs.getBoolean(valueIndex) == true) ? "1" : "0";
195 case java.sql.Types.INTEGER:
196 case java.sql.Types.SMALLINT:
197 case java.sql.Types.TINYINT:
198 case java.sql.Types.BIGINT:
200 int out = rs.getInt(valueIndex);
203 outValue = new Integer(out).toString();
208 case java.sql.Types.NUMERIC:
210 /** todo Numeric can be float or double depending upon
211 * metadata.getScale() / especially with oracle */
212 long outl = rs.getLong(valueIndex);
215 outValue = new Long(outl).toString();
220 case java.sql.Types.REAL:
222 float tempf = rs.getFloat(valueIndex);
228 int tempf_int = (int) tempf;
229 tempf = (float) tempf_int;
231 outValue = "" + tempf;
232 outValue = outValue.replace('.', ',');
237 case java.sql.Types.DOUBLE:
239 double tempd = rs.getDouble(valueIndex);
245 int tempd_int = (int) tempd;
246 tempd = (double) tempd_int;
248 outValue = "" + tempd;
249 outValue = outValue.replace('.', ',');
254 case java.sql.Types.CHAR:
255 case java.sql.Types.VARCHAR:
256 case java.sql.Types.LONGVARCHAR:
257 outValue = rs.getString(valueIndex);
261 case java.sql.Types.LONGVARBINARY:
262 outValue = rs.getString(valueIndex);
266 case java.sql.Types.TIMESTAMP:
268 // it's important to use Timestamp here as getting it
269 // as a string is undefined and is only there for debugging
270 // according to the API. we can make it a string through formatting.
272 Timestamp timestamp = (rs.getTimestamp(valueIndex));
275 java.util.Date date = new java.util.Date(timestamp.getTime());
277 Calendar calendar = new GregorianCalendar();
278 calendar.setTime(date);
279 calendar.setTimeZone(timezone);
280 outValue = internalDateFormat.format(date);
282 int offset = calendar.get(Calendar.ZONE_OFFSET) + calendar.get(Calendar.DST_OFFSET);
283 String tzOffset = StringUtil.zeroPaddingNumber(Math.abs(offset) / _millisPerHour, 2, 2);
286 outValue = outValue + "-";
288 outValue = outValue + "+";
289 outValue = outValue + tzOffset;
295 outValue = "<unsupported value>";
296 logger.warn("Unsupported Datatype: at " + valueIndex + " (" + aType + ")");
298 } catch (SQLException e) {
299 throw new StorageObjectFailure("Could not get Value out of Resultset -- ",
308 * select-Operator um einen Datensatz zu bekommen.
309 * @param id Primaerschluessel des Datensatzes.
310 * @return liefert EntityObject des gefundenen Datensatzes oder null.
312 public Entity selectById(String id) throws StorageObjectExc {
313 if ((id == null) || id.equals("")) {
314 throw new StorageObjectExc("Database.selectById: Missing id");
317 // ask object store for object
318 if (StoreUtil.extendsStorableEntity(theEntityClass)) {
319 String uniqueId = id;
321 if (theEntityClass.equals(StorableObjectEntity.class)) {
322 uniqueId += ("@" + mainTable);
325 StoreIdentifier search_sid = new StoreIdentifier(theEntityClass, uniqueId);
326 logger.debug("CACHE: (dbg) looking for sid " + search_sid.toString());
328 Entity hit = (Entity) o_store.use(search_sid);
335 Statement stmt = null;
336 Connection con = obtainConnection();
337 Entity returnEntity = null;
342 /** todo better prepared statement */
344 "select * from " + mainTable + " where " + primaryKeyField + "=" + id;
345 stmt = con.createStatement();
346 rs = executeSql(stmt, selectSql);
350 returnEntity = makeEntityFromResultSet(rs);
353 logger.warn("No data for id: " + id + " in table " + mainTable);
359 logger.warn("No Data for Id " + id + " in Table " + mainTable);
362 catch (SQLException sqe) {
363 throwSQLException(sqe, "selectById");
366 catch (NumberFormatException e) {
367 logger.error("ID is no number: " + id);
370 freeConnection(con, stmt);
377 * This method makes it possible to make selects across multiple tables
379 * @param mainTablePrefix prefix for the mainTable
380 * @param extraTables a vector of tables for relational select
381 * @param aWhereClause whereClause
382 * @return EntityList of selected Objects
383 * @throws StorageObjectFailure
386 public EntityList selectByWhereClauseWithExtraTables(String mainTablePrefix,
387 List extraTables, String aWhereClause )
388 throws StorageObjectFailure {
389 return selectByWhereClause( mainTablePrefix, extraTables, aWhereClause, "", 0, defaultLimit);
392 public EntityList selectByFieldValue(String aField, String aValue) throws StorageObjectFailure {
393 return selectByFieldValue(aField, aValue, 0);
396 public EntityList selectByFieldValue(String aField, String aValue, int offset) throws StorageObjectFailure {
397 return selectByWhereClause(aField + "='" + JDBCStringRoutines.escapeStringLiteral(aValue)+"'", offset);
401 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
402 * Also offset wird der erste Datensatz genommen.
404 * @param where where-Clause
405 * @return EntityList mit den gematchten Entities
406 * @exception StorageObjectFailure
408 public EntityList selectByWhereClause(String where) throws StorageObjectFailure {
409 return selectByWhereClause(where, 0);
413 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
414 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
416 * @param whereClause where-Clause
417 * @param offset ab welchem Datensatz.
418 * @return EntityList mit den gematchten Entities
419 * @exception StorageObjectFailure
421 public EntityList selectByWhereClause(String whereClause, int offset) throws StorageObjectFailure {
422 return selectByWhereClause(whereClause, null, offset);
426 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
427 * Also offset wird der erste Datensatz genommen.
428 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
430 * @param where where-Clause
431 * @param order orderBy-Clause
432 * @return EntityList mit den gematchten Entities
433 * @exception StorageObjectFailure
435 public EntityList selectByWhereClause(String where, String order) throws StorageObjectFailure {
436 return selectByWhereClause(where, order, 0);
439 public EntityList selectByWhereClause(String mainTablePrefix, List extraTables, String where, String order) throws StorageObjectFailure {
440 return selectByWhereClause(mainTablePrefix, extraTables, where, order, 0, defaultLimit);
444 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
445 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
447 * @param whereClause where-Clause
448 * @param orderBy orderBy-Clause
449 * @param offset ab welchem Datensatz
450 * @return EntityList mit den gematchten Entities
451 * @exception StorageObjectFailure
453 public EntityList selectByWhereClause(String whereClause, String orderBy, int offset) throws StorageObjectFailure {
454 return selectByWhereClause(whereClause, orderBy, offset, defaultLimit);
458 * select-Operator returns EntityList with matching rows in Database.
459 * @param aWhereClause where-Clause
460 * @param anOrderByClause orderBy-Clause
461 * @param offset ab welchem Datensatz
462 * @param limit wieviele Datens?tze
463 * @return EntityList mit den gematchten Entities
464 * @exception StorageObjectFailure
466 public EntityList selectByWhereClause(String aWhereClause, String anOrderByClause,
467 int offset, int limit) throws StorageObjectFailure {
468 return selectByWhereClause("", null, aWhereClause, anOrderByClause, offset, limit);
473 * select-Operator returns EntityList with matching rows in Database.
474 * @param aWhereClause where-Clause
475 * @param anOrderByClause orderBy-Clause
476 * @param anOffset ab welchem Datensatz
477 * @param aLimit wieviele Datens?tze
478 * @return EntityList mit den gematchten Entities
479 * @exception StorageObjectFailure
481 public EntityList selectByWhereClause(
482 String aMainTablePrefix, List anExtraTables,
483 String aWhereClause, String anOrderByClause,
484 int anOffset, int aLimit) throws StorageObjectFailure {
486 // TODO get rid of emtpy Strings in anExtraTables
487 // make anExtraTables null, if single empty String in it
488 // cause StringUtil.splitString puts in emptyString
490 if (anExtraTables!=null && ((String) anExtraTables.get(0)).trim().equals("")){
491 logger.debug("+++ made anExtraTables to null!");
495 String useTable = mainTable;
496 String selection = "*";
498 if (aMainTablePrefix != null && aMainTablePrefix.trim().length() > 0) {
499 useTable += " " + aMainTablePrefix;
500 selection = aMainTablePrefix.trim() + ".*";
503 // check o_store for entitylist
504 // only if no relational select
505 if (anExtraTables==null) {
506 if (StoreUtil.extendsStorableEntity(theEntityClass)) {
507 StoreIdentifier searchSid = new StoreIdentifier(theEntityClass,
508 StoreContainerType.STOC_TYPE_ENTITYLIST,
509 StoreUtil.getEntityListUniqueIdentifierFor(mainTable,
510 aWhereClause, anOrderByClause, anOffset, aLimit));
511 EntityList hit = (EntityList) o_store.use(searchSid);
520 EntityList theReturnList = null;
521 Connection connection = null;
522 Statement statement = null;
528 // build sql-statement
530 if ((aWhereClause != null) && (aWhereClause.trim().length() == 0)) {
534 StringBuffer countSql =
535 new StringBuffer("select count(*) from ").append(useTable);
536 StringBuffer selectSql =
537 new StringBuffer("select "+selection+" from ").append(useTable);
539 // append extratables, if necessary
540 if (anExtraTables!=null) {
541 for (int i=0;i < anExtraTables.size();i++) {
542 if (!anExtraTables.get(i).equals("")) {
543 countSql.append( ", " + anExtraTables.get(i));
544 selectSql.append( ", " + anExtraTables.get(i));
549 if (aWhereClause != null) {
550 selectSql.append(" where ").append(aWhereClause);
551 countSql.append(" where ").append(aWhereClause);
554 if ((anOrderByClause != null) && !(anOrderByClause.trim().length() == 0)) {
555 selectSql.append(" order by ").append(anOrderByClause);
558 if ((aLimit > -1) && (anOffset > -1)) {
559 selectSql.append(" LIMIT ").append(aLimit).append(" OFFSET ").append(anOffset);
564 connection = obtainConnection();
565 statement = connection.createStatement();
568 resultSet = executeSql(statement, selectSql.toString());
570 if (resultSet != null) {
571 theReturnList = new EntityList();
572 Entity theResultEntity;
573 while (resultSet.next()) {
574 theResultEntity = makeEntityFromResultSet(resultSet);
575 theReturnList.add(theResultEntity);
581 // making entitylist infos
584 if (theReturnList != null) {
585 // now we decide if we have to know an overall count...
588 if ((aLimit > -1) && (anOffset > -1)) {
589 if (offsetCount == aLimit) {
590 resultSet = executeSql(statement, countSql.toString());
592 if (resultSet != null) {
593 if (resultSet.next()) {
594 count = resultSet.getInt(1);
600 logger.error("Could not count: " + countSql);
605 theReturnList.setCount(count);
606 theReturnList.setOffset(anOffset);
607 theReturnList.setWhere(aWhereClause);
608 theReturnList.setOrder(anOrderByClause);
609 theReturnList.setStorage(this);
610 theReturnList.setLimit(aLimit);
612 if (anOffset >= aLimit) {
613 theReturnList.setPrevBatch(anOffset - aLimit);
616 if ((anOffset + offsetCount) < count) {
617 theReturnList.setNextBatch(anOffset + aLimit);
620 if (anExtraTables==null && StoreUtil.extendsStorableEntity(theEntityClass)) {
621 StoreIdentifier sid = theReturnList.getStoreIdentifier();
622 logger.debug("CACHE (add): " + sid.toString());
627 catch (SQLException sqe) {
628 throwSQLException(sqe, "selectByWhereClause");
632 if (connection != null) {
633 freeConnection(connection, statement);
635 } catch (Throwable t) {
639 return theReturnList;
643 * Bastelt aus einer Zeile der Datenbank ein EntityObjekt.
645 * @param rs Das ResultSetObjekt.
646 * @return Entity Die Entity.
648 private Entity makeEntityFromResultSet(ResultSet rs)
649 throws StorageObjectFailure {
650 Map theResultHash = new HashMap();
651 String theResult = null;
653 Entity returnEntity = null;
656 if (StoreUtil.extendsStorableEntity(theEntityClass)) {
657 StoreIdentifier searchSid = StorableObjectEntity.getStoreIdentifier(this,
659 Entity hit = (Entity) o_store.use(searchSid);
660 if (hit != null) return hit;
663 for (int i = 0; i < getFieldNames().size(); i++) {
664 // alle durchlaufen bis nix mehr da
665 type = fieldTypes[i];
667 if (type == java.sql.Types.LONGVARBINARY) {
668 InputStreamReader is =
669 (InputStreamReader) rs.getCharacterStream(i + 1);
672 char[] data = new char[32768];
673 StringBuffer theResultString = new StringBuffer();
676 while ((len = is.read(data)) > 0) {
677 theResultString.append(data, 0, len);
681 theResult = theResultString.toString();
688 theResult = getValueAsString(rs, (i + 1), type);
691 if (theResult != null) {
692 theResultHash.put(getFieldNames().get(i), theResult);
696 if (theEntityClass != null) {
697 returnEntity = (Entity) theEntityClass.newInstance();
698 returnEntity.setStorage(this);
699 returnEntity.setFieldValues(theResultHash);
701 if (returnEntity instanceof StorableObject) {
702 logger.debug("CACHE: ( in) " + returnEntity.getId() + " :" + mainTable);
703 o_store.add(((StorableObject) returnEntity).getStoreIdentifier());
706 throwStorageObjectException("Internal Error: theEntityClass not set!");
709 catch (IllegalAccessException e) {
710 throwStorageObjectException("No access! -- " + e.getMessage());
712 catch (IOException e) {
713 throwStorageObjectException("IOException! -- " + e.getMessage());
715 catch (InstantiationException e) {
716 throwStorageObjectException("No Instatiation! -- " + e.getMessage());
718 catch (SQLException sqe) {
719 throwSQLException(sqe, "makeEntityFromResultSet");
728 * Inserts an entity into the database.
731 * @return der Wert des Primary-keys der eingef?gten Entity
733 public String insert(Entity theEntity) throws StorageObjectFailure {
736 String returnId = null;
737 Connection con = null;
738 PreparedStatement pstmt = null;
741 StringBuffer f = new StringBuffer();
742 StringBuffer v = new StringBuffer();
745 boolean firstField = true;
748 for (int i = 0; i < getFieldNames().size(); i++) {
749 aField = (String) getFieldNames().get(i);
751 if (!aField.equals(primaryKeyField)) {
755 if (!theEntity.hasFieldValue(aField) && (
756 aField.equals("webdb_create") ||
757 aField.equals("webdb_lastchange"))) {
761 if (theEntity.hasFieldValue(aField)) {
764 JDBCStringRoutines.escapeStringLiteral(theEntity.getFieldValue(aField)) + "'";
768 // wenn Wert gegeben, dann einbauen
769 if (aValue != null) {
770 if (firstField == false) {
786 StringBuffer sqlBuf =
787 new StringBuffer("insert into ").append(mainTable).append("(").append(f)
788 .append(") values (").append(v).append(")");
789 String sql = sqlBuf.toString();
791 logger.info("INSERT: " + sql);
792 con = obtainConnection();
793 con.setAutoCommit(false);
794 pstmt = con.prepareStatement(sql);
796 int ret = pstmt.executeUpdate();
803 // pstmt = con.prepareStatement("select currval('" + + "_id_seq')");
805 returnId = getLatestInsertedId(con);
806 theEntity.setId(returnId);
808 catch (SQLException sqe) {
809 throwSQLException(sqe, "insert");
813 con.setAutoCommit(true);
815 catch (Exception e) {
818 freeConnection(con, pstmt);
821 /** todo store entity in o_store */
826 * Updates an entity in the database
830 public void update(Entity theEntity) throws StorageObjectFailure {
831 Connection con = null;
832 PreparedStatement pstmt = null;
834 /** todo this is stupid: why do we prepare statement, when we
835 * throw it away afterwards. should be regular statement
836 * update/insert could better be one routine called save()
837 * that chooses to either insert or update depending if we
838 * have a primary key in the entity. i don't know if we
839 * still need the streamed input fields. // rk */
841 /** todo extension: check if Entity did change, otherwise we don't need
842 * the roundtrip to the database */
843 /** invalidating corresponding entitylists in o_store*/
847 String id = theEntity.getId();
849 StringBuffer fv = new StringBuffer();
850 boolean firstField = true;
852 // build sql statement
853 for (int i = 0; i < getFieldNames().size(); i++) {
854 aField = (String) getFieldNames().get(i);
857 // todo if entity.hasFieldValue returns false, then the value should be stored as null
858 if (!(aField.equals(primaryKeyField) ||
859 aField.equals("webdb_create") ||
860 aField.equals("webdb_lastchange"))) {
861 if (theEntity.hasFieldValue(aField)) {
862 if (firstField == false) {
869 fv.append(aField).append("='").append(JDBCStringRoutines.escapeStringLiteral(theEntity.getFieldValue(aField))).append("'");
871 // fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getFieldValue(aField))).append("'");
877 new StringBuffer("update ").append(mainTable).append(" set ").append(fv);
880 if (getFieldNames().contains("webdb_lastchange")) {
881 sql.append(",webdb_lastchange=NOW()");
884 // special case: the webdb_create requires the field in yyyy-mm-dd HH:mm
885 // format so anything extra will be ignored. -mh
886 if (getFieldNames().contains("webdb_create") &&
887 theEntity.hasFieldValue("webdb_create")) {
888 // minimum of 10 (yyyy-mm-dd)...
889 if (theEntity.getFieldValue("webdb_create").length() >= 10) {
890 String dateString = theEntity.getFieldValue("webdb_create");
892 // if only 10, then add 00:00 so it doesn't throw a ParseException
893 if (dateString.length() == 10) {
894 dateString = dateString + " 00:00";
899 java.util.Date d = userInputDateFormat.parse(dateString);
900 // Timestamp tStamp = new Timestamp(d.getTime());
901 sql.append(",webdb_create='" + JDBCStringRoutines.formatDate(d) + "'");
903 catch (ParseException e) {
904 throw new StorageObjectFailure(e);
909 sql.append(" where id=").append(id);
910 logger.info("UPDATE: " + sql);
913 con = obtainConnection();
914 con.setAutoCommit(false);
915 pstmt = con.prepareStatement(sql.toString());
917 pstmt.executeUpdate();
919 catch (SQLException sqe) {
920 throwSQLException(sqe, "update");
924 con.setAutoCommit(true);
926 catch (Exception e) {
930 freeConnection(con, pstmt);
936 * @param id des zu loeschenden Datensatzes
937 * @return boolean liefert true zurueck, wenn loeschen erfolgreich war.
939 public boolean delete(String id) throws StorageObjectFailure {
940 // ostore send notification
941 if (StoreUtil.extendsStorableEntity(theEntityClass)) {
942 String uniqueId = id;
944 if (theEntityClass.equals(StorableObjectEntity.class)) {
945 uniqueId += ("@" + mainTable);
948 logger.debug("CACHE: (del) " + id);
950 StoreIdentifier search_sid =
951 new StoreIdentifier(theEntityClass,
952 StoreContainerType.STOC_TYPE_ENTITY, uniqueId);
953 o_store.invalidate(search_sid);
956 /** todo could be prepared Statement */
957 Statement stmt = null;
958 Connection con = null;
961 "delete from " + mainTable + " where " + primaryKeyField + "='" + id + "'";
963 logger.debug("DELETE " + sql);
965 con = obtainConnection();
966 stmt = con.createStatement();
967 res = stmt.executeUpdate(sql);
969 catch (SQLException sqe) {
970 throwSQLException(sqe, "delete");
973 freeConnection(con, stmt);
978 return (res > 0) ? true : false;
982 * Deletes entities based on a where clause
984 * @param aWhereClause
986 * @throws StorageObjectFailure
988 public int deleteByWhereClause(String aWhereClause) throws StorageObjectFailure {
991 Statement stmt = null;
992 Connection con = null;
995 "delete from " + mainTable + " where " + aWhereClause;
997 //theLog.printInfo("DELETE " + sql);
999 con = obtainConnection();
1000 stmt = con.createStatement();
1001 res = stmt.executeUpdate(sql);
1003 catch (SQLException sqe) {
1004 throwSQLException(sqe, "delete");
1007 freeConnection(con, stmt);
1013 /* noch nicht implementiert.
1014 * @return immer false
1016 public boolean delete(EntityList theEntityList) {
1021 * Diese Methode fuehrt den Sqlstring <i>sql</i> aus und timed im Logfile.
1022 * @param stmt Statemnt
1023 * @param sql Sql-String
1025 public ResultSet executeSql(Statement stmt, String sql)
1026 throws StorageObjectFailure, SQLException {
1028 long startTime = System.currentTimeMillis();
1031 rs = stmt.executeQuery(sql);
1033 logger.info((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1035 catch (SQLException e) {
1036 logger.error(e.getMessage() +"\n" + (System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1043 private Map processRow(ResultSet aResultSet) throws StorageObjectFailure {
1045 Map result = new HashMap();
1046 ResultSetMetaData metaData = aResultSet.getMetaData();
1047 int nrColumns = metaData.getColumnCount();
1048 for (int i=0; i<nrColumns; i++) {
1049 result.put(metaData.getColumnName(i+1), getValueAsString(aResultSet, i+1, metaData.getColumnType(i+1)));
1054 catch (Throwable e) {
1055 throw new StorageObjectFailure(e);
1059 public List executeFreeSql(String sql, int aLimit) throws StorageObjectFailure, StorageObjectExc {
1060 Connection connection = null;
1061 Statement statement = null;
1063 List result = new ArrayList();
1064 connection = obtainConnection();
1065 statement = connection.createStatement();
1066 ResultSet resultset = executeSql(statement, sql);
1068 while (resultset.next() && result.size() < aLimit) {
1069 result.add(processRow(resultset));
1078 catch (Throwable e) {
1079 throw new StorageObjectFailure(e);
1082 if (connection!=null) {
1083 freeConnection(connection, statement);
1088 public Map executeFreeSingleRowSql(String anSqlStatement) throws StorageObjectFailure, StorageObjectExc {
1090 List resultList = executeFreeSql(anSqlStatement, 1);
1092 if (resultList.size()>0)
1093 return (Map) resultList.get(0);
1100 catch (Throwable t) {
1101 throw new StorageObjectFailure(t);
1105 public String executeFreeSingleValueSql(String sql) throws StorageObjectFailure, StorageObjectExc {
1106 Map row = executeFreeSingleRowSql(sql);
1111 Iterator i = row.values().iterator();
1113 return (String) i.next();
1118 public int getSize(String where) throws SQLException, StorageObjectFailure {
1119 return getSize("", null, where);
1122 * returns the number of rows in the table
1124 public int getSize(String mainTablePrefix, List extraTables, String where) throws SQLException, StorageObjectFailure {
1126 long startTime = System.currentTimeMillis();
1128 String useTable = mainTable;
1129 if (mainTablePrefix!=null && mainTablePrefix.trim().length()>0) {
1130 useTable+=" "+mainTablePrefix;
1132 StringBuffer countSql =
1133 new StringBuffer("select count(*) from ").append(useTable);
1134 // append extratables, if necessary
1135 if (extraTables!=null) {
1136 for (int i=0;i < extraTables.size();i++) {
1137 if (!extraTables.get(i).equals("")) {
1138 countSql.append( ", " + extraTables.get(i));
1143 if ((where != null) && (where.length() != 0)) {
1144 countSql.append( " where " + where);
1147 Connection con = null;
1148 Statement stmt = null;
1152 con = obtainConnection();
1153 stmt = con.createStatement();
1155 ResultSet rs = executeSql(stmt, countSql.toString());
1158 result = rs.getInt(1);
1161 catch (SQLException e) {
1162 logger.error("Database.getSize: " + e.getMessage());
1165 freeConnection(con, stmt);
1167 logger.info((System.currentTimeMillis() - startTime) + "ms. for: " + countSql);
1172 public int executeUpdate(Statement stmt, String sql)
1173 throws StorageObjectFailure, SQLException {
1175 long startTime = System.currentTimeMillis();
1178 rs = stmt.executeUpdate(sql);
1180 logger.info((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1182 catch (SQLException e) {
1183 logger.error("Failed: " + (System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1190 public int executeUpdate(String sql)
1191 throws StorageObjectFailure, SQLException {
1193 long startTime = System.currentTimeMillis();
1194 Connection con = null;
1195 PreparedStatement pstmt = null;
1198 con = obtainConnection();
1199 pstmt = con.prepareStatement(sql);
1200 result = pstmt.executeUpdate();
1202 catch (Throwable e) {
1203 logger.error("Database.executeUpdate(" + sql + "): " + e.getMessage());
1204 throw new StorageObjectFailure("Database.executeUpdate(" + sql + "): " + e.getMessage(), e);
1207 freeConnection(con, pstmt);
1210 logger.info((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1215 * Processes the metadata for the table this Database object is responsible for.
1217 private void processMetaData(ResultSetMetaData aMetaData) throws StorageObjectFailure {
1218 fieldNames = new ArrayList();
1221 int numFields = aMetaData.getColumnCount();
1222 fieldTypes = new int[numFields];
1224 for (int i = 1; i <= numFields; i++) {
1225 fieldNames.add(aMetaData.getColumnName(i));
1226 fieldTypes[i - 1] = aMetaData.getColumnType(i);
1229 catch (SQLException e) {
1230 throwSQLException(e, "processMetaData");
1235 * Retrieves metadata from the table this Database object represents
1237 private void retrieveMetaData() throws StorageObjectFailure {
1238 Connection connection = null;
1239 PreparedStatement statement = null;
1240 String sql = "select * from " + mainTable + " where 0=1";
1243 connection = obtainConnection();
1244 statement = connection.prepareStatement(sql);
1246 logger.debug("METADATA: " + sql);
1247 ResultSet resultSet = statement.executeQuery();
1249 processMetaData(resultSet.getMetaData());
1255 catch (SQLException e) {
1256 throwSQLException(e, "retrieveMetaData");
1259 freeConnection(connection, statement);
1263 public Connection obtainConnection() throws StorageObjectFailure {
1265 return MirGlobal.getDatabaseEngine().obtainConnection();
1267 catch (Exception e) {
1268 throw new StorageObjectFailure(e);
1272 public void freeConnection(Connection aConnection, Statement aStatement) throws StorageObjectFailure {
1276 catch (Throwable t) {
1277 logger.warn("Can't close statemnet: " + t.toString());
1281 MirGlobal.getDatabaseEngine().releaseConnection(aConnection);
1283 catch (Throwable t) {
1284 logger.warn("Can't release connection: " + t.toString());
1289 * Wertet SQLException aus und wirft dannach eine StorageObjectException
1290 * @param sqe SQLException
1291 * @param aFunction Funktonsname, in der die SQLException geworfen wurde
1293 protected void throwSQLException(SQLException sqe, String aFunction) throws StorageObjectFailure {
1295 String message = "";
1299 state = sqe.getSQLState();
1300 message = sqe.getMessage();
1301 vendor = sqe.getErrorCode();
1304 String information =
1307 ", vendor= " + vendor +
1308 ", message=" + message +
1309 ", function= " + aFunction;
1311 logger.error(information);
1313 throw new StorageObjectFailure(information, sqe);
1316 protected void _throwStorageObjectException(Exception e, String aFunction)
1317 throws StorageObjectFailure {
1320 logger.error(e.getMessage() + aFunction);
1321 throw new StorageObjectFailure(aFunction, e);
1326 * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach
1327 * eine StorageObjectException
1328 * @param aMessage Nachricht mit dem Fehler
1329 * @exception StorageObjectFailure
1331 void throwStorageObjectException(String aMessage) throws StorageObjectFailure {
1332 logger.error(aMessage);
1333 throw new StorageObjectFailure(aMessage, null);
1337 * Invalidates any cached entity list
1339 private void invalidateStore() {
1340 // invalidating all EntityLists corresponding with theEntityClass
1341 if (StoreUtil.extendsStorableEntity(theEntityClass)) {
1342 StoreContainerType stoc_type =
1343 StoreContainerType.valueOf(theEntityClass, StoreContainerType.STOC_TYPE_ENTITYLIST);
1344 o_store.invalidate(stoc_type);
1349 * Retrieves a binary value
1351 public InputStream getBinaryField(String aQuery) throws StorageObjectFailure, SQLException {
1352 Connection connection=null;
1353 Statement statement=null;
1354 InputStream inputStream;
1355 InputStream imageInputStream = null;
1358 connection = obtainConnection();
1360 connection.setAutoCommit(false);
1361 statement = connection.createStatement();
1362 ResultSet resultSet = executeSql(statement, aQuery);
1364 if(resultSet!=null) {
1365 if (resultSet.next()) {
1366 inputStream = resultSet.getBlob(1).getBinaryStream();
1367 imageInputStream = new BinaryFieldInputStream(inputStream, connection, statement);
1375 catch (Throwable t) {
1376 logger.error("EntityImages.getImage failed: " + t.toString());
1377 t.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1380 connection.setAutoCommit(true);
1382 catch (Throwable e) {
1383 logger.error("EntityImages.getImage resetting transaction mode failed: " + e.toString());
1384 e.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1388 freeConnection(connection, statement);
1390 catch (Throwable e) {
1391 logger.error("EntityImages.getImage freeing connection failed: " +e.toString());
1394 throw new StorageObjectFailure(t);
1397 return imageInputStream;
1401 * Sets a binary value. The query is supposed to contain 1 ? denoting where the
1402 * binary value should be inserted.
1404 * e.g. <code>update images set image_data = ? where id= 22</code>
1406 public void setBinaryField(String aQuery, byte aData[]) throws StorageObjectFailure, SQLException {
1407 PreparedStatement statement = null;
1408 Connection connection = obtainConnection();
1410 connection.setAutoCommit(false);
1412 statement = connection.prepareStatement(aQuery);
1413 statement.setBinaryStream(1, new ByteArrayInputStream(aData), aData.length);
1414 statement.execute();
1415 connection.commit();
1418 connection.setAutoCommit(true);
1422 freeConnection(connection, statement);
1427 * a small wrapper class that allows us to store the DB connection resources
1428 * that the BlobInputStream is using and free them upon closing of the stream
1430 private class BinaryFieldInputStream extends InputStream {
1431 InputStream inputStream;
1432 Connection connection;
1433 Statement statement;
1435 public BinaryFieldInputStream(InputStream aBlobInputStream, Connection aConnection, Statement aStatement ) {
1436 inputStream = aBlobInputStream;
1437 connection = aConnection;
1438 statement = aStatement;
1441 public void close () throws IOException {
1442 inputStream.close();
1444 connection.setAutoCommit(true);
1445 freeConnection(connection, statement);
1447 catch (Exception e) {
1448 throw new IOException("close(): "+e.toString());
1452 public int read() throws IOException {
1453 return inputStream.read();