List of usage examples for org.apache.commons.dbutils QueryRunner QueryRunner
public QueryRunner()
From source file:io.apiman.gateway.engine.jdbc.JdbcRegistry.java
/** * @see io.apiman.gateway.engine.IRegistry#registerClient(io.apiman.gateway.engine.beans.Client, io.apiman.gateway.engine.async.IAsyncResultHandler) *//*from w w w .j ava2 s . c o m*/ @Override public void registerClient(Client client, IAsyncResultHandler<Void> handler) { Connection conn = null; try { conn = ds.getConnection(); conn.setAutoCommit(false); QueryRunner run = new QueryRunner(); // Validate the client and populate the api map with apis found during validation. validateClient(client, conn); // Remove any old data first, then (re)insert run.update(conn, "DELETE FROM gw_clients WHERE org_id = ? AND id = ? AND version = ?", //$NON-NLS-1$ client.getOrganizationId(), client.getClientId(), client.getVersion()); String bean = mapper.writeValueAsString(client); run.update(conn, "INSERT INTO gw_clients (api_key, org_id, id, version, bean) VALUES (?, ?, ?, ?, ?)", //$NON-NLS-1$ client.getApiKey(), client.getOrganizationId(), client.getClientId(), client.getVersion(), bean); DbUtils.commitAndClose(conn); handler.handle(AsyncResultImpl.create((Void) null)); } catch (Exception re) { DbUtils.rollbackAndCloseQuietly(conn); handler.handle(AsyncResultImpl.create(re, Void.class)); } }
From source file:de.unibremen.informatik.tdki.combo.rewriting.FakeFilterRewriter.java
private void createFilterInDB2() { for (int j = 1; j < 6; j++) { StringBuilder builder = new StringBuilder(); builder.append("CREATE FUNCTION "); builder.append("fake_filter").append(libNo).append("("); for (int i = 0; i < j; i++) { builder.append("inParm").append(i).append(" INTEGER"); if (i < j - 1) { builder.append(", "); }//from w w w. j ava 2 s .co m } builder.append(")\n"); builder.append("RETURNS INTEGER\n"); builder.append("LANGUAGE C\n"); builder.append("PARAMETER STYLE SQL\n"); builder.append("NO SQL\n"); builder.append("NOT FENCED\n"); builder.append("THREADSAFE\n"); builder.append("DETERMINISTIC\n"); builder.append("RETURNS NULL ON NULL INPUT\n"); builder.append("NO EXTERNAL ACTION\n"); builder.append("EXTERNAL NAME \'").append(outputDir).append("libDB2FakeFilter").append(libNo) .append(".dylib!fake_filter").append(libNo).append("\'"); QueryRunner qRunner = new QueryRunner(); try { qRunner.update(connection, builder.toString()); } catch (SQLException ex) { throw new RuntimeException(ex); } } }
From source file:dbutils.ExampleJDBC.java
/** * ?/* ww w. ja v a 2 s . c o m*/ */ public static void insertAndUpdateData() throws SQLException { Connection conn = getConnection(); QueryRunner qr = new QueryRunner(); try { //??insert? Object[] insertParams = { "John Doe", "", 12, 3 }; int inserts = qr.update(conn, "INSERT INTO test_student(name,gender,age,team_id) VALUES (?,?,?,?)", insertParams); System.out.println("inserted " + inserts + " data"); Object[] updateParams = { "John Doe Update", "John Doe" }; int updates = qr.update(conn, "UPDATE test_student SET name=? WHERE name=?", updateParams); System.out.println("updated " + updates + " data"); } catch (SQLException e) { e.printStackTrace(); conn.rollback(); } finally { DbUtils.close(conn); } }
From source file:azkaban.executor.JdbcExecutorLoader.java
private synchronized void uploadExecutableFlow(Connection connection, ExecutableFlow flow, EncodingType encType) throws ExecutorManagerException, IOException { final String INSERT_EXECUTABLE_FLOW = "INSERT INTO execution_flows " + "(project_id, flow_id, version, status, submit_time, submit_user, update_time) " + "values (?,?,?,?,?,?,?)"; QueryRunner runner = new QueryRunner(); long submitTime = System.currentTimeMillis(); long id;//from ww w .ja v a 2 s . co m try { flow.setStatus(Status.PREPARING); runner.update(connection, INSERT_EXECUTABLE_FLOW, flow.getProjectId(), flow.getFlowId(), flow.getVersion(), Status.PREPARING.getNumVal(), submitTime, flow.getSubmitUser(), submitTime); connection.commit(); id = runner.query(connection, LastInsertID.LAST_INSERT_ID, new LastInsertID()); if (id == -1L) { throw new ExecutorManagerException("Execution id is not properly created."); } logger.info("Flow given " + flow.getFlowId() + " given id " + id); flow.setExecutionId((int) id); updateExecutableFlow(connection, flow, encType); } catch (SQLException e) { throw new ExecutorManagerException("Error creating execution.", e); } }
From source file:com.ouc.cpss.dao.BaseDao.java
/** * ?// w ww .j a v a 2 s.c o m * * @param conn * @param sql sql? * @param params ?? * @return */ public boolean batchUpdate(Connection conn, String sql, Object[][] params) throws SQLException { QueryRunner qRunner = new QueryRunner(); int result = 0; boolean flag = false; result = qRunner.batch(conn, sql, params).length; if (result > 0) { flag = true; } return flag; }
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; }/*from w w w . ja 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:com.versatus.jwebshield.securitylock.SecurityLockService.java
private SecurityLock checkSecurityLock(int userId, String ip) throws SQLException { logger.debug("checkAccountLock: userid=" + userId); logger.debug("checkAccountLock: ip=" + ip); SecurityLock res;//from ww w. ja v a 2s. co m Object[] params = new Object[] { userId, ip }; QueryRunner run = new QueryRunner(); Connection conn = dbHelper.getConnection(); BeanHandler<SecurityLock> rsh = new BeanHandler(SecurityLock.class) { @Override public SecurityLock handle(ResultSet rs) throws SQLException { SecurityLock brp = null; if (rs.first()) { brp = new BasicRowProcessor().toBean(rs, SecurityLock.class); } return brp; } }; try { res = run.query(conn, lockCheckSql, rsh, params); logger.debug("checkAccountLock: response=" + res); if (res != null) { if (res.isLock()) { logger.debug("checkAccountLock: Calendar.getInstance()=" + Calendar.getInstance().getTime()); logger.debug("checkAccountLock: TimeWhenUnlock()=" + res.getTimeWhenUnlock()); logger.debug("checkAccountLock: is time to ulock=" + Calendar.getInstance().getTime().after(res.getTimeWhenUnlock())); if (Calendar.getInstance().getTime().after(res.getTimeWhenUnlock())) { logger.info("unlocking IP " + res.getIp()); int r = run.update(conn, resetLockSql, new Object[] { ip }); logger.debug("checkAccountLock: reset response=" + r); res = run.query(conn, lockCheckSql, rsh, params); logger.debug("checkAccountLock: after reset response=" + res); } } } else { res = new SecurityLock(); res.setLock(false); } } finally { try { DbUtils.close(conn); } catch (SQLException e) { // ignore } } return res; }
From source file:it.attocchi.db.DbUtilsConnector.java
public <T> List<T> executeTrimedString(boolean keepConnOpen, String aQuery, Class<T> clazz) throws Exception { List<T> result = new ArrayList<T>(); // No DataSource so we must handle Connections manually QueryRunner run = new QueryRunner() { protected ResultSet wrap(ResultSet rs) { return StringTrimmedResultSet.wrap(rs); }/*from w ww .j a v a 2 s . co m*/ }; try { /* * 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, getResultSetHandler(clazz)); } finally { if (!keepConnOpen) close(); } return result; }
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);/*www . ja v a2 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:azkaban.migration.scheduler.JdbcScheduleLoader.java
@Override public List<Schedule> loadSchedules() throws ScheduleManagerException { logger.info("Loading all schedules from db."); Connection connection = getConnection(); QueryRunner runner = new QueryRunner(); ResultSetHandler<List<Schedule>> handler = new ScheduleResultHandler(); List<Schedule> schedules; try {/*from www .j a v a2 s . c o m*/ schedules = runner.query(connection, SELECT_ALL_SCHEDULES, handler); } catch (SQLException e) { logger.error(SELECT_ALL_SCHEDULES + " failed."); DbUtils.closeQuietly(connection); throw new ScheduleManagerException("Loading schedules from db failed. ", e); } finally { DbUtils.closeQuietly(connection); } logger.info("Now trying to update the schedules"); // filter the schedules Iterator<Schedule> scheduleIterator = schedules.iterator(); while (scheduleIterator.hasNext()) { Schedule sched = scheduleIterator.next(); if (!sched.updateTime()) { logger.info("Schedule " + sched.getScheduleName() + " was scheduled before azkaban start, skipping it."); scheduleIterator.remove(); removeSchedule(sched); } else { logger.info("Recurring schedule, need to update next exec time"); try { updateNextExecTime(sched); } catch (Exception e) { e.printStackTrace(); throw new ScheduleManagerException("Update next execution time failed.", e); } logger.info("Schedule " + sched.getScheduleName() + " loaded and updated."); } } logger.info("Loaded " + schedules.size() + " schedules."); return schedules; }