JDBC查询步骤:
- 测试是否导入JDBC包
- 创建Connection,连接数据库
- 声明用于编写sql的字符串
- 创建用于执行sql的Statement
- 若有返回结果,创建用于存储结果的ResultSet
- 用next()方法遍历ResultSet
- 为要读取的每个字段声明变量,并getString(序号|字段名)
分页查询要点
- 分页查询方法传入Statement,不用再创建一遍连接
- 查询从第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();
}
}
}
运行结果: