Poor man's JdbcTemplate

JdbcTemplate.java

import java.util.List;

import com.google.common.base.Function;

public interface JdbcTemplate {
	<T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper);
	<T> T queryFirst(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper);
	int queryForInt(String sql, Object[] args, int[] argTypes);
	<T> void eachDo(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper, Function<T, Void> block);
	int update(String sql, Object[] args, int[] argTypes);
	int[] updateBatch(String sql, Iterable<Object[]> argss, int[] argTypes);
	void executeScript(String name);
}

MasterSlaveJdbcTemplate.java

import java.util.List;

import javax.sql.DataSource;

import com.google.common.base.Function;

public class MasterSlaveJdbcTemplate implements JdbcTemplate {
	private final JdbcTemplate master;
	private final JdbcTemplate[] slaves;
	private int c = 0;

	public MasterSlaveJdbcTemplate(DataSource master, DataSource[] slaves) {
		this.master = new SimpleJdbcTemplate(master);
		this.slaves = new JdbcTemplate[slaves.length];
		for (int i = 0; i < slaves.length; i++) {
			this.slaves[i] = new SimpleJdbcTemplate(slaves[i]);
		}
	}
	
	public MasterSlaveJdbcTemplate(JdbcTemplate master, JdbcTemplate[] slaves) {
		this.master = master;
		this.slaves = slaves;
	}
	
	protected JdbcTemplate selectSlave() {
		return slaves[c++ % slaves.length];
	}

	@Override
	public <T> List<T> query(String sql, Object[] args, int[] argTypes,	RowMapper<T> rowMapper) {
		return selectSlave().query(sql, args, argTypes, rowMapper);
	}

	@Override
	public <T> T queryFirst(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) {
		return selectSlave().queryFirst(sql, args, argTypes, rowMapper);
	}

	@Override
	public int queryForInt(String sql, Object[] args, int[] argTypes) {
		return selectSlave().queryForInt(sql, args, argTypes);
	}

	@Override
	public <T> void eachDo(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper, Function<T, Void> block) {
		selectSlave().eachDo(sql, args, argTypes, rowMapper, block);
	}

	@Override
	public int update(String sql, Object[] args, int[] argTypes) {
		return master.update(sql, args, argTypes);
	}

	@Override
	public int[] updateBatch(String sql, Iterable<Object[]> argss, int[] argTypes) {
		return master.updateBatch(sql, argss, argTypes);
	}

	@Override
	public void executeScript(String name) {
		master.executeScript(name);
	}
}

MasterSlaveJdbcTemplateTest.java

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

import org.jmock.Expectations;
import org.jmock.Mockery;
import org.jmock.integration.junit4.JMock;
import org.jmock.integration.junit4.JUnit4Mockery;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;

import com.google.common.base.Function;

@RunWith(JMock.class)
public class MasterSlaveJdbcTemplateTest {
	private Mockery mockery = new JUnit4Mockery();
	private MasterSlaveJdbcTemplate dut;
	private JdbcTemplate master;
	private JdbcTemplate[] slaves;
	
	@Before public void beforeEach() {
		master = mockery.mock(JdbcTemplate.class, "master");
		slaves = new JdbcTemplate[] { mockery.mock(JdbcTemplate.class, "slave1") }; 
		dut = new MasterSlaveJdbcTemplate(master, slaves);
	}
	
	@Test public void updateShouldUseMaster() {
		final String sql = "";
		final Object[] args = new Object[] {};
		final List<Object[]> argss = new ArrayList<Object[]>();
		final int[] argTypes = new int[] {};
		mockery.checking(new Expectations() {{
			oneOf(master).update(sql, args, argTypes);
			oneOf(master).updateBatch(sql, argss, argTypes);
		}});
		dut.update(sql, args, argTypes);
		dut.updateBatch(sql, argss, argTypes);
	}
	
	@Test public void executeScriptShouldUseMaster() {
		final String name = "";
		mockery.checking(new Expectations() {{
			oneOf(master).executeScript(name);
		}});
		dut.executeScript(name);
	}
	
	@Test public void queryShouldUseSlave() {
		final String sql = "";
		final Object[] args = new Object[] {};
		final int[] argTypes = new int[] {};
		final RowMapper<Object> rowMapper = new RowMapper<Object>() {
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new Object();
			}
		};
		final Function<Object, Void> block = new Function<Object, Void>() {
			@Override
			public Void apply(Object input) {
				return null;
			}
		};
		mockery.checking(new Expectations() {{
			oneOf(slaves[0]).query(sql, args, argTypes, rowMapper);
			oneOf(slaves[0]).queryFirst(sql, args, argTypes, rowMapper);
			oneOf(slaves[0]).queryForInt(sql, args, argTypes);
			oneOf(slaves[0]).eachDo(sql, args, argTypes, rowMapper, block);
		}});
		dut.query(sql, args, argTypes, rowMapper);
		dut.queryFirst(sql, args, argTypes, rowMapper);
		dut.queryForInt(sql, args, argTypes);
		dut.eachDo(sql, args, argTypes, rowMapper, block);
	}
}

SimpleJdbcTemplate.java

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.google.common.base.Charsets;
import com.google.common.base.Function;
import com.google.common.io.Resources;

public class SimpleJdbcTemplate implements JdbcTemplate {
	protected final DataSource dataSource;

	public SimpleJdbcTemplate(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				statement.setObject(i + 1, args[i], argTypes[i]);
			}
			rs = statement.executeQuery();
			List<T> result = new ArrayList<T>();
			int rowNum = 0;
			while (rs.next()) {
				result.add(rowMapper.mapRow(rs, rowNum));
				rowNum++;
			}
			return result;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			SQLUtils.closeQuietly(rs);
			SQLUtils.closeQuietly(statement);
			SQLUtils.closeQuietly(connection);
		}
	}

	public <T> T queryFirst(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				statement.setObject(i + 1, args[i], argTypes[i]);
			}
			rs = statement.executeQuery();
			if (rs.next()) {
				return rowMapper.mapRow(rs, 0);
			}
			return null;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			SQLUtils.closeQuietly(rs);
			SQLUtils.closeQuietly(statement);
			SQLUtils.closeQuietly(connection);
		}
	}

	public int queryForInt(String sql, Object[] args, int[] argTypes) {
		return queryFirst(sql, args, argTypes, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getInt(1);
			}
		});
	}

	public <T> void eachDo(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper, Function<T, Void> block) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				statement.setObject(i + 1, args[i], argTypes[i]);
			}
			rs = statement.executeQuery();
			int rowNum = 0;
			while (rs.next()) {
				block.apply(rowMapper.mapRow(rs, rowNum));
				rowNum++;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			SQLUtils.closeQuietly(rs);
			SQLUtils.closeQuietly(statement);
			SQLUtils.closeQuietly(connection);
		}
	}

	public int update(String sql, Object[] args, int[] argTypes) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				statement.setObject(i + 1, args[i], argTypes[i]);
			}
			return statement.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			SQLUtils.closeQuietly(statement);
			SQLUtils.closeQuietly(connection);
		}
	}

	public int[] updateBatch(String sql, Iterable<Object[]> argss, int[] argTypes) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dataSource.getConnection();
			conn.setAutoCommit(false);
			stmt = conn.prepareStatement(sql);
			for (Object[] args : argss) {
				for (int i = 0; i < args.length; i++) {
					stmt.setObject(i + 1, args[i], argTypes[i]);
				}
				stmt.addBatch();
			}
			int[] result = stmt.executeBatch();
			conn.commit();
			return result;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			SQLUtils.closeQuietly(stmt);
			SQLUtils.closeQuietly(conn);
		}
	}

	public void executeScript(String name) {
		try {
			String[] sqls = Resources.toString(Resources.getResource(name), Charsets.UTF_8).split(";");
			Connection connection = null;
			Statement statement = null;
			try {
				connection = dataSource.getConnection();
				connection.setAutoCommit(false);
				statement = connection.createStatement();
				for (String sql : sqls) {
					statement.executeUpdate(sql);
				}
				connection.commit();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			} finally {
				SQLUtils.closeQuietly(statement);
				SQLUtils.closeQuietly(connection);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
}