自己设计的JdbcTemplate

自己写的Jdbc模板，使用了Template,Strategy,Call Back等，可以自动设置参数，并可以完成ResultSet到JTable以及List的自动映射。极大减少重复代码，比较经典。

PreparedStatementCallback 是用于回调的接口

public interface PreparedStatementCallback {
	public Object doInPreparedStatement(PreparedStatement pstmt) throws SQLException,ClassNotFoundException;
}

public class JdbcTemplate {
	
	private static final Log logger = LogFactory.getLog(JdbcTemplate.class);	

	/**
	 * execute template
	 * @param action
	 * @param sql
	 * @param arrayParams
	 * @param mapParams
	 * @return
	 * @throws PersistenceException
	 */
	private Object execute(PreparedStatementCallback action, String sql,
			Object[] arrayParams,HashMap<Integer, Object> mapParams) throws PersistenceException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		Object result = null;
		try {
			conn = DataSourceFactory.getConnection();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);
			// call back
			result = action.doInPreparedStatement(pstmt);
			logger.debug(pstmt.toString());
			conn.commit();
		} catch (ClassNotFoundException e) {
			transactionRollback(conn);
			throw new PersistenceException(e);
		} catch (SQLException e) {
			transactionRollback(conn);
			throw new PersistenceException(e);
		} finally {
			this.closeStatement(pstmt);
			this.closeConnection(conn);
		}
		return result;
	}
	
	/**
	 * hook method
	 * @param sql
	 * @param arrayParams
	 * @param mapParams
	 * @return
	 * @throws PersistenceException
	 */
	private Object execute(final String sql,final Object[] arrayParams,
			final HashMap<Integer, Object> mapParams) throws PersistenceException{
		PreparedStatementCallback callBack = new PreparedStatementCallback() {
			@Override
			public Object doInPreparedStatement(PreparedStatement pstmt)
					throws SQLException, ClassNotFoundException {
				if(arrayParams!=null)
					setParameter(pstmt, arrayParams);
				if(mapParams!=null)
					setParameter(pstmt, mapParams);
				int count = pstmt.executeUpdate();
				return new Boolean(count > 0);
			}
		};
		if(arrayParams!=null)
			return execute(callBack,sql,arrayParams,null);
		else if(mapParams!=null)
			return execute(callBack,sql,null,mapParams);
		else
			return execute(callBack,sql,null,null);
	}
	
	/**
	 * hook method
	 * @param sql
	 * @param arrayParams
	 * @param mapParams
	 * @return
	 * @throws PersistenceException
	 */
	private Object executeQuery(final String sql,final Object[] arrayParams,final HashMap<Integer, Object> mapParams) throws PersistenceException {
		PreparedStatementCallback callBack = new PreparedStatementCallback() {
			@Override
			public Object doInPreparedStatement(PreparedStatement pstmt)
					throws SQLException, ClassNotFoundException {
				if(arrayParams!=null)
					setParameter(pstmt, arrayParams);
				if(mapParams!=null)
					setParameter(pstmt, mapParams);
				ResultSet rs =  pstmt.executeQuery();
				return convertResultSetToList(rs);
			}
		};
		if(arrayParams!=null)
			return execute(callBack,sql,arrayParams,null);
		else if(mapParams!=null)
			return execute(callBack,sql,null,mapParams);
		else
			return execute(callBack,sql,null,null);
	}
	
	public Object fillDataToTable(final String sql,final Object[] arrayParams) throws PersistenceException {
		PreparedStatementCallback callBack = new PreparedStatementCallback() {
			@Override
			public Object doInPreparedStatement(PreparedStatement pstmt)
					throws SQLException, ClassNotFoundException {
				if(arrayParams!=null)
					setParameter(pstmt, arrayParams);				
				ResultSet rs =  pstmt.executeQuery();
				return convertResultSetToTable(rs);
			}
		};
		return execute(callBack,sql,arrayParams,null);
	}
	
	
	public Object execute(String sql,Object[] arrayParams) throws PersistenceException{
		return execute(sql,arrayParams,null);
	}
	
	public Object execute(String sql,HashMap<Integer, Object> mapParams) throws PersistenceException{
		return execute(sql,null,mapParams);
	}
	
	public Object execute(String sql) throws PersistenceException{
		return execute(sql,null,null);
	}
	
	public Object executeQuery(final String sql,
			final Object[] arrayParams) throws PersistenceException {
		return executeQuery(sql,arrayParams,null);	
	}
	
	public Object executeQuery(final String sql,
			final HashMap<Integer, Object> mapParams) throws PersistenceException {
		return executeQuery(sql,null,mapParams);	
	}
	
	public Object executeQuery(final String sql) throws PersistenceException {
		return executeQuery(sql,null,null);	
	}
	
		
	/**
	 * setParameter
	 * @param sql
	 * @param parameters HashMap<Integer, Object> 
	 * @return
	 * @throws PersistenceException
	 */
	private PreparedStatement setParameter(PreparedStatement pstmt,
			HashMap<Integer, Object> parameters) throws ClassNotFoundException,
			SQLException {
		if (parameters!=null) {
			if (parameters.size()>=0) {
				for (int i = 1; i <= parameters.size(); i++) {
					try {
						if (parameters.get(i).getClass() == Class
								.forName("java.lang.String")) {
							pstmt.setString(i, parameters.get(i).toString());
						}
						if (parameters.get(i).getClass() == Class
								.forName("java.sql.Date")) {
							pstmt.setDate(i, java.sql.Date.valueOf(parameters
									.get(i).toString()));
						}
						if (parameters.get(i).getClass() == Class
								.forName("java.lang.Boolean")) {
							pstmt.setBoolean(i, (Boolean) (parameters.get(i)));
						}
						if (parameters.get(i).getClass() == Class
								.forName("java.lang.Integer")) {
							pstmt.setInt(i, (Integer) parameters.get(i));
						}
						if (parameters.get(i).getClass() == Class
								.forName("java.lang.Float")) {
							pstmt.setFloat(i, (Float) parameters.get(i));
						}
						if (parameters.get(i).getClass() == Class
								.forName("java.lang.Double")) {
							pstmt.setDouble(i, (Double) parameters.get(i));
						}
					} catch (ClassNotFoundException ex) {
						throw ex;
					} catch (SQLException ex) {
						throw ex;
					}
				}
			}
		}
		return pstmt;
	}
	
	/**
	 * setParameter
	 * @param sql
	 * @param parameters Object[]
	 * @return
	 * @throws PersistenceException
	 */
	private PreparedStatement setParameter(PreparedStatement pstmt,
			Object[] parameters) throws ClassNotFoundException,
			SQLException {
		if (parameters!=null) {
			if (parameters.length>=0) {
				for (int i = 0; i < parameters.length; i++) {
					try {
						if (parameters[i].getClass() == Class
								.forName("java.lang.String")) {
							pstmt.setString(i+1, parameters[i].toString());
						}
						if (parameters[i].getClass() == Class
								.forName("java.sql.Date")) {
							pstmt.setDate(i+1, java.sql.Date.valueOf(parameters[i].toString()));
						}
						if (parameters[i].getClass() == Class
								.forName("java.lang.Boolean")) {
							pstmt.setBoolean(i+1, (Boolean) (parameters[i]));
						}
						if (parameters[i].getClass() == Class
								.forName("java.lang.Integer")) {
							pstmt.setInt(i+1, (Integer) parameters[i]);
						}
						if (parameters[i].getClass() == Class
								.forName("java.lang.Float")) {
							pstmt.setFloat(i+1, (Float) parameters[i]);
						}
						if (parameters[i].getClass() == Class
								.forName("java.lang.Double")) {
							pstmt.setDouble(i+1, (Double) parameters[i]);
						}
					} catch (ClassNotFoundException ex) {
						throw ex;
					} catch (SQLException ex) {
						throw ex;
					}
				}
			}
		}
		return pstmt;
	}

	private List<HashMap<Object,Object>> convertResultSetToList(
			ResultSet rs) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		List<HashMap<Object, Object>> tempList = new ArrayList<HashMap<Object, Object>>();
		HashMap<Object, Object> tempHash = null;
		try{
			while (rs.next()) {
				tempHash = new HashMap<Object, Object>();
				for (int i = 0; i < rsmd.getColumnCount(); i++) {
					tempHash.put(rsmd.getColumnName(i + 1).toUpperCase(), rs
							.getString(rsmd.getColumnName(i + 1)));
				}
				tempList.add(tempHash);
			}
		}catch(SQLException e){
			throw e;
		}
		finally{
			this.closeResultSet(rs);
		}
		return tempList;
	}
	
	private TableModel convertResultSetToTable(ResultSet rs) throws SQLException {
		DefaultTableModel tableModel = null;
		int columnCount = 0;
		ResultSetMetaData rsmd = null;
		try {
			tableModel = new DefaultTableModel();
			tableModel.setRowCount(0);
			rsmd = rs.getMetaData();
			columnCount = rsmd.getColumnCount();
			String[] arr = new String[columnCount];
			for(int i=0;i<columnCount;i++){
				tableModel.addColumn(rsmd.getColumnName(i+1));
			}
			while (rs.next()) {
				for (int i = 0; i < rsmd.getColumnCount(); i++) {
					arr[i] = rs.getString(rsmd.getColumnName(i + 1));
				}
				tableModel.addRow(arr);
			}
		} catch (SQLException e) {
			throw e;
		} finally{
			this.closeResultSet(rs);
		}
		return tableModel;
	}

	private void transactionRollback(Connection conn) {
		if (conn != null) {
			try {
				conn.rollback();
			} catch (SQLException e) {
				logger.debug("Could not roll back transaction", e);
			}
		}
	}

	private void closeStatement(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				logger.debug("Could not close JDBC Statement", e);
			}
		}
	}

	private void closeConnection(Connection conn){
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				logger.debug("Could not close JDBC Statement", e);
			}
		}
	}
	
	private void closeResultSet(ResultSet rs){
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				logger.debug("Could not close ResultSet", e);
			}
		}
	}
}

评论

1 楼 iql 2009-03-16  

业务复杂时事务如何控制？