JDBC for SQL Server - Herong's Tutorial Examples - v3.14, by Herong Yang
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
Table of Contents
JDBC (Java Database Connectivity) Introduction
Microsoft SQL Server Express Edition
Microsoft JDBC Driver for SQL Server
►Microsoft JDBC Driver - Query Statements and Result Sets
Commonly Used JDBC Class Methods
Calling createStatement() and executeQuery
Receiving ResultSet Objects from executeQuery
Closing ResultSet Objects - res.close()
Looping through ResultSet with res.next()
►Retrieving Field Values using res.get*() Methods
Using ResultSetMetaData Objects to List All Fields
Microsoft JDBC Driver - DatabaseMetaData Object
Microsoft JDBC Driver - DDL Statements
Microsoft JDBC Driver - DML Statements
SQL Server - PreparedStatement
SQL Server CLOB (Character Large Object) - TEXT
SQL Server BLOB (Binary Large Object) - BLOB
Using Connection Pool with JDBC
JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver
JDBC-ODBC Bridge Driver - Flat Text Files
JDBC-ODBC Bridge Driver - MS Access