mysql resultset用法_JDBC系列教材 （四）- 在JDBC中使用ResultSet查询SQL语句

package jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class JdbcQuery {

private static final String URL = "jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8&serverTimezone=UTC";

private static final String DRIVER_NAME = "com.mysql.cj.jdbc.Driver";

private static final String USER = "root";

private static final String PASSWORD = "root";

/**

* 分页查询

*

* @param start 起始数

* @param count 一页显示总数

*/

public static void pageQuery(Statement s, int start, int count) {

String querySql = String.format("select * from hero limit %d, %d", start, count);

try {

ResultSet rs = s.executeQuery(querySql);

while (rs.next()) {

int id = rs.getInt(1);

String name = rs.getString(2);

float hp = rs.getFloat(3);

int damage = rs.getInt(4);

System.out.printf("id:%d, name:%s, hp:%.2f, damage:%d %n", id, name, hp, damage);

}

} catch (SQLException e) {

e.printStackTrace();

}

}

public static void main(String[] args) {

Connection c = null;

Statement s = null;

try {

Class.forName(DRIVER_NAME);

System.out.println("数据库驱动加载成功！");

} catch (ClassNotFoundException e) {

System.out.println("数据库驱动加载失败！");

e.printStackTrace();

}

try {

c = DriverManager.getConnection(URL, USER, PASSWORD);

System.out.println("数据库连接成功！");

s = c.createStatement();

// 查询hero表中一共有多少条记录

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

ResultSet rs = s.executeQuery(sql);

int total = 0;

if (rs.next()) {

total = rs.getInt(1);

}

System.out.println("hero表中一共有" + total + "条记录");

// 一页查询total_every_page条记录，一共可分为pages页

int total_every_page = 12;

int pages = (int) Math.ceil((double) total / (double) total_every_page);

System.out.println("一共可分为" + pages + "页");

for (int i = 0; i < pages; i++) {

System.out.printf("查询第%d页结果：%n", i + 1);

pageQuery(s, i * total_every_page, total_every_page);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (s != null) {

try {

s.close();

System.out.println("Statement已关闭");

} catch (SQLException e) {

System.out.println("Statement关闭异常");

e.printStackTrace();

}

}

if (c != null) {

try {

c.close();

System.out.println("Connection已关闭");

} catch (SQLException e) {

System.out.println("Connection关闭异常");

e.printStackTrace();

}

}

}

}

}