Java Tutorial/Database/SQLException Warning

Chaining SQLExceptions

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    try {
      Connection conn = getHSQLConnection();
      conn.setAutoCommit(false);
      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 = conn.createStatement();
      ResultSet rs = st.executeQuery("SELECT * FROM survey");
      outputResultSet(rs);
      rs.close();
      st.close();
      conn.close();
    } catch (SQLException sqle) {
      do { // loop through each exception
        // do something with each exception
        System.err.println("Exception occurred:\nMessage: " + sqle.getMessage());
        System.err.println("SQL state: " + sqle.getSQLState());
        System.err.println("Vendor code: " + sqle.getErrorCode() + "\n----------------");
      } while ((sqle = sqle.getNextException()) != null);
    }
  }
  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 getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Check for a SQL Warning Using PreparedStatement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
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.executeUpdate("create table survey (id int, name VARCHAR(30) );");
    String INSERT_RECORD = "insert into survey(id, name) values(?,?)";
    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setInt(1, 1);
    pstmt.setString(2, "name");
    pstmt.executeUpdate();
    // Get warnings on PreparedStatement object
    SQLWarning warning = pstmt.getWarnings();
    while (warning != null) {
      // Process statement warnings...
      String message = warning.getMessage();
      String sqlState = warning.getSQLState();
      int errorCode = warning.getErrorCode();
      warning = warning.getNextWarning();
    }
    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", "");
  }
}

Check for a warning using a Connection object

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLWarning;
public class Main {
  public static void main(String[] args) throws Exception {
      try {
        Connection conn = getConnection(); // get a java.sql.Connection object
        SQLWarning warning = conn.getWarnings();
        while (warning != null) {
            // process connection warning
            String message = warning.getMessage();
            String sqlState = warning.getSQLState();
            int errorCode = warning.getErrorCode();
            warning = warning.getNextWarning();
        }
    }
    catch (SQLException e) {
        // ignore the exception
    }
    finally {
      // close JDBC resources: ResultSet, Statement, Connection
    }
      
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Checking for a Warning Using a ResultSet Object

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    // Get warnings on ResultSet object
    ResultSet rs = null;
    Connection conn = null;
    Statement stmt = null;
    try {
      conn = getConnection(); // get a java.sql.Connection object
      stmt = conn.createStatement(); // create a statement
      // get a result set
      String sqlQuery = "select id, name from employees";
      rs = stmt.executeQuery(sqlQuery);
      while (rs.next()) {
        // use result set
        // 
        // get warnings on the current row of the ResultSet object
        SQLWarning warning = rs.getWarnings();
        if (warning != null) {
          // process result set warnings
        }
      }
    } catch (SQLException e) {
      // ignore the exception
    } finally {
      // close JDBC resources: ResultSet, Statement, Connection
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Checking for a Warning Using a Statement Object

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    // Get warnings on Statement object
    Connection conn = null;
    Statement stmt = null;
    try {
      conn = getConnection(); // get a java.sql.Connection object
      stmt = conn.createStatement(); // create a statement
      // use the statement
      // get warnings on Statement object
      SQLWarning warning = stmt.getWarnings();
      if (warning != null) {
        // Process statement warnings
      }
    } catch (SQLException e) {
      // ignore the exception
    } finally {
      // close JDBC resources: ResultSet, Statement, Connection
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Determine Whether a SQL Warning Has Occurred

You can check for a warning in three places:

The Connection object (java.sql.Connection)
      The Statement object (java.sql.Statement)
      The ResultSet object (java.sql.ResultSet)

Exceptions in JDBC

1. java.sql.SQLException extends the java.lang.Exception class
2. java.sql.SQLException is a core JDBC exception class that provides information about database access errors and other errors. Most of the JDBC API methods throw SQLException.
3. java.sql.BatchUpdateException: a BatchUpdateException provides the update counts for all commands that were executed successfully during the batch update.
4. java.sql.DataTruncation reports a DataTruncation warning (on reads) or throws a DataTruncation exception (on writes) when JDBC unexpectedly truncates a data value.
5. java.sql.SQLWarning provides information about database access warnings.

Extracting Information from SQLException

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    try {
      Connection conn = getHSQLConnection();
      conn.setAutoCommit(false);
      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 = conn.createStatement();
      ResultSet rs = st.executeQuery("SELECT * FROM survey");
      outputResultSet(rs);
      rs.close();
      st.close();
      conn.close();
    } catch (SQLException sqle) {
      String sqlMessage = sqle.getMessage();
      String sqlState = sqle.getSQLState();
      int vendorCode = sqle.getErrorCode();
      System.err.println("Exception occurred:");
      System.err.println("Message: " + sqlMessage);
      System.err.println("SQL state: " + sqlState);
      System.err.println("Vendor code: " + vendorCode + "\n----------------");
    }
  }
  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 getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Get the Details of a SQLException

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    try {
      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");
      rs.close();
      st.close();
      conn.close();
    } catch (SQLException e) {
      while (e != null) {
        String errorMessage = e.getMessage();
        System.err.println("sql error message:" + errorMessage);
        // This vendor-independent string contains a code.
        String sqlState = e.getSQLState();
        System.err.println("sql state:" + sqlState);
        int errorCode = e.getErrorCode();
        System.err.println("error code:" + errorCode);
        // String driverName = conn.getMetaData().getDriverName();
        // System.err.println("driver name:"+driverName);
        // processDetailError(drivername, errorCode);
        e = e.getNextException();
      }
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

java.sql.SQLException Methods

Return Type      Method                                 Description
int              getErrorCode()                         Retrieves the vendor-specific exception code for this SQLException object
SQLException     getNextException()                     Retrieves the exception chained to this SQLException object
String           getSQLState()                          Retrieves the SQLState for this SQLException object
void             setNextException(SQLException ex)      Adds a SQLException object to the end of the chain

Print the stack trace for a SQLException to STDERR.

import java.io.PrintWriter;
import java.sql.SQLException;
/* 
 * Licensed to the Apache Software Foundation (ASF) under one or more
 *  contributor license agreements.  See the NOTICE file distributed with
 *  this work for additional information regarding copyright ownership.
 *  The ASF licenses this file to You under the Apache License, Version 2.0
 *  (the "License"); you may not use this file except in compliance with
 *  the License.  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 *
 *
 */
public class Main {

  /**
   * Print the stack trace for a SQLException to STDERR.
   *
   * @param e SQLException to print stack trace of
   */
  public static void printStackTrace(SQLException e) {
      printStackTrace(e, new PrintWriter(System.err));
  }
  /**
   * Print the stack trace for a SQLException to a 
   * specified PrintWriter. 
   *
   * @param e SQLException to print stack trace of
   * @param pw PrintWriter to print to
   */
  public static void printStackTrace(SQLException e, PrintWriter pw) {
      SQLException next = e;
      while (next != null) {
          next.printStackTrace(pw);
          next = next.getNextException();
          if (next != null) {
              pw.println("Next SQLException:");
          }
      }
  }
}

Print warnings on a Connection to a specified PrintWriter.

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
/* 
 * Licensed to the Apache Software Foundation (ASF) under one or more
 *  contributor license agreements.  See the NOTICE file distributed with
 *  this work for additional information regarding copyright ownership.
 *  The ASF licenses this file to You under the Apache License, Version 2.0
 *  (the "License"); you may not use this file except in compliance with
 *  the License.  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 *
 *
 */
public class Main {

  /**
   * Print warnings on a Connection to STDERR.
   *
   * @param conn Connection to print warnings from
   */
  public static void printWarnings(Connection conn) {
      printWarnings(conn, new PrintWriter(System.err));
  }
  /**
   * Print warnings on a Connection to a specified PrintWriter. 
   *
   * @param conn Connection to print warnings from
   * @param pw PrintWriter to print to
   */
  public static void printWarnings(Connection conn, PrintWriter pw) {
      if (conn != null) {
          try {
              printStackTrace(conn.getWarnings(), pw);
          } catch (SQLException e) {
              printStackTrace(e, pw);
          }
      }
  }

  /**
   * Print the stack trace for a SQLException to a 
   * specified PrintWriter. 
   *
   * @param e SQLException to print stack trace of
   * @param pw PrintWriter to print to
   */
  public static void printStackTrace(SQLException e, PrintWriter pw) {
      SQLException next = e;
      while (next != null) {
          next.printStackTrace(pw);
          next = next.getNextException();
          if (next != null) {
              pw.println("Next SQLException:");
          }
      }
  }
}

Print warnings on a Connection to STDERR.

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
/* 
 * Licensed to the Apache Software Foundation (ASF) under one or more
 *  contributor license agreements.  See the NOTICE file distributed with
 *  this work for additional information regarding copyright ownership.
 *  The ASF licenses this file to You under the Apache License, Version 2.0
 *  (the "License"); you may not use this file except in compliance with
 *  the License.  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 *
 *
 */
public class Main {

  /**
   * Print warnings on a Connection to STDERR.
   *
   * @param conn Connection to print warnings from
   */
  public static void printWarnings(Connection conn) {
      printWarnings(conn, new PrintWriter(System.err));
  }
  /**
   * Print warnings on a Connection to a specified PrintWriter. 
   *
   * @param conn Connection to print warnings from
   * @param pw PrintWriter to print to
   */
  public static void printWarnings(Connection conn, PrintWriter pw) {
      if (conn != null) {
          try {
              printStackTrace(conn.getWarnings(), pw);
          } catch (SQLException e) {
              printStackTrace(e, pw);
          }
      }
  }

  /**
   * Print the stack trace for a SQLException to a 
   * specified PrintWriter. 
   *
   * @param e SQLException to print stack trace of
   * @param pw PrintWriter to print to
   */
  public static void printStackTrace(SQLException e, PrintWriter pw) {
      SQLException next = e;
      while (next != null) {
          next.printStackTrace(pw);
          next = next.getNextException();
          if (next != null) {
              pw.println("Next SQLException:");
          }
      }
  }
}

Use JDBC Chained Exception

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ChainedExceptionDemo {
  static Connection conn;
  public static void main(String[] args) {
    String driver = "com.mysql.jdbc.Driver";
    String connectionURL = "jdbc:mysql://127.0.0.1:3306/sample";
    try {
      Class.forName(driver);
    } catch (java.lang.ClassNotFoundException e) {
      e.printStackTrace();
    }
    try {
      conn = DriverManager.getConnection(connectionURL);
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM NONEXISTINGTABLE");
      rs.next();
      rs.close();
    } catch (SQLException sx) {
      for (Throwable e : sx) {
        System.err.println("Error encountered: " + e);
      }
    }
  }
}

Using SQLWarning

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 = getHSQLConnection();
    conn.setAutoCommit(false);
    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 = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    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 getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}