List of usage examples for org.apache.commons.dbutils QueryRunner QueryRunner
public QueryRunner()
From source file:com.aw.core.dao.DAOSql.java
/** * Helper method used to retrieve a list of objects. The object element of the list * is determined by the resultSetHandler * * @param sql SQL query/* w w w .j a va 2 s.c om*/ * @param filterKeys key used to restrict the search * @param resultSetHandler maps a resultset row to a object (array, bean, etc) * @return List of objects */ public Object executeQuery(String sql, Object[] filterKeys, ResultSetHandler resultSetHandler) { QueryRunner queryRunner = new QueryRunner(); try { if (logger.isDebugEnabled()) logger.debug("Executing:" + AWQueryRunner.buildSQLLog(sql, filterKeys)); Connection con = getHibernateConnection(); return queryRunner.query(con, sql, filterKeys, resultSetHandler); } catch (Exception e) { //logger.error("Error executing query", e); throw AWBusinessException.wrapUnhandledException(logger, e); } }
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;//w ww . j a va2s . co 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.netflix.metacat.usermetadata.mysql.MySqlLookupService.java
/** * Returns the list of values of the lookup name. * @param lookupId lookup id//from w w w . j a v a 2s . co m * @return list of lookup values */ @Override public Set<String> getValues(final Long lookupId) { final Connection connection = DBUtil.getReadConnection(getDataSource()); try { return new QueryRunner().query(connection, SQL_GET_LOOKUP_VALUES, rs -> { final Set<String> result = Sets.newHashSet(); while (rs.next()) { result.add(rs.getString("value")); } return result; }, lookupId); } catch (Exception e) { final String message = String.format("Failed to get the lookup values for id %s", lookupId); log.error(message, e); throw new UserMetadataServiceException(message, e); } finally { DBUtil.closeReadConnection(connection); } }
From source file:com.ouc.cpss.dao.BaseDao.java
/** * ??// w w w. j ava 2 s. c o m * * @param conn * @param sql sql? * @param params ? * @return * @throws java.sql.SQLException */ public boolean update(Connection conn, String sql, Object[] params) throws SQLException { boolean flag = false; QueryRunner qRunner = new QueryRunner(); int i = qRunner.update(conn, sql, params); if (i > 0) { flag = true; } return flag; }
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 www . j a va 2 s.co 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:net.gcolin.simplerepo.search.SearchController.java
public SearchController(ConfigurationManager configManager) throws IOException { this.configManager = configManager; File plugins = new File(configManager.getRoot(), "plugins"); plugins.mkdirs();/*ww w . j av a2 s. c om*/ System.setProperty("derby.system.home", plugins.getAbsolutePath()); BasicDataSource s = new BasicDataSource(); s.setDriverClassName("org.apache.derby.jdbc.EmbeddedDriver"); s.setUrl("jdbc:derby:search" + (new File(plugins, "search").exists() ? "" : ";create=true")); s.setUsername("su"); s.setPassword(""); s.setMaxTotal(10); s.setMinIdle(0); s.setDefaultAutoCommit(true); datasource = s; Set<String> allTables = new HashSet<>(); Connection connection = null; try { try { connection = datasource.getConnection(); connection.setAutoCommit(false); DatabaseMetaData dbmeta = connection.getMetaData(); try (ResultSet rs = dbmeta.getTables(null, null, null, new String[] { "TABLE" })) { while (rs.next()) { allTables.add(rs.getString("TABLE_NAME").toLowerCase()); } } if (!allTables.contains("artifact")) { QueryRunner run = new QueryRunner(); run.update(connection, "CREATE TABLE artifactindex(artifact bigint NOT NULL, version bigint NOT NULL)"); run.update(connection, "INSERT INTO artifactindex (artifact,version) VALUES (?,?)", 1L, 1L); run.update(connection, "CREATE TABLE artifact(id bigint NOT NULL,groupId character varying(120), artifactId character varying(120),CONSTRAINT artifact_pkey PRIMARY KEY (id))"); run.update(connection, "CREATE TABLE artifactversion(artifact_id bigint NOT NULL,id bigint NOT NULL," + "version character varying(100)," + "reponame character varying(30)," + "CONSTRAINT artifactversion_pkey PRIMARY KEY (id)," + "CONSTRAINT fk_artifactversion_artifact_id FOREIGN KEY (artifact_id) REFERENCES artifact (id) )"); run.update(connection, "CREATE TABLE artifacttype(version_id bigint NOT NULL,packaging character varying(20) NOT NULL,classifier character varying(30)," + "CONSTRAINT artifacttype_pkey PRIMARY KEY (version_id,packaging,classifier)," + "CONSTRAINT fk_artifacttype_version FOREIGN KEY (version_id) REFERENCES artifactversion (id))"); run.update(connection, "CREATE INDEX artifactindex ON artifact(groupId,artifactId)"); run.update(connection, "CREATE INDEX artifactgroupindex ON artifact(groupId)"); run.update(connection, "CREATE INDEX artifactversionindex ON artifactversion(version)"); } connection.commit(); } catch (SQLException ex) { connection.rollback(); throw ex; } finally { DbUtils.close(connection); } } catch (SQLException ex) { throw new IOException(ex); } }
From source file:azkaban.project.JdbcProjectLoader.java
private Project fetchProjectById(Connection connection, int id) throws ProjectManagerException { QueryRunner runner = new QueryRunner(); // Fetch the project Project project = null;/*from ww w. j a v a2 s .co m*/ ProjectResultHandler handler = new ProjectResultHandler(); try { List<Project> projects = runner.query(connection, ProjectResultHandler.SELECT_PROJECT_BY_ID, handler, id); if (projects.isEmpty()) { throw new ProjectManagerException("No project with id " + id + " exists in db."); } project = projects.get(0); } catch (SQLException e) { logger.error(ProjectResultHandler.SELECT_PROJECT_BY_ID + " failed."); throw new ProjectManagerException("Query for existing project failed. Project " + id, e); } // Fetch the user permissions List<Triple<String, Boolean, Permission>> permissions = fetchPermissionsForProject(connection, project); for (Triple<String, Boolean, Permission> perm : permissions) { if (perm.getThird().toFlags() != 0) { if (perm.getSecond()) { project.setGroupPermission(perm.getFirst(), perm.getThird()); } else { project.setUserPermission(perm.getFirst(), perm.getThird()); } } } return project; }
From source file:io.apiman.gateway.engine.jdbc.JdbcRegistry.java
/** * Removes all of the api contracts from the database. * @param client/*from ww w . j a v a 2 s.com*/ * @param connection * @throws SQLException */ protected void unregisterApiContracts(Client client, Connection connection) throws SQLException { QueryRunner run = new QueryRunner(); run.update(connection, "DELETE FROM contracts WHERE client_org_id = ? AND client_id = ? AND client_version = ?", //$NON-NLS-1$ client.getOrganizationId(), client.getClientId(), client.getVersion()); }
From source file:hermes.store.schema.DefaultJDBCAdapter.java
public void insert(Connection connection, String storeId, Message message) throws SQLException, JMSException { final String destinationName = message.getJMSDestination() == null ? "default" : JMSUtils.getDestinationName(message.getJMSDestination()); final Domain domain = message.getJMSDestination() == null ? Domain.QUEUE : Domain.getDomain(message.getJMSDestination()); final String messageAsXMLString = xmlHelper.toXML(message); final InputStream messageAsXML = new StringInputStream(messageAsXMLString); final String messageId = getNextMessageId(storeId); ///*from w ww .j a v a 2 s . c om*/ // DBUtils does not seem to correctly deal with CLOBS, so we have to use // normal JDBC... // // runner.update(connection, "insert into messages values (?, ?)", new // Object[] { message.getJMSMessageID(), messageAsXML }); final PreparedStatement pstmt = connection.prepareStatement("insert into messages values (?, ?)"); pstmt.setString(1, messageId); pstmt.setAsciiStream(2, messageAsXML, messageAsXMLString.length()); pstmt.execute(); pstmt.close(); final QueryRunner runner = new QueryRunner(); runner.update(connection, "insert into stores values (?, ?, ?, ?)", new Object[] { storeId, destinationName, domain.getId(), messageId }); }
From source file:it.attocchi.db.DbUtilsConnector.java
public List<Map<String, Object>> executeMap(boolean keepConnOpen, String aQuery) throws Exception { List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); // No DataSource so we must handle Connections manually QueryRunner run = new QueryRunner(); try {/*from w w w .j av a 2 s .c o m*/ /* * Sembra che il like con i parametri ufficiali non funzioni, forse * dovuto al fatto che son tutti object */ logger.debug(aQuery); result = run.query(getConnection(), aQuery, new MapListHandler()); } finally { if (!keepConnOpen) close(); } return result; }