/**
* Deactivates a given persistent ID.
*
* @param persistentId ID to deactivate
* @param deactivation deactivation time, if null the current time is used
* @throws java.sql.SQLException thrown if there is a problem communication with the database
*/
public void deactivatePersistentId(String persistentId, Timestamp deactivation)
throws SQLException {
Timestamp deactivationTime = deactivation;
if (deactivationTime == null) {
deactivationTime = new Timestamp(System.currentTimeMillis());
}
Connection dbConn = dataSource.getConnection();
try {
log.debug(
"Deactivating persistent id {} as of {}", persistentId, deactivationTime.toString());
PreparedStatement statement = dbConn.prepareStatement(deactivateIdSQL);
statement.setQueryTimeout(queryTimeout);
statement.setTimestamp(1, deactivationTime);
statement.setString(2, persistentId);
statement.executeUpdate();
} finally {
try {
if (dbConn != null && !dbConn.isClosed()) {
dbConn.close();
}
} catch (SQLException e) {
log.error("Error closing database connection", e);
}
}
}
public static boolean addMapping(Item item, Category c) throws SQLException {
final String sql =
"insert into "
+ TABLE_NAME
+ " ("
+ ITEM_ID_FIELD_NAME
+ ", "
+ CATEGORY_ID_FIELD_NAME
+ ") values (?, ?);";
final PreparedStatement st =
DataManager.getCon().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
st.setQueryTimeout(5);
final int res;
try {
st.setLong(1, item.getID());
st.setLong(2, c.getID());
try {
res = st.executeUpdate();
} catch (MySQLIntegrityConstraintViolationException s) {
return false;
}
} finally {
try {
if (st != null) {
st.close();
}
} catch (final SQLException e) {
System.out.println("Error closing prepared statement : " + e.getMessage());
}
}
return res == 1;
}
/**
* Gets all the persistent ID entries for a (principal, peer, local) tuple.
*
* @param localId local ID part of the persistent ID
* @param peerEntity entity ID of the peer the ID is for
* @param localEntity entity ID of the ID issuer
* @return the active identifier
* @throws java.sql.SQLException thrown if there is a problem communication with the database
*/
public List<PersistentIdEntry> getPersistentIdEntries(
String localEntity, String peerEntity, String localId) throws SQLException {
StringBuilder sqlBuilder = new StringBuilder(idEntrySelectSQL);
sqlBuilder.append(localEntityColumn).append(" = ?");
sqlBuilder.append(" AND ").append(peerEntityColumn).append(" = ?");
sqlBuilder.append(" AND ").append(localIdColumn).append(" = ?");
String sql = sqlBuilder.toString();
log.debug("Selecting all persistent ID entries based on prepared sql statement: {}", sql);
Connection dbConn = dataSource.getConnection();
try {
PreparedStatement statement = dbConn.prepareStatement(sql);
statement.setQueryTimeout(queryTimeout);
log.debug("Setting prepared statement parameter {}: {}", 1, localEntity);
statement.setString(1, localEntity);
log.debug("Setting prepared statement parameter {}: {}", 2, peerEntity);
statement.setString(2, peerEntity);
log.debug("Setting prepared statement parameter {}: {}", 3, localId);
statement.setString(3, localId);
return buildIdentifierEntries(statement.executeQuery());
} finally {
try {
if (dbConn != null && !dbConn.isClosed()) {
dbConn.close();
}
} catch (SQLException e) {
log.error("Error closing database connection", e);
}
}
}
@Override
public Map<String, Set> query(String cypher, String[] params, int skip, int limit)
throws SQLException {
Map<String, Set> map = new HashMap<String, Set>();
Set<Neo4jNode> nodes = new HashSet<Neo4jNode>();
Set<Neo4jRelationship> relationships = new HashSet<Neo4jRelationship>();
StringBuffer sb = new StringBuffer();
sb.append(cypher);
sb.append(" ");
sb.append("skip " + skip);
sb.append(" ");
sb.append("limit " + limit);
conn = getConn();
PreparedStatement psmt = conn.prepareStatement(sb.toString());
int i = 0;
for (String param : params) {
psmt.setString(i++, param);
}
psmt.setQueryTimeout(Integer.parseInt(queryTimeOut));
ResultSet rs = psmt.executeQuery();
while (rs.next()) {
nodes.addAll(jsonNode(rs.getString(1)));
relationships.addAll(jsonRelationship(rs.getString(2)));
}
map.put("nodes", nodes);
map.put("relationships", relationships);
return map;
}
public void setQueryTimeout(int seconds) throws SQLException {
Profiler profiler = _profilerPoint.start();
try {
_preparedStatement.setQueryTimeout(seconds);
} finally {
profiler.finish();
}
}
/**
* Convenience method to create a new PreparedStatement for a query.
*
* @param conn The Connection to use for the statement
* @param stmtText Statement text
* @param resultSetType Type of result set
* @param resultSetConcurrency Concurrency for the result set
* @return The PreparedStatement
* @throws SQLException thrown if an error occurs creating the statement
*/
public PreparedStatement getStatementForQuery(
ManagedConnection conn, String stmtText, String resultSetType, String resultSetConcurrency)
throws SQLException {
Connection c = (Connection) conn.getConnection();
if (supportsBatching) {
// Check for a waiting batched statement that is ready for processing
ConnectionStatementState state = getConnectionStatementState(conn);
if (state != null && state.processable) {
// Process the batch statement before returning our new query statement
processConnectionStatement(conn);
}
}
// Create a new PreparedStatement for this query
PreparedStatement ps = null;
if (resultSetType != null || resultSetConcurrency != null) {
int rsTypeValue = ResultSet.TYPE_FORWARD_ONLY;
if (resultSetType != null) {
if (resultSetType.equals("scroll-sensitive")) {
rsTypeValue = ResultSet.TYPE_SCROLL_SENSITIVE;
} else if (resultSetType.equals("scroll-insensitive")) {
rsTypeValue = ResultSet.TYPE_SCROLL_INSENSITIVE;
}
}
int rsConcurrencyValue = ResultSet.CONCUR_READ_ONLY;
if (resultSetConcurrency != null && resultSetConcurrency.equals("updateable")) {
rsConcurrencyValue = ResultSet.CONCUR_UPDATABLE;
}
ps = c.prepareStatement(stmtText, rsTypeValue, rsConcurrencyValue);
ps
.clearBatch(); // In case using statement caching and given one with batched statements
// left hanging (C3P0)
} else {
ps = c.prepareStatement(stmtText);
ps
.clearBatch(); // In case using statement caching and given one with batched statements
// left hanging (C3P0)
}
if (queryTimeout > 0) {
// Apply any query timeout
ps.setQueryTimeout(queryTimeout / 1000); // queryTimeout is in milliseconds
}
if (NucleusLogger.DATASTORE.isDebugEnabled()) {
NucleusLogger.DATASTORE.debug(LOCALISER.msg("052110", StringUtils.toJVMIDString(ps)));
}
if (!jdbcStatements) {
// Wrap with our parameter logger
ps = new ParamLoggingPreparedStatement(ps, stmtText);
((ParamLoggingPreparedStatement) ps).setParamsInAngleBrackets(paramValuesInBrackets);
}
return ps;
}
/**
* Prepares and executes parameterized SELECT queries with variadic variables.
*
* @param sql The MySQL statement being prepared
* @param args The variadic variables being passed into the parameters of the query.
*/
public Optional<ResultSet> query(String sql, Object... args) {
try {
PreparedStatement statement = prepareStatement(connection, sql, args);
statement.setQueryTimeout(30); // set timeout to 30 sec.
return Optional.of(statement.executeQuery());
} catch (SQLException e) {
// Print organized SQL error message
printSQLException(e);
return Optional.empty();
}
}
/**
* Method to apply any restrictions to the created ResultSet.
*
* @param ps The PreparedStatement
* @param query The query
* @param applyTimeout Whether to apply the query timeout (if any) direct to the PreparedStatement
* @throws SQLException Thrown when an error occurs applying the constraints
*/
public static void prepareStatementForExecution(
PreparedStatement ps, Query query, boolean applyTimeout) throws SQLException {
NucleusContext nucleusCtx = query.getExecutionContext().getNucleusContext();
RDBMSStoreManager storeMgr = (RDBMSStoreManager) query.getStoreManager();
PersistenceConfiguration conf = nucleusCtx.getPersistenceConfiguration();
if (applyTimeout) {
Integer timeout = query.getDatastoreReadTimeoutMillis();
if (timeout != null && timeout > 0) {
ps.setQueryTimeout(timeout / 1000);
}
}
// Apply any fetch size
int fetchSize = 0;
if (query.getFetchPlan().getFetchSize() > 0) {
// FetchPlan has a size set so use that
fetchSize = query.getFetchPlan().getFetchSize();
}
if (storeMgr.getDatastoreAdapter().supportsQueryFetchSize(fetchSize)) {
ps.setFetchSize(fetchSize);
}
// Apply any fetch direction
String fetchDir =
conf.getStringProperty(RDBMSPropertyNames.PROPERTY_RDBMS_QUERY_FETCH_DIRECTION);
Object fetchDirExt =
query.getExtension(RDBMSPropertyNames.PROPERTY_RDBMS_QUERY_FETCH_DIRECTION);
if (fetchDirExt != null) {
fetchDir = (String) fetchDirExt;
if (!fetchDir.equals("forward")
&& !fetchDir.equals("reverse")
&& !fetchDir.equals("unknown")) {
throw new NucleusUserException(LOCALISER.msg("052512"));
}
}
if (fetchDir.equals("reverse")) {
ps.setFetchDirection(ResultSet.FETCH_REVERSE);
} else if (fetchDir.equals("unknown")) {
ps.setFetchDirection(ResultSet.FETCH_UNKNOWN);
}
// Add a limit on the number of rows to include the maximum we may need
long toExclNo = query.getRangeToExcl();
if (toExclNo != 0 && toExclNo != Long.MAX_VALUE) {
if (toExclNo > Integer.MAX_VALUE) {
// setMaxRows takes an int as input so limit to the correct range
ps.setMaxRows(Integer.MAX_VALUE);
} else {
ps.setMaxRows((int) toExclNo);
}
}
}
/**
* Stores a persistent ID entry into the database.
*
* @param entry entry to persist
* @throws java.sql.SQLException thrown is there is a problem writing to the database
*/
public void storePersistentIdEntry(PersistentIdEntry entry) throws SQLException {
StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
sqlBuilder.append(table).append(" (");
sqlBuilder.append(localEntityColumn).append(", ");
sqlBuilder.append(peerEntityColumn).append(", ");
sqlBuilder.append(principalNameColumn).append(", ");
sqlBuilder.append(localIdColumn).append(", ");
sqlBuilder.append(persistentIdColumn).append(", ");
sqlBuilder.append(peerProvidedIdColumn).append(", ");
sqlBuilder.append(createTimeColumn);
sqlBuilder.append(") VALUES (?, ?, ?, ?, ?, ?, ?)");
String sql = sqlBuilder.toString();
Connection dbConn = dataSource.getConnection();
try {
log.debug("Storing persistent ID entry based on prepared sql statement: {}", sql);
PreparedStatement statement = dbConn.prepareStatement(sql);
statement.setQueryTimeout(queryTimeout);
log.debug("Setting prepared statement parameter {}: {}", 1, entry.getLocalEntityId());
statement.setString(1, entry.getLocalEntityId());
log.debug("Setting prepared statement parameter {}: {}", 2, entry.getPeerEntityId());
statement.setString(2, entry.getPeerEntityId());
log.debug("Setting prepared statement parameter {}: {}", 3, entry.getPrincipalName());
statement.setString(3, entry.getPrincipalName());
log.debug("Setting prepared statement parameter {}: {}", 4, entry.getLocalId());
statement.setString(4, entry.getLocalId());
log.debug("Setting prepared statement parameter {}: {}", 5, entry.getPersistentId());
statement.setString(5, entry.getPersistentId());
if (entry.getPeerProvidedId() == null) {
log.debug("Setting prepared statement parameter {}: {}", 6, Types.VARCHAR);
statement.setNull(6, Types.VARCHAR);
} else {
log.debug("Setting prepared statement parameter {}: {}", 6, entry.getPeerProvidedId());
statement.setString(6, entry.getPeerProvidedId());
}
Timestamp timestamp = new Timestamp(System.currentTimeMillis());
log.debug("Setting prepared statement parameter {}: {}", 7, timestamp.toString());
statement.setTimestamp(7, timestamp);
statement.executeUpdate();
} finally {
try {
if (dbConn != null && !dbConn.isClosed()) {
dbConn.close();
}
} catch (SQLException e) {
log.error("Error closing database connection", e);
}
}
}
public Map<String, Set> doubleNodes(Neo4jNode node1, Neo4jNode node2, String type, int step)
throws SQLException {
Map<String, Set> map = new HashMap<String, Set>();
Set<Neo4jNode> nodes = new HashSet<Neo4jNode>();
Set<Neo4jRelationship> relationships = new HashSet<Neo4jRelationship>();
if (!type.equals("")) type = ":" + type;
StringBuffer sb = new StringBuffer("start ");
if (node1.getNode_type().equals("1")) {
sb.append("a=node:nodes(node={1}),");
} else {
sb.append("a=node:nodes(regno={1}),");
}
if (node2.getNode_type().equals("1")) {
sb.append("b=node:nodes(node={2}) ");
} else {
sb.append("b=node:nodes(regno={2}) ");
}
sb.append("match p=allShortestPaths(a-["); // 全部最短路径
sb.append(type);
sb.append("*..");
sb.append(step);
sb.append("]-b) return nodes(p),relationships(p),length(p) limit "); // 2点之间存在多条最短路径
int[] arrays = {1, 5 - step}; // 多节点5层以下取多条路径 5层以上取最短路径
int limit = Math.max(arrays[0], arrays[1]);
sb.append(limit);
conn = getConn();
PreparedStatement psmt = conn.prepareStatement(sb.toString());
psmt.setString(1, node1.getNode());
psmt.setString(2, node2.getNode());
psmt.setQueryTimeout(Integer.parseInt(queryTimeOut));
ResultSet rs = psmt.executeQuery();
while (rs.next()) {
nodes.addAll(jsonNode(rs.getString(1)));
// Iterator<Neo4jNode> it=nodes.iterator();
// while(it.hasNext()){
// Neo4jNode i=it.next();
// System.out.println(i.getInv_name()+"="+i.getNode());
// }
if (rs.getInt(3) == 1) {
relationships.addAll(exBenifit(node1, node2, type));
} else {
relationships.addAll(jsonRelationship(rs.getString(2)));
}
}
map.put("nodes", nodes);
map.put("relationships", relationships);
return map;
}
public PersistentIdEntry getActivePersistentIdEntry(
String localEntity, String peerEntity, String localId, boolean isActive) throws SQLException {
StringBuilder sqlBuilder = new StringBuilder(idEntrySelectSQL);
sqlBuilder.append(localEntityColumn).append(" = ?");
sqlBuilder.append(" AND ").append(peerEntityColumn).append(" = ?");
sqlBuilder.append(" AND ").append(localIdColumn).append(" = ?");
if (isActive) {
sqlBuilder.append(" AND ").append(deactivationTimeColumn).append(" IS NULL");
} else {
sqlBuilder.append(" AND ").append(deactivationTimeColumn).append(" IS NOT NULL");
}
String sql = sqlBuilder.toString();
log.debug("Selecting persistent ID entry based on prepared sql statement: {}", sql);
Connection dbConn = dataSource.getConnection();
try {
PreparedStatement statement = dbConn.prepareStatement(sql);
statement.setQueryTimeout(queryTimeout);
log.debug("Setting prepared statement parameter {}: {}", 1, localEntity);
statement.setString(1, localEntity);
log.debug("Setting prepared statement parameter {}: {}", 2, peerEntity);
statement.setString(2, peerEntity);
log.debug("Setting prepared statement parameter {}: {}", 3, localId);
statement.setString(3, localId);
log.debug("Getting active persistent Id entries.");
List<PersistentIdEntry> entries = buildIdentifierEntries(statement.executeQuery());
if (entries == null || entries.size() == 0) {
return null;
}
if (entries.size() > 1) {
log.warn("More than one active identifier, only the first will be used");
}
return entries.get(0);
} finally {
try {
if (dbConn != null && !dbConn.isClosed()) {
dbConn.close();
}
} catch (SQLException e) {
log.error("Error closing database connection", e);
}
}
}
/**
* 初始化真正的PreparedStatement，对当前对象的操作全部都设置到真正的PreparedStatement
*
* @throws SQLException
*/
private void prepare() throws SQLException {
DALFactory dalFactory = DALFactory.getDefault();
List<Object> values = dalParameters.getValues();
Map<String, Object> context = new HashMap<String, Object>();
SQLStruct sqlStruct = dalFactory.getSqlAnalyzer().parse(sql, context);
SQLInfo sqlInfo = null;
if (sqlStruct.isCanParse()) {
sqlInfo =
dalFactory
.getSqlAnalyzer()
.analyse(sql, sqlStruct, values.toArray(new Object[values.size()]), context);
}
this.parsePartition(sqlStruct, sqlInfo);
this.initRealPreparedStatement();
if (this.maxFieldSize != 0) {
ps.setMaxFieldSize(maxFieldSize);
}
if (this.maxRows != 0) {
ps.setMaxRows(maxRows);
}
if (!this.escapeProcessing) {
ps.setEscapeProcessing(escapeProcessing);
}
if (this.queryTimeout != 0) {
ps.setQueryTimeout(queryTimeout);
}
if (this.cursorName != null) {
ps.setCursorName(cursorName);
}
if (this.fetchDirection != 0) {
ps.setFetchDirection(fetchDirection);
}
if (this.fetchSize != 0) {
ps.setFetchSize(fetchSize);
}
if (!this.poolable) {
ps.setPoolable(poolable);
}
this.dalParameters.initRealPreparedStatement(ps);
}
/**
* Gets the number of persistent ID entries for a (principal, peer, local) tuple.
*
* @param localEntity entity ID of the ID issuer
* @param peerEntity entity ID of the peer the ID is for
* @param localId local ID part of the persistent ID
* @return the number of identifiers
* @throws java.sql.SQLException thrown if there is a problem communication with the database
*/
public int getNumberOfPersistentIdEntries(String localEntity, String peerEntity, String localId)
throws SQLException {
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("SELECT");
sqlBuilder.append(" count(").append(persistentIdColumn).append(")");
sqlBuilder.append(" FROM ").append(table).append(" WHERE ");
sqlBuilder.append(localEntityColumn).append(" = ?");
sqlBuilder.append(" AND ");
sqlBuilder.append(peerEntityColumn).append(" = ?");
sqlBuilder.append(" AND ");
sqlBuilder.append(localIdColumn).append(" = ?");
String sql = sqlBuilder.toString();
Connection dbConn = dataSource.getConnection();
try {
log.debug(
"Selecting number of persistent ID entries based on prepared sql statement: {}", sql);
PreparedStatement statement = dbConn.prepareStatement(sql);
statement.setQueryTimeout(queryTimeout);
log.debug("Setting prepared statement parameter {}: {}", 1, localEntity);
statement.setString(1, localEntity);
log.debug("Setting prepared statement parameter {}: {}", 2, peerEntity);
statement.setString(2, peerEntity);
log.debug("Setting prepared statement parameter {}: {}", 3, localId);
statement.setString(3, localId);
ResultSet rs = statement.executeQuery();
rs.next();
return rs.getInt(1);
} finally {
try {
if (dbConn != null && !dbConn.isClosed()) {
dbConn.close();
}
} catch (SQLException e) {
log.error("Error closing database connection", e);
}
}
}
private Set<Neo4jRelationship> exBenifit(Neo4jNode node1, Neo4jNode node2, String type)
throws SQLException {
// TODO Auto-generated method stub
Set<Neo4jRelationship> relationships = new HashSet<Neo4jRelationship>();
StringBuffer sb = new StringBuffer("start ");
sb.append("a=node:nodes(node={1}),");
sb.append("b=node:nodes(node={2}) ");
sb.append("match p=a-[");
sb.append(type);
sb.append("]-b return relationships(p)");
conn = getConn();
PreparedStatement psmt = conn.prepareStatement(sb.toString());
psmt.setString(1, node1.getNode());
psmt.setString(2, node2.getNode());
psmt.setQueryTimeout(Integer.parseInt(queryTimeOut));
ResultSet rs = psmt.executeQuery();
while (rs.next()) {
relationships.addAll(jsonRelationship(rs.getString(1)));
}
return relationships;
}
/** Execute the query returning the row count. */
public BeanIdList findIds() throws SQLException {
long startNano = System.nanoTime();
try {
// get the list that we are going to put the id's into.
// This was already set so that it is available to be
// read by other threads (it is a synchronised list)
List<Object> idList = query.getIdList();
if (idList == null) {
// running in foreground thread (not FutureIds query)
idList = Collections.synchronizedList(new ArrayList<Object>());
query.setIdList(idList);
}
BeanIdList result = new BeanIdList(idList);
SpiTransaction t = request.getTransaction();
Connection conn = t.getInternalConnection();
pstmt = conn.prepareStatement(sql);
if (query.getBufferFetchSizeHint() > 0) {
pstmt.setFetchSize(query.getBufferFetchSizeHint());
}
if (query.getTimeout() > 0) {
pstmt.setQueryTimeout(query.getTimeout());
}
bindLog = predicates.bind(new DataBind(pstmt));
ResultSet rset = pstmt.executeQuery();
dataReader = new RsetDataReader(rset);
boolean hitMaxRows = false;
boolean hasMoreRows = false;
rowCount = 0;
DbReadContext ctx = new DbContext();
while (rset.next()) {
Object idValue = desc.getIdBinder().read(ctx);
idList.add(idValue);
// reset back to 0
dataReader.resetColumnPosition();
rowCount++;
if (maxRows > 0 && rowCount == maxRows) {
hitMaxRows = true;
hasMoreRows = rset.next();
break;
}
}
if (hitMaxRows) {
result.setHasMore(hasMoreRows);
}
long exeNano = System.nanoTime() - startNano;
executionTimeMicros = (int) exeNano / 1000;
return result;
} finally {
close();
}
}
/**
* Function accepts parameter in Map. The patient count will be obfuscated if the user is OBFUS
*/
public void generateResult(Map param) throws CRCTimeOutException, I2B2DAOException {
SetFinderConnection sfConn = (SetFinderConnection) param.get("SetFinderConnection");
SetFinderDAOFactory sfDAOFactory = (SetFinderDAOFactory) param.get("SetFinderDAOFactory");
// String patientSetId = (String)param.get("PatientSetId");
String queryInstanceId = (String) param.get("QueryInstanceId");
String TEMP_DX_TABLE = (String) param.get("TEMP_DX_TABLE");
String resultInstanceId = (String) param.get("ResultInstanceId");
// String itemKey = (String) param.get("ItemKey");
String resultTypeName = (String) param.get("ResultOptionName");
String processTimingFlag = (String) param.get("ProcessTimingFlag");
int obfuscatedRecordCount = (Integer) param.get("ObfuscatedRecordCount");
int transactionTimeout = (Integer) param.get("TransactionTimeout");
TransactionManager tm = (TransactionManager) param.get("TransactionManager");
this.setDbSchemaName(sfDAOFactory.getDataSourceLookup().getFullSchema());
Map ontologyKeyMap = (Map) param.get("setFinderResultOntologyKeyMap");
String serverType = (String) param.get("ServerType");
CallOntologyUtil ontologyUtil = (CallOntologyUtil) param.get("CallOntologyUtil");
List<String> roles = (List<String>) param.get("Roles");
String tempTableName = "";
PreparedStatement stmt = null;
boolean errorFlag = false, timeoutFlag = false;
String itemKey = "";
int actualTotal = 0, obsfcTotal = 0;
boolean obfscDataRoleFlag = false;
try {
LogTimingUtil logTimingUtil = new LogTimingUtil();
logTimingUtil.setStartTime();
obfscDataRoleFlag = checkDataObscRole(sfDAOFactory.getOriginalDataSourceLookup(), roles);
itemKey = getItemKeyFromResultType(sfDAOFactory, resultTypeName);
log.debug("Result type's " + resultTypeName + " item key value " + itemKey);
LogTimingUtil subLogTimingUtil = new LogTimingUtil();
subLogTimingUtil.setStartTime();
ConceptsType conceptsType = ontologyUtil.callGetChildren(itemKey);
if (conceptsType != null && conceptsType.getConcept().size() < 1) {
throw new I2B2DAOException(
"Could not fetch children result type "
+ resultTypeName
+ " item key [ "
+ itemKey
+ " ]");
}
subLogTimingUtil.setEndTime();
if (processTimingFlag != null) {
if (processTimingFlag.trim().equalsIgnoreCase(ProcessTimingReportUtil.DEBUG)) {
ProcessTimingReportUtil ptrUtil =
new ProcessTimingReportUtil(sfDAOFactory.getDataSourceLookup());
ptrUtil.logProcessTimingMessage(
queryInstanceId,
ptrUtil.buildProcessTiming(
subLogTimingUtil,
"BUILD - " + resultTypeName + " : Ontology Call(GetChildren) ",
""));
}
}
String itemCountSql =
" select count(distinct PATIENT_NUM) as item_count from "
+ this.getDbSchemaName()
+ "observation_fact obs_fact "
+ " where obs_fact.patient_num in (select patient_num from "
+ TEMP_DX_TABLE
+ " ) "
+ " and obs_fact.concept_cd in (select concept_cd from "
+ this.getDbSchemaName()
+ "concept_dimension where concept_path like ?)";
ResultType resultType = new ResultType();
resultType.setName(resultTypeName);
stmt = sfConn.prepareStatement(itemCountSql);
CancelStatementRunner csr = new CancelStatementRunner(stmt, transactionTimeout);
Thread csrThread = new Thread(csr);
csrThread.start();
for (ConceptType conceptType : conceptsType.getConcept()) {
String dimCode = this.getDimCodeInSqlFormat(conceptType);
itemCountSql =
" select count(distinct PATIENT_NUM) as item_count from "
+ this.getDbSchemaName()
+ " observation_fact "
+ " where "
+ " patient_num in (select patient_num from "
+ TEMP_DX_TABLE
+ " ) and "
+ conceptType.getFacttablecolumn()
+ " IN (select "
+ conceptType.getFacttablecolumn()
+ " from "
+ getDbSchemaName()
+ conceptType.getTablename()
+ " "
+ " where "
+ conceptType.getColumnname()
+ " "
+ conceptType.getOperator()
+ " "
+ dimCode
+ ")";
stmt = sfConn.prepareStatement(itemCountSql);
// // smuniraju: Currently, in postgres, a timeout value > 0 will result in "setQueryTimeout
// is not yet implemented"
// stmt.setQueryTimeout(transactionTimeout);
int queryTimeout =
(serverType.equalsIgnoreCase(DAOFactoryHelper.POSTGRES)) ? 0 : transactionTimeout;
stmt.setQueryTimeout(queryTimeout);
log.debug("Executing count sql [" + itemCountSql + "]");
//
subLogTimingUtil.setStartTime();
ResultSet resultSet = stmt.executeQuery();
if (csr.getSqlFinishedFlag()) {
timeoutFlag = true;
throw new CRCTimeOutException("The query was canceled.");
}
resultSet.next();
int demoCount = resultSet.getInt("item_count");
subLogTimingUtil.setEndTime();
if (processTimingFlag != null) {
if (processTimingFlag.trim().equalsIgnoreCase(ProcessTimingReportUtil.DEBUG)) {
ProcessTimingReportUtil ptrUtil =
new ProcessTimingReportUtil(sfDAOFactory.getDataSourceLookup());
ptrUtil.logProcessTimingMessage(
queryInstanceId,
ptrUtil.buildProcessTiming(
subLogTimingUtil,
"BUILD - "
+ resultTypeName
+ " : COUNT SQL for "
+ conceptType.getDimcode()
+ " ",
"sql=" + itemCountSql));
}
}
//
actualTotal += demoCount;
if (obfscDataRoleFlag) {
GaussianBoxMuller gaussianBoxMuller = new GaussianBoxMuller();
demoCount = (int) gaussianBoxMuller.getNormalizedValueForCount(demoCount);
obsfcTotal += demoCount;
}
DataType mdataType = new DataType();
mdataType.setValue(String.valueOf(demoCount));
mdataType.setColumn(conceptType.getName());
mdataType.setType("int");
resultType.getData().add(mdataType);
}
csr.setSqlFinishedFlag();
csrThread.interrupt();
stmt.close();
edu.harvard.i2b2.crc.datavo.i2b2result.ObjectFactory of =
new edu.harvard.i2b2.crc.datavo.i2b2result.ObjectFactory();
BodyType bodyType = new BodyType();
bodyType.getAny().add(of.createResult(resultType));
ResultEnvelopeType resultEnvelop = new ResultEnvelopeType();
resultEnvelop.setBody(bodyType);
JAXBUtil jaxbUtil = CRCJAXBUtil.getJAXBUtil();
StringWriter strWriter = new StringWriter();
subLogTimingUtil.setStartTime();
jaxbUtil.marshaller(of.createI2B2ResultEnvelope(resultEnvelop), strWriter);
subLogTimingUtil.setEndTime();
tm.begin();
IXmlResultDao xmlResultDao = sfDAOFactory.getXmlResultDao();
xmlResultDao.createQueryXmlResult(resultInstanceId, strWriter.toString());
//
if (processTimingFlag != null) {
if (!processTimingFlag.trim().equalsIgnoreCase(ProcessTimingReportUtil.NONE)) {
ProcessTimingReportUtil ptrUtil =
new ProcessTimingReportUtil(sfDAOFactory.getDataSourceLookup());
if (processTimingFlag.trim().equalsIgnoreCase(ProcessTimingReportUtil.DEBUG)) {
ptrUtil.logProcessTimingMessage(
queryInstanceId,
ptrUtil.buildProcessTiming(subLogTimingUtil, "JAXB - " + resultTypeName, ""));
}
logTimingUtil.setEndTime();
ptrUtil.logProcessTimingMessage(
queryInstanceId,
ptrUtil.buildProcessTiming(logTimingUtil, "BUILD - " + resultTypeName, ""));
}
}
tm.commit();
} catch (com.microsoft.sqlserver.jdbc.SQLServerException sqlServerEx) {
// if the setQueryTimeout worked, then the message would be timed
// out
if (sqlServerEx.getMessage().indexOf("timed out") > -1) {
timeoutFlag = true;
throw new CRCTimeOutException(sqlServerEx.getMessage(), sqlServerEx);
} else if (sqlServerEx
.getMessage()
.indexOf( // if the stmt.cancel()
// worked, then this
// exception is
// thrown
"The query was canceled.")
> -1) {
timeoutFlag = true;
throw new CRCTimeOutException(sqlServerEx.getMessage(), sqlServerEx);
} else {
errorFlag = true;
log.error("Sqlserver error while executing sql", sqlServerEx);
throw new I2B2DAOException("Sqlserver error while executing sql", sqlServerEx);
}
} catch (SQLException sqlEx) {
// catch oracle query timeout error ORA-01013
if (sqlEx.toString().indexOf("ORA-01013") > -1) {
timeoutFlag = true;
throw new CRCTimeOutException(sqlEx.getMessage(), sqlEx);
}
if (sqlEx.getMessage().indexOf("The query was canceled.") > -1) {
timeoutFlag = true;
throw new CRCTimeOutException(sqlEx.getMessage(), sqlEx);
}
errorFlag = true;
log.error("Error while executing sql", sqlEx);
throw new I2B2DAOException("Error while executing sql", sqlEx);
} catch (Exception sqlEx) {
errorFlag = true;
log.error("QueryResultPatientSetGenerator.generateResult:" + sqlEx.getMessage(), sqlEx);
throw new I2B2DAOException(
"QueryResultPatientSetGenerator.generateResult:" + sqlEx.getMessage(), sqlEx);
} finally {
IQueryResultInstanceDao resultInstanceDao = sfDAOFactory.getPatientSetResultDAO();
if (errorFlag) {
resultInstanceDao.updatePatientSet(
resultInstanceId, QueryStatusTypeId.STATUSTYPE_ID_ERROR, 0);
} else {
// set the setsize and the description of the result instance if
// the user role is obfuscated
if (timeoutFlag == false) { // check if the query completed
try {
tm.begin();
String obfusMethod = "", description = null;
if (obfscDataRoleFlag) {
obfusMethod = IQueryResultInstanceDao.OBSUBTOTAL;
// add () to the result type description
// read the description from result type
} else {
obfuscatedRecordCount = actualTotal;
}
IQueryResultTypeDao resultTypeDao = sfDAOFactory.getQueryResultTypeDao();
List<QtQueryResultType> resultTypeList =
resultTypeDao.getQueryResultTypeByName(resultTypeName);
// add "(Obfuscated)" in the description
// description = resultTypeList.get(0)
// .getDescription()
// + " (Obfuscated) ";
String queryName =
sfDAOFactory
.getQueryMasterDAO()
.getQueryDefinition(
sfDAOFactory
.getQueryInstanceDAO()
.getQueryInstanceByInstanceId(queryInstanceId)
.getQtQueryMaster()
.getQueryMasterId())
.getName();
resultInstanceDao.updatePatientSet(
resultInstanceId,
QueryStatusTypeId.STATUSTYPE_ID_FINISHED,
null,
// obsfcTotal,
obfuscatedRecordCount,
actualTotal,
obfusMethod);
description = resultTypeList.get(0).getDescription() + " for \"" + queryName + "\"";
// set the result instance description
resultInstanceDao.updateResultInstanceDescription(resultInstanceId, description);
tm.commit();
} catch (NotSupportedException e) {
throw new I2B2DAOException(
"Failed to write obfuscated description " + e.getMessage(), e);
} catch (SystemException e) {
throw new I2B2DAOException(
"Failed to write obfuscated description " + e.getMessage(), e);
} catch (SecurityException e) {
throw new I2B2DAOException(
"Failed to write obfuscated description " + e.getMessage(), e);
} catch (IllegalStateException e) {
throw new I2B2DAOException(
"Failed to write obfuscated description " + e.getMessage(), e);
} catch (RollbackException e) {
throw new I2B2DAOException(
"Failed to write obfuscated description " + e.getMessage(), e);
} catch (HeuristicMixedException e) {
throw new I2B2DAOException(
"Failed to write obfuscated description " + e.getMessage(), e);
} catch (HeuristicRollbackException e) {
throw new I2B2DAOException(
"Failed to write obfuscated description " + e.getMessage(), e);
}
}
}
}
}
public void setQueryTimeout(int seconds) throws SQLException {
statement.setQueryTimeout(seconds);
}
public void setQueryTimeout(int seconds) throws SQLException {
delegate.setQueryTimeout(seconds);
}
public Map<String, Set> single(Neo4jNode node1, int depth, String type, RelationQuery query)
throws SQLException {
Map<String, Set> map = new HashMap<String, Set>();
Set<Neo4jNode> nodes = new HashSet<Neo4jNode>();
Set<Neo4jRelationship> relationships = new HashSet<Neo4jRelationship>();
if (!type.equals("")) type = ":" + type;
StringBuffer sb = new StringBuffer("start ");
sb.append("a=node:nodes(");
if ("1".equals(node1.getNode_type())) {
sb.append("node");
} else {
sb.append("regno");
}
sb.append("={1}");
sb.append(") ");
sb.append("match p=a");
if (StringUtils.isNotNullOrEmpty(query.getFoward())) {
if ("down".equals(query.getFoward())) {
sb.append("<");
}
}
sb.append(" -[ ");
sb.append(type);
sb.append("*..");
sb.append(depth);
sb.append("]-");
if (StringUtils.isNotNullOrEmpty(query.getFoward())) {
if ("up".equals(query.getFoward())) {
sb.append(">");
}
}
sb.append(" b ");
sb.append(" where 1=1 ");
if (StringUtils.isNotNullOrEmpty(query.getEndStatus()) && query.getEndStatus().size() > 0) {
sb.append("and all(x in nodes(p) where x.entstatus='' ");
sb.append(" or ");
for (int i = 0; i < query.getEndStatus().size(); i++) {
sb.append(" x.entstatus='" + query.getEndStatus().get(i) + "'");
if (i != (query.getEndStatus().size() - 1)) {
sb.append(" or ");
}
}
sb.append(") ");
}
// all(x in nodes(p) where x.entstatus='')
if (StringUtils.isNotNullOrEmpty(query.getFoward())) { // 如果是只查询股东 需要包括人员
// entstatus=''
if ("down".equals(query.getFoward())) {
sb.append(" or b.entstatus='' ");
}
}
sb.append(" return nodes(p),relationships(p) order by length(p) ");
sb.append(" limit ");
sb.append(nodeLimit);
// "start a=node:nodes(node={1}) "
// + "match p=a-["+type+"*.."+depth+"]-b "
// + "return nodes(p),relationships(p) LIMIT 100"
conn = getConn();
PreparedStatement psmt = conn.prepareStatement(sb.toString());
psmt.setQueryTimeout(Integer.parseInt(queryTimeOut));
psmt.setString(1, node1.getNode());
// psmt.setInt(2, depth);
// psmt.setString(3, type);
ResultSet rs = psmt.executeQuery();
while (rs.next()) {
nodes.addAll(jsonNode(rs.getString(1)));
// Iterator<Neo4jNode> it=nodes.iterator();
// while(it.hasNext()){
// Neo4jNode i=it.next();
// System.out.println(i.getInv_name()+"="+i.getNode());
// }
relationships.addAll(jsonRelationship(rs.getString(2)));
}
map.put("nodes", nodes);
map.put("relationships", relationships);
return map;
}
public void setQueryTimeout(long millis) throws SQLException {
preparedStatement.setQueryTimeout(Long.valueOf(millis).intValue() / 1000);
}
protected void setQueryTimeout(PreparedStatement statement, int queryTimeoutInSeconds)
throws SQLException {
statement.setQueryTimeout(queryTimeoutInSeconds);
}
public void setQueryTimeout(int seconds) throws SQLException {
this.queryTimeout = seconds;
if (ps != null) {
ps.setQueryTimeout(seconds);
}
}
@Override
public void setQueryTimeout(int seconds) throws SQLException {
stmt.setQueryTimeout(seconds);
}
