Java Tutorial/Database/Query ResultSet

Convert a ResultSet to XML

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
public class Main {
  public static void main(String args[]) throws Exception {
    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    DocumentBuilder builder = factory.newDocumentBuilder();
    Document doc = builder.newDocument();
    Element results = doc.createElement("Results");
    doc.appendChild(results);
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager
        .getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:/access.mdb");
    
    ResultSet rs = con.createStatement().executeQuery("select * from product");
    ResultSetMetaData rsmd = rs.getMetaData();
    int colCount = rsmd.getColumnCount();
    while (rs.next()) {
      Element row = doc.createElement("Row");
      results.appendChild(row);
      for (int i = 1; i <= colCount; i++) {
        String columnName = rsmd.getColumnName(i);
        Object value = rs.getObject(i);
        Element node = doc.createElement(columnName);
        node.appendChild(doc.createTextNode(value.toString()));
        row.appendChild(node);
      }
    }
    DOMSource domSource = new DOMSource(doc);
    TransformerFactory tf = TransformerFactory.newInstance();
    Transformer transformer = tf.newTransformer();
    transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
    transformer.setOutputProperty(OutputKeys.METHOD, "xml");
    transformer.setOutputProperty(OutputKeys.ENCODING, "ISO-8859-1");
    StringWriter sw = new StringWriter();
    StreamResult sr = new StreamResult(sw);
    transformer.transform(domSource, sr);
    System.out.println(sw.toString());
    con.close();
    rs.close();
  }
}

Converting types: DATE to String

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn
        .createStatement();
    st.executeUpdate("create table survey (id int,myDate DATE);");
    String INSERT_RECORD = "insert into survey(id, myDate) values(?, ?)";
    
    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    java.sql.Date sqlDate = new java.sql.Date(new java.util.Date().getTime());
    pstmt.setDate(2, sqlDate);
    
    pstmt.executeUpdate();
    
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);
    rs.close();
    st.close();
    conn.close();
  }
  private static void outputResultSet(ResultSet rs) throws Exception {
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    for (int i = 1; i < numberOfColumns + 1; i++) {
      String columnName = rsMetaData.getColumnName(i);
      System.out.print(columnName + "   ");
    }
    System.out.println();
    System.out.println("----------------------");
    while (rs.next()) {
      for (int i = 1; i < numberOfColumns + 1; i++) {
        System.out.print(rs.getString(i) + "   ");
      }
      System.out.println();
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Determine If a Fetched Value Is NULL

To determine whether the actual value is a NULL, wasNull() must be called.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
    st = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
      String name = rs.getString(2);
      if (rs.wasNull()) {
        System.out.println("was NULL");
      } else {
        System.out.println("not NULL");
      }
    }
    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

not NULL
was NULL
not NULL

Get Data from a ResultSet

1. The result set maintains a reference to the current row called the cursor.
2. The cursor is positioned before the first row when a result set is created.
3. When a result set"s next() method is called, the cursor moves to the first row of the resultset, and that row becomes the current row.

Get int value from ResultSet

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    // extract data from the ResultSet
    while (rs.next()) {
      int id = rs.getInt(1);
      System.out.println("id=" + id);
      String name = rs.getString(2);
      System.out.println("name=" + name);
      if (rs.wasNull()) {
        System.out.println("name is null");
      } else {
        System.out.println("name is not null");
      }
      System.out.println("---------------");
    }
    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Get the Number of Rows in a Database Table

select count(*) form <table-name>

If you Do Not Know the Name, Position, and Type of Each Column, how to get value from ResultSet

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
    st = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
      int id = rs.getInt(1); // index 1 is the "id" column
      String name = rs.getString(2); // index 2 is the "name" column
      System.out.println(id);
      System.out.println(name);
    }
    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

1
nameValue
2
null
3
Tom

Limit the Number of Rows Returned from a SQL Query

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLWarning;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);
    Statement st = conn.createStatement();
    
    st.setFetchSize(1);
    
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,"nameValue")");
    st = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    
    rs.setFetchSize(1);
    
    outputResultSet(rs);
    checkForWarning(rs.getWarnings());
    rs.close();
    st.close();
    conn.close();
  }
  static boolean checkForWarning(SQLWarning w) {
    if (w == null) {
      return false;
    }
    do {
      System.err.println("Warning:\nMessage: " + w.getMessage());
      System.err.println("SQL state: " + w.getSQLState());
      System.err.println("Vendor code: " + w.getErrorCode() + "\n----------------");
    } while ((w = w.getNextWarning()) != null);
    return true;
  }
  private static void outputResultSet(ResultSet rs) throws Exception {
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    for (int i = 1; i < numberOfColumns + 1; i++) {
      String columnName = rsMetaData.getColumnName(i);
      System.out.print(columnName + "   ");
    }
    System.out.println();
    System.out.println("----------------------");
    while (rs.next()) {
      for (int i = 1; i < numberOfColumns + 1; i++) {
        System.out.print(rs.getString(i) + "   ");
      }
      System.out.println();
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

ResultSet: A SQL query returns a ResultSet containing the requested data

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement();
    
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    
    // extract data from the ResultSet
    while (rs.next()) {
        int id = rs.getInt(1);
        System.out.println("id="+id);
        String name = rs.getString(2);
        System.out.println("name="+name);
        if (rs.wasNull()) {
            System.out.println("name is null");
        } else {
             System.out.println("name is not null");
        }
        System.out.println("---------------");
    }
    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

id=1
name=nameValue
name is not null
---------------
id=2
name=null
name is null
---------------

ResultSet"s Methods to Access Columns by Index

1. String getString(int columnIndex);
2. boolean getBoolean(int columnIndex);
3. byte getByte(int columnIndex);
4. short getShort(int columnIndex);
5. int getInt(int columnIndex);
6. long getLong(int columnIndex);
7. float getFloat(int columnIndex);
8. double getDouble(int columnIndex);
9. byte[] getBytes(int columnIndex);
10. java.sql.Date getDate(int columnIndex);
11. java.sql.Date getDate(int columnIndex, java.util.Calendar cal);
12. java.sql.Time getTime(int columnIndex);
13. java.sql.Timestamp getTimestamp(int columnIndex);
14. java.sql.Timestamp getTimestamp(int columnIndex, java.util.Calendar cal);
15. java.io.InputStream getAsciiStream(int columnIndex);
16. java.io.InputStream getBinaryStream(int columnIndex);
17. Object getObject(int columnIndex);
18. Object getObject(int columnIndex, java.util.Map map);
19. java.sql.Array getArray(int columnIndex);
20. java.math.BigDecimal getBigDecimal(int columnIndex);
21. java.sql.Blob getBlob(int columnIndex);
22. java.sql.Clob getClob(int columnIndex);
23. java.io.Reader getCharacterStream(int columnIndex);
24. java.sql.Ref getRef(int columnIndex);
25. java.net.URL getURL(int columnIndex);

ResultSet"s Methods to Access Columns by Name

1. String getString(String columnName);
2. boolean getBoolean(String columnName);
3. byte getByte(String columnName);
4. short getShort(String columnName);
5. int getInt(String columnName);
6. long getLong(String columnName);
7. float getFloat(String columnName);
8. double getDouble(String columnName);
9. byte[] getBytes(String columnName);
10. java.sql.Date getDate(String columnName);
11. java.sql.Date getDate(String columnName, java.util.Calendar cal);
12. java.sql.Time getTime(String columnName);
13. java.sql.Timestamp getTimestamp(String columnName);
14. java.sql.Timestamp getTimestamp(String columnName, java.util.Calendar cal);
15. java.io.InputStream getAsciiStream(String columnName);
16. java.io.InputStream getBinaryStream(String columnName);
17. Object getObject(String columnName);
18. Object getObject(String columnName, java.util.Map map);
19. java.sql.Array getArray(String columnName);
20. java.math.BigDecimal getBigDecimal(String columnName);
21. java.sql.Blob getBlob(String columnName);
22. java.sql.Clob getClob(String columnName);
23. java.io.Reader getCharacterStream(String columnName);
24. java.sql.Ref getRef(String columnName);
25. java.net.URL getURL(String columnName);

Retrieving the Value of a Column Using the Column Name

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
      String name = rs.getString("name");
      System.out.println(name);
    }
    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

nameValue
null

Retrieving the Value of a Column Using the Index Number

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
      String name = rs.getString(2);
      System.out.println(name);
    }
    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

nameValue
null

Selecting all rows from a table and creates a result set:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
    st = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
      String name = rs.getString("name");
      System.out.println(name);
    }
    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

nameValue
null
Tom