Programming experience-avoid using try catch in database access functions

The code examples in many books use try catch in database access functions to mislead beginners.

Let's analyze a common function (Some large companies in ChinaCode, negative examples, cannot be imitated),

 1     public int updateData(String sql)     { 2         int resultRow = 0;          3         try{ 4             Connection con = ... 5             statement = con.createStatement();              6             resultRow = statement.executeUpdate(sql); 7             ...                       8         } catch (SQLException e) { 9             e.printStackTrace();         10         }                   11         return resultRow;     12     } 

 

The problem with the function mentioned here is that there will be problems with such calls (Ask the speaker to take a closer look at this pseudocode):
1) begin database transaction
2)UpdateData("Update user set last_active_time = ...");
3)UpdateData("Insert ....");
3) ftpSend ();
3) sendMail ();
4) commit ();

In updateData (), try catch or commit/rollback internally. This is a big problem!

 

There are many problems:

A) After an SQL Execution error occurs, it is simply output to the console. The error message is not returned or throw through throw Exception. The result is probably that the SQL operation fails, but the interface prompts "operation successful ".

B) if the code executes multiple update/delete operations consecutively, put them in one transaction. After an SQL Execution error occurs, SQLException is caught and transaction controls the code and cannot be rolled back.

C) Of course there are SQL Injection problems. PreparedStatement should be used here.

 

To avoid code"An error occurred while running the code. The interface prompts: operation successful"To avoid using try catch in database access functions. Furthermore, try catch should be disabled in tool classes, dao, and service code.

So where should try catch be put?

1) if it is a standalone program, an error message should be prompted to the user. try catch should be placed in the event response function. Of course, if transaction is used, begin/commit/rollback is also used here.

2) if it is a Web MVC program, the error message should be prompted to the user. try catch should be placed in the corresponding Event Response java/C # code of the URL. Of course, if transaction is used, begin/commit/rollback is also used here. If it is a Java EE program, it is recommended to put a try catch in the filter as a global exception Control, prevent a try catch error in the java/C # code of the corresponding Event Response in the URL. The error message should also be displayed on the interface.

3) for a scheduled task, try catch should be placed in the scheduled task class. When the scheduled task class calls the dao/service/tool class, the called functions should not have try catch. The error information should be recorded in the log.

4) if you do not need the MVC jsp/asp.net program, it is very troublesome to try catch. We recommend that you do not use this software architecture.

 

I think the correct code should be like this:

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.List;import org.apache.commons.dbutils.DbUtils;public class MyJdbcUitls {    public int updateData(Connection con, String sql, List<Object> paramValueList) throws SQLException {        // int resultRow = 0; try{        // Connection con = ...        // statement = con.createStatement();        // resultRow = statement.executeUpdate(sql);        // ... } catch (SQLException e) {        // e.printStackTrace(); }        // return resultRow; }}        PreparedStatement ps = null;        try {            ps = con.prepareStatement(sql);            if (paramValueList != null) {                for (int i = 0; i < paramValueList.size(); i++) {                    setOneParameter(i, ps, paramValueList.get(i));                }            }            int count = ps.executeUpdate();            return count;        } finally {            DbUtils.closeQuietly(ps);        }    }}

Note:

The reason for the connection to be passed in from the outside is that the update function cannot be determined yet. The actual business logic is that the update function is a transaction, or combine multiple update/delete to make a transaction.

 

Supplement:

Database Transaction control should be independent from the database access layer. Here is a correct control process:

Click-database transaction control layer-call one or more data access layer functions-the code is returned to the database transaction control layer and the commit/rollback is determined.

 

The reason for this is: you cannot avoid calling multiple data access layer functions in the Code consecutively. If in each data access layer function, commit/rollback, there will be multiple database transactions in the entire operation. The following isErrorProcess:

Click -- call one or more data access layer functions (each function contains commit/rollback ).

 

You can write a JdbcTransactionUtils class, which includes the following functions:

    public static void doWithJdbcTransactionDefaultCommit(SqlRunnable run, Connection con) {        doWithJdbcTransactionNoCommitRollback(run, con);        try {            con.commit();        } catch (Exception e) {            Log log = LogFactory.getLog(JdbcTransactionUtils.class);            log.error(e.getMessage(), e);            try {                con.rollback();            } catch (Exception err) {                log.error(err.getMessage(), err);            }            throw new NestableRuntimeException(e.getMessage(), e);        }    }

Avoid making commit/rollback a public function, because other programmers may miss something accidentally. To write public functions, you must be easy to use and make them easy to use.

The preceding database transaction control functions can be implemented.

However, this is not perfect. After all, sloppy programmers can still call multipleDatabase Transaction control layer, that is, calling multipleJdbcTransactionUtils.Dowithjdbctransactiondefacomcommit (). The result is as follows:

 

User click -- database transaction control layer function 1 --- Call one or more data access layer functions ---- the code returns to the database transaction control layer and determines commit/rollback --Database Transaction control layer function 2 --- Call one or more data access layer functions ---- the code is returned to the database transaction control layer and the commit/rollback.

Still not good.

 

In fact, what we expect is that every time a user clicks, the background should be a database transaction. Therefore, I mean,The database transaction control code should be merged with the background processing code of the web layer (such as the struts action, the. cs file corresponding to the asp.net page), and try catch should be processed here. Do not try catch for other called functions, such as database access functions and tool classes. After all, database access functions, such as tool classes, may be called by code in multiple places. If you write try catch in it, how can you write try catch to satisfy all the called modules, is hard to do.

 

Finally, I think the reasonable process is as follows:

Click --Click the processing program (struts action/asp.net page. cs), including try catch and database transaction control.--- Call one or more data access layer functions (no try catch) --- Call one or more tool-class functions (no try catch ).