java中游标

package YouBiao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class YouBiaoTest {
    /**
    * 一次只从数据库中查询最大maxCount条记录
    * @param sql 传入的sql语句
    * @param startNo 从哪一条记录开始
    * @param maxCount 总共取多少条记录
    */
    public void getData(String sql,int startNo,int maxCount){
    
     try {
         Connection conn = ConnectionUtil.getConnection();
    //  conn.prepareStatement(sql,游标类型,能否更新记录);
    //   游标类型:
//        ResultSet.TYPE_FORWORD_ONLY:只进游标
//        ResultSet.TYPE_SCROLL_INSENSITIVE:可滚动。但是不受其他用户对数据库更改的影响。
//        ResultSet.TYPE_SCROLL_SENSITIVE:可滚动。当其他用户更改数据库时这个记录也会改变。
    //   能否更新记录:
//        ResultSet.CONCUR_READ_ONLY,只读
//        ResultSet.CONCUR_UPDATABLE,可更新
      PreparedStatement pstat = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
      //最大查询到第几条记录
      pstat.setMaxRows(startNo+maxCount-1);
      ResultSet rs = pstat.executeQuery();
      //将游标移动到第一条记录
      rs.first();
    //  游标移动到要输出的第一条记录
      rs.relative(startNo-2);
      while(rs.next())
      System.out.println(rs.getInt(1));
     } catch (Exception e) {
      e.printStackTrace();
     }
    }
    /**
    * 从数据库中查询所有记录,然后通过游标来获取所需maxCount条记录
    * @param sql 传入的sql语句
    * @param startNo 从哪一条记录开始
    * @param maxCount 总共取多少条记录
    */
    public void getDataFromAll(String sql,int startNo,int maxCount){
    
     try {
      Connection conn = ConnectionUtil.getConnection();
      PreparedStatement pstat = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
      ResultSet rs = pstat.executeQuery();
      rs.first();
      rs.relative(startNo-1);
      int i = startNo-1;
      while(i < startNo + maxCount-1 && !rs.isAfterLast()){
      System.out.println(rs.getInt(1));
      i++;
      rs.next();
      }
     } catch (Exception e) {
      e.printStackTrace();
     }
    }
}


//==========工具类
package YouBiao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class ConnectionUtil {
    private static String driver;
    private static String uri;
    private static String user;
    private static String password;
 
    static {
        Properties props = new Properties();
        try {
            props.load(ConnectionUtil.class.getClassLoader()
                    .getResourceAsStream("properties文件路径"));
            driver = props.getProperty("driver");
            uri = props.getProperty("uri");
            user = props.getProperty("user");
            password = props.getProperty("password");
            Class.forName(driver);
        } catch (Exception e) {
        }
    }
 
    /**
     * 获得Connection对象
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        Connection con = DriverManager.getConnection(uri, user, password);
        return con;
    }
 
    /**
     * 关闭ResultSet对象
     * @param rs
     */
    public static void closeResultSet(ResultSet rs) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
            }
    }
 
    /**
     * 关闭Statement对象
     * @param stmt
     */
    public static void closeStatement(Statement stmt) {
        if (stmt != null)
            try {
                stmt.close();
            } catch (SQLException e) {
            }
    }
 
    /**
     * 关闭Connection对象
     * @param con
     */
    public static void closeConnection(Connection con) {
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
            }
    }


}

 

posted @ 2020-08-18 15:11  红尘沙漏  阅读(414)  评论(0编辑  收藏  举报