List of usage examples for org.springframework.jdbc.core JdbcTemplate queryForObject
@Override
@Nullable
public <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException
From source file:dao.SearchDAO.java
public static ObjectNode getPagedFlowByKeyword(String category, String keyword, int page, int size) { final List<FlowJob> pagedFlows = new ArrayList<FlowJob>(); final JdbcTemplate jdbcTemplate = getJdbcTemplate(); javax.sql.DataSource ds = jdbcTemplate.getDataSource(); DataSourceTransactionManager tm = new DataSourceTransactionManager(ds); TransactionTemplate txTemplate = new TransactionTemplate(tm); ObjectNode result;//from w w w.ja v a 2 s.c o m result = txTemplate.execute(new TransactionCallback<ObjectNode>() { public ObjectNode doInTransaction(TransactionStatus status) { String query = SEARCH_FLOW_WITH_PAGINATION.replace("$keyword", keyword); List<Map<String, Object>> rows = null; rows = jdbcTemplate.queryForList(query, (page - 1) * size, size); for (Map row : rows) { FlowJob flow = new FlowJob(); flow.flowId = (Long) row.get(FlowRowMapper.FLOW_ID_COLUMN); flow.flowName = (String) row.get(FlowRowMapper.FLOW_NAME_COLUMN); flow.flowPath = (String) row.get(FlowRowMapper.FLOW_PATH_COLUMN); flow.flowGroup = (String) row.get(FlowRowMapper.FLOW_GROUP_COLUMN); flow.appCode = (String) row.get(FlowRowMapper.APP_CODE_COLUMN); flow.appId = (Integer) row.get(FlowRowMapper.APP_ID_COLUMN); flow.displayName = flow.flowName; flow.link = "#/flows/name/" + flow.appCode + "/" + Long.toString(flow.flowId) + "/page/1?urn=" + flow.flowGroup; flow.path = flow.appCode + "/" + flow.flowPath; pagedFlows.add(flow); } long count = 0; try { count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class); } catch (EmptyResultDataAccessException e) { Logger.error("Exception = " + e.getMessage()); } ObjectNode resultNode = Json.newObject(); resultNode.put("count", count); resultNode.put("isFlowJob", true); resultNode.put("page", page); resultNode.put("category", category); resultNode.put("itemsPerPage", size); resultNode.put("totalPages", (int) Math.ceil(count / ((double) size))); resultNode.set("data", Json.toJson(pagedFlows)); return resultNode; } }); return result; }
From source file:uk.ac.kcl.partitioners.RealtimePKRangePartitioner.java
private ScheduledPartitionParams getParams(Timestamp startTimeStamp) { JdbcTemplate jdbcTemplate = new JdbcTemplate(sourceDataSource); String sql = "\n SELECT " + " MAX(" + column + ") AS max_id , \n" + " MIN(" + column + ") AS min_id , \n" + " MAX(" + timeStamp + ") AS max_time_stamp , \n" + " MIN(" + timeStamp + ") AS min_time_stamp \n" + " FROM " + table; if (configuredFirstRunTimestamp != null && firstRun) { sql = sql + " WHERE " + timeStamp + " >= CAST ('" + startTimeStamp.toString() + "' as " + env.getProperty("dbmsToJavaSqlTimestampType") + " ) "; } else if (startTimeStamp == null) { Timestamp newStartTimeStamp = getLastTimestampFromLastSuccessfulJob(); logger.info("Commencing from after " + newStartTimeStamp.toString()); sql = sql + "\n WHERE CAST (" + timeStamp + " as " + env.getProperty("dbmsToJavaSqlTimestampType") + " ) > CAST ('" + newStartTimeStamp.toString() + "' as " + env.getProperty("dbmsToJavaSqlTimestampType") + " ) "; } else if (firstRun) { //no new SQL required - process all data for first run logger.debug("first run"); } else {//from ww w. j a va2 s . c o m throw new RuntimeException("unable to determine partition requirement"); } logger.info("This job SQL: " + sql); return (ScheduledPartitionParams) jdbcTemplate.queryForObject(sql, new PartitionParamsRowMapper()); }
From source file:data.DefaultExchanger.java
public void exportData(String dbName, String catalogName, final JsonGenerator generator, JdbcTemplate jdbcTemplate) throws IOException { generator.writeFieldName(getTable()); generator.writeStartArray();// www . ja va2 s. c om final int[] rowCount = { 0 }; jdbcTemplate.query(getSelectSql(), new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { try { generator.writeStartObject(); setNode(generator, rs); generator.writeEndObject(); rowCount[0]++; } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } } }); generator.writeEndArray(); play.Logger.info("exported {{}} {}", rowCount[0], getTable()); if (hasSequence()) { String sequenceName = sequenceName(); long sequenceValue = 0; if (dbName.equalsIgnoreCase("MySQL")) { String sql = String.format("SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES " + "WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'", catalogName, getTable()); sequenceValue = jdbcTemplate.queryForObject(sql, Long.class); } else if (dbName.equalsIgnoreCase("H2")) { sequenceValue = jdbcTemplate.queryForObject("CALL NEXT VALUE FOR " + sequenceName, Long.class); } generator.writeFieldName(sequenceName); generator.writeNumber(sequenceValue); play.Logger.info("exported sequence {{}}", sequenceName()); } }
From source file:dao.SearchDAO.java
public static ObjectNode getPagedJobByKeyword(String category, String keyword, int page, int size) { final List<FlowJob> pagedFlowJobs = new ArrayList<FlowJob>(); final JdbcTemplate jdbcTemplate = getJdbcTemplate(); javax.sql.DataSource ds = jdbcTemplate.getDataSource(); DataSourceTransactionManager tm = new DataSourceTransactionManager(ds); TransactionTemplate txTemplate = new TransactionTemplate(tm); ObjectNode result;//from w ww . j a va2s.c o m result = txTemplate.execute(new TransactionCallback<ObjectNode>() { public ObjectNode doInTransaction(TransactionStatus status) { String query = SEARCH_JOB_WITH_PAGINATION.replace("$keyword", keyword); List<Map<String, Object>> rows = null; rows = jdbcTemplate.queryForList(query, (page - 1) * size, size); for (Map row : rows) { FlowJob flowJob = new FlowJob(); flowJob.flowId = (Long) row.get(FlowRowMapper.FLOW_ID_COLUMN); flowJob.jobId = (Long) row.get(FlowRowMapper.JOB_ID_COLUMN); flowJob.jobName = (String) row.get(FlowRowMapper.JOB_NAME_COLUMN); flowJob.jobPath = (String) row.get(FlowRowMapper.JOB_PATH_COLUMN); flowJob.jobType = (String) row.get(FlowRowMapper.JOB_TYPE_COLUMN); flowJob.flowName = (String) row.get(FlowRowMapper.FLOW_NAME_COLUMN); flowJob.flowPath = (String) row.get(FlowRowMapper.FLOW_PATH_COLUMN); flowJob.flowGroup = (String) row.get(FlowRowMapper.FLOW_GROUP_COLUMN); flowJob.appCode = (String) row.get(FlowRowMapper.APP_CODE_COLUMN); flowJob.appId = (Integer) row.get(FlowRowMapper.APP_ID_COLUMN); flowJob.displayName = flowJob.jobName; flowJob.link = "#/flows/name/" + flowJob.appCode + "/" + Long.toString(flowJob.flowId) + "/page/1?urn=" + flowJob.flowGroup; flowJob.path = flowJob.appCode + "/" + flowJob.jobPath; pagedFlowJobs.add(flowJob); } long count = 0; try { count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class); } catch (EmptyResultDataAccessException e) { Logger.error("Exception = " + e.getMessage()); } ObjectNode resultNode = Json.newObject(); resultNode.put("count", count); resultNode.put("isFlowJob", true); resultNode.put("page", page); resultNode.put("category", category); resultNode.put("itemsPerPage", size); resultNode.put("totalPages", (int) Math.ceil(count / ((double) size))); resultNode.set("data", Json.toJson(pagedFlowJobs)); return resultNode; } }); return result; }
From source file:dao.MetricsDAO.java
public static ObjectNode getPagedMetrics(String dashboardName, String group, Integer page, Integer size, String user) {/*w w w . j a va 2 s . c o m*/ Integer userId = UserDAO.getUserIDByUserName(user); final JdbcTemplate jdbcTemplate = getJdbcTemplate(); javax.sql.DataSource ds = jdbcTemplate.getDataSource(); DataSourceTransactionManager tm = new DataSourceTransactionManager(ds); TransactionTemplate txTemplate = new TransactionTemplate(tm); ObjectNode result; final Integer id = userId; result = txTemplate.execute(new TransactionCallback<ObjectNode>() { public ObjectNode doInTransaction(TransactionStatus status) { List<Map<String, Object>> rows; if (StringUtils.isBlank(dashboardName)) { rows = jdbcTemplate.queryForList(SELECT_PAGED_METRICS, id, (page - 1) * size, size); } else if (StringUtils.isBlank(group)) { String dbName; if (dashboardName.equals("[Other]")) { dbName = null; } else { dbName = dashboardName; } rows = jdbcTemplate.queryForList(SELECT_PAGED_METRICS_BY_DASHBOARD_NAME, id, dbName, dbName, (page - 1) * size, size); } else { String dbName; if (dashboardName.equals("[Other]")) { dbName = null; } else { dbName = dashboardName; } String grp; if (group.equals("[Other]")) { grp = null; } else { grp = group; } rows = jdbcTemplate.queryForList(SELECT_PAGED_METRICS_BY_DASHBOARD_AND_GROUP, id, dbName, dbName, grp, grp, (page - 1) * size, size); } List<Metric> pagedMetrics = new ArrayList<>(); for (Map row : rows) { Metric metric = new Metric(); metric.id = (int) row.get("metric_id"); metric.name = (String) row.get("metric_name"); metric.description = (String) row.get("metric_description"); metric.refID = (String) row.get("metric_ref_id"); metric.refIDType = (String) row.get("metric_ref_id_type"); metric.dashboardName = (String) row.get("dashboard_name"); metric.category = (String) row.get("metric_category"); metric.group = (String) row.get("metric_group"); metric.watchId = (Long) row.get("watch_id"); pagedMetrics.add(metric); } long count = 0; try { count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class); } catch (EmptyResultDataAccessException e) { Logger.error("Exception = " + e.getMessage()); } ObjectNode resultNode = Json.newObject(); resultNode.put("count", count); resultNode.put("page", page); resultNode.put("itemsPerPage", size); resultNode.put("totalPages", (int) Math.ceil(count / ((double) size))); resultNode.set("metrics", Json.toJson(pagedMetrics)); return resultNode; } }); return result; }
From source file:dao.SearchDAO.java
public static ObjectNode getPagedMetricByKeyword(final String category, String keyword, int page, int size) { List<Metric> pagedMetrics = new ArrayList<Metric>(); final JdbcTemplate jdbcTemplate = getJdbcTemplate(); javax.sql.DataSource ds = jdbcTemplate.getDataSource(); DataSourceTransactionManager tm = new DataSourceTransactionManager(ds); TransactionTemplate txTemplate = new TransactionTemplate(tm); ObjectNode result;/*from w w w .j a v a 2 s. c o m*/ result = txTemplate.execute(new TransactionCallback<ObjectNode>() { public ObjectNode doInTransaction(TransactionStatus status) { String query = SEARCH_METRIC_WITH_PAGINATION.replace("$keyword", keyword); List<Map<String, Object>> rows = null; rows = jdbcTemplate.queryForList(query, (page - 1) * size, size); for (Map row : rows) { Metric metric = new Metric(); metric.id = (Integer) row.get(MetricRowMapper.METRIC_ID_COLUMN); metric.name = (String) row.get(MetricRowMapper.METRIC_NAME_COLUMN); metric.refID = (String) row.get(MetricRowMapper.METRIC_REF_ID_COLUMN); metric.refIDType = (String) row.get(MetricRowMapper.METRIC_REF_ID_TYPE_COLUMN); metric.description = (String) row.get(MetricRowMapper.METRIC_DESCRIPTION_COLUMN); metric.dashboardName = (String) row.get(MetricRowMapper.METRIC_DASHBOARD_NAME_COLUMN); metric.category = (String) row.get(MetricRowMapper.METRIC_CATEGORY_COLUMN); metric.group = (String) row.get(MetricRowMapper.METRIC_GROUP_COLUMN); metric.source = "metric"; metric.urn = ""; if (StringUtils.isNotBlank(metric.dashboardName)) { metric.urn += metric.dashboardName + "/"; } if (StringUtils.isNotBlank(metric.group)) { metric.urn += metric.group + "/"; } if (StringUtils.isNotBlank(metric.name)) { metric.urn += metric.name; } ObjectNode schema = Json.newObject(); schema.put(MetricRowMapper.METRIC_REF_ID_COLUMN, metric.refID); schema.put(MetricRowMapper.METRIC_REF_ID_TYPE_COLUMN, metric.refIDType); schema.put(MetricRowMapper.METRIC_DESCRIPTION_COLUMN, metric.description); schema.put(MetricRowMapper.METRIC_DASHBOARD_NAME_COLUMN, metric.dashboardName); schema.put(MetricRowMapper.METRIC_CATEGORY_COLUMN, metric.category); schema.put(MetricRowMapper.METRIC_GROUP_COLUMN, metric.group); metric.schema = schema.toString(); pagedMetrics.add(metric); } long count = 0; try { count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class); } catch (EmptyResultDataAccessException e) { Logger.error("Exception = " + e.getMessage()); } ObjectNode resultNode = Json.newObject(); resultNode.put("count", count); resultNode.put("page", page); resultNode.put("category", category); resultNode.put("itemsPerPage", size); resultNode.put("totalPages", (int) Math.ceil(count / ((double) size))); resultNode.set("data", Json.toJson(pagedMetrics)); return resultNode; } }); return result; }
From source file:ca.nrc.cadc.vos.server.NodeDAOTest.java
@Test public void testDatabaseDateRoundTrip() { log.debug("testDatabaseDateRoundTrip - START"); try {/*from w w w. ja v a2 s . co m*/ DBConfig dbConfig = new DBConfig(); ConnectionConfig connConfig = dbConfig.getConnectionConfig(SERVER, DATABASE); this.dataSource = DBUtil.getDataSource(connConfig); NodeSchema ns = new NodeSchema("Node", "NodeProperty", true); // TOP this.nodeDAO = new NodeDAO(dataSource, ns, VOS_AUTHORITY, new X500IdentityManager(), DELETED_NODES); ContainerNode rootContainer = (ContainerNode) nodeDAO.getPath(HOME_CONTAINER); log.debug("ROOT: " + rootContainer); Assert.assertNotNull(rootContainer); String basePath = "/" + HOME_CONTAINER + "/"; // Create a container node String containerPath = basePath + getNodeName("trickle-test2"); ContainerNode containerNode = this.getCommonContainerNode(containerPath); containerNode.setParent(rootContainer); containerNode = (ContainerNode) nodeDAO.put(containerNode, owner); // Create a data node String dataPath = containerNode.getUri().getPath() + "/" + "dataNode" + System.currentTimeMillis(); DataNode dataNode = getCommonDataNode(dataPath); dataNode.setParent(containerNode); nodeDAO.put(dataNode, owner); // manually set the busy state JdbcTemplate jdbc = new JdbcTemplate(dataSource); String sql = "update Node set busyState='W' where name='" + dataNode.getName() + "'"; jdbc.update(sql); // update the metadata, using the strict option nodeDAO.updateNodeMetadata(dataNode, new FileMetadata(), true); // ensure the state is back to normal sql = "select busyState from Node where name='" + dataNode.getName() + "'"; String curState = (String) jdbc.queryForObject(sql, String.class); Assert.assertEquals("Wrong busy state", "N", curState); // manually reset the busy state sql = "update Node set busyState='W' where name='" + dataNode.getName() + "'"; jdbc.update(sql); // modify some metadata (this will tweak the date) List<NodeProperty> properties = new ArrayList<NodeProperty>(); properties.add(new NodeProperty(VOS.PROPERTY_URI_ISLOCKED, "true")); nodeDAO.updateProperties(dataNode, properties); // update the metadata again (should get illegal argument exception) try { nodeDAO.updateNodeMetadata(dataNode, new FileMetadata(), true); Assert.fail("Strict option failed."); } catch (IllegalStateException e) { // expected } } catch (Exception unexpected) { unexpected.printStackTrace(); log.error("unexpected exception", unexpected); Assert.fail("unexpected exception: " + unexpected); } finally { log.debug("testDatabaseDateRoundTrip - DONE"); } }
From source file:dao.AdvSearchDAO.java
public static ObjectNode search(JsonNode searchOpt, int page, int size) { ObjectNode resultNode = Json.newObject(); int count = 0; List<String> scopeInList = new ArrayList<String>(); List<String> scopeNotInList = new ArrayList<String>(); List<String> tableInList = new ArrayList<String>(); List<String> tableNotInList = new ArrayList<String>(); List<String> fieldAnyList = new ArrayList<String>(); List<String> fieldAllList = new ArrayList<String>(); List<String> fieldNotInList = new ArrayList<String>(); String fieldAllIDs = ""; String comments = ""; if (searchOpt != null && (searchOpt.isContainerNode())) { if (searchOpt.has("scope")) { JsonNode scopeNode = searchOpt.get("scope"); if (scopeNode != null && scopeNode.isContainerNode()) { if (scopeNode.has("in")) { JsonNode scopeInNode = scopeNode.get("in"); if (scopeInNode != null) { String scopeInStr = scopeInNode.asText(); if (StringUtils.isNotBlank(scopeInStr)) { String[] scopeInArray = scopeInStr.split(","); if (scopeInArray != null) { for (String value : scopeInArray) { if (StringUtils.isNotBlank(value)) { scopeInList.add(value.trim()); }/*from ww w.j av a2 s .co m*/ } } } } } if (scopeNode.has("not")) { JsonNode scopeNotInNode = scopeNode.get("not"); if (scopeNotInNode != null) { String scopeNotInStr = scopeNotInNode.asText(); if (StringUtils.isNotBlank(scopeNotInStr)) { String[] scopeNotInArray = scopeNotInStr.split(","); if (scopeNotInArray != null) { for (String value : scopeNotInArray) { if (StringUtils.isNotBlank(value)) { scopeNotInList.add(value.trim()); } } } } } } } } if (searchOpt.has("table")) { JsonNode tableNode = searchOpt.get("table"); if (tableNode != null && tableNode.isContainerNode()) { if (tableNode.has("in")) { JsonNode tableInNode = tableNode.get("in"); if (tableInNode != null) { String tableInStr = tableInNode.asText(); if (StringUtils.isNotBlank(tableInStr)) { String[] tableInArray = tableInStr.split(","); if (tableInArray != null) { for (String value : tableInArray) { if (StringUtils.isNotBlank(value)) { tableInList.add(value.trim()); } } } } } } if (tableNode.has("not")) { JsonNode tableNotInNode = tableNode.get("not"); if (tableNotInNode != null) { String tableNotInStr = tableNotInNode.asText(); if (StringUtils.isNotBlank(tableNotInStr)) { String[] tableNotInArray = tableNotInStr.split(","); if (tableNotInArray != null) { for (String value : tableNotInArray) { if (StringUtils.isNotBlank(value)) { tableNotInList.add(value.trim()); } } } } } } } } if (searchOpt.has("fields")) { JsonNode fieldNode = searchOpt.get("fields"); if (fieldNode != null && fieldNode.isContainerNode()) { if (fieldNode.has("any")) { JsonNode fieldAnyNode = fieldNode.get("any"); if (fieldAnyNode != null) { String fieldAnyStr = fieldAnyNode.asText(); if (StringUtils.isNotBlank(fieldAnyStr)) { String[] fieldAnyArray = fieldAnyStr.split(","); if (fieldAnyArray != null) { for (String value : fieldAnyArray) { if (StringUtils.isNotBlank(value)) { fieldAnyList.add(value.trim()); } } } } } } if (fieldNode.has("all")) { JsonNode fieldAllNode = fieldNode.get("all"); if (fieldAllNode != null) { String fieldAllStr = fieldAllNode.asText(); if (StringUtils.isNotBlank(fieldAllStr)) { String[] fieldAllArray = fieldAllStr.split(","); if (fieldAllArray != null) { for (String value : fieldAllArray) { if (StringUtils.isNotBlank(value)) { fieldAllList.add(value.trim()); } } } } } } if (fieldNode.has("not")) { JsonNode fieldNotInNode = fieldNode.get("not"); if (fieldNotInNode != null) { String fieldNotInStr = fieldNotInNode.asText(); if (StringUtils.isNotBlank(fieldNotInStr)) { String[] fieldNotInArray = fieldNotInStr.split(","); if (fieldNotInArray != null) { for (String value : fieldNotInArray) { if (StringUtils.isNotBlank(value)) { fieldNotInList.add(value.trim()); } } } } } } } } String datasetSources = ""; if (searchOpt.has("sources")) { JsonNode sourcesNode = searchOpt.get("sources"); if (sourcesNode != null) { datasetSources = sourcesNode.asText(); } } boolean needAndKeyword = false; int fieldQueryIndex = 0; if (fieldAllList.size() > 0) { String fieldAllQuery = "SELECT DISTINCT f1.dataset_id FROM dict_field_detail f1 "; String fieldWhereClause = " WHERE "; for (String field : fieldAllList) { fieldQueryIndex++; if (fieldQueryIndex == 1) { fieldWhereClause += "f1.field_name LIKE '%" + field + "%' "; } else { fieldAllQuery += "JOIN dict_field_detail f" + fieldQueryIndex + " ON f" + (fieldQueryIndex - 1) + ".dataset_id = f" + fieldQueryIndex + ".dataset_id "; fieldWhereClause += " and f" + fieldQueryIndex + ".field_name LIKE '%" + field + "%' "; } } fieldAllQuery += fieldWhereClause; List<Map<String, Object>> rows = getJdbcTemplate().queryForList(fieldAllQuery); for (Map row : rows) { fieldAllIDs += (Long) row.get("dataset_id") + ","; } if (fieldAllIDs.length() > 0) { fieldAllIDs = fieldAllIDs.substring(0, fieldAllIDs.length() - 1); } if (StringUtils.isBlank(fieldAllIDs)) { fieldAllIDs = Integer.toString(0); } } List<Dataset> pagedDatasets = new ArrayList<Dataset>(); final JdbcTemplate jdbcTemplate = getJdbcTemplate(); javax.sql.DataSource ds = jdbcTemplate.getDataSource(); DataSourceTransactionManager tm = new DataSourceTransactionManager(ds); TransactionTemplate txTemplate = new TransactionTemplate(tm); ObjectNode result; if (searchOpt.has("comments")) { JsonNode commentsNode = searchOpt.get("comments"); if (commentsNode != null) { comments = commentsNode.asText(); if (StringUtils.isNotBlank(comments)) { if (scopeInList.size() == 0 && scopeNotInList.size() == 0 && tableInList.size() == 0 && tableNotInList.size() == 0 && fieldAllList.size() == 0 && fieldAnyList.size() == 0 && fieldNotInList.size() == 0) { final String commentsQueryStr = SEARCH_DATASETS_BY_COMMENTS_WITH_PAGINATION .replace("$keyword", comments); result = txTemplate.execute(new TransactionCallback<ObjectNode>() { public ObjectNode doInTransaction(TransactionStatus status) { List<Map<String, Object>> rows = null; rows = jdbcTemplate.queryForList(commentsQueryStr, (page - 1) * size, size); for (Map row : rows) { Dataset ds = new Dataset(); ds.id = (Long) row.get("id"); ds.name = (String) row.get("name"); ds.source = (String) row.get("source"); ds.urn = (String) row.get("urn"); ds.schema = (String) row.get("schema"); pagedDatasets.add(ds); } long count = 0; try { count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class); } catch (EmptyResultDataAccessException e) { Logger.error("Exception = " + e.getMessage()); } ObjectNode resultNode = Json.newObject(); resultNode.put("count", count); resultNode.put("page", page); resultNode.put("itemsPerPage", size); resultNode.put("totalPages", (int) Math.ceil(count / ((double) size))); resultNode.set("data", Json.toJson(pagedDatasets)); return resultNode; } }); return result; } } } } String query = ""; if (StringUtils.isNotBlank(comments)) { query = "SELECT DISTINCT d.id FROM dict_dataset d"; } else { query = "SELECT SQL_CALC_FOUND_ROWS " + "DISTINCT d.id, d.name, d.schema, d.source, d.urn, " + "FROM_UNIXTIME(d.source_modified_time) as modified FROM dict_dataset d"; } if (fieldAllList.size() > 0 || fieldAnyList.size() > 0 || fieldNotInList.size() > 0) { String fieldQuery = "SELECT DISTINCT dataset_id FROM dict_field_detail f WHERE ("; query += " WHERE d.id IN ( "; query += fieldQuery; String whereClause = ""; boolean fieldNeedAndKeyword = false; if (fieldAnyList.size() > 0) { whereClause = " ("; int indexForAnyList = 0; for (String field : fieldAnyList) { if (indexForAnyList == 0) { whereClause += "f.field_name LIKE '%" + field + "%'"; } else { whereClause += " or f.field_name LIKE '%" + field + "%'"; } indexForAnyList++; } whereClause += " ) "; fieldNeedAndKeyword = true; query += whereClause; } if (fieldAllList.size() > 0 && StringUtils.isNotBlank(fieldAllIDs)) { if (fieldNeedAndKeyword) { whereClause = " and ("; } else { whereClause = " ("; } whereClause += "f.dataset_id IN (" + fieldAllIDs + ")"; whereClause += " ) "; query += whereClause; fieldNeedAndKeyword = true; } if (fieldNotInList.size() > 0) { if (fieldNeedAndKeyword) { whereClause = " and ( f.dataset_id not in (select dataset_id from dict_field_detail where"; } else { whereClause = " ( f.dataset_id not in (select dataset_id from dict_field_detail where"; } int indexForNotInList = 0; for (String field : fieldNotInList) { if (indexForNotInList == 0) { whereClause += " field_name LIKE '%" + field + "%'"; } else { whereClause += " or field_name LIKE '%" + field + "%'"; } indexForNotInList++; } whereClause += " )) "; query += whereClause; fieldNeedAndKeyword = true; } needAndKeyword = true; query += ") )"; } if (scopeInList.size() > 0 || scopeNotInList.size() > 0) { if (needAndKeyword) { query += " and"; } else { query += " where"; } boolean scopeNeedAndKeyword = false; if (scopeInList.size() > 0) { query += " d.parent_name in ("; scopeNeedAndKeyword = true; int indexForScopeInList = 0; for (String scope : scopeInList) { if (indexForScopeInList == 0) { query += "'" + scope + "'"; } else { query += ", '" + scope + "'"; } indexForScopeInList++; } query += ") "; } if (scopeNotInList.size() > 0) { if (scopeNeedAndKeyword) { query += " and d.parent_name not in ("; } else { query += " d.parent_name not in ("; } int indexForScopeNotInList = 0; for (String scope : scopeNotInList) { if (indexForScopeNotInList == 0) { query += "'" + scope + "'"; } else { query += ", '" + scope + "'"; } indexForScopeNotInList++; } query += ") "; } needAndKeyword = true; } String condition1 = ""; String condition2 = ""; String condition3 = ""; String condition4 = ""; if (tableInList.size() > 0 || tableNotInList.size() > 0) { if (needAndKeyword) { query += " and"; } else { query += " where"; } boolean tableNeedAndKeyword = false; if (tableInList.size() > 0) { query += " ("; int indexForTableInList = 0; for (String table : tableInList) { if (indexForTableInList == 0) { query += "d.name LIKE '%" + table + "%'"; } else { condition1 += " or "; condition2 += " or "; condition3 += " or "; condition4 += " or "; query += " or d.name LIKE '%" + table + "%'"; } condition1 += "name = '" + table + "'"; condition2 += "name LIKE '" + table + "%'"; condition3 += "name LIKE '%" + table + "'"; condition4 += "name LIKE '%" + table + "%'"; indexForTableInList++; } query += " ) "; tableNeedAndKeyword = true; } if (tableNotInList.size() > 0) { if (tableNeedAndKeyword) { query += " and ("; } else { query += " ("; } int indexForTableNotInList = 0; for (String table : tableNotInList) { if (indexForTableNotInList == 0) { query += "d.name NOT LIKE '%" + table + "%'"; } else { query += " and d.name NOT LIKE '%" + table + "%'"; } indexForTableNotInList++; } query += " ) "; } needAndKeyword = true; } if (StringUtils.isNotBlank(datasetSources)) { if (needAndKeyword) { query += " and"; } else { query += " WHERE"; } query += " d.source in ("; String[] dataestSourceArray = datasetSources.split(","); for (int i = 0; i < dataestSourceArray.length; i++) { query += "'" + dataestSourceArray[i] + "'"; if (i != (dataestSourceArray.length - 1)) { query += ","; } } query += ")"; } if ((tableInList.size() > 0 || tableNotInList.size() > 0) && StringUtils.isNotBlank(condition1) && StringUtils.isNotBlank(condition2) && StringUtils.isNotBlank(condition3) && StringUtils.isNotBlank(condition4)) { query += ADVSEARCH_RANK_CLAUSE.replace("$condition1", condition1).replace("$condition2", condition2) .replace("$condition3", condition3).replace("$condition4", condition4); } else { query += " ORDER BY CASE WHEN urn LIKE 'teradata://DWH_%' THEN 2 " + "WHEN urn LIKE 'hdfs://data/tracking/%' THEN 1 " + "WHEN urn LIKE 'teradata://DWH/%' THEN 3 " + "WHEN urn LIKE 'hdfs://data/databases/%' THEN 4 " + "WHEN urn LIKE 'hdfs://data/dervied/%' THEN 5 ELSE 99 end, urn"; } if (StringUtils.isBlank(comments)) { query += " LIMIT " + (page - 1) * size + ", " + size; final String queryString = query; result = txTemplate.execute(new TransactionCallback<ObjectNode>() { public ObjectNode doInTransaction(TransactionStatus status) { List<Map<String, Object>> rows = null; rows = jdbcTemplate.queryForList(queryString); for (Map row : rows) { Dataset ds = new Dataset(); ds.id = (Long) row.get("id"); ds.name = (String) row.get("name"); ds.source = (String) row.get("source"); ds.urn = (String) row.get("urn"); ds.schema = (String) row.get("schema"); pagedDatasets.add(ds); } long count = 0; try { count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class); } catch (EmptyResultDataAccessException e) { Logger.error("Exception = " + e.getMessage()); } ObjectNode resultNode = Json.newObject(); resultNode.put("count", count); resultNode.put("page", page); resultNode.put("itemsPerPage", size); resultNode.put("totalPages", (int) Math.ceil(count / ((double) size))); resultNode.set("data", Json.toJson(pagedDatasets)); return resultNode; } }); return result; } else { String datasetIDStr = ""; final String queryString = query; datasetIDStr = txTemplate.execute(new TransactionCallback<String>() { public String doInTransaction(TransactionStatus status) { List<Map<String, Object>> rows = null; rows = jdbcTemplate.queryForList(queryString); String idsString = ""; for (Map row : rows) { Long id = (Long) row.get("id"); idsString += Long.toString(id) + ","; } if (StringUtils.isNotBlank(idsString)) { idsString = idsString.substring(0, idsString.length() - 1); } return idsString; } }); if (StringUtils.isBlank(datasetIDStr)) { resultNode.put("count", 0); resultNode.put("page", page); resultNode.put("itemsPerPage", size); resultNode.put("totalPages", 0); resultNode.set("data", Json.toJson("")); return resultNode; } final String commentsQueryWithConditionStr = DATASET_BY_COMMENT_PAGINATION_IN_CLAUSE .replace("$keyword", comments).replace("$id_list", datasetIDStr); result = txTemplate.execute(new TransactionCallback<ObjectNode>() { public ObjectNode doInTransaction(TransactionStatus status) { List<Map<String, Object>> rows = null; rows = jdbcTemplate.queryForList(commentsQueryWithConditionStr, (page - 1) * size, size); for (Map row : rows) { Dataset ds = new Dataset(); ds.id = (Long) row.get("id"); ds.name = (String) row.get("name"); ds.source = (String) row.get("source"); ds.urn = (String) row.get("urn"); ds.schema = (String) row.get("schema"); pagedDatasets.add(ds); } long count = 0; try { count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class); } catch (EmptyResultDataAccessException e) { Logger.error("Exception = " + e.getMessage()); } ObjectNode resultNode = Json.newObject(); resultNode.put("count", count); resultNode.put("page", page); resultNode.put("itemsPerPage", size); resultNode.put("totalPages", (int) Math.ceil(count / ((double) size))); resultNode.set("data", Json.toJson(pagedDatasets)); return resultNode; } }); return result; } } resultNode.put("count", 0); resultNode.put("page", page); resultNode.put("itemsPerPage", size); resultNode.put("totalPages", 0); resultNode.set("data", Json.toJson("")); return resultNode; }
From source file:ca.nrc.cadc.vos.server.NodeDAOTest.java
@Test public void testPropagationNewThenApply() { log.debug("testPropagationNewThenApply - START"); try {/*from ww w .j ava2 s.c om*/ DBConfig dbConfig = new DBConfig(); ConnectionConfig connConfig = dbConfig.getConnectionConfig(SERVER, DATABASE); this.dataSource = DBUtil.getDataSource(connConfig); NodeSchema ns = new NodeSchema("Node", "NodeProperty", true); // TOP this.nodeDAO = new NodeDAO(dataSource, ns, VOS_AUTHORITY, new X500IdentityManager(), DELETED_NODES); ContainerNode rootContainer = (ContainerNode) nodeDAO.getPath(HOME_CONTAINER); log.debug("ROOT: " + rootContainer); Assert.assertNotNull(rootContainer); String basePath = "/" + HOME_CONTAINER + "/"; // Create a container node String containerName = getNodeName("trickle-test3"); String containerPath = basePath + containerName; ContainerNode containerNode = this.getCommonContainerNode(containerPath); containerNode.setParent(rootContainer); containerNode = (ContainerNode) nodeDAO.put(containerNode, owner); // create a data node String dataPath = containerNode.getUri().getPath() + "/" + "dataNode" + System.currentTimeMillis(); DataNode dataNode = getCommonDataNode(dataPath); dataNode.setParent(containerNode); nodeDAO.put(dataNode, owner); JdbcTemplate jdbc = new JdbcTemplate(dataSource); // ensure the contentLength is zero to start String sql = "select contentLength from Node where name='" + dataNode.getName() + "'"; Long contentLength = jdbc.queryForLong(sql); Assert.assertEquals("Wrong content length", Long.valueOf(0L), contentLength); // ensure the data node delta is zero to start sql = "select delta from Node where name='" + dataNode.getName() + "'"; Long delta = jdbc.queryForLong(sql); Assert.assertEquals("Wrong delta", Long.valueOf(0L), delta); // manually set the busy state sql = "update Node set busyState='W' where name='" + dataNode.getName() + "'"; jdbc.update(sql); // update the metadata, using the strict option FileMetadata metadata = new FileMetadata(); metadata.setContentLength(10L); metadata.setMd5Sum("a94fc20c049422af7c591e2984f1f82d"); nodeDAO.updateNodeMetadata(dataNode, metadata, true); // ensure the state is back to normal sql = "select busyState from Node where name='" + dataNode.getName() + "'"; String curState = (String) jdbc.queryForObject(sql, String.class); Assert.assertEquals("Wrong busy state", "N", curState); // ensure the contentLength is correct sql = "select contentLength from Node where name='" + dataNode.getName() + "'"; contentLength = jdbc.queryForLong(sql); Assert.assertEquals("Wrong content length", Long.valueOf(10L), contentLength); // ensure the data node delta is correct sql = "select delta from Node where name='" + dataNode.getName() + "'"; delta = jdbc.queryForLong(sql); Assert.assertEquals("Wrong delta", Long.valueOf(10L), delta); // ensure the md5sum is correct sql = "select contentMD5 from Node where name='" + dataNode.getName() + "'"; String md5sum = (String) jdbc.queryForObject(sql, String.class); Assert.assertEquals("Wrong md5 sum", "a94fc20c049422af7c591e2984f1f82d".toUpperCase(), md5sum.toUpperCase()); // get the nodeID sql = "select nodeID from Node where name='" + dataNode.getName() + "'"; long nodeID = jdbc.queryForLong(sql); log.debug("nodeID is " + nodeID); // get the propagation List<NodeSizePropagation> propagations = nodeDAO.getOutstandingPropagations(100, false); NodeSizePropagation propagation = null; for (NodeSizePropagation next : propagations) { log.debug("Looking at propgation with nodeID: " + next.getChildID()); if (next.getChildID() == nodeID) propagation = next; } Assert.assertNotNull("Null propagation", propagation); // apply the propagation nodeDAO.applyPropagation(propagation); // ensure the data node content length is correct sql = "select contentLength from Node where name='" + dataNode.getName() + "'"; contentLength = jdbc.queryForLong(sql); Assert.assertEquals("Wrong content length", Long.valueOf(10L), contentLength); // ensure the data node delta is correct sql = "select delta from Node where name='" + dataNode.getName() + "'"; delta = jdbc.queryForLong(sql); Assert.assertEquals("Wrong delta", Long.valueOf(0L), delta); // ensure the container node content length is correct sql = "select contentLength from Node where name='" + containerName + "'"; contentLength = jdbc.queryForLong(sql); Assert.assertEquals("Wrong container content length", Long.valueOf(0L), contentLength); // ensure the container node delta is correct sql = "select delta from Node where name='" + containerName + "'"; delta = jdbc.queryForLong(sql); Assert.assertEquals("Wrong container delta", Long.valueOf(10L), delta); } catch (Exception unexpected) { unexpected.printStackTrace(); log.error("unexpected exception", unexpected); Assert.fail("unexpected exception: " + unexpected); } finally { log.debug("testPropagationNewThenApply - DONE"); } }
From source file:ca.nrc.cadc.vos.server.NodeDAOTest.java
@Test public void testPropagationNewReplaceThenApply() { log.debug("testPropagationNewReplaceThenApply - START"); try {/*from w w w. java 2s. c o m*/ DBConfig dbConfig = new DBConfig(); ConnectionConfig connConfig = dbConfig.getConnectionConfig(SERVER, DATABASE); this.dataSource = DBUtil.getDataSource(connConfig); NodeSchema ns = new NodeSchema("Node", "NodeProperty", true); // TOP this.nodeDAO = new NodeDAO(dataSource, ns, VOS_AUTHORITY, new X500IdentityManager(), DELETED_NODES); ContainerNode rootContainer = (ContainerNode) nodeDAO.getPath(HOME_CONTAINER); log.debug("ROOT: " + rootContainer); Assert.assertNotNull(rootContainer); String basePath = "/" + HOME_CONTAINER + "/"; // Create a container node String containerName = getNodeName("trickle-test4"); String containerPath = basePath + containerName; ContainerNode containerNode = this.getCommonContainerNode(containerPath); containerNode.setParent(rootContainer); containerNode = (ContainerNode) nodeDAO.put(containerNode, owner); // create a data node String dataPath = containerNode.getUri().getPath() + "/" + "dataNode" + System.currentTimeMillis(); DataNode dataNode = getCommonDataNode(dataPath); dataNode.setParent(containerNode); nodeDAO.put(dataNode, owner); JdbcTemplate jdbc = new JdbcTemplate(dataSource); // ensure the contentLength is zero to start String sql = "select contentLength from Node where name='" + dataNode.getName() + "'"; Long contentLength = jdbc.queryForLong(sql); Assert.assertEquals("Wrong content length", Long.valueOf(0L), contentLength); // ensure the data node delta is zero to start sql = "select delta from Node where name='" + dataNode.getName() + "'"; Long delta = jdbc.queryForLong(sql); Assert.assertEquals("Wrong delta", Long.valueOf(0L), delta); // manually set the busy state sql = "update Node set busyState='W' where name='" + dataNode.getName() + "'"; jdbc.update(sql); // update the metadata, using the strict option FileMetadata metadata = new FileMetadata(); metadata.setContentLength(10L); metadata.setMd5Sum("a94fc20c049422af7c591e2984f1f82d"); nodeDAO.updateNodeMetadata(dataNode, metadata, true); // ensure the state is back to normal sql = "select busyState from Node where name='" + dataNode.getName() + "'"; String curState = (String) jdbc.queryForObject(sql, String.class); Assert.assertEquals("Wrong busy state", "N", curState); // ensure the contentLength is correct sql = "select contentLength from Node where name='" + dataNode.getName() + "'"; contentLength = jdbc.queryForLong(sql); Assert.assertEquals("Wrong content length", Long.valueOf(10L), contentLength); // ensure the md5sum is correct sql = "select contentMD5 from Node where name='" + dataNode.getName() + "'"; String md5sum = (String) jdbc.queryForObject(sql, String.class); Assert.assertEquals("Wrong md5 sum", "a94fc20c049422af7c591e2984f1f82d".toUpperCase(), md5sum.toUpperCase()); // ensure the data node delta is correct sql = "select delta from Node where name='" + dataNode.getName() + "'"; delta = jdbc.queryForLong(sql); Assert.assertEquals("Wrong delta", Long.valueOf(10L), delta); // start the replace--manually set the busy state sql = "update Node set busyState='W' where name='" + dataNode.getName() + "'"; jdbc.update(sql); // update the metadata, using the strict option metadata = new FileMetadata(); metadata.setContentLength(15L); metadata.setMd5Sum("c2831384aae9c2e175c255797c2cfca5"); nodeDAO.updateNodeMetadata(dataNode, metadata, true); // ensure the state is back to normal sql = "select busyState from Node where name='" + dataNode.getName() + "'"; curState = (String) jdbc.queryForObject(sql, String.class); Assert.assertEquals("Wrong busy state", "N", curState); // ensure the contentLength is correct sql = "select contentLength from Node where name='" + dataNode.getName() + "'"; contentLength = jdbc.queryForLong(sql); Assert.assertEquals("Wrong content length", Long.valueOf(15L), contentLength); // ensure the md5sum is correct sql = "select contentMD5 from Node where name='" + dataNode.getName() + "'"; md5sum = (String) jdbc.queryForObject(sql, String.class); Assert.assertEquals("Wrong md5 sum", "c2831384aae9c2e175c255797c2cfca5".toUpperCase(), md5sum.toUpperCase()); // ensure the data node delta is correct sql = "select delta from Node where name='" + dataNode.getName() + "'"; delta = jdbc.queryForLong(sql); Assert.assertEquals("Wrong delta", Long.valueOf(15L), delta); // get the nodeID sql = "select nodeID from Node where name='" + dataNode.getName() + "'"; long nodeID = jdbc.queryForLong(sql); // get the propagation List<NodeSizePropagation> propagations = nodeDAO.getOutstandingPropagations(100, false); NodeSizePropagation propagation = null; for (NodeSizePropagation next : propagations) { if (next.getChildID() == nodeID) propagation = next; } Assert.assertNotNull("Null propagation", propagation); // apply the propagation nodeDAO.applyPropagation(propagation); // ensure the data node content length is correct sql = "select contentLength from Node where name='" + dataNode.getName() + "'"; contentLength = jdbc.queryForLong(sql); Assert.assertEquals("Wrong content length", Long.valueOf(15L), contentLength); // ensure the data node delta is correct sql = "select delta from Node where name='" + dataNode.getName() + "'"; delta = jdbc.queryForLong(sql); Assert.assertEquals("Wrong delta", Long.valueOf(0L), delta); // ensure the container node content length is correct sql = "select contentLength from Node where name='" + containerName + "'"; contentLength = jdbc.queryForLong(sql); Assert.assertEquals("Wrong container content length", Long.valueOf(0L), contentLength); // ensure the container node delta is correct sql = "select delta from Node where name='" + containerName + "'"; delta = jdbc.queryForLong(sql); Assert.assertEquals("Wrong container delta", Long.valueOf(15L), delta); } catch (Exception unexpected) { unexpected.printStackTrace(); log.error("unexpected exception", unexpected); Assert.fail("unexpected exception: " + unexpected); } finally { log.debug("testPropagationNewReplaceThenApply - DONE"); } }