<document>
  <header>
    <product>resin-ee</product>
    <title>Query CMP</title>
        <description>
          <p>The Amber Query API resembles the JDBC PreparedStatement with
enhanced SQL and direct support for objects.</p>
        </description>
    <type>tutorial</type>
    <tutorial-startpage>query</tutorial-startpage>
  </header>

  <body>
    <summary/>

<section title="Files in this tutorial">
<deftable>
<tr><td><viewfile-link file="WEB-INF/resin-web.xml"/>
    <td>resin-web.xml configuration
<tr><td><viewfile-link file="WEB-INF/classes/META-INF/persistence.xml"/>
    <td>persistence.xml configuration
<tr><td><viewfile-link file="WEB-INF/classes/example/Student.java"/>
    <td>The student bean
<tr><td><viewfile-link file="WEB-INF/classes/example/House.java"/>
    <td>The house bean
<tr><td><viewfile-link file="WEB-INF/classes/example/QueryServlet.java"/>
    <td>The course servlet
</deftable>
</section>

<section title="Database Schema">

<example title="House.java">
<a href="amber|amber-table.xtp#@Entity">@Entity</a>
public class House {
  <a href="amber|amber-table.xtp#@Id">@Id</a>
  <a href="amber|amber-table.xtp#@Column">@Column</a>(name="id")
  public long getId()

  <a href="amber|amber-table.xtp#@Basic">@Basic</a>
  public String getName()

  <a href="amber|amber-table.xtp#@OneToMany">@OneToMany</a>(targetEntity=Student.class,
        mappedBy="house")
  public Collection getStudentList()
}
</example>

<example title="Student.java">
<a href="amber|amber-table.xtp#@Entity">@Entity</a>
public class Student {
  <a href="amber|amber-table.xtp#@Id">@Id</a>
  <a href="amber|amber-table.xtp#@Column">@Column</a>(name="id")
  public long getId()

  <a href="amber|amber-table.xtp#@Basic">@Basic</a>
  public String getName()

  <a href="amber|amber-table.xtp#@ManyToOne">@ManyToOne</a>
  <a href="amber|amber-table.xtp#@JoinColumn">@JoinColumn</a>(name="house")
  public House getHouse()
}
</example>

</section>

<section title="Query">

<example title="QueryServlet.java">
  private void doService(PrintWriter out)
    throws java.io.IOException
  {
    Query allHouse = _entityManager.createQuery("SELECT o FROM House o");
    
    String sql = ("SELECT s" +
		  " FROM House h, IN(h.studentList) s" +
		  " WHERE h.id=?1 AND s.gender='M'");
    Query boysInHouse = _entityManager.createQuery(sql);
    
    List houses = allHouse.getResultList();

    for (int i = 0; i &lt; houses.size(); i++) {
      House house = (House) houses.get(i);
      
      out.println("&lt;H3>Boys living in " + house.getName() + ":&lt;/H3>");

      boysInHouse.setParameter(1, new Long(house.getId()));
      List boys = boysInHouse.getResultList();

      if (boys.size() == 0)
	out.println("No boys are living in " + house.getName());

      for (int j = 0; j &lt; boys.size(); j++) {
	Student boy = (Student) boys.get(j);

	out.println(boy.getName() + "&lt;br>");
      }
    }
  }
}
</example>

</section>
  </body>
</document>
