Suppose that you want to insert a record into the HRDept.Employee table, which is defined like this:
CREATE TABLE HRDept.Employee( EMPNO CHAR(6) NOT NULL, FIRSTNME VARCHAR(12) NOT NULL, MIDINIT CHAR(1), LASTNAME VARCHAR(15), WORKDEPT CHAR(2), PHONENO CHAR(4), HIREDATE DATE, PRIMARY KEY(EMPNO))
The corresponding bean is defined like this:
public Employee { @Column(name="EMPNO") public String employeeId; @Column(name="FIRSTNME") public String firstName; @Column(name="MIDINIT") public String middleInitial; public String lastName; @Column(name="WORKDEPT") public String departmentId; @Column(name="PHONENO") public String extension; public Date hireDate; }
public int update(java.lang.String sql, Object... parameters)your application logic might look similar to the following code:
Connection con = DriverManager.getConnection(...); Data db = DataFactory.getData(con); Employee newCollegeHire = new Employee("000010", "CHRISTINE", "I", "HAAS", "A00", "3978", new java.sql.Date(System.currentTimeMillis())); int oneCount = db.update( "INSERT INTO HRDept.Employee(EMPNO, FIRSTNME," + " MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE) " + "VALUES(:employeeId, :firstName, :middleInitial," + " :lastName, :departmentId, :extension," + " :hireDate )", newCollegeHire );
You want to add a new "PAYROLL PROGRAMMING" project to the table HRDept.PROJ. However, you have information only for the projName, departNo, respEmp, and majProj properties of the DeptProject object that you plan to use for passing values to the INSERT statement in the update() method.
Eventually, you will have the information for the rest of the properties in the DeptProject object. To update the record for the "PAYROLL PROGRAMMING" project with that remaining information, you will need to know the value in the PROJID column, which is a generated identity column that is used a the primary key.
So, when you insert the new record, you need a means of retrieving the value that is generated in the PROJID column and placing that value in the projId property of the DeptProject object.
This is the definition of the HRDept.PROJ table:
CREATE TABLE HRDept.PROJ (PROJID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL, PROJNAME VARCHAR(24), DEPTNO CHAR(3), RESPEMP CHAR(6), PRSTAFF DECIMAL(5, 2), PRSTDATE DATE, PRENDATE DATE, MAJPROJ INTEGER, PRIMARY KEY(PROJID))
This is the definition of DeptProject, the bean that corresponds to this table:
public class DeptProject { @GeneratedKey public Integer projId; public String projName; public String departNo; public String respEmp; public BigDecimal prStaff; public Date prStDate; public Date prEnDate; public String majProj; }
public int update (java.lang.String sql, Object... parameters)your code to insert a row into the HRDept.PROJ table might look like this:
Connection con = DriverManager.getConnection(...); Data db = DataFactory.getData(con); DeptProject newProject = new DeptProject ("PAYROLL PROGRAMMING", "A00", "000010", 319); // 319: GENERAL AD SYSTEMS int updateCount = db.update( "INSERT INTO " + "HRDept.PROJ(PROJNAME, DEPTNO, RESPEMP, MAJPROJ) " + "VALUES(:projName, :departNo, :respEmp, :majProj)", newProject);
Because the projId property in the DeptProject object is defined with an @GeneratedKey annotation and the column PROJID is defined as an identity column that always generates an integer, the value in the PROJID column is passed into the projId property before control is returned from the call to the update() method.
When you later get information about the project's staffing level, start date, and end date, you can use the value for projId when updating the project's row in the HRDept.PROJ table.
newProject.prStaff = ...; newProject.prStDate = ...; newProject.prEnDate = ...; oneCount = db.update("UPDATE HRDept.PROJ SET " + "PRSTAFF=:prStaff, PRSTDATE=:prStDate, " + "PRENDATE=:prEnDate WHERE PROJID=:projId ", newProject);
If you want to pass values without a bean and retrieve generated values into an object other than a bean, you can use the following version of the update() method:
<T> T update(java.lang.String sql, Class<T> returnClass, String[] columnNames, Object... parameters)
When the return type is Object[].class, the first n elements of the array are the n generated values from the columns in the generatedColNames parameter. The last element in the array is the update count.
When the return type is a simple, directly assignable JDBC class, a single generated value is returned of the given type. The update count is not returned.
For example, a project might be represented by an Object[], not a bean. The HRDept.PROJ table can be updated and the generated key for the new project retrieved as follows:
Object[] myProj = new Object[4]; myProj[0] = ...; myProj[1] = ...; myProj[2] = ...; myProj[3] = ...; String[] generatedColNames = new String[] {"PROJID"}; Integer generatedProjID; String sql = "INSERT INTO HRDept.PROJ(PROJNAME, DEPTNO, " + "RESPEMP, MAJPROJ) values (?, ?, ?, ?)"; generatedProjID = db.update( sql, Integer.class, generatedColNames, myProj);