MyBatis打印输出SQL语句

作者：--    发布时间：2019-11-20

hibernate是可以配置 show_sql 显示 自动生成的sql 语句，用 format_sql 可以格式化sql 语句，但如果用 mybatis 怎么实现这个功能呢？如果你搜索看一下，基本都是通过配置日志来实现的，比如配置我们最常用的 log4j.properties 来实现。

首页我们创建一个 java 工程叫作：mybatis12，内容与之前 mybatis+spring 差不多，实现一个通过指定用户id并读取其订单列表，来观察sql的执行情况。其工程目录结构如下：

log4j.properties 内容如下：

# by h3.com
log4j.rootlogger=debug,stdout,logfile
log4j.appender.stdout=org.apache.log4j.consoleappender
#log4j.appender.stdout.target=system.err
log4j.appender.stdout.layout=org.apache.log4j.simplelayout
log4j.appender.logfile=org.apache.log4j.fileappender
log4j.appender.logfile.file=c:/mybatis_show_sql.log
log4j.appender.logfile.layout=org.apache.log4j.patternlayout
log4j.appender.logfile.layout.conversionpattern=%d{yyyy-mm-dd hh:mm:ss} %f %p %m%n
 
log4j.logger.com.ibatis=debug
log4j.logger.com.ibatis.common.jdbc.simpledatasource=debug
log4j.logger.com.ibatis.common.jdbc.scriptrunner=debug
log4j.logger.com.ibatis.sqlmap.engine.impl.sqlmapclientdelegate=debug
log4j.logger.java.sql.connection=debug
log4j.logger.java.sql.statement=debug
log4j.logger.java.sql.preparedstatement=debug

主类测试代码如下：

import java.io.reader;
import java.text.messageformat;
import java.util.list;

import org.apache.ibatis.io.resources;
import org.apache.ibatis.session.sqlsession;
import org.apache.ibatis.session.sqlsessionfactory;
import org.apache.ibatis.session.sqlsessionfactorybuilder;

import com.h3.dao.iuser;
import com.h3.pojo.user;

public class main {
	private static sqlsessionfactory sqlsessionfactory;
	private static reader reader;

	static {
		try {
			reader = resources.getresourceasreader("config/configure.xml");
			sqlsessionfactory = new sqlsessionfactorybuilder().build(reader);
		} catch (exception e) {
			e.printstacktrace();
		}
	}

	public static sqlsessionfactory getsession() {
		return sqlsessionfactory;
	}

	/**
	 * @param args
	 */
	public static void main(string[] args) {
		// todo auto-generated method stub
		sqlsession session = sqlsessionfactory.opensession();
		try {
			//user user = (user) session.selectone(
			//		"com.h3.mybatis.models.usermapper.getuserbyid", 1);
			iuser iuser = session.getmapper(iuser.class);
			getuserlist();
			//testinsert();
			testupdate();
			//testdelete();
			
		} finally {
			session.close();
		}
	}
	// 
	public static void testinsert()
	{
		try
		{
			sqlsession session = sqlsessionfactory.opensession();
			iuser usermapper = session.getmapper(iuser.class);
			system.out.println("test insert start...");
			user user = new user();
			user.setid(0);
			user.setname("google");
			user.setdept("tech");
			user.setwebsite("http://www.google.com");
			user.setphone("120");
			usermapper.insertuser(user);
			session.commit();

			system.out.println("\r\nafter insert");
			getuserlist();
			system.out.println("test insert finished...");
		}
		catch (exception e)
		{
			e.printstacktrace();
		}
	}
	
	// 用户列表
	public static void getuserlist(){
		try
		{
			sqlsession session = sqlsessionfactory.opensession();
			iuser iuser = session.getmapper(iuser.class);
			system.out.println("test get start...");
			printusers(iuser.getuserlist());
			system.out.println("test get finished...");
		}catch (exception e)
		{
			e.printstacktrace();
		}
	}
	public static void testupdate()
	{
		try
		{
			sqlsession session = sqlsessionfactory.opensession();
			iuser iuser = session.getmapper(iuser.class);
			system.out.println("test update start...");
			printusers(iuser.getuserlist());
			user user = iuser.getuser(1);
			user.setname("new name");
			iuser.updateuser(user);
			session.commit();
			system.out.println("\r\nafter update");
			printusers(iuser.getuserlist());
			system.out.println("test update finished...");
		}catch (exception e)
		{
			e.printstacktrace();
		}
	}
	
	public static void testdelete()
	{
		try
		{
			sqlsession session = sqlsessionfactory.opensession();
			iuser iuser = session.getmapper(iuser.class);
			system.out.println("test delete start...");
			system.out.println("before delete");
			printusers(iuser.getuserlist());
			iuser.deleteuser(3);
			session.commit();
			system.out.println("\r\nafter delete");
			printusers(iuser.getuserlist());
			system.out.println("test delete finished...");
		}catch (exception e)
		{
			e.printstacktrace();
		}
	}
	/**
	 * 
	 * 
	 * @param users
	 */
	private static void printusers(final list<user> users)
	{
		int count = 0;

		for (user user : users)
		{
			system.out.println(messageformat.format("============= user[{0}]=================", ++count));
			system.out.println("user id: " + user.getid());
			system.out.println("user name: " + user.getname());
			system.out.println("user dept: " + user.getdept());
			system.out.println("user website: " + user.getwebsite());
		}
	}
}

执行后，在myeclise终端输出结果如下：

debug - logging initialized using 'class org.apache.ibatis.logging.commons.jakartacommonsloggingimpl' adapter.
debug - pooleddatasource forcefully closed/removed all connections.
debug - pooleddatasource forcefully closed/removed all connections.
debug - pooleddatasource forcefully closed/removed all connections.
debug - pooleddatasource forcefully closed/removed all connections.
test get start...
debug - opening jdbc connection
debug - created connection 22927632.
debug - setting autocommit to false on jdbc connection [com.mysql.jdbc.jdbc4connection@15dd910]
debug - ooo using connection [com.mysql.jdbc.jdbc4connection@15dd910]
debug - ==>  preparing: select * from user 
debug - ==> parameters: 
debug - <==      total: 2
============= user[1]=================
user id: 1
user name: new name
user dept: tech
user website: http://www.h3.com
============= user[2]=================
user id: 2
user name: hevi
user dept: tech
user website: http://www.baidu.com
test get finished...
test update start...
debug - opening jdbc connection
debug - created connection 33189144.
debug - setting autocommit to false on jdbc connection [com.mysql.jdbc.jdbc4connection@1fa6d18]
debug - ooo using connection [com.mysql.jdbc.jdbc4connection@1fa6d18]
debug - ==>  preparing: select * from user 
debug - ==> parameters: 
debug - <==      total: 2
============= user[1]=================
user id: 1
user name: new name
user dept: tech
user website: http://www.h3.com
============= user[2]=================
user id: 2
user name: hevi
user dept: tech
user website: http://www.baidu.com
debug - ooo using connection [com.mysql.jdbc.jdbc4connection@1fa6d18]
debug - ==>  preparing: select * from user where id=? 
debug - ==> parameters: 1(integer)
debug - <==      total: 1
debug - ooo using connection [com.mysql.jdbc.jdbc4connection@1fa6d18]
debug - ==>  preparing: update user set name = ?, dept = ?, website = ?, phone = ? where id = ? 
debug - ==> parameters: new name(string), tech(string), http://www.h3.com(string), 13800009988(string), 1(integer)
debug - <==    updates: 1
debug - committing jdbc connection [com.mysql.jdbc.jdbc4connection@1fa6d18]

after update
debug - ooo using connection [com.mysql.jdbc.jdbc4connection@1fa6d18]
debug - ==>  preparing: select * from user 
debug - ==> parameters: 
debug - <==      total: 2
============= user[1]=================
user id: 1
user name: new name
user dept: tech
user website: http://www.h3.com
============= user[2]=================
user id: 2
user name: hevi
user dept: tech
user website: http://www.baidu.com
test update finished...

代码下载：http://pan.baidu.com/s/1jgk165o

jar 包下载：http://pan.baidu.com/s/1bnyrj9h