List of usage examples for org.apache.commons.dbutils QueryRunner query
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException
From source file:com.gs.obevo.dbmetadata.impl.dialects.Db2MetadataDialect.java
@Override public ImmutableCollection<DaRoutine> searchExtraRoutines(final DaSchema schema, String procedureName, Connection conn) throws SQLException { QueryRunner query = new QueryRunner(); // using queryRunner so that we can reuse the connection String procedureClause = procedureName == null ? "" : " AND R.ROUTINENAME = '" + procedureName + "'"; final String sql = "SELECT ROUTINENAME, SPECIFICNAME, TEXT FROM SYSCAT.ROUTINES R WHERE R.ROUTINETYPE = 'F'\n" + "AND R.ROUTINESCHEMA = '" + schema.getName() + "'\n" + procedureClause; LOG.debug("Executing function metadata query SQL: {}", sql); ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn, sql, new MapListHandler())) .toImmutable();//from w ww . ja v a2 s .co m if (LOG.isDebugEnabled()) { LOG.debug("Results:"); for (Map<String, Object> map : maps) { LOG.debug("ROW: {}", map.toString()); } } return maps.collect(new Function<Map<String, Object>, DaRoutine>() { @Override public DaRoutine valueOf(Map<String, Object> map) { return new DaRoutinePojoImpl((String) map.get("ROUTINENAME"), schema, DaRoutineType.function, (String) map.get("SPECIFICNAME"), clobToString((Clob) map.get("TEXT"))); } }); }
From source file:com.odap.server.scheduler.JobManager.java
private synchronized void processJobs(List<Map<String, Object>> newJobList) { logger.info("Entering processJobs()"); future_offset = 1;/*from w w w. ja v a2 s.c o m*/ for (Map<String, Object> job : newJobList) { logger.info("Searching for job id:" + job.get("id")); Object jobExists = null; for (Map<String, Object> scheduledJob : scheduledMapList) { if (scheduledJob.get("id").toString().compareTo(job.get("id").toString()) == 0) { jobExists = scheduledJob; break; } } if (jobExists == null) { //Create new quartz job logger.info("Creating new job for " + job.get("id")); try { Class<? extends Job> jobClass = (Class<? extends Job>) Class .forName(job.get("pre_processor").toString()); JobDetail quartzJob = newJob(jobClass).withIdentity("job-" + job.get("id"), "jobman").build(); quartzJob.getJobDataMap().put("server_id", job.get("server_id")); quartzJob.getJobDataMap().put("job_id", job.get("id")); quartzJob.getJobDataMap().put("account_id", job.get("account_id")); quartzJob.getJobDataMap().put("server_name", job.get("server_name")); quartzJob.getJobDataMap().put("row", job); QueryRunner qRunner = new QueryRunner(); String sql = "select * from notifications n, joomla.cloud_users u, servers s, jobs j WHERE n.user_id = u.id and j.server_id = s.id AND job_id = " + job.get("id"); logger.info("Getting notifications attached to job:" + sql); quartzJob.getJobDataMap().put("notifications", (List<Map<String, Object>>) qRunner.query(conn, sql, new MapListHandler())); // If the cron entry is empty/null then we schedule it as soon as possible to run // otherwise we schedule the cron job //TODO prevent flood of jobs on restart and workload management if (job.get("cron") == null || job.get("cron").toString().compareTo("") == 0) { logger.debug("Scheduling one time execution for job " + job.get("id")); SimpleTrigger trigger = (SimpleTrigger) newTrigger() .withIdentity("trigger-" + job.get("id"), "onetime") .startAt(futureDate(future_offset += 25, IntervalUnit.SECOND)) // some Date .forJob("job-" + job.get("id"), "jobman") // identify job with name, group strings .build(); sched.scheduleJob(quartzJob, trigger); } else { sched.scheduleJob(quartzJob, newTrigger().withIdentity("trig-" + job.get("id"), "jobman") .withSchedule(cronSchedule(job.get("cron").toString())) .forJob("job-" + job.get("id"), "jobman").build()); } } catch (Exception e) { logger.error("Unable to create job " + job.get("id"), e); } } } // Now deal with the case where jobs need to be descheduled as this is a second run for (Map<String, Object> scheduledJob : scheduledMapList) { boolean job_found = false; for (Map<String, Object> newjob : newJobList) { if (newjob.get("id").toString().compareTo(scheduledJob.get("id").toString()) == 0) { job_found = true; break; } } if (job_found == false && scheduledJob.get("cron") != null && scheduledJob.get("cron").toString().length() > 0) { logger.info("Need to unschedule job " + scheduledJob.get("id")); try { sched.deleteJob(new JobKey("job-" + scheduledJob.get("id"), "jobman")); } catch (SchedulerException e) { logger.error("Unable to remove job", e); } } } scheduledMapList = newJobList; logger.info("Exiting processJobs()"); }
From source file:com.itdaoshi.discuz.dao.CdbUcMembersDAObject.java
@Override protected Long getNextPrimaryID() { QueryRunner run = new QueryRunner(); ResultSetHandler h = new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { if (!rs.next()) { return null; }/*from w ww.j av a 2 s .co m*/ ResultSetMetaData meta = rs.getMetaData(); int cols = meta.getColumnCount(); Object[] result = new Object[cols]; for (int i = 0; i < cols; i++) { result[i] = rs.getObject(i + 1); } return result; } }; try { Object[] result = (Object[]) run.query(conn, "SELECT MAX(uid) FROM CDB_UC_MEMBERS ", h); return (Long) result[0] + 1; // do something with the result } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:edu.pitt.dbmi.ipm.service.storage.PostgreStorage.java
@Override public JSONObject getJSONResult(String SPARQL_URL, String query) throws QueryException { JSONObject jsonObj = null;/* ww w.java 2 s . co m*/ List<Map<String, Object>> listOfMaps = null; Connection connection = null; try { connection = getConnection(); QueryRunner queryRunner = new QueryRunner(); listOfMaps = queryRunner.query(connection, query, new MapListHandler()); // convert list of maps to listof maps of maps List<Map<String, Map<String, Object>>> newList = new LinkedList<Map<String, Map<String, Object>>>(); Map<String, Map<String, Object>> outerMap = null; Map<String, Object> innerMap = null; List<String> headerList = new LinkedList<String>(); Object value = ""; String key = null; for (Map<String, Object> oldMap : listOfMaps) { outerMap = new HashMap<String, Map<String, Object>>(); for (Map.Entry<String, Object> entry : oldMap.entrySet()) { key = entry.getKey(); innerMap = new HashMap<String, Object>(); value = entry.getValue(); if (value != null) { innerMap.put("value", value); outerMap.put(key, innerMap); } if (!headerList.contains(key)) headerList.add(key); } newList.add(outerMap); } String newJson = "{\"results\":{\"bindings\":" + new Gson().toJson(newList) + "},\"head\":{\"vars\":" + new Gson().toJson(headerList) + "}}"; listOfMaps.clear(); listOfMaps = null; newList.clear(); newList = null; jsonObj = new JSONObject(newJson); } catch (SQLException se) { throw new QueryException( "PostgreStorage getJSONResult: Couldn't query the database." + se.getMessage()); } catch (JSONException e) { throw new QueryException("PostgreStorage getJSONResult: " + e.getMessage()); } catch (Exception e) { throw new QueryException("PostgreStorage getJSONResult: " + e.getMessage()); } finally { DbUtils.closeQuietly(connection); } return jsonObj; }
From source file:com.itdaoshi.dokeos.dao.UserDAObject.java
@Override protected Long getNextPrimaryID() { QueryRunner run = new QueryRunner(); ResultSetHandler h = new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { if (!rs.next()) { return null; }/*w ww. j a v a 2 s .c o m*/ ResultSetMetaData meta = rs.getMetaData(); int cols = meta.getColumnCount(); Object[] result = new Object[cols]; for (int i = 0; i < cols; i++) { result[i] = rs.getObject(i + 1); } return result; } }; try { Object[] result = (Object[]) run.query(conn, "SELECT MAX(user_id) FROM USER ", h); return (Long) result[0] + 1; // do something with the result } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:javasnack.flywaydb.FlywaydbDemo1Test.java
@Test public void typicalMigrationFlow() throws Exception { Properties properties = new Properties(); properties.setProperty("flyway.user", "sa"); properties.setProperty("flyway.password", ""); properties.setProperty("flyway.url", "jdbc:h2:mem:flywaydb_demo1;DB_CLOSE_DELAY=-1"); properties.setProperty("flyway.driver", "org.h2.Driver"); final Flyway flyway = new Flyway(); flyway.configure(properties);/*from ww w .ja va 2 s. c o m*/ assertNotNull(flyway.getDataSource()); flyway.setLocations("flywaydbdemos/demo1"); // not initialized -> no target schemas. assertEquals(flyway.getSchemas().length, 0); flyway.init(); assertEquals(flyway.getSchemas().length, 1); assertEquals(flyway.getSchemas()[0], "PUBLIC"); assertEquals(flyway.getTable(), "schema_version"); MigrationInfoService mis = flyway.info(); assertEquals(mis.all().length, 4); for (MigrationInfo mi : mis.all()) { System.out.println(mi.getVersion()); System.out.println(mi.getDescription()); System.out.println(mi.getState()); System.out.println(mi.getType()); } // 3 migrations (V1.1, V1.2, V1.3) must be pending status. assertEquals(mis.pending().length, 3); // initialized version (V1) applied. assertEquals(mis.applied().length, 1); // current version is initialized version (V1). MigrationInfo mi = mis.current(); assertEquals(mi.getVersion().getVersion(), "1"); assertEquals(mi.getDescription(), flyway.getInitDescription()); assertEquals(mi.getState(), MigrationState.SUCCESS); // migrate to V1.2 flyway.setTarget(MigrationVersion.fromVersion("1.2")); flyway.migrate(); mis = flyway.info(); assertEquals(mis.all().length, 4); // no pending, V1.3 -> "ABOVE_TARGET". assertEquals(mis.pending().length, 0); // V1, V1.1, V1.2 were applied. assertEquals(mis.applied().length, 3); for (MigrationInfo _mi : mis.all()) { System.out.println(_mi.getVersion()); System.out.println(_mi.getDescription()); System.out.println(_mi.getState()); System.out.println(_mi.getType()); } mi = mis.current(); assertEquals(mi.getVersion().getVersion(), "1.2"); assertEquals(mi.getDescription(), "add t1 hobby column"); assertEquals(mi.getState(), MigrationState.SUCCESS); // migrate to latest version flyway.setTarget(MigrationVersion.LATEST); flyway.migrate(); mis = flyway.info(); assertEquals(mis.all().length, 4); assertEquals(mis.pending().length, 0); assertEquals(mis.applied().length, 4); mi = mis.current(); assertEquals(mi.getVersion().getVersion(), "1.3"); assertEquals(mi.getDescription(), "insert t1 data2"); assertEquals(mi.getState(), MigrationState.SUCCESS); // select and validate records. QueryRunner run = new QueryRunner(); ResultSetHandler<Map<Long, T1>> h = new AbstractKeyedHandler<Long, T1>() { @Override protected T1 createRow(ResultSet rs) throws SQLException { T1 row = new T1(); row.id = rs.getLong("id"); row.name = rs.getString("name"); row.age = rs.getInt("age"); row.hobby = rs.getString("hobby"); return row; } @Override protected Long createKey(ResultSet rs) throws SQLException { return rs.getLong("id"); } }; Map<Long, T1> found = run.query(conn, "select id, name, age, hobby from t1", h); assertEquals(found.size(), 4); T1 jon = found.get(1L); assertEquals(jon.name, "jon"); assertEquals(jon.hobby, ""); T1 alice = found.get(3L); assertEquals(alice.name, "alice"); assertEquals(alice.hobby, "swimming"); }
From source file:javasnack.flywaydb.FlywaydbDemo3Test.java
@Test public void usingPlaceholderAndEncoding() throws Exception { Properties properties = new Properties(); properties.setProperty("flyway.user", "sa"); properties.setProperty("flyway.password", ""); properties.setProperty("flyway.url", "jdbc:h2:mem:flywaydb_demo3;DB_CLOSE_DELAY=-1"); properties.setProperty("flyway.driver", "org.h2.Driver"); final Flyway flyway = new Flyway(); flyway.configure(properties);//from w w w .j a va2 s . c o m flyway.setLocations("flywaydbdemos/demo3"); flyway.setEncoding("UTF-8"); Map<String, String> placeholders = new HashMap<>(); placeholders.put("L1", "abc"); placeholders.put("L2", "def"); placeholders.put("L3", "abc"); placeholders.put("L4", "def"); flyway.setPlaceholders(placeholders); MigrationInfoService mis = flyway.info(); assertEquals(mis.all().length, 3); for (MigrationInfo mi : mis.all()) { System.out.println(mi.getVersion()); System.out.println(mi.getDescription()); System.out.println(mi.getState()); System.out.println(mi.getType()); } // 3 migrations (V1, V1.1, V1.2) must be pending status. assertEquals(mis.pending().length, 3); // no version applied. assertEquals(mis.applied().length, 0); // no current version. MigrationInfo mi = mis.current(); assertNull(mi); // migrate to V1.1 flyway.setTarget(MigrationVersion.fromVersion("1.1")); flyway.migrate(); mis = flyway.info(); assertEquals(mis.all().length, 3); // no pending, V1.2 -> "ABOVE_TARGET". assertEquals(mis.pending().length, 0); // V1, V1.1, V1.2 were applied. assertEquals(mis.applied().length, 2); for (MigrationInfo _mi : mis.all()) { System.out.println(_mi.getVersion()); System.out.println(_mi.getDescription()); System.out.println(_mi.getState()); System.out.println(_mi.getType()); } mi = mis.current(); assertEquals(mi.getVersion().getVersion(), "1.1"); assertEquals(mi.getDescription(), "add t1 hobby column"); assertEquals(mi.getState(), MigrationState.SUCCESS); // change placeholder prefix and suffix. flyway.setPlaceholderPrefix("%{%"); flyway.setPlaceholderSuffix("%}%"); // migrate to latest version flyway.setTarget(MigrationVersion.LATEST); flyway.migrate(); mis = flyway.info(); assertEquals(mis.all().length, 3); assertEquals(mis.pending().length, 0); assertEquals(mis.applied().length, 3); mi = mis.current(); assertEquals(mi.getVersion().getVersion(), "1.2"); assertEquals(mi.getDescription(), "insert t1 data2"); assertEquals(mi.getState(), MigrationState.SUCCESS); // select and validate records. QueryRunner run = new QueryRunner(); ResultSetHandler<Map<Long, T1>> h = new AbstractKeyedHandler<Long, T1>() { @Override protected T1 createRow(ResultSet rs) throws SQLException { T1 row = new T1(); row.id = rs.getLong("id"); row.name = rs.getString("name"); row.age = rs.getInt("age"); row.hobby = rs.getString("hobby"); return row; } @Override protected Long createKey(ResultSet rs) throws SQLException { return rs.getLong("id"); } }; Map<Long, T1> found = run.query(conn, "select id, name, age, hobby from abc_def_t1", h); assertEquals(found.size(), 4); T1 jon = found.get(1L); assertEquals(jon.name, ""); assertEquals(jon.hobby, ""); T1 alice = found.get(3L); assertEquals(alice.name, "alice"); assertEquals(alice.hobby, "swimming"); }
From source file:com.iana.boesc.dao.BOESCDaoImpl.java
@Override public GIERInfoDetails getDVIRAdditionaldetails(String eqPrefix, String eqNumber) throws Exception { QueryRunner run = new QueryRunner(getGierDataSource()); logger.info("In getDVIRAdditionaldetails(" + eqPrefix + " , " + eqNumber + " )..."); GIERInfoDetails gierInfo = new GIERInfoDetails(); boolean errorFlag = false; String sql = "SELECT CurrentIEPCompanyId,ch_pool_id FROM Equipment WHERE EqPrefix = ? AND EqNumber = ?"; Map<String, Object> eqDetailsMap = run.query(sql, new MapHandler(), new Object[] { eqPrefix, eqNumber }); if (eqDetailsMap != null && eqDetailsMap.size() > 0) { if (!ValidationUtils.validateStringObject(eqDetailsMap.get("CurrentIEPCompanyId")).equals("") && !ValidationUtils.validateStringObject(eqDetailsMap.get("ch_pool_id")).equals("")) { String companyId = eqDetailsMap.get("CurrentIEPCompanyId").toString(); String chassisPoolId = eqDetailsMap.get("ch_pool_id").toString(); sql = "SELECT CompanySCACcd, USDOTNumber FROM Company WHERE CompanyId = ?"; Map<String, Object> companyDetailsMap = run.query(sql, new MapHandler(), new Object[] { companyId }); if (companyDetailsMap != null && companyDetailsMap.size() > 0) { if (!ValidationUtils.validateStringObject(companyDetailsMap.get("CompanySCACcd")).equals("") && !ValidationUtils.validateStringObject(companyDetailsMap.get("USDOTNumber")) .equals("")) { String scacCode = companyDetailsMap.get("CompanySCACcd").toString(); String usdotNumber = companyDetailsMap.get("USDOTNumber").toString(); sql = "SELECT CH_POOL_ID, CH_POOL_NAME FROM chassispool WHERE POOL_ID = ?"; Map<String, Object> poolDetailsMap = run.query(sql, new MapHandler(), new Object[] { chassisPoolId }); if (poolDetailsMap != null && poolDetailsMap.size() > 0) { if (!ValidationUtils.validateStringObject(poolDetailsMap.get("CH_POOL_ID")).equals("") && !ValidationUtils.validateStringObject(poolDetailsMap.get("CH_POOL_NAME")) .equals("")) { String ch_pool_id = poolDetailsMap.get("CH_POOL_ID").toString(); String ch_pool_name = poolDetailsMap.get("CH_POOL_NAME").toString(); gierInfo = setGIERDetails(ch_pool_id, ch_pool_name, scacCode, usdotNumber); } else { gierInfo = setGIERDetails("", "", scacCode, usdotNumber); }//from www. j a va 2 s . co m } else { gierInfo = setGIERDetails("", "", scacCode, usdotNumber); } } else { errorFlag = true; } } else { errorFlag = true; } } else { errorFlag = true; } } else { errorFlag = true; } if (errorFlag) { gierInfo = setGIERDetails("", "", "", ""); } return gierInfo; }
From source file:com.iana.boesc.dao.BOESCDaoImpl.java
@Override public Map<String, Map<String, Object>> getAllRegisteredUserDetailsMapByTypeId(int userTypeId) { Map<String, Map<String, Object>> map = new HashMap<String, Map<String, Object>>(); QueryRunner run = new QueryRunner(getDataSource()); try {/* w w w.ja v a2s. c om*/ String query = "SELECT C.BOESC_USER_ID,U.SCAC,C.SEG_DELIM, C.ELE_DELIM,C.TRAN_TYPE FROM BOESC_CONFIG C, BOESC_USERS U WHERE " + " C.BOESC_USER_ID = U.BOESC_USER_ID AND C.TRAN_TYPE IN ('" + GlobalVariables.BOESC + "','" + GlobalVariables.DVIR + "') AND U.BOESC_USER_TYPE_ID = ? "; List<Map<String, Object>> listUsersDetails = run.query(query, new MapListHandler(), userTypeId); //logger.info("List size ::"+listUsersDetails.size()); //logger.info("List ::"+listUsersDetails); if (listUsersDetails == null || listUsersDetails.size() == 0) return map; else map = populateUserDetailsFromUserType(userTypeId, listUsersDetails); } catch (SQLException e) { e.printStackTrace(); logger.error("SQLException occured :", e); logger.error("We are unable to load user information from DB due to SQL Exception."); } catch (Exception e) { e.printStackTrace(); logger.error("Exception occured :", e); logger.error("We are unable to load user information from DB due to Exception."); } return map; }
From source file:com.iana.boesc.dao.BOESCDaoImpl.java
@Override public UIIAInfoDetails getUIIAdetailsforDVIR(DVIR_EDI322Bean eb) throws Exception { System.out.println("Inside getUIIAdetailsforDVIR"); QueryRunner run = new QueryRunner(getUiiaDataSource()); UIIAInfoDetails uiiaInfo = new UIIAInfoDetails(); StringBuffer sbQry = new StringBuffer( "SELECT CONCAT(IFNULL(d.DRV_FNAME,''),' ',IFNULL(d.DRV_LNAME,'')) AS DRV_NAME, IFNULL(d.MC_EIN,'') MC_EIN, IFNULL(d.idd_pin,'') idd_pin, IFNULL(d.MC_DOT,'') MC_DOT,IFNULL(d.SCAC_CODE,'') SCAC_CODE FROM dvir_driver d "); sbQry.append(// w w w.j av a 2 s. c o m " WHERE d.SCAC_CODE =? AND d.DRV_LICENSE_NO = ? AND d.STATE_ABBR = ? AND d.DRV_TERMINATED_DT IS NULL"); Map<String, Object> mcDetailsMap = run.query(sbQry.toString(), new MapHandler(), new Object[] { eb.getMcScac(), eb.getDrvLicNo(), eb.getDrvState() }); if (mcDetailsMap != null && mcDetailsMap.size() > 0) { if (!ValidationUtils.validateStringObject(mcDetailsMap.get("idd_pin")).equals("")) { String drvName = mcDetailsMap.get("DRV_NAME").toString(); String mcEin = mcDetailsMap.get("MC_EIN").toString(); String mcDot = mcDetailsMap.get("MC_DOT").toString(); String iddPin = mcDetailsMap.get("idd_pin").toString(); uiiaInfo = setUIIADetails(drvName, mcEin, mcDot, iddPin); } else { uiiaInfo = setUIIADetails("", "", "", ""); } } return uiiaInfo; }