List of usage examples for org.apache.commons.dbutils QueryRunner update
public int update(String sql, Object... params) throws SQLException
From source file:cn.itcast.bbs.dao.FlowDao.java
public void updateFlow(Flow flow) throws SQLException { QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource()); String sql = "update flow set num = ?,historydate = ? where id = ? "; System.out.println(flow.getNum() + ":" + new java.sql.Date(flow.getHistorydate().getTime())); runner.update(sql, new Object[] { flow.getNum(), new java.sql.Date(flow.getHistorydate().getTime()), 1 }); }
From source file:io.apiman.gateway.engine.jdbc.PollCachingJdbcRegistry.java
/** * Stores a "dataversion" record in the ES store. There is only a single one of these. The * return value of the add will include the version number of the entity. This version * number is what we use to determine whether our cache is stale. *//*w w w . ja v a 2 s .com*/ protected void updateDataVersion() { Connection conn = null; try { long newVersion = System.currentTimeMillis(); conn = ds.getConnection(); conn.setAutoCommit(false); QueryRunner run = new QueryRunner(); run.update(conn, "DELETE FROM gw_dataversion"); //$NON-NLS-1$ run.update(conn, "INSERT INTO gw_dataversion (version) VALUES (?)", //$NON-NLS-1$ newVersion); DbUtils.commitAndClose(conn); dataVersion = newVersion; } catch (SQLException e) { dataVersion = -1; } }
From source file:name.marcelomorales.siqisiqi.bonecp.DataSourceProviderTest.java
@Test public void testStatistics() throws Exception { Config config = ConfigFactory.parseString("bonecp.url=\"jdbc:derby:memory:dbstats;create=true\"") .withFallback(ConfigFactory.load()); DataSourceProvider dsp = new DataSourceProvider(config); assertNull(dsp.getStatistics());/*w w w . j ava 2s .c om*/ DataSource dataSource = dsp.get(); Connection connection = dataSource.getConnection(); connection.createStatement().execute("CREATE TABLE TABLETEST1 (ACOLUMN VARCHAR(10))"); connection.commit(); QueryRunner queryRunner = new QueryRunner(); queryRunner.update(connection, "INSERT INTO TABLETEST1 VALUES ('AAA')"); queryRunner.update(connection, "INSERT INTO TABLETEST1 VALUES (?)", "BBB"); queryRunner.update(connection, "INSERT INTO TABLETEST1 VALUES (?)", "CCC"); connection.commit(); List<String> values = queryRunner.query(connection, "SELECT * FROM TABLETEST1 ORDER BY ACOLUMN ASC", new ColumnListHandler<String>()); connection.commit(); connection.close(); assertEquals("AAA", values.get(0)); assertEquals("BBB", values.get(1)); assertEquals("CCC", values.get(2)); Statistics statistics = dsp.getStatistics(); assertTrue(statistics.getCacheHits() > 0); assertTrue(statistics.getCacheMiss() > 0); assertEquals(1, statistics.getConnectionsRequested()); assertEquals(4, statistics.getStatementsPrepared()); dsp.close(); }
From source file:azkaban.trigger.JdbcTriggerLoaderTest.java
@After public void clearDB() { if (!testDBExists) { return;//from w w w . java 2 s .c om } DataSource dataSource = DataSourceUtils.getMySQLDataSource(host, port, database, user, password, numConnections); Connection connection = null; try { connection = dataSource.getConnection(); } catch (SQLException e) { e.printStackTrace(); testDBExists = false; DbUtils.closeQuietly(connection); return; } QueryRunner runner = new QueryRunner(); try { runner.update(connection, "DELETE FROM triggers"); } catch (SQLException e) { e.printStackTrace(); testDBExists = false; DbUtils.closeQuietly(connection); return; } DbUtils.closeQuietly(connection); }
From source file:info.pancancer.arch3.persistence.PostgreSQL.java
private boolean runUpdateStatement(String query, Object... params) { try {//from w ww . ja v a 2 s . c o m QueryRunner run = new QueryRunner(dataSource); run.update(query, params); return true; } catch (SQLException e) { throw new RuntimeException(e); } }
From source file:azkaban.scheduler.JdbcScheduleLoader.java
@Override public void removeSchedule(Schedule s) throws ScheduleManagerException { logger.info("Removing schedule " + s.getScheduleName() + " from db."); QueryRunner runner = createQueryRunner(); try {//from www. ja v a 2 s . c om int removes = runner.update(REMOVE_SCHEDULE_BY_KEY, s.getScheduleId()); if (removes == 0) { throw new ScheduleManagerException("No schedule has been removed."); } } catch (SQLException e) { logger.error(REMOVE_SCHEDULE_BY_KEY + " failed."); throw new ScheduleManagerException("Remove schedule " + s.getScheduleName() + " from db failed. ", e); } }
From source file:de.unibremen.informatik.tdki.combo.ui.CommandComplete.java
private static void dropFilter(String namePattern) { // TODO: add a stored procedure for filter dropping try {//w w w. j a v a 2s .c o m final QueryRunner qRunner = new QueryRunner(); qRunner.query( "SELECT funcname, parm_count FROM Syscat.Functions WHERE funcname LIKE '" + namePattern + "%'", new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { StringBuilder query = new StringBuilder(); query.append("DROP FUNCTION ").append(rs.getString(1)).append("("); int noOfParams = rs.getInt(2); for (int i = 0; i < noOfParams; i++) { query.append("INTEGER"); if (i != noOfParams - 1) { query.append(","); } } query.append(")"); qRunner.update(connection, query.toString()); } }); } catch (SQLException ex) { throw new RuntimeException(ex); } }
From source file:azkaban.trigger.JdbcTriggerLoader.java
@Override public void removeTrigger(Trigger t) throws TriggerLoaderException { logger.info("Removing trigger " + t.toString() + " from db."); QueryRunner runner = createQueryRunner(); try {//from w w w .ja va 2 s . com int removes = runner.update(REMOVE_TRIGGER, t.getTriggerId()); if (removes == 0) { throw new TriggerLoaderException("No trigger has been removed."); } } catch (SQLException e) { logger.error(REMOVE_TRIGGER + " failed."); throw new TriggerLoaderException("Remove trigger " + t.toString() + " from db failed. ", e); } }
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();//from w w w . j a va2 s . c o m 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.database.AzkabanDatabaseSetup.java
private void runTableScripts(Connection conn, String table, String version, String dbType, boolean update) throws IOException, SQLException { String scriptName = ""; if (update) { scriptName = "update." + table + "." + version; logger.info("Update table " + table + " to version " + version); } else {// w w w .ja va2 s. c o m scriptName = "create." + table; logger.info("Creating new table " + table + " version " + version); } String dbSpecificScript = scriptName + "." + dbType + ".sql"; File script = new File(scriptPath, dbSpecificScript); if (!script.exists()) { String dbScript = scriptName + ".sql"; script = new File(scriptPath, dbScript); if (!script.exists()) { throw new IOException("Creation files do not exist for table " + table); } } BufferedInputStream buff = null; try { buff = new BufferedInputStream(new FileInputStream(script)); String queryStr = IOUtils.toString(buff); String[] splitQuery = queryStr.split(";\\s*\n"); QueryRunner runner = new QueryRunner(); for (String query : splitQuery) { runner.update(conn, query); } // If it's properties, then we want to commit the table before we update // it if (table.equals("properties")) { conn.commit(); } String propertyName = table + ".version"; if (!installedVersions.containsKey(table)) { runner.update(conn, INSERT_DB_PROPERTY, propertyName, DataSourceUtils.PropertyType.DB.getNumVal(), version, System.currentTimeMillis()); } else { runner.update(conn, UPDATE_DB_PROPERTY, version, System.currentTimeMillis(), propertyName, DataSourceUtils.PropertyType.DB.getNumVal()); } conn.commit(); } finally { IOUtils.closeQuietly(buff); } }