DB2 uses the Hibernate Interceptor to implement dirty read (withur)

Work needs: Recently, the development of the system to adapt to the underlying database to add support for DB2, although the use of DB2, but performance considerations, and business needs. The query does not require transaction control, that is, multiple transaction security levels of DB2. During the query, you do not need to pay attention to updates and inserts. Therefore, the query must support dirty reading. The SQL statement for each query must be followed

Work needs: Recently, the development of the system to adapt to the underlying database to add support for DB2, although the use of DB2, but performance considerations, and business needs. The query does not require transaction control, that is, multiple transaction security levels of DB2. During the query, you do not need to pay attention to updates and inserts. Therefore, the query must support dirty reading. The SQL statement for each query must be followed

Work needs: Recently, the development of the system to adapt to the underlying database to add support for DB2, although the use of DB2, but performance considerations, and business needs. The query does not require transaction control, that is, multiple transaction security levels of DB2. During the query, you do not need to pay attention to updates and inserts. Therefore, the query must support dirty reading. The with ur option must be added to the end of each query SQL statement.

I have been searching for it online for a long time. Many people are asking, but there is no result. Finally, find a solution on google and use the hibernate Interceptor to intercept it. The following code is used:

Import org. hibernate. emptyInterceptor; import org. slf4j. logger; import org. slf4j. loggerFactory;/*** When configuring DB2 in hibernate, in order to prevent the impact of the high transaction security level on the query, the query must be independently formulated with ur. * This type is a hibernate Interceptor Used to add the with ur option to the end of the select query to prevent locking the database during query. * @ Author superxb **/public class DB2Interceptor extends EmptyInterceptor {private static final long serialVersionUID = 1L; private static final Logger logger = LoggerFactory. getLogger (DB2Interceptor. class); @ Overridepublic String onPrepareStatement (String str) {// convert all SQL strings to lowercase String compstr = str. toLowerCase (); // All select statements, as long as they do not contain with ur. Add with urif (compstr. matches ("^ select .*")&&! Compstr. matches (". * for update. *") {if (! Compstr. matches (". * with ur. * ") {str + =" with ur "; logger. debug ("Appending \" with ur \ "to query. ") ;}} return str ;}}

After the interceptor is created, configure it in the sessionFactory of hibernate. The configuration is as follows:

 
  
   
  
  
  
   
  ............
 

After the above configuration. By default, as long as the SQL statement starting with select does not contain with ur, it will add with ur at the end to ensure the transaction security level. When hibernate is mapped to the DB2 database, attackers can perform dirty read operations.