This example uses the following simple definition of a table called HRDEPT.PROJ.
CREATE TABLE SAMPLE_SCHEMA.PROJ (PROJNO CHAR(6) PRIMARY KEY NOT NULL, PROJNAME VARCHAR(24), DEPTNO CHAR(3), RESPEMP CHAR(6), PRSTAFF DECIMAL(5, 2), PRSTDATE DATE, PRENDATE DATE, MAJPROJ CHAR(6))
A bean that corresponds to this table might look like this.
public class DeptProject { private String projectId; private String projectName; private String owningDepartment; private String owningEmployee; private BigDecimal avgStaffLevel; private Date startDate; private Date endDate; private String containingProject; @Column(name="PROJNO") public String getProjectId() { return this.projectId; } @Column(name="PROJNAME") public String getProjectName() { return this.projectName; } @Column(name="DEPTNO") public String getOwningDepartment() { return this.owningDepartment; } @Column(name="RESPEMP") public String getOwningEmployee() { return this.owningEmployee; } @Column(name="PRSTAFF") public BigDecimal getAvgStaffLevel() { return this.avgStaffLevel; } @Column(name="PRSTDATE") public Date getStartDate() { return this.startDate; } @Column(name="PRENDATE") public Date getEndDate() { return this.endDate; } @Column(name="MAJPROJ") public String getContainingProject() { return this.containingProject; } public void setProjectId(String pi) { this.projectId = pi; } public void setProjectName(String pn) { this.projectName = pn; } public void setOwningDepartment(String od) { this.owningDepartment = od; } public void setOwningEmployee(String oe) { this.owningEmployee = oe; } public void setAvgStaffLevel(BigDecimal asl) { this.avgStaffLevel = asl; } public void setStartDate(Date sd) { this.startDate = sd; } public void setEndDate(Date ed) { this.endDate = ed; } public void setContainingProject(String cp) { this.containingProject = cp; } }
If a bean is to be automatically built from the results of a query, you must use the @Column annotation when the name of a column in a database object does not match the name of the corresponding property in a bean.
When you use annotated methods to manipulate a database object, you must define an interface that defines those methods, use the pureQuery Generator to generate an implementation of that interface, and then write an application that calls the methods that are in the implementation class.
You might want to write an interface that contains a routine that reports the projects that need the most staff members.
public interface projectStaffing { @Select(sql="SELECT * FROM SAMPLE_SCHEMA.PROJ WHERE " + "(DAYS(PRENDATE) - DAYS(PRSTDATE)+1) * PRSTAFF = (" + " SELECT MAX((DAYS(PRENDATE) - DAYS(PRSTDATE)+1) * " + " PRSTAFF) FROM SAMPLE_SCHEMA.PROJ ) " + " ORDER BY PRENDATE ") public List<DeptProject> biggestProject(); }
After you generate an implementation of the projectStaffing interface and populate the PROJ table, you can find the largest project, in terms of staffing, with code that looks like this:
Connection con = DriverManager.getConnection(...); projectStaffing pM = DataFactory.getData( projectStaffing.class, con ); List<DeptProject> deptList = pM.biggestProject(); for ( DeptProject dProj: deptList ) { System.out.println("Name: " + dProj.getProjectName() + " Department: " + dProj.getOwningDepartment() ); }
If you want the SELECT statement for reporting the projects that need the highest level of staffing to be visible in the applications source, you can use the inline programming style.
In your application, you would call a version of the overloaded queryList() method that is defined in the Data interface.
Your application might look like this:
Connection con = DriverManager.getConnection(...); Data db = DataFactory.getData(con); List<DeptProject> deptList = db.queryList( "SELECT * FROM SAMPLE_SCHEMA.PROJ WHERE " + "(DAYS(PRENDATE) - DAYS(PRSTDATE)+1) * PRSTAFF = (" + " SELECT MAX((DAYS(PRENDATE) - DAYS(PRSTDATE)+1) * " + " PRSTAFF) FROM SAMPLE_SCHEMA.PROJ ) " + " ORDER BY PRENDATE", DeptProject.class ); for ( DeptProject dProj: deptList ) { System.out.println("Name: " + dProj.getProjectName() + " Department: " + dProj.getOwningDepartment() ); }