Java tutorial
/* * PatientView * * Copyright (c) Worth Solutions Limited 2004-2013 * * This file is part of PatientView. * * PatientView is free software: you can redistribute it and/or modify it under the terms of the * GNU General Public License as published by the Free Software Foundation, either version 3 of the License, * or (at your option) any later version. * PatientView is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even * the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * You should have received a copy of the GNU General Public License along with PatientView in a file * titled COPYING. If not, see <http://www.gnu.org/licenses/>. * * @package PatientView * @link http://www.patientview.org * @author PatientView <info@patientview.org> * @copyright Copyright (c) 2004-2013, Worth Solutions Limited * @license http://www.gnu.org/licenses/gpl-3.0.html The GNU General Public License V3.0 */ package org.patientview.radar.dao.impl; import org.apache.commons.lang.StringUtils; import org.patientview.model.Centre; import org.patientview.model.Clinician; import org.patientview.model.Country; import org.patientview.model.Ethnicity; import org.patientview.model.enums.SourceType; import org.patientview.radar.dao.UtilityDao; import org.patientview.radar.model.Consultant; import org.patientview.radar.model.DiagnosisCode; import org.patientview.radar.model.Relative; import org.patientview.radar.model.filter.ConsultantFilter; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.simple.SimpleJdbcInsert; import javax.sql.DataSource; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class UtilityDaoImpl extends BaseDaoImpl implements UtilityDao { private static final Logger LOGGER = LoggerFactory.getLogger(UtilityDaoImpl.class); private SimpleJdbcInsert consultantsInsert; public void setDataSource(DataSource dataSource) { super.setDataSource(dataSource); consultantsInsert = new SimpleJdbcInsert(dataSource).withTableName("tbl_Consultants") .usingGeneratedKeyColumns("cID").usingColumns("cSNAME", "cFNAME", "cCentre"); } public void createUnit(String unitCode) { jdbcTemplate.execute("INSERT INTO unit(unitcode, NAME, shortname, specialty_id) VALUES ('" + unitCode + "','" + unitCode + "','" + unitCode + "',0)"); } public void deleteUnit(String unitCode) { jdbcTemplate.execute("DELETE FROM unit WHERE unitcode = '" + unitCode + "'"); } public void deletePatientViewUser(String nshNo) { jdbcTemplate.execute("DELETE " + " FROM USER " + " WHERE username IN (SELECT username " + " FROM usermapping " + " WHERE nhsno = '" + nshNo + "')"); } public void deletePatientViewMapping(String nhsNo) { jdbcTemplate.execute("DELETE FROM usermapping WHERE nhsno = '" + nhsNo + "'"); } public Centre getCentre(long id) { return jdbcTemplate.queryForObject("SELECT * FROM unit WHERE id = ?", new Object[] { id }, new CentreRowMapper()); } public List<Centre> getCentres() { return jdbcTemplate.query("SELECT * FROM unit WHERE sourceType = ? ORDER BY name", new Object[] { "renalunit" }, new CentreRowMapper()); } public List<Centre> getCentres(String nhsNo) { return jdbcTemplate.query( "SELECT DISTINCT u.* " + " FROM usermapping um, unit u " + "WHERE um.unitcode = u.unitcode " + " AND u.sourceType = ? " + " AND um.nhsno = ? " + " AND um.username NOT LIKE '%-GP%'", new Object[] { "renalunit", nhsNo }, new CentreRowMapper()); } public Consultant getConsultant(long id) { return jdbcTemplate.queryForObject("SELECT * FROM tbl_Consultants WHERE cID = ?", new Object[] { id }, new ConsultantRowMapper()); } public List<Consultant> getConsultants(ConsultantFilter filter, int page, int numberPerPage) { if (filter == null) { filter = new ConsultantFilter(); } List<String> sqlQueries = new ArrayList<String>(); List<Object> params = new ArrayList<Object>(); // normal sql query without any filter options sqlQueries.add( "SELECT " + " tbl_Consultants.*, " + " unit.name AS cName " + "FROM " + " tbl_Consultants " + "INNER JOIN " + " unit " + "ON " + " tbl_Consultants.cCentre = unit.id"); // if there are search queries then build the where if (filter.hasSearchCriteria()) { sqlQueries.add(buildWhereQuery(filter.getSearchFields(), true, params)); } // if the filter has a sort then order by it if (filter.hasSortFilter()) { sqlQueries.add(buildOrderQuery(filter.getSortField(), filter.isReverse())); } // if a range has been set limit the results sqlQueries.add(buildLimitQuery(page, numberPerPage, params)); // combine the statement and return result return jdbcTemplate.query(StringUtils.join(sqlQueries.toArray(), " "), params.toArray(), new ConsultantRowMapper()); } public List<Consultant> getConsultantsByCentre(Centre centre) { return jdbcTemplate.query("SELECT * FROM tbl_Consultants WHERE cCentre = ?", new Object[] { centre.getId() }, new ConsultantRowMapper()); } public void saveConsultant(final Consultant consultant) throws Exception { Map<String, Object> consultantMap = new HashMap<String, Object>() { { put("cSNAME", consultant.getSurname()); put("cFNAME", consultant.getForename()); put("cCentre", consultant.getCentre().getId()); put("cID", consultant.getId()); } }; if (consultant.hasValidId()) { String updateSql = buildUpdateQuery("tbl_Consultants", "cID", consultantMap); namedParameterJdbcTemplate.update(updateSql, consultantMap); } else { Number id = consultantsInsert.executeAndReturnKey(consultantMap); consultant.setId(id.longValue()); } } public void deleteConsultant(Consultant consultant) throws Exception { Map<String, Object> consultantMap = new HashMap<String, Object>(); consultantMap.put("cID", consultant.getId()); namedParameterJdbcTemplate.update("DELETE FROM tbl_Consultants WHERE cID = :cID;", consultantMap); } public Country getCountry(long id) { try { return jdbcTemplate.queryForObject("SELECT * FROM tbl_Country WHERE cID = ?", new Long[] { id }, new CountryRowMapper()); } catch (EmptyResultDataAccessException e) { LOGGER.debug("Could not get country with id {}", id); return null; } } public List<Country> getCountries() { return jdbcTemplate.query("SELECT * FROM tbl_Country", new CountryRowMapper()); } public Ethnicity getEthnicityByCode(String ethnicityCode) { try { return jdbcTemplate.queryForObject("SELECT * FROM tbl_Ethnicity WHERE eCode = ?", new Object[] { ethnicityCode }, new EthnicityRowMapper()); } catch (EmptyResultDataAccessException e) { LOGGER.debug("Could not get ethnicity with code {}", ethnicityCode); return null; } } public List<Ethnicity> getEthnicities() { return jdbcTemplate.query("SELECT * FROM tbl_Ethnicity", new EthnicityRowMapper()); } public Relative getRelative(long id) { try { return jdbcTemplate.queryForObject("SELECT * FROM tbl_Relative WHERE rID = ?", new Object[] { id }, new RelativeRowMapper()); } catch (EmptyResultDataAccessException e) { LOGGER.debug("Could not get relative with ID {}", id); return null; } } public List<Relative> getRelatives() { return jdbcTemplate.query("SELECT * FROM tbl_Relative", new RelativeRowMapper()); } public Map<Long, Integer> getPatientCountPerUnitByDiagnosisCode(DiagnosisCode diagnosisCode) { List<PatientCountItem> patientCountList = jdbcTemplate.query( "SELECT COUNT(*) as \"count\", u.id as \"unitcode\" " + "FROM patient p " + "INNER JOIN tbl_diagnosis diagnosis ON p.radarNo = diagnosis.RADAR_NO " + "INNER JOIN usermapping um on p.nhsno = um.nhsno " + "INNER JOIN unit u ON um.unitcode = u.unitcode " + "WHERE diag = ? " + "AND u.sourceType = ? " + "AND um.username NOT LIKE '%-GP%' " + "GROUP BY u.id;", new Object[] { diagnosisCode.getId(), "renalunit" }, new PatientCountByUnitRowMapper()); Map<Long, Integer> patientCountMap = new HashMap<Long, Integer>(); for (PatientCountItem item : patientCountList) { patientCountMap.put(item.getHospitalId(), item.getCount()); } return patientCountMap; } public int getPatientCountByUnit(Centre centre) { try { StringBuilder query = new StringBuilder(); query.append("SELECT COUNT(DISTINCT p.nhsno) "); query.append("FROM user u "); query.append("INNER JOIN patient p "); query.append("INNER JOIN usermapping m "); query.append("WHERE m.nhsno = p.nhsno "); query.append("AND u.username NOT LIKE '%-GP%' "); query.append("AND u.username = m.username "); query.append("AND m.unitcode <> 'PATIENT' "); query.append("AND m.unitcode = ? "); query.append("AND p.sourceType = '"); query.append(SourceType.RADAR.getName()); query.append("'"); return jdbcTemplate.queryForObject(query.toString(), new Object[] { centre.getUnitCode() }, Integer.class); } catch (EmptyResultDataAccessException e) { return 0; } } private class CentreRowMapper implements RowMapper<Centre> { public Centre mapRow(ResultSet resultSet, int i) throws SQLException { // Create a centre and set the fields from the resultset Centre centre = new Centre(); centre.setId(resultSet.getLong("id")); centre.setName(resultSet.getString("name")); centre.setAbbreviation(resultSet.getString("shortname")); // Set country from our DAO centre.setCountry(getCountry(resultSet.getLong("country"))); centre.setUnitCode(resultSet.getString("unitcode")); centre.setRenalAdminEmail(resultSet.getString("renaladminemail")); return centre; } } private class CountryRowMapper implements RowMapper<Country> { public Country mapRow(ResultSet resultSet, int i) throws SQLException { // Create a country and set the fields from our resultset Country country = new Country(); country.setId(resultSet.getLong("cID")); country.setName(resultSet.getString("cName")); return country; } } private class EthnicityRowMapper implements RowMapper<Ethnicity> { public Ethnicity mapRow(ResultSet resultSet, int i) throws SQLException { // Construct ethnicity object and set fields Ethnicity ethnicity = new Ethnicity(); ethnicity.setId(resultSet.getLong("eID")); ethnicity.setName(resultSet.getString("eName")); ethnicity.setCode(resultSet.getString("eCode")); return ethnicity; } } private class RelativeRowMapper implements RowMapper<Relative> { public Relative mapRow(ResultSet resultSet, int i) throws SQLException { // Construct a relative object and set all the fields Relative relative = new Relative(); relative.setId(resultSet.getLong("rID")); relative.setName(resultSet.getString("RELATIVE")); return relative; } } private class ConsultantRowMapper implements RowMapper<Consultant> { public Consultant mapRow(ResultSet resultSet, int i) throws SQLException { // Construct a consultant object and set all the fields Consultant consultant = new Consultant(); consultant.setId(resultSet.getLong("cID")); consultant.setSurname(resultSet.getString("cSNAME")); consultant.setForename(resultSet.getString("cFNAME")); // Centre could be null, in which case we get a 0 returned by getLong long centreId = resultSet.getLong("cCentre"); if (centreId > 0) { consultant.setCentre(getCentre(centreId)); } return consultant; } } private class PatientCountByUnitRowMapper implements RowMapper<PatientCountItem> { public PatientCountItem mapRow(ResultSet resultSet, int i) throws SQLException { return new PatientCountItem(resultSet.getLong("unitcode"), resultSet.getInt("count")); } } private class PatientCountItem { long hospitalId; int count; private PatientCountItem(long hospitalId, int count) { this.hospitalId = hospitalId; this.count = count; } public long getHospitalId() { return hospitalId; } public void setHospitalId(long hospitalId) { this.hospitalId = hospitalId; } public int getCount() { return count; } public void setCount(int count) { this.count = count; } } public Clinician getClinician(Long id) { List<Clinician> clinicians = jdbcTemplate.query("SELECT " + " u.id, u.username, u.firstname, u.lastname, um.unitcode " + "FROM user u, usermapping um " + "WHERE " + " u.username = um.username " + " AND um.username NOT LIKE '%-GP%' " + " AND um.unitcode != 'PATIENT' " + "AND u.id = ? ", new Long[] { id }, new ClinicianRowMapper()); if (clinicians != null && !clinicians.isEmpty()) { return clinicians.get(0); } return null; } public List<Clinician> getClinicians(Centre centre) { return jdbcTemplate.query( "SELECT " + " u.*, um.unitcode " + "FROM user u, usermapping um " + "WHERE " + " u.username = um.username " + "AND u.isclinician = 1 " + "AND um.unitcode = ? ", new String[] { centre.getUnitCode() }, new ClinicianRowMapper()); } public Centre getCentre(String unitCode) { try { return jdbcTemplate.queryForObject("SELECT * FROM unit WHERE unitcode = ?", new Object[] { unitCode }, new CentreRowMapper()); } catch (EmptyResultDataAccessException e) { LOGGER.error("Could not get unit with unitcode {}", unitCode); return null; } } public List<Centre> getRenalUnitCentre(String nhsNo) { try { return jdbcTemplate.query( "SELECT * FROM usermapping um LEFT OUTER JOIN unit u ON um.unitcode = u.unitcode " + "WHERE um.nhsno = ? " + " AND um.username NOT LIKE '%-GP%' " + " AND um.unitcode != 'PATIENT' " + " AND u.sourceType = 'renalunit' ", new Object[] { nhsNo }, new CentreRowMapper()); } catch (EmptyResultDataAccessException e) { LOGGER.debug("Could not get unit with nhsno {}", nhsNo); return null; } } public void deletePatient(String nshNo) { jdbcTemplate.execute("DELETE FROM patient WHERE nhsno = '" + nshNo + "'"); } public void deletePatientForRadar(Long id) { Map<String, Object> parameters = new HashMap<String, Object>(); parameters.put("id", id); namedParameterJdbcTemplate.update("DELETE FROM tbl_patient_user WHERE id = :id", parameters); } public String getUserName(String nhsNo) { String username = null; try { return jdbcTemplate.queryForObject( "SELECT DISTINCT CONCAT(u.firstName, ' ', u.lastName) FROM user u, usermapping um " + "WHERE u.username = um.username " + "AND um.nhsno = ? " + "AND u.username NOT LIKE '%-GP%'; ", new Object[] { nhsNo }, String.class); } catch (EmptyResultDataAccessException era) { LOGGER.debug("No username result found for " + nhsNo); } return username; } // Does the username have any mappings to any renal units. public boolean isGroupAdmin(String username) { StringBuilder query = new StringBuilder(); query.append("SELECT DISTINCT 1 "); query.append("FROM unit unt "); query.append(", usermapping map "); query.append("WHERE map.unitcode = unt.unitcode "); query.append("AND map.username = '"); query.append(username); query.append("' "); query.append("AND unt.sourceType = 'renalunit' "); try { jdbcTemplate.queryForObject(query.toString(), Integer.class); } catch (EmptyResultDataAccessException ee) { return true; } return false; } public String getUserName(Long id) { if (id == null) { return ""; } try { return jdbcTemplate.queryForObject( "SELECT u.username FROM user u " + "WHERE u.id = ? " + "AND u.username NOT LIKE '%-GP%'; ", new Object[] { id }, String.class); } catch (EmptyResultDataAccessException e) { LOGGER.debug("Could not get user with id {}", id); return ""; } } public String getUserFullName(Long id) { if (id == null) { return ""; } try { return jdbcTemplate.queryForObject("SELECT CONCAT(u.firstname, ' ', u.lastname) name FROM user u " + "WHERE u.id = ? " + "AND u.username NOT LIKE '%-GP%'; ", new Object[] { id }, String.class); } catch (EmptyResultDataAccessException e) { LOGGER.debug("Could not get user with id {}", id); return ""; } } private class ClinicianRowMapper implements RowMapper<Clinician> { public Clinician mapRow(ResultSet resultSet, int i) throws SQLException { // Construct a relative object and set all the fields Clinician clinician = new Clinician(); // In future we might need to split the fullname of the user for a clinician clinician.setForename(resultSet.getString("firstName")); clinician.setId(resultSet.getLong("id")); clinician.setSurname(resultSet.getString("lastName")); // Centre could be null, in which case we get a 0 returned by getLong String unitcode = resultSet.getString("unitcode"); if (unitcode != null && !"".equals(unitcode)) { clinician.setCentre(getCentre(unitcode)); } return clinician; } } }