Page History

h1. Starting with DAS

This page provides a simple "how to" and give a step-by-step to build a simple aplication using DAS/SDO 
features from Tuscany project. Here you'll build our "CompanyWeb Sample Web Application". This sample uses 
Mysql and Tomcat.

*h3. A. Initial Setup*
# Install Tomcat(latest version) and Mysql(latest version). 
# Download dependencies and libraries 
# Create companyweb directory in (Tomcat root)/webapps/. EX: (Tomcat root)/webapps/companyweb 
# Create the directory (Tomcat root)/webapps/companyweb/WEB-INF/lib and put the libraries in it. Otherwise you can put them in (Tomcat root)/common/lib 
# The required libraries are: 
    i) common-{(latest version}).jar 
   ii) ecore-{(latest version}).jar 
  iii) ecore-change-{(latest version}.jariv).jar
   iv) ecore-xmi-{(lateste version}.jarv).jar
    v) log4j-{(latest version}).jar 
   vi) sdo-api-xxx.jar 
  vii) tuscany-das-rdb-xxx.jar 
 viii) tuscany-sdo-xxx.jarixjar
   ix) xsd-{(latest version}).jar 
    x) mysql-connector-java-{(latest version}).jar -> This is the JDBC connector, It'll be used to connect to Mysql database
    
*h3. B. Creating CompanyWeb Database*
After the setting up your enviroment, the next step will be to create the databse where the DAS will connect and manage the transaction with the SDO features. 

# Run the following commands in the Mysql Command Shell 
    
create database companyweb; -> This command will create the CompanyWeb database 
use companyweb; -> Set the shell to work with companyweb database 

# Create a text file and name it as "comapnyweb.sql", you should insert the following lines in this file

{noformat}
CREATE TABLE EMPLOYEE ( 
ID INTEGER NOT NULL AUTO_INCREMENT, NAME VARCHAR(30), SN VARCHAR(10), MANAGER SMALLINT, DEPARTMENTID INTEGER, PRIMARY KEY (ID) 
); 

CREATE TABLE DEPARTMENT ( 
ID INTEGER NOT NULL AUTO_INCREMENT, NAME VARCHAR(30), LOCATION VARCHAR(30), NUMBER VARCHAR(10), COMPANYID INTEGER, EOTM INTEGER, PRIMARY KEY (ID) 
); 

CREATE TABLE COMPANY ( 
ID INTEGER NOT NULL AUTO_INCREMENT, NAME VARCHAR(30), PRIMARY KEY (ID) 
); 

CREATE UNIQUE INDEX SQL060217085530980 ON COMPANY (ID ASC); 

CREATE UNIQUE INDEX SQL060217085531710 ON DEPARTMENT (ID ASC); 

INSERT INTO COMPANY VALUES (51, "ACME Publishing"); 

INSERT INTO COMPANY VALUES (52, "Do-rite plumbing"); 

INSERT INTO COMPANY VALUES (53, "MegaCorp"); 
{noformat}

# Run the following command in the Mysql Shell 
source {(path})/companyweb.sql -> Executes the script to create tables, constraints, etc

*h3. C. Creating XML configuration file*
# Create the file CompanyConfig.xml in the directory {(Tomcat root})/webapps/companyweb/WEB-INF/classes 

# Edit it and write the following code
{noformat}
<?xml version="1.0" encoding="ASCII"?> 
<Config xmlns="http:///org.apache.tuscany.das.rdb/config.xsd"> 

<Command name="all companies" SQL="select * from COMPANY" kind="Select"/> 
<Command name="all companies and departments" SQL="select * from COMPANY left outer join DEPARTMENT on COMPANY.ID = DEPARTMENT.COMPANYID" kind="Select"/> 
<Command name="all departments for company" SQL="select * from COMPANY inner join DEPARTMENT on COMPANY.ID = DEPARTMENT.COMPANYID where COMPANY.ID = ?" kind="Select"/> 
<Command name="company by id with departments" SQL="select * from COMPANY left outer join DEPARTMENT on COMPANY.ID = DEPARTMENT.COMPANYID where COMPANY.ID = ?" kind="Select"/> 
<Table tableName="COMPANY"> 
<Column columnName="ID" primaryKey="true" generated="true"/> 
</Table> 
<Table tableName="DEPARTMENT"> 
?	<Column columnName="ID" primaryKey="true" generated="true"/> 
</Table> 
<Relationship name="departments" primaryKeyTable="COMPANY" foreignKeyTable="DEPARTMENT" many="true"> 
<KeyPair primaryKeyColumn="ID" foreignKeyColumn="COMPANYID"/> 
</Relationship> 
</Config> 
{noformat}

# Save the file. 

*h3. D. Creating the class and jsp wich manages the DAS/SDO features*

# Create the file CompanyClient.java in the directory {(Tomcat root})/webapps/companyweb/WEB-INF/classes/org/apache/tuscany/samples/das/companyweb 

# Write the following code in the file : 

{noformat}
package org.apache.tuscany.samples.das.companyweb;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Random;

import org.apache.tuscany.das.rdb.Command;
import org.apache.tuscany.das.rdb.DAS;

import commonj.sdo.DataObject;

public class CompanyClient {

    private Random generator = new Random();

    private DAS das = DAS.FACTORY.createDAS(getConfig("CompanyConfig.xml"));

    public final List getCompanies() {

        Command read = das.getCommand("all companies");
        DataObject root = read.executeQuery();
        return root.getList("COMPANY");

    }

    public final List getCompaniesWithDepartments() {

        Command read = das.getCommand("all companies and departments");
        DataObject root = read.executeQuery();
        return root.getList("COMPANY");
    }

    public final List getDepartmentsForCompany(int id) {
        Command read = das.getCommand("all departments for company");
        read.setParameter(1, new Integer(id));
        DataObject root = read.executeQuery();
        return root.getList("COMPANY");
    }

    public final void addDepartmentToFirstCompany() {
        Command read = das.getCommand("all companies and departments");
        DataObject root = read.executeQuery();
        DataObject firstCustomer = root.getDataObject("COMPANY[1]");

        DataObject newDepartment = root.createDataObject("DEPARTMENT");
        newDepartment.setString("NAME", "Default Name");
        firstCustomer.getList("departments").add(newDepartment);

        das.applyChanges(root);

    }

    public final void deleteDepartmentsFromFirstCompany() {

        // This section gets the ID of the first Company just so I can
        // demonstrate a parameterized command next
        Command readAll = das.getCommand("all companies and departments");
        DataObject root = readAll.executeQuery();
        int idOfFirstCustomer = root.getInt("COMPANY[1]/ID");
        System.out.println("ID of first company is: " + idOfFirstCustomer);

        // Read a specific company based on the known ID
        Command readCust = das.getCommand("company by id with departments");
        readCust.setParameter(1, new Integer(idOfFirstCustomer));
        root = readCust.executeQuery();

        // Delete all the comany's departments from the graph
        DataObject firstCustomer = root.getDataObject("COMPANY[1]");

        // Shallow copy of list for deleting. This is required to avoid the
        // dreaded
        // ConcurrentModificationException since #delete operation also removes
        // from the original list
        List allDepartments = new ArrayList(firstCustomer.getList("departments"));

        Iterator i = allDepartments.iterator();
        DataObject department;
        while (i.hasNext()) {
            department = (DataObject) i.next();
            System.out.println("Deleting department named: " + department.getString("NAME"));
            department.delete();
        }

        das.applyChanges(root);

    }

    public final void changeFirstCompanysDepartmentNames() {

        // This section gets the ID of the first Company just so I can
        // demonstrate a parameterized command next
        Command readAll = das.getCommand("all companies and departments");
        DataObject root = readAll.executeQuery();
        int idOfFirstCustomer = root.getInt("COMPANY[1]/ID");
        System.out.println("ID of first company is: " + idOfFirstCustomer);

        // Read a specific company based on the known ID
        Command readCust = das.getCommand("company by id with departments");
        readCust.setParameter(1, new Integer(idOfFirstCustomer));
        root = readCust.executeQuery();

        // Modify all the comany's department names
        DataObject firstCustomer = root.getDataObject("COMPANY[1]");
        Iterator i = firstCustomer.getList("departments").iterator();
        DataObject department;
        while (i.hasNext()) {
            department = (DataObject) i.next();
            System.out.println("Modifying department: " + department.getString("NAME"));
            department.setString("NAME", getRandomDepartmentName());
        }
        das.applyChanges(root);

    }
    
    
    public void releaseResources() {
        das.releaseResources();
    }

    // Utilities

    private String getRandomDepartmentName() {
        int number = generator.nextInt(1000) + 1;
        return "Dept-" + number;
    }

    private InputStream getConfig(String fileName) {
        return getClass().getClassLoader().getResourceAsStream(fileName);
    }

}
{noformat}

#Create following Company.jsp file under (Tomcat root})/webapps/companyweb.

{noformat}
<html>
<head>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

	pageEncoding="ISO-8859-1"

        import="org.apache.tuscany.samples.das.companyweb.CompanyClient"
        import="commonj.sdo.*"
%>

<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Company Test</title>
</head>
<body>

<H2>Tuscany DAS Companies WEB Example</H2>


<form>
<input type="submit" id="doFill" name="doFill" value="All Companies">
<input type="submit" id="doFillAll" name="doFillAll" value="All Companies/Departments">
<input type="submit" id="doAddDepartment" name="doAddDepartment" value="Add department to first company">
<input type="submit" id="doChangeDepartmentNames" name="doChangeDepartmentNames" value="Change Company(1) Dept names">
<input type="submit" id="doDeleteDepartments" name="doDeleteDepartments" value="Delete Company(1) Depts">
<hr>

<!-- Do Fill -->
<%if(request.getParameter("doFill") != null){%>

<table border>
	<thead>
		<tr>
			<th>ID</th>
			<th>Name</th>
		</tr>
	</thead>
	<tbody>

		<%
		CompanyClient companyClient = new CompanyClient();
		java.util.Iterator i = companyClient.getCompaniesWithDepartments().iterator();
		while (i.hasNext()) {
			DataObject company = (DataObject)i.next();
		%>
			<tr>
				<td><%=company.getInt("ID")%></td>
				<td><%=company.getString("NAME")%></td>
			<tr>
		<%	
		}
		companyClient.releaseResources();
		%>
		
	</tbody>
</table>
<%}%>


<!-- Do Add Department -->
<%
if(request.getParameter("doAddDepartment") != null){
    CompanyClient companyClient = new CompanyClient();
    companyClient.addDepartmentToFirstCompany();
    companyClient.releaseResources();
}
%>

<!-- Do Delete Departments from first company -->
<%
if(request.getParameter("doDeleteDepartments") != null){
    CompanyClient companyClient = new CompanyClient();
    companyClient.deleteDepartmentsFromFirstCompany();
    companyClient.releaseResources();
}
%>

<!-- Do Change First Company's Department Names -->
<%
if(request.getParameter("doChangeDepartmentNames") != null){
    CompanyClient companyClient = new CompanyClient();
    companyClient.changeFirstCompanysDepartmentNames();
    companyClient.releaseResources();
}
%>


<!-- Do FillAll -->
<%if(request.getParameter("doFill") == null) {%>

<table border>
	<thead>
		<tr>
			<th>ID</th>
			<th>Name</th>
			<th>Department_ID</th>
			<th>Department_Name</th>
		</tr>
	</thead>
	<tbody>

		<%
		CompanyClient companyClient = new CompanyClient();
		java.util.Iterator i = companyClient.getCompaniesWithDepartments().iterator();
		while (i.hasNext()) {
			DataObject company = (DataObject)i.next();
		%>
			<tr>
				<td><%=company.getInt("ID")%></td>
				<td><%=company.getString("NAME")%></td>
			<tr>

			

			<%
			java.util.Iterator j = company.getList("departments").iterator();
			while (j.hasNext()) {
				DataObject department = (DataObject)j.next();
			%>
				<tr>
					<td></td><td></td><td><%=department.getInt("ID")%></td>
					<td><%=department.getString("NAME")%></td>
				<tr>
			<%	
			}
			%>
		<%	
		}
		companyClient.releaseResources();
		%>
		
	</tbody>
</table>
<%}%>

</form>
</body>
</html>
{noformat}

*h3. E. Creating web and context configuration files supporting Tomcat.
#Under WEB-INF create following web.xml file and save it.

*web.xml*
{noformat}
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web
Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">

<web-app>
    <display-name>Tuscany DAS sample Company  WEB</display-name>

    <welcome-file-list id="WelcomeFileList">
        <welcome-file>Company.jsp</welcome-file>
    </welcome-file-list>
</web-app>
{noformat}

#Under META-INF create following context.xml file and save it.

*context.xml*
{noformat}
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/DAS Stand alone app" debug="5" reloadable="true" crossContext="true">
   <Manager pathname=""/>
   <ResourceLink name="jdbc/dastest" global="jdbc/dastest" type="javax.sql.DataSource" />                     
</Context>
{noformat}

#Under {Tomcat root}/conf modify *server.xml* with following entry in <GlobalNamingResources>

{noformat}
<Resource name="jdbc/dastest"
type="javax.sql.DataSource"  auth="Container"
description="MySQL database for DAS Web sample"
maxActive="100" maxIdle="30" maxWait="10000"
username="" password=""
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql:///dastest?user=xxx&password=yyy"/>
{noformat}

*h3. F. Run the sample companyweb*
All setup is complete now. Run the application in Tomcat and try different options like query company and
departments, addition, deletion etc.