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.
57 public class Database implements StorageObject {
58 private static Class GENERIC_ENTITY_CLASS = mir.entity.StorableObjectEntity.class;
59 protected static final ObjectStore o_store = ObjectStore.getInstance();
60 private static final int _millisPerHour = 60 * 60 * 1000;
62 protected LoggerWrapper logger;
64 protected MirPropertiesConfiguration configuration;
65 protected String mainTable;
66 protected String primaryKeySequence = null;
67 protected String primaryKeyField = "id";
69 protected boolean evaluatedMetaData = false;
70 protected ArrayList metadataFields;
71 protected ArrayList metadataLabels;
72 protected ArrayList metadataNotNullFields;
73 protected int[] metadataTypes;
74 protected Class theEntityClass;
75 protected boolean hasTimestamp = true;
76 private int defaultLimit;
79 SimpleDateFormat internalDateFormat;
80 SimpleDateFormat userInputDateFormat;
83 * Kontruktor bekommt den Filenamen des Konfigurationsfiles ?bergeben.
84 * Aus diesem file werden <code>Database.Logfile</code>,
85 * <code>Database.Username</code>,<code>Database.Password</code>,
86 * <code>Database.Host</code> und <code>Database.Adaptor</code>
87 * ausgelesen und ein Broker f?r die Verbindugen zur Datenbank
90 public Database() throws StorageObjectFailure {
91 configuration = MirPropertiesConfiguration.instance();
92 logger = new LoggerWrapper("Database");
93 timezone = TimeZone.getTimeZone(configuration.getString("Mir.DefaultTimezone"));
94 internalDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
95 internalDateFormat.setTimeZone(timezone);
97 userInputDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
98 userInputDateFormat.setTimeZone(timezone);
100 String theAdaptorName = configuration.getString("Database.Adaptor");
101 defaultLimit = Integer.parseInt(configuration.getString("Database.Limit"));
104 theEntityClass = GENERIC_ENTITY_CLASS;
106 catch (Throwable e) {
107 logger.error("Error in Database() constructor with " + theAdaptorName + " -- " + e.getMessage());
108 throw new StorageObjectFailure("Error in Database() constructor.", e);
113 * Liefert die Entity-Klasse zur?ck, in der eine Datenbankzeile gewrappt
114 * wird. Wird die Entity-Klasse durch die erbende Klasse nicht ?berschrieben,
115 * wird eine mir.entity.GenericEntity erzeugt.
117 * @return Class-Objekt der Entity
119 public java.lang.Class getEntityClass() {
120 return theEntityClass;
124 * Liefert die Standardbeschr?nkung von select-Statements zur?ck, also
125 * wieviel Datens?tze per Default selektiert werden.
127 * @return Standard-Anzahl der Datens?tze
129 public int getLimit() {
134 * Liefert den Namen des Primary-Keys zur?ck. Wird die Variable nicht von
135 * der erbenden Klasse ?berschrieben, so ist der Wert <code>PKEY</code>
136 * @return Name des Primary-Keys
138 public String getIdName() {
139 return primaryKeyField;
143 * Liefert den Namen der Tabelle, auf das sich das Datenbankobjekt bezieht.
145 * @return Name der Tabelle
147 public String getTableName() {
152 * Returns the id that was most recently added to the database
154 private String getLatestInsertedId(Connection aConnection) throws SQLException {
155 if (primaryKeySequence==null)
156 primaryKeySequence = mainTable+"_id_seq";
158 PreparedStatement statement = aConnection.prepareStatement("select currval('" + primaryKeySequence + "')");
160 ResultSet rs = statement.executeQuery();
162 return rs.getString(1);
166 * Liefert eine Liste der Felder der Tabelle
167 * @return ArrayList mit Feldern
169 public List getFields() throws StorageObjectFailure {
170 if (metadataFields == null) {
174 return metadataFields;
178 * Gets value out of ResultSet according to type and converts to String
179 * @param rs ResultSet.
180 * @param aType a type from java.sql.Types.*
181 * @param valueIndex index in ResultSet
182 * @return returns the value as String. If no conversion is possible
183 * /unsupported value/ is returned
185 private String getValueAsString(ResultSet rs, int valueIndex, int aType)
186 throws StorageObjectFailure {
187 String outValue = null;
192 case java.sql.Types.BIT:
193 outValue = (rs.getBoolean(valueIndex) == true) ? "1" : "0";
197 case java.sql.Types.INTEGER:
198 case java.sql.Types.SMALLINT:
199 case java.sql.Types.TINYINT:
200 case java.sql.Types.BIGINT:
202 int out = rs.getInt(valueIndex);
205 outValue = new Integer(out).toString();
210 case java.sql.Types.NUMERIC:
212 /** todo Numeric can be float or double depending upon
213 * metadata.getScale() / especially with oracle */
214 long outl = rs.getLong(valueIndex);
217 outValue = new Long(outl).toString();
222 case java.sql.Types.REAL:
224 float tempf = rs.getFloat(valueIndex);
230 int tempf_int = (int) tempf;
231 tempf = (float) tempf_int;
233 outValue = "" + tempf;
234 outValue = outValue.replace('.', ',');
239 case java.sql.Types.DOUBLE:
241 double tempd = rs.getDouble(valueIndex);
247 int tempd_int = (int) tempd;
248 tempd = (double) tempd_int;
250 outValue = "" + tempd;
251 outValue = outValue.replace('.', ',');
256 case java.sql.Types.CHAR:
257 case java.sql.Types.VARCHAR:
258 case java.sql.Types.LONGVARCHAR:
259 outValue = rs.getString(valueIndex);
263 case java.sql.Types.LONGVARBINARY:
264 outValue = rs.getString(valueIndex);
268 case java.sql.Types.TIMESTAMP:
270 // it's important to use Timestamp here as getting it
271 // as a string is undefined and is only there for debugging
272 // according to the API. we can make it a string through formatting.
274 Timestamp timestamp = (rs.getTimestamp(valueIndex));
277 java.util.Date date = new java.util.Date(timestamp.getTime());
279 Calendar calendar = new GregorianCalendar();
280 calendar.setTime(date);
281 calendar.setTimeZone(timezone);
282 outValue = internalDateFormat.format(date);
284 int offset = calendar.get(Calendar.ZONE_OFFSET) + calendar.get(Calendar.DST_OFFSET);
285 String tzOffset = StringUtil.zeroPaddingNumber(Math.abs(offset) / _millisPerHour, 2, 2);
288 outValue = outValue + "-";
290 outValue = outValue + "+";
291 outValue = outValue + tzOffset;
297 outValue = "<unsupported value>";
298 logger.warn("Unsupported Datatype: at " + valueIndex + " (" + aType + ")");
300 } catch (SQLException e) {
301 throw new StorageObjectFailure("Could not get Value out of Resultset -- ",
310 * select-Operator um einen Datensatz zu bekommen.
311 * @param id Primaerschluessel des Datensatzes.
312 * @return liefert EntityObject des gefundenen Datensatzes oder null.
314 public Entity selectById(String id) throws StorageObjectExc {
315 if ((id == null) || id.equals("")) {
316 throw new StorageObjectExc("Database.selectById: Missing id");
319 // ask object store for object
320 if (StoreUtil.extendsStorableEntity(theEntityClass)) {
321 String uniqueId = id;
323 if (theEntityClass.equals(StorableObjectEntity.class)) {
324 uniqueId += ("@" + mainTable);
327 StoreIdentifier search_sid = new StoreIdentifier(theEntityClass, uniqueId);
328 logger.debug("CACHE: (dbg) looking for sid " + search_sid.toString());
330 Entity hit = (Entity) o_store.use(search_sid);
337 Statement stmt = null;
338 Connection con = obtainConnection();
339 Entity returnEntity = null;
344 /** todo better prepared statement */
346 "select * from " + mainTable + " where " + primaryKeyField + "=" + id;
347 stmt = con.createStatement();
348 rs = executeSql(stmt, selectSql);
351 if (evaluatedMetaData == false) {
352 evalMetaData(rs.getMetaData());
356 returnEntity = makeEntityFromResultSet(rs);
359 logger.warn("No data for id: " + id + " in table " + mainTable);
365 logger.warn("No Data for Id " + id + " in Table " + mainTable);
368 catch (SQLException sqe) {
369 throwSQLException(sqe, "selectById");
372 catch (NumberFormatException e) {
373 logger.error("ID is no number: " + id);
376 freeConnection(con, stmt);
383 * This method makes it possible to make selects across multiple tables
385 * @param mainTablePrefix prefix for the mainTable
386 * @param extraTables a vector of tables for relational select
387 * @param aWhereClause whereClause
388 * @return EntityList of selected Objects
389 * @throws StorageObjectFailure
392 public EntityList selectByWhereClauseWithExtraTables(String mainTablePrefix,
393 List extraTables, String aWhereClause )
394 throws StorageObjectFailure {
395 return selectByWhereClause( mainTablePrefix, extraTables, aWhereClause, "", 0, defaultLimit);
398 public EntityList selectByFieldValue(String aField, String aValue) throws StorageObjectFailure {
399 return selectByFieldValue(aField, aValue, 0);
402 public EntityList selectByFieldValue(String aField, String aValue, int offset) throws StorageObjectFailure {
403 return selectByWhereClause(aField + "='" + JDBCStringRoutines.escapeStringLiteral(aValue)+"'", offset);
407 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
408 * Also offset wird der erste Datensatz genommen.
410 * @param where where-Clause
411 * @return EntityList mit den gematchten Entities
412 * @exception StorageObjectFailure
414 public EntityList selectByWhereClause(String where) throws StorageObjectFailure {
415 return selectByWhereClause(where, 0);
419 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
420 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
422 * @param whereClause where-Clause
423 * @param offset ab welchem Datensatz.
424 * @return EntityList mit den gematchten Entities
425 * @exception StorageObjectFailure
427 public EntityList selectByWhereClause(String whereClause, int offset) throws StorageObjectFailure {
428 return selectByWhereClause(whereClause, null, offset);
432 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
433 * Also offset wird der erste Datensatz genommen.
434 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
436 * @param where where-Clause
437 * @param order orderBy-Clause
438 * @return EntityList mit den gematchten Entities
439 * @exception StorageObjectFailure
441 public EntityList selectByWhereClause(String where, String order) throws StorageObjectFailure {
442 return selectByWhereClause(where, order, 0);
445 public EntityList selectByWhereClause(String mainTablePrefix, List extraTables, String where, String order) throws StorageObjectFailure {
446 return selectByWhereClause(mainTablePrefix, extraTables, where, order, 0, defaultLimit);
450 * select-Operator liefert eine EntityListe mit den gematchten Datens?tzen zur?ck.
451 * Als maximale Anzahl wird das Limit auf der Konfiguration genommen.
453 * @param whereClause where-Clause
454 * @param orderBy orderBy-Clause
455 * @param offset ab welchem Datensatz
456 * @return EntityList mit den gematchten Entities
457 * @exception StorageObjectFailure
459 public EntityList selectByWhereClause(String whereClause, String orderBy, int offset) throws StorageObjectFailure {
460 return selectByWhereClause(whereClause, orderBy, offset, defaultLimit);
464 * select-Operator returns EntityList with matching rows in Database.
465 * @param aWhereClause where-Clause
466 * @param anOrderByClause orderBy-Clause
467 * @param offset ab welchem Datensatz
468 * @param limit wieviele Datens?tze
469 * @return EntityList mit den gematchten Entities
470 * @exception StorageObjectFailure
472 public EntityList selectByWhereClause(String aWhereClause, String anOrderByClause,
473 int offset, int limit) throws StorageObjectFailure {
474 return selectByWhereClause("", null, aWhereClause, anOrderByClause, offset, limit);
479 * select-Operator returns EntityList with matching rows in Database.
480 * @param aWhereClause where-Clause
481 * @param anOrderByClause orderBy-Clause
482 * @param offset ab welchem Datensatz
483 * @param limit wieviele Datens?tze
484 * @return EntityList mit den gematchten Entities
485 * @exception StorageObjectFailure
487 public EntityList selectByWhereClause(String mainTablePrefix, List extraTables,
488 String aWhereClause, String anOrderByClause,
489 int offset, int limit) throws StorageObjectFailure {
491 // TODO get rid of emtpy Strings in extraTables
492 // make extraTables null, if single empty String in it
493 // cause StringUtil.splitString puts in emptyString
494 if (extraTables != null && ((String)extraTables.get(0)).trim().equals(""))
496 logger.debug("+++ made extraTables to null!");
500 String useTable = mainTable;
501 String selectStar = "*";
502 if (mainTablePrefix!=null && mainTablePrefix.trim().length()>0) {
503 useTable+=" "+mainTablePrefix;
504 selectStar=mainTablePrefix.trim() + ".*";
507 // check o_store for entitylist
508 // only if no relational select
509 if (extraTables==null) {
510 if (StoreUtil.extendsStorableEntity(theEntityClass)) {
511 StoreIdentifier searchSid = new StoreIdentifier(theEntityClass,
512 StoreContainerType.STOC_TYPE_ENTITYLIST,
513 StoreUtil.getEntityListUniqueIdentifierFor(mainTable,
514 aWhereClause, anOrderByClause, offset, limit));
515 EntityList hit = (EntityList) o_store.use(searchSid);
524 EntityList theReturnList = null;
525 Connection con = null;
526 Statement stmt = null;
531 // build sql-statement
533 if ((aWhereClause != null) && (aWhereClause.trim().length() == 0)) {
537 StringBuffer countSql =
538 new StringBuffer("select count(*) from ").append(useTable);
539 StringBuffer selectSql =
540 new StringBuffer("select "+selectStar+" from ").append(useTable);
542 // append extratables, if necessary
543 if (extraTables!=null) {
544 for (int i=0;i < extraTables.size();i++) {
545 if (!extraTables.get(i).equals("")) {
546 countSql.append( ", " + extraTables.get(i));
547 selectSql.append( ", " + extraTables.get(i));
552 if (aWhereClause != null) {
553 selectSql.append(" where ").append(aWhereClause);
554 countSql.append(" where ").append(aWhereClause);
557 if ((anOrderByClause != null) && !(anOrderByClause.trim().length() == 0)) {
558 selectSql.append(" order by ").append(anOrderByClause);
561 if ((limit > -1) && (offset > -1)) {
562 selectSql.append(" LIMIT ").append(limit).append(" OFFSET ").append(offset);
567 con = obtainConnection();
568 stmt = con.createStatement();
571 rs = executeSql(stmt, selectSql.toString());
574 if (!evaluatedMetaData) {
575 evalMetaData(rs.getMetaData());
578 theReturnList = new EntityList();
579 Entity theResultEntity;
581 theResultEntity = makeEntityFromResultSet(rs);
582 theReturnList.add(theResultEntity);
588 // making entitylist infos
591 if (theReturnList != null) {
592 // now we decide if we have to know an overall count...
595 if ((limit > -1) && (offset > -1)) {
596 if (offsetCount == limit) {
597 rs = executeSql(stmt, countSql.toString());
601 count = rs.getInt(1);
607 logger.error("Could not count: " + countSql);
612 theReturnList.setCount(count);
613 theReturnList.setOffset(offset);
614 theReturnList.setWhere(aWhereClause);
615 theReturnList.setOrder(anOrderByClause);
616 theReturnList.setStorage(this);
617 theReturnList.setLimit(limit);
619 if (offset >= limit) {
620 theReturnList.setPrevBatch(offset - limit);
623 if ((offset + offsetCount) < count) {
624 theReturnList.setNextBatch(offset + limit);
627 if (extraTables==null && StoreUtil.extendsStorableEntity(theEntityClass)) {
628 StoreIdentifier sid = theReturnList.getStoreIdentifier();
629 logger.debug("CACHE (add): " + sid.toString());
634 catch (SQLException sqe) {
635 throwSQLException(sqe, "selectByWhereClause");
640 freeConnection(con, stmt);
642 } catch (Throwable t) {
646 return theReturnList;
650 * Bastelt aus einer Zeile der Datenbank ein EntityObjekt.
652 * @param rs Das ResultSetObjekt.
653 * @return Entity Die Entity.
655 private Entity makeEntityFromResultSet(ResultSet rs)
656 throws StorageObjectFailure {
657 Map theResultHash = new HashMap();
658 String theResult = null;
660 Entity returnEntity = null;
663 if (StoreUtil.extendsStorableEntity(theEntityClass)) {
664 StoreIdentifier searchSid = StorableObjectEntity.getStoreIdentifier(this,
666 Entity hit = (Entity) o_store.use(searchSid);
667 if (hit != null) return hit;
671 int size = metadataFields.size();
673 for (int i = 0; i < size; i++) {
674 // alle durchlaufen bis nix mehr da
675 theType = metadataTypes[i];
677 if (theType == java.sql.Types.LONGVARBINARY) {
678 InputStreamReader is =
679 (InputStreamReader) rs.getCharacterStream(i + 1);
682 char[] data = new char[32768];
683 StringBuffer theResultString = new StringBuffer();
686 while ((len = is.read(data)) > 0) {
687 theResultString.append(data, 0, len);
691 theResult = theResultString.toString();
696 theResult = getValueAsString(rs, (i + 1), theType);
699 if (theResult != null) {
700 theResultHash.put(metadataFields.get(i), theResult);
704 if (theEntityClass != null) {
705 returnEntity = (Entity) theEntityClass.newInstance();
706 returnEntity.setStorage(this);
707 returnEntity.setFieldValues(theResultHash);
709 if (returnEntity instanceof StorableObject) {
710 logger.debug("CACHE: ( in) " + returnEntity.getId() + " :" + mainTable);
711 o_store.add(((StorableObject) returnEntity).getStoreIdentifier());
714 throwStorageObjectException("Internal Error: theEntityClass not set!");
717 catch (IllegalAccessException e) {
718 throwStorageObjectException("No access! -- " + e.getMessage());
720 catch (IOException e) {
721 throwStorageObjectException("IOException! -- " + e.getMessage());
723 catch (InstantiationException e) {
724 throwStorageObjectException("No Instatiation! -- " + e.getMessage());
726 catch (SQLException sqe) {
727 throwSQLException(sqe, "makeEntityFromResultSet");
736 * Inserts an entity into the database.
739 * @return der Wert des Primary-keys der eingef?gten Entity
741 public String insert(Entity theEntity) throws StorageObjectFailure {
744 String returnId = null;
745 Connection con = null;
746 PreparedStatement pstmt = null;
749 StringBuffer f = new StringBuffer();
750 StringBuffer v = new StringBuffer();
753 boolean firstField = true;
756 for (int i = 0; i < getFields().size(); i++) {
757 aField = (String) getFields().get(i);
759 if (!aField.equals(primaryKeyField)) {
763 if (!theEntity.hasFieldValue(aField) && (
764 aField.equals("webdb_create") ||
765 aField.equals("webdb_lastchange"))) {
769 if (theEntity.hasFieldValue(aField)) {
772 JDBCStringRoutines.escapeStringLiteral(theEntity.getFieldValue(aField)) + "'";
776 // wenn Wert gegeben, dann einbauen
777 if (aValue != null) {
778 if (firstField == false) {
794 StringBuffer sqlBuf =
795 new StringBuffer("insert into ").append(mainTable).append("(").append(f)
796 .append(") values (").append(v).append(")");
797 String sql = sqlBuf.toString();
799 logger.info("INSERT: " + sql);
800 con = obtainConnection();
801 con.setAutoCommit(false);
802 pstmt = con.prepareStatement(sql);
804 int ret = pstmt.executeUpdate();
811 // pstmt = con.prepareStatement("select currval('" + + "_id_seq')");
813 returnId = getLatestInsertedId(con);
814 theEntity.setId(returnId);
816 catch (SQLException sqe) {
817 throwSQLException(sqe, "insert");
821 con.setAutoCommit(true);
823 catch (Exception e) {
826 freeConnection(con, pstmt);
829 /** todo store entity in o_store */
834 * Updates an entity in the database
838 public void update(Entity theEntity) throws StorageObjectFailure {
839 Connection con = null;
840 PreparedStatement pstmt = null;
842 /** todo this is stupid: why do we prepare statement, when we
843 * throw it away afterwards. should be regular statement
844 * update/insert could better be one routine called save()
845 * that chooses to either insert or update depending if we
846 * have a primary key in the entity. i don't know if we
847 * still need the streamed input fields. // rk */
849 /** todo extension: check if Entity did change, otherwise we don't need
850 * the roundtrip to the database */
851 /** invalidating corresponding entitylists in o_store*/
855 String id = theEntity.getId();
857 StringBuffer fv = new StringBuffer();
858 boolean firstField = true;
860 // build sql statement
861 for (int i = 0; i < getFields().size(); i++) {
862 aField = (String) metadataFields.get(i);
865 // todo if entity.hasFieldValue returns false, then the value should be stored as null
866 if (!(aField.equals(primaryKeyField) ||
867 aField.equals("webdb_create") ||
868 aField.equals("webdb_lastchange"))) {
869 if (theEntity.hasFieldValue(aField)) {
870 if (firstField == false) {
877 fv.append(aField).append("='").append(JDBCStringRoutines.escapeStringLiteral(theEntity.getFieldValue(aField))).append("'");
879 // fv.append(aField).append("='").append(StringUtil.quote((String)theEntity.getFieldValue(aField))).append("'");
885 new StringBuffer("update ").append(mainTable).append(" set ").append(fv);
888 if (metadataFields.contains("webdb_lastchange")) {
889 sql.append(",webdb_lastchange=NOW()");
892 // special case: the webdb_create requires the field in yyyy-mm-dd HH:mm
893 // format so anything extra will be ignored. -mh
894 if (metadataFields.contains("webdb_create") &&
895 theEntity.hasFieldValue("webdb_create")) {
896 // minimum of 10 (yyyy-mm-dd)...
897 if (theEntity.getFieldValue("webdb_create").length() >= 10) {
898 String dateString = theEntity.getFieldValue("webdb_create");
900 // if only 10, then add 00:00 so it doesn't throw a ParseException
901 if (dateString.length() == 10) {
902 dateString = dateString + " 00:00";
907 java.util.Date d = userInputDateFormat.parse(dateString);
908 // Timestamp tStamp = new Timestamp(d.getTime());
909 sql.append(",webdb_create='" + JDBCStringRoutines.formatDate(d) + "'");
911 catch (ParseException e) {
912 throw new StorageObjectFailure(e);
917 sql.append(" where id=").append(id);
918 logger.info("UPDATE: " + sql);
921 con = obtainConnection();
922 con.setAutoCommit(false);
923 pstmt = con.prepareStatement(sql.toString());
925 pstmt.executeUpdate();
927 catch (SQLException sqe) {
928 throwSQLException(sqe, "update");
932 con.setAutoCommit(true);
934 catch (Exception e) {
938 freeConnection(con, pstmt);
944 * @param id des zu loeschenden Datensatzes
945 * @return boolean liefert true zurueck, wenn loeschen erfolgreich war.
947 public boolean delete(String id) throws StorageObjectFailure {
948 // ostore send notification
949 if (StoreUtil.extendsStorableEntity(theEntityClass)) {
950 String uniqueId = id;
952 if (theEntityClass.equals(StorableObjectEntity.class)) {
953 uniqueId += ("@" + mainTable);
956 logger.debug("CACHE: (del) " + id);
958 StoreIdentifier search_sid =
959 new StoreIdentifier(theEntityClass,
960 StoreContainerType.STOC_TYPE_ENTITY, uniqueId);
961 o_store.invalidate(search_sid);
964 /** todo could be prepared Statement */
965 Statement stmt = null;
966 Connection con = null;
969 "delete from " + mainTable + " where " + primaryKeyField + "='" + id + "'";
971 logger.debug("DELETE " + sql);
973 con = obtainConnection();
974 stmt = con.createStatement();
975 res = stmt.executeUpdate(sql);
977 catch (SQLException sqe) {
978 throwSQLException(sqe, "delete");
981 freeConnection(con, stmt);
986 return (res > 0) ? true : false;
990 * Deletes entities based on a where clause
992 * @param aWhereClause
994 * @throws StorageObjectFailure
996 public int deleteByWhereClause(String aWhereClause) throws StorageObjectFailure {
999 Statement stmt = null;
1000 Connection con = null;
1003 "delete from " + mainTable + " where " + aWhereClause;
1005 //theLog.printInfo("DELETE " + sql);
1007 con = obtainConnection();
1008 stmt = con.createStatement();
1009 res = stmt.executeUpdate(sql);
1011 catch (SQLException sqe) {
1012 throwSQLException(sqe, "delete");
1015 freeConnection(con, stmt);
1021 /* noch nicht implementiert.
1022 * @return immer false
1024 public boolean delete(EntityList theEntityList) {
1029 * Diese Methode fuehrt den Sqlstring <i>sql</i> aus und timed im Logfile.
1030 * @param stmt Statemnt
1031 * @param sql Sql-String
1033 public ResultSet executeSql(Statement stmt, String sql)
1034 throws StorageObjectFailure, SQLException {
1036 long startTime = System.currentTimeMillis();
1039 rs = stmt.executeQuery(sql);
1041 logger.info((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1043 catch (SQLException e) {
1044 logger.error(e.getMessage() +"\n" + (System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1051 private Map processRow(ResultSet aResultSet) throws StorageObjectFailure {
1053 Map result = new HashMap();
1054 ResultSetMetaData metaData = aResultSet.getMetaData();
1055 int nrColumns = metaData.getColumnCount();
1056 for (int i=0; i<nrColumns; i++) {
1057 result.put(metaData.getColumnName(i+1), getValueAsString(aResultSet, i+1, metaData.getColumnType(i+1)));
1062 catch (Throwable e) {
1063 throw new StorageObjectFailure(e);
1067 public List executeFreeSql(String sql, int aLimit) throws StorageObjectFailure, StorageObjectExc {
1068 Connection connection = null;
1069 Statement statement = null;
1071 List result = new ArrayList();
1072 connection = obtainConnection();
1073 statement = connection.createStatement();
1074 ResultSet resultset = executeSql(statement, sql);
1076 while (resultset.next() && result.size() < aLimit) {
1077 result.add(processRow(resultset));
1086 catch (Throwable e) {
1087 throw new StorageObjectFailure(e);
1090 if (connection!=null) {
1091 freeConnection(connection, statement);
1096 public Map executeFreeSingleRowSql(String anSqlStatement) throws StorageObjectFailure, StorageObjectExc {
1098 List resultList = executeFreeSql(anSqlStatement, 1);
1100 if (resultList.size()>0)
1101 return (Map) resultList.get(0);
1108 catch (Throwable t) {
1109 throw new StorageObjectFailure(t);
1113 public String executeFreeSingleValueSql(String sql) throws StorageObjectFailure, StorageObjectExc {
1114 Map row = executeFreeSingleRowSql(sql);
1119 Iterator i = row.values().iterator();
1121 return (String) i.next();
1126 public int getSize(String where) throws SQLException, StorageObjectFailure {
1127 return getSize("", null, where);
1130 * returns the number of rows in the table
1132 public int getSize(String mainTablePrefix, List extraTables, String where) throws SQLException, StorageObjectFailure {
1134 long startTime = System.currentTimeMillis();
1136 String useTable = mainTable;
1137 if (mainTablePrefix!=null && mainTablePrefix.trim().length()>0) {
1138 useTable+=" "+mainTablePrefix;
1140 StringBuffer countSql =
1141 new StringBuffer("select count(*) from ").append(useTable);
1142 // append extratables, if necessary
1143 if (extraTables!=null) {
1144 for (int i=0;i < extraTables.size();i++) {
1145 if (!extraTables.get(i).equals("")) {
1146 countSql.append( ", " + extraTables.get(i));
1151 if ((where != null) && (where.length() != 0)) {
1152 countSql.append( " where " + where);
1155 Connection con = null;
1156 Statement stmt = null;
1160 con = obtainConnection();
1161 stmt = con.createStatement();
1163 ResultSet rs = executeSql(stmt, countSql.toString());
1166 result = rs.getInt(1);
1169 catch (SQLException e) {
1170 logger.error("Database.getSize: " + e.getMessage());
1173 freeConnection(con, stmt);
1175 logger.info((System.currentTimeMillis() - startTime) + "ms. for: " + countSql);
1180 public int executeUpdate(Statement stmt, String sql)
1181 throws StorageObjectFailure, SQLException {
1183 long startTime = System.currentTimeMillis();
1186 rs = stmt.executeUpdate(sql);
1188 logger.info((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1190 catch (SQLException e) {
1191 logger.error("Failed: " + (System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1198 public int executeUpdate(String sql)
1199 throws StorageObjectFailure, SQLException {
1201 long startTime = System.currentTimeMillis();
1202 Connection con = null;
1203 PreparedStatement pstmt = null;
1206 con = obtainConnection();
1207 pstmt = con.prepareStatement(sql);
1208 result = pstmt.executeUpdate();
1210 catch (Throwable e) {
1211 logger.error("Database.executeUpdate(" + sql + "): " + e.getMessage());
1212 throw new StorageObjectFailure("Database.executeUpdate(" + sql + "): " + e.getMessage(), e);
1215 freeConnection(con, pstmt);
1218 logger.info((System.currentTimeMillis() - startTime) + "ms. for: " + sql);
1223 * Wertet ResultSetMetaData aus und setzt interne Daten entsprechend
1224 * @param md ResultSetMetaData
1226 private void evalMetaData(ResultSetMetaData md) throws StorageObjectFailure {
1227 this.evaluatedMetaData = true;
1228 this.metadataFields = new ArrayList();
1229 this.metadataLabels = new ArrayList();
1230 this.metadataNotNullFields = new ArrayList();
1233 int numFields = md.getColumnCount();
1234 this.metadataTypes = new int[numFields];
1239 for (int i = 1; i <= numFields; i++) {
1240 aField = md.getColumnName(i);
1241 metadataFields.add(aField);
1242 metadataLabels.add(md.getColumnLabel(i));
1243 aType = md.getColumnType(i);
1244 metadataTypes[i - 1] = aType;
1246 if (aField.equals(primaryKeyField)) {
1249 if (md.isNullable(i) == ResultSetMetaData.columnNullable) {
1250 metadataNotNullFields.add(aField);
1254 catch (SQLException e) {
1255 throwSQLException(e, "evalMetaData");
1260 * Wertet die Metadaten eines Resultsets fuer eine Tabelle aus,
1261 * um die alle Columns und Typen einer Tabelle zu ermitteln.
1263 private void get_meta_data() throws StorageObjectFailure {
1264 Connection con = null;
1265 PreparedStatement pstmt = null;
1266 String sql = "select * from " + mainTable + " where 0=1";
1269 con = obtainConnection();
1270 pstmt = con.prepareStatement(sql);
1272 logger.debug("METADATA: " + sql);
1273 ResultSet rs = pstmt.executeQuery();
1274 evalMetaData(rs.getMetaData());
1277 catch (SQLException e) {
1278 throwSQLException(e, "get_meta_data");
1281 freeConnection(con, pstmt);
1285 public Connection obtainConnection() throws StorageObjectFailure {
1287 return MirGlobal.getDatabaseEngine().obtainConnection();
1289 catch (Exception e) {
1290 throw new StorageObjectFailure(e);
1294 public void freeConnection(Connection aConnection, Statement aStatement) throws StorageObjectFailure {
1298 catch (Throwable t) {
1299 logger.warn("Can't close statemnet: " + t.toString());
1303 MirGlobal.getDatabaseEngine().releaseConnection(aConnection);
1305 catch (Throwable t) {
1306 logger.warn("Can't release connection: " + t.toString());
1311 * Wertet SQLException aus und wirft dannach eine StorageObjectException
1312 * @param sqe SQLException
1313 * @param aFunction Funktonsname, in der die SQLException geworfen wurde
1315 protected void throwSQLException(SQLException sqe, String aFunction) throws StorageObjectFailure {
1317 String message = "";
1321 state = sqe.getSQLState();
1322 message = sqe.getMessage();
1323 vendor = sqe.getErrorCode();
1326 String information =
1329 ", vendor= " + vendor +
1330 ", message=" + message +
1331 ", function= " + aFunction;
1333 logger.error(information);
1335 throw new StorageObjectFailure(information, sqe);
1338 protected void _throwStorageObjectException(Exception e, String aFunction)
1339 throws StorageObjectFailure {
1342 logger.error(e.getMessage() + aFunction);
1343 throw new StorageObjectFailure(aFunction, e);
1348 * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach
1349 * eine StorageObjectException
1350 * @param aMessage Nachricht mit dem Fehler
1351 * @exception StorageObjectFailure
1353 void throwStorageObjectException(String aMessage) throws StorageObjectFailure {
1354 logger.error(aMessage);
1355 throw new StorageObjectFailure(aMessage, null);
1359 * Invalidates any cached entity list
1361 private void invalidateStore() {
1362 // invalidating all EntityLists corresponding with theEntityClass
1363 if (StoreUtil.extendsStorableEntity(theEntityClass)) {
1364 StoreContainerType stoc_type =
1365 StoreContainerType.valueOf(theEntityClass, StoreContainerType.STOC_TYPE_ENTITYLIST);
1366 o_store.invalidate(stoc_type);
1371 * Retrieves a binary value
1373 public InputStream getBinaryField(String aQuery) throws StorageObjectFailure, SQLException {
1374 Connection connection=null;
1375 Statement statement=null;
1376 InputStream inputStream;
1377 InputStream imageInputStream = null;
1380 connection = obtainConnection();
1382 connection.setAutoCommit(false);
1383 statement = connection.createStatement();
1384 ResultSet resultSet = executeSql(statement, aQuery);
1386 if(resultSet!=null) {
1387 if (resultSet.next()) {
1388 inputStream = resultSet.getBlob(1).getBinaryStream();
1389 imageInputStream = new BinaryFieldInputStream(inputStream, connection, statement);
1397 catch (Throwable t) {
1398 logger.error("EntityImages.getImage failed: " + t.toString());
1399 t.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1402 connection.setAutoCommit(true);
1404 catch (Throwable e) {
1405 logger.error("EntityImages.getImage resetting transaction mode failed: " + e.toString());
1406 e.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1410 freeConnection(connection, statement);
1412 catch (Throwable e) {
1413 logger.error("EntityImages.getImage freeing connection failed: " +e.toString());
1416 throw new StorageObjectFailure(t);
1419 return imageInputStream;
1423 * Sets a binary value. The query is supposed to contain 1 ? denoting where the
1424 * binary value should be inserted.
1426 * e.g. <code>update images set image_data = ? where id= 22</code>
1428 public void setBinaryField(String aQuery, byte aData[]) throws StorageObjectFailure, SQLException {
1429 PreparedStatement statement = null;
1430 Connection connection = obtainConnection();
1432 connection.setAutoCommit(false);
1434 statement = connection.prepareStatement(aQuery);
1435 statement.setBinaryStream(1, new ByteArrayInputStream(aData), aData.length);
1436 statement.execute();
1437 connection.commit();
1440 connection.setAutoCommit(true);
1444 freeConnection(connection, statement);
1449 * a small wrapper class that allows us to store the DB connection resources
1450 * that the BlobInputStream is using and free them upon closing of the stream
1452 private class BinaryFieldInputStream extends InputStream {
1453 InputStream inputStream;
1454 Connection connection;
1455 Statement statement;
1457 public BinaryFieldInputStream(InputStream aBlobInputStream, Connection aConnection, Statement aStatement ) {
1458 inputStream = aBlobInputStream;
1459 connection = aConnection;
1460 statement = aStatement;
1463 public void close () throws IOException {
1464 inputStream.close();
1466 connection.setAutoCommit(true);
1467 freeConnection(connection, statement);
1469 catch (Exception e) {
1470 throw new IOException("close(): "+e.toString());
1474 public int read() throws IOException {
1475 return inputStream.read();