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:org.codesearch.commons.database.DBAccessImpl.java
/** * gets the id of the repository with the given name * /*from www . j a va2s . c om*/ * @param repoName the name of the repository * @return the id if the repository, or -1 if not found * @throws DatabaseAccessException */ private int getRepoIdForRepoName(String repoName) throws DatabaseAccessException { int repoId = -1; QueryRunner run = new QueryRunner(dataSource); SingleValueStringHandler h = new SingleValueStringHandler(); try { String result = run.query(STMT_GET_REPO_ID_FOR_NAME, h, repoName); if (StringUtils.isNotEmpty(result)) { repoId = Integer.parseInt(result); } } catch (SQLException ex) { throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex); } return repoId; }
From source file:org.dbmfs.DatabaseAccessor.java
/** * ????????.<br>/*from w w w . j a v a 2 s . c om*/ * * @param tableName ?? * @return ?????? */ public List<String> getRecordKeyList(String tableName, int offset, int limit) throws Exception { List<String> resultList = new ArrayList(); try { // ? List<String> primaryKeyColumnNames = getPrimaryKeyColumnNames(tableName); // ???????? if (primaryKeyColumnNames.size() == 0) return resultList; // ??select??????? // TODO:??????????? // SQL?? String query = createAllColumnQuery(tableName, offset, limit); // String query = createPrimaryKeyQuery(tableName, primaryKeyColumnNames); ResultSetHandler<?> resultSetHandler = new MapListHandler(); QueryRunner qr = new QueryRunner(); // List<Map<String, Object>> queryResult = (List<Map<String, Object>>) qr.query(injectConn, query, resultSetHandler); // ????? Map<String, Map<String, Object>> allColumnMeta = getAllColumnMeta(tableName, true); String metaSerializeString = serializeMetaInfomation(allColumnMeta); // ?????? for (int idx = 0; idx < queryResult.size(); idx++) { Map data = queryResult.get(idx); StringBuilder queryDataStrBuf = new StringBuilder(40); String pKeyStrSep = ""; for (int pIdx = 0; pIdx < primaryKeyColumnNames.size(); pIdx++) { queryDataStrBuf.append(pKeyStrSep); queryDataStrBuf.append(data.get(primaryKeyColumnNames.get(pIdx))); pKeyStrSep = DatabaseAccessor.primaryKeySep; } data.put(tableMetaInfoKey, metaSerializeString); List cachePutList = new ArrayList(); cachePutList.add(data); dataCacheFolder.put(tableName + tableNameSep + queryDataStrBuf.toString(), cachePutList); resultList.add(queryDataStrBuf.toString()); } } catch (Exception e) { e.printStackTrace(); throw e; } return resultList; }
From source file:org.dbmfs.DatabaseAccessor.java
/** * ?????????????.<br>//w ww . jav a2 s .com * * @param query (Select?) * @param primaryKeyColumnNames ????? * @return ?????? */ public List<String> getRecordKeyList(String query, List<String> primaryKeyColumnNames, int offset, int limit) throws Exception { List<String> resultList = new ArrayList(); try { ResultSetHandler<?> resultSetHandler = new MapListHandler(); QueryRunner qr = new QueryRunner(); // ???limit offset String executeQuery = createAllColumnQuery("(" + query + ")", offset, limit); // List<Map<String, Object>> queryResult = (List<Map<String, Object>>) qr.query(injectConn, query, resultSetHandler); // ?????? for (int idx = 0; idx < queryResult.size(); idx++) { Map data = queryResult.get(idx); StringBuilder queryDataStrBuf = new StringBuilder(40); String pKeyStrSep = ""; for (int pIdx = 0; pIdx < primaryKeyColumnNames.size(); pIdx++) { queryDataStrBuf.append(pKeyStrSep); queryDataStrBuf.append(data.get(primaryKeyColumnNames.get(pIdx))); // TODO???data.get????queryprimaryKeyColumnNames.get(pIdx)??? // ?(TODONo1)?? pKeyStrSep = DatabaseAccessor.primaryKeySep; } /* List cachePutList = new ArrayList(); cachePutList.add(data); dataCacheFolder.put(tableName + tableNameSep + queryDataStrBuf.toString(), cachePutList);*/ resultList.add(queryDataStrBuf.toString()); } } catch (Exception e) { e.printStackTrace(); throw e; } return resultList; }
From source file:org.kaaproject.kaa.server.datamigration.MigrateData.java
/** * The entry point of migrate data application. * * @param args the input options/* www . j a va2s . c o m*/ */ public static void main(String[] args) { Options options = new Options(); for (int i = 0; i < args.length; i++) { String arg = args[i]; if (arg.charAt(0) == '-') { String option = arg.substring(1, arg.length()).trim(); if (i >= args.length - 1) { throw new IllegalArgumentException("Not found value after option -" + option); } switch (option) { case "u": options.setUsername(args[i + 1]); break; case "p": options.setPassword(args[i + 1]); break; case "h": options.setHost(args[i + 1]); break; case "db": options.setDbName(args[i + 1]); break; case "nosql": options.setNoSql(args[i + 1]); break; case "driver": options.setDriverClassName(args[i + 1]); break; case "url": options.setJdbcUrl(args[i + 1]); break; default: throw new IllegalArgumentException("No such option: -" + option); } } } LOG.debug(options.toString()); try { List<Schema> schemas = new ArrayList<>(); conn = DataSources.getDataSource(options).getConnection(); QueryRunner runner = new QueryRunner(); Long maxId = runner.query(conn, "select max(id) as max_id from base_schems", rs -> rs.next() ? rs.getLong("max_id") : null); BaseSchemaIdCounter.setInitValue(maxId); final UpdateUuidsMigration updateUuidsMigration = new UpdateUuidsMigration(conn, options); final EndpointProfileMigration endpointProfileMigration = new EndpointProfileMigration(options); List<AbstractCtlMigration> migrationList = new ArrayList<>(); migrationList.add(new CtlConfigurationMigration(conn)); migrationList.add(new CtlEventsMigration(conn)); migrationList.add(new CtlNotificationMigration(conn, options)); migrationList.add(new CtlLogMigration(conn)); new EndpointSpecificConfigurationMigration(options.getHost(), options.getDbName(), options.getNoSql()) .transform(); final CtlAggregation aggregation = new CtlAggregation(conn); final BaseSchemaRecordsCreation recordsCreation = new BaseSchemaRecordsCreation(conn); // convert uuids from latin1 to base64 updateUuidsMigration.transform(); endpointProfileMigration.transform(); //before phase for (AbstractCtlMigration m : migrationList) { m.beforeTransform(); } // transform phase for (AbstractCtlMigration m : migrationList) { schemas.addAll(m.transform()); } //aggregation phase Map<Ctl, List<Schema>> ctlToSchemas = aggregation.aggregate(schemas); //base schema records creation phase recordsCreation.create(ctlToSchemas); //after phase for (AbstractCtlMigration m : migrationList) { m.afterTransform(); } conn.commit(); } catch (SQLException | IOException | ConfigurationGenerationException ex) { LOG.error("Error: " + ex.getMessage(), ex); DbUtils.rollbackAndCloseQuietly(conn); } finally { DbUtils.rollbackAndCloseQuietly(conn); } }
From source file:org.kaaproject.kaa.server.datamigration.UpdateUuidsMigration.java
/** * Change encoding of uuids from Latin1 to Base64 in relational and NoSQL databases. * *///from w ww. j a v a 2 s. c om public void transform() throws IOException, SQLException { QueryRunner run = new QueryRunner(); ResultSetHandler<List<Configuration>> rsHandler = new BeanListHandler<>(Configuration.class); List<Configuration> configs = run.query(connection, "SELECT * FROM configuration", rsHandler); for (Configuration config : configs) { JsonNode json = new ObjectMapper().readTree(config.getConfigurationBody()); JsonNode jsonEncoded = encodeUuids(json); byte[] encodedConfigurationBody = jsonEncoded.toString().getBytes(); int updates = run.update(connection, "UPDATE configuration SET configuration_body=? WHERE id=?", encodedConfigurationBody, config.getId()); if (updates != 1) { System.err.println("Error: failed to update configuration: " + config); } } if (nosql.equals(Options.DEFAULT_NO_SQL)) { MongoDatabase database = client.getDatabase(dbName); MongoCollection<Document> userConfiguration = database.getCollection("user_configuration"); FindIterable<Document> documents = userConfiguration.find(); for (Document d : documents) { String body = (String) d.get("body"); JsonNode json = new ObjectMapper().readTree(body); JsonNode jsonEncoded = encodeUuids(json); userConfiguration.updateOne(Filters.eq("_id", d.get("_id")), Filters.eq("$set", Filters.eq("body", jsonEncoded))); } } else { Session session = cluster.connect(dbName); BatchStatement batchStatement = new BatchStatement(); String tableName = "user_conf"; ResultSet results = session.execute(select().from(tableName)); for (Row row : results) { String userId = row.getString("user_id"); String appToken = row.getString("app_token"); int schemaVersion = row.getInt("schema_version"); String body = row.getString("body"); String bodyEncoded = encodeUuids(new ObjectMapper().readTree(body)).toString(); batchStatement.add(update(tableName).with(set("body", bodyEncoded)).where(eq("user_id", userId)) .and(eq("app_token", appToken)).and(eq("schema_version", schemaVersion))); } session.execute(batchStatement); session.close(); cluster.close(); } }
From source file:org.kaaproject.kaa.server.datamigration.utils.datadefinition.DataDefinition.java
/** * Drop foreign key with autogenerated name based on the table where constrain declared and * referenced table name.//from w w w . j a va 2 s. co m */ public void dropUnnamedFk(String tableName, String referencedTableName) throws SQLException { QueryRunner runner = new QueryRunner(); String query = String.format(QUERY_FIND_FK_NAME, tableName, referencedTableName); String fkName = runner.query(connection, query, rs -> rs.next() ? rs.getString(1) : null); if (fkName != null) { runner.update(connection, "ALTER TABLE " + tableName + " DROP FOREIGN KEY " + fkName); } else { System.err.println("FK name not found"); } }
From source file:org.moneta.dao.RecordResultSetHandlerTest.java
@Test public void testBasicHappyPath() throws Exception { QueryRunner runner = new QueryRunner(); Record[] recArray = runner.query(nativeConnection, "select * from INFORMATION_SCHEMA.SYSTEM_TABLES", handler);//from w w w . java2 s . c o m Assert.assertTrue(recArray != null); Assert.assertTrue(recArray.length == 92); Assert.assertTrue(searchForColumn(recArray, "Catalog")); Assert.assertTrue(!searchForColumn(recArray, "TABLE_CAT")); Assert.assertTrue(searchForColumn(recArray, "TABLE_TYPE")); handler.setStartRow(90L); recArray = runner.query(nativeConnection, "select * from INFORMATION_SCHEMA.SYSTEM_TABLES", handler); Assert.assertTrue(recArray != null); Assert.assertTrue(recArray.length == 3); handler.setStartRow(null); handler.setMaxRows(10L); recArray = runner.query(nativeConnection, "select * from INFORMATION_SCHEMA.SYSTEM_TABLES", handler); Assert.assertTrue(recArray != null); Assert.assertTrue(recArray.length == 10); }
From source file:org.mule.transport.jdbc.functional.JdbcBridgeFunctionalTestCase.java
protected void doTestBridge() throws Exception { QueryRunner queryRunner = jdbcConnector.getQueryRunner(); Connection connection = jdbcConnector.getConnection(); for (int i = 0; i < TEST_ROWS; i++) { queryRunner.update(connection, "INSERT INTO TEST(TYPE, DATA) VALUES (1, 'Test " + i + "')"); }/* www .j a va2 s . c om*/ List<?> results = (List<?>) queryRunner.query(connection, "SELECT * FROM TEST WHERE TYPE = 1", new ArrayListHandler()); assertEquals(TEST_ROWS, results.size()); long t0 = System.currentTimeMillis(); while (true) { results = (List<?>) queryRunner.query(connection, "SELECT * FROM TEST_OUT", new ArrayListHandler()); logger.info("Results found: " + results.size()); if (results.size() >= TEST_ROWS) { break; } results = (List<?>) queryRunner.query(connection, "SELECT * FROM TEST WHERE TYPE = 2", new ArrayListHandler()); logger.info("Locked records found: " + results.size()); assertTrue(TEST_ROWS >= results.size()); results = (List<?>) queryRunner.query(connection, "SELECT * FROM TEST WHERE TYPE = 1", new ArrayListHandler()); logger.info("Original records found: " + results.size()); assertTrue(TEST_ROWS >= results.size()); assertTrue(System.currentTimeMillis() - t0 < 20000); Thread.sleep(500); } }
From source file:org.mule.transport.jdbc.functional.JdbcEndpointWithNestedQueriesTestCase.java
@Test public void testDisposeAfterQueryExecution() throws Exception { QueryRunner queryRunner = jdbcConnector.getQueryRunner(); Connection connection = jdbcConnector.getConnection(); // Send a message to trigger nested query on JDBC outbound endpoint to execute. MuleClient client = muleContext.getClient(); client.send("vm://in", "some test data", null); // Assert that query executed correctly. List<?> results = (List<?>) queryRunner.query(connection, "SELECT * FROM TEST", new ArrayListHandler()); assertEquals(1, results.size());// w w w . j a va2s .c om // Try to dispose gracefully. try { muleContext.dispose(); } catch (Exception ex) { fail("Server disposal failed"); } }
From source file:org.mule.transport.jdbc.functional.JdbcFunctionalTestCase.java
@Test public void testSend() throws Exception { MuleClient client = new MuleClient(muleContext); client.send("jdbc://writeTest?type=2", new DefaultMuleMessage(TEST_MESSAGE, muleContext)); QueryRunner qr = jdbcConnector.getQueryRunner(); Object[] obj2 = (Object[]) qr.query(jdbcConnector.getConnection(), "SELECT DATA FROM TEST WHERE TYPE = 2", new ArrayHandler()); assertNotNull(obj2);/* w w w . j a v a2 s. co m*/ assertEquals(1, obj2.length); assertEquals(TEST_MESSAGE, obj2[0]); }