+ /** todo could be prepared Statement */
+ Statement stmt = null;
+ Connection con = null;
+ int res = 0;
+ String sql =
+ "delete from " + mainTable + " where " + primaryKeyField + "='" + id + "'";
+
+ logQueryBefore(sql);
+ try {
+ con = obtainConnection();
+ stmt = con.createStatement();
+ res = stmt.executeUpdate(sql);
+ }
+ catch (SQLException sqe) {
+ throwSQLException(sqe, "delete");
+ }
+ finally {
+ freeConnection(con, stmt);
+ }
+
+ invalidateStore();
+
+ return (res > 0) ? true : false;
+ }
+
+ /**
+ * Deletes entities based on a where clause
+ */
+ public int deleteByWhereClause(String aWhereClause) throws StorageObjectFailure {
+ invalidateStore();
+
+ Statement stmt = null;
+ Connection con = null;
+ int res = 0;
+ String sql =
+ "delete from " + mainTable + " where " + aWhereClause;
+
+ //theLog.printInfo("DELETE " + sql);
+ try {
+ con = obtainConnection();
+ stmt = con.createStatement();
+ res = stmt.executeUpdate(sql);
+ }
+ catch (SQLException sqe) {
+ throwSQLException(sqe, "delete");
+ }
+ finally {
+ freeConnection(con, stmt);
+ }
+
+ return res;
+ }
+
+ /* noch nicht implementiert.
+ * @return immer false
+ */
+ public boolean delete(EntityList theEntityList) {
+ return false;
+ }
+
+ /**
+ * Diese Methode fuehrt den Sqlstring <i>sql</i> aus und timed im Logfile.
+ * @param stmt Statemnt
+ * @param sql Sql-String
+ */
+ public ResultSet executeSql(Statement stmt, String sql)
+ throws StorageObjectFailure, SQLException {
+ ResultSet rs;
+ logQueryBefore(sql);
+ long startTime = System.currentTimeMillis();
+ try {
+ rs = stmt.executeQuery(sql);
+
+ logQueryAfter(sql, (System.currentTimeMillis() - startTime));
+ }
+ catch (SQLException e) {
+ logQueryError(sql, (System.currentTimeMillis() - startTime), e);
+ throw e;
+ }
+
+ return rs;
+ }
+
+ private Map processRow(ResultSet aResultSet) throws StorageObjectFailure {
+ try {
+ Map result = new HashMap();
+ ResultSetMetaData metaData = aResultSet.getMetaData();
+ int nrColumns = metaData.getColumnCount();
+ for (int i=0; i<nrColumns; i++) {
+ result.put(metaData.getColumnName(i+1), getValueAsString(aResultSet, i+1, metaData.getColumnType(i+1)));
+ }
+
+ return result;
+ }
+ catch (Throwable e) {
+ throw new StorageObjectFailure(e);
+ }
+ }
+
+ /**
+ * Executes 1 sql statement and returns the results as a <code>List</code> of
+ * <code>Map</code>s
+ */
+ public List executeFreeSql(String sql, int aLimit) throws StorageObjectFailure, StorageObjectExc {
+ Connection connection = null;
+ Statement statement = null;
+ try {
+ List result = new ArrayList();
+ connection = obtainConnection();
+ statement = connection.createStatement();
+ ResultSet resultset = executeSql(statement, sql);
+ try {
+ while (resultset.next() && result.size() < aLimit) {
+ result.add(processRow(resultset));
+ }
+ }
+ finally {
+ resultset.close();
+ }
+
+ return result;
+ }
+ catch (Throwable e) {
+ throw new StorageObjectFailure(e);
+ }
+ finally {
+ if (connection!=null) {
+ freeConnection(connection, statement);
+ }
+ }
+ }
+
+ /**
+ * Executes 1 sql statement and returns the first result row as a <code>Map</code>s
+ * (<code>null</code> if there wasn't any row)
+ */
+ public Map executeFreeSingleRowSql(String anSqlStatement) throws StorageObjectFailure, StorageObjectExc {
+ try {
+ List resultList = executeFreeSql(anSqlStatement, 1);
+ try {
+ if (resultList.size()>0)
+ return (Map) resultList.get(0);
+ return null;
+ }
+ finally {
+ }
+ }
+ catch (Throwable t) {
+ throw new StorageObjectFailure(t);
+ }
+ }
+
+ /**
+ * Executes 1 sql statement and returns the first column of the first result row as a <code>String</code>s
+ * (<code>null</code> if there wasn't any row)
+ */
+ public String executeFreeSingleValueSql(String sql) throws StorageObjectFailure, StorageObjectExc {
+ Map row = executeFreeSingleRowSql(sql);
+
+ if (row==null)
+ return null;
+
+ Iterator i = row.values().iterator();
+ if (i.hasNext())
+ return (String) i.next();
+ return null;
+ }
+
+ public int getSize(String where) throws SQLException, StorageObjectFailure {
+ return getSize("", null, where);
+ }
+ /**
+ * returns the number of rows in the table
+ */
+ public int getSize(String mainTablePrefix, List extraTables, String where) throws SQLException, StorageObjectFailure {
+
+ String useTable = mainTable;
+ if (mainTablePrefix!=null && mainTablePrefix.trim().length()>0) {
+ useTable+=" "+mainTablePrefix;
+ }
+ StringBuffer countSql =
+ new StringBuffer("select count(*) from ").append(useTable);
+ // append extratables, if necessary
+ if (extraTables!=null) {
+ for (int i=0;i < extraTables.size();i++) {
+ if (!extraTables.get(i).equals("")) {
+ countSql.append( ", " + extraTables.get(i));
+ }
+ }
+ }
+
+ if ((where != null) && (where.length() != 0)) {
+ countSql.append( " where " + where);
+ }
+
+ Connection con = null;
+ Statement stmt = null;
+ int result = 0;
+ logQueryBefore(countSql.toString());
+ long startTime = System.currentTimeMillis();
+
+ try {
+ con = obtainConnection();
+ stmt = con.createStatement();
+
+ ResultSet rs = executeSql(stmt, countSql.toString());
+
+ while (rs.next()) {
+ result = rs.getInt(1);
+ }
+ }
+ catch (SQLException e) {
+ logger.error("Database.getSize: " + e.getMessage());
+ }
+ finally {
+ freeConnection(con, stmt);
+ }
+ logQueryAfter(countSql.toString(), (System.currentTimeMillis() - startTime));
+
+ return result;
+ }
+
+ public int executeUpdate(Statement stmt, String sql)
+ throws StorageObjectFailure, SQLException {
+ int rs;
+
+ logQueryBefore(sql);
+ long startTime = System.currentTimeMillis();
+
+ try {
+ rs = stmt.executeUpdate(sql);
+
+ logQueryAfter(sql, (System.currentTimeMillis() - startTime));
+ }
+ catch (SQLException e) {
+ logQueryError(sql, (System.currentTimeMillis() - startTime), e);
+ throw e;
+ }
+
+ return rs;
+ }
+
+ public int executeUpdate(String sql)
+ throws StorageObjectFailure, SQLException {
+ int result = -1;
+ Connection con = null;
+ PreparedStatement pstmt = null;
+
+ logQueryBefore(sql);
+ long startTime = System.currentTimeMillis();
+ try {
+ con = obtainConnection();
+ pstmt = con.prepareStatement(sql);
+ result = pstmt.executeUpdate();
+ logQueryAfter(sql, System.currentTimeMillis() - startTime);
+ }
+ catch (Throwable e) {
+ logQueryError(sql, System.currentTimeMillis() - startTime, e);
+ throw new StorageObjectFailure("Database.executeUpdate(" + sql + "): " + e.getMessage(), e);
+ }
+ finally {
+ freeConnection(con, pstmt);
+ }
+ return result;
+ }
+
+ /**
+ * Processes the metadata for the table this Database object is responsible for.
+ */
+ private void processMetaData(ResultSetMetaData aMetaData) throws StorageObjectFailure {
+ fieldNames = new ArrayList();
+ fieldNameToType = new HashMap();
+
+ try {
+ int numFields = aMetaData.getColumnCount();
+ fieldTypes = new int[numFields];
+
+ for (int i = 1; i <= numFields; i++) {
+ fieldNames.add(aMetaData.getColumnName(i));
+ fieldTypes[i - 1] = aMetaData.getColumnType(i);
+ fieldNameToType.put(aMetaData.getColumnName(i), new Integer(aMetaData.getColumnType(i)));
+ }
+ }
+ catch (SQLException e) {
+ throwSQLException(e, "processMetaData");
+ }
+ }
+
+ /**
+ * Retrieves metadata from the table this Database object represents
+ */
+ private void acquireMetaData() throws StorageObjectFailure {
+ Connection connection = null;
+ PreparedStatement statement = null;
+ String sql = "select * from " + mainTable + " where 0=1";
+
+ try {
+ connection = obtainConnection();
+ statement = connection.prepareStatement(sql);
+
+ logger.debug("METADATA: " + sql);
+ ResultSet resultSet = statement.executeQuery();
+ try {
+ processMetaData(resultSet.getMetaData());
+ }
+ finally {
+ resultSet.close();
+ }
+ }
+ catch (SQLException e) {
+ throwSQLException(e, "acquireMetaData");
+ }
+ finally {
+ freeConnection(connection, statement);
+ }
+ }
+
+ public Connection obtainConnection() throws StorageObjectFailure {
+ try {
+ return MirGlobal.getDatabaseEngine().obtainConnection();
+ }
+ catch (Exception e) {
+ throw new StorageObjectFailure(e);
+ }
+ }
+
+ public void freeConnection(Connection aConnection, Statement aStatement) throws StorageObjectFailure {
+ try {
+ aStatement.close();
+ }
+ catch (Throwable t) {
+ logger.warn("Can't close statemnet: " + t.toString());
+ }
+
+ try {
+ MirGlobal.getDatabaseEngine().releaseConnection(aConnection);
+ }
+ catch (Throwable t) {
+ logger.warn("Can't release connection: " + t.toString());
+ }
+ }
+
+ /**
+ * Wertet SQLException aus und wirft dannach eine StorageObjectException
+ * @param sqe SQLException
+ * @param aFunction Funktonsname, in der die SQLException geworfen wurde
+ */
+ protected void throwSQLException(SQLException sqe, String aFunction) throws StorageObjectFailure {
+ String state = "";
+ String message = "";
+ int vendor = 0;
+
+ if (sqe != null) {
+ state = sqe.getSQLState();
+ message = sqe.getMessage();
+ vendor = sqe.getErrorCode();
+ }
+
+ String information =
+ "SQL Error: " +
+ "state= " + state +
+ ", vendor= " + vendor +
+ ", message=" + message +
+ ", function= " + aFunction;
+
+ logger.error(information);
+
+ throw new StorageObjectFailure(information, sqe);
+ }
+
+ protected void _throwStorageObjectException(Exception e, String aFunction)
+ throws StorageObjectFailure {
+
+ if (e != null) {
+ logger.error(e.getMessage() + aFunction);
+ throw new StorageObjectFailure(aFunction, e);
+ }
+ }
+
+ /**
+ * Loggt Fehlermeldung mit dem Parameter Message und wirft dannach
+ * eine StorageObjectException
+ * @param aMessage Nachricht mit dem Fehler
+ * @exception StorageObjectFailure
+ */
+ void throwStorageObjectException(String aMessage) throws StorageObjectFailure {
+ logger.error(aMessage);
+ throw new StorageObjectFailure(aMessage, null);
+ }
+
+ /**
+ * Invalidates any cached entity list
+ */
+ private void invalidateStore() {
+ // invalidating all EntityLists corresponding with entityClass
+ if (StoreUtil.extendsStorableEntity(entityClass)) {
+ StoreContainerType stoc_type =
+ StoreContainerType.valueOf(entityClass, StoreContainerType.STOC_TYPE_ENTITYLIST);
+ o_store.invalidate(stoc_type);
+ }
+ }
+
+ /**
+ * Retrieves a binary value
+ */
+ public InputStream getBinaryField(String aQuery) throws StorageObjectFailure, SQLException {
+ Connection connection=null;
+ Statement statement=null;
+ InputStream inputStream;
+ InputStream imageInputStream = null;
+
+ try {
+ connection = obtainConnection();
+ try {
+ connection.setAutoCommit(false);
+ statement = connection.createStatement();
+ ResultSet resultSet = executeSql(statement, aQuery);
+
+ if(resultSet!=null) {
+ if (resultSet.next()) {
+ if (resultSet.getMetaData().getColumnType(1) == java.sql.Types.BINARY) {
+ byte[] data = resultSet.getBytes(1);
+ imageInputStream = new ByteArrayInputStream(data);
+ }
+ else {
+ inputStream = resultSet.getBlob(1).getBinaryStream();
+ imageInputStream = new BinaryFieldInputStream(inputStream, connection, statement);
+ }
+ }
+ resultSet.close();
+ }
+ }
+ finally {
+ }
+ }
+ catch (Throwable t) {
+ logger.error("EntityImages.getImage failed: " + t.toString());
+ t.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
+
+ try {
+ connection.setAutoCommit(true);
+ }
+ catch (Throwable e) {
+ logger.error("EntityImages.getImage resetting transaction mode failed: " + e.toString());
+ e.printStackTrace(logger.asPrintWriter(LoggerWrapper.DEBUG_MESSAGE));
+ }
+
+ try {
+ freeConnection(connection, statement);
+ }
+ catch (Throwable e) {
+ logger.error("EntityImages.getImage freeing connection failed: " +e.toString());
+ }
+
+ throw new StorageObjectFailure(t);
+ }
+
+ return imageInputStream;
+ }
+
+ /**
+ * Sets a binary value for a particular field in a record specified by its identifier
+ */
+ public void setBinaryField(String aFieldName, String anObjectId, byte aData[]) throws StorageObjectFailure, SQLException {
+ PreparedStatement statement = null;
+ Connection connection = obtainConnection();
+
+ try {
+ connection.setAutoCommit(false);
+ try {
+ // are we using bytea ?
+ if (getFieldType(aFieldName) == java.sql.Types.BINARY) {
+ statement = connection.prepareStatement(
+ "update " + mainTable + " set " + aFieldName + " = ? where " + getIdFieldName() + "=" + Integer.parseInt(anObjectId));
+ statement.setBytes(1, aData);
+ statement.execute();
+ connection.commit();
+ }
+ // or the old oid's
+ else {
+ PGConnection postgresqlConnection = (org.postgresql.PGConnection) ((DelegatingConnection) connection).getDelegate();
+ LargeObjectManager lobManager = postgresqlConnection.getLargeObjectAPI();
+ int oid = lobManager.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
+ LargeObject obj = lobManager.open(oid, LargeObjectManager.WRITE); // Now open the file File file =
+ obj.write(aData);
+ obj.close();
+ statement = connection.prepareStatement(
+ "update " + mainTable + " set " + aFieldName + " = ? where " + getIdFieldName() + "=" + Integer.parseInt(anObjectId));
+ statement.setInt(1, oid);
+ statement.execute();
+ connection.commit();
+ }
+ }
+ finally {
+ connection.setAutoCommit(true);
+ }
+ }
+ finally {
+ freeConnection(connection, statement);
+ }
+ }