2 * Copyright (C) 2001-2005 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.AbstractEntity;
34 import mir.entity.Entity;
35 import mir.entity.EntityList;
36 import mir.entity.StorableObjectEntity;
37 import mir.log.LoggerWrapper;
38 import mir.misc.StringUtil;
39 import mir.storage.store.*;
40 import mir.util.JDBCStringRoutines;
41 import mir.util.StreamCopier;
42 import mircoders.global.MirGlobal;
43 import org.apache.commons.dbcp.DelegatingConnection;
44 import org.postgresql.PGConnection;
45 import org.postgresql.largeobject.LargeObject;
46 import org.postgresql.largeobject.LargeObjectManager;
50 import java.text.ParseException;
51 import java.text.SimpleDateFormat;
55 * Implements database access.
57 * @version $Id: Database.java,v 1.44.2.31 2005/04/16 18:27:31 zapata Exp $
62 public class Database {
63 private static int DEFAULT_LIMIT = 20;
64 private static Class GENERIC_ENTITY_CLASS = mir.entity.StorableObjectEntity.class;
65 protected static final ObjectStore o_store = ObjectStore.getInstance();
66 private static final int _millisPerHour = 60 * 60 * 1000;
68 protected LoggerWrapper logger;
70 protected String mainTable;
71 protected String primaryKeyField = "id";
73 protected List fieldNames;
74 private int[] fieldTypes;
75 private Map fieldNameToType;
77 protected Class entityClass;
80 private Set binaryFields;
83 SimpleDateFormat internalDateFormat;
84 SimpleDateFormat userInputDateFormat;
86 public Database() throws DatabaseFailure {
87 MirPropertiesConfiguration configuration = MirPropertiesConfiguration.instance();
88 logger = new LoggerWrapper("Database");
89 timezone = TimeZone.getTimeZone(configuration.getString("Mir.DefaultTimezone"));
90 internalDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
91 internalDateFormat.setTimeZone(timezone);
93 userInputDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
94 userInputDateFormat.setTimeZone(timezone);
96 binaryFields = new HashSet();
98 String theAdaptorName = configuration.getString("Database.Adaptor");
101 entityClass = GENERIC_ENTITY_CLASS;
103 catch (Throwable e) {
104 logger.error("Error in Database() constructor with " + theAdaptorName + " -- " + e.getMessage());
105 throw new DatabaseFailure("Error in Database() constructor.", e);
109 public Class getEntityClass() {
113 public Entity createNewEntity() throws DatabaseFailure {
115 AbstractEntity result = (AbstractEntity) entityClass.newInstance();
116 result.setStorage(this);
120 catch (Throwable t) {
121 throw new DatabaseFailure(t);
125 public String getIdFieldName() {
126 return primaryKeyField;
129 public String getTableName() {
134 * Returns a list of field names for this <code>Database</code>
136 public List getFieldNames() throws DatabaseFailure {
137 if (fieldNames == null) {
144 public boolean hasField(String aFieldName) {
145 return getFieldNames().contains(aFieldName);
149 * Gets value out of ResultSet according to type and converts to String
151 * @param aResultSet ResultSet.
152 * @param aType a type from java.sql.Types.*
153 * @param aFieldIndex index in ResultSet
154 * @return returns the value as String. If no conversion is possible
155 * /unsupported value/ is returned
157 private String getValueAsString(ResultSet aResultSet, int aFieldIndex, int aType)
158 throws DatabaseFailure {
159 String outValue = null;
161 if (aResultSet != null) {
164 case java.sql.Types.BIT:
165 outValue = (aResultSet.getBoolean(aFieldIndex) == true) ? "1" : "0";
169 case java.sql.Types.INTEGER:
170 case java.sql.Types.SMALLINT:
171 case java.sql.Types.TINYINT:
172 case java.sql.Types.BIGINT:
174 int out = aResultSet.getInt(aFieldIndex);
176 if (!aResultSet.wasNull()) {
177 outValue = new Integer(out).toString();
182 case java.sql.Types.NUMERIC:
183 /** todo Numeric can be float or double depending upon
184 * metadata.getScale() / especially with oracle */
185 long outl = aResultSet.getLong(aFieldIndex);
187 if (!aResultSet.wasNull()) {
188 outValue = new Long(outl).toString();
193 case java.sql.Types.REAL:
195 float tempf = aResultSet.getFloat(aFieldIndex);
197 if (!aResultSet.wasNull()) {
201 int tempf_int = (int) tempf;
202 tempf = (float) tempf_int;
204 outValue = "" + tempf;
205 outValue = outValue.replace('.', ',');
210 case java.sql.Types.DOUBLE:
212 double tempd = aResultSet.getDouble(aFieldIndex);
214 if (!aResultSet.wasNull()) {
218 int tempd_int = (int) tempd;
219 tempd = (double) tempd_int;
221 outValue = "" + tempd;
222 outValue = outValue.replace('.', ',');
227 case java.sql.Types.CHAR:
228 case java.sql.Types.VARCHAR:
229 case java.sql.Types.LONGVARCHAR:
230 outValue = aResultSet.getString(aFieldIndex);
234 case java.sql.Types.LONGVARBINARY:
235 outValue = aResultSet.getString(aFieldIndex);
239 case java.sql.Types.TIMESTAMP:
241 // it's important to use Timestamp here as getting it
242 // as a string is undefined and is only there for debugging
243 // according to the API. we can make it a string through formatting.
245 Timestamp timestamp = (aResultSet.getTimestamp(aFieldIndex));
247 if (!aResultSet.wasNull()) {
248 java.util.Date date = new java.util.Date(timestamp.getTime());
250 Calendar calendar = new GregorianCalendar();
251 calendar.setTime(date);
252 calendar.setTimeZone(timezone);
253 outValue = internalDateFormat.format(date);
255 int offset = calendar.get(Calendar.ZONE_OFFSET) + calendar.get(Calendar.DST_OFFSET);
256 String tzOffset = StringUtil.zeroPaddingNumber(Math.abs(offset) / _millisPerHour, 2, 2);
259 outValue = outValue + "-";
261 outValue = outValue + "+";
262 outValue = outValue + tzOffset;
268 outValue = "<unsupported value>";
269 logger.warn("Unsupported Datatype: at " + aFieldIndex + " (" + aType + ")");
271 } catch (SQLException e) {
272 throw new DatabaseFailure("Could not get Value out of Resultset -- ",
281 * Return an entity specified by id
283 public Entity selectById(String anId) throws DatabaseExc {
284 if ((anId == null) || anId.equals("")) {
285 throw new DatabaseExc("Database.selectById: Missing id");
288 // ask object store for object
289 if (StoreUtil.extendsStorableEntity(entityClass)) {
290 String uniqueId = anId;
292 if (entityClass.equals(StorableObjectEntity.class)) {
293 uniqueId += ("@" + mainTable);
296 StoreIdentifier search_sid = new StoreIdentifier(entityClass, uniqueId);
297 logger.debug("CACHE: (dbg) looking for sid " + search_sid.toString());
299 Entity hit = (Entity) o_store.use(search_sid);
306 Connection con = obtainConnection();
307 Entity returnEntity = null;
308 PreparedStatement statement = null;
312 String query = "select * from " + mainTable + " where " + primaryKeyField + " = ?";
314 statement = con.prepareStatement(query);
315 statement.setString(1, anId);
317 logQueryBefore(query);
319 long startTime = System.currentTimeMillis();
321 rs = statement.executeQuery();
323 logQueryAfter(query, (System.currentTimeMillis() - startTime));
325 catch (SQLException e) {
326 logQueryError(query, (System.currentTimeMillis() - startTime), e);
332 returnEntity = makeEntityFromResultSet(rs);
335 logger.warn("No data for id: " + anId + " in table " + mainTable);
341 logger.warn("No Data for Id " + anId + " in Table " + mainTable);
344 catch (Throwable e) {
345 throw new DatabaseFailure(e);
348 freeConnection(con, statement);
354 public EntityList selectByWhereClauseWithExtraTables(String mainTablePrefix, List extraTables, String aWhereClause) throws DatabaseExc, DatabaseFailure {
355 return selectByWhereClause( mainTablePrefix, extraTables, aWhereClause, "", 0, DEFAULT_LIMIT);
358 public EntityList selectByFieldValue(String aField, String aValue) throws DatabaseExc, DatabaseFailure {
359 return selectByFieldValue(aField, aValue, 0);
362 public EntityList selectByFieldValue(String aField, String aValue, int offset) throws DatabaseExc, DatabaseFailure {
363 return selectByWhereClause(aField + "='" + JDBCStringRoutines.escapeStringLiteral(aValue)+"'", offset);
366 public EntityList selectByWhereClause(String where) throws DatabaseExc, DatabaseFailure {
367 return selectByWhereClause(where, 0);
370 public EntityList selectByWhereClause(String whereClause, int offset) throws DatabaseExc, DatabaseFailure {
371 return selectByWhereClause(whereClause, null, offset);
374 public EntityList selectByWhereClause(String mainTablePrefix, List extraTables, String where, String order) throws DatabaseExc, DatabaseFailure {
375 return selectByWhereClause(mainTablePrefix, extraTables, where, order, 0, DEFAULT_LIMIT);
378 public EntityList selectByWhereClause(String whereClause, String orderBy, int offset) throws DatabaseExc, DatabaseFailure {
379 return selectByWhereClause(whereClause, orderBy, offset, DEFAULT_LIMIT);
382 public EntityList selectByWhereClause(String aWhereClause, String anOrderByClause,
383 int offset, int limit) throws DatabaseExc, DatabaseFailure {
384 return selectByWhereClause("", null, aWhereClause, anOrderByClause, offset, limit);
387 public EntityList selectByWhereClause(
388 String aMainTablePrefix, List anExtraTables,
389 String aWhereClause, String anOrderByClause,
390 int anOffset, int aLimit) throws DatabaseExc, DatabaseFailure {
392 if (anExtraTables!=null && ((String) anExtraTables.get(0)).trim().equals("")){
396 RecordRetriever retriever = new RecordRetriever(mainTable, aMainTablePrefix);
398 // check o_store for entitylist
399 // only if no relational select
400 if (anExtraTables==null) {
401 if (StoreUtil.extendsStorableEntity(entityClass)) {
402 StoreIdentifier searchSid = new StoreIdentifier(entityClass,
403 StoreContainerType.STOC_TYPE_ENTITYLIST,
404 StoreUtil.getEntityListUniqueIdentifierFor(mainTable,
405 aWhereClause, anOrderByClause, anOffset, aLimit));
406 EntityList hit = (EntityList) o_store.use(searchSid);
414 EntityList result = null;
415 Connection connection = null;
417 if (anExtraTables!=null) {
418 Iterator i = anExtraTables.iterator();
419 while (i.hasNext()) {
420 String table = (String) i.next();
421 if (!"".equals(table)) {
422 retriever.addExtraTable(table);
427 if (aWhereClause != null) {
428 retriever.appendWhereClause(aWhereClause);
431 if ((anOrderByClause != null) && !(anOrderByClause.trim().length() == 0)) {
432 retriever.appendOrderByClause(anOrderByClause);
435 if (anOffset>-1 && aLimit>-1) {
436 retriever.setLimit(aLimit+1);
437 retriever.setOffset(anOffset);
440 Iterator i = getFieldNames().iterator();
441 while (i.hasNext()) {
442 retriever.addField((String) i.next());
447 connection = obtainConnection();
448 ResultSet resultSet = retriever.execute(connection);
450 boolean hasMore = false;
452 if (resultSet != null) {
453 result = new EntityList();
457 while (((aLimit == -1) || (position<aLimit)) && resultSet.next()) {
458 entity = makeEntityFromResultSet(resultSet);
463 hasMore = resultSet.next();
467 if (result != null) {
468 result.setOffset(anOffset);
469 result.setWhere(aWhereClause);
470 result.setOrder(anOrderByClause);
471 result.setStorage(this);
472 result.setLimit(aLimit);
475 result.setNextBatch(anOffset + aLimit);
478 if (anExtraTables==null && StoreUtil.extendsStorableEntity(entityClass)) {
479 StoreIdentifier sid = result.getStoreIdentifier();
480 logger.debug("CACHE (add): " + sid.toString());
485 catch (Throwable e) {
486 throw new DatabaseFailure(e);
490 if (connection != null) {
491 freeConnection(connection);
493 } catch (Throwable t) {
500 private Entity makeEntityFromResultSet(ResultSet rs) {
501 Map fields = new HashMap();
502 String theResult = null;
504 Entity returnEntity = null;
507 if (StoreUtil.extendsStorableEntity(entityClass)) {
508 StoreIdentifier searchSid = StorableObjectEntity.getStoreIdentifier(this,
510 Entity hit = (Entity) o_store.use(searchSid);
511 if (hit != null) return hit;
514 for (int i = 0; i < getFieldNames().size(); i++) {
515 type = fieldTypes[i];
517 if (type == java.sql.Types.LONGVARBINARY) {
518 InputStreamReader is =
519 (InputStreamReader) rs.getCharacterStream(i + 1);
522 char[] data = new char[32768];
523 StringBuffer theResultString = new StringBuffer();
526 while ((len = is.read(data)) > 0) {
527 theResultString.append(data, 0, len);
531 theResult = theResultString.toString();
538 theResult = getValueAsString(rs, (i + 1), type);
541 if (theResult != null) {
542 fields.put(getFieldNames().get(i), theResult);
546 if (entityClass != null) {
547 returnEntity = createNewEntity();
548 returnEntity.setFieldValues(fields);
550 if (returnEntity instanceof StorableObject) {
551 logger.debug("CACHE: ( in) " + returnEntity.getId() + " :" + mainTable);
552 o_store.add(((StorableObject) returnEntity).getStoreIdentifier());
556 throw new DatabaseExc("Internal Error: entityClass not set!");
559 catch (Throwable e) {
560 throw new DatabaseFailure(e);
567 * Inserts an entity into the database.
570 * @return the value of the primary key of the inserted record
572 public String insert(Entity anEntity) throws DatabaseFailure {
575 RecordInserter inserter =
576 new RecordInserter(mainTable, getPrimaryKeySequence());
578 String returnId = null;
579 Connection con = null;
580 PreparedStatement pstmt = null;
586 for (int i = 0; i < getFieldNames().size(); i++) {
587 fieldName = (String) getFieldNames().get(i);
589 if (!fieldName.equals(primaryKeyField)) {
591 if (!anEntity.hasFieldValue(fieldName) && (
592 fieldName.equals("webdb_create") ||
593 fieldName.equals("webdb_lastchange"))) {
594 inserter.assignVerbatim(fieldName, "now()");
597 if (anEntity.hasFieldValue(fieldName)) {
598 inserter.assignString(fieldName, anEntity.getFieldValue(fieldName));
604 con = obtainConnection();
605 returnId = inserter.execute(con);
607 anEntity.setId(returnId);
611 freeConnection(con, pstmt);
613 catch (Exception e) {
616 freeConnection(con, pstmt);
619 /** todo store entity in o_store */
624 * Updates an entity in the database
628 public void update(Entity theEntity) throws DatabaseFailure {
629 Connection connection = null;
633 RecordUpdater generator = new RecordUpdater(getTableName(), theEntity.getId());
637 // build sql statement
638 for (int i = 0; i < getFieldNames().size(); i++) {
639 field = (String) getFieldNames().get(i);
641 if (!(field.equals(primaryKeyField) ||
642 field.equals("webdb_create") ||
643 field.equals("webdb_lastchange") ||
644 binaryFields.contains(field))) {
646 if (theEntity.hasFieldValue(field)) {
647 generator.assignString(field, theEntity.getFieldValue(field));
653 if (hasField("webdb_lastchange")) {
654 generator.assignVerbatim("webdb_lastchange", "now()");
657 // special case: the webdb_create requires the field in yyyy-mm-dd HH:mm
658 // format so anything extra will be ignored. -mh
659 if (hasField("webdb_create") &&
660 theEntity.hasFieldValue("webdb_create")) {
661 // minimum of 10 (yyyy-mm-dd)...
662 if (theEntity.getFieldValue("webdb_create").length() >= 10) {
663 String dateString = theEntity.getFieldValue("webdb_create");
665 // if only 10, then add 00:00 so it doesn't throw a ParseException
666 if (dateString.length() == 10) {
667 dateString = dateString + " 00:00";
672 java.util.Date d = userInputDateFormat.parse(dateString);
673 generator.assignDateTime("webdb_create", d);
675 catch (ParseException e) {
676 throw new DatabaseFailure(e);
682 connection = obtainConnection();
683 generator.execute(connection);
686 freeConnection(connection);
690 private void invalidateObject(String anId) {
691 // ostore send notification
692 if (StoreUtil.extendsStorableEntity(entityClass)) {
693 String uniqueId = anId;
695 if (entityClass.equals(StorableObjectEntity.class)) {
696 uniqueId += ("@" + mainTable);
699 logger.debug("CACHE: (del) " + anId);
701 StoreIdentifier search_sid =
702 new StoreIdentifier(entityClass,
703 StoreContainerType.STOC_TYPE_ENTITY, uniqueId);
704 o_store.invalidate(search_sid);
710 * @param id des zu loeschenden Datensatzes
711 * @return boolean liefert true zurueck, wenn loeschen erfolgreich war.
713 public boolean delete(String id) throws DatabaseFailure {
714 invalidateObject(id);
716 /** todo could be prepared Statement */
718 Connection connection = obtainConnection();
719 PreparedStatement statement = null;
722 statement = connection.prepareStatement("delete from " + mainTable + " where " + primaryKeyField + "=?");
723 statement.setInt(1, Integer.parseInt(id));
724 logQueryBefore("delete from " + mainTable + " where " + primaryKeyField + "=" + id + "");
725 resultCode = statement.executeUpdate();
727 catch (SQLException e) {
728 logger.warn("Can't delete record", e);
731 freeConnection(connection, statement);
736 return (resultCode > 0) ? true : false;
740 * Deletes entities based on a where clause
742 public int deleteByWhereClause(String aWhereClause) throws DatabaseFailure {
745 Statement stmt = null;
746 Connection con = null;
749 "delete from " + mainTable + " where " + aWhereClause;
751 //theLog.printInfo("DELETE " + sql);
753 con = obtainConnection();
754 stmt = con.createStatement();
755 res = stmt.executeUpdate(sql);
757 catch (Throwable e) {
758 throw new DatabaseFailure(e);
761 freeConnection(con, stmt);
767 /* noch nicht implementiert.
768 * @return immer false
770 public boolean delete(EntityList theEntityList) {
774 public ResultSet executeSql(Statement stmt, String sql)
775 throws DatabaseFailure, SQLException {
778 long startTime = System.currentTimeMillis();
780 rs = stmt.executeQuery(sql);
782 logQueryAfter(sql, (System.currentTimeMillis() - startTime));
784 catch (SQLException e) {
785 logQueryError(sql, (System.currentTimeMillis() - startTime), e);
792 private Map processRow(ResultSet aResultSet) throws DatabaseFailure {
794 Map result = new HashMap();
795 ResultSetMetaData metaData = aResultSet.getMetaData();
796 int nrColumns = metaData.getColumnCount();
797 for (int i=0; i<nrColumns; i++) {
798 result.put(metaData.getColumnName(i+1), getValueAsString(aResultSet, i+1, metaData.getColumnType(i+1)));
803 catch (Throwable e) {
804 throw new DatabaseFailure(e);
809 * Executes 1 sql statement and returns the results as a <code>List</code> of
812 public List executeFreeSql(String sql, int aLimit) throws DatabaseFailure, DatabaseExc {
813 Connection connection = null;
814 Statement statement = null;
816 List result = new ArrayList();
817 connection = obtainConnection();
818 statement = connection.createStatement();
819 ResultSet resultset = executeSql(statement, sql);
821 while (resultset.next() && result.size() < aLimit) {
822 result.add(processRow(resultset));
831 catch (Throwable e) {
832 throw new DatabaseFailure(e);
835 if (connection!=null) {
836 freeConnection(connection, statement);
842 * Executes 1 sql statement and returns the first result row as a <code>Map</code>s
843 * (<code>null</code> if there wasn't any row)
845 public Map executeFreeSingleRowSql(String anSqlStatement) throws DatabaseFailure, DatabaseExc {
847 List resultList = executeFreeSql(anSqlStatement, 1);
849 if (resultList.size()>0)
850 return (Map) resultList.get(0);
856 catch (Throwable t) {
857 throw new DatabaseFailure(t);
862 * Executes 1 sql statement and returns the first column of the first result row as a <code>String</code>s
863 * (<code>null</code> if there wasn't any row)
865 public String executeFreeSingleValueSql(String sql) throws DatabaseFailure, DatabaseExc {
866 Map row = executeFreeSingleRowSql(sql);
871 Iterator i = row.values().iterator();
873 return (String) i.next();
877 public int getSize(String where) throws SQLException, DatabaseFailure {
878 return getSize("", null, where);
881 * returns the number of rows in the table
883 public int getSize(String mainTablePrefix, List extraTables, String where) throws SQLException, DatabaseFailure {
885 String useTable = mainTable;
886 if (mainTablePrefix!=null && mainTablePrefix.trim().length()>0) {
887 useTable+=" "+mainTablePrefix;
889 StringBuffer countSql =
890 new StringBuffer("select count(*) from ").append(useTable);
891 // append extratables, if necessary
892 if (extraTables!=null) {
893 for (int i=0;i < extraTables.size();i++) {
894 if (!extraTables.get(i).equals("")) {
895 countSql.append( ", " + extraTables.get(i));
900 if ((where != null) && (where.length() != 0)) {
901 countSql.append( " where " + where);
904 Connection con = null;
905 Statement stmt = null;
907 logQueryBefore(countSql.toString());
908 long startTime = System.currentTimeMillis();
911 con = obtainConnection();
912 stmt = con.createStatement();
914 ResultSet rs = executeSql(stmt, countSql.toString());
917 result = rs.getInt(1);
920 catch (SQLException e) {
921 logger.error("Database.getSize: " + e.getMessage());
924 freeConnection(con, stmt);
926 logQueryAfter(countSql.toString(), (System.currentTimeMillis() - startTime));
931 public int executeUpdate(Statement stmt, String sql)
932 throws DatabaseFailure, SQLException {
936 long startTime = System.currentTimeMillis();
939 rs = stmt.executeUpdate(sql);
941 logQueryAfter(sql, (System.currentTimeMillis() - startTime));
943 catch (SQLException e) {
944 logQueryError(sql, (System.currentTimeMillis() - startTime), e);
951 public int executeUpdate(String sql)
952 throws DatabaseFailure, SQLException {
954 Connection con = null;
955 PreparedStatement pstmt = null;
958 long startTime = System.currentTimeMillis();
960 con = obtainConnection();
961 pstmt = con.prepareStatement(sql);
962 result = pstmt.executeUpdate();
963 logQueryAfter(sql, System.currentTimeMillis() - startTime);
965 catch (Throwable e) {
966 logQueryError(sql, System.currentTimeMillis() - startTime, e);
967 throw new DatabaseFailure("Database.executeUpdate(" + sql + "): " + e.getMessage(), e);
970 freeConnection(con, pstmt);
976 * Processes the metadata for the table this Database object is responsible for.
978 private void processMetaData(ResultSetMetaData aMetaData) throws DatabaseFailure {
979 fieldNames = new ArrayList();
980 fieldNameToType = new HashMap();
983 int numFields = aMetaData.getColumnCount();
984 fieldTypes = new int[numFields];
986 for (int i = 1; i <= numFields; i++) {
987 fieldNames.add(aMetaData.getColumnName(i));
988 fieldTypes[i - 1] = aMetaData.getColumnType(i);
989 fieldNameToType.put(aMetaData.getColumnName(i), new Integer(aMetaData.getColumnType(i)));
992 catch (Throwable e) {
993 throw new DatabaseFailure(e);
998 * Retrieves metadata from the table this Database object represents
1000 private void acquireMetaData() throws DatabaseFailure {
1001 Connection connection = null;
1002 PreparedStatement statement = null;
1003 String sql = "select * from " + mainTable + " where 0=1";
1006 connection = obtainConnection();
1007 statement = connection.prepareStatement(sql);
1009 logger.debug("METADATA: " + sql);
1010 ResultSet resultSet = statement.executeQuery();
1012 processMetaData(resultSet.getMetaData());
1018 catch (Throwable e) {
1019 throw new DatabaseFailure(e);
1022 freeConnection(connection, statement);
1026 public Connection obtainConnection() throws DatabaseFailure {
1028 return MirGlobal.getDatabaseEngine().obtainConnection();
1030 catch (Exception e) {
1031 throw new DatabaseFailure(e);
1035 public void freeConnection(Connection aConnection) throws DatabaseFailure {
1037 MirGlobal.getDatabaseEngine().releaseConnection(aConnection);
1039 catch (Throwable t) {
1040 logger.warn("Can't release connection: " + t.toString());
1044 public void freeConnection(Connection aConnection, Statement aStatement) throws DatabaseFailure {
1048 catch (Throwable t) {
1049 logger.warn("Can't close statemnet: " + t.toString());
1052 freeConnection(aConnection);
1055 protected void _throwStorageObjectException(Exception e, String aFunction)
1056 throws DatabaseFailure {
1059 logger.error(e.getMessage() + aFunction);
1060 throw new DatabaseFailure(aFunction, e);
1066 * Invalidates any cached entity list
1068 private void invalidateStore() {
1069 // invalidating all EntityLists corresponding with entityClass
1070 if (StoreUtil.extendsStorableEntity(entityClass)) {
1071 StoreContainerType stoc_type =
1072 StoreContainerType.valueOf(entityClass, StoreContainerType.STOC_TYPE_ENTITYLIST);
1073 o_store.invalidate(stoc_type);
1078 * Retrieves a binary value
1080 public byte[] getBinaryField(String aQuery) throws DatabaseFailure, SQLException {
1081 Connection connection=null;
1082 Statement statement=null;
1083 InputStream inputStream;
1086 connection = obtainConnection();
1088 connection.setAutoCommit(false);
1089 statement = connection.createStatement();
1090 ResultSet resultSet = executeSql(statement, aQuery);
1092 if(resultSet!=null) {
1093 if (resultSet.next()) {
1094 if (resultSet.getMetaData().getColumnType(1) == java.sql.Types.BINARY) {
1095 return resultSet.getBytes(1);
1098 inputStream = resultSet.getBlob(1).getBinaryStream();
1099 ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
1100 StreamCopier.copy(inputStream, outputStream);
1101 return outputStream.toByteArray();
1109 connection.setAutoCommit(true);
1111 catch (Throwable e) {
1112 logger.error("EntityImages.getImage resetting transaction mode failed: " + e.toString());
1113 e.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1117 freeConnection(connection, statement);
1119 catch (Throwable e) {
1120 logger.error("EntityImages.getImage freeing connection failed: " +e.toString());
1125 catch (Throwable t) {
1126 logger.error("EntityImages.getImage failed: " + t.toString());
1127 t.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1129 throw new DatabaseFailure(t);
1136 * Sets a binary value for a particular field in a record specified by its identifier
1138 public void setBinaryField(String aFieldName, String anObjectId, byte aData[]) throws DatabaseFailure, SQLException {
1139 PreparedStatement statement = null;
1140 Connection connection = obtainConnection();
1143 connection.setAutoCommit(false);
1145 // are we using bytea ?
1146 if (getFieldType(aFieldName) == java.sql.Types.BINARY) {
1147 statement = connection.prepareStatement(
1148 "update " + mainTable + " set " + aFieldName + " = ? where " + getIdFieldName() + "=" + Integer.parseInt(anObjectId));
1149 statement.setBytes(1, aData);
1150 statement.execute();
1151 connection.commit();
1155 PGConnection postgresqlConnection = (org.postgresql.PGConnection) ((DelegatingConnection) connection).getDelegate();
1156 LargeObjectManager lobManager = postgresqlConnection.getLargeObjectAPI();
1157 int oid = lobManager.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
1158 LargeObject obj = lobManager.open(oid, LargeObjectManager.WRITE); // Now open the file File file =
1161 statement = connection.prepareStatement(
1162 "update " + mainTable + " set " + aFieldName + " = ? where " + getIdFieldName() + "=" + Integer.parseInt(anObjectId));
1163 statement.setInt(1, oid);
1164 statement.execute();
1165 connection.commit();
1169 connection.setAutoCommit(true);
1173 freeConnection(connection, statement);
1178 * Can be overridden to specify a primary key sequence name not named according to
1179 * the convention (tablename _id_seq)
1181 protected String getPrimaryKeySequence() {
1182 return mainTable+"_id_seq";
1186 * Can be called by subclasses to specify fields that are binary, and that shouldn't
1187 * be updated outside of {@link #setBinaryField}
1189 * @param aBinaryField The field name of the binary field
1191 protected void markBinaryField(String aBinaryField) {
1192 binaryFields.add(aBinaryField);
1195 private void logQueryBefore(String aQuery) {
1196 logger.debug("about to perform QUERY " + aQuery);
1197 // (new Throwable()).printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
1200 private void logQueryAfter(String aQuery, long aTime) {
1201 logger.info("QUERY " + aQuery + " took " + aTime + "ms.");
1204 private void logQueryError(String aQuery, long aTime, Throwable anException) {
1205 logger.error("QUERY " + aQuery + " took " + aTime + "ms, but threw exception " + anException.toString());
1208 private int getFieldType(String aFieldName) {
1209 if (fieldNameToType == null) {
1213 return ((Integer) fieldNameToType.get(aFieldName)).intValue();