InMyBatis

Use jDialects In MyBatis

Here use jSqlBox instead of use jDialects in MyBatis because at inside of jSqlBox included jDialects and jSqlBox offers more functions.
Exmple:

public class TestDemo {
	protected static ThreadLocal<Object[]> paginInfo = new ThreadLocal<Object[]>();

	@Table(name = "users")
	public static class User extends ActiveRecord {
		@UUID25
		@Id
		private String id;
		private String firstName;
		private String lastName;
		private Integer age;
		//getter & setters 略
	}

	public static interface UserMapper {
		@Select("select concat(firstName, ' ', lastName) as USERNAME, age as AGE from users where age>#{age}")
		List<Map<String, Object>> getOlderThan(int age);
	}

	//See https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/Interceptor.md
	@Intercepts({
			@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
					RowBounds.class, ResultHandler.class }),
			@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
					RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class }), })
	public class JDialectsPlugin implements Interceptor {

		@Override
		public Object intercept(Invocation invocation) throws Throwable {
			Object[] args = invocation.getArgs();
			MappedStatement ms = (MappedStatement) args[0];
			Object parameter = args[1];
			RowBounds rowBounds = (RowBounds) args[2];
			@SuppressWarnings("rawtypes")
			ResultHandler resultHandler = (ResultHandler) args[3];
			Executor executor = (Executor) invocation.getTarget();
			CacheKey cacheKey;
			BoundSql boundSql;
			if (args.length == 4) {
				boundSql = ms.getBoundSql(parameter);
				cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
			} else {
				cacheKey = (CacheKey) args[4];
				boundSql = (BoundSql) args[5];
			}
			if (paginInfo.get() != null) {// if paginInfo exist in threadlocal
				Configuration configuration = ms.getConfiguration();
				String pageSql = ((Dialect) paginInfo.get()[0]).paginAndTrans((Integer) paginInfo.get()[1],
						(Integer) paginInfo.get()[2], boundSql.getSql());
				BoundSql pageBoundSql = new BoundSql(configuration, pageSql, boundSql.getParameterMappings(),
						parameter);
				return executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, pageBoundSql);
			} else
				return executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
		}

		@Override
		public Object plugin(Object target) {
			return Plugin.wrap(target, this);
		}

		@Override
		public void setProperties(Properties properties) {
		}

	}

	@Test
	public void doTest() {
		HikariDataSource dataSource = new HikariDataSource();// DataSource

		// H2 is a memory database
		dataSource.setDriverClassName("org.h2.Driver");
		dataSource.setJdbcUrl("jdbc:h2:mem:DBName;MODE=MYSQL;DB_CLOSE_DELAY=-1;TRACE_LEVEL_SYSTEM_OUT=0");
		dataSource.setUsername("sa");
		dataSource.setPassword("");

		SqlBoxContext ctx = new SqlBoxContext(dataSource);
		SqlBoxContext.setDefaultContext(ctx);
		ctx.setAllowShowSQL(true);
		String[] ddlArray = ctx.toDropAndCreateDDL(User.class);
		for (String ddl : ddlArray)
			ctx.quiteExecute(ddl);
		for (int i = 1; i <= 100; i++)
			new User().put("firstName", "Foo" + i, "lastName", "Bar" + i, "age", i).insert();

		TransactionFactory transactionFactory = new JdbcTransactionFactory();
		Environment environment = new Environment("demo", transactionFactory, dataSource);
		Configuration configuration = new Configuration(environment);
		configuration.addMapper(UserMapper.class);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
		configuration.addInterceptor(new JDialectsPlugin());

		SqlSession session = null;
		try {
			session = sqlSessionFactory.openSession();
			Connection conn = session.getConnection();
			Assert.assertEquals(100, ctx.nQueryForLongValue(conn, "select count(*) from users"));

			List<Map<String, Object>> users;
			try {
				paginInfo.set(new Object[] { ctx.getDialect(), 3, 10 });
				users = session.getMapper(UserMapper.class).getOlderThan(50);
			} finally {
				paginInfo.remove();
			}
			Assert.assertEquals(10, users.size());
			for (Map<String, Object> map : users)
				System.out.println("UserName=" + map.get("USERNAME") + ", age=" + map.get("AGE"));
		} finally {
			session.close();
		}
		dataSource.close();
	}
}

Above demo used jDialects's DDL、pagination、function translate and jSqlBox's ActiveRecord.
The demo can run on different databases only need change the DataSource setting.
Full source code of the demo