Java结果集映射与元数据

结果集映射将SQL查询结果转换为Java对象，元数据提供数据库结构信息。

ResultSet基本使用

获取数据

PreparedStatement pstmt = conn.prepareStatement(
    "SELECT id, name, age, created_at FROM users");
ResultSet rs = pstmt.executeQuery();

while (rs.next()) {
    // 按列名获取
    int id = rs.getInt("id");
    String name = rs.getString("name");
    int age = rs.getInt("age");
    Date createdAt = rs.getDate("created_at");

    // 或按列索引获取（从1开始）
    int id = rs.getInt(1);
    String name = rs.getString(2);
}

处理NULL值

// 方式1：先判断是否为NULL
int age = rs.getInt("age");
if (rs.wasNull()) {
    age = 0;  // NULL时设置默认值
}

// 方式2：使用getObject
Object ageObj = rs.getObject("age");
Integer age = (ageObj != null) ? (Integer) ageObj : null;

// 方式3：Java 8+ Optional
Integer age = Optional.ofNullable(rs.getObject("age", Integer.class))
                      .orElse(0);

不同类型数据获取

// 数值类型
int intValue = rs.getInt("int_column");
long longValue = rs.getLong("long_column");
double doubleValue = rs.getDouble("double_column");
BigDecimal decimal = rs.getBigDecimal("decimal_column");

// 字符类型
String string = rs.getString("string_column");

// 时间类型
Date date = rs.getDate("date_column");
Time time = rs.getTime("time_column");
Timestamp timestamp = rs.getTimestamp("timestamp_column");

// 二进制类型
byte[] bytes = rs.getBytes("binary_column");
Blob blob = rs.getBlob("blob_column");
InputStream stream = blob.getBinaryStream();

// 大文本
Clob clob = rs.getClob("clob_column");
Reader reader = clob.getCharacterStream();

手动映射对象

public class User {
    private int id;
    private String name;
    private int age;
    private Date createdAt;

    // getters/setters...
}

public List<User> mapUsers(ResultSet rs) throws SQLException {
    List<User> users = new ArrayList<>();
    while (rs.next()) {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        user.setAge(rs.getInt("age"));
        user.setCreatedAt(rs.getTimestamp("created_at"));
        users.add(user);
    }
    return users;
}

使用RowMapper（Spring JDBC）

RowMapper接口

public class UserRowMapper implements RowMapper<User> {
    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        user.setAge(rs.getInt("age"));
        user.setCreatedAt(rs.getTimestamp("created_at"));
        return user;
    }
}

// 使用
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
List<User> users = jdbcTemplate.query(
    "SELECT * FROM users",
    new UserRowMapper()
);

BeanPropertyRowMapper

// 自动映射（列名与属性名匹配）
List<User> users = jdbcTemplate.query(
    "SELECT id, name, age, created_at FROM users",
    new BeanPropertyRowMapper<>(User.class)
);

// 列名映射规则：
// user_name → userName（驼峰转换）
// created_at → createdAt

Lambda简化

List<User> users = jdbcTemplate.query(
    "SELECT * FROM users",
    (rs, rowNum) -> {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        return user;
    }
);

ResultSetMetaData元数据

获取元数据

PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users");
ResultSet rs = pstmt.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();

// 列数
int columnCount = metaData.getColumnCount();

// 遍历所有列信息
for (int i = 1; i <= columnCount; i++) {
    String columnName = metaData.getColumnName(i);       // 列名
    String columnLabel = metaData.getColumnLabel(i);     // 别名
    int columnType = metaData.getColumnType(i);          // SQL类型
    String columnTypeName = metaData.getColumnTypeName(i); // 类型名
    int precision = metaData.getPrecision(i);            // 精度
    int scale = metaData.getScale(i);                    // 小数位
    boolean nullable = metaData.isNullable(i) == 1;      // 是否可NULL
    int displaySize = metaData.getColumnDisplaySize(i);  // 显示长度
}

动态映射

public Map<String, Object> rowToMap(ResultSet rs) throws SQLException {
    ResultSetMetaData metaData = rs.getMetaData();
    int columnCount = metaData.getColumnCount();
    Map<String, Object> row = new HashMap<>();

    for (int i = 1; i <= columnCount; i++) {
        String columnName = metaData.getColumnLabel(i);
        Object value = rs.getObject(i);
        row.put(columnName, value);
    }
    return row;
}

// 使用
List<Map<String, Object>> rows = jdbcTemplate.query(
    "SELECT * FROM users",
    (rs, rowNum) -> rowToMap(rs)
);

SQL类型对照

// JDBC Types.java类型常量
Types.INTEGER     // 整数
Types.BIGINT      // 长整数
Types.VARCHAR     // 字符串
Types.DATE        // 日期
Types.TIMESTAMP   // 时间戳
Types.BOOLEAN     // 布尔
Types.DECIMAL     // 小数

// 类型转换
int sqlType = metaData.getColumnType(i);
switch (sqlType) {
    case Types.INTEGER:
        int value = rs.getInt(i);
        break;
    case Types.VARCHAR:
        String value = rs.getString(i);
        break;
    // ...
}

DatabaseMetaData数据库元数据

获取数据库信息

DatabaseMetaData dbMeta = conn.getMetaData();

// 数据库基本信息
String dbName = dbMeta.getDatabaseProductName();     // MySQL
String dbVersion = dbMeta.getDatabaseProductVersion(); // 8.0.28
String driverName = dbMeta.getDriverName();         // MySQL Connector
String driverVersion = dbMeta.getDriverVersion();   // 8.0.28
int majorVersion = dbMeta.getDatabaseMajorVersion();
int minorVersion = dbMeta.getDatabaseMinorVersion();

获取表信息

// 获取所有表
ResultSet tables = dbMeta.getTables(
    null,           // catalog（数据库）
    null,           // schema
    null,           // tableNamePattern
    new String[]{"TABLE"}  // 类型
);

while (tables.next()) {
    String tableName = tables.getString("TABLE_NAME");
    String tableType = tables.getString("TABLE_TYPE");
    String remarks = tables.getString("REMARKS");  // 注释
}

获取列信息

// 获取表的列信息
ResultSet columns = dbMeta.getColumns(
    null, null, "users", null
);

while (columns.next()) {
    String columnName = columns.getString("COLUMN_NAME");
    String columnType = columns.getString("TYPE_NAME");
    int columnSize = columns.getInt("COLUMN_SIZE");
    String remarks = columns.getString("REMARKS");
    String isNullable = columns.getString("IS_NULLABLE");
}

获取主键信息

ResultSet primaryKeys = dbMeta.getPrimaryKeys(null, null, "users");

while (primaryKeys.next()) {
    String columnName = primaryKeys.getString("COLUMN_NAME");
    short keySeq = primaryKeys.getShort("KEY_SEQ");  // 主键顺序
    String pkName = primaryKeys.getString("PK_NAME"); // 主键名
}

大数据量ResultSet处理

流式ResultSet

// MySQL流式结果集（避免一次性加载）
PreparedStatement pstmt = conn.prepareStatement(
    "SELECT * FROM large_table",
    ResultSet.TYPE_FORWARD_ONLY,
    ResultSet.CONCUR_READ_ONLY
);
pstmt.setFetchSize(100);  // 每次获取100条

ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
    // 处理每条记录，内存占用小
}

分页查询

// MySQL分页
int page = 1;
int pageSize = 100;
String sql = "SELECT * FROM users LIMIT " + (page - 1) * pageSize + ", " + pageSize;

// Oracle分页
String sql = "SELECT * FROM (SELECT a.*, ROWNUM rn FROM users a WHERE ROWNUM <= ?) WHERE rn > ?";

注意事项

ResultSet使用后需关闭释放资源

列索引从1开始，不是0

处理NULL值用wasNull()或getObject()

流式ResultSet需特定配置

元数据查询可能较慢，不要频繁调用

要点总结

1. ResultSet按列名或索引获取数据，索引从1开始
2. wasNull()判断数值类型是否为NULL
3. RowMapper封装映射逻辑，BeanPropertyRowMapper自动映射
4. ResultSetMetaData获取列信息，动态处理结果
5. DatabaseMetaData获取表结构、主键等数据库信息