Java tutorial
/* * Copyright (c) 2006-2007 Massachusetts General Hospital * All rights reserved. This program and the accompanying materials * are made available under the terms of the i2b2 Software License v1.0 * which accompanies this distribution. * * Contributors: * Rajesh Kuttan */ package edu.harvard.i2b2.crc.dao.setfinder; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.Date; import java.util.List; import javax.sql.DataSource; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.SqlParameter; import org.springframework.jdbc.object.SqlUpdate; import edu.harvard.i2b2.common.exception.I2B2DAOException; import edu.harvard.i2b2.common.exception.StackTraceUtil; import edu.harvard.i2b2.common.util.db.JDBCUtil; import edu.harvard.i2b2.crc.dao.CRCDAO; import edu.harvard.i2b2.crc.dao.DAOFactoryHelper; import edu.harvard.i2b2.crc.datavo.db.DataSourceLookup; import edu.harvard.i2b2.crc.datavo.db.QtQueryInstance; import edu.harvard.i2b2.crc.datavo.db.QtQueryResultInstance; import edu.harvard.i2b2.crc.datavo.db.QtQueryResultType; import edu.harvard.i2b2.crc.datavo.db.QtQueryStatusType; import edu.harvard.i2b2.crc.datavo.db.StatusEnum; /** * This is class handles persistance of result instance and its update operation * $Id: QueryResultInstanceSpringDao.java,v 1.14 2010/07/22 18:54:51 rk903 Exp $ * * @author rkuttan */ public class QueryResultInstanceSpringDao extends CRCDAO implements IQueryResultInstanceDao { JdbcTemplate jdbcTemplate = null; SavePatientSetResult savePatientSetResult = null; PatientSetResultRowMapper patientSetMapper = null; DataSourceLookup dataSourceLookup = null; public QueryResultInstanceSpringDao(DataSource dataSource, DataSourceLookup dataSourceLookup) { setDataSource(dataSource); setDbSchemaName(dataSourceLookup.getFullSchema()); jdbcTemplate = new JdbcTemplate(dataSource); this.dataSourceLookup = dataSourceLookup; patientSetMapper = new PatientSetResultRowMapper(); } /** * Function to create result instance for given query instance id. The * result instance status is set to running. Use updatePatientSet function * to change the status to completed or error * * @param queryInstanceId * @return */ public String createPatientSet(String queryInstanceId, String resultName) throws I2B2DAOException { QtQueryResultInstance resultInstance = new QtQueryResultInstance(); resultInstance.setDeleteFlag("N"); QueryResultTypeSpringDao resultTypeDao = new QueryResultTypeSpringDao(dataSource, dataSourceLookup); List<QtQueryResultType> resultType = resultTypeDao.getQueryResultTypeByName(resultName); if (resultType.size() < 1) { throw new I2B2DAOException(" Result type [" + resultName + "] not found"); } resultInstance.setQtQueryResultType(resultType.get(0)); resultInstance.setDescription(resultType.get(0).getDescription()); QtQueryInstance queryInstance = new QtQueryInstance(); queryInstance.setQueryInstanceId(queryInstanceId); resultInstance.setQtQueryInstance(queryInstance); QtQueryStatusType queryStatusType = new QtQueryStatusType(); queryStatusType.setStatusTypeId(StatusEnum.QUEUED.ordinal()); resultInstance.setQtQueryStatusType(queryStatusType); Date startDate = new Date(System.currentTimeMillis()); resultInstance.setStartDate(startDate); savePatientSetResult = new SavePatientSetResult(getDataSource(), getDbSchemaName(), dataSourceLookup); savePatientSetResult.save(resultInstance); return resultInstance.getResultInstanceId(); } /** * Function used to update result instance Particularly its status and size * * @param resultInstanceId * @param statusTypeId * @param setSize */ public void updatePatientSet(String resultInstanceId, int statusTypeId, int setSize) { updatePatientSet(resultInstanceId, statusTypeId, "", setSize, 0, ""); } /** * Function used to update result instance Particularly its status and size * * @param resultInstanceId * @param statusTypeId * @param setSize */ public void updatePatientSet(String resultInstanceId, int statusTypeId, String message, int setSize, int realSetSize, String obsMethod) { Date endDate = new Date(System.currentTimeMillis()); String sql = "update " + getDbSchemaName() + "qt_query_result_instance set set_size = ?, real_set_size = ? , obfusc_method = ?, status_type_id =?, end_date = ?, message = ? where result_instance_id = ?"; jdbcTemplate.update(sql, new Object[] { setSize, realSetSize, obsMethod, statusTypeId, endDate, message, resultInstanceId }, new int[] { Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.INTEGER, Types.TIMESTAMP, Types.VARCHAR, Types.INTEGER }); } /** * Function used to update result instance description * * @param resultInstanceId * @param description */ public void updateResultInstanceDescription(String resultInstanceId, String description) { String sql = "update " + getDbSchemaName() + "qt_query_result_instance set description = ? where result_instance_id = ?"; jdbcTemplate.update(sql, new Object[] { description, Integer.parseInt(resultInstanceId) }, new int[] { Types.VARCHAR, Types.INTEGER }); } /** * Return list of query result instance by query instance id * * @param queryInstanceId * @return List<QtQueryResultInstance> */ @SuppressWarnings("unchecked") public List<QtQueryResultInstance> getResultInstanceList(String queryInstanceId) { String sql = "select * from " + getDbSchemaName() + "qt_query_result_instance where query_instance_id = ? "; List<QtQueryResultInstance> queryResultInstanceList = jdbcTemplate.query(sql, new Object[] { Integer.parseInt(queryInstanceId) }, patientSetMapper); return queryResultInstanceList; } /** * Return list of query result instance by query result id * * @param queryResultId * @return QtQueryResultInstance */ @SuppressWarnings("unchecked") public QtQueryResultInstance getResultInstanceById(String queryResultId) throws I2B2DAOException { String sql = "select * from " + getDbSchemaName() + "qt_query_result_instance where result_instance_id = ? "; List<QtQueryResultInstance> queryResultInstanceList = jdbcTemplate.query(sql, new Object[] { Integer.parseInt(queryResultId) }, patientSetMapper); if (queryResultInstanceList.size() > 0) { return queryResultInstanceList.get(0); } else { throw new I2B2DAOException("Query result id " + queryResultId + " not found"); } } /** * Return list of query result instance by query instance id and result name * * @param queryInstanceId * @param resultName * @return QtQueryResultInstance */ @SuppressWarnings("unchecked") public QtQueryResultInstance getResultInstanceByQueryInstanceIdAndName(String queryInstanceId, String resultName) { String sql = "select * from " + getDbSchemaName() + "qt_query_result_instance ri, " + getDbSchemaName() + "qt_query_result_type rt where ri.query_instance_id = ? and ri.result_type_id = rt.result_type_id and rt.name=?"; QtQueryResultInstance queryResultInstanceList = (QtQueryResultInstance) jdbcTemplate.queryForObject(sql, new Object[] { Integer.parseInt(queryInstanceId), resultName }, patientSetMapper); return queryResultInstanceList; } /** * Return a list of query result instance with waiting status * * @param queueName * @param maxListSize * @return */ public List<QtQueryResultInstance> getUnfinishedInstanceByQueue(String queueName, int maxListSize) { List<QtQueryResultInstance> resultInstanceList = null; int waitStatus = 1; String sql = "select * from " + getDbSchemaName() + "qt_query_result_instance ri, " + getDbSchemaName() + "qt_query_result_type rt where status_type_id = ? and queue_name = ? and ri.result_type_id = rt.result_type_id order by start_date"; resultInstanceList = jdbcTemplate.query(sql, new Object[] { waitStatus, queueName }, patientSetMapper); return resultInstanceList; } /** * Get result instance count by set size * * @param userId * @param compareDays * @param setSize * @param totalCount * @return * @throws I2B2DAOException */ public int getResultInstanceCountBySetSize(String userId, int compareDays, int resultTypeId, int setSize, int totalCount) throws I2B2DAOException { // int betweenDayValue = compareDays / 2; int startBetweenDayValue = compareDays * -1; int returnSetSize = 0; String queryCountSql = ""; if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) { queryCountSql = " select count(r1.result_instance_id) result_count,r1.real_set_size " + " from " + this.getDbSchemaName() + "qt_query_result_instance r1 inner join " + this.getDbSchemaName() + "qt_query_result_instance r2 on " + " r1.real_set_size = r2.real_set_size, " + this.getDbSchemaName() + "qt_query_instance qi " + " where " + " r1.start_date between sysdate- " + compareDays + " and sysdate " + " and r2.start_date between sysdate- " + compareDays + " and sysdate " + " and r1.result_type_id = ?" + " and r2.result_type_id = ? " + " and qi.user_id = ? " + " and qi.query_instance_id = r1.query_instance_id " + " and qi.query_instance_id = r2.query_instance_id " + " and r1.real_set_size = ? " + " group by r1.real_set_size " + " having count(r1.result_instance_id) > ? "; } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) { queryCountSql = " select count(r1.result_instance_id) result_count,r1.real_set_size " + " from " + this.getDbSchemaName() + "qt_query_result_instance r1 inner join " + this.getDbSchemaName() + "qt_query_result_instance r2 on " + " r1.real_set_size = r2.real_set_size, " + this.getDbSchemaName() + "qt_query_instance qi " + " where " + " r1.start_date between LOCALTIMESTAMP - INTERVAL '" + compareDays + " days' and LOCALTIMESTAMP " + " and r2.start_date between LOCALTIMESTAMP - INTERVAL '" + compareDays + " days' and LOCALTIMESTAMP " + " and r1.result_type_id = ?" + " and r2.result_type_id = ? " + " and qi.user_id = ? " + " and qi.query_instance_id = r1.query_instance_id " + " and qi.query_instance_id = r2.query_instance_id " + " and r1.real_set_size = ? " + " group by r1.real_set_size " + " having count(r1.result_instance_id) > ? "; } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) { queryCountSql = " select count(r1.result_instance_id) result_count,r1.real_set_size " + " from " + this.getDbSchemaName() + "qt_query_result_instance r1 inner join " + this.getDbSchemaName() + "qt_query_result_instance r2 on " + " r1.real_set_size = r2.real_set_size, " + this.getDbSchemaName() + "qt_query_instance qi " + " where " + " r1.start_date between DATEADD ( day , " + startBetweenDayValue + ", getDate()) and DATEADD ( day , " + "1" + ", getDate()) " + " and r2.start_date between DATEADD ( day , " + startBetweenDayValue + ", getDate()) and DATEADD ( day , " + "1" + ", getDate()) " + " and r1.result_type_id = ? " + " and r2.result_type_id = ? " + " and qi.user_id = ? " + " and qi.query_instance_id = r1.query_instance_id " + " and qi.query_instance_id = r2.query_instance_id " + " and r1.real_set_size = ? " + " group by r1.real_set_size " + " having count(r1.result_instance_id) > ? "; } Connection conn = null; PreparedStatement preparedStmt = null; try { conn = dataSource.getConnection(); log.debug("Executing sql [" + queryCountSql + "]"); preparedStmt = conn.prepareStatement(queryCountSql); preparedStmt.setInt(1, resultTypeId); preparedStmt.setInt(2, resultTypeId); preparedStmt.setString(3, userId); preparedStmt.setInt(4, setSize); preparedStmt.setInt(5, totalCount); ResultSet resultSet = preparedStmt.executeQuery(); if (resultSet.next()) { returnSetSize = resultSet.getInt("result_count"); } } catch (SQLException e) { e.printStackTrace(); throw new I2B2DAOException( "Error while calculating query count by set size" + StackTraceUtil.getStackTrace(e)); } finally { try { JDBCUtil.closeJdbcResource(null, preparedStmt, conn); } catch (SQLException e) { e.printStackTrace(); } } return returnSetSize; } private static class SavePatientSetResult extends SqlUpdate { private String INSERT_ORACLE = ""; private String INSERT_SQLSERVER = ""; private String SEQUENCE_ORACLE = ""; private String SEQUENCE_POSTGRESQL = ""; private String INSERT_POSTGRESQL = ""; DataSourceLookup dataSourceLookup = null; public SavePatientSetResult(DataSource dataSource, String dbSchemaName, DataSourceLookup dataSourceLookup) { super(); setDataSource(dataSource); if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) { INSERT_ORACLE = "INSERT INTO " + dbSchemaName + "QT_QUERY_RESULT_INSTANCE " + "(RESULT_INSTANCE_ID, QUERY_INSTANCE_ID, RESULT_TYPE_ID, SET_SIZE,START_DATE,END_DATE,STATUS_TYPE_ID,DELETE_FLAG) " + "VALUES (?,?,?,?,?,?,?,?)"; setSql(INSERT_ORACLE); SEQUENCE_ORACLE = "select " + dbSchemaName + "QT_SQ_QRI_QRIID.nextval from dual"; declareParameter(new SqlParameter(Types.INTEGER)); } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) { INSERT_SQLSERVER = "INSERT INTO " + dbSchemaName + "QT_QUERY_RESULT_INSTANCE " + "( QUERY_INSTANCE_ID, RESULT_TYPE_ID, SET_SIZE,START_DATE,END_DATE,STATUS_TYPE_ID,DELETE_FLAG) " + "VALUES (?,?,?,?,?,?,?)"; setSql(INSERT_SQLSERVER); } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) { INSERT_POSTGRESQL = "INSERT INTO " + dbSchemaName + "QT_QUERY_RESULT_INSTANCE " + "(RESULT_INSTANCE_ID, QUERY_INSTANCE_ID, RESULT_TYPE_ID, SET_SIZE,START_DATE,END_DATE,STATUS_TYPE_ID,DELETE_FLAG) " + "VALUES (?,?,?,?,?,?,?,?)"; setSql(INSERT_POSTGRESQL); SEQUENCE_POSTGRESQL = "select " //+ dbSchemaName + "nextval('qt_query_result_instance_result_instance_id_seq') "; declareParameter(new SqlParameter(Types.INTEGER)); } declareParameter(new SqlParameter(Types.INTEGER)); declareParameter(new SqlParameter(Types.INTEGER)); declareParameter(new SqlParameter(Types.INTEGER)); declareParameter(new SqlParameter(Types.TIMESTAMP)); declareParameter(new SqlParameter(Types.TIMESTAMP)); declareParameter(new SqlParameter(Types.INTEGER)); declareParameter(new SqlParameter(Types.VARCHAR)); this.dataSourceLookup = dataSourceLookup; compile(); } public void save(QtQueryResultInstance resultInstance) { JdbcTemplate jdbc = getJdbcTemplate(); int resultInstanceId = 0; Object[] object = null; if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) { object = new Object[] { resultInstance.getQtQueryInstance().getQueryInstanceId(), resultInstance.getQtQueryResultType().getResultTypeId(), resultInstance.getSetSize(), resultInstance.getStartDate(), resultInstance.getEndDate(), resultInstance.getQtQueryStatusType().getStatusTypeId(), resultInstance.getDeleteFlag() }; } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) { resultInstanceId = jdbc.queryForInt(SEQUENCE_ORACLE); resultInstance.setResultInstanceId(String.valueOf(resultInstanceId)); object = new Object[] { resultInstance.getResultInstanceId(), resultInstance.getQtQueryInstance().getQueryInstanceId(), resultInstance.getQtQueryResultType().getResultTypeId(), resultInstance.getSetSize(), resultInstance.getStartDate(), resultInstance.getEndDate(), resultInstance.getQtQueryStatusType().getStatusTypeId(), resultInstance.getDeleteFlag() }; } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) { resultInstanceId = jdbc.queryForInt(SEQUENCE_POSTGRESQL); resultInstance.setResultInstanceId(String.valueOf(resultInstanceId)); object = new Object[] { resultInstance.getResultInstanceId(), resultInstance.getQtQueryInstance().getQueryInstanceId(), resultInstance.getQtQueryResultType().getResultTypeId(), resultInstance.getSetSize(), resultInstance.getStartDate(), resultInstance.getEndDate(), resultInstance.getQtQueryStatusType().getStatusTypeId(), resultInstance.getDeleteFlag() }; } update(object); if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) { int resultInstanceIdentityId = jdbc.queryForInt("SELECT @@IDENTITY"); resultInstance.setResultInstanceId(String.valueOf(resultInstanceIdentityId)); } } } private class PatientSetResultRowMapper implements RowMapper { QueryStatusTypeSpringDao statusTypeDao = new QueryStatusTypeSpringDao(dataSource, dataSourceLookup); QueryResultTypeSpringDao resultTypeDao = new QueryResultTypeSpringDao(dataSource, dataSourceLookup); public Object mapRow(ResultSet rs, int rowNum) throws SQLException { QtQueryResultInstance resultInstance = new QtQueryResultInstance(); resultInstance.setResultInstanceId(rs.getString("RESULT_INSTANCE_ID")); QtQueryInstance queryInstance = new QtQueryInstance(); queryInstance.setQueryInstanceId(rs.getString("QUERY_INSTANCE_ID")); resultInstance.setQtQueryInstance(queryInstance); resultInstance.setDescription(rs.getString("DESCRIPTION")); int resultTypeId = rs.getInt("RESULT_TYPE_ID"); resultInstance.setQtQueryResultType(resultTypeDao.getQueryResultTypeById(resultTypeId)); resultInstance.setSetSize(rs.getInt("SET_SIZE")); resultInstance.setRealSetSize(rs.getInt("REAL_SET_SIZE")); resultInstance.setObfuscateMethod(rs.getString("OBFUSC_METHOD")); resultInstance.setStartDate(rs.getTimestamp("START_DATE")); resultInstance.setEndDate(rs.getTimestamp("END_DATE")); resultInstance.setMessage(rs.getString("MESSAGE")); // QtQueryStatusType queryStatusType = new QtQueryStatusType(); int statusTypeId = rs.getInt("STATUS_TYPE_ID"); resultInstance.setQtQueryStatusType(statusTypeDao.getQueryStatusTypeById(statusTypeId)); // resultInstance.setQtQueryStatusType(queryStatusType); resultInstance.setDeleteFlag(rs.getString("DELETE_FLAG")); return resultInstance; } } }