-------- This is a good resource seen on the Internet, collect it --------
statement of problem
In an application, the operation of processing JDBC is a high repetition rate. When you perform SQL queries on a JDBC data source, you usually need to do the following steps: 1. Generate SQL statement 2. Get a connection 3. Get a preparedStateMent object
4. Set the value that needs to be sent to the database 5. Execute SQL statement 6. Except for the result of the processing query, you also need to handle SQLException exceptions. If these steps listed above are dispersed in various parts of the program, multiple TRY / CATCH blocks are required to process exceptions. If we look at the steps listed above, we will find that the program code involved in these steps in these steps will not be large when performing different SQL statements: We use the same method to get database connections and preperedstate physical objects; use Setxxx method to set the value in the preperedStatement object; the process of processing SQL query results is basically constant. In this article, we remove three steps in the above six steps by defining three JDBC models, which makes the entire process easier and has better versatility.
Query model we define a class named SQLProcessor, defined a executeQuery () method to perform SQL statements in this class, we try to keep your code's simpleness when implementing this method, and transfer as few parameters as possible this method. Below is the definition of this method:
Public Object [] ExecuteQuery (String SQL, Object [] PstmntValues,
ResultProcessor Processor;
We know that in the JDBC process of the SQL statement, we have three factors: SQL statement, preparedStatement object, and how to explain and process query results. In the method definition above, SQL is saved in SQL statement; the PSTMNTVALUES object array saves a value that needs to be placed in the preparedStatement object; the Processor parameter is an object that can handle the results of the query, so we involve the object involved in the JDBC program Divided into three parts. Let's take a look at ExecuteQuery () and some ways related to it:
Public class sqlprocessor {
Public Object [] ExecuteQuery (String SQL, Object [] PstmntValues,
Resultprocessor processor) {
// Get a connection
Connection conn = connectionManager.getConnection ();
/ / Redirect the execution of the SQL statement to the handlQuery () method
Object [] Results = HandleQuery (SQL, PSTMNTVALUES, Processor, CONN);
// Close connection
CloseConn (CONN);
// Return the result
Return Results;
}
Protected Object [] HandleQuery (String SQL, Object [] PstmntValues,
Resultprocessor Processor, Connection Conn
{
// Get a prepaaredStatement object
PreparedStatement Stmnt = NULL;
Try {
// get prepaaredStatement
STMNT = conn.preparestatement (SQL); / / Send value to PreparedStatement
IF (PSTMNTVALUES! = null) {
PreparedStatementFactory.BUILDStatement (stmnt, pstmntvalues);
}
/ / Execute SQL statement
ResultSet RS = stmnt.executeQuery ();
// Get the results of the query
Object [] results = processor.process (RS);
// Close PreparedStateMent object
Closestmnt (STMNT);
// Return the result
Return Results;
// Treatment exception
} catch (sqlexception e) {
String message = "Unable to perform query statements" SQL;
// Turn off all resources
CloseConn (CONN);
Closestmnt (STMNT);
// Throw DatabaseQueryException
Throw New DatabaseQueryException (Message);
}
}
}
...
}
There are two ways in the program to explain: preparedStatementFactory.BuildStatement () and processor.process (). The buildStatement () method delivers all objects in the PSTMNTVALUES object array to the corresponding location in the PrepareStatement object. E.g:
...
// Remove the value of each object in an array of objects,
/ / Set the corresponding value in the corresponding position in the PreparedStatement object
For (int i = 0; i // If the value of the object is empty, set the SQL null value Value InstanceOf Nullsqltype { Stmnt.setnull (i 1, (nullsqltype) value) .GetfieldType ()); } else { STMNT.SETOBJECT (i 1, value); } } Because the Stmnt.SetOject (int index, object value) method cannot accept an empty object as a parameter. In order to make the program can handle null values, we use the NullsqlType class you designed. When a nullsqltype object is initialized, the SQL type of the corresponding column in the database table will be saved. In the above example we can see that a SQL NULL actually corresponding SQL type is saved in the properties of the NullsqlType object. We use the getFieldType () method of the NullsqlType object to fill the empty 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 the result of the SQL query. It has only one method process (), which returns an array of objects generated after processing SQL query results. Public interface resultprocessor { Public Object [] Process (ResultSet RS) throws sqlexception; } The typical implementation method of Process () is to traverse the ResultSet object returned to the query, and convert the value saved in the ResultSet object to the corresponding object to place an object array. Below we explain how to use these classes and interfaces through an example. For example, when we need to remove user information from a user information table from the database, the table name is User: Column Name Data Type IDNumberUsernamevarchar2emailvarchar2 We need to define a class user in the program to map the table above: Public user (int ID, string username, String email) If we use a general method to read data in the USER table, we need a way to read data from the database table and then send the data into the User object. And once the query statement changes, we need to modify a large number of code. Let us look at how to use the solution described herein. First construct a SQL statement. Private static final string SQL_GET_USER = "SELECT * from users where id =?" Then create an instance class of the ResultProcessor interface, which we can get a User object from the query results. Public Class UserResultProcessor Implements Resultprocessor { // Column name definition (omit) ... 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 results are valid, process the results of the query While (rs.next ()) { User = new user (rs.getint (color), rs.getstring (colorn_username), rs.getstring (column_email); Users.add (user); } Return Users.Toarray (New User "); Finally, the SQL query will be executed and the instruction to return the User object is placed in the getUser () method. Public user getuser (int userid) { / / Generate a SqlProcessor object and perform a query SqlProcessor Processor = New SqlProcessor (); Object [] users = processor.executeQuery (SQL_GET_USER_BY_ID, New Object [] {new integer (userid)}, New userResultProcessor ()); // Return the first User object to the query Return (user) Users [0]; } This is all the work we need to do: just implement a Processor class and a getUser () method. In the model described in this article, we do not need to process database connection operations, generate the code of the PREPARESTATEMENT object and an exception processing section. If you need to check the user ID in the same table, we only need to declare a new query statement in your code and then reuse most of the Code in the USERRESULTPROCESSOR class. Update model What if the update is involved in the SQL statement? We can design update models similar to design query models, and we need to add some new methods to the SqlProcessor class. These methods are similar to the executeQuery () and handlequeuery () methods, but we need to change the code for processing the ResultSet object, and the number of updated rows as the return value of the method. Public void ExecuteUpdate (String SQL, Object [] PstmntValues, UpdateProcessor Processor) { // Get database connections Connection conn = connectionManager.getConnection (); / / Execute SQL statement HandleUpdate (SQL, PSTMNTVALUES, Processor, CONN); // Close connection CloseConn (CONN); } Protected Void HandleUpdate (String SQL, Object [] PstmntValues, UpdateProcessor Processor, Connection CONN) { PreparedStatement Stmnt = NULL; Try { STMNT = conn.preparestatement (SQL); / / Send value to the PrepareStatement object IF (PSTMNTVALUES! = null) { PreparedStatementFactory.BUILDStatement (stmnt, pstmntvalues); } / / Execute an update statement Int rows = stmnt.executeUpdate (); // How many row of data is updated Processor.Process (ROWS); Closestmnt (STMNT); // abnormal processing } catch (sqlexception e) { String message = "Unable to perform query statements" SQL; CloseConn (CONN); Closestmnt (STMNT); Throw new DatabaseUpdateException (MESSAGE); } } The two methods and methods of processing queries are different in how they handle the return value. Since the update statement only needs to return the number of rows being updated, we do not need to handle the results returned by SQL operations. In fact, in some cases, even the number of rows being updated does not need to return, what we do so is that in some cases need to confirm that the update operation has been completed. We designed the UpdateProcessor interface to handle the number of updates returned by the Update operation. Public interface updateProcessor { Public Void Process (int Rows); } For example, in the program, you need to guarantee the update operation, update at least one record in the table. In the implementation class of the UpdateProcessor interface, you can add a detection of the number of rows. When no record is updated, the Processor () method can throw a custom exception; you can also record the updated row number to the log file; or Inspire a custom update event. All in all, you can do anything in it. Below is an example of using an update model: first generate a SQL statement Private static final string SQL_UPDATE_USER = "Update users set usrname =?, Email =? Where id =?"; Implement the UPDATEPROCESSOR interface. In the Processor () method, check if the UPDATE operation updates the data. If not, throw an IllegalStateException exception. Public class mandatoryupdateprocessor imports UpdateProcessor { Public void process (int rows) { IF (rows <1) { String message = "Update the operation does not update the data in the database table." Throw new IllegalStateException (Message); } } } Finally, execute the UPDATE operation and processing the results in the updateuser () method. 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 the database, the difference between transactions and independent SQL statements is that transactions use a database connection within the lifetime, and the AutoCommit property must be set to false. So we need to specify when the transaction begins, when it ends, and submits a transaction at the end of the transaction. We can reuse most of the code in SqlProcessor to handle transactions. Perhaps the reader will ask why the work to perform updates and processing updates is done in the executeUpdate () and handleUpdate () two functions - actually they can be merged into the same function. The reason for doing this is to isolate the code to process the database connection and the code to process the SQL operation. This solution is easy to encode for transaction models that need to be shared between multiple SQL operations. In the transaction, we need to save the status of the transaction, especially the status of the database connection. In the previous SqlProcessor, there is no save state, in order to ensure the reuse of the SQLProcessor class, we have designed a packaging class, which is packaged in the SQLProcessor class, and maintains the status of the transaction in the life cycle. Public class sqltransaction { Private sqlprocessor sqlprocessor; PRIVATE CONNECTION CONN; / / Default construction method, this method initializes the database connection, and sets AutoCommit to false ... Public void ExecuteUpdate (String SQL, Object [] PstmntValues, UpdateProcessor Processor) { // Get the result. If the update operation fails, roll back to the transaction starting point and throw an exception Try { SqlProcessor.HandleUpdate (SQL, PSTMNTVALUES, Processor, Conn); } catches (DatabaseUpdateExcection E) { RollbackTransaction (); Throw e; } } Public void committransaction () { // End, submit updates and recycle resources Try { CONN.COMMIT (); SqlProcessor.CloseConn (Conn); // If an exception occurs, roll back to the transaction starting point and recycle resources} Catch (Exception E) { RollbackTransaction (); Throw New DatabaseUpdateException ("I can't submit the current transaction"); } } Private void rollbacktransaction () { / / Roll back to transaction starting and recycle resources Try { CONN. ROLLBACK (); Conn.setautocommit (TRUE); SqlProcessor.CloseConn (Conn); // If an abnormality occurs during the rollback process, ignore the exception } catch (sqlexception e) { SqlProcessor.CloseConn (Conn); } } } Some new methods have occurred in SqlTransAction, which are mainly used to process database connections and transaction management. When a transaction begins, the SQLTransaction object obtains a new database connection and sets the connected AutoCommit to false, and all SQL statements are used in the same connection. Transactions will only be submitted only when committransaction () is called. If an exception occurs during the process of executing the SQL statement, the program will automatically issue a rollback request to recover the changes to the database. For developers, there is no need to worry about working back or closing the connection after an abnormality. Here is an example of using a transaction model. Public Static Void UpdateUsers (user [] users) { // Start a transaction SQLTransaction Trans = SqlProcessor.StartTransAction (); // update data User User = NULL; For (int i = 0; i User = users [i]; Trans.executeUpdate (SQL_UPDATE_USER, New Object [] {user.getusername (), User.GetfirstName (), User.getlastname (), User.getemail (), NEW integer (user.getid ())} NEW MANDAToryUpdateProcessor ()); } // Submit a transaction Trans.committransaction (); } In the example we only use the update statement (in most cases, both transactions are made by update operations), and the implementation method of query statements is similar to the update statement. problem When I actually use these models mentioned above, I have encountered some problems, the following is the summary of these problems, I hope to help everyone. Custom database connections When transaction is transaction, it is possible to use the database connection to the database connection in the case of multiple transactions. ConnectionManager needs to know which connection should be taken out from the database connection pool. You can simply modify the model to meet the requirements above. For example, in the ExecuteQuery () and ExecuteUpdate () methods, you can connect the database as a parameter and transfer them to the ConnectionManager object. Remember that all connection management should be placed in the executexxxx () method. Another solution is also a more object-oriented solution, which is a constructor that passes a connection plant as a parameter to SQLProcessor. For different connection factories, we need different SQLProcessor objects. Return Value of the ResultProcessor class: Object array or LIST? Why is the process () method in the ResultProcessor interface is an object array? Why don't you use a list class? This is because SQL query returns a line data in most cases, in which case List objects will be excessive. But if you are confident that the SQL query will return multiple lines, you can use the List object. Database operation exceptions We can use multiple custom database operation anomaly class to replace the SQLException exception that occurred at runtime. It is best to inherit the RuntimeException class when these custom anomaly classes, which can centrally process these exceptions. Maybe you will think that the abnormal handling is placed in an abnormal place. But one of the purposes of our model is to remove or weaken an exception handling in the JDBC application development, only using RuntimeException we can achieve this. ? ? ? ? ?
