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];
}
}
}
类似资料：