自定义的Native SQL Query返回强类型的Bean

问题：

在项目中，我们常常会实现Report功能，通常使用Native SQL Query返回查询的数据集，而这些数据集是Raw typed，Query的调用者获得这些原始的数据集后需要显示地将其转化成强类型的Java Bean类，如何减少这些繁琐的转换呢?

 

解决方案：

将原始数据集的每条记录根据一定的规则将其自动映射成Java Bean 对象，比如为Query 返回的列名在Java Bean类中查找匹配的字段（忽略_,空格，大小写，等）， 使用反射将该列的值赋值到Java Bean的字段中。

 

Spring 中为我们提供了一个现成的类用来实现此功能的类-BeanPropertyRowMapper，该类就是使用反射依据列名在Java Bean类中查找匹配的属性，如下是其核心代码片段：

public T mapRow(ResultSet rs, int rowNumber) throws SQLException {
		Assert.state(this.mappedClass != null, "Mapped class was not specified");
		T mappedObject = BeanUtils.instantiate(this.mappedClass);
		BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(mappedObject);
		initBeanWrapper(bw);

		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		Set<String> populatedProperties = (isCheckFullyPopulated() ? new HashSet<String>() : null);

		for (int index = 1; index <= columnCount; index++) {
			String column = JdbcUtils.lookupColumnName(rsmd, index);
			PropertyDescriptor pd = this.mappedFields.get(column.replaceAll(" ", "").toLowerCase());
			if (pd != null) {
				try {
					Object value = getColumnValue(rs, index, pd);
					if (logger.isDebugEnabled() && rowNumber == 0) {
						logger.debug("Mapping column '" + column + "' to property '" +
								pd.getName() + "' of type " + pd.getPropertyType());
					}
					try {
						bw.setPropertyValue(pd.getName(), value);
					}
					catch (TypeMismatchException e) {
						if (value == null && primitivesDefaultedForNullValue) {
							logger.debug("Intercepted TypeMismatchException for row " + rowNumber +
									" and column '" + column + "' with value " + value +
									" when setting property '" + pd.getName() + "' of type " + pd.getPropertyType() +
									" on object: " + mappedObject);
						}
						else {
							throw e;
						}
					}
					if (populatedProperties != null) {
						populatedProperties.add(pd.getName());
					}
				}
				catch (NotWritablePropertyException ex) {
					throw new DataRetrievalFailureException(
							"Unable to map column " + column + " to property " + pd.getName(), ex);
				}
			}
		}

		if (populatedProperties != null && !populatedProperties.equals(this.mappedProperties)) {
			throw new InvalidDataAccessApiUsageException("Given ResultSet does not contain all fields " +
					"necessary to populate object of class [" + this.mappedClass + "]: " + this.mappedProperties);
		}

		return mappedObject;
	}

 

从上面可以看出 其输入的数据源，只是指出ResultSet,如过需要支持其他的数据源，则得自己实现了。如下为我自己实现的一个例子，输入源为CursoredStream(EclipseLink)：

public T mapRow(CursoredStream cs) {

		DatabaseRecord row = (DatabaseRecord) cs.next();

		List<DatabaseField> fields = cs.getFields();

		Assert.state(this.mappedClass != null, "Mapped class was not specified");
		T mappedObject = BeanUtils.instantiate(this.mappedClass);
		BeanWrapper bw = PropertyAccessorFactory
				.forBeanPropertyAccess(mappedObject);
		initBeanWrapper(bw);

		// ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = fields.size();

		Set<String> populatedProperties = (isCheckFullyPopulated() ? new HashSet<String>()
				: null);

		for (DatabaseField field : fields) {
			String columnName = field.getName();// JdbcUtils.lookupColumnName(rsmd,
												// index);
			PropertyDescriptor pd = this.mappedFields.get(columnName
					.replaceAll(" ", "").toLowerCase());
			if (pd != null) {
				try {
					Object value = getColumnValue(row, field, pd);

					try {
						bw.setPropertyValue(pd.getName(), value);
					} catch (TypeMismatchException e) {
						if (value == null && primitivesDefaultedForNullValue) {
							logger.debug("Intercepted TypeMismatchException for row "
									+ cs.getPosition()
									+ " and column '"
									+ columnName
									+ "' with value "
									+ value
									+ " when setting property '"
									+ pd.getName()
									+ "' of type "
									+ pd.getPropertyType()
									+ " on object: " + mappedObject);
						} else {
							throw e;
						}
					}
					if (populatedProperties != null) {
						populatedProperties.add(pd.getName());
					}
				} catch (NotWritablePropertyException ex) {
					throw new DataRetrievalFailureException(
							"Unable to map column " + columnName
									+ " to property " + pd.getName(), ex);
				}
			}
		}

		if (populatedProperties != null
				&& !populatedProperties.equals(this.mappedProperties)) {
			throw new InvalidDataAccessApiUsageException(
					"Given ResultSet does not contain all fields "
							+ "necessary to populate object of class ["
							+ this.mappedClass + "]: " + this.mappedProperties);
		}

		return mappedObject;
	}