/* SDX: Documentary System in XML. Copyright (C) 2000, 2001, 2002 Ministere de la culture et de la communication (France), AJLSM Ministere de la culture et de la communication, Mission de la recherche et de la technologie 3 rue de Valois, 75042 Paris Cedex 01 (France) mrt@culture.fr, michel.bottin@culture.fr AJLSM, 17, rue Vital Carles, 33000 Bordeaux (France) sevigny@ajlsm.com This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc. 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA or connect to: http://www.fsf.org/copyleft/gpl.html */ package fr.gouv.culture.sdx.utils.database; import fr.gouv.culture.sdx.exception.SDXException; import fr.gouv.culture.sdx.exception.SDXExceptionCode; import fr.gouv.culture.sdx.utils.Utilities; import fr.gouv.culture.sdx.utils.rdbms.DataSourceComponentBacked; import org.apache.avalon.framework.configuration.Configuration; import org.apache.avalon.framework.configuration.ConfigurationException; import org.apache.avalon.framework.parameters.ParameterException; import org.apache.avalon.framework.parameters.Parameters; import java.sql.*; import java.util.ArrayList; import java.util.Enumeration; import java.util.Hashtable; /** * Created by IntelliJ IDEA. * User: rpandey * Date: Mar 3, 2003 * Time: 3:58:12 PM * To change this template use Options | File Templates. */ public abstract class AbstractJDBCDatabase extends DataSourceComponentBacked implements Database { protected final String FIELD_ID = "id"; protected final String FIELD_PROPERTY_NAME = "propertyName"; protected final String FIELD_PROPERTY_VALUE = "propertyValue"; protected final int COLUMN_PARAM_INDEX_FIELD_ID = 1; protected final int COLUMN_PARAM_INDEX_FIELD_PROPERTY_NAME = 2; protected final int COLUMN_PARAM_INDEX_FIELD_PROPERTY_VALUE = 3; public static final String[] _searchModes = {"UNION", "INTERSECT", "EXCEPT"}; /**Hashtable of property values*/ protected Hashtable props = null; protected Hashtable entityCache = new Hashtable(); public void configure(Configuration configuration) throws ConfigurationException { super.configure(configuration); if (!Utilities.checkString(this.getId())) throw new ConfigurationException("we dont have a valid id with which to build a table name");//TODOException: better message here } public DatabaseEntity getEntity(String id) throws SDXException { if (!Utilities.checkString(id)) return null; DatabaseEntity dbe = null; dbe = getEntityFromCache(id); if (dbe != null) return dbe; DatabaseConnection sqldbConn = getConnection(); Connection conn = sqldbConn.getConnection(); String queryString = getEntityGetQuery(); PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(queryString); ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, id); rs = ps.executeQuery(); dbe = getEntity(id, rs); addEntityToCache(dbe); return dbe; } catch (SQLException e) { String[] args = new String[1]; args[0] = id; throw new SDXException(logger, SDXExceptionCode.ERROR_GET_ENTITY, args, e); } finally { //is it a good practice to close both the result set and prepared statement? //i think the prepared statement closes the corresponding results set?-rbp if (ps != null) { try { if (ps != null) ps.close(); } catch (SQLException e) { String[] args = new String[2]; args[0] = this.getId(); args[1] = e.getMessage(); throw new SDXException(logger, SDXExceptionCode.ERROR_CLOSE_SQL_PREPARED_STATEMENT, args, e); } } releaseConnection(sqldbConn); } } public DatabaseEntity[] getEntities() throws SDXException { DatabaseConnection sqldbConn = getConnection(); Connection conn = sqldbConn.getConnection(); String queryString = getAllEntitiesQuery(); PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(queryString); rs = ps.executeQuery(); return getEntities(rs); } catch (SQLException e) { String[] args = new String[1]; args[0] = this.getId(); throw new SDXException(logger, SDXExceptionCode.ERROR_GET_ENTITIES, args, e); } finally { //is it a good practice to close both the result set and prepared statement? //i think the prepared statement closes the corresponding results set?-rbp if (ps != null) { try { if (ps != null) ps.close(); } catch (SQLException e) { String[] args = new String[2]; args[0] = this.getId(); args[1] = e.getMessage(); throw new SDXException(logger, SDXExceptionCode.ERROR_CLOSE_SQL_PREPARED_STATEMENT, args, e); } } releaseConnection(sqldbConn); } } public String getPropertyValue(String entityId, String name) throws SDXException { DatabaseEntity dbe = getEntity(entityId); if (dbe == null || !Utilities.checkString(name)) return null; return dbe.getProperty(name); } public String[] getPropertyValues(String entityId, String propertyName) throws SDXException { DatabaseEntity dbe = getEntity(entityId); if (dbe == null || !Utilities.checkString(propertyName)) return null; return dbe.getPropertyValues(propertyName); } public Property[] getProperties(String entityId) throws SDXException { DatabaseEntity dbe = getEntity(entityId); if (dbe == null) return null; return dbe.getProperties(); } public synchronized void save(DatabaseEntity ent) throws SDXException { // Now the properties if (ent == null) return; String entityId = ent.getId(); if (!Utilities.checkString(entityId)) return;//TODO?:should we be throwing an exception here,not sure as DatabseEntity constructor is restrictive enough?-rbp Property[] props = ent.getProperties(); DatabaseConnection sqldbConn = getConnection(); Connection conn = sqldbConn.getConnection(); String queryString = null; PreparedStatement ps = null; try { //adding actual properties if (props != null && props.length > 0) { for (int i = 0; i < props.length; i++) { String[] values = props[i].getValues(); if (values != null) { for (int j = 0; j < values.length; j++) { queryString = getPropertyAddQuery(); ps = conn.prepareStatement(queryString); ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, entityId); //TODO: i don't think the property should be stored if the name or val is null String propName = props[i].getName(); String propVal = values[j]; if (Utilities.checkString(propName) && Utilities.checkString(propVal)) { ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_NAME, propName); ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_VALUE, propVal); ps.executeUpdate(); ps.clearParameters(); } } } } //ent.dump(System.out); } else { //adding a placeholder for the property, if no properties queryString = getPropertyAddQuery(); ps = conn.prepareStatement(queryString); ps.clearParameters(); ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, entityId); ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_NAME, ""); ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_VALUE, ""); ps.executeUpdate(); } } catch (SQLException e) { String[] args = new String[2]; args[0] = entityId; args[1] = this.getId(); throw new SDXException(logger, SDXExceptionCode.ERROR_SAVE_ENTITY, args, e); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { String[] args = new String[2]; args[0] = this.getId(); args[1] = e.getMessage(); throw new SDXException(logger, SDXExceptionCode.ERROR_CLOSE_SQL_PREPARED_STATEMENT, args, e); } } sqldbConn.commit(); releaseConnection(sqldbConn); } } public synchronized void delete(DatabaseEntity ent) throws SDXException { if (ent != null) { String entityId = ent.getId(); if (Utilities.checkString(entityId)) { DatabaseConnection sqldbConn = getConnection(); Connection conn = sqldbConn.getConnection(); String queryString = getEntityDeleteQuery(); PreparedStatement ps = null; try { ps = conn.prepareStatement(queryString); ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, entityId); ps.executeUpdate(); deleteEntityFromCache(entityId); } catch (SQLException e) { String[] args = new String[2]; args[0] = entityId; args[1] = this.getId(); throw new SDXException(logger, SDXExceptionCode.ERROR_DELETE_ENTITY, args, e); } finally { //is it a good practice to close both the result set and prepared statement? //i think the prepared statement closes the corresponding results set?-rbp if (ps != null) { try { if (ps != null) ps.close(); } catch (SQLException e) { String[] args = new String[2]; args[0] = this.getId(); args[1] = e.getMessage(); throw new SDXException(logger, SDXExceptionCode.ERROR_CLOSE_SQL_PREPARED_STATEMENT, args, e); } } sqldbConn.commit(); releaseConnection(sqldbConn); } } } } public synchronized void delete(DatabaseEntity[] entities) throws SDXException { if (entities == null) return; Connection conn = null; String entityId = null; PreparedStatement ps = null; String queryString = getEntityDeleteQuery(); DatabaseConnection sqldbConn = getConnection(); try { TODO://this should be one query executed with a list of id's for (int i = 0; i < entities.length; i++) { DatabaseEntity ent = entities[i]; if (ent != null) { entityId = ent.getId(); if (Utilities.checkString(entityId)) { if (conn == null) { conn = sqldbConn.getConnection(); ps = conn.prepareStatement(queryString); } ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, entityId); ps.executeUpdate(); deleteEntityFromCache(entityId); } } } } catch (SQLException e) { String[] args = new String[2]; args[0] = this.getId(); throw new SDXException(logger, SDXExceptionCode.ERROR_DELETE_ENTITIES, args, e); } finally { //is it a good practice to close both the result set and prepared statement? //i think the prepared statement closes the corresponding results set?-rbp if (ps != null) { try { if (ps != null) ps.close(); } catch (SQLException e) { String[] args = new String[2]; args[0] = this.getId(); args[1] = e.getMessage(); throw new SDXException(logger, SDXExceptionCode.ERROR_CLOSE_SQL_PREPARED_STATEMENT, args, e); } } sqldbConn.commit(); releaseConnection(sqldbConn); } } public void update(DatabaseEntity ent) throws SDXException { if (ent != null) { delete(ent); save(ent); } } public long size() { DatabaseConnection sqldbConn = null; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; int ret = -1; try { sqldbConn = getConnection(); //TODO: what is the proper query here given no unique id in this structure conn = sqldbConn.getConnection(); String queryString = "SELECT COUNT(DISTINCT " + FIELD_ID + ") FROM " + getTableName(); ps = conn.prepareStatement(queryString); rs = ps.executeQuery(); rs.next(); ret = rs.getInt(1); return ret; } catch (SQLException e) { return ret; } catch (SDXException e) { return ret; } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { String[] args = new String[2]; args[0] = this.getId(); args[1] = e.getMessage(); new SDXException(logger, SDXExceptionCode.ERROR_CLOSE_SQL_PREPARED_STATEMENT, args, e); } } try { releaseConnection(sqldbConn); } catch (SDXException e) { Utilities.logException(logger, e); } } } public synchronized void empty() throws SDXException { DatabaseConnection sqlDbConn = getConnection(); Connection conn = null; PreparedStatement ps = null; try { conn = sqlDbConn.getConnection(); String queryString = getDeleteAllQuery(); ps = conn.prepareStatement(queryString); ps.executeUpdate(); } catch (SQLException e) { String[] args = new String[2]; args[0] = this.getId(); args[1] = e.getMessage(); //TODOException: better message here, basically database and repository exceptions need to be refactored throw new SDXException(logger, SDXExceptionCode.ERROR_EMPTY, args, e); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { String[] args = new String[2]; args[0] = this.getId(); args[1] = e.getMessage(); throw new SDXException(logger, SDXExceptionCode.ERROR_CLOSE_SQL_PREPARED_STATEMENT, args, e); } } sqlDbConn.commit(); this.releaseConnection(sqlDbConn); } } public void setProperties(Hashtable props) { this.props = props; } public boolean entityExists(String id) { if (!Utilities.checkString(id)) return false; DatabaseEntity dbe = null; try { dbe = this.getEntity(id); } catch (SDXException e) { Utilities.logException(logger, e); return false; } if (dbe == null) return false; else return true; } public String[] search(Parameters params) throws SDXException { return search(params, SEARCH_MODE_AND); } public String[] search(final Parameters params, int mode) throws SDXException { if (mode != SEARCH_MODE_OR && mode != SEARCH_MODE_AND && mode != SEARCH_MODE_NOT) return new String[0]; String modeString = _searchModes[mode]; //SELECT * FROM tablename WHERE propertyName = paramName propertyValue = paramValue. if (params == null) return new String[0]; String[] entities = new String[0]; DatabaseConnection sqlDbConn = getConnection(); Connection conn = sqlDbConn.getConnection(); String queryString = ""; final String l_templateQueryString = "SELECT " + FIELD_ID + " FROM " + getTableName() + " WHERE " + FIELD_PROPERTY_NAME + " = ? AND " + FIELD_PROPERTY_VALUE + " = ?"; //getting the param names //TODO: can this be made more efficient, how can we avoid two loops? final String[] paramNames = params.getNames(); try { //building the query statement for (int i = 0; i < paramNames.length; i++) { //TODONOW: this must be optimized with a better query possibly using temp tables if (i == 0) queryString += l_templateQueryString; else queryString += " " + modeString + "DISTINCT " + l_templateQueryString; } //fill the query statement with params if (Utilities.checkString(queryString)) { Template template = new Template(conn, queryString); QueryExecutor qe = new QueryExecutor() { String[] l_dbes = null; public void prepare(PreparedStatement ps) throws SQLException, SDXException { //setting the params for (int j = 0; j < paramNames.length; j++) { //ps.setString(paramIdx++ , FIELD_PROPERTY_NAME); int paramIdx = j * 2; ps.setString(paramIdx + 1, paramNames[j]); // ps.setString(paramIdx++, FIELD_PROPERTY_VALUE); try { ps.setString(paramIdx + 2, params.getParameter(paramNames[j])); } catch (ParameterException e) { throw new SDXException(logger, SDXExceptionCode.ERROR_GET_PARAMETERS, null, e); } } } public void collect(ResultSet rs) throws SQLException, SDXException { l_dbes = getEntityIds(rs); } public Object get() { return l_dbes; } }; template.execute(qe, Template.MODE_EXECUTE_QUERY); entities = (String[]) qe.get(); } if (entities==null){ entities = new String[0]; } return entities; } catch (SDXException e) { String[] args = new String[1]; args[0] = this.getId(); throw new SDXException(this.logger, SDXExceptionCode.ERROR_SEARCH_DATABASE, args, e); } finally { releaseConnection(sqlDbConn); } } public void setId(String id) { super.id = id; } public String getId() { return super.id; } /** Initializes the repository. * * If there are no tables in the database, * we create the necessary table * * @throws fr.gouv.culture.sdx.exception.SDXException */ public void init() throws SDXException { /** First try to access the database and see if the tables exist. */ DatabaseConnection sqlDbConn = getConnection(); Connection conn = null; ResultSet rs = null; try { conn = sqlDbConn.getConnection(); //if we don't have a database we can't do much //TODO: check for null connection-rbp DatabaseMetaData dbmd = conn.getMetaData(); rs = dbmd.getTables(null, null, getTableName(), null); if (!rs.next()) { // The table doesn't exist, so we should create it. createTable(conn); createIndicies(conn); } } catch (SQLException e) { String[] args = new String[1]; args[0] = this.getId(); throw new SDXException(logger, SDXExceptionCode.ERROR_INIT_DATABASE, args, e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { String[] args = new String[2]; args[0] = this.getId(); args[1] = e.getMessage(); throw new SDXException(logger, SDXExceptionCode.ERROR_CLOSE_RESULT_SET, args, e); } } sqlDbConn.commit(); releaseConnection(sqlDbConn); } } protected String getTableCreationQuery() { return "CREATE TABLE " + getTableName() + " ( " + FIELD_ID + " VARCHAR(255) NOT NULL, " + FIELD_PROPERTY_NAME + " VARCHAR(255) NOT NULL, " + FIELD_PROPERTY_VALUE + " VARCHAR(255) NOT NULL, " + "PRIMARY KEY (" + FIELD_ID + "(255), " + FIELD_PROPERTY_NAME + "(255), " + FIELD_PROPERTY_VALUE + "(255) ))"; } /** Returns an SQL query that could select a entity using its id. *

* This query should have one parameter for the id. * @return The query. */ protected String getEntityGetQuery() { return "SELECT * FROM " + getTableName() + " WHERE " + FIELD_ID + " = ?"; } protected String getAllEntitiesQuery() { return "SELECT * FROM " + getTableName(); } protected String getEntityDeleteQuery() { return "DELETE FROM " + getTableName() + " WHERE " + FIELD_ID + " = ?"; } protected String getPropertyAddQuery() { return "INSERT INTO " + getTableName() + " (" + FIELD_ID + "," + FIELD_PROPERTY_NAME + "," + FIELD_PROPERTY_VALUE + ") VALUES (?, ?, ?)"; } protected String getPropertyRemoveQueryWithId() { return "DELETE FROM " + getTableName() + " WHERE " + FIELD_ID + " = ? AND " + FIELD_PROPERTY_NAME + " = ? AND " + FIELD_PROPERTY_VALUE + " = ?"; } protected String getPropertyRemoveQueryWithoutId() { return "DELETE FROM " + getTableName() + " WHERE " + FIELD_PROPERTY_NAME + " = ? AND " + FIELD_PROPERTY_VALUE + " = ?"; } protected String getDeleteAllQuery() { return "DELETE FROM " + getTableName(); } protected String getCreateIndiciesQuery(String fieldName) { return "CREATE INDEX " + fieldName + " ON " + getTableName() + " (" + fieldName + ")"; //return "CREATE INDEX " + getIndexName() + " ON " + getTableName() + " (" + FIELD_ID + ", " + FIELD_PROPERTY_NAME + ", " + FIELD_PROPERTY_VALUE + ")"; } /**Calling methods should close ResultSet after calling this method * * @param id * @param rs * @return * @throws fr.gouv.culture.sdx.exception.SDXException */ protected DatabaseEntity getEntity(String id, ResultSet rs) throws SDXException { if (!Utilities.checkString(id) || rs == null) return null; DatabaseEntity dbe = null; try { while (rs.next()) { String rowid = rs.getString(FIELD_ID); String propName = rs.getString(FIELD_PROPERTY_NAME); String propVal = rs.getString(FIELD_PROPERTY_VALUE); if (Utilities.checkString(rowid) && id.equals(rowid)) { if (dbe == null) dbe = new DatabaseEntity(id); dbe.addProperty(propName, propVal); } } return dbe; } catch (SQLException e) { String[] args = new String[1]; args[0] = id; throw new SDXException(logger, SDXExceptionCode.ERROR_GET_ENTITY, args, e); } } /**Calling methods should close ResultSet after calling this method * * @param rs * @return * @throws fr.gouv.culture.sdx.exception.SDXException */ protected DatabaseEntity[] getEntities(ResultSet rs) throws SDXException { if (rs == null) return null; Hashtable entities = null; try { while (rs.next()) { if (entities == null) entities = new Hashtable(); String rowid = rs.getString(FIELD_ID); String propName = rs.getString(FIELD_PROPERTY_NAME); String propVal = rs.getString(FIELD_PROPERTY_VALUE); if (Utilities.checkString(rowid) && Utilities.checkString(propName)) { DatabaseEntity dbe = (DatabaseEntity) entities.get(rowid); if (dbe == null) dbe = new DatabaseEntity(rowid); dbe.addProperty(propName, propVal); entities.put(rowid, dbe); } } if (entities == null || entities.size() == 0) return null; return (DatabaseEntity[]) entities.values().toArray(new DatabaseEntity[0]); } catch (SQLException e) { String[] args = new String[1]; args[0] = this.getId(); throw new SDXException(logger, SDXExceptionCode.ERROR_GET_ENTITIES, args, e); } } /**Calling methods should close ResultSet after calling this method * * @param rs * @return * @throws fr.gouv.culture.sdx.exception.SDXException */ protected String[] getEntityIds(ResultSet rs) throws SDXException { if (rs == null) return null; ArrayList ids = null; try { while (rs.next()) { if (ids == null) ids = new ArrayList(); String rowid = rs.getString(FIELD_ID); if (Utilities.checkString(rowid)) ids.add(rowid); } if (ids == null || ids.size() == 0) return null; return (String[]) ids.toArray(new String[0]); } catch (SQLException e) { String[] args = new String[1]; args[0] = this.getId(); throw new SDXException(logger, SDXExceptionCode.ERROR_GET_ENTITIES, args, e); } } /** * Creates and index on the "id" field. */ protected void createIndicies(Connection conn) throws SDXException { PreparedStatement ps = null; try { ps = conn.prepareStatement(getCreateIndiciesQuery(FIELD_ID)); ps.executeUpdate(); ps = conn.prepareStatement(getCreateIndiciesQuery(FIELD_PROPERTY_NAME)); ps.executeUpdate(); ps = conn.prepareStatement(getCreateIndiciesQuery(FIELD_PROPERTY_VALUE)); ps.executeUpdate(); } catch (SQLException e) { String[] args = new String[2]; args[0] = this.getTableName(); args[1] = this.getId(); throw new SDXException(logger, SDXExceptionCode.ERROR_CREATE_INDEX, args, e); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e1) { String[] args1 = new String[2]; args1[0] = this.id; args1[1] = e1.getMessage(); throw new SDXException(logger, SDXExceptionCode.ERROR_CLOSE_SQL_PREPARED_STATEMENT, args1, e1); } } } } /**If the entity does not exist the method fails silently * * @param entityId * @param propertyName * @param propertyValue * @throws SDXException */ public void addProperty(String entityId, String propertyName, String propertyValue) throws SDXException { //don't have good params if (!Utilities.checkString(entityId) || !Utilities.checkString(propertyName) || !Utilities.checkString(propertyValue)) return; if (this.entityExists(entityId)) { DatabaseConnection sqldbConn = getConnection(); Connection conn = sqldbConn.getConnection(); String queryString = getPropertyAddQuery(); try { PreparedStatement ps = conn.prepareStatement(queryString); ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, entityId); ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_NAME, propertyName); ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_VALUE, propertyValue); ps.executeUpdate(); deleteEntityFromCache(entityId); } catch (SQLException e) { String[] args = new String[2]; args[0] = entityId; args[1] = this.getId(); throw new SDXException(logger, SDXExceptionCode.ERROR_ADD_PROPERTY, args, e); } finally { sqldbConn.commit(); this.releaseConnection(sqldbConn); } } } /**If the entity does not exist the method fails silently * * @param entityId * @param propertyName * @param propertyValue * @throws SDXException */ public void removeProperty(String entityId, String propertyName, String propertyValue) throws SDXException { //don't have good params if (!Utilities.checkString(entityId) || !Utilities.checkString(propertyName) || !Utilities.checkString(propertyValue)) return; if (this.entityExists(entityId)) { DatabaseConnection sqldbConn = getConnection(); Connection conn = sqldbConn.getConnection(); String queryString = getPropertyRemoveQueryWithId(); try { PreparedStatement ps = conn.prepareStatement(queryString); ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, entityId); ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_NAME, propertyName); ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_VALUE, propertyValue); ps.executeUpdate(); deleteEntityFromCache(entityId); } catch (SQLException e) { String[] args = new String[2]; args[0] = entityId; args[1] = this.getId(); throw new SDXException(logger, SDXExceptionCode.ERROR_REMOVE_PROPERTY_WITH_ID, args, e); } finally { sqldbConn.commit(); this.releaseConnection(sqldbConn); } } } public void removeProperty(String propertyName, String propertyValue) throws SDXException { //don't have good params if (!Utilities.checkString(propertyName) || !Utilities.checkString(propertyValue)) return; DatabaseConnection sqldbConn = getConnection(); Connection conn = sqldbConn.getConnection(); String queryString = getPropertyRemoveQueryWithoutId(); try { PreparedStatement ps = conn.prepareStatement(queryString); ps.setString(1, propertyName); ps.setString(2, propertyValue); ps.executeUpdate(); refreshEntityCache(); } catch (SQLException e) { String[] args = new String[3]; args[0] = propertyName; args[1] = propertyValue; args[2] = this.getId(); throw new SDXException(logger, SDXExceptionCode.ERROR_REMOVE_PROPERTY_WITHOUT_ID, args, e); } finally { sqldbConn.commit(); this.releaseConnection(sqldbConn); } } protected synchronized DatabaseEntity getEntityFromCache(String id) { if (!Utilities.checkString(id)) return null; return (DatabaseEntity) this.entityCache.get(id); } protected synchronized void addEntityToCache(DatabaseEntity dbe) { if (dbe == null || !Utilities.checkString(dbe.getId())) return; this.entityCache.put(dbe.getId(), dbe); } protected synchronized void deleteEntityFromCache(String id) { if (!Utilities.checkString(id)) return; this.entityCache.remove(id); } public String getWildcardSearchToken() { return "%";//sql wildcard character is percent } protected synchronized boolean isEntityInCache(String id) { if (!Utilities.checkString(id)) return false; return this.entityCache.containsKey(id); } protected synchronized void refreshEntityCache() throws SDXException { Enumeration ids = this.entityCache.keys(); if (ids != null) { while (ids.hasMoreElements()) { String id = (String) ids.nextElement(); if (Utilities.checkString(id)) addEntityToCache(getEntity(id)); } } } }