Sql server exception java

Java Guides

In this article, we will discuss how to handle SQLExceptions that encounters during an interaction with a data source.

Table of contents

1. Overview of SQLException
2. Retrieving Exceptions
3. Retrieving Warnings
4. Categorized SQLExceptions
5. Other Subclasses of SQLException

Overview of SQLException

When JDBC encounters an error during an interaction with a data source, it throws an instance of SQLException as opposed to Exception. (A data source in this context represents the database to which a Connection object is connected.) The SQLException instance contains the following information that can help you determine the cause of the error:

A description of the error — Retrieve the String object that contains this description by calling the method SQLException.getMessage.

A SQLState code — These codes and their respective meanings have been standardized by ISO/ANSI and Open Group (X/Open), although some codes have been reserved for database vendors to define for themselves. This String object consists of five alphanumeric characters. Retrieve this code by calling the method SQLException.getSQLState.

An error code — This is an integer value identifying the error that caused the SQLException instance to be thrown. Its value and meaning are implementation-specific and might be the actual error code returned by the underlying data source. Retrieve the error by calling the method SQLException.getErrorCode.

A cause — A SQLException instance might have a causal relationship, which consists of one or more Throwable objects that caused the SQLException instance to be thrown. To navigate this chain of causes, recursively call the method SQLException.getCause until a null value is returned.

A reference to any chained exceptions — If more than one error occurs, the exceptions are referenced through this chain. Retrieve these exceptions by calling the method SQLException.getNextException on the exception that was thrown.

Retrieving Exceptions

Let’s write a generic method to retrieve the SQLState, error code, error description, and cause (if there is one) contained in the SQLException as well as any other exception chained to it:

public static void printSQLException(SQLException ex) < for (Throwable e: ex) < if (e instanceof SQLException) < if (ignoreSQLException(((SQLException) e).getSQLState()) == false) < e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) < System.out.println("Cause: " + t); t = t.getCause(); > > > > >

For example, if our SQL statement has a syntax error then the above method prints the following result:

SQLState: 42000 Error Code: 1064 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where at line 1 

Instead of outputting SQLException information, we could instead first retrieve the SQLState then process the SQLException accordingly. For example, below ignoreSQLException() method returns true if the SQLState is equal to code 42Y55 (and you are using Java DB as your DBMS), which causes above printSQLException() method to ignore the SQLException:

public static boolean ignoreSQLException(String sqlState) < if (sqlState == null) < System.out.println("The SQL state is not defined!"); return false; > // X0Y32: Jar file already exists in schema if (sqlState.equalsIgnoreCase("X0Y32")) return true; // 42Y55: Table already exists in schema if (sqlState.equalsIgnoreCase("42Y55")) return true; return false; >

Retrieving Warnings

SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. For example, a warning might let you know that a privilege you attempted to revoke was not revoked. Or a warning might tell you that an error occurred during a requested disconnection.

The following methods illustrate how to get complete information about any warnings reported on Statement or ResultSet objects:

public static void getWarningsFromResultSet(ResultSet rs) throws SQLException < printWarnings(rs.getWarnings()); > public static void getWarningsFromStatement(Statement stmt) throws SQLException < printWarnings(stmt.getWarnings()); > public static void printWarnings(SQLWarning warning) throws SQLException < if (warning != null) < System.out.println("\n---Warning---\n"); while (warning != null) < System.out.println("Message: " + warning.getMessage()); System.out.println("SQLState: " + warning.getSQLState()); System.out.print("Vendor error code: "); System.out.println(warning.getErrorCode()); System.out.println(""); warning = warning.getNextWarning(); > > >

The most common warning is a DataTruncation warning, a subclass of SQLWarning. All DataTruncation objects have an SQLState of 01004, indicating that there was a problem with reading or writing data. DataTruncation methods let you find out in which column or parameter data was truncated, whether the truncation was on a read or write operation, how many bytes should have been transferred, and how many bytes were actually transferred.

Categorized SQLExceptions

Your JDBC driver might throw a subclass of SQLException that corresponds to a common SQLState or a common error state that is not associated with a specific SQLState class value. This enables you to write more portable error-handling code. These exceptions are subclasses of one of the following classes:

Other Subclasses of SQLException

• BatchUpdateException is thrown when an error occurs during a batch update operation. In addition to the information provided by SQLException, BatchUpdateException provides the update counts for all statements that were executed before the error occurred.
• SQLClientInfoException is thrown when one or more client information properties could not be set on a Connection. In addition to the information provided by SQLException, SQLClientInfoException provides a list of client information properties that were not set.

Источник

Обработка ошибок

При использовании Microsoft JDBC Driver для SQL Server все состояния ошибок в базе данных возвращаются приложениям Java в виде исключений при помощи класса SQLServerException. Приведенные далее методы класса SQLServerException унаследованы от классов java.sql.SQLException и java.lang.Throwable. Их можно использовать для возвращения конкретной информации о возникшей ошибке SQL Server.

• getSQLState() возвращает стандартный код состояния исключения X/Open или SQL99.
• getErrorCode() возвращает специфический номер ошибки базы данных.
• getMessage() возвращает полный текст исключения. В тексте сообщения об ошибке описывается проблема и зачастую содержатся заполнители для информации, такие как имена объектов, которые вставляются в сообщение об ошибке во время отображения.
• getSQLServerError() возвращает объект SQLServerError с подробными сведениями об исключении, полученными от SQL Server. Если ошибок не было, этот метод возвращает значение NULL.

Следующие методы класса SQLServerError можно применить для получения дополнительных сведений об ошибке, созданной на сервере.

• SQLServerError.getErrorMessage() возвращает сообщение об ошибке, которое вернул сервер.
• SQLServerError.getErrorNumber() возвращает номер, который обозначает тип ошибки.
• SQLServerError.getErrorState() возвращает числовой код ошибки, полученный из SQL Server, который обозначает ошибку, предупреждение или сообщение «данные не найдены».
• SQLServerError.getErrorSeverity() возвращает уровень серьезности полученной ошибки.
• SQLServerError.getServerName() возвращает имя компьютера, на котором работает создавший ошибку экземпляр SQL Server.
• SQLServerError.getProcedureName() возвращает имя хранимой процедуры или удаленного вызова процедуры, в которой произошла ошибка.
• SQLServerError.getLineNumber() возвращает номер строки в пакете команд Transact-SQL или хранимой процедуре, в которой произошла ошибка.

В следующем примере открытое соединение с SQL Server образцом базы данных AdventureWorks2022 передается в функцию и создается неправильно сформированная инструкция SQL без предложения FROM. Далее инструкция выполняется и происходит обработка исключения SQL.

public static void executeSQLException(Connection con) < try (Statement stmt = con.createStatement();) < String SQL = "SELECT TOP 10 * Person.Contact"; ResultSet rs = stmt.executeQuery(SQL); while (rs.next()) < System.out.println(rs.getString("FirstName") + " " + rs.getString("LastName")); >> catch (SQLException se) < do < System.out.println("SQL STATE: " + se.getSQLState()); System.out.println("ERROR CODE: " + se.getErrorCode()); System.out.println("MESSAGE: " + se.getMessage()); System.out.println(); >while (se != null); > > 

Источник

Handling errors

When using the Microsoft JDBC Driver for SQL Server, all database error conditions are returned to your Java application as exceptions using the SQLServerException class. The following methods of the SQLServerException class are inherited from java.sql.SQLException and java.lang.Throwable; and they can be used to return specific information about the SQL Server error that has occurred:

• getSQLState() returns the standard X/Open or SQL99 state code of the exception.
• getErrorCode() returns the specific database error number.
• getMessage() returns the full text of the exception. The error message text describes the problem, and frequently includes placeholders for information, such as object names, that are inserted in the error message when it’s displayed.
• getSQLServerError() returns the SQLServerError object containing detailed info about the exception as received from SQL Server. This method returns null if no server error has occurred.

The following methods of the SQLServerError class can be used to obtain more details about the error generated from the server.

• SQLServerError.getErrorMessage() returns the error message as received from the server.
• SQLServerError.getErrorNumber() returns a number that identifies the type of the error.
• SQLServerError.getErrorState() returns a numeric error code from SQL Server that represents an error, warning or «no data found» message.
• SQLServerError.getErrorSeverity() returns the severity level of the error received.
• SQLServerError.getServerName() returns the name of the computer that is running an instance of SQL Server that generated the error.
• SQLServerError.getProcedureName() returns the name of the stored procedure or remote procedure call (RPC) that generated the error.
• SQLServerError.getLineNumber() returns the line number within the Transact-SQL command batch or stored procedure that generated the error.

In the next example, an open connection to the SQL Server AdventureWorks2022 sample database is passed in to the function and a malformed SQL statement is constructed that doesn’t have a FROM clause. Then, the statement is run and an SQL exception is processed.

public static void executeSQLException(Connection con) < try (Statement stmt = con.createStatement();) < String SQL = "SELECT TOP 10 * Person.Contact"; ResultSet rs = stmt.executeQuery(SQL); while (rs.next()) < System.out.println(rs.getString("FirstName") + " " + rs.getString("LastName")); >> catch (SQLException se) < do < System.out.println("SQL STATE: " + se.getSQLState()); System.out.println("ERROR CODE: " + se.getErrorCode()); System.out.println("MESSAGE: " + se.getMessage()); System.out.println(); >while (se != null); > > 

Источник