gt: 			employer = new Employer();
context: package com.patientregistrar.persistence.jdbc;
  
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.sql.Types;
  import java.util.ArrayList;
  import java.util.Iterator;
  import java.util.List;
  
  import javax.sql.DataSource;
  
  import org.apache.log4j.Logger;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.data.domain.Page;
  import org.springframework.data.domain.Pageable;
  import org.springframework.data.domain.Sort;
  import org.springframework.data.repository.CrudRepository;
  import org.springframework.stereotype.Component;
  
  import com.patientregistrar.domain.Address;
  import com.patientregistrar.domain.BasicPerson;
  import com.patientregistrar.domain.Employer;
  import com.patientregistrar.domain.Patient;
  import com.patientregistrar.domain.Person;
  import com.patientregistrar.persistence.mongodb.PatientRepository;
  
  /**
   * <p>
   * The class <code>PatientRepositoryJdbcImpl</code> is a concrete JDBC implementation
   * of <code>PatientRepository</code> / Spring Data's <code>CrudRepository</code>.  
   * </p>
   * @see CrudRepository
   */
  @Component("patientRepositoryJdbc")
  public class PatientRepositoryJdbcImpl implements PatientRepository {
  
  	private static final String SQL_INSERT_EMERGENCY_CONTACT = "insert into emergencyContact( firstName, lastName, middleInitial, phoneNumber ) values (?,?,?,?);";
  
  	private static final String SQL_INSERT_EMPLOYER = "insert into employer( name, phoneNumber, addressLine1, addressLine2, city, state, zip ) values (?,?,?,?,?,?,?);";
  
  	private static final String SQL_INSERT_PERSON = "insert into person ( firstName, lastName, middleInitial, phoneNumber, dateOfBirth, ssn, addressLine1, addressLine2, city, state, zip, insuranceThruSelf, insuranceSourceId, employerid, emergencyContactid1, emergencyContactid2 ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
  	
  	private static final String SQL_GET_ONE_PERSON = "select * from person where personid = ?";
  	
  	private static final String SQL_GET_ONE_ER_CONTACT = "select * from emergencycontact where emergencyContactId = ?";
  	
  	private static final String SQL_GET_ONE_EMPLOYER = "select * from employer where employerid = ?";
  	
  	private static final String SQL_DELETE_EMPLOYER = "delete from employer where employerid = ?";
  	
  	private static final String SQL_DELETE_PERSON = "delete from person where personid = ?";
  	
  	private static final String SQL_DELETE_ER_CONTACT = "delete from emergencycontact where emergencyContactid = ?";
  	
  	private static final String SQL_GET_DELETE_IDS = "select insuranceSourceId, employerid, emergencyContactid1, emergencyContactid2 from person where personid = ?";
  	
  	private static final String SQL_FIND_BY_LASTNAME = "select personid from person where upper(lastname) like ?";
  	
  	private static final Logger LOGGER = Logger.getLogger(PatientRepositoryJdbcImpl.class);
  
  	@Autowired
  	private DataSource dataSource;
  	
  	@Override
  	public void delete(String id) {		
  		try(Connection c = dataSource.getConnection(); 
  				PreparedStatement psContact = c.prepareStatement(SQL_DELETE_ER_CONTACT); 
  				PreparedStatement psEmployer = c.prepareStatement(SQL_DELETE_EMPLOYER); 
  				PreparedStatement psPerson = c.prepareStatement(SQL_DELETE_PERSON);
  				PreparedStatement psLookupIds = c.prepareStatement(SQL_GET_DELETE_IDS)) {
  			
  			c.setAutoCommit(false);			
  			int personId = Integer.valueOf(id);
  			
  			psLookupIds.setInt(1, personId);
  			ResultSet rs = psLookupIds.executeQuery();
  			rs.next();
  			Integer insuranceSourceId = rs.getInt("insuranceSourceId");
  			int employerid = rs.getInt("employerid");
  			int emergencyContactid1 = rs.getInt("emergencyContactid1");
  			int emergencyContactid2 = rs.getInt("emergencyContactid2");
  			
  			if(insuranceSourceId != null) {
  				psLookupIds.clearParameters();
  				psLookupIds.setInt(1, personId);
  				rs = psLookupIds.executeQuery();
  				rs.next();
  				int altemergencyContactid1 = rs.getInt("emergencyContactid1");
  				int altemergencyContactid2 = rs.getInt("emergencyContactid2");
  
  				psPerson.clearParameters();
  				psPerson.setInt(1, personId);
  				psPerson.executeUpdate();				
  				
  				psContact.clearParameters();
  				psContact.setInt(1, altemergencyContactid1);
  				psContact.executeUpdate();
  
  				psContact.clearParameters();
  				psContact.setInt(1, altemergencyContactid2);
  				psContact.executeUpdate();
  			}			
  			
  			psPerson.clearParameters();
  			psPerson.setInt(1, personId);
  			psPerson.executeUpdate();			
  			
  			psContact.clearParameters();
  			psContact.setInt(1, emergencyContactid1);
  			psContact.executeUpdate();
  			
  			psContact.clearParameters();
  			psContact.setInt(1, emergencyContactid2);
  			psContact.executeUpdate();
  					
  			psEmployer.clearParameters();
  			psEmployer.setInt(1,employerid);
  			psEmployer.executeUpdate();
  						
  			c.commit();
  			
  		} catch(SQLException sqle) {
  			LOGGER.error(sqle, sqle);
  			throw new RuntimeException(sqle);
  		}
  	}
  
  	@Override
  	public void delete(Patient patient) {
  		delete(patient.getId());
  	}
  
  	@Override
  	public void delete(Iterable<? extends Patient> patients) {
  		for(Patient patient : patients) {
  			delete(patient.getId());
  		}
  	}
  
  	@Override
  	public boolean exists(String id) {
  		try(Connection c = dataSource.getConnection(); PreparedStatement psPerson = c.prepareStatement(SQL_GET_ONE_PERSON)) {
  			psPerson.setInt(1, Integer.valueOf(id));
  			ResultSet rs = psPerson.executeQuery();
  			return rs.next();			
  		} catch(SQLException sqle) {
  			LOGGER.error(sqle, sqle);
  			throw new RuntimeException(sqle);
  		}
  	}
  
  	@Override
  	public Patient findOne(String id) {
  		Patient patient = null;
  		try(Connection c = dataSource.getConnection(); PreparedStatement psPerson = c.prepareStatement(SQL_GET_ONE_PERSON)) {
  					
  			psPerson.setInt(1, Integer.valueOf(id));
  			ResultSet rs = psPerson.executeQuery();
  			boolean exists = rs.next();
  			if(!exists) {
  				return patient;
  			}
  			
  			patient = new Patient();
  			patient.setAddress(new Address());
  			patient.getAddress().setAddressLine1(rs.getString("addressLine1"));
  			patient.getAddress().setAddressLine2(rs.getString("addressLine2"));
  			patient.getAddress().setCity(rs.getString("city"));
  			patient.getAddress().setState(rs.getString("state"));
  			patient.getAddress().setZip(rs.getString("zip"));
  			patient.setDateOfBirth(rs.getDate("dateOfBirth"));
  			patient.setEmergencyContact1(fetchEmergencyContact(rs.getInt("emergencyContactid1")));
  			patient.setEmergencyContact2(fetchEmergencyContact(rs.getInt("emergencyContactid2")));
  			patient.setEmployer(fetchEmployer(rs.getInt("employerid")));
  			patient.setFirstName(rs.getString("firstName"));
  			patient.setId(id);			
  			patient.setInsuranceThroughSelf(rs.getBoolean("insuranceThruSelf"));
  			if(!patient.getInsuranceThroughSelf()) {
  				patient.setInsuranceSource(fetchInsuranceSource(rs.getInt("insuranceSourceId")));
  			}
  			patient.setLastName(rs.getString("lastname"));
  			patient.setMiddleInitial(rs.getString("middleInitial").charAt(0));
  			patient.setPhoneNumber(rs.getString("phoneNumber"));
  			patient.setSsn(rs.getString("ssn"));
  			
  		} catch(SQLException sqle) {
  			LOGGER.error(sqle, sqle);
  			throw new RuntimeException(sqle);
  		}
  		return patient;
  	}
  	
  	private Person fetchInsuranceSource(int id) {
  		Person person = null;
  		
  		try(Connection c = dataSource.getConnection(); PreparedStatement psPerson = c.prepareStatement(SQL_GET_ONE_PERSON)) {
  			
  			psPerson.setInt(1, id);
  			ResultSet rs = psPerson.executeQuery();
  			boolean exists = rs.next();
  			if(!exists) {
  				return person;
  			}
  
  			person = new Person();
  			person.setAddress(new Address());
  			person.getAddress().setAddressLine1(rs.getString("addressLine1"));
  			person.getAddress().setAddressLine2(rs.getString("addressLine2"));
  			person.getAddress().setCity(rs.getString("city"));
  			person.getAddress().setState(rs.getString("state"));
  			person.getAddress().setZip(rs.getString("zip"));
  			person.setDateOfBirth(rs.getDate("dateOfBirth"));
  			person.setEmployer(fetchEmployer(rs.getInt("employerid")));
  			person.setFirstName(rs.getString("firstName"));
  			person.setLastName(rs.getString("lastname"));
  			person.setMiddleInitial(rs.getString("middleInitial").charAt(0));
  			person.setPhoneNumber(rs.getString("phoneNumber"));
  			person.setSsn(rs.getString("ssn"));
  
  		} catch(SQLException sqle) {
  			LOGGER.error(sqle, sqle);
  			throw new RuntimeException(sqle);
  		}		
  		
  		return person;
  	}
  	
  	private BasicPerson fetchEmergencyContact(int id) {
  		BasicPerson contact = null;
  		
  		try(Connection c = dataSource.getConnection(); PreparedStatement psContact = c.prepareStatement(SQL_GET_ONE_ER_CONTACT)) {
  			
  			psContact.setInt(1, id);
  			ResultSet rs = psContact.executeQuery();
  			boolean exists = rs.next();
  			if(!exists) {
  				return contact;
  			}
  
  			contact = new BasicPerson();
  			contact.setFirstName(rs.getString("firstName"));
  			contact.setLastName(rs.getString("lastName"));
  			contact.setMiddleInitial(rs.getString("middleInitial").charAt(0));
  			contact.setPhoneNumber(rs.getString("phoneNumber"));
  		} catch(SQLException sqle) {
  			LOGGER.error(sqle, sqle);
  			throw new RuntimeException(sqle);
  		}
  		
  		return contact;
  	}
  	
  	private Employer fetchEmployer(int id) {
  		Employer employer = null;
  		
  		try(Connection c = dataSource.getConnection(); PreparedStatement psEmployer = c.prepareStatement(SQL_GET_ONE_EMPLOYER)) {
  			
  			psEmployer.setInt(1, id);
  			ResultSet rs = psEmployer.executeQuery();
  			boolean exists = rs.next();
  			if(!exists) {
  				return employer;
  			}
  
  