How to print out the JDBC SQL statement on the console or log, jdbcsql

How to print out the JDBC SQL statement on the console or log, jdbcsql

Today, jdbc preparestatement is used to write SQL statement queries. However, the actual query results are inconsistent with the expected results during execution, then there is a way to output the final SQL statement in the console or Log4j log for debugging. If it is hibernate, you can configure show_ SQL to true to display the executed SQL in the console, because the pure JDBC method is used, and then you directly use the System in the program. out. println (SQL); print the SQL statement, and then print the parameters. This method is indeed acceptable. However, when an insert or Update statement is encountered, because there are a lot of matching parameters that need to be passed in the actual operation, and then the System. out. println () is a bit more written, and a dozen or more parameters need to be written. Then I wonder if I can simplify it...

/*** Display SQL statement * @ param SQL * @ param paramMap */public static void show_ SQL (Logger log, String SQL, List <String> paramList) {log.info ("# SQL: {}", SQL); log.info ("# parameter: {}", JSONArray. fromObject (paramList);/* if (null! = ParamList & paramList. size ()> 0) {for (int I = 0; I <paramList. size (); I ++) {log.info ("" + I + ":" + paramList. get (I ));}}*/}

The most primitive method is to take one by one:

// Output the SQL statement paramList. add (officeUserInfo. getUsername (); paramList. add (officeUserInfo. getPassword (); paramList. add (officeUserInfo. getCust_name (); paramList. add (officeUserInfo. getMobile (); paramList. add (String. valueOf (officeUserInfo. getState (); paramList. add (officeUserInfo. getOffice_id (); paramList. add (String. valueOf (officeUserInfo. getRole (); paramList. add (officeUserInfo. getTel (); paramList. add (officeUserInfo. getLeadPerson (); paramList. add (officeUserInfo. getMobile2 (); paramList. add (officeUserInfo. getTel2 (); paramList. add (officeUserInfo. getContact (); paramList. add (officeUserInfo. getQq (); paramList. add (officeUserInfo. getFex (); paramList. add (officeUserInfo. getAddr (); paramList. add (officeUserInfo. getAddr2 (); paramList. add (officeUserInfo. getCust_tax_code (); paramList. add (officeUserInfo. getIdcard_no (); paramList. add (officeUserInfo. getSws (); paramList. add (officeUserInfo. getSsfj (); paramList. add (String. valueOf (officeUserInfo. getUserType (); paramList. add (officeUserInfo. getCreate_user (); paramList. add (officeUserInfo. getUpdate_user (); paramList. add (officeUserInfo. getCreate_time (); paramList. add (officeUserInfo. getUpdate_time (); CommonUtil. show_ SQL (log, SQL, paramList );

This method is a little hurt. Although it is stupid, it is indeed an effective method. Later I remembered the reflection mechanism I just learned, I suddenly realized that I could get the value through the Java reflection mechanism, and then the code above can be simplified as below. I have already introduced the reflection mechanism in the previous blog, here is the value method encapsulated in the reflection tool class that is directly called:

Reflection mechanism of Java learning:
Http://blog.csdn.net/hu1991die/article/details/43793931

Reflection value:

// Map <String, String> getFieldValueMap = ClassRefUtil. getFieldValueMap (officeUserInfo); for (Entry <String, String> entry: getFieldValueMap. entrySet () {// because the returned result is a set of Map types, You Need To iterate the set, load the value to paramList, and output the paramList in the log loop. add (entry. getValue ();} CommonUtil. show_ SQL (log, SQL, paramList );

Output result:

The final output result is the same as the above one. In fact, the above result can also be a set of map types returned after the reflection value is directly cyclically output. Because paramList is used in many aspects, then the parameters are all placed in the paramList, so I feel that this is an extra option. In this case, we can directly traverse the map set to output the results. here we need to traverse the results from the map set first, then load it into the list, and traverse the list to output the final result. because many of the above are used, this is not changed.

Example:

Output result:

Then, when the following insert statements assign values through PrepareStatement pre-compilation, they can also use this reflection Value Method to assign values cyclically. However, you need to pay attention to this point: in the insert statement? The order of attributes must be exactly the same as the order of attributes defined in the POJO class. Otherwise, an incorrect match may occur when the values are assigned, it is possible that the value of B has been assigned to a, which will hurt a lot. I think those frameworks (such as Hibernate) should all be processed through reflection, I still don't know how to deal with it. I will study it another day ..