使用存储过程实现分页查询

存储过程

create procedure page(in num1 int,in num2 int)
begin
 select id,name,email,birth from customers limit num1,num2;
end;

Customer类

package bean;

import java.sql.Date;

public class Customer {
	private Integer id;
	private String name;
	private String email;
	private Date birth;

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Date getBirth() {
		return birth;
	}

	public void setDate(Date birth) {
		this.birth = birth;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public Integer getId() {
		return id;
	}

	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
	}

	public Customer() {
	}

	public Customer(Integer id, String name, String email, Date birth) {
		super();
		this.id = id;
		this.name = name;
		this.email = email;
		this.birth = birth;
	}

}

Test 类

package test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import Utils.JDBCUtils;
import bean.Customer;

public class Test1 {

	public static void main(String[] args) {
		Connection conn = JDBCUtils.getConnection();
		int pageNum = 5;
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入需要查询第几页（每页5位用户）");
		int start = 0;
		int num = sc.nextInt();
		if (1 == num) {

		} else {
			start = num * 5 - 6;
		}
		try {
			CallableStatement cs = conn.prepareCall("{call page(?,?)}");
			cs.setInt(1, start);
			cs.setInt(2, pageNum);
			cs.execute();
			ResultSet rs = cs.getResultSet();
			List<Customer> list = new ArrayList();
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Date birth = rs.getDate("birth");

				Customer cust = new Customer(id, name, email, birth);
				list.add(cust);

			}
			list.forEach(System.out::println);

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}