/**
 * 
 */
package ch.titouille.app;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
 
/**
 * @author titouille
 *
 */
public class ConnectionHelper
{
	/**
	 * url of the db server
	 */
	private String url;
	/**
	 * username to connect on the db
	 */
	private String username;
	/**
	 * password to connect on the db
	 */
	private String password;
	/**
	 * instance of the connection helper
	 */
	private static ConnectionHelper instance;
 
	/**
	 * @author titouille
	 * initialize basic variables
	 */
	//private ConnectionHelper()
	private ConnectionHelper()
	{
		try 
		{
			// initialization of basic variables
			Class.forName("com.mysql.jdbc.Driver");
			this.url = "jdbc:mysql://localhost/test";
			this.username = "root";
			this.password = "test";
		} 
		catch( Exception e ) 
		{
			e.printStackTrace();
		}
	}
	/**
	 * @author titouille
	 * return the singleton ConnectionHelper instance
	 * @return ConnectionHelper
	 */
	public static ConnectionHelper getInstance()
	{
		if( instance == null )
			instance = new ConnectionHelper();
		return instance;
	}
	/**
	 * @author titouille
	 * get the db connection to execute queries
	 * @return Connection
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException 
	{
		// test if instance exists
		if( instance == null ) 
		{
			instance = new ConnectionHelper();
		}
		try 
		{
			// get db connection
			return DriverManager.getConnection( instance.url, instance.username, instance.password );
		} 
		catch( SQLException e ) 
		{
			throw e;
		}
	}
	/**
	 * @author titouille
	 * close the db connection
	 * @param connection
	 */
	public static void close( Connection connection )
	{
		try 
		{
			if( connection != null ) 
			{
				connection.close();
			}
		} 
		catch( SQLException e ) 
		{
			e.printStackTrace();
		}
	}
}

package ch.titouille.app;
/**
 * 
 * @author titouille
 *
 */
public class DAOException extends RuntimeException
{
	/**
	 * serial version uid
	 */
	static final long serialVersionUID = -1881205326938716446L;
 
	/**
	 * @author titouille
	 * throw DAO exception
	 * @param message
	 */
	public DAOException(String message)
	{
		super(message);
	}
 
	/**
	 * @author titouille
	 * throw DAO exception
	 * @param cause
	 */
	public DAOException(Throwable cause)
	{
		super(cause);
	}
 
	/**
	 * @author titouille
	 * throw DAO exception
	 * @param message
	 * @param cause
	 */
	public DAOException(String message, Throwable cause)
	{
		super(message, cause);
	}
 
}

/**
 * 
 */
package ch.titouille.app.vo;
 
import org.w3c.dom.Document;
 
 
/**
 * @author titouille
 *
 */
public class BuildingVO
{
	private int id;
	private Document xmlDoc;
 
	/**
	 * @return the id
	 */
	public int getId()
	{
		return id;
	}
 
	/**
	 * @param id the id to set
	 */
	public void setId(int id)
	{
		this.id = id;
	}
 
	/**
	 * @return the xmlDoc
	 */
	public Document getXmlDoc()
	{
		return xmlDoc;
	}
 
	/**
	 * @param xmlDoc the xmlDoc to set
	 */
	public void setXmlDoc(Document xmlDoc)
	{
		this.xmlDoc = xmlDoc;
	}	
}

package ch.titouille.app.vo
{
	import flash.xml.XMLDocument;
 
	[Managed]
	[RemoteClass(alias="ch.titouille.app.vo.BuildingVO")]
	public class BuildingVO
	{
		private var _id:uint;
		private var _xmlDoc:XML;
 
		public function BuildingVO()
		{
		}
 
		public function get id():uint
		{
			return this._id;
		}
		public function set id( value:uint ):void
		{
			this._id = value;
		}
		public function get xmlDoc():XML
		{
			return this._xmlDoc;
		}
		public function set xmlDoc( value:XML ):void
		{
			this._xmlDoc = value;
		}
		public function get xmlList():XMLList
		{
			return this._xmlDoc.children();
		}
	}
}

/**
 * @author titouille
 */
package ch.titouille.app.assemblers;
 
 
import org.w3c.dom.Document;
 
import flex.data.assemblers.AbstractAssembler;
import flex.messaging.io.ArrayCollection;
 
 
import ch.titouille.app.vo.BuildingVO;
import ch.titouille.app.dao.BuildingDAO;
 
 
/**
 * @author titouille
 *
 */
public class BuildingAssembler extends AbstractAssembler
{
	/**
	 * @author titouille
	 * get buildings / apparts from db switch ownerid
	 * @param actorid
	 * @return
	 */
	public ArrayCollection fill( Integer ownerid )
	{
		// create new RoomDAO instance
		BuildingDAO bdao = new BuildingDAO();
 
		// get document
		Document x = bdao.getBuildings( ownerid );
 
		// create new returned ArrayCollection
		ArrayCollection a = new ArrayCollection();
 
		// create new BuildingVO instance and add id and xml document into
		BuildingVO o = new BuildingVO();
		o.setId(1);
		o.setXmlDoc( x );
 
		// add BuildingVO instance into ArrayCollection
		a.add( o );
 
		return a;
	}
}

/**
 * 
 */
package com.neolinkup.app.dao;
 
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.CharacterIterator;
import java.text.StringCharacterIterator;
 
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
 
import org.w3c.dom.Document;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
 
import com.neolinkup.app.ConnectionHelper;
import com.neolinkup.app.DAOException;
 
 
/**
 * @author titouille
 *
 */
public class BuildingDAO
{
	/**
	 * @author titouille
	 * get buildings for a specific owner
	 * @param actorid
	 * @return
	 */
	public Document getBuildings( Integer ownerid )
	{
		// initialization of basic variables
		Connection c = null;
		String s = "<buildings>";
		try
		{
			// get connection
			c = ConnectionHelper.getConnection();
 
			// create statement to execute query
			Statement s1 = c.createStatement();
 
			// get all cinema for a specific user
			ResultSet rs1 = s1.executeQuery("SELECT buildingid, buildingname FROM building where ownerid=" + ownerid.toString() + " order by buildingname;" );
 
			// if resultSet is not empty
			while( rs1.next() )
			{
				// open building tag and add each building into XML structure
				s += "<building id='"+ rs1.getInt("buildingid") + "' label='"+ RoomDAO.forXML( rs1.getString("buildingname") ) +"'>";
 
				// create statement to execute query
				Statement s2 = c.createStatement();
 
				// get all appartments from current building
				ResultSet rs2 = s2.executeQuery( "Select appartid, appartname from appart where appartid=" + rs1.getInt("buildingid") + " order by appartweight;" );
 
				// if resultSet is not empty
				while( rs2.next() )
				{
					// add each appartment into current building hierarchy 
					s += "<appart id='" + rs2.getInt( "appartid" ) + "' label='" + RoomDAO.forXML( rs2.getString( "appartname" ) ) + "'/>";
				}
				// close building tag
				s += "</building>";
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			throw new DAOException(e);
		}
		finally
		{
			ConnectionHelper.close(c);
		}
		// close root tag
		s += "</buildings>";
 
		return this._getXMLDocument( s );
	}

	/**
	 * @author titouille
	 * escape special characters like <, >, ', ", & for XML
	 * @param aText
	 * @return String escaped string
	 */
	public static String forXML(String aText)
	{
		String result = new String();
		final StringCharacterIterator iterator = new StringCharacterIterator(aText);
		char character =  iterator.current();
		while (character != CharacterIterator.DONE )
		{
			switch( character )
			{
				case '<' :	result += "&lt;";		break;
				case '>' :	result += "&gt;";		break;
				case '\"' :	result += "&quot;";		break;
				case '\'' :	result += "&#039;";		break;
				case '&' :	result += "&amp;";		break;
				default :	result += character;	break;
			}
 
			character = iterator.next();
		}
		return result;
	}
 
	/**
	 * @author titouille
	 * get xml document from xml string
	 * @param s
	 * @return
	 */
	private Document _getXMLDocument( String s )
	{
		try 
		{
			// create a builder factory
			DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory.newInstance();
 
			// get a builder from builder factory
			DocumentBuilder docBuilder = docBuilderFactory.newDocumentBuilder();
 
			// parse string to create xml document
			Document d = docBuilder.parse( new InputSource( new ByteArrayInputStream( s.getBytes() ) ) );
 
			return d;
		}
		// throw exceptions
		catch( ParserConfigurationException pce ) 
		{
			System.out.println( "ERROR - ParserConfigurationException: getXmlDocument - " + pce.getMessage() );
			return null;
		}
		catch( SAXException saxe ) 
		{
			System.out.println( "ERROR - SAXException: getXmlDocument - " + saxe.getMessage() );
			return null;
		}
		catch( IOException ioe )
		{
			System.out.println( "ERROR - IOException: getXmlDocument - " + ioe.getMessage() );
			return null;
		}	
	}
 
}

<destination id="buildingvo.hibernate">
	<adapter ref="java-dao" />
	<properties>
		<source>ch.titouille.app.assemblers.BuildingAssembler</source>
		<scope>application</scope>
		<metadata>
			<identity property="id"/>
		</metadata>
		<network>
			<session-timeout>20</session-timeout>
			<paging enabled="false" pageSize="10" />
			<throttle-inbound policy="ERROR" max-frequency="500"/>
			<throttle-outbound policy="REPLACE" max-frequency="500"/>
		</network>
		<server>
			<fill-method>
				<name>fill</name>
				<params>java.lang.Integer</params>
			</fill-method>
		</server>
	</properties>
</destination>