View Javadoc
1   package ejava.jpa.examples.query;
2   
3   import java.util.ArrayList;
4   import java.util.Collection;
5   import java.util.Date;
6   
7   import javax.persistence.*;
8   
9   @Entity @Table(name="JPAQL_CLERK")
10  @NamedNativeQueries({
11  	@NamedNativeQuery(name = "Clerk.clerkSales", query = 
12  			"select clerk.CLERK_ID, "
13  			+ "clerk.FIRST_NAME, "
14  			+ "clerk.LAST_NAME, "
15  			+ "clerk.HIRE_DATE, "
16  			+ "clerk.TERM_DATE, "
17  			+ "sum(sales.amount) total_sales " 
18  			+ "from JPAQL_CLERK clerk "
19  			+ "left outer join JPAQL_SALE_CLERK_LINK slink on clerk.CLERK_ID=slink.CLERK_ID "
20  			+ "left outer join JPAQL_SALE sales on sales.SALE_ID=slink.SALE_ID "
21  			+ "group by clerk.CLERK_ID, "
22  			+ "clerk.FIRST_NAME, "
23  			+ "clerk.LAST_NAME, "
24  			+ "clerk.HIRE_DATE, "
25  			+ "clerk.TERM_DATE "
26  			+ "order by total_sales DESC",
27  			resultSetMapping="Clerk.clerkSalesResult")
28  })
29  @SqlResultSetMappings({
30  	@SqlResultSetMapping(name = "Clerk.clerkSalesResult", 
31  			entities={ @EntityResult(entityClass = Clerk.class )},
32  			columns={@ColumnResult(name = "total_sales")}
33  	)
34  })
35  public class Clerk {
36      @Id @GeneratedValue @Column(name="CLERK_ID")
37      private long id;
38  
39      @Column(name="FIRST_NAME", length=16, nullable=false)
40      private String firstName;
41      
42      @Column(name="LAST_NAME", length=16, nullable=false)
43      private String lastName;
44      
45      @Temporal(TemporalType.DATE)
46      @Column(name="HIRE_DATE", nullable=false)
47      private Date hireDate;
48      
49      @Temporal(TemporalType.DATE)
50      @Column(name="TERM_DATE", nullable=true)
51      private Date termDate;
52  
53      @ManyToMany(mappedBy="clerks", fetch=FetchType.LAZY)
54      private Collection<Sale> sales = new ArrayList<Sale>();
55  
56      public long getId() { return id; }
57  
58      public Collection<Sale> getSales() { return sales; }
59      public Clerk setSales(Collection<Sale> sales) {
60          this.sales = sales;
61          return this;
62      }
63      public Clerk addSale(Sale...sale) {
64      	if (sale != null) {
65      		for (Sale s : sale) {
66      			if (s != null) { sales.add(s); }
67      		}
68      	}
69      	return this;
70      }
71      
72      public Date getHireDate() { return hireDate; }
73      public Clerk setHireDate(Date hireDate) {
74          this.hireDate = hireDate;
75          return this;
76      }
77      
78      public Date getTermDate() { return termDate; }
79      public Clerk setTermDate(Date termDate) {
80          this.termDate = termDate;
81          return this;
82      }    
83  
84      public String getFirstName() { return firstName; }
85      public Clerk setFirstName(String firstName) {
86          this.firstName = firstName;
87          return this;
88      }    
89      
90      public String getLastName() { return lastName; }
91      public Clerk setLastName(String lastName) {
92          this.lastName = lastName;
93          return this;
94      }
95  
96      public String toString() {
97          StringBuilder text = new StringBuilder();
98          text.append("firstName=" + firstName);
99          text.append(", lastName=" + lastName);
100         text.append(", hireDate=" + hireDate);
101         text.append(", termDate=" + termDate);
102         text.append(", sales(" + sales.size() + ")={");
103         for(Sale s : sales) {
104             text.append(s.getId() + ", ");
105         }
106         text.append("}");
107         return text.toString();
108     }
109 }