Java Tutorial/Database/SQLException Warning

Материал из Java эксперт
Перейти к: навигация, поиск

Chaining SQLExceptions

   <source lang="java">

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", "");
 }

}</source>





Check for a SQL Warning Using PreparedStatement

   <source lang="java">

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", "");
 }

}</source>





Check for a warning using a Connection object

   <source lang="java">

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", "");
 }

}</source>





Checking for a Warning Using a ResultSet Object

   <source lang="java">

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", "");
 }

}</source>





Checking for a Warning Using a Statement Object

   <source lang="java">

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", "");
 }

}</source>





Determine Whether a SQL Warning Has Occurred

You can check for a warning in three places:



   <source lang="java">

The Connection object (java.sql.Connection) 

     The Statement object (java.sql.Statement)
     The ResultSet object (java.sql.ResultSet)</source>



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

   <source lang="java">

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", "");
 }

}</source>





Get the Details of a SQLException

   <source lang="java">

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", "");
 }

}</source>





java.sql.SQLException Methods

   <source lang="java">

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</source>





Print the stack trace for a SQLException to STDERR.

   <source lang="java">

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:");
         }
     }
 }

}</source>





Print warnings on a Connection to a specified PrintWriter.

   <source lang="java">

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:");
         }
     }
 }

}</source>





Print warnings on a Connection to STDERR.

   <source lang="java">

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:");
         }
     }
 }

}</source>





Use JDBC Chained Exception

   <source lang="java">

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);
     }
   }
 }

}</source>





Using SQLWarning

   <source lang="java">

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", "");
 }

}</source>


Источник — «http://jexp.ru/index.php?title=Java_Tutorial/Database/SQLException_Warning&oldid=4780»