Example usage for org.apache.commons.dbcp PoolingConnection createStatement

Introduction

In this page you can find the example usage for org.apache.commons.dbcp PoolingConnection createStatement.

Prototype

public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException 

Source Link

Usage

From source file:org.opennms.provisiond.utils.CsvRequisitionParser.java

private static void migrateDbNodes() throws SQLException, UnknownHostException, ClassNotFoundException {

    String distinctNodesQueryStr = "  " + "SELECT nodeId AS \"nodeid\"," + "       nodeLabel AS \"nodelabel\","
            + "       foreignSource AS \"foreignsource\"," + "       foreignId AS \"foreignid\" "
            + "  FROM node " + " WHERE nodeid in (" + "  SELECT " + "DISTINCT nodeid " + "    FROM ipinterface "
            + "   WHERE iplike(ipaddr, '" + m_iplikeQuery + "')) " + "ORDER BY nodeid";

    if (m_addOnly) {
        distinctNodesQueryStr = "  " + "SELECT nodeId AS \"nodeid\"," + "       nodeLabel AS \"nodelabel\","
                + "       foreignSource AS \"foreignsource\"," + "       foreignId AS \"foreignid\" "
                + "  FROM node " + " WHERE nodeid in (" + "  SELECT " + "DISTINCT nodeid "
                + "    FROM ipinterface " + "   WHERE iplike(ipaddr, '" + m_iplikeQuery + "')) "
                + "  AND foreignsource is NULL " + "ORDER BY nodeid";
    }/*  w  ww  .  j  a va  2 s  .c  o  m*/

    Connection connection = null;
    Statement distinctNodesStatement = null;
    PoolingConnection pool = null;
    connection = createConnection();
    connection.setAutoCommit(false);
    pool = new PoolingConnection(connection);
    distinctNodesStatement = pool.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

    ResultSet distinctNodesResultSet = null;
    int rowsFound = 0;
    distinctNodesResultSet = distinctNodesStatement.executeQuery(distinctNodesQueryStr);
    distinctNodesResultSet.last();
    rowsFound = distinctNodesResultSet.getRow();
    distinctNodesResultSet.beforeFirst();

    System.out.println(rowsFound + " nodes found.");

    int nodesMigrated = 0;
    while (distinctNodesResultSet.next()) {
        System.out.println("Processing row: " + distinctNodesResultSet.getRow() + "...");

        int nodeId = distinctNodesResultSet.getInt("nodeid");
        String queryStr = "" + "  SELECT ipaddr " + "    FROM ipinterface " + "   WHERE nodeid = " + nodeId
                + " " + "     AND issnmpprimary = 'P' " + "ORDER BY inet(ipaddr)" + "   LIMIT 1";

        Statement findPrimaryStatement = pool.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        System.out.println("Querying DB for SNMP Primary interface for node: " + nodeId + "...");
        ResultSet findPrimaryResultSet = findPrimaryStatement.executeQuery(queryStr);

        String primaryIp = null;

        if (findPrimaryResultSet.next()) {
            primaryIp = findPrimaryResultSet.getString("ipaddr");
            System.out.println("SNMP Primary found: " + primaryIp);
        }

        findPrimaryResultSet.close();
        findPrimaryStatement.close();

        if (primaryIp == null) {
            System.out.println("SNMP Primary not found.  Determining lowest numbered IP to set as Primary...");
            queryStr = "" + "  SELECT ipaddr " + "    FROM ipinterface " + "   WHERE nodeid = " + nodeId + " "
                    + "ORDER BY inet(ipaddr)" + "   LIMIT 1";
            findPrimaryStatement = pool.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            findPrimaryResultSet = findPrimaryStatement.executeQuery(queryStr);
        }

        if (primaryIp == null && findPrimaryResultSet.next()) {
            primaryIp = findPrimaryResultSet.getString("ipaddr");
            System.out.println("SNMP Primary found: " + primaryIp);
        }

        findPrimaryResultSet.close();
        findPrimaryStatement.close();

        if (primaryIp == null) {
            System.out.println(
                    "SNMP Primary not found.  Skipping node.  (This should never happen since it is the iplike query that finds the distinct nodes :( )");
            continue;
        }

        String foreignId = null;
        if (m_useNodeId) {
            foreignId = String.valueOf(nodeId);
        } else {
            foreignId = String.valueOf(System.currentTimeMillis());
        }

        String label = distinctNodesResultSet.getString("nodelabel");
        distinctNodesResultSet.updateString("foreignsource", m_foreignSource);
        distinctNodesResultSet.updateString("foreignId", foreignId);

        System.out.println("Updating node (" + nodeId + ":" + label + ") with foreignsource:" + m_foreignSource
                + " and foreignId:" + foreignId);
        distinctNodesResultSet.updateRow();
        System.out.println("Node updated.");

        RequisitionData rd = new RequisitionData(label, primaryIp, m_foreignSource, foreignId);

        if (m_categoryAddExisting) {
            String categoriesQueryString = "" + "SELECT c.categoryname as \"categoryname\" "
                    + "  FROM categories c " + "  JOIN category_node cn "
                    + "    ON cn.categoryid = c.categoryid " + "  JOIN node n on n.nodeid = cn.nodeid "
                    + " WHERE n.nodeid = " + nodeId;
            Statement categoriesStatement = pool.createStatement();

            ResultSet crs = categoriesStatement.executeQuery(categoriesQueryString);

            Set<String> categories = new LinkedHashSet<String>();
            while (crs.next()) {
                categories.add(crs.getString("categoryname"));
            }

            crs.close();
            categoriesStatement.close();
            rd.setCategories(categories);
        }

        System.out.println("Updating requistion...");
        createOrUpdateRequistion(rd);
        System.out.println("Requistion updated!  Next...\n");
        nodesMigrated++;
    }

    try {
        connection.commit();
    } catch (SQLException e) {
        e.printStackTrace();
        connection.rollback();
    }

    distinctNodesResultSet.close();
    distinctNodesStatement.close();
    pool.close();
    connection.close();

    System.out.println(nodesMigrated + " Nodes migrated to foreign source " + m_foreignSource);

}

---