abstractdao.java

发泄网！ 发泄网！ 发泄网！ 发泄网！ 发泄网！ 发泄网！ 发泄网！ 发泄网！

	/* CRMS, customer relationship management system
	Copyright (C) 2003  Service To Youth Council

	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

	For further information contact the SYC ICT department on [e-mail]
	98 Kermode Street
	North Adelaide
	South Australia
	SA 5006 
	[phone]
	*/
package crms.dao;

import java.sql.*;
import crms.util.*;
import org.apache.log4j.Logger;
import java.util.*;
import java.io.*;
import java.net.*;

/**
 * <p>An AbstractDAO is the base abstract class that all other Data Access
 * Objects will extend. A Data Access object is one which is responsible
 * for wrapping all operations (and data conversions) from a data source
 * into an application.</p>
 *
 * @author  dmurphy
 */
public abstract class AbstractDAO {

	static Logger logger = Logger.getLogger(AbstractDAO.class);
	public static String SUPER_USER = "superuser";
	
	private DAOFactory factory = null;
	private static String PARAM_ENTITY_PERMISSION = "entity.permission";

	public static java.text.SimpleDateFormat df = new java.text.SimpleDateFormat("d/M/yyyy h:mm a");
	
	/** Creates a new instance of AbstractDAO */
	public AbstractDAO() {
	}
	
	
	public static void processQuery(String sql, ResultSetAction action) {
	 
		 Connection con = null;
		 ResultSet rs = null;
		 Statement stmt = null;
		 
		 try {
				
				con = DAOFactory.getInstance().getConnection();
		 
				stmt = con.createStatement();
				
				logger.debug("Executing query...");
				logger.debug(sql);
				
				rs = stmt.executeQuery(sql);
				
				while (rs.next()) {
					action.performAction(rs);
				}
				
		 }
		 catch (Exception ex) {
				System.out.println(sql);
				throw new RuntimeException(ex);
		 }
		 finally {
				try {
					if (rs != null) {
						 rs.close();
					}

					if (stmt != null) {
						 stmt.close();
					}

					if (con != null) {
						 con.close();
					}	
				}
				catch (SQLException ex) {
					throw new RuntimeException(ex);
				}
		 }
	}
	
	public List executeQuery(String sql) {
		return executeQuery(sql, null);
	}
	public List executeQuery(String sql, Object data) {
		
		ArrayList results = new ArrayList();
		
		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
		
		try {
				con = getFactory().getInstance().getConnection();
				
				stmt = con.createStatement();
				
				logger.debug("Executing query:");
				logger.debug(sql);
				
				rs = stmt.executeQuery(sql);
				
				while (rs.next()) {
					results.add(createFromResultSet(rs, data));
				}
				
		}
		catch (Exception ex) {
				System.out.println(sql);
				throw new RuntimeException(ex);
		}
		finally {
				try {
					if (rs != null) {
						rs.close();
					}

					if (stmt != null) {
						stmt.close();
					}

					if (con != null) {
						con.close();
					}	
				}
				catch (SQLException ex) {
					throw new RuntimeException(ex);
				}
		}
		return results;
	}

	/** Create the object from a result set */	
	public abstract Object createFromResultSet(ResultSet rs) throws SQLException, UnsupportedEncodingException;

	public Object createFromResultSet(ResultSet rs, Object data) throws SQLException, UnsupportedEncodingException {
		return createFromResultSet(rs);
	}
	
	
	/**
	 * <p>Sets an instance of a DAOFactory object onto this concrete DAO.</p>
	 * <p>The DAOFactory is responsible for handling the actual database
	 * connections (and/or connection pooling) that the concrete Data
	 * Access Will use.</p>
	 * @param factory Instance of DAOFactory object to associate with this DAO.
	 */
	
	public void setFactory(DAOFactory factory) {
		this.factory = factory;
	}
	
	/**
	 * <p>Gets the Data Access Object factory associated with this Data Access
	 * Object. That is, the DAOFactory that created this DAO.</p>
	 * @return DAOFactory object.
	 */
	public DAOFactory getFactory() {
		return this.factory;
	}
	
	public String quote(String name) {
		return "\"" + name + "\"";
	}

	static public String escape(String string) {
		if (string == null) return "";
		return string.replaceAll("'", "\\\\'");
	}	
	public String quoteSingle(String value) {
		if (value == null) return "''";
		return "'" + escape(value) + "'";
	}
	
	public int updateSQL(String sql) throws Exception {
		return updateSQL(sql, null);
	}
	
	public int updateSQL(String sql, Connection newCon) throws Exception {
		
		logger.debug("UpdateSQL : " + sql);
		
		Connection con = null;

		Statement stmt = null;
		int result = -1;

		try {
			if (newCon == null) {
				con = factory.getConnection();
			} else {
				con = newCon;
			}
			stmt = con.createStatement();
				
			result = stmt.executeUpdate(sql);

		} catch (SQLException ex) {
				throw new Exception(ex);
		} finally {
				if (stmt != null) {
					stmt.close();
				}
				if (newCon == null && con != null) {
					con.close();
				}
		}
		
		return result;
		
	}

	public long insertSQL(String sql, Connection newCon) throws Exception {
		
		logger.debug("UpdateSQL : " + sql);
		
		Connection con = null;

		Statement stmt = null;
		long result = -1;

		try {
			if (newCon == null) {
				con = factory.getConnection();
			} else {
				con = newCon;
			}
			stmt = con.createStatement();
				
			result = stmt.executeUpdate(sql);

			ResultSet rs = stmt.getGeneratedKeys();
			if (rs.next()) {
				result = rs.getLong( 1 );
			}
		} catch (SQLException ex) {
				throw new Exception(ex);
		} finally {
				if (stmt != null) {
					stmt.close();
				}
				if (newCon == null && con != null) {
					con.close();
				}
		}
		
		return result;
		
	}
	
	
	public HashMap getMetaData(String contactType, int id) {
		
		String sql = "SELECT *\n";
		sql += "  FROM \"DataStore\"\n";
		sql += " WHERE \"ContactType\" = " + quoteSingle(contactType.trim());
		sql += "	AND \"ContactNumber\" = " + quoteSingle(String.valueOf(id));

		logger.debug("Executing sql:");
		logger.debug(sql);
		
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		HashMap metaData = new HashMap();
		
		try {
				con = getFactory().getInstance().getConnection();
				stmt = con.createStatement();
				
				rs = stmt.executeQuery(sql);
				
				while (rs.next()) {
					String type = rs.getString("ContactValueType");
					String result = URLDecoder.decode(rs.getString("ContactValue"), "UTF-8");
					metaData.put(type, result);
					logger.debug("Added meta data " + type + " = " + result);
				}
		}
		catch (Exception ex) {
				
		}
		finally {
				try {
					if (rs != null) {
						rs.close();
					}

					if (stmt != null) {
						stmt.close();
				}

				if (con != null) {
					con.close();
				}
			}
			catch (SQLException ex) {
				logger.fatal(ex);
				throw new RuntimeException(ex);
			}
		}
		return metaData;
		
	}
	
	public String getMetaData(String contactType, int id, String valueType) {
		return getMetaData(contactType,  String.valueOf(id), valueType);
	}
	
	/**
	 *  Loads the first matching meta data item for a contact or organisation from the 'DataStore'
	 *  database table.
	 */
	public String getMetaData(String contactType, String id, String valueType) {
		ArrayList data = getMetaDataList(contactType, id, valueType);
		if (data.size() > 0) {
			return (String)data.get(0);
		}

		return null;
	}
	/**
	 *  Loads multiple meta data items for a contact or organisation from the 'DataStore'
	 *  database table.
	 */
	public ArrayList getMetaDataList(String contactType, String id, String valueType) {
		String sql = "SELECT *\n";
		sql += "  FROM \"DataStore\"\n";
		sql += " WHERE \"ContactType\" = " + quoteSingle(contactType.trim());
		sql += "	AND \"ContactNumber\" = " + quoteSingle(id);
		sql += "	AND \"ContactValueType\" = " + quoteSingle(valueType);
		
		logger.debug("Executing sql:");
		logger.debug(sql);
		
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList result = new ArrayList();
		
		try {
			con = getFactory().getInstance().getConnection();
			stmt = con.createStatement();
			
			rs = stmt.executeQuery(sql);
			
			while (rs.next()) {
				result.add(decode(rs.getString("ContactValue")));
			}

			logger.debug("Found " + result.size() + " entries for " + valueType);
		}
		catch (Exception ex) { }
		finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (con != null) {
					con.close();
				}
			}