使用存储过程实现分页查询
存储过程
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();
}
}
}