Example usage for java.sql ResultSet beforeFirst

List of usage examples for java.sql ResultSet beforeFirst

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. beforeFirst

Introduction

In this page you can find the example usage for java.sql ResultSet beforeFirst.

Prototype

void beforeFirst() throws SQLException;

Source Link

Document

Moves the cursor to the front of this ResultSet object, just before the first row.

Usage

From source file:org.opennms.model.utils.AssetsUpdater.java

protected static void parseCsv2(final File csv) throws ClassNotFoundException, SQLException, IOException {

    String sql = m_dbQuery;//from  w ww.j  a  va2s  .  c om

    Connection con = createConnection(false);
    PreparedStatement ps = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_UPDATABLE);

    BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(csv)));
    CSVReader csvReader = new CSVReader(br);
    String[] line;
    int lineCnt = 0;
    while ((line = csvReader.readNext()) != null) {

        System.out.println("Processing csv line: " + String.valueOf(++lineCnt));

        if (line.length != m_fieldMap.size() + 1) {
            continue;
        }

        String foreignSource = m_formatter.formatForeignSource(StringUtils.isBlank(line[0]) ? null : line[0]);

        System.out.println("Running query for foreignSource: " + foreignSource + " ...");
        ps.setString(1, foreignSource);
        ResultSet rs = ps.executeQuery();
        rs.last();
        int rows = rs.getRow();
        if (rows < 1) {
            rs.close();
            System.out.println("No results found for foreignsource: " + foreignSource
                    + "; continuing to next foreignsource...");
            continue;
        }
        System.out.println("Found " + rows + " rows.");

        rs.beforeFirst();

        while (rs.next()) {
            System.out.println("Updating node: " + rs.getInt("nodeid"));

            Set<Entry<Integer, String>> entrySet = m_fieldMap.entrySet();
            for (Entry<Integer, String> entry : entrySet) {
                int csvField = entry.getKey() - 1;
                String columnName = entry.getValue();
                System.out.println(
                        "\t" + "updating column: " + columnName + " with csv field: " + line[csvField]);
                rs.updateString(columnName, line[csvField]);
            }

            rs.updateRow();
        }
        rs.close();
    }

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

    csvReader.close();
    ps.close();
    con.close();
}

From source file:recite18th.library.Db.java

public static ArrayList processDataSetResultSetAsArrayList(ResultSet resultSet, String fqnModel) {
    ArrayList result = new ArrayList();

    try {/*from   w  w  w . j  a va 2  s .c o  m*/
        ResultSetMetaData metaData;
        int nColoumn;
        String columnName;
        String fieldValue;
        Field field;
        Object modelInstance;

        metaData = resultSet.getMetaData();
        nColoumn = metaData.getColumnCount();
        resultSet.beforeFirst();
        Class modelClass = Class.forName(fqnModel);

        while (resultSet.next()) {
            modelInstance = modelClass.newInstance();
            for (int i = 1; i <= nColoumn; i++) {
                columnName = metaData.getColumnName(i);
                fieldValue = resultSet.getString(i);
                PropertyUtils.setSimpleProperty(modelInstance, columnName, fieldValue);
                //the good ol'ways.. don't use BeanUtils... The problem is, how can it able to get the 
                //field from super class??
                //field = modelInstance.getClass().getDeclaredField(columnName);                    
                //field.set(modelInstance, fieldValue);

            }
            result.add(modelInstance);
        }
    } catch (Exception ex) {
        Logger.getLogger(Db.class.getName()).log(Level.SEVERE, null, ex);
    }
    return result;
}

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 w w .j  ava2  s .  com*/

    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);

}

From source file:recite18th.library.Db.java

public static String[][] processDataSetResultSet(ResultSet resultSet) {
    try {/*ww w.j  a v a 2 s  .c o  m*/
        ResultSetMetaData metaData;
        int indexOfRow = 0; // index dimulai dari nol.
        int nColoumn;
        int nRow;
        String[][] result;

        metaData = resultSet.getMetaData();
        nColoumn = metaData.getColumnCount();

        resultSet.last(); // menuju paling baris terakhir
        nRow = resultSet.getRow();
        result = new String[nRow][];
        resultSet.beforeFirst();
        while (resultSet.next()) {
            // disini skalian langsung ke baris berikutnya.
            result[indexOfRow] = new String[nColoumn];
            for (int i = 0; i < nColoumn; i++) {
                ////LoggingWindow.addToLog(nColoumn);
                result[indexOfRow][i] = resultSet.getString(i + 1);

            }
            indexOfRow++;
        }
        return result;
    } catch (SQLException ex) {
        Logger.getLogger(Db.class.getName()).log(Level.SEVERE, null, ex);
        return null;
    }
}

From source file:com.autentia.tnt.bill.migration.support.OriginalInformationRecoverer.java

/**
 * Recupera la fecha de pago o cobro de cada una de las facturas cuyo tipo se envia por parametro
 * @param billType tipo de factura/*from  ww w . j a  v a  2 s .co m*/
 */
public static Date[] getFechaFacturaOriginal(String billType) throws Exception {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    LineNumberReader file = null;
    Date[] result = new Date[0];

    try {
        log.info("RECOVERING FECHAS " + billType + " ORIGINALES");

        // connect to database
        Class.forName(BillToBillPaymentMigration.DATABASE_DRIVER);
        con = DriverManager.getConnection(BillToBillPaymentMigration.DATABASE_CONNECTION,
                BillToBillPaymentMigration.DATABASE_USER, BillToBillPaymentMigration.DATABASE_PASS); //NOSONAR
        con.setAutoCommit(false); // DATABASE_PASS vacia
        String sql = "SELECT date_add(creationDate, INTERVAL expiration DAY) as date FROM Bill B where billType = ? order by date";
        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, billType);
        rs = pstmt.executeQuery();

        rs.last();
        result = new Date[rs.getRow()];
        rs.beforeFirst();
        int counter = 0;

        while (rs.next()) {
            result[counter] = rs.getDate(1);
            log.info("\t" + result[counter]);
            counter++;
        }
        con.commit();
    } catch (Exception e) {
        log.error("FAILED: WILL BE ROLLED BACK: ", e);
        if (con != null) {
            con.rollback();
        }
    } finally {
        cierraFichero(file);
        liberaConexion(con, pstmt, rs);
    }
    return result;
}

From source file:com.autentia.tnt.bill.migration.support.MigratedInformationRecoverer.java

/**
 * Recupera la fecha de pago o cobro de cada una de las facturas cuyo tipo se envia por parametro
 * @param billType tipo de factura// w  ww .j  av a  2 s . com
 */
public static Date[] getFechaFacturaMigrated(String billType) throws Exception {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    LineNumberReader file = null;
    Date[] result = new Date[0];

    try {
        log.info("RECOVERING FECHAS " + billType + " MIGRADAS");

        // connect to database
        Class.forName(BillToBillPaymentMigration.DATABASE_DRIVER);
        con = DriverManager.getConnection(BillToBillPaymentMigration.DATABASE_CONNECTION,
                BillToBillPaymentMigration.DATABASE_USER, BillToBillPaymentMigration.DATABASE_PASS); //NOSONAR
        con.setAutoCommit(false); // DATABASE_PASS vacio.

        String sql = "SELECT bp.expirationDate FROM BillPayment bp, Bill b where bp.billId = b.id and b.billType = ? order by bp.expirationDate";
        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, billType);

        rs = pstmt.executeQuery();

        rs.last();
        result = new Date[rs.getRow()];
        rs.beforeFirst();
        int counter = 0;

        while (rs.next()) {
            result[counter] = rs.getDate(1);
            log.info("\t" + result[counter]);
            counter++;
        }

    } catch (Exception e) {
        log.error("FAILED: WILL BE ROLLED BACK: ", e);
        if (con != null) {
            con.rollback();
        }

    } finally {
        cierraFichero(file);
        liberaConexion(con, pstmt, rs);
    }
    return result;
}

From source file:com.autentia.tnt.bill.migration.support.MigratedInformationRecoverer.java

/**
 * Recupera la suma total de todos los conceptos de cada una de las facturas cuyo tipo se envia por parametro
 * @param billType tipo de factura//ww  w .  j  a  v a 2  s .c o  m
 */
public static double[] getImporteFacturaMigrated(String billType) throws Exception {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    LineNumberReader file = null;
    double[] result = new double[0];

    try {
        log.info("RECOVERING IMPORTE FACTURAS " + billType + " MIGRADAS");

        // connect to database
        Class.forName(BillToBillPaymentMigration.DATABASE_DRIVER);
        con = DriverManager.getConnection(BillToBillPaymentMigration.DATABASE_CONNECTION,
                BillToBillPaymentMigration.DATABASE_USER, BillToBillPaymentMigration.DATABASE_PASS); // NOSONAR
        con.setAutoCommit(false); // DATABASE_PASS vacio         

        String sql = "SELECT bp.amount FROM BillPayment bp, Bill b where bp.billId = b.id and b.billType = ? order by amount";

        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, billType);
        rs = pstmt.executeQuery();

        rs.last();
        result = new double[rs.getRow()];
        rs.beforeFirst();
        int counter = 0;

        while (rs.next()) {
            result[counter] = rs.getDouble(1);
            log.info("\t" + result[counter]);
            counter++;
        }

    } catch (Exception e) {
        log.error("FAILED: WILL BE ROLLED BACK: ", e);
        if (con != null) {
            con.rollback();
        }

    } finally {
        cierraFichero(file);
        liberaConexion(con, pstmt, rs);
    }
    return result;
}

From source file:genepi.db.JdbcDataAccessObject.java

public int insert(String sql, Object[] params) throws SQLException {

    Connection connection = database.getDataSource().getConnection();

    try {//from   ww  w.ja  v  a 2  s  .  co m
        PreparedStatement statement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);

        runner.fillStatement(statement, params);

        statement.executeUpdate();

        ResultSet rs = statement.getGeneratedKeys();
        rs.beforeFirst();
        rs.next();
        int id = rs.getInt(1);
        connection.close();
        return id;
    } catch (Exception e) {
        throw e;
    } finally {
        connection.close();
    }
}

From source file:de.static_interface.reallifeplugin.module.contract.database.table.ContractUsersTable.java

@Override
public ContractUserRow[] deserialize(ResultSet rs) throws SQLException {
    int rowcount = 0;
    if (rs.last()) {
        rowcount = rs.getRow();//from   ww w .  j  a va  2s  .c o m
        rs.beforeFirst();
    }

    ContractUserRow[] rows = new ContractUserRow[rowcount];
    int i = 0;

    while (rs.next()) {
        ContractUserRow row = new ContractUserRow();
        if (hasColumn(rs, "id")) {
            row.id = rs.getInt("id");
        }
        if (hasColumn(rs, "uuid")) {
            row.uuid = UUID.fromString(rs.getString("uuid"));
        }
        rows[i] = row;
        i++;
    }
    return rows;
}

From source file:de.static_interface.reallifeplugin.module.stockmarket.database.table.StockUsersTable.java

@Override
public StockUserRow[] deserialize(ResultSet rs) throws SQLException {
    int rowcount = 0;
    if (rs.last()) {
        rowcount = rs.getRow();//from w  w w .  j av a 2s. co m
        rs.beforeFirst();
    }

    StockUserRow[] rows = new StockUserRow[rowcount];
    int i = 0;

    while (rs.next()) {
        StockUserRow row = new StockUserRow();
        if (hasColumn(rs, "id")) {
            row.id = rs.getInt("id");
        }
        if (hasColumn(rs, "amount")) {
            row.amount = rs.getInt("amount");
        }
        if (hasColumn(rs, "stock_id")) {
            row.stockId = rs.getInt("stock_id");
        }
        if (hasColumn(rs, "user_id")) {
            row.userId = rs.getInt("user_id");
        }
        rows[i] = row;
        i++;
    }
    return rows;
}