fmpp SqlDataLoader

package fmpp.dataloaders;

import java.util.List;

import fmpp.tdd.DataLoader;

public class SqlDataLoader implements DataLoader {
	@SuppressWarnings("unchecked")
	@Override
	public java.lang.Object load(fmpp.Engine arg0, List args)
			throws java.lang.Exception {
		if (args.size() < 5) {
			throw new IllegalArgumentException(
					"At least 5 argument (driverClassName, url, username, password, sql) needed.");
		}

		Object obj = args.get(0);
		if (!(obj instanceof String)) {
			throw new IllegalArgumentException(
					"The 1st argument (driverClassName) must be a string.");
		}
		String driverClassName = obj.toString();

		obj = args.get(1);
		if (!(obj instanceof String)) {
			throw new IllegalArgumentException(
					"The 1st argument (url) must be a string.");
		}
		String url = obj.toString();

		obj = args.get(2);
		if (!(obj instanceof String)) {
			throw new IllegalArgumentException(
					"The 1st argument (username) must be a string.");
		}
		String username = obj.toString();

		obj = args.get(3);
		if (!(obj instanceof String)) {
			throw new IllegalArgumentException(
					"The 1st argument (password) must be a string.");
		}
		String password = obj.toString();

		obj = args.get(4);
		if (!(obj instanceof String)) {
			throw new IllegalArgumentException(
					"The 1st argument (sql) must be a string.");
		}
		String sql = obj.toString();

		SqlTemplateModel model = new SqlTemplateModel();
		model.load(driverClassName, url, username, password, sql);
		return model;
	}
}

package fmpp.dataloaders;

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import fmpp.models.TemplateModelArrayCollection;
import fmpp.models.TemplateModelListCollection;
import fmpp.models.TemplateModelListSequence;
import freemarker.template.SimpleDate;
import freemarker.template.SimpleNumber;
import freemarker.template.SimpleScalar;
import freemarker.template.TemplateBooleanModel;
import freemarker.template.TemplateCollectionModel;
import freemarker.template.TemplateHashModel;
import freemarker.template.TemplateHashModelEx;
import freemarker.template.TemplateModel;
import freemarker.template.TemplateModelException;
import freemarker.template.TemplateSequenceModel;

public class SqlTemplateModel implements TemplateSequenceModel,
		TemplateHashModel {
	// data
	private boolean loaded;

	private List<RowHash> rows = new ArrayList<RowHash>();

	private Map<String, Integer> nameToCol = new HashMap<String, Integer>();

	private int colCount;

	private List<String> keyList = new ArrayList<String>();

	public TemplateModel get(int index) throws TemplateModelException {
		return (TemplateModel) this.rows.get(index);
	}

	public int size() throws TemplateModelException {
		return this.rows.size();
	}

	public TemplateModel get(String key) throws TemplateModelException {
		if (key.equals("headers")) {
			return new TemplateModelListSequence(this.keyList);
		} else {
			return null;
		}
	}

	public boolean isEmpty() throws TemplateModelException {
		return this.colCount == 0;
	}

	public void load(String driverClassName, String url, String username,
			String password, String sql) throws SQLException,
			ClassNotFoundException {
		if (this.loaded) {
			throw new IllegalStateException(
					"Data already loaded into this CSV sequence.");
		}
		new Parser().load(driverClassName, url, username, password, sql);
		this.loaded = true;
	}

	private class Parser {
		private void load(String driverClassName, String url, String username,
				String password, String sql) throws SQLException,
				ClassNotFoundException {
			Class.forName(driverClassName); // 加载驱动程序
			Connection connection = null;
			try {
				connection = DriverManager.getConnection(url, username,
						password);

				connection.setAutoCommit(false);

				doInConnection(connection, sql);

				if (!connection.getAutoCommit()) {
					connection.commit();
					connection.setAutoCommit(true);
				}
			} finally {
				if (connection != null) {
					try {
						connection.close();
					} catch (SQLException e) {
					} catch (Throwable ex) {
					}
					connection = null;
				}
			}
		}

		private void doInConnection(Connection connection, String sql)
				throws SQLException {
			Statement stmt = null;
			try {
				stmt = connection.createStatement();
				doInStatement(stmt, sql);
			} finally {
				if (stmt != null) {
					try {
						stmt.close();
					} catch (SQLException e) {
					} catch (Throwable ex) {
					}
					stmt = null;
				}
			}
		}

		private void doInStatement(Statement statement, String sql)
				throws SQLException {
			ResultSet rs = null;
			try {
				rs = statement.executeQuery(sql);
				doInResultSet(rs);
			} finally {
				try {
					rs.close();
				} catch (SQLException e) {
				} catch (Throwable ex) {
				}
				rs = null;
			}
		}

		private void doInResultSet(ResultSet rs) throws SQLException {
			ArrayList<Integer> headerTypes = new ArrayList<Integer>();

			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			colCount = 0;
			for (int column = 1; column <= columnCount; column++) {
				String columnName = metaData.getColumnName(column);
				int jdbcType = metaData.getColumnType(column);
				nameToCol.put(columnName, colCount);
				keyList.add(columnName);
				headerTypes.add(jdbcType);
				colCount++;
			}
			metaData = null;

			if (colCount > 0) {
				while (rs.next()) {
					TemplateModel[] row = new TemplateModel[colCount];
					for (int column = 0; column < colCount; column++) {
						switch (headerTypes.get(column)) {
						case Types.VARCHAR:
						case Types.CHAR:
						case Types.LONGVARCHAR:
							String str = rs.getString(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleScalar(""); // Field
								// IsNull
							} else {
								row[column] = new SimpleScalar(str);
							}
							break;
						case Types.SMALLINT:
						case Types.TINYINT:
							short s = rs.getShort(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleNumber(0);// Field
								// IsNull
							} else {
								row[column] = new SimpleNumber(s);
							}
							break;
						case Types.INTEGER:
							int i = rs.getInt(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleNumber(0);// Field
								// IsNull
							} else {
								row[column] = new SimpleNumber(i);
							}
							break;
						case Types.BIGINT:
							long l = rs.getLong(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleNumber(0);// Field
								// IsNull
							} else {
								row[column] = new SimpleNumber(l);
							}
							break;
						case Types.REAL:
						case Types.FLOAT:
							float f = rs.getFloat(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleNumber(0);// Field
								// IsNull
							} else {
								row[column] = new SimpleNumber(f);
							}
							break;
						case Types.DOUBLE:
							double d = rs.getDouble(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleNumber(0);// Field
								// IsNull
							} else {
								row[column] = new SimpleNumber(d);
							}
							break;
						case Types.NUMERIC:
						case Types.DECIMAL:
							BigDecimal bd = rs.getBigDecimal(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleNumber(0);// Field
								// IsNull
							} else {
								row[column] = new SimpleNumber(bd.doubleValue());
							}
							break;
						case Types.DATE:
							java.sql.Date date = rs.getDate(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleDate(new java.sql.Date(
										0));// Field
								// IsNull
							} else {
								row[column] = new SimpleDate(date);
							}
							break;
						case Types.TIME:
							java.sql.Time t = rs.getTime(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleDate(new java.sql.Time(
										0)); // Field
								// IsNull
							} else {
								row[column] = new SimpleDate(t);
							}
							break;
						case Types.TIMESTAMP:
							java.sql.Timestamp ts = rs.getTimestamp(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleDate(
										new java.sql.Timestamp(0)); // Field
								// IsNull
							} else {
								row[column] = new SimpleDate(ts);
							}
							break;
						case Types.CLOB:
							Clob clob = rs.getClob(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleScalar(""); // Field
								// IsNull
							} else {
								row[column] = new SimpleScalar(clob
										.getSubString(1l, (int) clob.length()));
							}
							break;
						case Types.BIT:
						case Types.BOOLEAN:
							boolean b = rs.getBoolean(column + 1);
							if (rs.wasNull()) {
								row[column] = TemplateBooleanModel.FALSE; // Field
								// IsNull
							} else {
								row[column] = b ? TemplateBooleanModel.TRUE
										: TemplateBooleanModel.FALSE;
							}
							break;
						case Types.BLOB:
							Blob blob = rs.getBlob(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleScalar(""); // Field
								// IsNull
							} else {
								row[column] = new SimpleScalar(new String(blob
										.getBytes(1l, (int) blob.length())));
							}
							break;
						case Types.BINARY:
						case Types.VARBINARY:
						case Types.LONGVARBINARY:
							byte[] bs = rs.getBytes(column + 1);
							if (rs.wasNull()) {
								row[column] = new SimpleScalar(""); // Field
								// IsNull
							} else {
								row[column] = new SimpleScalar(new String(bs));
							}
							break;
						default:
							row[column] = new SimpleScalar(""); // Unknow Type
						}
					}
					rows.add(new RowHash(row));
				}
			}
		}
	}

	private class RowHash implements TemplateHashModelEx, TemplateSequenceModel {

		private TemplateModel[] cols;

		private RowHash(TemplateModel[] cols) {
			this.cols = cols;
		}

		public int size() {
			return colCount;
		}

		public TemplateCollectionModel keys() {
			return new TemplateModelListCollection(keyList);
		}

		public TemplateCollectionModel values() {
			return new TemplateModelArrayCollection(cols);
		}

		public TemplateModel get(String key) {
			Integer i = (Integer) nameToCol.get(key);
			if (i != null) {
				return cols[i.intValue()];
			} else {
				return null;
			}
		}

		public boolean isEmpty() {
			return size() == 0;
		}

		public TemplateModel get(int i) throws TemplateModelException {
			return cols[i];
		}
	}
}