Retrieving Field Values using res.get*() Methods

JDBC for SQL Server - Herong's Tutorial Examples

∟Microsoft JDBC Driver - Query Statements and Result Sets

∟Retrieving Field Values using res.get*() Methods

This section describes how to retrieve field values of the current row in the ResultSet object with the res.get*() methods.

Once the result set is captured in an object, you can think of it as a "table" with rows and columns (fields). As shown in the previous tutorial, you can use res.next() to loop through each row. Then use res.get*() methods to retrieve field values of the current row by giving the field name or field position as shown below:

type value = res.getType(i); // retrieve by position
   // position value starts from 1

type value = res.getType(name); // retrieve by name

The tutorial Java program below shows you how to list tables in the current database. Multiple attributes of each table are retrieved and displayed:

/* GetFieldValues.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class GetFieldValues {
  public static void main(String [] args) {
    Connection con = null;
    try {

// Obtaining a connection to SQL Server
      con = DriverManager.getConnection(
          "jdbc:sqlserver://localhost\\SQLEXPRESS;"
        + "user=herong;password=T0pSecret;"
        + "database=AdventureWorks2019");

// Getting field values
      Statement sta = con.createStatement();
      ResultSet res = sta.executeQuery(
        "SELECT * FROM  sys.objects"
        + " WHERE type_desc='USER_TABLE'");
      System.out.println("User Tables:");
      while (res.next()) {
        String name = res.getString("name");
        int id = res.getInt(2);
        String type = res.getString(7);
        System.out.println("   "+name+", "+id+", "+type);
      }

      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

If you run this example program, you will get something like:

herong> java -cp .;mssql-jdbc-9.4.1.jre16.jar GetFieldValues

   BuildVersion, 5575058, USER_TABLE
   Address, 37575172, USER_TABLE
   Customer, 85575343, USER_TABLE
   CustomerAddress, 149575571, USER_TABLE
   Product, 197575742, USER_TABLE
   ProductCategory, 309576141, USER_TABLE
   ProductDescription, 357576312, USER_TABLE
   ProductModel, 405576483, USER_TABLE
   ProductModelProductDescription, 453576654, USER_TABLE
   SalesOrderDetail, 501576825, USER_TABLE
   SalesOrderHeader, 613577224, USER_TABLE
   ErrorLog, 2073058421, USER_TABLE