Spring訪問數據庫異常的處理方法
摘要：天我們將談談Spring訪問數據庫異常的處理方法，使用JDBC API時，很多操作都要聲明拋出java.sql.SQLException異常，通常情況下是要制定異常處理策略。
使用JDBC API時，很多操作都要聲明拋出java.sql.SQLException異常，通常情況下是要制定異常處理策略。而Spring的JDBC模塊為我們提供了一套異常處理機制，這套異常系統的基類是DataAccessException，它是RuntimeException的一種類型，那么就不用強制去捕捉異常了，Spring的異常體系如下：
目前為止我們還沒有明確地處理Spring中JDBC模塊的異常。要理解它的異常處理機制，我們來做幾個測試。看下面的測試代碼：
1. public void insert(final Vehicle vehicle) {
2. String sql = "insert into vehicle
3. (ID,PLATE,CHASSIS,COLOR,WHEEL,SEAT) values
4. (:id,:plate,:chassis,:color,:wheel,:seat)";
5. SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(
6. vehicle);
7. getSimpleJdbcTemplate().update(sql, parameterSource);
8. }
9. public void insert(final Vehicle vehicle) {
10. String sql = "insert into vehicle(ID,PLATE,CHASSIS,COLOR,WHEEL,SEAT)
11. values(:id,:plate,:chassis,:color,:wheel,:seat)";
12. SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(
13. vehicle);
14. getSimpleJdbcTemplate().update(sql, parameterSource);
15. }
2. String sql = "insert into vehicle
3. (ID,PLATE,CHASSIS,COLOR,WHEEL,SEAT) values
4. (:id,:plate,:chassis,:color,:wheel,:seat)";
5. SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(
6. vehicle);
7. getSimpleJdbcTemplate().update(sql, parameterSource);
8. }
9. public void insert(final Vehicle vehicle) {
10. String sql = "insert into vehicle(ID,PLATE,CHASSIS,COLOR,WHEEL,SEAT)
11. values(:id,:plate,:chassis,:color,:wheel,:seat)";
12. SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(
13. vehicle);
14. getSimpleJdbcTemplate().update(sql, parameterSource);
15. }
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext(
"classpath:org/ourpioneer/vehicle/spring/applicationContext.xml");
VehicleDAO vehicleDAO = (VehicleDAO) ctx.getBean("vehicleDAO");
Vehicle vehicle = new Vehicle("遼B-000000", "1A00000001", "RED", 4, 4);
vehicle.setId(1);
vehicleDAO.insert(vehicle);
}
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext(
"classpath:org/ourpioneer/vehicle/spring/applicationContext.xml");
VehicleDAO vehicleDAO = (VehicleDAO) ctx.getBean("vehicleDAO");
Vehicle vehicle = new Vehicle("遼B-000000", "1A00000001", "RED", 4, 4);
vehicle.setId(1);
vehicleDAO.insert(vehicle);
}
ApplicationContext ctx = new ClassPathXmlApplicationContext(
"classpath:org/ourpioneer/vehicle/spring/applicationContext.xml");
VehicleDAO vehicleDAO = (VehicleDAO) ctx.getBean("vehicleDAO");
Vehicle vehicle = new Vehicle("遼B-000000", "1A00000001", "RED", 4, 4);
vehicle.setId(1);
vehicleDAO.insert(vehicle);
}
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext(
"classpath:org/ourpioneer/vehicle/spring/applicationContext.xml");
VehicleDAO vehicleDAO = (VehicleDAO) ctx.getBean("vehicleDAO");
Vehicle vehicle = new Vehicle("遼B-000000", "1A00000001", "RED", 4, 4);
vehicle.setId(1);
vehicleDAO.insert(vehicle);
}
修改SQL語句，不使用自增主鍵的特性，并在這里設置重復的主鍵，那么運行程序，就會報出字段重復的異常。下面來捕捉這個異常：
1. try {
2. vehicleDAO.insert(vehicle);
3. } catch (DataAccessException e) {
4. SQLException sqle = (SQLException) e.getCause();
5. System.out.println("Error code: " + sqle.getErrorCode());
6. System.out.println("SQL state: " + sqle.getSQLState());
7. }
8. try {
9. vehicleDAO.insert(vehicle);
10. } catch (DataAccessException e) {
11. SQLException sqle = (SQLException) e.getCause();
12. System.out.println("Error code: " + sqle.getErrorCode());
13. System.out.println("SQL state: " + sqle.getSQLState());
14. }
2. vehicleDAO.insert(vehicle);
3. } catch (DataAccessException e) {
4. SQLException sqle = (SQLException) e.getCause();
5. System.out.println("Error code: " + sqle.getErrorCode());
6. System.out.println("SQL state: " + sqle.getSQLState());
7. }
8. try {
9. vehicleDAO.insert(vehicle);
10. } catch (DataAccessException e) {
11. SQLException sqle = (SQLException) e.getCause();
12. System.out.println("Error code: " + sqle.getErrorCode());
13. System.out.println("SQL state: " + sqle.getSQLState());
14. }
此時，我們就可以獲得錯誤碼和SQL狀態（不同的數據庫系統會有不同）：
關于HSQL數據庫的錯誤碼可以到org.hsqldb.Trace類中查看，只要注意運行結果會有一個負號，而類中定義的是沒有負號的。這樣就知道了這個錯誤的具體含義，比如104：唯一約束驗證失敗。這就是我們故意設置的重復主鍵問題。
Spring的JDBC模塊為我們預定義了一些錯誤代碼，它存儲在org.springframework.jdbc.support包下的sql-error-codes.xml文件中，其中描述HSQL的內容為：
1. bean id="HSQL" class="org.springframework.jdbc.support.SQLErrorCodes"
2. property name="databaseProductName"
3. valueHSQL Database Engine/value
4. /property
5. property name="badSqlGrammarCodes"
6. value-22,-28/value
7. /property
8. property name="duplicateKeyCodes"
9. value-104/value
10. /property
11. property name="dataIntegrityViolationCodes"
12. value-9/value
13. /property
14. property name="dataAccessResourceFailureCodes"
15. value-80/value
16. /property
17. /bean
18. bean id="HSQL" class="org.springframework.jdbc.support.SQLErrorCodes"
19. property name="databaseProductName"
20. valueHSQL Database Engine/value
21. /property
22. property name="badSqlGrammarCodes"
23. value-22,-28/value
24. /property
25. property name="duplicateKeyCodes"
26. value-104/value
27. /property
28. property name="dataIntegrityViolationCodes"
29. value-9/value
30. /property
31. property name="dataAccessResourceFailureCodes"
32. value-80/value
33. /property
34. /bean
2. property name="databaseProductName"
3. valueHSQL Database Engine/value
4. /property
5. property name="badSqlGrammarCodes"
6. value-22,-28/value
7. /property
8. property name="duplicateKeyCodes"
9. value-104/value
10. /property
11. property name="dataIntegrityViolationCodes"
12. value-9/value
13. /property
14. property name="dataAccessResourceFailureCodes"
15. value-80/value
16. /property
17. /bean
18. bean id="HSQL" class="org.springframework.jdbc.support.SQLErrorCodes"
19. property name="databaseProductName"
20. valueHSQL Database Engine/value
21. /property
22. property name="badSqlGrammarCodes"
23. value-22,-28/value
24. /property
25. property name="duplicateKeyCodes"
26. value-104/value
27. /property
28. property name="dataIntegrityViolationCodes"
29. value-9/value
30. /property
31. property name="dataAccessResourceFailureCodes"
32. value-80/value
33. /property
34. /bean
其余數據庫的錯誤碼內容也可以從這個文件之中獲得。下面我們來看看如何自定義異常處理。上面我們已經知道在org.springframework.jdbc.support包下有sql-error-codes.xml文件，在Spring啟動時會自動讀取這個文件中的錯誤碼，它為我們預分類了一些錯誤碼，而我們可以加強它，來使用我們自定義的異常。首先，定義一個異常類，我們就來自定義一下前面的-104錯誤，就是HSQL的重復鍵的問題：
1. package org.ourpioneer.vehicle.exception;
2. import org.springframework.dao.DataIntegrityViolationException;
3. public class VehicleDuplicateKeyException extends
4. DataIntegrityViolationException {
5. public VehicleDuplicateKeyException(String msg) {
6. super(msg);
7. }
8. public VehicleDuplicateKeyException(String msg, Throwable cause) {
9. super(msg, cause);
10. }
11. }
12. package org.ourpioneer.vehicle.exception;
13. import org.springframework.dao.DataIntegrityViolationException;
14. public class VehicleDuplicateKeyException extends
15. DataIntegrityViolationException {
16. public VehicleDuplicateKeyException(String msg) {
17. super(msg);
18. }
19. public VehicleDuplicateKeyException(String msg, Throwable cause) {
20. super(msg, cause);
21. }
22. }
2. import org.springframework.dao.DataIntegrityViolationException;
3. public class VehicleDuplicateKeyException extends
4. DataIntegrityViolationException {
5. public VehicleDuplicateKeyException(String msg) {
6. super(msg);
7. }
8. public VehicleDuplicateKeyException(String msg, Throwable cause) {
9. super(msg, cause);
10. }
11. }
12. package org.ourpioneer.vehicle.exception;
13. import org.springframework.dao.DataIntegrityViolationException;
14. public class VehicleDuplicateKeyException extends
15. DataIntegrityViolationException {
16. public VehicleDuplicateKeyException(String msg) {
17. super(msg);
18. }
19. public VehicleDuplicateKeyException(String msg, Throwable cause) {
20. super(msg, cause);
21. }
22. }
之后我們重新新建一個sql-error-codes.xml代碼，并將它放到類路徑的根目錄下，這樣Spring會發現它并使用我們自定義的文件，在配置中定義如下：
1. bean id="HSQL" class="org.springframework.jdbc.support.SQLErrorCodes"
2. property name="databaseProductName" value="HSQL Database Engine" /
3. property name="useSqlStateForTranslation" value="false" /
4. property name="customTranslations"
5. list
6. ref local="vehicleDuplicateKeyTranslation" /
7. /list
8. /property
9. /bean
10. bean id="vehicleDuplicateKeyTranslation"
11. class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation"
12. property name="errorCodes" value="-104" /
13. property name="exceptionClass"
14. value="org.ourpioneer.vehicle.exception.VehicleDuplicateKeyException" /
15. /bean
16. bean id="HSQL" class="org.springframework.jdbc.support.SQLErrorCodes"
17. property name="databaseProductName" value="HSQL Database Engine" /
18. property name="useSqlStateForTranslation" value="false" /
19. property name="customTranslations"
20. list
21. ref local="vehicleDuplicateKeyTranslation" /
22. /list
23. /property
24. /bean
25. bean id="vehicleDuplicateKeyTranslation"
26. class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation"
27. property name="errorCodes" value="-104" /
28. property name="exceptionClass"
29. value="org.ourpioneer.vehicle.exception.VehicleDuplicateKeyException" /
30. /bean
2. property name="databaseProductName" value="HSQL Database Engine" /
3. property name="useSqlStateForTranslation" value="false" /
4. property name="customTranslations"
5. list
6. ref local="vehicleDuplicateKeyTranslation" /
7. /list
8. /property
9. /bean
10. bean id="vehicleDuplicateKeyTranslation"
11. class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation"
12. property name="errorCodes" value="-104" /
13. property name="exceptionClass"
14. value="org.ourpioneer.vehicle.exception.VehicleDuplicateKeyException" /
15. /bean
16. bean id="HSQL" class="org.springframework.jdbc.support.SQLErrorCodes"
17. property name="databaseProductName" value="HSQL Database Engine" /
18. property name="useSqlStateForTranslation" value="false" /
19. property name="customTranslations"
20. list
21. ref local="vehicleDuplicateKeyTranslation" /
22. /list
23. /property
24. /bean
25. bean id="vehicleDuplicateKeyTranslation"
26. class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation"
27. property name="errorCodes" value="-104" /
28. property name="exceptionClass"
29. value="org.ourpioneer.vehicle.exception.VehicleDuplicateKeyException" /
30. /bean
HSQL的bean的名稱不要改，并將useSqlStateForTranslation置為false，就可以使用我們自己定義的異常類了。在主函數中移除try/catch塊，啟動程序，我們就可以看到如下內容：
從啟動信息中可以發現Spring發現了我們自定義的sql-error-codes.xml，并替換其中的HSQL數據庫處理部分，使用了我們定義的異常，模擬出主鍵重復的異常后，VehicleDuplicateKeyException就拋出了。除此之外，還可以實現SQLExceptionTranslator接口，并在JDBC模板中注入其實例來實現異常控制，我們來看一下，首先創建一個Translator類：
1. package org.ourpioneer.vehicle.exception;
2. import java.sql.SQLException;
3. import org.springframework.dao.DataAccessException;
4. import org.springframework.jdbc.UncategorizedSQLException;
5. import org.springframework.jdbc.support.SQLExceptionTranslator;
6. public class VehicleDuplicateKeyTranslator implements SQLExceptionTranslator {
7. public DataAccessException translate(String task, String sql,
8. SQLException ex) {
9. if (task == null) {
10. task = "";
11. }
12. if (sql == null) {
13. }
14. if (ex.getErrorCode() == -104) {
15. return new VehicleDuplicateKeyException(buildMessage(task, sql, ex));
16. } else {
17. return new UncategorizedSQLException(task, sql, ex);
18. }
19. }
20. private String buildMessage(String task, String sql, SQLException ex) {
21. return "數據庫操作異常：" + task + "; SQL [" + sql + "]; " + ex.getMessage();
22. }
23. }
24. package org.ourpioneer.vehicle.exception;
25. import java.sql.SQLException;
26. import org.springframework.dao.DataAccessException;
27. import org.springframework.jdbc.UncategorizedSQLException;
28. import org.springframework.jdbc.support.SQLExceptionTranslator;
29. public class VehicleDuplicateKeyTranslator implements SQLExceptionTranslator {
30. public DataAccessException translate(String task, String sql,
31. SQLException ex) {
32. if (task == null) {
33. task = "";
34. }
35. if (sql == null) {
36. }
37. if (ex.getErrorCode() == -104) {
38. return new VehicleDuplicateKeyException(buildMessage(task, sql, ex));
39. } else {
40. return new UncategorizedSQLException(task, sql, ex);
41. }
42. }
43. private String buildMessage(String task, String sql, SQLException ex) {
44. return "數據庫操作異常：" + task + "; SQL [" + sql + "]; " + ex.getMessage();
45. }
46. }
2. import java.sql.SQLException;
3. import org.springframework.dao.DataAccessException;
4. import org.springframework.jdbc.UncategorizedSQLException;
5. import org.springframework.jdbc.support.SQLExceptionTranslator;
6. public class VehicleDuplicateKeyTranslator implements SQLExceptionTranslator {
7. public DataAccessException translate(String task, String sql,
8. SQLException ex) {
9. if (task == null) {
10. task = "";
11. }
12. if (sql == null) {
13. }
14. if (ex.getErrorCode() == -104) {
15. return new VehicleDuplicateKeyException(buildMessage(task, sql, ex));
16. } else {
17. return new UncategorizedSQLException(task, sql, ex);
18. }
19. }
20. private String buildMessage(String task, String sql, SQLException ex) {
21. return "數據庫操作異常：" + task + "; SQL [" + sql + "]; " + ex.getMessage();
22. }
23. }
24. package org.ourpioneer.vehicle.exception;
25. import java.sql.SQLException;
26. import org.springframework.dao.DataAccessException;
27. import org.springframework.jdbc.UncategorizedSQLException;
28. import org.springframework.jdbc.support.SQLExceptionTranslator;
29. public class VehicleDuplicateKeyTranslator implements SQLExceptionTranslator {
30. public DataAccessException translate(String task, String sql,
31. SQLException ex) {
32. if (task == null) {
33. task = "";
34. }
35. if (sql == null) {
36. }
37. if (ex.getErrorCode() == -104) {
38. return new VehicleDuplicateKeyException(buildMessage(task, sql, ex));
39. } else {
40. return new UncategorizedSQLException(task, sql, ex);
41. }
42. }
43. private String buildMessage(String task, String sql, SQLException ex) {
44. return "數據庫操作異常：" + task + "; SQL [" + sql + "]; " + ex.getMessage();
45. }
46. }
其中，要覆蓋translate方法，方法有三個參數，task表示當前操作要進行的任務是什么，sql就是執行的sql語句，ex表示SQLException，我們可以從中獲取異常信息，其處理代碼僅僅捕捉了錯誤碼為-104（HSQL數據庫）的錯誤，其余的配置信息可以根據需要來自行添加。之后要在Spring中重新配置它們：
1. bean id="vehicleDuplicateKeyTranslator"
2. class="org.ourpioneer.vehicle.exception.VehicleDuplicateKeyTranslator"/bean
3. bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
4. property name="exceptionTranslator" ref="vehicleDuplicateKeyTranslator" /
5. property name="dataSource" ref="dataSource" /
6. /bean
7. bean id="vehicleDAO" class="org.ourpioneer.vehicle.dao.VehicleDAOImpl"
8. property name="jdbcTemplate" ref="jdbcTemplate" /
9. /bean
10. bean id="vehicleDuplicateKeyTranslator"
11. class="org.ourpioneer.vehicle.exception.VehicleDuplicateKeyTranslator"/bean
12. bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
13. property name="exceptionTranslator" ref="vehicleDuplicateKeyTranslator" /
14. property name="dataSource" ref="dataSource" /
15. /bean
16. bean id="vehicleDAO" class="org.ourpioneer.vehicle.dao.VehicleDAOImpl"
17. property name="jdbcTemplate" ref="jdbcTemplate" /
18. /bean
2. class="org.ourpioneer.vehicle.exception.VehicleDuplicateKeyTranslator"/bean
3. bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
4. property name="exceptionTranslator" ref="vehicleDuplicateKeyTranslator" /
5. property name="dataSource" ref="dataSource" /
6. /bean
7. bean id="vehicleDAO" class="org.ourpioneer.vehicle.dao.VehicleDAOImpl"
8. property name="jdbcTemplate" ref="jdbcTemplate" /
9. /bean
10. bean id="vehicleDuplicateKeyTranslator"
11. class="org.ourpioneer.vehicle.exception.VehicleDuplicateKeyTranslator"/bean
12. bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
13. property name="exceptionTranslator" ref="vehicleDuplicateKeyTranslator" /
14. property name="dataSource" ref="dataSource" /
15. /bean
16. bean id="vehicleDAO" class="org.ourpioneer.vehicle.dao.VehicleDAOImpl"
17. property name="jdbcTemplate" ref="jdbcTemplate" /
18. /bean
調整DAO實現類的代碼：
1. public class VehicleDAOImpl extends SimpleJdbcDaoSupport implements VehicleDAO {
2. … …
3. public void insert(final Vehicle vehicle) {
4. String sql = "insert into vehicle(ID,PLATE,CHASSIS,COLOR,WHEEL,SEAT) values(?,?,?,?,?,?)";
5. getJdbcTemplate().update(sql, vehicle.getId(),vehicle.getPlate(),vehicle.getChassis(),vehicle.getColor(),vehicle.getWheel(),vehicle.getSeat());
6. }
7. … …
8. }
9. public class VehicleDAOImpl extends SimpleJdbcDaoSupport implements VehicleDAO {
10. … …
11. public void insert(final Vehicle vehicle) {
12. String sql = "insert into vehicle(ID,PLATE,CHASSIS,COLOR,WHEEL,SEAT) values(?,?,?,?,?,?)";
13. getJdbcTemplate().update(sql, vehicle.getId(),vehicle.getPlate(),vehicle.getChassis(),vehicle.getColor(),vehicle.getWheel(),vehicle.getSeat());
14. }
15. … …
16. }
2. … …
3. public void insert(final Vehicle vehicle) {
4. String sql = "insert into vehicle(ID,PLATE,CHASSIS,COLOR,WHEEL,SEAT) values(?,?,?,?,?,?)";
5. getJdbcTemplate().update(sql, vehicle.getId(),vehicle.getPlate(),vehicle.getChassis(),vehicle.getColor(),vehicle.getWheel(),vehicle.getSeat());
6. }
7. … …
8. }
9. public class VehicleDAOImpl extends SimpleJdbcDaoSupport implements VehicleDAO {
10. … …
11. public void insert(final Vehicle vehicle) {
12. String sql = "insert into vehicle(ID,PLATE,CHASSIS,COLOR,WHEEL,SEAT) values(?,?,?,?,?,?)";
13. getJdbcTemplate().update(sql, vehicle.getId(),vehicle.getPlate(),vehicle.getChassis(),vehicle.getColor(),vehicle.getWheel(),vehicle.getSeat());
14. }
15. … …
16. }
為了進行測試，其它代碼可不用修改，這樣繼續運行測試程序，同時將sql-error-codes.xml文件從類路徑的根路徑下去除，就可以得到如下結果：
Spring的JDBC模塊在自定義異常處理上也非常靈活，可以選擇自己喜歡的方式來實現。希望對使用者有用，歡迎交流，下一部分開始介紹Spring的ORM。
全站熱搜
留言列表
