List of usage examples for org.springframework.jdbc.core JdbcTemplate queryForList
@Override
public List<Map<String, Object>> queryForList(String sql) throws DataAccessException
From source file:io.pivotal.spring.xd.jdbcgpfdist.LoadIT.java
@Test public void testUpdateMultiColumns() { context.register(Config3.class, CommonConfig.class); context.refresh();//from ww w . ja v a 2 s . co m JdbcTemplate template = context.getBean(JdbcTemplate.class); String drop = "DROP TABLE IF EXISTS AbstractLoadTests;"; String create = "CREATE TABLE AbstractLoadTests (col1 text, col2 text, col3 text);"; template.execute(drop); template.execute(create); List<String> data = new ArrayList<String>(); for (int i = 0; i < 10; i++) { template.execute("insert into AbstractLoadTests values('DATA" + i + "', 'DATA', 'DATA');"); data.add("DATA" + i + "\tDATA" + i + "\tDATA" + i + "\n"); } broadcastData(data); GreenplumLoad greenplumLoad = context.getBean(GreenplumLoad.class); greenplumLoad.load(); List<Map<String, Object>> queryForList = template.queryForList("SELECT * from AbstractLoadTests;"); assertThat(queryForList, notNullValue()); assertThat(queryForList.size(), is(10)); for (int i = 0; i < 10; i++) { assertThat(queryForList.get(i).get("col2"), is(queryForList.get(i).get("col1"))); assertThat(queryForList.get(i).get("col3"), is(queryForList.get(i).get("col1"))); } }
From source file:au.aurin.org.svc.GeodataFinder.java
public List<Map<String, Object>> getOrgs() { final String query = "select org_id,orgname from organisations"; try {/* w w w. ja v a 2 s. com*/ final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); final List<Map<String, Object>> outMap = jdbcTemplate.queryForList(query); return outMap; } catch (final Exception e) { LOGGER.info("error in getOrgs is : {}", e.toString()); } return null; }
From source file:au.aurin.org.svc.GeodataFinder.java
public List<Map<String, Object>> getApps() { final String query = "select app_id,appname from application"; try {//from w w w . j a v a 2s . c o m final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); final List<Map<String, Object>> outMap = jdbcTemplate.queryForList(query); return outMap; } catch (final Exception e) { LOGGER.info("error in getApps is : {}", e.toString()); } return null; }
From source file:au.aurin.org.svc.GeodataFinder.java
public List<Map<String, Object>> getRoles() { final String query = "select role_id,rolename from roles"; try {/*from w w w.j a va2s . c om*/ final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); final List<Map<String, Object>> outMap = jdbcTemplate.queryForList(query); return outMap; } catch (final Exception e) { LOGGER.info("error in getRoles is : {}", e.toString()); } return null; }
From source file:au.aurin.org.svc.GeodataFinder.java
public String getUserID(final String uuid) { final String query = "select * from users where uuid='" + uuid + "'"; // uuid String out = ""; final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); LOGGER.info("getUserID: query is {} ", query); final List<Map<String, Object>> rows = jdbcTemplate.queryForList(query); for (final Map row : rows) { out = (String) row.get("uuid"); }/*from ww w . j a v a 2s. com*/ return out; }
From source file:au.aurin.org.svc.GeodataFinder.java
public String getPassID(final String uuid) { final String query = "select * from users where uuid='" + uuid + "'"; // uuid String out = ""; final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); LOGGER.info("getUserID: query is {} ", query); final List<Map<String, Object>> rows = jdbcTemplate.queryForList(query); for (final Map row : rows) { out = (String) row.get("password"); }/* ww w. jav a2 s. co m*/ return out; }
From source file:au.aurin.org.svc.GeodataFinder.java
public String getUuidFromEmail(final String email) { final String query = "select * from users where email='" + email + "'"; String out = ""; final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); LOGGER.info("getUuidFromEmail: query is {} ", query); final List<Map<String, Object>> rows = jdbcTemplate.queryForList(query); for (final Map row : rows) { out = (String) row.get("uuid"); }//from w ww . j ava 2s. com return out; }
From source file:au.aurin.org.svc.GeodataFinder.java
public dummyuserData getOneUser(final String user_id) { String query = "select user_id, email, firstname, lastname from users where user_id=?"; try {/* w w w.j av a 2s .co m*/ final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); LOGGER.info("getUser: query is {} ", query); query = "select a.user_id,a.email,a.firstname, a.lastname from users as a where a.user_id=" + user_id; // final dummyuserData user = (dummyuserData) jdbcTemplate.queryForObject( // query, new String[] { user_id }, new BeanPropertyRowMapper( // dummyuserData.class)); query = "select user_id, email, firstname, lastname from users where user_id=" + user_id; final List<Map<String, Object>> rows = jdbcTemplate.queryForList(query); final dummyuserData user = new dummyuserData(); for (final Map row : rows) { user.setUser_id((Integer) row.get("user_id")); user.setEmail((String) row.get("email")); user.setFirstname((String) row.get("firstname")); user.setLastname((String) row.get("lastname")); } LOGGER.info(user.getEmail()); return user; } catch (final Exception e) { LOGGER.info("error in getOneUser is : {}", e.toString()); } return null; }
From source file:de.uhh.l2g.dao.VideoDao.java
/** * Gets the number open access lectureseries. * * @param facilityId the facility id//from www.ja v a 2s. co m * @param tree the tree * @return the number open access lectureseries */ public Integer getNumberOpenAccessLectureseries(Integer facilityId, String tree) { //use jdbc template JdbcTemplate jdbst = new JdbcTemplate(this.getDataSource()); //prepare query String sqlquery = ""; //prepare facilities list //get all sub-facilitye from input-id List<Facility> einList = null; String partEinQuery = ""; //if facilityId choosen if (facilityId != 0) { einList = facilityDao.getByParentId(facilityId); //add parentId-facility himself to list Facility ein = facilityDao.getById(facilityId).iterator().next(); einList.add(ein); //now get all videos for each facility from list //prepare string for sql-query Iterator<Facility> iE = einList.iterator(); partEinQuery = "("; while (iE.hasNext()) { Facility e = iE.next(); partEinQuery += "video_facility.facilityId=" + e.getId() + " || "; } // and update part query partEinQuery += "video_facility.facilityId=" + facilityId + ")"; sqlquery = "SELECT COUNT(*) FROM video, video_facility, facility WHERE ( video.openAccess=1 AND video.id=video_facility.videoId AND " + partEinQuery + " AND video_facility.facilityId=facility.id AND facility.typ='" + tree + "' ) GROUP BY lectureseriesId ORDER BY video.generationDate DESC "; } else { sqlquery = "SELECT COUNT(*) FROM video, video_facility, facility WHERE ( video.openAccess=1 AND video.id=video_facility.videoId AND video_facility.facilityId=facility.id AND facility.typ='" + tree + "') GROUP BY lectureseriesId ORDER BY video.generationDate DESC "; } //execute return jdbst.queryForList(sqlquery).size(); }
From source file:dao.AdvSearchDAO.java
public static ObjectNode searchFlows(JsonNode searchOpt, int page, int size) { ObjectNode resultNode = Json.newObject(); int count = 0; List<String> appcodeInList = new ArrayList<String>(); List<String> appcodeNotInList = new ArrayList<String>(); List<String> flowInList = new ArrayList<String>(); List<String> flowNotInList = new ArrayList<String>(); List<String> jobInList = new ArrayList<String>(); List<String> jobNotInList = new ArrayList<String>(); if (searchOpt != null && (searchOpt.isContainerNode())) { if (searchOpt.has("appcode")) { JsonNode appcodeNode = searchOpt.get("appcode"); if (appcodeNode != null && appcodeNode.isContainerNode()) { if (appcodeNode.has("in")) { JsonNode appcodeInNode = appcodeNode.get("in"); if (appcodeInNode != null) { String appcodeInStr = appcodeInNode.asText(); if (StringUtils.isNotBlank(appcodeInStr)) { String[] appcodeInArray = appcodeInStr.split(","); if (appcodeInArray != null) { for (String value : appcodeInArray) { if (StringUtils.isNotBlank(value)) { appcodeInList.add(value.trim()); }/*from ww w . jav a 2 s . co m*/ } } } } } if (appcodeNode.has("not")) { JsonNode appcodeNotInNode = appcodeNode.get("not"); if (appcodeNotInNode != null) { String appcodeNotInStr = appcodeNotInNode.asText(); if (StringUtils.isNotBlank(appcodeNotInStr)) { String[] appcodeNotInArray = appcodeNotInStr.split(","); if (appcodeNotInArray != null) { for (String value : appcodeNotInArray) { if (StringUtils.isNotBlank(value)) { appcodeNotInList.add(value.trim()); } } } } } } } } if (searchOpt.has("flow")) { JsonNode flowNode = searchOpt.get("flow"); if (flowNode != null && flowNode.isContainerNode()) { if (flowNode.has("in")) { JsonNode flowInNode = flowNode.get("in"); if (flowInNode != null) { String flowInStr = flowInNode.asText(); if (StringUtils.isNotBlank(flowInStr)) { String[] flowInArray = flowInStr.split(","); if (flowInArray != null) { for (String value : flowInArray) { if (StringUtils.isNotBlank(value)) { flowInList.add(value.trim()); } } } } } } if (flowNode.has("not")) { JsonNode flowNotInNode = flowNode.get("not"); if (flowNotInNode != null) { String flowNotInStr = flowNotInNode.asText(); if (StringUtils.isNotBlank(flowNotInStr)) { String[] flowNotInArray = flowNotInStr.split(","); if (flowNotInArray != null) { for (String value : flowNotInArray) { if (StringUtils.isNotBlank(value)) { flowNotInList.add(value.trim()); } } } } } } } } if (searchOpt.has("job")) { JsonNode jobNode = searchOpt.get("job"); if (jobNode != null && jobNode.isContainerNode()) { if (jobNode.has("in")) { JsonNode jobInNode = jobNode.get("in"); if (jobInNode != null) { String jobInStr = jobInNode.asText(); if (StringUtils.isNotBlank(jobInStr)) { String[] jobInArray = jobInStr.split(","); if (jobInArray != null) { for (String value : jobInArray) { if (StringUtils.isNotBlank(value)) { jobInList.add(value.trim()); } } } } } } if (jobNode.has("not")) { JsonNode jobNotInNode = jobNode.get("not"); if (jobNotInNode != null) { String jobNotInStr = jobNotInNode.asText(); if (StringUtils.isNotBlank(jobNotInStr)) { String[] jobNotInArray = jobNotInStr.split(","); if (jobNotInArray != null) { for (String value : jobNotInArray) { if (StringUtils.isNotBlank(value)) { jobNotInList.add(value.trim()); } } } } } } } } boolean needAndKeyword = false; 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; String query = null; if (jobInList.size() > 0 || jobNotInList.size() > 0) { query = ADV_SEARCH_JOB; } else { query = ADV_SEARCH_FLOW; } if (appcodeInList.size() > 0 || appcodeNotInList.size() > 0) { boolean appcodeNeedAndKeyword = false; if (appcodeInList.size() > 0) { int indexForAppcodeInList = 0; for (String appcode : appcodeInList) { if (indexForAppcodeInList == 0) { query += "WHERE a.app_code in ('" + appcode + "'"; } else { query += ", '" + appcode + "'"; } indexForAppcodeInList++; } query += ") "; appcodeNeedAndKeyword = true; } if (appcodeNotInList.size() > 0) { if (appcodeNeedAndKeyword) { query += " AND "; } else { query += " WHERE "; } int indexForAppcodeNotInList = 0; for (String appcode : appcodeNotInList) { if (indexForAppcodeNotInList == 0) { query += "a.app_code not in ('" + appcode + "'"; } else { query += ", '" + appcode + "'"; } indexForAppcodeNotInList++; } query += ") "; } needAndKeyword = true; } if (flowInList.size() > 0 || flowNotInList.size() > 0) { if (needAndKeyword) { query += " AND "; } else { query += " WHERE "; } boolean flowNeedAndKeyword = false; if (flowInList.size() > 0) { query += "( "; int indexForFlowInList = 0; for (String flow : flowInList) { if (indexForFlowInList == 0) { query += "f.flow_name LIKE '%" + flow + "%'"; } else { query += " or f.flow_name LIKE '%" + flow + "%'"; } indexForFlowInList++; } query += ") "; flowNeedAndKeyword = true; } if (flowNotInList.size() > 0) { if (flowNeedAndKeyword) { query += " AND "; } query += "( "; int indexForFlowNotInList = 0; for (String flow : flowNotInList) { if (indexForFlowNotInList == 0) { query += "f.flow_name NOT LIKE '%" + flow + "%'"; } else { query += " and f.flow_name NOT LIKE '%" + flow + "%'"; } indexForFlowNotInList++; } query += ") "; } needAndKeyword = true; } if (jobInList.size() > 0 || jobNotInList.size() > 0) { if (needAndKeyword) { query += " AND "; } else { query += " WHERE "; } query += "( "; boolean jobNeedAndKeyword = false; if (jobInList.size() > 0) { query += "( "; int indexForJobInList = 0; for (String job : jobInList) { if (indexForJobInList == 0) { query += "j.job_name LIKE '%" + job + "%'"; } else { query += " or j.job_name LIKE '%" + job + "%'"; } indexForJobInList++; } query += ") "; jobNeedAndKeyword = true; } if (jobNotInList.size() > 0) { if (jobNeedAndKeyword) { query += " AND "; } query += "( "; int indexForJobNotInList = 0; for (String job : jobNotInList) { if (indexForJobNotInList == 0) { query += "j.job_name NOT LIKE '%" + job + "%'"; } else { query += " and j.job_name NOT LIKE '%" + job + "%'"; } indexForJobNotInList++; } query += ") "; } query += " ) "; } 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) { FlowJob flow = new FlowJob(); flow.appCode = (String) row.get("app_code"); flow.flowName = (String) row.get("flow_name"); flow.flowPath = (String) row.get("flow_path"); flow.flowGroup = (String) row.get("flow_group"); flow.jobName = (String) row.get("job_name"); flow.jobPath = (String) row.get("job_path"); flow.flowId = (Long) row.get("flow_id"); if (StringUtils.isNotBlank(flow.jobName)) { flow.displayName = flow.jobName; } else { 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("page", page); resultNode.put("isFlowJob", true); resultNode.put("itemsPerPage", size); resultNode.put("totalPages", (int) Math.ceil(count / ((double) size))); resultNode.set("data", Json.toJson(pagedFlows)); 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; }