abstractdao.java

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

			catch (SQLException ex) {
				logger.fatal(ex);
				throw new RuntimeException(ex);
			}
		}
		return result;
	}
	
	/**
	 *  <p>Inserts or Updates (intelligently, based on whether the row already
	 *  exists or not meta data defined by the provided parameters. This data
	 *  is stored in the 'DataStore' table.
	 *
	 *  <p><b>Note:</b> A null contactValue will cause the row of meta data to
	 *  be deleted from the database.</p>
	 */
	
	
	public void updateMetaData(String contactNumber, String contactType, String contactValue, String contactValueType) throws Exception {
		updateMetaData(null, contactNumber, contactType, contactValue, contactValueType);
	}

	public void updateMetaData( String contactNumber, AbstractCode contactType, String contactValue, AbstractCode contactValueType) throws Exception {
		updateMetaData(null, contactNumber, contactType.getCode(), contactValue, contactValueType.getCode());
	}

	public void updateMetaData( String contactNumber, AbstractCode contactType, AbstractCode contactValue, AbstractCode contactValueType) throws Exception {
		if (contactValue == null) {
			updateMetaData(null, contactNumber, contactType.getCode(), (String)null, contactValueType.getCode());
		}
		else {
			updateMetaData(null, contactNumber, contactType.getCode(), contactValue.getCode(), contactValueType.getCode());
		}
	}
	
	public void updateMetaData(Connection newCon, String contactNumber, AbstractCode contactType, String contactValue, AbstractCode contactValueType) throws Exception {
		if (contactValue == null) {
			updateMetaData(newCon, contactNumber, contactType.getCode(), (String)null, contactValueType.getCode());
		} else {
			updateMetaData(newCon, contactNumber, contactType.getCode(), contactValue, contactValueType.getCode());
		}
	}

	public void updateMetaData( Connection newCon, String contactNumber, AbstractCode contactType, AbstractCode contactValue, AbstractCode contactValueType) throws Exception {
		if (contactValue == null) {
			updateMetaData(newCon, contactNumber, contactType.getCode(), (String)null, contactValueType.getCode());
		} else {
			updateMetaData(newCon, contactNumber, contactType.getCode(), contactValue.getCode(), contactValueType.getCode());
		}
	}

	/** Update the meta-data for an object that has multiple values.  This uses the naive method of delete * matching, insert all the values in
	 * the arraylist.  It uses the transaction support to hopefully make loss of values impossible.
	 */
	public void updateMetaData(Connection newCon, String contactNumber, String contactType, ArrayList contactValueList, String contactValueType) throws Exception {
		Connection con = null;
		Statement stmt = null;
		boolean rowExists = false;
		try {
			if (newCon == null) {
				con = getFactory().getConnection();
			} else {
				con = newCon;
			}

			con.setAutoCommit(false);
			stmt = con.createStatement();
			
			String sql = "DELETE FROM \"DataStore\"\n";
			sql += "WHERE \"ContactNumber\"=" + quoteSingle(contactNumber) + "\n";
			sql += "  AND \"ContactType\"=" + quoteSingle(contactType) + "\n";
			sql += "  AND \"ContactValueType\"=" + quoteSingle(contactValueType) + "\n";
			
			stmt.executeUpdate(sql);

			String contactValue = null;
			Iterator i = (contactValueList != null) ? contactValueList.iterator() : null; 
			while (i != null && i.hasNext()) {
				contactValue = (String)i.next();
				sql = "INSERT into \"DataStore\"\n";
				sql += "(\"ContactNumber\", \"ContactType\",\"ContactValue\",\"ContactValueType\")\n";
				sql += "VALUES\n";
				sql += "(";
				sql += quoteSingle(contactNumber) + ", ";
				sql += quoteSingle(contactType) + ", ";
				sql += quoteSingle(encode(contactValue)) + ", ";
				sql += quoteSingle(contactValueType) + ")\n";
				stmt.executeUpdate(sql);
			}
			con.commit();
		 }
		 catch (SQLException ex) {
				throw new Exception(ex);
		 }
		 finally {
				if (stmt != null) {
					stmt.close();
				}
				if (newCon == null && con != null) {
					con.close();
				}
		 }
		
	}
	public void updateMetaData(Connection newCon, String contactNumber, String contactType, String contactValue, String contactValueType) throws Exception {
		Connection con = null;
		Statement stmt = null;
		boolean rowExists = false;
		try {
				if (newCon == null) {
					con = getFactory().getConnection();
				} else {
					con = newCon;
				}

				stmt = con.createStatement();
				
				String sql = "SELECT 1 FROM \"DataStore\"\n";
				sql += "WHERE \"ContactNumber\"=" + quoteSingle(contactNumber) + "\n";
				sql += "  AND \"ContactType\"=" + quoteSingle(contactType) + "\n";
				sql += "  AND \"ContactValueType\"=" + quoteSingle(contactValueType) + "\n";
				
				ResultSet rs = stmt.executeQuery(sql);
				if (rs.next()) {
					rowExists = true;
				}
				rs.close();
				
				if (contactValue != null) {
					if (rowExists) {
						// Update Row
						sql = "UPDATE \"DataStore\"\n";
						sql += "	SET \"ContactValue\"=" + quoteSingle(encode(contactValue)) + "\n";
						sql += "WHERE \"ContactNumber\"=" + quoteSingle(contactNumber) + "\n";
						sql += "  AND \"ContactType\"=" + quoteSingle(contactType) + "\n";
						sql += "  AND \"ContactValueType\"=" + quoteSingle(contactValueType) + "\n";

					} else {
						// Insert Row

						sql = "INSERT into \"DataStore\"\n";
						sql += "(\"ContactNumber\", \"ContactType\",\"ContactValue\",\"ContactValueType\")\n";
						sql += "VALUES\n";
						sql += "(";
						sql += quoteSingle(contactNumber) + ", ";
						sql += quoteSingle(contactType) + ", ";
						sql += quoteSingle(encode(contactValue)) + ", ";
						sql += quoteSingle(contactValueType) + ")\n";
					}
				} else {
					// Delete Row
					sql = "DELETE from \"DataStore\"\n";
					sql += "WHERE \"ContactNumber\"=" + quoteSingle(contactNumber) + "\n";
					sql += "  AND \"ContactType\"=" + quoteSingle(contactType) + "\n";
					sql += "  AND \"ContactValueType\"=" + quoteSingle(contactValueType) + "\n";
				}
				
				int rowsUpdated = updateSQL(sql,con);
				
//				if (rowsUpdated != 1) {
//					throw new RuntimeException("Invalid number of rows updated. Should be 1, was " + rowsUpdated);
//				}
		 
		 }
		 catch (SQLException ex) {
				throw new Exception(ex);
		 }
		 finally {
				if (stmt != null) {
					stmt.close();
				}
				if (newCon == null && con != null) {
					con.close();
				}
		 }
	}
	
	public boolean entityExists(String sql) throws Exception {
		 Connection con = getFactory().getConnection();
		 Statement stmt = null;
		 boolean result = false;
		 
		 try {
		 
				stmt = con.createStatement();
				
				ResultSet rs = stmt.executeQuery(sql);
				
				if (rs.next()) {
					result = true;
				}
				rs.close();
		 }
		 catch (SQLException ex) {
				throw new Exception(ex);
		 }
		 finally {
				if (stmt != null) {
					stmt.close();
				}
				
				if (con != null) {
					con.close();
				}
		 }
		 return result;
	}
	
	public String encode(String toEncode) {
		 if (toEncode == null) {
				return null;
		 }
		 try {
				return java.net.URLEncoder.encode(toEncode, "UTF8").trim();
		 }
		 catch (java.io.UnsupportedEncodingException ex) {
				throw new RuntimeException(ex);
		 }
	}
	
	public static String decode(String toDecode) {
		 try {
			if (toDecode == null) { return ""; }
				return java.net.URLDecoder.decode(toDecode, "UTF8").trim();
		 }
		 catch (java.io.UnsupportedEncodingException ex) {
				throw new RuntimeException(ex);
		 }
	}

	
	/**
	 * <p>Used by createXMLResultset to insert a particular column value in the
	 * XML to be returned to the client.</p>
	 * <p>Override this method to provide special conversion for special fields
	 * into the XML, for example to URLDecode values (free text fields)</p>
	 * @param colName Database name of column being converted
	 * @param columnClass Java class name of column being converted
	 * @param value Java object representing the column value
	 * @return Converted value as String
	 */
	public static String convertColumnValue(String colName, String columnClass, Object value) {
		
		if (value != null) {
				if (value instanceof java.util.Date) {
				 return df.format((java.util.Date) value);  
				} else if (value instanceof java.lang.String) {
					return decode((String)value);	
				}
				else
				{
					return value.toString().trim();
				}
		 } else {
				return null;
		 }
	}
	
	 /**
	 * Creates an XML document in a StringBuffer that represents the ResultSet
	 * returned from the database.
	 *
	 * @param collectionName Sets the tag text to use if this query will return a collection of objects. This
	 * XML tag will be the parent of any item nodes of the collection.
	 * @param elementName The XML tag to use to name each element that is created as a result of
	 * the data produced.
	 * @param sql The SQL query used to produce the data from the server.
	 * @return A StringBuffer object containing the automatically created XML from the result
	 * of the database query. Note that database field names are used to create
	 * the inner tag names used in this XML.
	 */
	public static StringBuffer createXMLResultset(String collectionName, String elementName, String sql) {

		 StringBuffer buf = new StringBuffer();
		 ResultSet rs = null;
		 Statement stmt = null;
		 Connection con = null;

		 try {
				con = DAOFactory.getInstance().getConnection();

				/**
				*The following set of statements are synchronised on
				*the database connection object to avoid two queries
				*running at the same time on the one connection.
				*(Connection Pooling will fix this).
				*/

				stmt = con.createStatement();

				rs = stmt.executeQuery(sql);
				if (rs !=  null) {

					/**
					 * A note about the ResultSetMetaData:
					 * - only call each method once - subsequent
					 * calls will cause a Column Index out of Range
					 * Postgres error.
					 * Also, column numbers are indexed from 1, not 0.
					 */
					ResultSetMetaData meta = rs.getMetaData();

					if (collectionName != null) {
						 buf.append("	 <" + collectionName + ">\n");
					}
					while (rs.next()) {

						 if (elementName != null) {
								buf.append("		 <" + elementName + ">\n");
						 }

						 int columnCount = meta.getColumnCount();

						 for (int i=0; i < columnCount; i++) {
								String colName = meta.getColumnName(i+1);
								String tableName = meta.getTableName(i+1);

								buf.append("				<column name='" + colName + "'>");

								String columnClass = meta.getColumnClassName(i+1);
								if (tableName.equals("DataStore")) {
									buf.append( decode((String) rs.getObject(i+1)));
								} else {
									buf.append(convertColumnValue(colName, columnClass, rs.getObject(i+1)));
								}

								buf.append("</column>\n");
						 }
						 if (elementName != null) {
								buf.append("	</" + elementName + ">\n");
						 }
					}

					if (collectionName != null) {
						 buf.append("	 </" + collectionName + ">\n");
					}
				}
		 }
		 catch (SQLException ex) {
				ex.printStackTrace();
				throw new RuntimeException( ex.getMessage() ) ;
		 }
		 finally {
				try {
					if (rs != null) {
						 rs.close();
					}
					if (stmt != null) {
						 stmt.close();
					}
					if (con != null) {
						 con.close();
					}
				}
				catch (SQLException ex) {
					ex.printStackTrace();
					throw new RuntimeException( ex.getMessage() ) ;
				}

		 }

		 return buf;
	}

	/** Automatically append where or and when adding criteria to a query. 
	* At the start of the SQL generation function define a variable:<br>
	* <code> boolean used = false;<br>
	* StringBuffer sql = new StringBuffer("SELECT *\n");</code>
	* Call using something like:<br>
	* <code> used = criteria(used, sql);</code>
	*
	* @param used	Whether the <code>where</code> statement has been previosuly used.
	* @param sql	A StringBuffer which is the SQL statement as it currently stands
	* @return true always
	*/
	protected boolean criteria(boolean used, StringBuffer sql) {
		if (used) {
			sql.append("AND ");
		} else {
			sql.append("WHERE ");
		}
		return true;
	}
}