/ - Diff - Metacat - Ecoinformatics Redmine

« Previous | Next »

Revision 5108

Added by daigle over 14 years ago

move dbconnection object out of base class and create it for each db access so it can be released

     	private Logger logMetacat = Logger.getLogger(XMLAccessAccess.class);
     	// Constructor
     	public XMLAccessAccess() throws AccessException {
     		super("XMLAccessAccess");
+    	}
     	public XMLAccessAccess() throws AccessException {}
     	/**
     	 * Get all xml access for a document
-...
     		// first get the job from the db and put it into a DAO
     		PreparedStatement pstmt = null;
     		try {
     		DBConnection conn = null;
     		int serialNumber = -1;
     		try {
     			conn = DBConnectionPool.getDBConnection("XMLAccessAccess.getXMLAccessForDoc");
         		serialNumber = conn.getCheckOutSerialNumber();
     			String sql = "SELECT * FROM xml_access WHERE docid = ?";
     			pstmt = conn.prepareStatement(sql);
-...
     			logMetacat.error(errorStr);
     			throw new AccessException(errorStr);
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("XMLAccessAccess.getXMLAccessForDoc - An error occurred "
     						+ "closing prepared statement: " + sqle.getMessage());
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}
-...
     		// first get the job from the db and put it into a DAO
     		PreparedStatement pstmt = null;
     		DBConnection conn = null;
     		int serialNumber = -1;
     		try {
     			conn = DBConnectionPool.getDBConnection("XMLAccessAccess.getXMLAccessForPrincipal");
         		serialNumber = conn.getCheckOutSerialNumber();
     			String sql = "SELECT * FROM xml_access WHERE docid = ? AND principal_name = ?";
     			pstmt = conn.prepareStatement(sql);
-...
     			logMetacat.error(errorStr);
     			throw new AccessException(errorStr);
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("XMLAccessAccess.getXMLAccessForPrincipal - An error occurred "
     						+ "closing prepared statement: " + sqle.getMessage());
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}
-...
     		// first get the job from the db and put it into a DAO
     		PreparedStatement pstmt = null;
     		DBConnection conn = null;
     		int serialNumber = -1;
     		try {
     			conn = DBConnectionPool.getDBConnection("XMLAccessAccess.getXMLAccessForPrincipal");
         		serialNumber = conn.getCheckOutSerialNumber();
     			String sql = "SELECT * FROM xml_access WHERE docid = ? AND principal_name = ? " +
     				"AND perm_type = ? AND perm_order = ?";
     			pstmt = conn.prepareStatement(sql);
-...
     			logMetacat.error(errorStr);
     			throw new AccessException(errorStr);
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("XMLAccessAccess.getXMLAccessForPrincipal - An error occurred "
     						+ "closing prepared statement: " + sqle.getMessage());
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}
-...
     	 *            permission order
     	 */
     	public void addXMLAccess(String docId, String principalName, Long permission, String permType,
     			String permOrder) throws AccessException {
     			String permOrder) throws AccessException, PermOrderException {
     		permOrderConflict(docId, permOrder);
     		Vector<XMLAccessDAO> xmlAccessList =
     			getXMLAccessForPrincipal(docId, principalName, permType, permOrder);
-...
+    		}
     		XMLAccessDAO xmlAccessDAO = xmlAccessList.get(0);
     		// if the permission on the xml access dao does not already contain the permission we are
     		//trying to add, update the access record with the existing permission bitwis OR-ed with our
     		// new permission
-...
     	 * @param xmlAccessList
     	 *            list of xml access dao objects that hold new access for the document
     	 */
     	public void setXMLAccess(String docId, Vector<XMLAccessDAO> xmlAccessList) throws AccessException {
     	public void replaceAccess(String docId, Vector<XMLAccessDAO> xmlAccessList) throws AccessException {
     		deleteXMLAccessForDoc(docId);
     		// if more than one record exists for this principal on this document with the same
-...
     					+ "xml access permissions for doc id: " + docId + ", principal: " +
     					principalName + ":" + sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("XMLAccessAccess.insertXMLAccess - SQL error when closing prepared " +
     						"statement after inserting xml access permissions for doc id: " + docId +
     						", principal: " +  principalName + ":" + sqle.getMessage());
     			} finally {
     				DBConnectionPool.returnDBConnection(conn, serialNumber);
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}
-...
     					+ "xml access permissions for doc id: " + docId + ", principal: " +
     					principalName + ":" + sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("XMLAccessAccess.updateXMLAccessPermission - SQL error when closing prepared " +
     						"statement after updating xml access permissions for doc id: " + docId +
     						", principal: " +  principalName + ":" + sqle.getMessage());
     			} finally {
     				DBConnectionPool.returnDBConnection(conn, serialNumber);
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}
-...
     			throw new AccessException("XMLAccessAccess.deleteXMLAccessForDoc - SQL error when deleting"
     					+ "xml access permissions for doc id: " + docId + ":" + sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("XMLAccessAccess.deleteXMLAccessForDoc - SQL error when closing prepared " +
     						"statement after deleting xml access permissions for doc id: " + ":" + sqle.getMessage());
     			} finally {
     				DBConnectionPool.returnDBConnection(conn, serialNumber);
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}
-...
     					+ "xml access permissions for doc id: " + docId + ", principal: " +
     					principalName + ":" + sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("XMLAccessAccess.deleteXMLAccessForPrincipal - SQL error when closing prepared " +
     						"statement after deleting xml access permissions for doc id: " + docId +
     						", principal: " +  principalName + ":" + sqle.getMessage());
     			} finally {
     				DBConnectionPool.returnDBConnection(conn, serialNumber);
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}
-...
     	 * @param principal
     	 *            principal credentials
     	 */
     	private void permOrderConflict(String docId, String permOrder) throws AccessException, PermOrderException {
     		if (docId == null) {
     			throw new AccessException("XMLAccessAccess.permOrderConflict - docid is required when " +
     					"determining perm order conflict");
+    		}
     		if (permOrder == null) {
     			throw new AccessException("XMLAccessAccess.permOrderConflict - perm order is required when " +
     					"determining perm order conflict");
+    		}
     	    PreparedStatement pstmt = null;
     		DBConnection conn = null;
     		int serialNumber = -1;
     		try {
     			// check out DBConnection
     			conn = DBConnectionPool.getDBConnection("XMLAccessAccess.permOrderConflict");
     			String sql = "SELECT * FROM xml_access WHERE docid = ? AND perm_order != ?";
     			pstmt = conn.prepareStatement(sql);
     			// Bind the values to the query
     			pstmt.setString(1, docId);
     			pstmt.setString(2, permOrder);
     			String sqlReport = "XMLAccessAccess.permOrderConflict - SQL: " + sql;
     			sqlReport += " [" + docId + "," + permOrder + "]";
     			logMetacat.info(sqlReport);
     			pstmt.execute();
     			ResultSet resultSet = pstmt.getResultSet();
     			if (resultSet.next()) {
     				throw new PermOrderException("XMLAccessAccess.addXMLAccess - cannot add permission " +
     					"record for doc id: " + docId + "with permOrder: " + permOrder + " due to permOrder conflict");
+    			}
     		} catch (SQLException sqle) {
     			throw new AccessException("XMLAccessAccess.permOrderConflict - SQL error when checking"
     					+ "for perm order conflict on: " + docId + ":" + sqle.getMessage());
     		} finally {
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}
     	/**
     	 * Delete xml access.  This removes all access records from the database for a principal
     	 * for a given document, perm type and perm order
     	 * @param docId
     	 *            document id
     	 * @param principal
     	 *            principal credentials
     	 */
     	private void deleteXMLAccessForPrincipal(String docId, String principalName, String permType, String permOrder) throws AccessException {
     		if (docId == null) {
     			throw new AccessException("XMLAccessAccess.deleteXMLAccessForPrincipal - docid is required when " +
-...
     		try {
     			// check out DBConnection
     			conn = DBConnectionPool.getDBConnection("XMLAccessAccess.deleteXMLAccessForPrincipal");
     			String sql = "DELETE FROM xml_access WHERE docid = ? AND principal_name = ?" +
     				"AND perm_type = ? AND perm_ord";
     			String sql = "DELETE FROM xml_access WHERE docid = ? AND principal_name = ? " +
     				"AND perm_type = ? AND perm_order = ?";
     			pstmt = conn.prepareStatement(sql);
     			// Bind the values to the query
-...
     					+ "xml access permissions for doc id: " + docId + ", principal: " +
     					principalName + ":" + sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("XMLAccessAccess.deleteXMLAccessForPrincipal - SQL error when closing prepared " +
     						"statement after deleting xml access permissions for doc id: " + docId +
     						", principal: " +  principalName + ":" + sqle.getMessage());
     			} finally {
     				DBConnectionPool.returnDBConnection(conn, serialNumber);
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}

     package edu.ucsb.nceas.metacat.shared;
     import java.sql.PreparedStatement;
     import java.sql.ResultSet;
     import java.sql.SQLException;
     import org.apache.log4j.Logger;
     import edu.ucsb.nceas.metacat.database.DBConnection;
     import edu.ucsb.nceas.metacat.database.DBConnectionPool;
     public abstract class BaseAccess {
     	protected DBConnection conn = null;
         protected int serialNumber = -1;
         protected ResultSet rs = null;
         protected BaseAccess(String methodName) throws AccessException {
             //check out DBConnection
         	try {
         		conn = DBConnectionPool.
                     	getDBConnection(methodName);
         		serialNumber = conn.getCheckOutSerialNumber();
         	} catch (SQLException sqle) {
         		throw new AccessException("Could not get connection from DB connection pool: "
         				+ sqle.getMessage());
+        	}
+        }
         protected abstract BaseDAO populateDAO(ResultSet resultSet) throws SQLException ;
         protected void closeDBObjects(PreparedStatement pstmt, DBConnection conn,
         		int serialNumber, Logger logMetacat) {
     		try {
     			if (pstmt != null) {
     				pstmt.close();
+    			}
     		} catch (SQLException sqle) {
     			logMetacat.error("BaseAccess.closeDBObjects - An error occurred "
     					+ "closing prepared statement: " + sqle.getMessage());
     		} finally {
     			DBConnectionPool.returnDBConnection(conn, serialNumber);
+    		}
+    	}
+    }

     import org.apache.log4j.Logger;
     import org.quartz.Job;
     import edu.ucsb.nceas.metacat.database.DBConnection;
     import edu.ucsb.nceas.metacat.database.DBConnectionPool;
     import edu.ucsb.nceas.metacat.shared.AccessException;
     import edu.ucsb.nceas.metacat.shared.BaseAccess;
-...
     	private Logger logMetacat = Logger.getLogger(ScheduledJobAccess.class);
     	// Constructor
     	public ScheduledJobAccess() throws AccessException {
     		super("ScheduledJobAccess");
+    	}
     	public ScheduledJobAccess() throws AccessException {}
     	/**
     	 * Get a job based on it's id
-...
     		// first get the job from the db and put it into a DAO
     		PreparedStatement pstmt = null;
     		DBConnection conn = null;
     		int serialNumber = -1;
     		try {
     			conn = DBConnectionPool.getDBConnection("ScheduledJobAccess.getJob");
         		serialNumber = conn.getCheckOutSerialNumber();
     			String sql = "SELECT * FROM scheduled_job WHERE id = ? AND status != 'deleted'";
     			pstmt = conn.prepareStatement(sql);
-...
     			throw new AccessException("ScheduledJobAccess.getJob - SQL error when getting scheduled job: "
     					+ jobId  + " : "  + sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("ScheduledJobAccess.getJob - An error occurred "
     						+ "closing prepared statement: " + sqle.getMessage());
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
     		// Now get all the job parameters and put those into a list of job parameter
-...
     		// first get the job from the db and put it into a DAO
     		PreparedStatement pstmt = null;
     		DBConnection conn = null;
     		int serialNumber = -1;
     		try {
     			conn = DBConnectionPool.getDBConnection("ScheduledJobAccess.getJobByName");
         		serialNumber = conn.getCheckOutSerialNumber();
     			String sql = "SELECT * FROM scheduled_job WHERE name = ? AND status != 'deleted'";
     			pstmt = conn.prepareStatement(sql);
-...
     			throw new AccessException("ScheduledJobAccess.getJobByName - SQL error when getting scheduled job by name: "
     					+ jobName  + " : "  + sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("ScheduledJobAccess.getJobByName - An error occurred "
     						+ "closing prepared statement: " + sqle.getMessage());
     			} finally {
     				DBConnectionPool.returnDBConnection(conn, serialNumber);
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
     		// Now get all the job parameters and put those into a list of job parameter
-...
     		// Get all jobs where the status is not deleted
     		PreparedStatement pstmt = null;
     		DBConnection conn = null;
     		int serialNumber = -1;
     		try {
     			conn = DBConnectionPool.getDBConnection("ScheduledJobAccess.getAllJobs");
         		serialNumber = conn.getCheckOutSerialNumber();
     			String sql = "SELECT * FROM scheduled_job WHERE status != 'deleted'";
     			if (groupName != null) {
     				sql += " AND group_name = ?" ;
-...
     			throw new AccessException("ScheduledJobAccess.getJobByName - SQL error when getting all jobs : "
     					+ sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("ScheduledJobAccess.getAllJobs - An error occurred "
     						+ "closing prepared statement: " + sqle.getMessage());
     			} finally {
     				DBConnectionPool.returnDBConnection(conn, serialNumber);
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}
-...
     	 */
     	public void createJob(ScheduledJobDAO jobDAO, HashMap<String, String> jobParams) throws AccessException {
     		PreparedStatement pstmt = null;
     		DBConnection conn = null;
     		int serialNumber = -1;
     		// First insert the job
     		try {
     			conn = DBConnectionPool.getDBConnection("ScheduledJobAccess.createJob");
         		serialNumber = conn.getCheckOutSerialNumber();
     			String sql =
     				"INSERT INTO scheduled_job (date_created, date_updated, status, name, trigger_name, group_name, class_name, start_time, end_time, interval_value, interval_unit) "
     				+ "VALUES(now(), now(), ?, ?, ?, ?, ?, ?, ?, ?, ?)";
-...
     			throw new AccessException("ScheduledJobAccess.createJob - SQL error when creating scheduled job "
     					+ jobDAO.getName()  + " : "  + sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("ScheduledJobAccess.createJob - An error occurred "
     						+ "closing prepared statement: " + sqle.getMessage());
     			} finally {
     				DBConnectionPool.returnDBConnection(conn, serialNumber);
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
     		// Then iterate through the job params and insert them into the db
-...
+    		}
     		PreparedStatement pstmt = null;
     		DBConnection conn = null;
     		int serialNumber = -1;
     		try {
     			conn = DBConnectionPool.getDBConnection("ScheduledJobAccess.createJob");
         		serialNumber = conn.getCheckOutSerialNumber();
     			String sql = "UPDATE scheduled_job SET status = ? WHERE id = ?";
     			pstmt = conn.prepareStatement(sql);
-...
     			throw new AccessException("ScheduledJobAccess.deleteJob - SQL error when "
     					+ "deleting scheduled job " + jobDAO.getName()  + " : "  + sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("ScheduledJobAccess.updateJobStatus - An error occurred "
     						+ "closing prepared statement: " + sqle.getMessage());
     			} finally {
     				DBConnectionPool.returnDBConnection(conn, serialNumber);
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}

     import org.apache.log4j.Logger;
     import edu.ucsb.nceas.metacat.database.DBConnection;
     import edu.ucsb.nceas.metacat.database.DBConnectionPool;
     import edu.ucsb.nceas.metacat.shared.AccessException;
     import edu.ucsb.nceas.metacat.shared.BaseAccess;
-...
     	private Logger logMetacat = Logger.getLogger(ScheduledJobParamAccess.class);
     	// Constructor
     	public ScheduledJobParamAccess() throws AccessException {
     		super("ScheduledJobParamsAccess");
+    	}
     	public ScheduledJobParamAccess() throws AccessException {}
     	/**
     	 * Insert job parameters into the database.
-...
     			jobParamsDAO.setValue(jobParams.get(paramKey));
     			PreparedStatement pstmt = null;
     			DBConnection conn = null;
     			int serialNumber = -1;
     			try {
     				conn = DBConnectionPool.getDBConnection("ScheduledJobParamAccess.createJobParams");
     	    		serialNumber = conn.getCheckOutSerialNumber();
     				String sql =
     					"INSERT INTO scheduled_job_params (date_created, date_updated, status, job_id, key, value) "
     					+ "VALUES(now(), now(), ?, ?, ?, ?)";
-...
     				throw new AccessException("ScheduledJobParamsAccess.createJobParams - SQL error when creating scheduled job parameter : "
     					 + sqle.getMessage());
     			} finally {
     				try {
     					if (pstmt != null) {
     						pstmt.close();
+    					}
     				} catch (SQLException sqle) {
     					logMetacat.error("ScheduledJobParamAccess.createJobParams - An error occurred "
     							+ "closing prepared statement: " + sqle.getMessage());
     				} finally {
     					DBConnectionPool.returnDBConnection(conn, serialNumber);
+    				}
     				closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    			}
+    		}
+    	}
-...
     	 */
     	protected void deleteJobParams(Long jobId) throws AccessException {
     		PreparedStatement pstmt = null;
     		DBConnection conn = null;
     		int serialNumber = -1;
     		// change the status to deleted
     		try {
     			conn = DBConnectionPool.getDBConnection("ScheduledJobParamAccess.deleteJobParams");
         		serialNumber = conn.getCheckOutSerialNumber();
     			String sql = "UPDATE scheduled_job_params SET status = ? WHERE jobId = ?";
     			pstmt = conn.prepareStatement(sql);
-...
     			throw new AccessException("ScheduledJobParamsAccess.deleteJobParams - SQL error "
     					+ "when deleting scheduled job params for job" + jobId  + " : "  + sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("ScheduledJobParamsAccess.deleteJobParams - An error occurred "
     						+ "closing prepared statement: " + sqle.getMessage());
     			} finally {
     				DBConnectionPool.returnDBConnection(conn, serialNumber);
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}
-...
     		// Get the job parameters for the job id
     		PreparedStatement pstmt = null;
     		DBConnection conn = null;
     		int serialNumber = -1;
     		ScheduledJobParamDAO jobParamDAO = null;
     		try {
     			conn = DBConnectionPool.getDBConnection("ScheduledJobParamAccess.getJobParamsForJobId");
         		serialNumber = conn.getCheckOutSerialNumber();
     			String sql = "SELECT * FROM scheduled_job_params WHERE job_id = ? AND status != 'deleted'";
     			pstmt = conn.prepareStatement(sql);
-...
     			throw new AccessException("ScheduledJobAccess.getJobParamsForJobId - SQL error when getting "
     					+ "scheduled job parameter for job id: " + jobId  + " : "  + sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("ScheduledJobParamAccess.getJobParamsForJobId - An error occurred "
     						+ "closing prepared statement: " + sqle.getMessage());
     			} finally {
     				DBConnectionPool.returnDBConnection(conn, serialNumber);
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}
-...
     		// get all job parameters
     		PreparedStatement pstmt = null;
     		DBConnection conn = null;
     		int serialNumber = -1;
     		ScheduledJobParamDAO jobParamDAO = null;
     		try {
     			conn = DBConnectionPool.getDBConnection("ScheduledJobParamAccess.getAllJobParams");
         		serialNumber = conn.getCheckOutSerialNumber();
     			String sql = "SELECT * FROM scheduled_job_params WHERE status != 'deleted'";
     			pstmt = conn.prepareStatement(sql);
-...
     			throw new AccessException("ScheduledJobParamAccess.getAllJobParams - SQL error when getting "
     					+ "scheduled job parameters : "  + sqle.getMessage());
     		} finally {
     			try {
     				if (pstmt != null) {
     					pstmt.close();
+    				}
     			} catch (SQLException sqle) {
     				logMetacat.error("ScheduledJobParamsAccess.getAllJobParams - An error occurred "
     						+ "closing prepared statement: " + sqle.getMessage());
     			} finally {
     				DBConnectionPool.returnDBConnection(conn, serialNumber);
+    			}
     			closeDBObjects(pstmt, conn, serialNumber, logMetacat);
+    		}
+    	}

Also available in: Unified diff

Project

General

Profile

Metacat

Revision 5108

Added by daigle over 14 years ago