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.CallableStatement; import java.sql.Connection; 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.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; 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.I2B2Exception; 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.QtQueryMaster; import edu.harvard.i2b2.crc.datavo.db.QtQueryStatusType; import edu.harvard.i2b2.crc.dao.DataSourceLookupDAOFactory; /** * Class to handle persistance operation of Query instance i.e. each run of * query is called query instance $Id: QueryInstanceSpringDao.java,v 1.4 * 2008/04/08 19:38:24 rk903 Exp $ * * @author rkuttan * @see QtQueryInstance */ public class QueryInstanceSpringDao extends CRCDAO implements IQueryInstanceDao { JdbcTemplate jdbcTemplate = null; SaveQueryInstance saveQueryInstance = null; QtQueryInstanceRowMapper queryInstanceMapper = null; private DataSourceLookup dataSourceLookup = null; /** log **/ protected final Log log = LogFactory.getLog(getClass()); public QueryInstanceSpringDao(DataSource dataSource, DataSourceLookup dataSourceLookup) { setDataSource(dataSource); setDbSchemaName(dataSourceLookup.getFullSchema()); jdbcTemplate = new JdbcTemplate(dataSource); this.dataSourceLookup = dataSourceLookup; queryInstanceMapper = new QtQueryInstanceRowMapper(); } /** * Function to create query instance * * @param queryMasterId * @param userId * @param groupId * @param batchMode * @param statusId * @return query instance id */ public String createQueryInstance(String queryMasterId, String userId, String groupId, String batchMode, int statusId) { QtQueryInstance queryInstance = new QtQueryInstance(); queryInstance.setUserId(userId); queryInstance.setGroupId(groupId); queryInstance.setBatchMode(batchMode); queryInstance.setDeleteFlag("N"); QtQueryMaster queryMaster = new QtQueryMaster(); queryMaster.setQueryMasterId(queryMasterId); queryInstance.setQtQueryMaster(queryMaster); QtQueryStatusType statusType = new QtQueryStatusType(); statusType.setStatusTypeId(statusId); queryInstance.setQtQueryStatusType(statusType); Date startDate = new Date(System.currentTimeMillis()); queryInstance.setStartDate(startDate); saveQueryInstance = new SaveQueryInstance(getDataSource(), getDbSchemaName(), dataSourceLookup); saveQueryInstance.save(queryInstance); return queryInstance.getQueryInstanceId(); } /** * Returns list of query instance for the given master id * * @param queryMasterId * @return List<QtQueryInstance> */ @SuppressWarnings("unchecked") public List<QtQueryInstance> getQueryInstanceByMasterId(String queryMasterId) { String sql = "select * from " + getDbSchemaName() + "qt_query_instance where query_master_id = ?"; List<QtQueryInstance> queryInstanceList = jdbcTemplate.query(sql, new Object[] { Integer.parseInt(queryMasterId) }, queryInstanceMapper); return queryInstanceList; } /** * Find query instance by id * * @param queryInstanceId * @return QtQueryInstance */ public QtQueryInstance getQueryInstanceByInstanceId(String queryInstanceId) { String sql = "select * from " + getDbSchemaName() + "qt_query_instance where query_instance_id =?"; QtQueryInstance queryInstance = (QtQueryInstance) jdbcTemplate.queryForObject(sql, new Object[] { Integer.parseInt(queryInstanceId) }, queryInstanceMapper); return queryInstance; } /** * Update query instance * * @param queryInstance * @return QtQueryInstance * @throws I2B2DAOException */ public QtQueryInstance update(QtQueryInstance queryInstance, boolean appendMessageFlag) throws I2B2DAOException { Integer statusTypeId = (queryInstance.getQtQueryStatusType() != null) ? queryInstance.getQtQueryStatusType().getStatusTypeId() : null; String messageUpdate = ""; if (appendMessageFlag) { String concatOperator = ""; if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) { concatOperator = "||"; messageUpdate = " MESSAGE = nvl(MESSAGE,'') " + concatOperator + " ? "; } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER) || dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) { //concatOperator = "+"; // messageUpdate = " MESSAGE = isnull(Cast(MESSAGE as nvarchar(4000)),'') " // + concatOperator + " ? "; // Cast(notes as nvarchar(4000)) //update message field updateMessage(queryInstance.getQueryInstanceId(), queryInstance.getMessage(), true); if (queryInstance.getEndDate() != null) { //update rest of the fields String sql = "UPDATE " + getDbSchemaName() + "QT_QUERY_INSTANCE set USER_ID = ?, GROUP_ID = ?,BATCH_MODE = ?,END_DATE = ? ,STATUS_TYPE_ID = ? " + " where query_instance_id = ? "; jdbcTemplate.update(sql, new Object[] { queryInstance.getUserId(), queryInstance.getGroupId(), queryInstance.getBatchMode(), queryInstance.getEndDate(), statusTypeId, Integer.parseInt(queryInstance.getQueryInstanceId()) }); } else { //update rest of the fields String sql = "UPDATE " + getDbSchemaName() + "QT_QUERY_INSTANCE set USER_ID = ?, GROUP_ID = ?,BATCH_MODE = ?,STATUS_TYPE_ID = ? " + " where query_instance_id = ? "; jdbcTemplate.update(sql, new Object[] { queryInstance.getUserId(), queryInstance.getGroupId(), queryInstance.getBatchMode(), statusTypeId, Integer.parseInt(queryInstance.getQueryInstanceId()) }); } return queryInstance; } } else { messageUpdate = " MESSAGE = ?"; } if (queryInstance.getEndDate() != null) { String sql = "UPDATE " + getDbSchemaName() + "QT_QUERY_INSTANCE set USER_ID = ?, GROUP_ID = ?,BATCH_MODE = ?,END_DATE = ? ,STATUS_TYPE_ID = ?, " + messageUpdate + " where query_instance_id = ? "; jdbcTemplate.update(sql, new Object[] { queryInstance.getUserId(), queryInstance.getGroupId(), queryInstance.getBatchMode(), queryInstance.getEndDate(), statusTypeId, (queryInstance.getMessage() == null) ? "" : queryInstance.getMessage(), queryInstance.getQueryInstanceId() }); } else { String sql = "UPDATE " + getDbSchemaName() + "QT_QUERY_INSTANCE set USER_ID = ?, GROUP_ID = ?,BATCH_MODE = ?,STATUS_TYPE_ID = ?, " + messageUpdate + " where query_instance_id = ? "; jdbcTemplate.update(sql, new Object[] { queryInstance.getUserId(), queryInstance.getGroupId(), queryInstance.getBatchMode(), statusTypeId, (queryInstance.getMessage() == null) ? "" : queryInstance.getMessage(), Integer.parseInt(queryInstance.getQueryInstanceId()) }); } return queryInstance; } /** * Update query instance message * * @param queryInstanceId * @param message * @param appendMessageFlag * @return */ public void updateMessage(String queryInstanceId, String message, boolean appendMessageFlag) throws I2B2DAOException { String messageUpdate = ""; if (appendMessageFlag) { String concatOperator = ""; if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) { concatOperator = "||"; messageUpdate = " MESSAGE = nvl(MESSAGE,'') " + concatOperator + " ? "; } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) { concatOperator = "||"; messageUpdate = " MESSAGE = ? "; } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) { // Cast(notes as nvarchar(4000)) //messageUpdate = " message.write (?, NULL, 0) "; Connection conn = null; try { conn = getDataSource().getConnection(); CallableStatement callStmt = conn .prepareCall("{call " + getDbSchemaName() + "UPDATE_QUERYINSTANCE_MESSAGE(?,?,?)}"); callStmt.setString(1, message); callStmt.setString(2, queryInstanceId); callStmt.registerOutParameter(3, java.sql.Types.VARCHAR); // callStmt.setString(2, tempPatientMappingTableName); callStmt.execute(); this.getSQLServerProcedureError(dataSourceLookup.getServerType(), callStmt, 3); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); throw new I2B2DAOException("SQLException occured" + sqlEx.getMessage(), sqlEx); } catch (Exception ex) { ex.printStackTrace(); throw new I2B2DAOException("Exception occured" + ex.getMessage(), ex); } finally { if (conn != null) { try { conn.close(); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); log.error("Error while closing connection", sqlEx); } } } return; ////// } } else { messageUpdate = " MESSAGE = ?"; } String sql = "UPDATE " + getDbSchemaName() + "QT_QUERY_INSTANCE set " + messageUpdate + " where query_instance_id = ? "; jdbcTemplate.update(sql, new Object[] { (message == null) ? "" : message, Integer.parseInt(queryInstanceId) }); } private static class SaveQueryInstance extends SqlUpdate { private String INSERT_ORACLE = ""; private String INSERT_SQLSERVER = ""; private String SEQUENCE_ORACLE = ""; private String SEQUENCE_POSTGRESQL = ""; private String INSERT_POSTGRESQL = ""; private DataSourceLookup dataSourceLookup = null; public SaveQueryInstance(DataSource dataSource, String dbSchemaName, DataSourceLookup dataSourceLookup) { super(); this.dataSourceLookup = dataSourceLookup; // sqlServerSequenceDao = new // SQLServerSequenceDAO(dataSource,dataSourceLookup) ; setDataSource(dataSource); if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) { INSERT_ORACLE = "INSERT INTO " + dbSchemaName + "QT_QUERY_INSTANCE " + "(QUERY_INSTANCE_ID, QUERY_MASTER_ID, USER_ID, GROUP_ID,BATCH_MODE,START_DATE,END_DATE,STATUS_TYPE_ID,DELETE_FLAG) " + "VALUES (?,?,?,?,?,?,?,?,?)"; setSql(INSERT_ORACLE); SEQUENCE_ORACLE = "select " + dbSchemaName + "QT_SQ_QI_QIID.nextval from dual"; declareParameter(new SqlParameter(Types.INTEGER)); } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) { INSERT_SQLSERVER = "INSERT INTO " + dbSchemaName + "QT_QUERY_INSTANCE " + "( QUERY_MASTER_ID, USER_ID, GROUP_ID,BATCH_MODE,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_INSTANCE " + "(QUERY_INSTANCE_ID, QUERY_MASTER_ID, USER_ID, GROUP_ID,BATCH_MODE,START_DATE,END_DATE,STATUS_TYPE_ID,DELETE_FLAG) " + "VALUES (?,?,?,?,?,?,?,?,?)"; setSql(INSERT_POSTGRESQL); SEQUENCE_POSTGRESQL = "select " // + dbSchemaName + "nextval('qt_query_instance_query_instance_id_seq') "; declareParameter(new SqlParameter(Types.INTEGER)); } declareParameter(new SqlParameter(Types.INTEGER)); declareParameter(new SqlParameter(Types.VARCHAR)); declareParameter(new SqlParameter(Types.VARCHAR)); declareParameter(new SqlParameter(Types.VARCHAR)); declareParameter(new SqlParameter(Types.TIMESTAMP)); declareParameter(new SqlParameter(Types.TIMESTAMP)); declareParameter(new SqlParameter(Types.INTEGER)); declareParameter(new SqlParameter(Types.VARCHAR)); compile(); } public void save(QtQueryInstance queryInstance) { JdbcTemplate jdbc = getJdbcTemplate(); int queryInstanceId = 0; Object[] object = null; if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) { object = new Object[] { queryInstance.getQtQueryMaster().getQueryMasterId(), queryInstance.getUserId(), queryInstance.getGroupId(), queryInstance.getBatchMode(), queryInstance.getStartDate(), queryInstance.getEndDate(), queryInstance.getQtQueryStatusType().getStatusTypeId(), queryInstance.getDeleteFlag() }; } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) { queryInstanceId = jdbc.queryForInt(SEQUENCE_ORACLE); queryInstance.setQueryInstanceId(String.valueOf(queryInstanceId)); object = new Object[] { queryInstance.getQueryInstanceId(), queryInstance.getQtQueryMaster().getQueryMasterId(), queryInstance.getUserId(), queryInstance.getGroupId(), queryInstance.getBatchMode(), queryInstance.getStartDate(), queryInstance.getEndDate(), queryInstance.getQtQueryStatusType().getStatusTypeId(), queryInstance.getDeleteFlag() }; } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) { queryInstanceId = jdbc.queryForInt(SEQUENCE_POSTGRESQL); queryInstance.setQueryInstanceId(String.valueOf(queryInstanceId)); object = new Object[] { queryInstance.getQueryInstanceId(), queryInstance.getQtQueryMaster().getQueryMasterId(), queryInstance.getUserId(), queryInstance.getGroupId(), queryInstance.getBatchMode(), queryInstance.getStartDate(), queryInstance.getEndDate(), queryInstance.getQtQueryStatusType().getStatusTypeId(), queryInstance.getDeleteFlag() }; } update(object); if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) { int queryInstanceIdentityId = jdbc.queryForInt("SELECT @@IDENTITY"); queryInstance.setQueryInstanceId(String.valueOf(queryInstanceIdentityId)); } } } private class QtQueryInstanceRowMapper implements RowMapper { QueryStatusTypeSpringDao statusTypeDao = new QueryStatusTypeSpringDao(dataSource, dataSourceLookup); public Object mapRow(ResultSet rs, int rowNum) throws SQLException { QtQueryInstance queryInstance = new QtQueryInstance(); queryInstance.setQueryInstanceId(rs.getString("QUERY_INSTANCE_ID")); QtQueryMaster queryMaster = new QtQueryMaster(); queryMaster.setQueryMasterId(rs.getString("QUERY_MASTER_ID")); queryInstance.setQtQueryMaster(queryMaster); queryInstance.setUserId(rs.getString("USER_ID")); queryInstance.setGroupId(rs.getString("GROUP_ID")); queryInstance.setBatchMode(rs.getString("BATCH_MODE")); queryInstance.setStartDate(rs.getTimestamp("START_DATE")); queryInstance.setEndDate(rs.getTimestamp("END_DATE")); queryInstance.setMessage(rs.getString("MESSAGE")); int statusTypeId = rs.getInt("STATUS_TYPE_ID"); queryInstance.setQtQueryStatusType(statusTypeDao.getQueryStatusTypeById(statusTypeId)); queryInstance.setDeleteFlag(rs.getString("DELETE_FLAG")); return queryInstance; } } private void getSQLServerProcedureError(String serverType, CallableStatement callStmt, int outParamIndex) throws SQLException, I2B2Exception { if (serverType.equalsIgnoreCase(DataSourceLookupDAOFactory.SQLSERVER)) { String errorMsg = callStmt.getString(outParamIndex); if (errorMsg != null) { log.debug("error codde" + errorMsg); throw new I2B2Exception("Error from stored procedure [" + errorMsg + "]"); } } } }