package com.firefly.db;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.firefly.utils.log.Log;
import com.firefly.utils.log.LogFactory;

public class JDBCHelper {

	private final static Log log = LogFactory.getInstance().getLog("firefly-system");

	private final DataSource dataSource;
	private final QueryRunner runner;
	private BeanProcessor defaultBeanProcessor = new GenerousBeanProcessor();

	public JDBCHelper(DataSource dataSource) {
		this.dataSource = dataSource;
		this.runner = new QueryRunner(dataSource);
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public QueryRunner getRunner() {
		return runner;
	}

	public BeanProcessor getDefaultBeanProcessor() {
		return defaultBeanProcessor;
	}

	public void setDefaultBeanProcessor(BeanProcessor defaultBeanProcessor) {
		this.defaultBeanProcessor = defaultBeanProcessor;
	}

	public <T> T querySingleColumn(String sql, Object... params) {
		try {
			return runner.query(sql, new ScalarHandler<T>(), params);
		} catch (SQLException e) {
			log.error("query exception, sql: {}", e, sql);
			return null;
		}
	}

	public <T> T query(String sql, Class<T> t, Object... params) {
		return this.query(sql, t, defaultBeanProcessor, params);
	}

	public <T> T query(String sql, Class<T> t, BeanProcessor beanProcessor, Object... params) {
		try {
			return runner.query(sql, new BeanHandler<T>(t, new BasicRowProcessor(beanProcessor)), params);
		} catch (SQLException e) {
			log.error("query exception, sql: {}", e, sql);
			return null;
		}
	}

	public <T> List<T> queryForList(String sql, Class<T> t, Object... params) {
		return this.queryForList(sql, t, defaultBeanProcessor, params);
	}

	public <T> List<T> queryForList(String sql, Class<T> t, BeanProcessor beanProcessor, Object... params) {
		try {
			return runner.query(sql, new BeanListHandler<T>(t, new BasicRowProcessor(beanProcessor)), params);
		} catch (SQLException e) {
			log.error("query exception, sql: {}", e, sql);
			return null;
		}
	}

}

@@ -18,16 +18,44 @@
 ly.db;%0A%0A
+import java.sql.Connection;%0A
 import j
@@ -511,16 +511,59 @@
 ndler;%0A%0A
+import com.firefly.utils.function.Action2;%0A
 import c
@@ -2479,10 +2479,482 @@
 %09%09%7D%0A%09%7D%0A%0A
+%09public void executeTransaction(Action2%3CConnection, QueryRunner%3E action) %7B%0A%09%09try %7B%0A%09%09%09Connection connection = dataSource.getConnection();%0A%09%09%09connection.setAutoCommit(false);%0A%09%09%09try %7B%0A%09%09%09%09action.call(connection, runner);%0A%09%09%09%09connection.commit();%0A%09%09%09%7D catch (Throwable t) %7B%0A%09%09%09%09connection.rollback();%0A%09%09%09%09log.error(%22the transaction exception%22, t);%0A%09%09%09%7D finally %7B%0A%09%09%09%09connection.close();%0A%09%09%09%7D%0A%09%09%7D catch (SQLException e) %7B%0A%09%09%09log.error(%22get connection exception%22, e);%0A%09%09%7D%0A%09%7D%0A%0A
 %7D%0A

add simple transaction template