Examples with QueryRunner org.apache.commons.dbutils.QueryRunner used on opensource projects

Example 41 with QueryRunner

use of org.apache.commons.dbutils.QueryRunner in project metacat by Netflix.

the class MysqlUserMetadataService method searchByOwners.

@Override
public List<QualifiedName> searchByOwners(final Set<String> owners) {
    final List<QualifiedName> result = Lists.newArrayList();
    final StringBuilder query = new StringBuilder(SQL.SEARCH_DEFINITION_METADATA_NAMES);
    final List<Object> paramList = Lists.newArrayList();
    query.append(" where 1=0");
    owners.forEach(s -> {
        query.append(" or data like ?");
        paramList.add("%\"userId\":\"" + s.trim() + "\"%");
    });
    final Object[] params = new Object[paramList.size()];
    final Connection connection = DBUtil.getReadConnection(poolingDataSource);
    try {
        // Handler for reading the result set
        final ResultSetHandler<Void> handler = rs -> {
            while (rs.next()) {
                final String definitionName = rs.getString("name");
                result.add(QualifiedName.fromString(definitionName, false));
            }
            return null;
        };
        new QueryRunner().query(connection, query.toString(), handler, paramList.toArray(params));
    } catch (SQLException e) {
        log.error("Sql exception", e);
        throw new UserMetadataServiceException("Failed to get definition data", e);
    } finally {
        DBUtil.closeReadConnection(connection);
    }
    return result;
}

Example 42 with QueryRunner

use of org.apache.commons.dbutils.QueryRunner in project metacat by Netflix.

the class MysqlUserMetadataService method saveMetadatas.

@Override
public void saveMetadatas(final String user, final List<? extends HasMetadata> metadatas, final boolean merge) {
    try {
        final Connection conn = poolingDataSource.getConnection();
        try {
            @SuppressWarnings("unchecked") final List<List<HasMetadata>> subLists = Lists.partition((List<HasMetadata>) metadatas, config.getUserMetadataMaxInClauseItems());
            for (List<HasMetadata> hasMetadatas : subLists) {
                final List<String> uris = Lists.newArrayList();
                final List<QualifiedName> names = Lists.newArrayList();
                // Get the names and uris
                final List<HasDefinitionMetadata> definitionMetadatas = Lists.newArrayList();
                final List<HasDataMetadata> dataMetadatas = Lists.newArrayList();
                hasMetadatas.stream().forEach(hasMetadata -> {
                    if (hasMetadata instanceof HasDefinitionMetadata) {
                        final HasDefinitionMetadata oDef = (HasDefinitionMetadata) hasMetadata;
                        names.add(oDef.getDefinitionName());
                        if (oDef.getDefinitionMetadata() != null) {
                            definitionMetadatas.add(oDef);
                        }
                    }
                    if (hasMetadata instanceof HasDataMetadata) {
                        final HasDataMetadata oData = (HasDataMetadata) hasMetadata;
                        if (oData.isDataExternal() && oData.getDataMetadata() != null && oData.getDataMetadata().size() > 0) {
                            uris.add(oData.getDataUri());
                            dataMetadatas.add(oData);
                        }
                    }
                });
                if (!definitionMetadatas.isEmpty() || !dataMetadatas.isEmpty()) {
                    // Get the existing metadata based on the names and uris
                    final Map<String, ObjectNode> definitionMap = getDefinitionMetadataMap(names);
                    final Map<String, ObjectNode> dataMap = getDataMetadataMap(uris);
                    // Curate the list of existing and new metadatas
                    final List<Object[]> insertDefinitionMetadatas = Lists.newArrayList();
                    final List<Object[]> updateDefinitionMetadatas = Lists.newArrayList();
                    final List<Object[]> insertDataMetadatas = Lists.newArrayList();
                    final List<Object[]> updateDataMetadatas = Lists.newArrayList();
                    definitionMetadatas.stream().forEach(oDef -> {
                        final QualifiedName qualifiedName = oDef.getDefinitionName();
                        if (qualifiedName != null && oDef.getDefinitionMetadata() != null && oDef.getDefinitionMetadata().size() != 0) {
                            final String name = qualifiedName.toString();
                            final ObjectNode oNode = definitionMap.get(name);
                            if (oNode == null) {
                                insertDefinitionMetadatas.add(new Object[] { metacatJson.toJsonString(oDef.getDefinitionMetadata()), user, user, name });
                            } else {
                                metacatJson.mergeIntoPrimary(oNode, oDef.getDefinitionMetadata());
                                updateDefinitionMetadatas.add(new Object[] { metacatJson.toJsonString(oNode), user, name });
                            }
                        }
                    });
                    dataMetadatas.stream().forEach(oData -> {
                        final String uri = oData.getDataUri();
                        final ObjectNode oNode = dataMap.get(uri);
                        if (oData.getDataMetadata() != null && oData.getDataMetadata().size() != 0) {
                            if (oNode == null) {
                                insertDataMetadatas.add(new Object[] { metacatJson.toJsonString(oData.getDataMetadata()), user, user, uri });
                            } else {
                                metacatJson.mergeIntoPrimary(oNode, oData.getDataMetadata());
                                updateDataMetadatas.add(new Object[] { metacatJson.toJsonString(oNode), user, uri });
                            }
                        }
                    });
                    //Now run the queries
                    final QueryRunner runner = new QueryRunner();
                    if (!insertDefinitionMetadatas.isEmpty()) {
                        runner.batch(conn, SQL.INSERT_DEFINITION_METADATA, insertDefinitionMetadatas.toArray(new Object[insertDefinitionMetadatas.size()][4]));
                    }
                    if (!updateDefinitionMetadatas.isEmpty()) {
                        runner.batch(conn, SQL.UPDATE_DEFINITION_METADATA, updateDefinitionMetadatas.toArray(new Object[updateDefinitionMetadatas.size()][3]));
                    }
                    if (!insertDataMetadatas.isEmpty()) {
                        runner.batch(conn, SQL.INSERT_DATA_METADATA, insertDataMetadatas.toArray(new Object[insertDataMetadatas.size()][4]));
                    }
                    if (!updateDataMetadatas.isEmpty()) {
                        runner.batch(conn, SQL.UPDATE_DATA_METADATA, updateDataMetadatas.toArray(new Object[updateDataMetadatas.size()][3]));
                    }
                }
            }
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.close();
        }
    } catch (SQLException e) {
        log.error("Sql exception", e);
        throw new UserMetadataServiceException("Failed to save metadata", e);
    }
}

Example 43 with QueryRunner

use of org.apache.commons.dbutils.QueryRunner in project notes by KevinBlandy.

the class UtilsDemo method update.

public static void update() throws SQLException {
    // ����QueryRunner���󴫵����ӳض���
    QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
    // ����SQLģ��(��,ɾ����)
    String sql = "insert into users values(?,?,?,?)";
    // ��������
    Object[] params = { "Litch", "1234", 22, "Boy" };
    // ����SQLģ��Ͳ���,ִ�в�ѯ���.���ر�Ӱ��IJ�����
    int u = qr.update(sql, params);
}

Example 44 with QueryRunner

use of org.apache.commons.dbutils.QueryRunner in project testcontainers-java by testcontainers.

the class JDBCDriverTest method performTestForScriptedSchema.

private void performTestForScriptedSchema(String jdbcUrl) throws SQLException {
    try (HikariDataSource dataSource = getDataSource(jdbcUrl, 1)) {
        boolean result = new QueryRunner(dataSource).query("SELECT foo FROM bar WHERE foo LIKE '%world'", rs -> {
            rs.next();
            String resultSetString = rs.getString(1);
            assertEquals("A basic SELECT query succeeds where the schema has been applied from a script", "hello world", resultSetString);
            return true;
        });
        assertTrue("The database returned a record as expected", result);
    }
}

Example 45 with QueryRunner

use of org.apache.commons.dbutils.QueryRunner in project testcontainers-java by testcontainers.

the class JDBCDriverWithPoolTest method testMySQLWithConnectionPoolUsingSameContainer.

@Test
public void testMySQLWithConnectionPoolUsingSameContainer() throws SQLException, InterruptedException {
    // Populate the database with some data in multiple threads, so that multiple connections from the pool will be used
    for (int i = 0; i < 100; i++) {
        executorService.submit(() -> {
            try {
                new QueryRunner(dataSource).insert("INSERT INTO my_counter (n) VALUES (5)", (ResultSetHandler<Object>) rs -> true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });
    }
    // Complete population of the database
    executorService.shutdown();
    executorService.awaitTermination(5, TimeUnit.MINUTES);
    // compare to expected results
    int count = new QueryRunner(dataSource).query("SELECT COUNT(1) FROM my_counter", rs -> {
        rs.next();
        return rs.getInt(1);
    });
    assertEquals("Reuse of a datasource points to the same DB container", 100, count);
    int sum = new QueryRunner(dataSource).query("SELECT SUM(n) FROM my_counter", rs -> {
        rs.next();
        return rs.getInt(1);
    });
    // 100 records * 5 = 500 expected
    assertEquals("Reuse of a datasource points to the same DB container", 500, sum);
}