浏览 2208 次
锁定老帖子 主题:fmpp SqlDataLoader
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2008-11-03
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]; } } } 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |