Example usage for java.sql ResultSet relative

List of usage examples for java.sql ResultSet relative

Introduction

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

Prototype

boolean relative(int rows) throws SQLException;

Source Link

Document

Moves the cursor a relative number of rows, either positive or negative.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st.executeUpdate("insert into survey (id,name ) values (3,'Tom')");
    ResultSet rs = st.executeQuery("SELECT * FROM survey");

    rs.last();//  ww  w. j  av  a  2 s .  co  m
    // Move cursor up 2 rows from the current row. If this moves
    // cursor beyond the first row, cursor is put before the first row
    rs.relative(-2);

    // Get data at cursor
    String id = rs.getString("id");
    System.out.println(id);

    rs.close();
    st.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/testdb", "root", "");

    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = statement.executeQuery("SELECT * FROM products");

    // Move to the second row
    resultSet.absolute(2);/*ww  w  .  j a va 2 s.com*/
    System.out.println("You are now in: " + resultSet.getRow());

    // Move 2 records forward from the current position (fourth row)
    resultSet.relative(2);
    System.out.println("You are now in: " + resultSet.getRow());

    // Move to the last row in the result set
    resultSet.absolute(-1);
    System.out.println("You are now in: " + resultSet.getRow());

    // Move 3 records backward from the current position (second row)
    resultSet.relative(-3);
    System.out.println("You are now in: " + resultSet.getRow());
    connection.close();
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);//  w  w  w . j a v a  2 s  .  c  o m

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);
    // Create a scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

    // Move cursor forward
    while (resultSet.next()) {
        // Get data at cursor
        String s = resultSet.getString(1);
    }

    // Move cursor backward
    while (resultSet.previous()) {
        // Get data at cursor
        String s = resultSet.getString(1);
    }

    // Move cursor down 5 rows from the current row. If this moves cursor beyond the last row, cursor is put after the last row
    resultSet.relative(5);

}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);//from ww  w .java2 s. co m

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);
    // Create a scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");

    // Move cursor forward
    while (resultSet.next()) {
        // Get data at cursor
        String s = resultSet.getString(1);
    }

    // Move cursor backward
    while (resultSet.previous()) {
        // Get data at cursor
        String s = resultSet.getString(1);
    }

    // Move cursor up 3 rows from the current row. If this moves cursor beyond the first row, cursor is put before the first row
    resultSet.relative(-3);

}

From source file:ScrollableRs.java

public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
    String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:ORCL";
    Connection conn = DriverManager.getConnection(jdbcUrl, "yourName", "mypwd");
    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = stmt.executeQuery("SELECT ssn, name, salary FROM EMPLOYEES");
    while (rs.next()) {
        printRow(rs);/*from w  w w  .j a  va 2 s  . c o  m*/
    }
    rs.afterLast();
    System.out.println("\"After-last-row\" = " + rs.isAfterLast());
    rs.beforeFirst();
    System.out.println("\"Before-first-row\" = " + rs.isBeforeFirst());
    rs.first();
    printRow(rs);
    rs.last();
    printRow(rs);
    rs.previous();
    printRow(rs);

    rs.next();
    printRow(rs);

    rs.absolute(3);
    printRow(rs);

    rs.relative(-2);
    printRow(rs);
    if (conn != null)
        conn.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    try {/*www  .ja  va 2s . c  o m*/
        String url = "jdbc:odbc:yourdatabasename";
        String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        String user = "guest";
        String password = "guest";

        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);

        Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        String sqlQuery = "SELECT EMPNO, EName, Job, MGR, HIREDATE FROM EMP";

        ResultSet rs = stmt.executeQuery(sqlQuery);

        int rowSize = 0;
        while (rs.next()) {
            rowSize++;
        }

        System.out.println("Number of Rows in ResultSet is: " + rowSize);
        if (rowSize == 0) {
            System.out.println("Since there are no rows, exiting...");
            System.exit(0);
        }

        int cursorPosition = Math.round(rowSize / 2);

        System.out.println("Moving to position: " + cursorPosition);
        rs.absolute(cursorPosition);
        System.out.println("Name: " + rs.getString(2));

        rs.relative(-1);

        cursorPosition = rs.getRow();
        System.out.println("Moving to position: " + cursorPosition);
        System.out.println("Name: " + rs.getString(2));

        System.out.println("Moving to the first row");
        while (!rs.isFirst()) {
            rs.previous();
        }
        System.out.println("Name: " + rs.getString(2));
        connection.close();
    } catch (Exception e) {
        System.err.println(e);
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st.executeUpdate("insert into survey (id,name ) values (3,'Tom')");
    ResultSet rs = st.executeQuery("SELECT * FROM survey");

    // Move cursor forward
    while (rs.next()) {
        // Get data at cursor
        String id = rs.getString(1);
        String name = rs.getString(2);
    }/*  w w  w  .j  a  v a2  s.co m*/

    // Move cursor backward
    while (rs.previous()) {
        // Get data at cursor
        String id = rs.getString(1);
        String name = rs.getString(2);
    }

    // Move cursor to the first row
    rs.first();

    // Move cursor to the last row
    rs.last();

    // Move cursor to the end, after the last row
    rs.afterLast();

    // Move cursor to the beginning, before the first row.
    // cursor position is 0.
    rs.beforeFirst();

    // Move cursor to the second row
    rs.absolute(2);

    // Move cursor to the last row
    rs.absolute(-1);

    // Move cursor to the second-to-last row
    rs.absolute(-2);

    // Move cursor down 5 rows from the current row. If this moves
    // cursor beyond the last row, cursor is put after the last row
    rs.relative(5);

    // Move cursor up 3 rows from the current row. If this moves
    // cursor beyond the first row, cursor is put before the first row
    rs.relative(-3);

    rs.close();
    st.close();
    conn.close();

}

From source file:it.cnr.icar.eric.server.persistence.rdb.AbstractDAO.java

/**
 * Gets a List of binding objects from specified ResultSet.
 *///from  w w w . j  ava2 s .c o m
public List<Object> getObjects(ResultSet rs, int startIndex, int maxResults) throws RegistryException {
    List<Object> res = new ArrayList<Object>();

    try {
        if (startIndex > 0) {
            // calling rs.next() is a workaround for some drivers, such
            // as Derby's, that do not set the cursor during call to 
            // rs.relative(...)
            rs.next();
            @SuppressWarnings("unused")
            boolean onRow = rs.relative(startIndex - 1);
        }

        int cnt = 0;
        while (rs.next()) {
            Object obj = createObject();
            loadObject(obj, rs);
            res.add(obj);

            if (++cnt == maxResults) {
                break;
            }
        }
    } catch (SQLException e) {
        log.error(ServerResourceBundle.getInstance().getString("message.CaughtException1"), e);
        throw new RegistryException(e);
    } catch (JAXBException j) {
        log.error(ServerResourceBundle.getInstance().getString("message.CaughtException1"), j);
        throw new RegistryException(j);
    }

    return res;
}

From source file:net.xqx.controller.web.QyzzController.java

/**
 * ?// www  . java 2 s  .  c  om
 * 
 * @return
 */
@RequestMapping("/fdckfqy")
public String fdckfqy(HttpServletRequest request) {
    PageDao pageDao = new PageDao();
    String pageCount = request.getParameter("pageCount");// ???
    if (pageCount == null || "".equals(pageCount)) {
        pageCount = "1";
    }
    int totalRow = 0;// ?
    String total = request.getParameter("totalRow");
    int totalrow = 0;
    if (null != total && !"".equals(total)) {
        totalrow = Integer.parseInt(total);
    }

    Page page = null;
    String companyName = request.getParameter("companyName");
    if (companyName != null && !"".equals(companyName)) {
        companyName = companyName.trim();
    }
    String certificateLevel = request.getParameter("certificateLevel");
    request.setAttribute("companyName", companyName);
    request.setAttribute("certificateLevel", certificateLevel);

    Connection conn = pageDao.getAptitudeConnection();
    PreparedStatement statement = null;
    ResultSet rs = null;
    String hql = "";
    try {

        if (companyName != null && !"".equals(companyName)
                && (certificateLevel == null || "".equals(certificateLevel))) {
            hql = "select count(*) from TCompanyInfo c left join TCertificate cer "
                    + "on c.fCompanyId=cer.fCompanyId left join TRegInfo r "
                    + "on c.fCompanyId=r.fCompanyInfoId where c.fChecked=1 and c.fCompanyName like " + "'" + "%"
                    + companyName + "%" + "'";
            String sql = "select c.fCompanyName,c.fCompanyType,c.fRightMan,r.fCapital,r.fPaiclUpCapital,r.fRegLicenseNo,"
                    + "cer.fCertificateLevel,cer.fCertificateNo,c.fOperatingDate,cer.fCertifyDate,"
                    + "cer.fValidBeginDate,cer.fValidEndDate,c.fAddress,c.fDetails,c.fCompanyId from TCompanyInfo c left join TCertificate cer "
                    + "on c.fCompanyId=cer.fCompanyId left join TRegInfo r "
                    + "on c.fCompanyId=r.fCompanyInfoId where c.fChecked=1 and c.fCompanyName like ? order by c.fCompanyId desc";
            totalRow = pageDao.getAmount(hql);// select count ?
            if (totalRow != totalrow) {
                pageCount = "1";
            }
            page = new Page(totalRow, pageCount, 11);
            statement = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setMaxRows(page.getEndIndex());
            statement.setString(1, "%" + companyName + "%");
            rs = statement.executeQuery();
            rs.first();
            rs.relative(page.getBeginIndex() - 1);
            request.setAttribute("totalPage", page.getTotal());// ?
            request.setAttribute("pageCount", page.getCount());// ??
            request.setAttribute("companyName", companyName);// ??
        } else if (companyName != null && !"".equals(companyName) && certificateLevel != null
                && !"".equals(certificateLevel)) {
            hql = "select count(*) from TCompanyInfo c left join TCertificate cer "
                    + "on c.fCompanyId=cer.fCompanyId left join TRegInfo r "
                    + "on c.fCompanyId=r.fCompanyInfoId where c.fChecked=1 and c.fCompanyName like " + "'" + "%"
                    + companyName + "%" + "'" + " and cer.fCertificateLevel=" + "'" + certificateLevel + "'";

            String sql = "select c.fCompanyName,c.fCompanyType,c.fRightMan,r.fCapital,r.fPaiclUpCapital,r.fRegLicenseNo,"
                    + "cer.fCertificateLevel,cer.fCertificateNo,c.fOperatingDate,cer.fCertifyDate,"
                    + "cer.fValidBeginDate,cer.fValidEndDate,c.fAddress,c.fDetails,c.fCompanyId from TCompanyInfo c left join TCertificate cer "
                    + "on c.fCompanyId=cer.fCompanyId left join TRegInfo r "
                    + "on c.fCompanyId=r.fCompanyInfoId where c.fChecked=1 and c.fCompanyName like ? and cer.fCertificateLevel=? order by c.fCompanyId desc";
            totalRow = pageDao.getAmount(hql);// select count ?
            if (totalRow != totalrow) {
                pageCount = "1";
            }
            page = new Page(totalRow, pageCount, 11);
            statement = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            statement.setMaxRows(page.getEndIndex());
            statement.setString(1, "%" + companyName + "%");
            statement.setString(2, certificateLevel);
            rs = statement.executeQuery();
            rs.first();
            rs.relative(page.getBeginIndex() - 1);
            request.setAttribute("totalPage", page.getTotal());// ?
            request.setAttribute("pageCount", page.getCount());// ??
            request.setAttribute("companyName", companyName);// ??
            request.setAttribute("certificateLevel", certificateLevel);// ??
        }

        TCompanyInfo companyInfo = null;
        List<TCompanyInfo> companyInfos = new ArrayList<TCompanyInfo>();
        if (rs != null) {
            while (rs.next()) {
                companyInfo = new TCompanyInfo();
                companyInfo.setfCompanyName(rs.getString(1));
                companyInfo.setfCompanyType(rs.getString(2));
                companyInfo.setfRightMan(rs.getString(3));
                companyInfo.setfCapitals(rs.getString(4));
                companyInfo.setfCapitalsUp(rs.getString(5));
                companyInfo.setfLicenseNo(rs.getString(6));
                companyInfo.setfCertificateLevel(rs.getString(7));
                companyInfo.setfCertificateNo(rs.getString(8));
                companyInfo.setfOperatingDate(rs.getString(9));
                companyInfo.setfCertificateDate(rs.getString(10));
                companyInfo.setfCertificateBeginDate(rs.getString(11));
                companyInfo.setfCertificateEndDate(rs.getString(12));
                companyInfo.setfRegAddress(rs.getString(13));
                companyInfo.setfDetails(rs.getString(14));
                companyInfo.setfCompanyId(rs.getInt(15));
                companyInfos.add(companyInfo);
            }
        }
        request.setAttribute("companyInfos", companyInfos);
        // rs.close();
        // statement.close();
        // conn.close();

    } catch (SQLException e) {
        System.out.println("!");
        e.printStackTrace();
        return "web/qycx";
    } finally {
        pageDao.closeConnection(rs, statement, conn);

    }

    // 
    Sort hotNewsSort = new Sort(Direction.DESC, "fdjTimes", "ffbTime");
    Pageable hotNewsRecPageable = new PageRequest(0, 8, hotNewsSort);
    List<TNews> hotNewsList = newsDao.getHotNews(hotNewsRecPageable).getContent();
    request.setAttribute("hotNewsList", hotNewsList);

    // ??
    Sort recNewsSort = new Sort(Direction.DESC, "fIsRecord", "ffbTime");
    Pageable recNewsRecPageable = new PageRequest(0, 8, recNewsSort);
    List<TNews> recNewsList = newsDao.getNewsRec(recNewsRecPageable).getContent();
    request.setAttribute("recNewsList", recNewsList);
    request.setAttribute("totalRow", totalRow);
    return "web/qycx";
}

From source file:com.enonic.vertical.engine.handlers.SectionHandler.java

public XMLDocument getContentTitlesBySection(int sectionKey, String orderBy, int fromIndex, int count,
        boolean includeTotalCount, boolean approvedOnly) {
    ContentView contentView = ContentView.getInstance();
    StringBuffer sql = XDG.generateSelectSQL(this.db.tSectionContent2,
            new Column[] { this.db.tSectionContent2.sco_con_lKey, this.db.tSectionContent2.sco_bApproved },
            false, null);/* w w w  . ja v a2  s.  com*/
    sql.append(" LEFT JOIN ").append(ContentMinimalView.getInstance().getReplacementSql()).append(" ON ");
    sql.append(this.db.tSectionContent2.sco_con_lKey.getName()).append(" = ");
    sql.append(contentView.con_lKey.getName());
    sql = XDG.generateWhereSQL(sql, new Column[] { this.db.tSectionContent2.sco_mei_lKey });

    if (approvedOnly) {
        sql.append(" AND ");
        sql.append(this.db.tSectionContent2.sco_bApproved.getName());
        sql.append(" = 1");
    }

    String orderDirection = " ASC ";
    if (orderBy == null) {
        orderBy = contentView.cov_dteTimestamp.getName();
        orderDirection = " DESC ";
    }

    sql.append(" ORDER BY ");
    if (!approvedOnly) {
        sql.append(this.db.tSectionContent2.sco_bApproved.getName());
        sql.append(", ");
    }

    if (isSectionOrdered(sectionKey)) {
        sql.append(this.db.tSectionContent2.sco_lOrder.getName());
        sql.append(" ASC, ");
    }

    sql.append(orderBy);
    sql.append(orderDirection);

    Connection con = null;
    PreparedStatement prepStmt = null;
    ResultSet resultSet = null;
    TIntArrayList contentKeys;

    int totalCount = 0;
    if (count > 20) {
        contentKeys = new TIntArrayList();
    } else {
        contentKeys = new TIntArrayList();
    }

    HashMap<String, String> contentApprovedMap = new HashMap<String, String>();

    try {
        con = getConnection();
        prepStmt = con.prepareStatement(sql.toString());
        prepStmt.setInt(1, sectionKey);
        resultSet = prepStmt.executeQuery();

        boolean moreResults = resultSet.next();
        int i = fromIndex;

        // Skip rows:
        try {
            if (fromIndex > 0) {
                resultSet.relative(fromIndex);
            }
        } catch (SQLException e) {
            // ResultSet is not scrollable
            i = 0;
        }

        totalCount = fromIndex;
        for (; ((includeTotalCount || i < fromIndex + count) && moreResults); i++) {
            if (i < fromIndex) {
                moreResults = resultSet.next();
                continue;
            }

            if (i < fromIndex + count) {
                int contentKey = resultSet.getInt(1);
                boolean approved = resultSet.getBoolean(2);
                contentKeys.add(contentKey);
                contentApprovedMap.put(Integer.toString(contentKey), Boolean.toString(approved));
            }

            totalCount++;
            moreResults = resultSet.next();
        }
    } catch (SQLException sqle) {
        String message = "Failed to get content keys for content in sections: %t";
        VerticalEngineLogger.error(this.getClass(), 0, message, sqle);
    } finally {
        close(resultSet);
        close(prepStmt);
        close(con);
    }

    if (contentKeys.size() == 0) {
        org.jdom.Element contentsEl = new org.jdom.Element("contenttitles");
        if (includeTotalCount) {
            contentsEl.setAttribute("totalcount", "0");
        }
        return XMLDocumentFactory.create(new org.jdom.Document(contentsEl));
    }

    ContentHandler contentHandler = getContentHandler();
    MenuItemEntity section = menuItemDao.findByKey(sectionKey);
    XMLDocument doc = contentHandler.getContentTitles(contentKeys.toArray(), true, section);

    if (includeTotalCount) {
        org.jdom.Document jdomDoc = doc.getAsJDOMDocument();
        jdomDoc.getRootElement().setAttribute("totalcount", Integer.toString(totalCount));
    }

    return doc;
}