Example usage for java.sql ResultSet relative

List of usage examples for java.sql ResultSet relative

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. 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;
}