JDBC分页查询

JDBC查询步骤：

1. 测试是否导入JDBC包
2. 创建Connection，连接数据库
3. 声明用于编写sql的字符串
4. 创建用于执行sql的Statement
5. 若有返回结果，创建用于存储结果的ResultSet
6. 用next()方法遍历ResultSet
7. 为要读取的每个字段声明变量，并getString(序号|字段名)

分页查询要点

1. 分页查询方法传入Statement，不用再创建一遍连接
2. 查询从第x条开始的n条数据
   第1-5条：SELECT * FROM software LIMIT 0,5

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestJDBC {
    //传入Statement 不用再次创建链接Connection和Statement
    public static void list(Statement s,int start, int count){
        System.out.println("当前查询第"+(start+10)/count+"页");
        String sql = String.format("select * from software limit %d,%d",start,count);
        try{
            ResultSet rs = s.executeQuery(sql);
            while(rs.next()){
                String SID = rs.getString("SID");
                String HID = rs.getString("HID");
                String SNAME = rs.getString("SNAME");
                String TYPE = rs.getString("TYPE");
                String ABSTRACT = rs.getString("ABSTRACT");
                //printf可以带参数 println不能 只能SID+" "+HID
                System.out.printf("%s\t%s\t%s\t%s\t%s\t\n",SID,HID,SNAME,TYPE,ABSTRACT);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        //是否导入JDBC包
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/cluster?characterEncoding=UTF-8",
                "root", "123456"); Statement s = c.createStatement();) {

            String sql = "select count(*) from software";

            //总共数据条数
            ResultSet rs = s.executeQuery(sql);
            int total = 0;
            while (rs.next()) {
                total = rs.getInt(1);
            }
            System.out.println("表hardware中总共有:" + total+" 条数据");

            //分页查询 10条一页
            int pages = (total+9)/10;
            String sql2 = "select * from software";
            for(int i=0;i<pages;++i){
                list(s,i*10,10);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
}

运行结果：