The following code examples show the command beans discussed in Section 4.4.2. The DBQueryEmployee command bean (Code Example A.17) reads employee information for an employee with a given employee number from a database. Code Example A.17 DBQueryEmployee Command Beanpackage com.star.benefits.db; import java.sql.SQLException; import java.util.Date; import javax.sql.DataSource; public class DBQueryEmployee extends DBQueryBean { static String statement = "SELECT empl_first_name, empl_last_name, empl_birth_date " + "empl_start_date, empl_dept_id " + "FROM employees WHERE empl_id = ?"; public DBQueryEmployee(DataSource ds) throws SQLException { super(ds, statement); } public void setEmployeeNumber(int emplNum) throws SQLException { pstmt.setInt(1, emplNum); } public String getFirstName() throws SQLException { return resultSet.getString(1); } public String getLastName() throws SQLException { return resultSet.getString(2); } public Date getBirthDate() throws SQLException { return resultSet.getDate(3); } public Date getStartDate() throws SQLException { return resultSet.getDate(4); } public int getDepartmentNumber() throws SQLException { return resultSet.getInt(5); } } The DBQuerySelection command bean (Code Example A.18) reads the benefits selections for an employee with a given employee number from a database. Code Example A.18 DBQuerySelection Command Beanpackage com.star.benefits.db; import java.sql.SQLException; import javax.sql.DataSource; public class DBQuerySelection extends DBQueryBean { static String statement = "SELECT sel_coverage, sel_smoker, " + "sel_medical_plan, sel_dental_plan" + "FROM Selections WHERE sel_empl = ?"; public DBQuerySelection(DataSource ds) throws SQLException { super(ds, statement); } public void setEmployeeNumber(int emplNum) throws SQLException { pstmt.setInt(1, emplNum); } public int getCoverage() throws SQLException { return resultSet.getInt(1); } public boolean getSmokerStatus() throws SQLException { return resultSet.getString(2).equals("Y"); } public String getMedicalPlanId() throws SQLException { return resultSet.getString(3); } public String getDentalPlanId() throws SQLException { return resultSet.getString(4); } } The DBInsertSelection command bean (Code Example A.19) inserts benefits selections into the database. Code Example A.19 DBQInsertSelection Command Beanpackage com.star.benefits.db; import java.sql.SQLException; import javax.sql.DataSource; public class DBInsertSelection extends DBUpdateBean { static String statement = "INSERT INTO Selections VALUES (?, ?, ?, ?, ?)"; public DBInsertSelection(DataSource ds) throws SQLException { super(ds, statement); } public void setEmplNumber(int emplNum) throws SQLException { pstmt.setInt(1, emplNum); } public void setCoverage(int cov) throws SQLException { pstmt.setInt(2, cov); } public void setMedicalPlanId(String id) throws SQLException { pstmt.setString(3, id); } public void setDentalPlanId(String id) throws SQLException { pstmt.setString(4, id); } public void setSmokerStatus(boolean st) throws SQLException { pstmt.setString(5, st ? "Y" : "N"); } } The DBUpdateSelection command bean (Code Example A.20) updates benefits selections in the database. Code Example A.20 DBQUpdateSelection Command Beanpackage com.star.benefits.db; import java.sql.SQLException; import javax.sql.DataSource; public class DBUpdateSelection extends DBUpdateBean { static String statement = "UPDATE Selections SET " + "sel_coverage = ?, " + "sel_medical_plan = ?, " + "sel_dental_plan = ?, " + "sel_smoker = ? " + "WHERE sel_empl = ?"; public DBUpdateSelection(DataSource ds) throws SQLException { super(ds, statement); } public void setEmplNumber(int emplNum) throws SQLException { pstmt.setInt(5, emplNum); } public void setCoverage(int cov) throws SQLException { pstmt.setInt(1, cov); } public void setMedicalPlanId(String id) throws SQLException { pstmt.setString(2, id); } public void setDentalPlanId(String id) throws SQLException { pstmt.setString(3, id); } public void setSmokerStatus(boolean st) throws SQLException { pstmt.setString(4, st ? "Y" : "N"); } } Code Example A.21 illustrates the database-related command beans' superclasses. Code Example A.21 Command Bean Superclassespackage com.star.benefits.db; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.sql.DataSource; public class DBQueryBean { protected PreparedStatement pstmt; protected ResultSet resultSet = null; private Connection con; protected DBQueryBean(DataSource ds, String statement) throws SQLException { con = ds.getConnection(); pstmt = con.prepareStatement(statement); } public void execute() throws SQLException { resultSet = pstmt.executeQuery(); } public boolean next() throws SQLException { return resultSet.next(); } public void release() { try { if (resultSet != null) resultSet.close(); if (pstmt != null) pstmt.close(); if (con != null) con.close(); } catch (SQLException ex) { } } } package com.star.benefits.db; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import javax.sql.DataSource; public class DBUpdateBean { protected PreparedStatement pstmt; private Connection con; public int execute() throws SQLException { int rowCount = pstmt.executeUpdate(); pstmt.close(); con.close(); return rowCount; } protected DBUpdateBean(DataSource ds, String statement) throws SQLException { con = ds.getConnection(); pstmt = con.prepareStatement(statement); } public void release() { } } |