How to print out the SQL statements executed by JDBC in the console or log

Today, using the JDBC preparestatement to write SQL statements query, but in the process of executing the actual query results and expected results are inconsistent, and then you want to be able to output in the console or the log4j logs the final execution of the SQL statement, in order to debug. If it is hibernate you can display the executed SQL in the console by configuring Show_sql to True, because you are using pure JDBC notation, and then you go directly through SYSTEM.OUT.PRINTLN (SQL) in your program, print the SQL statement, and then print the parameters , and this way is really possible. However, when encountering an INSERT statement or UPDATE statement, because the actual operation needs to pass in a bit more matching parameters, and then through SYSTEM.OUT.PRINTLN () this way to write a bit more, more than 10 parameters need to write more than 10 lines, and then just think, Is there any way we can simplify ...

/**     * 显示SQL语句     * @param sql     * @param paramMap     */    publicstaticvoidshow_sql(Logger log,String sql,List<String> paramList){        log.info("##sql:{}",sql);        log.info("##参数:{}",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, directly one takes:

Output 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);

Then this way is a bit of injury, although relatively stupid, but really effective way, and then think of the previous time just learn the reflection mechanism, suddenly realize that it seems to be possible through the Java reflection mechanism directly get value, and then the above code can be simplified into the following, About the reflection mechanism has been introduced in the previous blog, which is also the direct invocation of the reflection tool class encapsulated in the value method:

The reflection mechanism of Java Learning:
http://blog.csdn.net/hu1991die/article/details/43793931

Take advantage of reflection values:

//value by reflection            Map<String,String>Getfieldvaluemap=Classrefutil.Getfieldvaluemap (Officeuserinfo); for (Entry<String,String>Entry:getfieldvaluemap.EntrySet ()) {//Because a collection of map types is returned, then the collection needs to be iterated, then the values are loaded into paramlist, and the last log loop outputParamlist.Add (entry.GetValue ()); } commonutil.Show_sql (Log, SQL, paramlist);

Output Result:

The final output and the above is consistent, the above can actually be directly loop output through reflection to return the collection of map type, because there are many places in front of the paramlist, and then the parameters are placed in the paramlist, so feel this place superfluous, It would have been directly possible to traverse the map collection to output the results, and here we have to go through the map collection, and then loaded into the list, and then traverse the list output The final result,, because many places in front of the use, so this place is not changed.

Example:

Output Result:

Then the following insert statement, through the Preparestatement pre-compilation way to assign value, in fact, it is possible to use this reflection to value the way, the loop assignment, but this place seems to need to pay attention to: in the INSERT statement? The order must be identical to the order of the attributes defined in the Pojo class, or it is possible to assign a value when the match is not correct, it is possible to assign a value of B position to a, then the injury is large, I think those frameworks (such as hibernate, etc.) should be processed by reflection, I do not know how to deal with it, another day to study the study.

How to print out the SQL statements executed by JDBC in the console or log