Adding a field to the patient table

In order to speed certain SQL queries, you may wish to de-normalize the database a little by adding a field to the patient table that has the same data as the field in another table. This is usually done to speed up patient search or the importing of lab data.

The goal is that when a form is submitted, data is persisted both in the main form table (in the following case, patientregistration) as well as in the patient table. Data updates also to both tables. One could accomplish this logic using sql triggers, but the developer would prefer to keep the logic in the application.

Add the new sql to create the new field in the SQL console:

ALTER TABLE zeprs.patient add column disable_lab_import tinyint(4);
ALTER TABLE zeprsdemo.patient add column disable_lab_import tinyint(4);

Get the field id for this field:

Create a new case in FormDAO.updatePatientValues

case 2144:
 updateDisableLabImport(conn, patientId, valueInt);
 break;

Create the new method:

/** 

* Updates disable_lab_import in patient table. 

* @param conn 

* @param patientId 

* @param status 

* @throws SQLException 

*/ 

private static void updateDisableLabImport(Connection conn, Long patientId, int disableLabImport) throws SQLException { 

ArrayList patientValues = new ArrayList(); 

patientValues.add(disableLabImport); 

patientValues.add(patientId); 

String sql = "UPDATE patient SET disable_lab_import=? WHERE id=?;"; 

DatabaseUtils.create(conn, sql, patientValues.toArray()); 

}

Add new code updatePatientValues(Connection conn, Long patientId, EncounterData vo, int formId, Date dateVisit, Long encounterId)

public static void updatePatientValues(Connection conn, Long patientId, EncounterData vo, int formId, Date dateVisit, Long encounterId) throws SQLException {

if (formId == 1) { //PatientRegistration

patientId = vo.getPatientId();

PatientRegistration patientRegistration = (PatientRegistration) vo;

String alternateId = patientRegistration.getField2145(); // Alternate Id field

if (alternateId != null) {

FormDAO.updatePatientValues(conn, patientId, 2145, alternateId, dateVisit, encounterId);

}

Byte disableLabImport = patientRegistration.getField2144(); // Disable Lab import field

if (disableLabImport != null) {

FormDAO.updatePatientValues(conn, patientId, 2144, disableLabImport.intValue(), dateVisit, encounterId);

}

}