package com.firefly.db;

import java.sql.Connection;
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.function.Action2;
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;
		}
	}

	public void executeTransaction(Action2<Connection, QueryRunner> action) {
		try {
			Connection connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			try {
				action.call(connection, runner);
				connection.commit();
			} catch (Throwable t) {
				connection.rollback();
				log.error("the transaction exception", t);
			} finally {
				connection.close();
			}
		} catch (SQLException e) {
			log.error("get connection exception", e);
		}
	}

}

@@ -545,22 +545,20 @@
 unction.
-Action
+Func
 2;%0Aimpor
@@ -2485,20 +2485,21 @@
 %09public 
-void
+%3CT%3E T
  execute
@@ -2514,14 +2514,12 @@
 ion(
-Action
+Func
 2%3CCo
@@ -2543,16 +2543,17 @@
 nner
-%3E action
+, T%3E func
 ) %7B%0A
@@ -2664,22 +2664,28 @@
 y %7B%0A%09%09%09%09
-action
+T ret = func
 .call(co
@@ -2724,24 +2724,40 @@
 n.commit();%0A
+%09%09%09%09return ret;%0A
 %09%09%09%7D catch (
@@ -2963,18 +2963,33 @@
 n%22, e);%0A
-
 %09%09%7D%0A
+%09%09return null;%0A
 %09%7D%0A%0A%7D%0A

add simple transaction template