记录Hibernate 增删改 执行的SQL语句

今天发现了以前写的一个记录Hibernate执行增删改的SQL日志的功能

即记录的sql日志可以直接拿到数据库中执行

想起来当初为了这个功能费了不少周折，网上也找了好多资料，但是都无法记录确切的SQL语句  如：insert into table (name,age)  values('李刚',35);

思路： 通过Hibernate 拦截器 + hibernate事件监听器 完成此功能 

要求：hibernate版本 3.2.5.ga  其他版本在方法调用上可能有些出入，需自行调整代码。 比如3.1中就没有getRootTableName(）方法 ，至于hibernate4没有验证过

转载请注明:http://blog.csdn.net/zidasine/article/details/7242829

1、监听器

package com.sqllog;import java.io.IOException;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import org.apache.log4j.Logger;import org.hibernate.EmptyInterceptor;import org.hibernate.Transaction;/** * hibernate 拦截器 * @author Zidasine * */public class SQLInterceptor extends EmptyInterceptor {/** *  */private static final long serialVersionUID = 4147408208347438253L;private final ThreadLocal<List<String>> content = new ThreadLocal<List<String>>();private Logger logger = Logger.getLogger(SQLInterceptor.class);public void add(String sql){content.get().add(sql);}private List<String> get(){return content.get();}private void set(List<String> value){content.set(value);}private void clear(){content.remove();}@Overridepublic void afterTransactionBegin(Transaction tx) {set(new ArrayList<String>());super.afterTransactionBegin(tx);}@Overridepublic void afterTransactionCompletion(Transaction tx) {if(tx.wasCommitted()){List<String> sqls = get();DateFormat format = new SimpleDateFormat("yyyy-MM-dd");String logName = format.format(new Date());StringBuffer buff = new StringBuffer();for(int i = 0;i < sqls.size();i++){buff.append(sqls.get(i));buff.append("\r\n\r\n");}try {SQLLogHandler.writeLog(logName, true, buff.toString());} catch (IOException e) {logger.error("记录sql日志错误", e);}}clear();super.afterTransactionCompletion(tx);}}

2、日志处理器

package com.sqllog;import java.io.File;import java.io.FileWriter;import java.io.IOException;import java.io.Writer;/** * 日志处理类 * @author Zidasine * */public class SQLLogHandler {// 日志输入目的目录private static final File LOG_PARENT_DIR = new File("D:\\SQL_LOG");private static final String LOG_SUFFIX = "log";static {if (!LOG_PARENT_DIR.exists()) {LOG_PARENT_DIR.mkdirs();}}private SQLLogHandler() {}public static void writeLog(String logName, boolean append, String message) throws IOException {File log = getLog(logName);if(log == null){log = createLog(logName);}if(log == null){throw new IOException("can't create log file: "+logName+"."+LOG_SUFFIX);}Writer writer = new FileWriter(log,append);writer.write(message);writer.flush();writer.close();}private static File getLog(String logName) {File log = new File(LOG_PARENT_DIR, logName + "." + LOG_SUFFIX);return log.exists() && log.isFile() ? log : null;}private static File createLog(String logName) throws IOException {File log = new File(LOG_PARENT_DIR, logName + "." + LOG_SUFFIX);return log.createNewFile() ? log : null;}}

3、sql语句转换器

package com.sqllog;/** * 生成SQL 语句用 Hibernate Object 操作 不考虑级联删除和多对象情况 *  * @author Zidasine *  */public class SQLConvertor {/** * 非主键字段名称 */private String[] columnNames;/** * 主键字段名称 */private String[] pkColumnNames;/** * 非主键值 */private Object[] columnValues;/** * 主键值 */private Object[] pkColumnValues;/** * 表名 */private String tableName;/** * 数据库中NULL值 */private final static String NULL = "NULL";private SQLConvertor() {}private SQLConvertor(String[] pkColumnNames, Object[] pkColumnValues,String[] columnNames, Object[] columnValues, String tableName) {super();this.columnNames = columnNames;this.pkColumnNames = pkColumnNames;this.columnValues = columnValues;this.pkColumnValues = pkColumnValues;this.tableName = tableName;}public String[] getColumnNames() {return columnNames;}public void setColumnNames(String[] columnNames) {this.columnNames = columnNames;}public String[] getPkColumnNames() {return pkColumnNames;}public void setPkColumnNames(String[] pkColumnNames) {this.pkColumnNames = pkColumnNames;}public Object[] getColumnValues() {return columnValues;}public void setColumnValues(Object[] columnValues) {this.columnValues = columnValues;}public Object[] getPkColumnValues() {return pkColumnValues;}public void setPkColumnValues(Object[] pkColumnValues) {this.pkColumnValues = pkColumnValues;}public String getTableName() {return tableName;}public void setTableName(String tableName) {this.tableName = tableName;}public static SQLConvertor newInstance(String[] pkColumns,Object[] pkValues,String[] columns,Object[] values,String table){return new SQLConvertor(pkColumns,pkValues,columns,values,table);}public String toInsertSQL(){// 表的列名称String[] tableColumns = new String[columnNames.length+pkColumnNames.length];System.arraycopy(columnNames, 0, tableColumns, 0, columnNames.length);System.arraycopy(pkColumnNames, 0, tableColumns, columnNames.length, pkColumnNames.length);// 列值Object[] tableColumnValues = new Object[columnValues.length+pkColumnValues.length];System.arraycopy(columnValues, 0, tableColumnValues, 0, columnValues.length);System.arraycopy(pkColumnValues, 0, tableColumnValues, columnValues.length, pkColumnValues.length);// 拼接语句StringBuffer buff = new StringBuffer();buff.append("insert into ");buff.append(tableName);buff.append(" (");StringBuffer values = new StringBuffer();values.append("values(");int columnCount = tableColumns.length;for(int i =0;i<columnCount;i++){buff.append(tableColumns[i]);if(tableColumnValues[i]!=null){values.append("'"+tableColumnValues[i]+"'");}else{values.append(NULL);}if(i<columnCount-1){buff.append(",");values.append(",");}}buff.append(") ");values.append(")");buff.append(values);buff.append(";");return buff.toString();}public String toDeleteSQL(){StringBuffer buff = new StringBuffer();buff.append("delete from ");buff.append(tableName);buff.append(" where ");int pkCount = pkColumnNames.length;for(int i=0 ;i<pkCount;i++){buff.append(pkColumnNames[i]);buff.append("=");buff.append("'"+pkColumnValues[i]+"'");if(i<pkCount-1){buff.append(" and ");}}buff.append(";");return buff.toString();}public String toUpdateSQL(){StringBuffer buff = new StringBuffer();buff.append("update ");buff.append(tableName);buff.append(" set ");int updateColumnCount = columnNames.length;for(int i=0;i<updateColumnCount;i++){buff.append(columnNames[i]);buff.append("=");if(columnValues[i]!=null){buff.append("'"+columnValues[i]+"'");}else{buff.append(NULL);}if(i < updateColumnCount - 1){buff.append(",");}}buff.append(" where ");int pkCount = pkColumnNames.length;for(int j = 0; j < pkCount; j++){buff.append(pkColumnValues[j]);buff.append("=");buff.append("'"+pkColumnValues[j]+"'");if(j < pkCount - 1){buff.append(" and ");}}buff.append(";");return buff.toString();}}

4、hibernate 增删改监听器

package com.sqllog;import java.beans.IntrospectionException;import java.lang.reflect.InvocationTargetException;import java.util.ArrayList;import java.util.List;import org.hibernate.event.PostInsertEvent;import org.hibernate.event.PostInsertEventListener;import org.hibernate.persister.entity.AbstractEntityPersister;import org.hibernate.type.Type;public class SQLPostInsertEventListener extends SQLPropertyValues implementsPostInsertEventListener {/** *  */private static final long serialVersionUID = -6424496850696095293L;@Overridepublic void onPostInsert(PostInsertEvent event) {AbstractEntityPersister ep = (AbstractEntityPersister) event.getPersister();//主键Type idType = ep.getIdentifierType();String[] keyColumns = ep.getRootTableKeyColumnNames();Object[] keyValues = null;try {keyValues = getPkValues(idType, event.getId(), ep.getFactory());} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IntrospectionException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();}//其他列String[] propertys = ep.getPropertyNames();List<String> columnNamesList = new ArrayList<String>();for(String property : propertys){String[] columns = ep.getPropertyColumnNames(property);for(String column : columns){columnNamesList.add(column);}}Object[] values = getValues(event.getState(),ep.getPropertyTypes(),ep.getFactory());String table = ep.getRootTableName();String[] columnArr = new String[columnNamesList.size()];SQLConvertor convertor = SQLConvertor.newInstance(keyColumns, keyValues, columnNamesList.toArray(columnArr), values, table);String sql = convertor.toInsertSQL();SQLInterceptor interceptor = (SQLInterceptor) ep.getFactory().getInterceptor();interceptor.add(sql);}}

package com.sqllog;import java.beans.IntrospectionException;import java.lang.reflect.InvocationTargetException;import java.util.ArrayList;import java.util.List;import org.hibernate.event.PostUpdateEvent;import org.hibernate.event.PostUpdateEventListener;import org.hibernate.persister.entity.AbstractEntityPersister;public class SQLPostUpdateEventListener extends SQLPropertyValues implementsPostUpdateEventListener {/** *  */private static final long serialVersionUID = 5734527340990478299L;@Overridepublic void onPostUpdate(PostUpdateEvent event) {AbstractEntityPersister ep = (AbstractEntityPersister) event.getPersister();//主键String[] keyColumns = ep.getRootTableKeyColumnNames();Object[] keyValues = null;try {keyValues = getPkValues(ep.getIdentifierType(), event.getId(), ep.getFactory());} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IntrospectionException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();}String[] propertys = ep.getPropertyNames();List<String> columnNamesList = new ArrayList<String>();for(String property : propertys){String[] columns = ep.getPropertyColumnNames(property);for(String column : columns){columnNamesList.add(column);}}Object[] values = getValues(event.getState(),ep.getPropertyTypes(),ep.getFactory());String table = ep.getRootTableName();String[] columnArr = new String[columnNamesList.size()];SQLConvertor convertor = SQLConvertor.newInstance(keyColumns, keyValues, columnNamesList.toArray(columnArr), values, table);String sql = convertor.toUpdateSQL();SQLInterceptor interceptor = (SQLInterceptor) ep.getFactory().getInterceptor();interceptor.add(sql);}}

package com.sqllog;import java.beans.IntrospectionException;import java.lang.reflect.InvocationTargetException;import org.hibernate.event.PostDeleteEvent;import org.hibernate.event.PostDeleteEventListener;import org.hibernate.persister.entity.AbstractEntityPersister;public class SQLPostDeleteEventListener extends SQLPropertyValues implements PostDeleteEventListener {/** *  */private static final long serialVersionUID = -8623174144121579378L;@Overridepublic void onPostDelete(PostDeleteEvent event) {AbstractEntityPersister ep = (AbstractEntityPersister) event.getPersister();//主键String[] keyColumns = ep.getRootTableKeyColumnNames();Object[] keyValues = null;try {keyValues = getPkValues(ep.getIdentifierType(), event.getId(), ep.getFactory());} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IntrospectionException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();}String table = ep.getRootTableName();SQLConvertor convertor = SQLConvertor.newInstance(keyColumns,keyValues,null,null,table);String sql = convertor.toDeleteSQL();SQLInterceptor interceptor = (SQLInterceptor) ep.getFactory().getInterceptor();interceptor.add(sql);}}

package com.sqllog;import java.beans.IntrospectionException;import java.beans.PropertyDescriptor;import java.io.Serializable;import java.lang.reflect.InvocationTargetException;import org.hibernate.engine.Mapping;import org.hibernate.type.ComponentType;import org.hibernate.type.EntityType;import org.hibernate.type.Type;public class SQLPropertyValues {protected Object[] getPkValues(Type type,Serializable id,Mapping factory) throws IntrospectionException, IllegalArgumentException, IllegalAccessException, InvocationTargetException{Object[] keyValues = null;if(type instanceof ComponentType){ComponentType keyType = (ComponentType) type;Type[] subTypes = keyType.getSubtypes();String[] keyNames = keyType.getPropertyNames();keyValues = new Object[subTypes.length];for(int i=0;i<subTypes.length;i++){Type t = subTypes[i];PropertyDescriptor pd = new PropertyDescriptor(keyNames[i],id.getClass());Object o = pd.getReadMethod().invoke(id);if(t instanceof EntityType){EntityType ty = (EntityType)t;String key = ty.getIdentifierOrUniqueKeyPropertyName(factory);PropertyDescriptor p = new PropertyDescriptor(key,o.getClass());Object v = p.getReadMethod().invoke(o);keyValues[i] = v;}else{keyValues[i]=o;}}}else{keyValues = new Object[]{id};}return keyValues;}protected Object[] getValues(Object[] values,Type[] types,Mapping factory){Object[] results = new Object[values.length];for(int i = 0;i < types.length; i++){Type type = types[i];if(type instanceof EntityType){EntityType entityType = (EntityType)type;String key = entityType.getIdentifierOrUniqueKeyPropertyName(factory);Object o = values[i];if(o != null){try {PropertyDescriptor pd = new PropertyDescriptor(key,o.getClass());Object val = pd.getReadMethod().invoke(o);results[i] = val;} catch (IntrospectionException e) {e.printStackTrace();} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();}}else{results[i] = o;}}else{results[i] = values[i];}}return results;}}

applicationContext.xml

<bean id="sqlInterceptor" class="com.sqllog.SQLInterceptor" /><bean id="insertListener" class="com.sqllog.SQLPostInsertEventListener" /><bean id="updateListener" class="com.sqllog.SQLPostUpdateEventListener" /><bean id="deleteListener" class="com.sqllog.SQLDeleteEventListener" /><bean id="sessionFactory" ......  <property name="eventListeners">       <map>          <entry key="post-commit-insert">                <ref local="insertListener"/>           </entry>           <entry key="post-commit-update">                <ref local="updateListener"/>           </entry>           <entry key="post-commit-delete">                <ref local="deleteListener"/>           </entry>       </map>  </property>

缺点：对于hibernate级联操作不支持

对于主键 如果是联合主键只处理了2层

功能虽然不算强大 基本可以满足需求 。。

至于如果有其他需求可以进行修改

由于源代码在内网中 以上代码为手动敲入，如果错误请见谅。