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;
43 import java.io.ByteArrayInputStream;
44 import java.io.IOException;
45 import java.io.InputStream;
46 import java.io.InputStreamReader;
48 import java.text.ParseException;
49 import java.text.SimpleDateFormat;
53 * Implements database access.
55 * @version $Id: Database.java,v 1.44.2.25 2005/01/09 22:07:45 zapata Exp $
59 public class Database implements StorageObject {
60 private static Class GENERIC_ENTITY_CLASS = mir.entity.StorableObjectEntity.class;
61 protected static final ObjectStore o_store = ObjectStore.getInstance();
62 private static final int _millisPerHour = 60 * 60 * 1000;
64 protected LoggerWrapper logger;
66 protected MirPropertiesConfiguration configuration;
67 protected String mainTable;
68 protected String primaryKeySequence = null;
69 protected String primaryKeyField = "id";
71 protected List fieldNames;
72 protected int[] fieldTypes;
74 protected Class entityClass;
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 entityClass = 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() {
122 public Entity createNewEntity() throws StorageObjectFailure {
124 AbstractEntity result = (AbstractEntity) entityClass.newInstance();
125 result.setStorage(this);
129 catch (Throwable t) {
130 throw new StorageObjectFailure(t);
135 * Liefert die Standardbeschr?nkung von select-Statements zur?ck, also
136 * wieviel Datens?tze per Default selektiert werden.
138 * @return Standard-Anzahl der Datens?tze
140 public int getLimit() {
145 * Liefert den Namen des Primary-Keys zur?ck. Wird die Variable nicht von
146 * der erbenden Klasse ?berschrieben, so ist der Wert <code>PKEY</code>
147 * @return Name des Primary-Keys
149 public String getIdName() {
150 return primaryKeyField;
154 * Liefert den Namen der Tabelle, auf das sich das Datenbankobjekt bezieht.
156 * @return Name der Tabelle
158 public String getTableName() {
163 * Returns the id that was most recently added to the database
165 private String getLatestInsertedId(Connection aConnection) throws SQLException {
166 if (primaryKeySequence==null)
167 primaryKeySequence = mainTable+"_id_seq";
169 PreparedStatement statement = aConnection.prepareStatement("select currval('" + primaryKeySequence + "')");
171 ResultSet rs = statement.executeQuery();
173 return rs.getString(1);
179 public List getFieldNames() throws StorageObjectFailure {
180 if (fieldNames == null) {
188 * Gets value out of ResultSet according to type and converts to String
189 * @param rs ResultSet.
190 * @param aType a type from java.sql.Types.*
191 * @param valueIndex index in ResultSet
192 * @return returns the value as String. If no conversion is possible
193 * /unsupported value/ is returned
195 private String getValueAsString(ResultSet rs, int valueIndex, int aType)
196 throws StorageObjectFailure {
197 String outValue = null;
202 case java.sql.Types.BIT:
203 outValue = (rs.getBoolean(valueIndex) == true) ? "1" : "0";
207 case java.sql.Types.INTEGER:
208 case java.sql.Types.SMALLINT:
209 case java.sql.Types.TINYINT:
210 case java.sql.Types.BIGINT:
212 int out = rs.getInt(valueIndex);
215 outValue = new Integer(out).toString();
220 case java.sql.Types.NUMERIC:
222 /** todo Numeric can be float or double depending upon
223 * metadata.getScale() / especially with oracle */
224 long outl = rs.getLong(valueIndex);
227 outValue = new Long(outl).toString();
232 case java.sql.Types.REAL:
234 float tempf = rs.getFloat(valueIndex);
240 int tempf_int = (int) tempf;
241 tempf = (float) tempf_int;
243 outValue = "" + tempf;
244 outValue = outValue.replace('.', ',');
249 case java.sql.Types.DOUBLE:
251 double tempd = rs.getDouble(valueIndex);
257 int tempd_int = (int) tempd;
258 tempd = (double) tempd_int;
260 outValue = "" + tempd;
261 outValue = outValue.replace('.', ',');
266 case java.sql.Types.CHAR:
267 case java.sql.Types.VARCHAR:
268 case java.sql.Types.LONGVARCHAR:
269 outValue = rs.getString(valueIndex);
273 case java.sql.Types.LONGVARBINARY:
274 outValue = rs.getString(valueIndex);
278 case java.sql.Types.TIMESTAMP:
280 // it's important to use Timestamp here as getting it
281 // as a string is undefined and is only there for debugging
282 // according to the API. we can make it a string through formatting.
284 Timestamp timestamp = (rs.getTimestamp(valueIndex));
287 java.util.Date date = new java.util.Date(timestamp.getTime());
289 Calendar calendar = new GregorianCalendar();
290 calendar.setTime(date);
291 calendar.setTimeZone(timezone);
292 outValue = internalDateFormat.format(date);
294 int offset = calendar.get(Calendar.ZONE_OFFSET) + calendar.get(Calendar.DST_OFFSET);
295 String tzOffset = StringUtil.zeroPaddingNumber(Math.abs(offset) / _millisPerHour, 2, 2);
298 outValue = outValue + "-";
300 outValue = outValue + "+";
301 outValue = outValue + tzOffset;
307 outValue = "<unsupported value>";
308 logger.warn("Unsupported Datatype: at " + valueIndex + " (" + aType + ")");
310 } catch (SQLException e) {
311 throw new StorageObjectFailure("Could not get Value out of Resultset -- ",
320 * select-Operator um einen Datensatz zu bekommen.
321 * @param id Primaerschluessel des Datensatzes.
322 * @return liefert EntityObject des gefundenen Datensatzes oder null.
324 public Entity selectById(String id) throws StorageObjectExc {
325 if ((id == null) || id.equals("")) {
326 throw new StorageObjectExc("Database.selectById: Missing id");
329 // ask object store for object
330 if (StoreUtil.extendsStorableEntity(entityClass)) {
331 String uniqueId = id;
333 if (entityClass.equals(StorableObjectEntity.class)) {
334 uniqueId += ("@" + mainTable);
337 StoreIdentifier search_sid = new StoreIdentifier(entityClass, uniqueId);
338 logger.debug("CACHE: (dbg) looking for sid " + search_sid.toString());
340 Entity hit = (Entity) o_store.use(search_sid);
347 Statement stmt = null;
348 Connection con = obtainConnection();
349 Entity returnEntity = null;
354 /** todo better prepared statement */
356 "select * from " + mainTable + " where " + primaryKeyField + "=" + id;
357 stmt = con.createStatement();
358 rs = executeSql(stmt, selectSql);
362 returnEntity = makeEntityFromResultSet(rs);
365 logger.warn("No data for id: " + id + " in table " + mainTable);
371 logger.warn("No Data for Id " + id + " in Table " + mainTable);
374 catch (SQLException sqe) {
375 throwSQLException(sqe, "selectById");
378 catch (NumberFormatException e) {
379 logger.error("ID is no number: " + id);
382 freeConnection(con, stmt);
389 * This method makes it possible to make selects across multiple tables
391 * @param mainTablePrefix prefix for the mainTable
392 * @param extraTables a vector of tables for relational select
393 * @param aWhereClause whereClause
394 * @return EntityList of selected Objects
395 * @throws StorageObjectFailure
398 public EntityList selectByWhereClauseWithExtraTables(String mainTablePrefix,
399 List extraTables, String aWhereClause )
400 throws StorageObjectFailure {
401 return selectByWhereClause( mainTablePrefix, extraTables, aWhereClause, "", 0, defaultLimit);
404 public EntityList selectByFieldValue(String aField, String aValue) throws StorageObjectFailure {
405 return selectByFieldValue(aField, aValue, 0);
408 public EntityList selectByFieldValue(String aField, String aValue, int offset) throws StorageObjectFailure {
409 return selectByWhereClause(aField + "='" + JDBCStringRoutines.escapeStringLiteral(aValue)+"'", offset);
413 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
414 * Also offset wird der erste Datensatz genommen.
416 * @param where where-Clause
417 * @return EntityList mit den gematchten Entities
418 * @exception StorageObjectFailure
420 public EntityList selectByWhereClause(String where) throws StorageObjectFailure {
421 return selectByWhereClause(where, 0);
425 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
426 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
428 * @param whereClause where-Clause
429 * @param offset ab welchem Datensatz.
430 * @return EntityList mit den gematchten Entities
431 * @exception StorageObjectFailure
433 public EntityList selectByWhereClause(String whereClause, int offset) throws StorageObjectFailure {
434 return selectByWhereClause(whereClause, null, offset);
438 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
439 * Also offset wird der erste Datensatz genommen.
440 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
442 * @param where where-Clause
443 * @param order orderBy-Clause
444 * @return EntityList mit den gematchten Entities
445 * @exception StorageObjectFailure
447 public EntityList selectByWhereClause(String where, String order) throws StorageObjectFailure {
448 return selectByWhereClause(where, order, 0);
451 public EntityList selectByWhereClause(String mainTablePrefix, List extraTables, String where, String order) throws StorageObjectFailure {
452 return selectByWhereClause(mainTablePrefix, extraTables, where, order, 0, defaultLimit);
456 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
457 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
459 * @param whereClause where-Clause
460 * @param orderBy orderBy-Clause
461 * @param offset ab welchem Datensatz
462 * @return EntityList mit den gematchten Entities
463 * @exception StorageObjectFailure
465 public EntityList selectByWhereClause(String whereClause, String orderBy, int offset) throws StorageObjectFailure {
466 return selectByWhereClause(whereClause, orderBy, offset, defaultLimit);
470 * select-Operator returns EntityList with matching rows in Database.
471 * @param aWhereClause where-Clause
472 * @param anOrderByClause orderBy-Clause
473 * @param offset ab welchem Datensatz
474 * @param limit wieviele Datens?tze
475 * @return EntityList mit den gematchten Entities
476 * @exception StorageObjectFailure
478 public EntityList selectByWhereClause(String aWhereClause, String anOrderByClause,
479 int offset, int limit) throws StorageObjectFailure {
480 return selectByWhereClause("", null, aWhereClause, anOrderByClause, offset, limit);
485 * select-Operator returns EntityList with matching rows in Database.
486 * @param aWhereClause where-Clause
487 * @param anOrderByClause orderBy-Clause
488 * @param anOffset ab welchem Datensatz
489 * @param aLimit wieviele Datens?tze
490 * @return EntityList mit den gematchten Entities
491 * @exception StorageObjectFailure
493 public EntityList selectByWhereClause(
494 String aMainTablePrefix, List anExtraTables,
495 String aWhereClause, String anOrderByClause,
496 int anOffset, int aLimit) throws StorageObjectFailure {
498 // TODO get rid of emtpy Strings in anExtraTables
499 // make anExtraTables null, if single empty String in it
500 // cause StringUtil.splitString puts in emptyString
502 if (anExtraTables!=null && ((String) anExtraTables.get(0)).trim().equals("")){
506 String useTable = mainTable;
507 String selection = "*";
509 if (aMainTablePrefix != null && aMainTablePrefix.trim().length() > 0) {
510 useTable += " " + aMainTablePrefix;
511 selection = aMainTablePrefix.trim() + ".*";
514 // check o_store for entitylist
515 // only if no relational select
516 if (anExtraTables==null) {
517 if (StoreUtil.extendsStorableEntity(entityClass)) {
518 StoreIdentifier searchSid = new StoreIdentifier(entityClass,
519 StoreContainerType.STOC_TYPE_ENTITYLIST,
520 StoreUtil.getEntityListUniqueIdentifierFor(mainTable,
521 aWhereClause, anOrderByClause, anOffset, aLimit));
522 EntityList hit = (EntityList) o_store.use(searchSid);
531 EntityList theReturnList = null;
532 Connection connection = null;
533 Statement statement = null;
536 // build sql-statement
538 if ((aWhereClause != null) && (aWhereClause.trim().length() == 0)) {
542 StringBuffer selectSql =
543 new StringBuffer("select "+selection+" from ").append(useTable);
545 // append extratables, if necessary
546 if (anExtraTables!=null) {
547 for (int i=0;i < anExtraTables.size();i++) {
548 if (!anExtraTables.get(i).equals("")) {
549 selectSql.append( ", " + anExtraTables.get(i));
554 if (aWhereClause != null) {
555 selectSql.append(" where ").append(aWhereClause);
558 if ((anOrderByClause != null) && !(anOrderByClause.trim().length() == 0)) {
559 selectSql.append(" order by ").append(anOrderByClause);
562 if ((aLimit > -1) && (anOffset > -1)) {
563 selectSql.append(" LIMIT ").append(aLimit+1).append(" OFFSET ").append(anOffset);
568 connection = obtainConnection();
569 statement = connection.createStatement();
570 boolean hasMore = false;
573 resultSet = executeSql(statement, selectSql.toString());
575 if (resultSet != null) {
576 theReturnList = new EntityList();
577 Entity theResultEntity;
579 while (((aLimit == -1) || (position<aLimit)) && resultSet.next()) {
580 theResultEntity = makeEntityFromResultSet(resultSet);
581 theReturnList.add(theResultEntity);
584 hasMore = resultSet.next();
588 if (theReturnList != null) {
589 // now we decide if we have to know an overall count...
590 theReturnList.setOffset(anOffset);
591 theReturnList.setWhere(aWhereClause);
592 theReturnList.setOrder(anOrderByClause);
593 theReturnList.setStorage(this);
594 theReturnList.setLimit(aLimit);
596 if (anOffset >= aLimit) {
597 theReturnList.setPrevBatch(anOffset - aLimit);
601 theReturnList.setNextBatch(anOffset + aLimit);
604 if (anExtraTables==null && StoreUtil.extendsStorableEntity(entityClass)) {
605 StoreIdentifier sid = theReturnList.getStoreIdentifier();
606 logger.debug("CACHE (add): " + sid.toString());
611 catch (SQLException sqe) {
612 throwSQLException(sqe, "selectByWhereClause");
616 if (connection != null) {
617 freeConnection(connection, statement);
619 } catch (Throwable t) {
623 return theReturnList;
626 private Entity makeEntityFromResultSet(ResultSet rs)
627 throws StorageObjectFailure {
628 Map theResultHash = new HashMap();
629 String theResult = null;
631 Entity returnEntity = null;
634 if (StoreUtil.extendsStorableEntity(entityClass)) {
635 StoreIdentifier searchSid = StorableObjectEntity.getStoreIdentifier(this,
637 Entity hit = (Entity) o_store.use(searchSid);
638 if (hit != null) return hit;
641 for (int i = 0; i < getFieldNames().size(); i++) {
642 // alle durchlaufen bis nix mehr da
643 type = fieldTypes[i];
645 if (type == java.sql.Types.LONGVARBINARY) {
646 InputStreamReader is =
647 (InputStreamReader) rs.getCharacterStream(i + 1);
650 char[] data = new char[32768];
651 StringBuffer theResultString = new StringBuffer();
654 while ((len = is.read(data)) > 0) {
655 theResultString.append(data, 0, len);
659 theResult = theResultString.toString();
666 theResult = getValueAsString(rs, (i + 1), type);
669 if (theResult != null) {
670 theResultHash.put(getFieldNames().get(i), theResult);
674 if (entityClass != null) {
675 returnEntity = createNewEntity();
676 returnEntity.setFieldValues(theResultHash);
678 if (returnEntity instanceof StorableObject) {
679 logger.debug("CACHE: ( in) " + returnEntity.getId() + " :" + mainTable);
680 o_store.add(((StorableObject) returnEntity).getStoreIdentifier());
683 throwStorageObjectException("Internal Error: entityClass not set!");
686 catch (IOException e) {
687 throwStorageObjectException("IOException! -- " + e.getMessage());
689 catch (SQLException sqe) {
690 throwSQLException(sqe, "makeEntityFromResultSet");
699 * Inserts an entity into the database.
702 * @return der Wert des Primary-keys der eingef?gten Entity
704 public String insert(Entity anEntity) throws StorageObjectFailure {
707 String returnId = null;
708 Connection con = null;
709 PreparedStatement pstmt = null;
712 StringBuffer f = new StringBuffer();
713 StringBuffer v = new StringBuffer();
716 boolean firstField = true;
719 for (int i = 0; i < getFieldNames().size(); i++) {
720 aField = (String) getFieldNames().get(i);
722 if (!aField.equals(primaryKeyField)) {
726 if (!anEntity.hasFieldValue(aField) && (
727 aField.equals("webdb_create") ||
728 aField.equals("webdb_lastchange"))) {
732 if (anEntity.hasFieldValue(aField)) {
735 JDBCStringRoutines.escapeStringLiteral(anEntity.getFieldValue(aField)) + "'";
739 // wenn Wert gegeben, dann einbauen
740 if (aValue != null) {
741 if (firstField == false) {
757 StringBuffer sqlBuf =
758 new StringBuffer("insert into ").append(mainTable).append("(").append(f)
759 .append(") values (").append(v).append(")");
760 String sql = sqlBuf.toString();
763 con = obtainConnection();
764 con.setAutoCommit(false);
765 pstmt = con.prepareStatement(sql);
767 int ret = pstmt.executeUpdate();
774 // pstmt = con.prepareStatement("select currval('" + + "_id_seq')");
776 returnId = getLatestInsertedId(con);
777 anEntity.setId(returnId);
779 catch (SQLException sqe) {
780 throwSQLException(sqe, "insert");
784 con.setAutoCommit(true);
786 catch (Exception e) {
789 freeConnection(con, pstmt);
792 /** todo store entity in o_store */
797 * Updates an entity in the database
801 public void update(Entity theEntity) throws StorageObjectFailure {
802 Connection con = null;
803 PreparedStatement pstmt = null;
805 /** todo this is stupid: why do we prepare statement, when we
806 * throw it away afterwards. should be regular statement
807 * update/insert could better be one routine called save()
808 * that chooses to either insert or update depending if we
809 * have a primary key in the entity. i don't know if we
810 * still need the streamed input fields. // rk */
812 /** todo extension: check if Entity did change, otherwise we don't need
813 * the roundtrip to the database */
814 /** invalidating corresponding entitylists in o_store*/
818 String id = theEntity.getId();
820 StringBuffer fv = new StringBuffer();
821 boolean firstField = true;
823 // build sql statement
824 for (int i = 0; i < getFieldNames().size(); i++) {
825 aField = (String) getFieldNames().get(i);
828 // todo if entity.hasFieldValue returns false, then the value should be stored as null
829 if (!(aField.equals(primaryKeyField) ||
830 aField.equals("webdb_create") ||
831 aField.equals("webdb_lastchange"))) {
832 if (theEntity.hasFieldValue(aField)) {
833 if (firstField == false) {
840 fv.append(aField).append("='").append(JDBCStringRoutines.escapeStringLiteral(theEntity.getFieldValue(aField))).append("'");
842 // fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getFieldValue(aField))).append("'");
848 new StringBuffer("update ").append(mainTable).append(" set ").append(fv);
851 if (getFieldNames().contains("webdb_lastchange")) {
852 sql.append(",webdb_lastchange=NOW()");
855 // special case: the webdb_create requires the field in yyyy-mm-dd HH:mm
856 // format so anything extra will be ignored. -mh
857 if (getFieldNames().contains("webdb_create") &&
858 theEntity.hasFieldValue("webdb_create")) {
859 // minimum of 10 (yyyy-mm-dd)...
860 if (theEntity.getFieldValue("webdb_create").length() >= 10) {
861 String dateString = theEntity.getFieldValue("webdb_create");
863 // if only 10, then add 00:00 so it doesn't throw a ParseException
864 if (dateString.length() == 10) {
865 dateString = dateString + " 00:00";
870 java.util.Date d = userInputDateFormat.parse(dateString);
871 // Timestamp tStamp = new Timestamp(d.getTime());
872 sql.append(",webdb_create='" + JDBCStringRoutines.formatDate(d) + "'");
874 catch (ParseException e) {
875 throw new StorageObjectFailure(e);
880 sql.append(" where id=").append(id);
881 logQueryBefore(sql.toString());
884 con = obtainConnection();
885 con.setAutoCommit(false);
886 pstmt = con.prepareStatement(sql.toString());
888 pstmt.executeUpdate();
890 catch (SQLException sqe) {
891 throwSQLException(sqe, "update");
895 con.setAutoCommit(true);
897 catch (Exception e) {
901 freeConnection(con, pstmt);
907 * @param id des zu loeschenden Datensatzes
908 * @return boolean liefert true zurueck, wenn loeschen erfolgreich war.
910 public boolean delete(String id) throws StorageObjectFailure {
911 // ostore send notification
912 if (StoreUtil.extendsStorableEntity(entityClass)) {
913 String uniqueId = id;
915 if (entityClass.equals(StorableObjectEntity.class)) {
916 uniqueId += ("@" + mainTable);
919 logger.debug("CACHE: (del) " + id);
921 StoreIdentifier search_sid =
922 new StoreIdentifier(entityClass,
923 StoreContainerType.STOC_TYPE_ENTITY, uniqueId);
924 o_store.invalidate(search_sid);
927 /** todo could be prepared Statement */
928 Statement stmt = null;
929 Connection con = null;
932 "delete from " + mainTable + " where " + primaryKeyField + "='" + id + "'";
936 con = obtainConnection();
937 stmt = con.createStatement();
938 res = stmt.executeUpdate(sql);
940 catch (SQLException sqe) {
941 throwSQLException(sqe, "delete");
944 freeConnection(con, stmt);
949 return (res > 0) ? true : false;
953 * Deletes entities based on a where clause
955 * @param aWhereClause
957 * @throws StorageObjectFailure
959 public int deleteByWhereClause(String aWhereClause) throws StorageObjectFailure {
962 Statement stmt = null;
963 Connection con = null;
966 "delete from " + mainTable + " where " + aWhereClause;
968 //theLog.printInfo("DELETE " + sql);
970 con = obtainConnection();
971 stmt = con.createStatement();
972 res = stmt.executeUpdate(sql);
974 catch (SQLException sqe) {
975 throwSQLException(sqe, "delete");
978 freeConnection(con, stmt);
984 /* noch nicht implementiert.
985 * @return immer false
987 public boolean delete(EntityList theEntityList) {
992 * Diese Methode fuehrt den Sqlstring <i>sql</i> aus und timed im Logfile.
993 * @param stmt Statemnt
994 * @param sql Sql-String
996 public ResultSet executeSql(Statement stmt, String sql)
997 throws StorageObjectFailure, SQLException {
1000 long startTime = System.currentTimeMillis();
1002 rs = stmt.executeQuery(sql);
1004 logQueryAfter(sql, (System.currentTimeMillis() - startTime));
1006 catch (SQLException e) {
1007 logQueryError(sql, (System.currentTimeMillis() - startTime), e);
1014 private Map processRow(ResultSet aResultSet) throws StorageObjectFailure {
1016 Map result = new HashMap();
1017 ResultSetMetaData metaData = aResultSet.getMetaData();
1018 int nrColumns = metaData.getColumnCount();
1019 for (int i=0; i<nrColumns; i++) {
1020 result.put(metaData.getColumnName(i+1), getValueAsString(aResultSet, i+1, metaData.getColumnType(i+1)));
1025 catch (Throwable e) {
1026 throw new StorageObjectFailure(e);
1030 public List executeFreeSql(String sql, int aLimit) throws StorageObjectFailure, StorageObjectExc {
1031 Connection connection = null;
1032 Statement statement = null;
1034 List result = new ArrayList();
1035 connection = obtainConnection();
1036 statement = connection.createStatement();
1037 ResultSet resultset = executeSql(statement, sql);
1039 while (resultset.next() && result.size() < aLimit) {
1040 result.add(processRow(resultset));
1049 catch (Throwable e) {
1050 throw new StorageObjectFailure(e);
1053 if (connection!=null) {
1054 freeConnection(connection, statement);
1059 public Map executeFreeSingleRowSql(String anSqlStatement) throws StorageObjectFailure, StorageObjectExc {
1061 List resultList = executeFreeSql(anSqlStatement, 1);
1063 if (resultList.size()>0)
1064 return (Map) resultList.get(0);
1071 catch (Throwable t) {
1072 throw new StorageObjectFailure(t);
1076 public String executeFreeSingleValueSql(String sql) throws StorageObjectFailure, StorageObjectExc {
1077 Map row = executeFreeSingleRowSql(sql);
1082 Iterator i = row.values().iterator();
1084 return (String) i.next();
1089 public int getSize(String where) throws SQLException, StorageObjectFailure {
1090 return getSize("", null, where);
1093 * returns the number of rows in the table
1095 public int getSize(String mainTablePrefix, List extraTables, String where) throws SQLException, StorageObjectFailure {
1097 String useTable = mainTable;
1098 if (mainTablePrefix!=null && mainTablePrefix.trim().length()>0) {
1099 useTable+=" "+mainTablePrefix;
1101 StringBuffer countSql =
1102 new StringBuffer("select count(*) from ").append(useTable);
1103 // append extratables, if necessary
1104 if (extraTables!=null) {
1105 for (int i=0;i < extraTables.size();i++) {
1106 if (!extraTables.get(i).equals("")) {
1107 countSql.append( ", " + extraTables.get(i));
1112 if ((where != null) && (where.length() != 0)) {
1113 countSql.append( " where " + where);
1116 Connection con = null;
1117 Statement stmt = null;
1119 logQueryBefore(countSql.toString());
1120 long startTime = System.currentTimeMillis();
1123 con = obtainConnection();
1124 stmt = con.createStatement();
1126 ResultSet rs = executeSql(stmt, countSql.toString());
1129 result = rs.getInt(1);
1132 catch (SQLException e) {
1133 logger.error("Database.getSize: " + e.getMessage());
1136 freeConnection(con, stmt);
1138 logQueryAfter(countSql.toString(), (System.currentTimeMillis() - startTime));
1143 public int executeUpdate(Statement stmt, String sql)
1144 throws StorageObjectFailure, SQLException {
1147 logQueryBefore(sql);
1148 long startTime = System.currentTimeMillis();
1151 rs = stmt.executeUpdate(sql);
1153 logQueryAfter(sql, (System.currentTimeMillis() - startTime));
1155 catch (SQLException e) {
1156 logQueryError(sql, (System.currentTimeMillis() - startTime), e);
1163 public int executeUpdate(String sql)
1164 throws StorageObjectFailure, SQLException {
1166 Connection con = null;
1167 PreparedStatement pstmt = null;
1169 logQueryBefore(sql);
1170 long startTime = System.currentTimeMillis();
1172 con = obtainConnection();
1173 pstmt = con.prepareStatement(sql);
1174 result = pstmt.executeUpdate();
1175 logQueryAfter(sql, System.currentTimeMillis() - startTime);
1177 catch (Throwable e) {
1178 logQueryError(sql, System.currentTimeMillis() - startTime, e);
1179 throw new StorageObjectFailure("Database.executeUpdate(" + sql + "): " + e.getMessage(), e);
1182 freeConnection(con, pstmt);
1188 * Processes the metadata for the table this Database object is responsible for.
1190 private void processMetaData(ResultSetMetaData aMetaData) throws StorageObjectFailure {
1191 fieldNames = new ArrayList();
1194 int numFields = aMetaData.getColumnCount();
1195 fieldTypes = new int[numFields];
1197 for (int i = 1; i <= numFields; i++) {
1198 fieldNames.add(aMetaData.getColumnName(i));
1199 fieldTypes[i - 1] = aMetaData.getColumnType(i);
1202 catch (SQLException e) {
1203 throwSQLException(e, "processMetaData");
1208 * Retrieves metadata from the table this Database object represents
1210 private void retrieveMetaData() throws StorageObjectFailure {
1211 Connection connection = null;
1212 PreparedStatement statement = null;
1213 String sql = "select * from " + mainTable + " where 0=1";
1216 connection = obtainConnection();
1217 statement = connection.prepareStatement(sql);
1219 logger.debug("METADATA: " + sql);
1220 ResultSet resultSet = statement.executeQuery();
1222 processMetaData(resultSet.getMetaData());
1228 catch (SQLException e) {
1229 throwSQLException(e, "retrieveMetaData");
1232 freeConnection(connection, statement);
1236 public Connection obtainConnection() throws StorageObjectFailure {
1238 return MirGlobal.getDatabaseEngine().obtainConnection();
1240 catch (Exception e) {
1241 throw new StorageObjectFailure(e);
1245 public void freeConnection(Connection aConnection, Statement aStatement) throws StorageObjectFailure {
1249 catch (Throwable t) {
1250 logger.warn("Can't close statemnet: " + t.toString());
1254 MirGlobal.getDatabaseEngine().releaseConnection(aConnection);
1256 catch (Throwable t) {
1257 logger.warn("Can't release connection: " + t.toString());
1262 * Wertet SQLException aus und wirft dannach eine StorageObjectException
1263 * @param sqe SQLException
1264 * @param aFunction Funktonsname, in der die SQLException geworfen wurde
1266 protected void throwSQLException(SQLException sqe, String aFunction) throws StorageObjectFailure {
1268 String message = "";
1272 state = sqe.getSQLState();
1273 message = sqe.getMessage();
1274 vendor = sqe.getErrorCode();
1277 String information =
1280 ", vendor= " + vendor +
1281 ", message=" + message +
1282 ", function= " + aFunction;
1284 logger.error(information);
1286 throw new StorageObjectFailure(information, sqe);
1289 protected void _throwStorageObjectException(Exception e, String aFunction)
1290 throws StorageObjectFailure {
1293 logger.error(e.getMessage() + aFunction);
1294 throw new StorageObjectFailure(aFunction, e);
1299 * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach
1300 * eine StorageObjectException
1301 * @param aMessage Nachricht mit dem Fehler
1302 * @exception StorageObjectFailure
1304 void throwStorageObjectException(String aMessage) throws StorageObjectFailure {
1305 logger.error(aMessage);
1306 throw new StorageObjectFailure(aMessage, null);
1310 * Invalidates any cached entity list
1312 private void invalidateStore() {
1313 // invalidating all EntityLists corresponding with entityClass
1314 if (StoreUtil.extendsStorableEntity(entityClass)) {
1315 StoreContainerType stoc_type =
1316 StoreContainerType.valueOf(entityClass, StoreContainerType.STOC_TYPE_ENTITYLIST);
1317 o_store.invalidate(stoc_type);
1322 * Retrieves a binary value
1324 public InputStream getBinaryField(String aQuery) throws StorageObjectFailure, SQLException {
1325 Connection connection=null;
1326 Statement statement=null;
1327 InputStream inputStream;
1328 InputStream imageInputStream = null;
1331 connection = obtainConnection();
1333 connection.setAutoCommit(false);
1334 statement = connection.createStatement();
1335 ResultSet resultSet = executeSql(statement, aQuery);
1337 if(resultSet!=null) {
1338 if (resultSet.next()) {
1339 inputStream = resultSet.getBlob(1).getBinaryStream();
1340 imageInputStream = new BinaryFieldInputStream(inputStream, connection, statement);
1348 catch (Throwable t) {
1349 logger.error("EntityImages.getImage failed: " + t.toString());
1350 t.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1353 connection.setAutoCommit(true);
1355 catch (Throwable e) {
1356 logger.error("EntityImages.getImage resetting transaction mode failed: " + e.toString());
1357 e.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1361 freeConnection(connection, statement);
1363 catch (Throwable e) {
1364 logger.error("EntityImages.getImage freeing connection failed: " +e.toString());
1367 throw new StorageObjectFailure(t);
1370 return imageInputStream;
1374 * Sets a binary value. The query is supposed to contain 1 ? denoting where the
1375 * binary value should be inserted.
1377 * e.g. <code>update images set image_data = ? where id= 22</code>
1379 public void setBinaryField(String aQuery, byte aData[]) throws StorageObjectFailure, SQLException {
1380 PreparedStatement statement = null;
1381 Connection connection = obtainConnection();
1383 connection.setAutoCommit(false);
1385 statement = connection.prepareStatement(aQuery);
1386 statement.setBinaryStream(1, new ByteArrayInputStream(aData), aData.length);
1387 statement.execute();
1388 connection.commit();
1391 connection.setAutoCommit(true);
1395 freeConnection(connection, statement);
1399 private void logQueryBefore(String aQuery) {
1400 logger.debug("about to perform QUERY " + aQuery);
1401 // (new Throwable()).printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1404 private void logQueryAfter(String aQuery, long aTime) {
1405 logger.info("QUERY " + aQuery + " took " + aTime + "ms.");
1408 private void logQueryError(String aQuery, long aTime, Throwable anException) {
1409 logger.error("QUERY " + aQuery + " took " + aTime + "ms, but threw exception " + anException.toString());
1413 * a small wrapper class that allows us to store the DB connection resources
1414 * that the BlobInputStream is using and free them upon closing of the stream
1416 private class BinaryFieldInputStream extends InputStream {
1417 InputStream inputStream;
1418 Connection connection;
1419 Statement statement;
1421 public BinaryFieldInputStream(InputStream aBlobInputStream, Connection aConnection, Statement aStatement ) {
1422 inputStream = aBlobInputStream;
1423 connection = aConnection;
1424 statement = aStatement;
1427 public void close () throws IOException {
1428 inputStream.close();
1430 connection.setAutoCommit(true);
1431 freeConnection(connection, statement);
1433 catch (Exception e) {
1434 throw new IOException("close(): "+e.toString());
1438 public int read() throws IOException {
1439 return inputStream.read();