Hibernate获取执行的SQL语句

今天无意间刷到有人问Hibernate怎么获取到执行的SQL语句，下面所有人都回复说代码中取不到，只能去日志文件中读取，感觉太误导人了，其实Hibernate完全是可以取到执行的SQL

 

方法一：

可以通过自定义EmptyInterceptor来实现，非常简单

 

public class TestInterceptor extends EmptyInterceptor {
	private static final long serialVersionUID = -460548083498143271L;

	@Override
		public String onPrepareStatement(String sql) {
			System.out.println(sql);
			//TODO 你想要实现的操作
			return super.onPrepareStatement(sql);
		}
}

使用方式，在sessionFactory bean中设置自定义的EmptyInterceptor ，以配置文件的方式为例

    <!--自定义的EmptyInterceptor-->
    <bean id="testInterceptor" class="com.xxx.xxx.xx.interceptor.TestInterceptor"/>

	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate5.LocalSessionFactoryBean" >
		<property name="dataSource" ref="dataSource" />
        <!--自定义的EmptyInterceptor-->
		<property name="entityInterceptor" ref="testInterceptor"/>
		<property name="packagesToScan">
			<list>
				<value>com.xxxx.**.entity.**</value>
			</list>
		</property>

		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">${spring.jpa.database-platform}</prop>
				<prop key="hibernate.dialect.storage_engine">innodb</prop>
				<prop key="hibernate.show_sql">${spring.jpa.show-sql}</prop>
				<!-- 将SQL脚本进行格式化后再输出 -->
				<prop key="hibernate.format_sql">${spring.jpa.properties.hibernate.format_sql}</prop>
				<prop key="hibernate.hbm2ddl.auto">${spring.jpa.hibernate.ddl-auto}</prop>
				<prop key="hibernate.default_batch_fetch_size">${spring.jpa.properties.hibernate.default_batch_fetch_size}</prop>
			</props>
		</property>
		<property name="implicitNamingStrategy" ref="springImplicitNamingStrategy" />
		<property name="physicalNamingStrategy" ref="springPhysicalNamingStrategy" />
	</bean>

如果使用Spring Boot的java形式配置，使用setEntityInterceptor(Interceptor entityInterceptor)方法即可

 

方法二：

实现StatementInspector接口

public class SqlInspector implements StatementInspector {
	private static final long serialVersionUID = 9132883548949230280L;

	@Override
	public String inspect(String sql) {
		System.out.println(sql);
		return sql;
	}

}

配置

<bean id="sessionFactory"
		class="org.springframework.orm.hibernate5.LocalSessionFactoryBean" >
		<property name="dataSource" ref="dataSource" />
		<property name="packagesToScan">
			<list>
				<value>com.xxxx.**.entity.**</value>
			</list>
		</property>

		<property name="hibernateProperties">
			<props>
                <!-- 拦截sql -->
				<prop key="hibernate.session_factory.statement_inspector">com.xxx.xxx.xx.interceptor.SqlInspector</prop>
				<prop key="hibernate.dialect">${spring.jpa.database-platform}</prop>
				<prop key="hibernate.dialect.storage_engine">innodb</prop>
				<prop key="hibernate.show_sql">${spring.jpa.show-sql}</prop>
				<!-- 将SQL脚本进行格式化后再输出 -->
				<prop key="hibernate.format_sql">${spring.jpa.properties.hibernate.format_sql}</prop>
				<prop key="hibernate.hbm2ddl.auto">${spring.jpa.hibernate.ddl-auto}</prop>
				<prop key="hibernate.default_batch_fetch_size">${spring.jpa.properties.hibernate.default_batch_fetch_size}</prop>
			</props>
		</property>
		<property name="implicitNamingStrategy" ref="springImplicitNamingStrategy" />
		<property name="physicalNamingStrategy" ref="springPhysicalNamingStrategy" />
	</bean>

 

补充：有人私信问配置文件的问题，不明白为什么SessionFactory的bean中的class="org.springframework.orm.hibernate5.LocalSessionFactoryBean"，为什么LocalSessionFactoryBean会变位SessionFactory？他们之间的关系是怎样的

看LocalSessionFactoryBean源码

public class LocalSessionFactoryBean extends HibernateExceptionTranslator
		implements FactoryBean<SessionFactory>, ResourceLoaderAware, BeanFactoryAware, InitializingBean, DisposableBean {

可以看到LocalSessionFactoryBean实现了FactoryBean<T>接口，再看FactoryBean<T>源码

/*
 * Copyright 2002-2020 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.springframework.beans.factory;

import org.springframework.lang.Nullable;

/**
 * Interface to be implemented by objects used within a {@link BeanFactory} which
 * are themselves factories for individual objects. If a bean implements this
 * interface, it is used as a factory for an object to expose, not directly as a
 * bean instance that will be exposed itself.
 *
 * <p><b>NB: A bean that implements this interface cannot be used as a normal bean.</b>
 * A FactoryBean is defined in a bean style, but the object exposed for bean
 * references ({@link #getObject()}) is always the object that it creates.
 *
 * <p>FactoryBeans can support singletons and prototypes, and can either create
 * objects lazily on demand or eagerly on startup. The {@link SmartFactoryBean}
 * interface allows for exposing more fine-grained behavioral metadata.
 *
 * <p>This interface is heavily used within the framework itself, for example for
 * the AOP {@link org.springframework.aop.framework.ProxyFactoryBean} or the
 * {@link org.springframework.jndi.JndiObjectFactoryBean}. It can be used for
 * custom components as well; however, this is only common for infrastructure code.
 *
 * <p><b>{@code FactoryBean} is a programmatic contract. Implementations are not
 * supposed to rely on annotation-driven injection or other reflective facilities.</b>
 * {@link #getObjectType()} {@link #getObject()} invocations may arrive early in the
 * bootstrap process, even ahead of any post-processor setup. If you need access to
 * other beans, implement {@link BeanFactoryAware} and obtain them programmatically.
 *
 * <p><b>The container is only responsible for managing the lifecycle of the FactoryBean
 * instance, not the lifecycle of the objects created by the FactoryBean.</b> Therefore,
 * a destroy method on an exposed bean object (such as {@link java.io.Closeable#close()}
 * will <i>not</i> be called automatically. Instead, a FactoryBean should implement
 * {@link DisposableBean} and delegate any such close call to the underlying object.
 *
 * <p>Finally, FactoryBean objects participate in the containing BeanFactory's
 * synchronization of bean creation. There is usually no need for internal
 * synchronization other than for purposes of lazy initialization within the
 * FactoryBean itself (or the like).
 *
 * @author Rod Johnson
 * @author Juergen Hoeller
 * @since 08.03.2003
 * @param <T> the bean type
 * @see org.springframework.beans.factory.BeanFactory
 * @see org.springframework.aop.framework.ProxyFactoryBean
 * @see org.springframework.jndi.JndiObjectFactoryBean
 */
public interface FactoryBean<T> {

	/**
	 * The name of an attribute that can be
	 * {@link org.springframework.core.AttributeAccessor#setAttribute set} on a
	 * {@link org.springframework.beans.factory.config.BeanDefinition} so that
	 * factory beans can signal their object type when it can't be deduced from
	 * the factory bean class.
	 * @since 5.2
	 */
	String OBJECT_TYPE_ATTRIBUTE = "factoryBeanObjectType";


	/**
	 * Return an instance (possibly shared or independent) of the object
	 * managed by this factory.
	 * <p>As with a {@link BeanFactory}, this allows support for both the
	 * Singleton and Prototype design pattern.
	 * <p>If this FactoryBean is not fully initialized yet at the time of
	 * the call (for example because it is involved in a circular reference),
	 * throw a corresponding {@link FactoryBeanNotInitializedException}.
	 * <p>As of Spring 2.0, FactoryBeans are allowed to return {@code null}
	 * objects. The factory will consider this as normal value to be used; it
	 * will not throw a FactoryBeanNotInitializedException in this case anymore.
	 * FactoryBean implementations are encouraged to throw
	 * FactoryBeanNotInitializedException themselves now, as appropriate.
	 * @return an instance of the bean (can be {@code null})
	 * @throws Exception in case of creation errors
	 * @see FactoryBeanNotInitializedException
	 */
	@Nullable
	T getObject() throws Exception;

	@Nullable
	Class<?> getObjectType();

	default boolean isSingleton() {
		return true;
	}

}

FactoryBean的源码注释大概意思就是实现这个接口的类实际得到的是T getObject()方法返回的对象，也就是SessionFactory对象，而不是LocalSessionFactoryBean