[SQL]: How to simplify JDBC code

Question proposal

In an application, processing JDBC operations is a high repetition rate. When you execute an SQL query on a JDBC data source, you usually need to perform the following steps:

1. Generate SQL statements

2. Obtain the connection

3. Get a preparedstatement object

4. Set the value to be sent to the database in the preparedstatement object.

5. Execute SQL statements

6. process query results

In addition, you also need to handle sqlexception exceptions. If the steps listed above are scattered across all parts of the program, multiple try/catch blocks are required in the program to handle exceptions.

If we take a closer look at the steps listed above, we will find that the program code involved in these steps will not change significantly when executing different SQL statements: we use the same method to obtain the database connection and preperedstatement object; Use the setxxx method to set the value in the preperedstatement object; the process of processing the SQL query results remains unchanged. In this article, we define three JDBC models to remove the three steps in the above six steps, which makes the entire process simpler and more universal.

Query Model

We have defined a class named sqlprocessor, which defines an executequery () method to execute SQL statements. We try to keep the code concise when implementing this method, and pass as few parameters as possible to this method. The following is the definition of this method:

public Object[] executeQuery(String sql, Object[] pStmntValues, ResultProcessor processor);

We know that there are three factors that change during the execution of SQL statement JDBC: SQL statement, preparedstatement object, and how to interpret and process the query results. In the preceding method definition, the SQL statement is saved in the SQL statement. The pstmntvalues object array stores the values in the preparedstatement object. The processor parameter is an object that can process the query results, therefore, we divide the objects involved in the JDBC program into three parts. Next let's take a look at the implementation of executequery () and some methods related to it:

Public class sqlprocessor {public object [] executequery (string SQL, object [] pstmntvalues, resultprocessor processor) {// obtain connection conn = connectionmanager. getconnection (); // redirects SQL statement execution to the handlquery () method object [] Results = handlequery (SQL, pstmntvalues, processor, Conn ); // close the connection closeconn (conn); // return results;} protected object [] handlequery (string SQL, object [] pstmntvalues, resultp Rocessor processor, connection conn) {// get a preparedstatement object preparedstatement statement Nt = NULL; try {// get preparedstatement statement Nt = Conn. preparestatement (SQL); // sends the value if (pstmntvalues! = NULL) {preparedstatementfactory. buildstatement (sequence nt, pstmntvalues);} // execute the SQL statement resultset rs = stmnt.exe cutequery (); // obtain the query result object [] Results = processor. process (RS); // close the preparedstatement object closesponnt (Response NT); // return the result return results; // handle the exception} catch (sqlexception e) {string message = & quot; unable to execute the query statement & quot; + SQL; // close all resources closeconn (conn); close0000nt (transaction NT ); // throw databasequeryexception throw new databasequeryexception (Message );}}}...}

The program has two methods to describe: preparedstatementfactory. buildstatement () and processor. Process (). The buildstatement () method sends all objects in the pstmntvalues object array to the corresponding position in the preparestatement object. For example:

... // Retrieve the value of each object in the object array, // set the corresponding value for (INT I = 0; I & lt; values. length; I ++) {// if the object value is null, set the SQL null value if (value instanceof nullsqltype) {limit nt. setnull (I + 1, (nullsqltype) value ). getfieldtype ();} else {response nt. setobject (I + 1, value );}}

Because the jsonnt. setoject (INT index, object Value) method cannot accept an empty object as a parameter. To enable the program to process null values, we use our own nullsqltype class. When an nullsqltype object is initialized, the SQL type of the corresponding column in the database table is saved. In the preceding example, we can see that the nullsqltype object stores an actual SQL type corresponding to SQL null. We use the getfieldtype () method of the nullsqltype object to fill in a null value to the preparedstatement object.

Let's take a look at the processor. Process () method. The processor class implements the resultprocessor interface, which is used to process SQL query results. It has only one method, process (). This method returns an array of objects generated after processing SQL query results.

public interface ResultProcessor { public Object[] process(ResultSet rs) throws SQLException;}

A typical Implementation Method of process () is to traverse the resultset object returned after the query and convert the value stored in the resultset object into the corresponding object into the object array. The following example shows how to use these classes and interfaces. For example, if we need to retrieve user information from a database user information table, the table name is user:

Column name	Data Type
ID	Number
Username	Varchar2
Email	Varchar2

We need to define a class user in the program to map the above table:

public User(int id, String userName, String email)

If we use the conventional method to read data from the user table, we need a method to read data from the database table and then send the data to the user object. In addition, once the query statement changes, we need to modify a lot of code. Let's take a look at the solutions described in this article.

First, construct an SQL statement.

private static final String SQL_GET_USER = "SELECT * FROM USERS WHERE ID = ?";

Create an instance class of the resultprocessor interface, through which we can obtain a user object from the query results.

Public class userresultprocessor implements resultprocessor {// column name definition (Omitted )... public object [] process (resultset RS) throws sqlexception {// use the list object to save all returned user objects List users = new arraylist (); User user = NULL; // If the query result is valid, process the query result while (RS. next () {user = new user (RS. getint (column_id), RS. getstring (column_username), RS. getstring (column_email); users. add (User);} return users. toarray (new user [users. size ()]);

Finally, put the commands for executing SQL queries and returning the user object into the getuser () method.

Public user getuser (INT userid) {// generate a sqlprocessor object and run sqlprocessor processor = new sqlprocessor (); object [] users = processor.exe cutequery (SQL _get_user_by_id, new object [] {New INTEGER (userid)}, new userresultprocessor (); // return the first user object returned (User) users [0];}

This is all we need to do: we only need to implement a processor class and a getuser () method. Compared with the traditional JDBC program, in the model described in this article, we do not need to process database connection operations and generate the preparestatement object and exception handling code. To query the user ID in the same table based on the user name, we only need to declare a new query statement in the code, and then reuse most of the Code in the userresultprocessor class.

Update Model

What if updates are involved in SQL statements? We can use a method similar to the design query model to design the update model. We need to add some new methods to the sqlprocessor class. These methods are similar to the executequery () and handlequery () methods, but we need to change the code for processing the resultset object and use the number of updated rows as the return value of the method.

Public void executeupdate (string SQL, object [] pstmntvalues, updateprocessor processor) {// obtain database connection conn = connectionmanager. getconnection (); // execute the SQL statement handleupdate (SQL, pstmntvalues, processor, Conn); // close the connection closeconn (conn);} protected void handleupdate (string SQL, object [] pstmntvalues, updateprocessor processor, connection conn) {preparedstatement partition Nt = NULL; try {partition Nt = Conn. P Reparestatement (SQL); // sends the value if (pstmntvalues! = NULL) {preparedstatementfactory. buildstatement (sequence nt, pstmntvalues);} // execute the update statement int rows = stmnt.exe cuteupdate (); // count how many rows of data are updated processor. process (rows); closesponnt (transaction NT); // Exception Handling} catch (sqlexception e) {string message = & quot; unable to execute the query statement & quot; + SQL; closeconn (conn); close0000nt (transaction NT); throw new databaseupdateexception (Message );}}

The two methods above differ from the Query Processing Method in how they process the return value. Because the update statement only needs to return the number of updated rows, we do not need to process the results returned by the SQL operation. In fact, in some cases, even the number of updated rows does not need to be returned. The reason for doing so is to confirm that the update operation has been completed in some cases.

We designed the updateprocessor interface to process the number of updated rows returned by the update operation.

public interface UpdateProcessor { public void process(int rows);}

For example, you need to ensure the update operation in the program to update at least one record in the table. In the implementation class of the updateprocessor interface, you can add a check for the number of modified rows. When no record is updated, the processor () method can throw a custom exception; you can also record the number of updated rows to the log file, or activate a custom update event. All in all, you can do anything in it.

The following is an example of using the update model:

First, generate an SQL statement

private static final String SQL_UPDATE_USER = "UPDATE USERS SET USERNAME = ?, EMAIL = ? WHERE ID = ?";

Implements the updateprocessor interface. In the processor () method, check whether the update operation updates data. If no, an illegalstateexception is thrown.

Public class mandatoryupdateprocessor implements updateprocessor {public void process (INT rows) {If (rows & lt; 1) {string message = & quot; the update operation does not update data in the database table. & Quot; throw new illegalstateexception (Message );}}}

Finally, execute the update operation in the updateuser () method and process the result.

public static void updateUser(User user) { SQLProcessor sqlProcessor = new SQLProcessor(); sqlProcessor.executeUpdate(SQL_UPDATE_USER, new Object[] {user.getUserName(), user.getEmail(), new Integer(user.getId())}, new MandatoryUpdateProcessor());}

 

Transaction Model

In a database, the difference between a transaction and an independent SQL statement is that the transaction uses a database connection during its life cycle, and the autocommit attribute must be set to false. Therefore, we need to specify the start time and end time of the transaction, and commit the transaction at the end of the transaction. We can reuse most of the Code in sqlprocessor to process transactions. At the beginning, the reader may ask why to put the update execution and update processing tasks in executeupdate () and handleupdate () completed in two functions -- they can actually be merged into the same function. The reason for this is to separate the code for processing database connections from the code for processing SQL operations. This scheme facilitates coding for transaction models that need to share database connections between multiple SQL operations.

In a transaction, we need to save the transaction status, especially the database connection status. The preceding sqlprocessor has no saved state attribute. To ensure reuse of the sqlprocessor class, we have designed a packaging class that encapsulates the sqlprocessor class, it also maintains the state of the transaction within the lifecycle.

Public class sqltransaction {private sqlprocessor; private connection conn; // default constructor. This method initializes the database connection and sets autocommit to false... public void executeupdate (string SQL, object [] pstmntvalues, updateprocessor processor) {// obtain the result. If the update operation fails, roll back to the transaction start point and throw an exception try {sqlprocessor. handleupdate (SQL, pstmntvalues, processor, Conn);} catch (databaseupdateexception e) {rollbacktransaction (); throw E;} public void committransaction () {// transaction end, submit update and recycle resource try {Conn. commit (); sqlprocessor. closeconn (conn); // if an exception occurs, roll back to the transaction start point and reclaim resources} catch (exception e) {rollbacktransaction (); throw new databaseupdateexception (& quot; unable to submit the current transaction & quot;) ;}} private void rollbacktransaction () {// roll back to the transaction start point and reclaim the resource try {Conn. rollback (); Conn. setautocommit (true); sqlprocessor. closeconn (conn); // if an exception occurs during rollback, ignore this exception} catch (sqlexception e) {sqlprocessor. closeconn (conn );}}}

Some new methods are introduced in sqltransaction. These methods are mainly used to process database connections and perform transaction management. When a transaction starts, the sqltransaction object obtains a new database connection and sets the autocommit of the connection to false. All subsequent SQL statements use the same connection.

Transactions are committed only when committransaction () is called. If an exception occurs during SQL statement execution, the program will automatically send a rollback application to restore the changes made by the program to the database. Developers do not need to worry about rolling back or closing the connection after an exception occurs. The following is an example of using the transaction model.

Public static void updateusers (User [] users) {// start transaction sqltransaction trans = sqlprocessor. starttransaction (); // update data user = NULL; For (INT I = 0; I & lt; users. length; I ++) {user = users [I]; trans.exe cuteupdate (SQL _update_user, new object [] {user. getUserName (), user. getfirstname (), user. getlastname (), user. getemail (), new INTEGER (user. GETID ()}, new mandatoryupdateprocessor ();} // submit the transaction trans. committransaction ();}

In this example, we only use update statements (in most cases, transactions are composed of update operations). The implementation of query statements is similar to that of update statements.

Problem

When using the models mentioned above, I encountered some problems. The following is a summary of these problems and I hope to help you.

Custom database connection

When a transaction is processed, it is possible that multiple transactions coexist and the databases they use are connected differently. Connectionmanager needs to know which connection it should retrieve from the database connection pool. You can modify the model to meet the preceding requirements. For example, in the executequery () and executeupdate () methods, you can take database connections as parameters and send them to connectionmanager objects. Remember that all connection management should be placed in the executexxx () method. Another solution is more object-oriented. It is a constructor that passes a connection factory as a parameter to sqlprocessor. For different connection factory types, we need different sqlprocessor objects.

Returned value of the resultprocessor class: Object array or list?

Why does the process () method in the resultprocessor interface return an array of objects? Why not use the list class? This is because in many practical applications, SQL queries return a row of data in most cases. In this case, using the list object will be redundant. However, if you are sure that the SQL query will return multiple rows of results, you can use the list object.

Database Operation exception

We can use multiple custom database operation exception classes to replace sqlexception exceptions that occur during running. It is best to inherit the runtimeexception class when these custom exception classes, so that these exceptions can be processed in a centralized manner. You may think that the exception handling should be placed in the place where the exception occurs. However, one of the purposes of designing this model is to remove or weaken the exception handling part in JDBC application development. Only runtimeexception can be used for this purpose.