BigDecimal返回前端数据超过16位导致精度丢失，比如6916750604278803063 变成 6916750604278803000

由于JS最多支持16位的数字型数据，所以必须对返回数据进行处理。

方法一：简单粗暴，强行转为字符串返回

方法二：在JDBC返回的resultset进行处理

package smartbix;

import java.io.IOException;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import smartbix.dataprocess.ClickHouseNullValue;
import smartbix.util.CommonErrorCode;
import smartbix.util.DBType;
import smartbix.util.DbUtil;
import smartbixlibs.org.apache.logging.log4j.LogManager;

/**
 * 工具类：GridData
 *
 * @since 2017-07-13
 */
public class GridDataUtil {
	private static BigDecimal MIN_INT = new BigDecimal(Integer.MIN_VALUE);
	private static BigDecimal MAX_INT = new BigDecimal(Integer.MAX_VALUE);
	private static BigDecimal MIN_LONG = new BigDecimal(Long.MIN_VALUE);
	private static BigDecimal MAX_LONG = new BigDecimal(Long.MAX_VALUE);

	/**
	 * 根据SQL获取GridData
	 * 
	 * @param conn
	 *            conn
	 * @param cellDataConvertor
	 *            单元格数据转换类
	 * @param sql
	 *            sql
	 * @param params
	 *            params
	 * @return GridData
	 */
	public static GridData executeSql(Connection conn, ICellDataConvertor cellDataConvertor, String sql, DBType dbType,
			Object... params) {
		GridData result = new GridData();
		PreparedStatement prep = null;
		ResultSet rs = null;
		try {
			LogManager.getLogger().debug(sql);
			prep = conn.prepareStatement(sql);
			rs = getResultSet(prep, params);
			result = getResultSet(rs, cellDataConvertor, Integer.MAX_VALUE, dbType);
		} catch (SQLException e) {
			throw SmartbiXException.create(CommonErrorCode.SQL_ERROR, e).setDetail(sql);
		} finally {
			DbUtil.closeDBObject(rs, prep, null);
		}
		return result;
	}

	/**
	 * 根据SQL获取IDataIterator
	 * 
	 * @param conn
	 *            conn
	 * @param cellDataConvertor
	 *            数据格式转换类
	 * @param sql
	 *            sql
	 * @param dbType
	 *            数据库类型
	 * @param params
	 *            params
	 * @return DataIterator
	 */
	public static DataIterator executeIterator(Connection conn, ICellDataConvertor cellDataConvertor, String sql,
			DBType dbType, Object... params) {
		try {
			ResultSet rs = null;
			LogManager.getLogger().debug(sql);
			if (dbType == DBType.PRESTO) {
				Statement stat = conn.createStatement();
				rs = stat.executeQuery(sql);
			} else {
				PreparedStatement prep = conn.prepareStatement(sql);
				rs = getResultSet(prep, params);
			}
			return getDataIterator(rs, cellDataConvertor, dbType);
		} catch (SQLException e) {
			throw SmartbiXException.create(CommonErrorCode.SQL_ERROR, e).setDetail(sql);
		}
	}

	/**
	 * 按ResultSet生成IDataIterator
	 * 
	 * @param rs
	 *            rs
	 * @param cellDataConvertor
	 *            单元格数据转换类
	 * @param dbType
	 *            dbType
	 * @return IDataIterator
	 */
	public static DataIterator getDataIterator(final ResultSet rs, final ICellDataConvertor cellDataConvertor,
			final DBType dbType) {
		ResultSetMetaData meta;
		int colCount;
		try {
			meta = rs == null ? null : rs.getMetaData();
			colCount = meta == null ? 0 : meta.getColumnCount();
		} catch (SQLException e) {
			throw SmartbiXException.create(CommonErrorCode.SQL_ERROR, e);
		}
		final int columnCount = colCount;
		//
		IDataIterator iterator = new IDataIterator() {
			private boolean closed;
			private long currentRow = 0L;

			@Override
			public long getMaxRow() {
				if (dbType == DBType.SYBASE) {
					return 10000L;
				} else {
					return -1L;
				}
			}

			@Override
			public void close() {
				if (closed || rs == null) {
					return;
				}
				try {
					Statement stat = rs.getStatement();
					Connection conn = stat.getConnection();
					DbUtil.closeDBObject(rs, stat, conn);
				} catch (SQLException e) {
					LogManager.getLogger().error(e.getMessage(), e);
				} finally {
					closed = true;
				}
			}

			@Override
			public boolean hasNext() {
				long maxRow = getMaxRow();
				if (maxRow != -1 && currentRow >= maxRow) {
					return false;
				}
				try {
					return rs == null ? false : rs.next();
				} catch (SQLException e) {
					close();
					return false;
				}
			}

			@Override
			public IRow next() {
				try {
					List<ICell> cells = new ArrayList<ICell>();
					for (int i = 1; i <= columnCount; i++) {
						CellData cell = getCell(rs, i, cellDataConvertor, dbType);
						cells.add(cell);
					}
					Row row = new Row();
					row.setCells(cells);
					currentRow++;
					return row;
				} catch (SQLException e) {
					close();
					return null;
				}
			}

		};
		//
		DataIterator result = new DataIterator();
		result.setIterator(iterator);
		result.setColumnLabels(getColumnLabels(meta));
		return result;
	}

	/**
	 * 获取SQL结果集中元数据的列头信息
	 * 
	 * @param meta
	 *            meta
	 * @return column labels
	 * @throws SQLException
	 *             SQLException
	 */
	public static List<String> getColumnLabels(ResultSetMetaData meta) {
		List<String> columnLabels = new ArrayList<String>();
		if (meta != null) {
			try {
				int columnCount = meta.getColumnCount();
				for (int i = 1; i <= columnCount; i++) {
					String columnLabel = meta.getColumnLabel(i);
					if (columnLabel == null || columnLabel.isEmpty()) {
						columnLabel = meta.getColumnName(i);
					}
					columnLabels.add(columnLabel);
				}
			} catch (SQLException e) {
				throw SmartbiXException.create(CommonErrorCode.SQL_ERROR, e);
			}
		}
		return columnLabels;
	}

	/**
	 * GridData转换为IDataIterator
	 * 
	 * @param gridData
	 *            gridData
	 * @return IDataIterator
	 */
	public static IDataIterator toDataIterator(final GridData gridData) {
		if (gridData == null || gridData.getData() == null) {
			return null;
		}
		return new IDataIterator() {
			Iterator<List<CellData>> datas = gridData.getData().iterator();
			private long currentRow = 0L;

			@Override
			public boolean hasNext() {
				long maxRow = getMaxRow();
				if (maxRow != -1 && currentRow >= maxRow) {
					return false;
				}
				return datas.hasNext();
			}

			@Override
			public long getMaxRow() {
				return -1L;
			}

			@Override
			public IRow next() {
				currentRow++;
				return new IRow() {
					private List<? extends ICell> cells;
					{
						cells = datas.next();
					}

					@Override
					public List<? extends ICell> getCells() {
						return cells;
					}
				};
			}

			@Override
			public void close() {
				//
			}
		};
	}

	/**
	 * IDataIterator转换为 GridData
	 * 
	 * @param dataIterator
	 *            dataIterator
	 * @return GridData
	 */
	public static GridData toGridData(IDataIterator dataIterator) {
		if (dataIterator == null) {
			return null;
		}
		GridData result = new GridData();
		List<List<CellData>> datas = new ArrayList<List<CellData>>();
		while (dataIterator.hasNext()) {
			List<CellData> cellDatas = new ArrayList<CellData>();
			IRow row = dataIterator.next();
			List<? extends ICell> cells = row.getCells();
			for (ICell cell : cells) {
				CellData cellData = new CellData();
				cellData.setType(cell.getType());
				cellData.setValue(cell.getValue());
				cellData.setDisplayValue(cell.getDisplayValue());
				cellDatas.add(cellData);
			}
			datas.add(cellDatas);
		}
		dataIterator.close();
		result.setData(datas);
		result.setTotalRowsCount(datas.size());
		return result;
	}

	/**
	 * 设置查询参数并返回ResultSet
	 * 
	 * @param prep
	 *            prep
	 * @param params
	 *            params
	 * @return ResultSet
	 * @throws SQLException
	 *             SQLException
	 */
	private static ResultSet getResultSet(PreparedStatement prep, Object... params) throws SQLException {
		long t1 = System.currentTimeMillis();
		for (int i = 0; i < params.length; i++) {
			Object param = params[i];
			if (param == null) {
				prep.setNull(i + 1, Types.VARCHAR);
			} else if (param instanceof String) {
				prep.setString(i + 1, param.toString());
			} else if (param instanceof Double) {
				prep.setDouble(i + 1, ((Double) param).doubleValue());
			} else if (param instanceof Integer) {
				prep.setInt(i + 1, ((Integer) param).intValue());
			} else if (param instanceof java.util.Date) {
				if (param instanceof Date) {
					prep.setDate(i + 1, (Date) param);
				} else if (param instanceof Time) {
					prep.setTime(i + 1, (Time) param);
				} else if (param instanceof Timestamp) {
					prep.setTimestamp(i + 1, (Timestamp) param);
				} else {
					prep.setTimestamp(i + 1, new Timestamp(((java.util.Date) param).getTime()));
				}
			}
		}
		ResultSet rs = prep.executeQuery();
		long t2 = System.currentTimeMillis();
		LogManager.getLogger().debug(String.format("[executeSql]: %d%s.", t2 - t1, "ms"));
		return rs;
	}

	/**
	 * 根据ResultSet获取 GridData
	 * 
	 * @param rs
	 *            rs
	 * @param cellDataConvertor
	 *            单元格数据转换类
	 * @return GridData
	 * @throws SQLException
	 *             SQLException
	 */
	private static GridData getResultSet(ResultSet rs, ICellDataConvertor cellDataConvertor, int maxRows, DBType dbType)
			throws SQLException {
		GridData gridData = new GridData();
		List<String> columnLabels = new ArrayList<String>();
		List<List<CellData>> data = new ArrayList<List<CellData>>();
		if (rs != null) {
			ResultSetMetaData meta = rs.getMetaData();
			int columnCount = meta.getColumnCount();
			columnLabels.addAll(getColumnLabels(meta));
			int rowCount = 0;
			while (rs.next() && ++rowCount <= maxRows) {
				List<CellData> row = new ArrayList<CellData>();
				for (int i = 1; i <= columnCount; i++) {
					CellData cell = getCell(rs, i, cellDataConvertor, dbType);
					row.add(cell);
				}
				data.add(row);
			}
		}
		gridData.setStringHeaders(columnLabels);
		gridData.setData(data);
		gridData.setTotalRowsCount(data.size());
		return gridData;
	}

	/**
	 * 获取单行ResultSet中指定列的数据
	 * 
	 * <pre>
	 * wasNull只有在rs.getXXX返回值的类型为原始类型时(比如：int、long、double等)才需要调用，
	 * 像其它非原始类型(比如：String等)可以直接判断是否等于null就不需要调用wasNull方法了，
	 * 否则oracle中对于非原始类型返回值为null时调用wasNull可能会抛错。
	 * </pre>
	 * 
	 * @param rs
	 *            rs
	 * @param columnIndex
	 *            the first column is 1, the second is 2, ...
	 * @param cellDataConvertor
	 *            数据格数据转换类
	 * @return CellData
	 */
	private static CellData getCell(ResultSet rs, int columnIndex, ICellDataConvertor cellDataConvertor, DBType dbType)
			throws SQLException {
		CellData cell = new CellData();
		if (rs != null) {
			ResultSetMetaData meta = rs.getMetaData();
			int columnType = meta.getColumnType(columnIndex);
			switch (columnType) {
				case java.sql.Types.BIT:
				case java.sql.Types.TINYINT:
				case java.sql.Types.SMALLINT:
				case java.sql.Types.INTEGER:
					if (columnType == java.sql.Types.BIT
							&& (dbType == DBType.GREENPLUM || dbType == DBType.POSTGRESQL)) {
						int b = rs.getBoolean(columnIndex) ? 1 : 0;
						cell.setIntValue(b);
					} else if (dbType == DBType.CLICK_HOUSE || dbType == DBType.SMARTBI_MPP) {
						// 抽数到ClickHouse时，null被置为默认值，所以查询时需要把默认值还原为null
						int val = rs.getInt(columnIndex);
						if (ClickHouseNullValue.NULL_INTEGER == val) {
							cell.setType(ValueType.INTEGER);
							cell.setNullValue();
						} else {
							cell.setIntValue(val);
						}
					} else {
						cell.setIntValue(rs.getInt(columnIndex));
					}
					if (rs.wasNull()) {
						cell.setNullValue();
					}
					break;
				case java.sql.Types.BIGINT:
					BigDecimal bd = rs.getBigDecimal(columnIndex);
					if (bd == null) {
						break;
					}
					// 抽数到ClickHouse时，null被置为默认值，所以查询时需要把默认值还原为null
					if (dbType == DBType.CLICK_HOUSE || dbType == DBType.SMARTBI_MPP) {
						if (ClickHouseNullValue.NULL_BITINT_OBJ.compareTo(bd) == 0) {
							cell.setType(ValueType.BIGINT);
							cell.setNullValue();
						} else {
							cell.setBigIntValue(bd.toBigInteger());
						}
					} else {
						cell.setBigIntValue(bd.toBigInteger());
					}
					if (rs.wasNull()) {
						cell.setNullValue();
					}
					break;
				case java.sql.Types.FLOAT:
				case java.sql.Types.DOUBLE:
				case java.sql.Types.REAL:
					// 抽数到ClickHouse时，null被置为默认值，所以查询时需要把默认值还原为null
					if (dbType == DBType.CLICK_HOUSE || dbType == DBType.SMARTBI_MPP) {
						double val = rs.getDouble(columnIndex);
						if (ClickHouseNullValue.NULL_DOUBLE == val) {
							cell.setType(ValueType.DOUBLE);
							cell.setNullValue();
						} else {
							cell.setDoubleValue(val);
						}
					} else {
						cell.setDoubleValue(rs.getDouble(columnIndex));
					}
					if (rs.wasNull()) {
						cell.setNullValue();
					}
					break;
				case java.sql.Types.NUMERIC:
				case java.sql.Types.DECIMAL:
					BigDecimal bd2 = rs.getBigDecimal(columnIndex);
					if (bd2 == null) {
						cell.setType(ValueType.DOUBLE);
						cell.setNullValue();
						break;
					}

					// 抽数到ClickHouse时，null被置为默认值，所以查询时需要把默认值还原为null
					if ((dbType == DBType.CLICK_HOUSE || dbType == DBType.SMARTBI_MPP)
							&& (ClickHouseNullValue.NULL_INTEGER_OBJ.compareTo(bd2) == 0
									|| ClickHouseNullValue.NULL_BITINT_OBJ.compareTo(bd2) == 0
									|| ClickHouseNullValue.NULL_DOUBLE_OBJ.compareTo(bd2) == 0)) {
						cell.setType(ValueType.DOUBLE);
						cell.setNullValue();
						break;
					}

					// 整数
					if (bd2.scale() == 0) {
						if (bd2.compareTo(MIN_INT) >= 0 && bd2.compareTo(MAX_INT) <= 0) {
							cell.setIntValue(bd2.intValue());
						} else if (bd2.compareTo(MIN_LONG) >= 0 && bd2.compareTo(MAX_LONG) <= 0) {
							cell.setLongValue(bd2.longValue());
						} else {
							cell.setBigIntValue(bd2.toBigInteger());
						}
						break;
					}

					// 浮点数
					cell.setBigDecimalValue(bd2);
					break;
				case java.sql.Types.CHAR:
				case java.sql.Types.VARCHAR:
				case java.sql.Types.LONGVARCHAR:
				case java.sql.Types.NCHAR:
				case java.sql.Types.NVARCHAR:
				case java.sql.Types.LONGNVARCHAR:
					String strValue = rs.getString(columnIndex);
					cell.setStringValue(strValue);
					break;
				case java.sql.Types.DATE:
					Date date = rs.getDate(columnIndex);
					// 抽数到ClickHouse时，null被置为默认值，所以查询时需要把默认值还原为null
					if (date != null && ClickHouseNullValue.NULL_DATE.compareTo(date) == 0) {
						cell.setType(ValueType.DATE);
						cell.setNullValue();
					} else {
						cell.setDateValue(ValueType.DATE, date);
					}
					break;
				case java.sql.Types.TIME:
					cell.setDateValue(ValueType.TIME, rs.getTime(columnIndex));
					break;
				case java.sql.Types.TIMESTAMP:
					Timestamp timestamp = rs.getTimestamp(columnIndex);
					// 抽数到ClickHouse时，null被置为默认值，所以查询时需要把默认值还原为null
					if (timestamp != null && ClickHouseNullValue.NULL_DATETIME.compareTo(timestamp) == 0) {
						cell.setType(ValueType.DATETIME);
						cell.setNullValue();
					} else {
						cell.setDateValue(ValueType.DATETIME, timestamp);
					}
					break;
				case java.sql.Types.CLOB:
				case java.sql.Types.NCLOB:
					String value = null;
					Reader reader = rs.getCharacterStream(columnIndex);
					if (reader != null) {
						StringBuilder sb = new StringBuilder(4096);
						try {
							char[] charbuf = new char[4096];
							for (int j = reader.read(charbuf); j > 0; j = reader.read(charbuf)) {
								sb.append(charbuf, 0, j);
							}
						} catch (IOException e) {
							throw new SQLException(e.getMessage());
						}
						value = sb.toString();
					} else {
						if (!rs.wasNull()) {
							value = "";
						}
					}
					cell.setStringValue(value);
					break;
				case java.sql.Types.LONGVARBINARY:
					String lsValue = rs.getString(columnIndex);
					cell.setType(ValueType.BINARY);
					cell.setValue(lsValue);
					break;
				case java.sql.Types.BLOB:
				case java.sql.Types.BINARY:
					String defaultValue = "<BINARY>";
					cell.setType(ValueType.BINARY);
					cell.setValue(defaultValue);
					break;
				default:
					cell.setType(ValueType.UNKNOWN);
					String svalue = rs.getString(columnIndex);
					cell.setValue(svalue);
					break;
			}
			cellDataConvertor.convert(cell, columnIndex);
		}
		return cell;
	}

	/**
	 * 将GridData转化为字符串(可供测试使用)
	 * 
	 * @param gridData
	 *            GridData
	 */
	protected static String toString(GridData gridData) {
		StringBuilder buff = new StringBuilder();
		if (gridData != null) {
			List<String> headers = gridData.getStringHeaders();
			if (headers != null && headers.size() > 0) {
				for (int i = 0; i < headers.size(); i++) {
					String header = headers.get(i);
					if (i > 0) {
						buff.append(", ");
					}
					buff.append(header);
				}
				buff.append("n");
			}
			List<List<CellData>> datas = gridData.getData();
			if (datas != null) {
				for (List<CellData> rowData : datas) {
					if (rowData == null || rowData.size() == 0) {
						continue;
					}
					for (int i = 0; i < rowData.size(); i++) {
						CellData cell = rowData.get(i);
						if (i > 0) {
							buff.append(", ");
						}
						buff.append(cell.getStringValue());
					}
					buff.append("n");
				}
			}
		}
		return buff.toString();
	}

}

package smartbix;

import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Date;

import smartbixlibs.com.fasterxml.jackson.annotation.JsonIgnore;

/**
 * 单元格数据
 *
 * @since 2017-06-20
 */
public class CellData implements ICell, Serializable {
	private ValueType type;
	private String displayValue;
	private Object value;

	public void setType(ValueType type) {
		this.type = type;
	}

	public ValueType getType() {
		return type;
	}

	public void setValue(Object value) {
		this.value = value;
	}

	public Object getValue() {
		return value;
	}

	public void setDisplayValue(String displayValue) {
		this.displayValue = displayValue;
	}

	public String getDisplayValue() {
		return displayValue;
	}

	@JsonIgnore
	public byte[] getByteArrayValue() {
		return (byte[]) value;
	}

	public void setByteArrayValue(byte[] byteArrayValue) {
		this.value = byteArrayValue;
	}

	@JsonIgnore
	public Date getDateValue() {
		return (Date) value;
	}

	public void setDateValue(ValueType type, Date dateValue) {
		this.type = type;
		this.value = dateValue;
	}

	public void setDateValue(Date dateValue) {
		this.type = ValueType.DATETIME;
		this.value = dateValue;
	}

	@JsonIgnore
	public double getDoubleValue() {
		return ((Number) value).doubleValue();
	}

	public void setDoubleValue(double doubleValue) {
		type = ValueType.DOUBLE;
		this.value = doubleValue;
	}

	@JsonIgnore
	public BigDecimal getBigDecimalValue() {
		return (BigDecimal) value;
	}

	public void setBigDecimalValue(BigDecimal bigDecimal) {
		type = ValueType.BIGDECIMAL;
		this.value = bigDecimal;
	}

	@JsonIgnore
	public int getIntValue() {
		return ((Number) value).intValue();
	}

	public void setIntValue(int intValue) {
		type = ValueType.INTEGER;
		this.value = intValue;
	}

	@JsonIgnore
	public long getLongValue() {
		return ((Number) value).longValue();
	}

	public void setLongValue(long longValue) {
		type = ValueType.LONG;
		this.value = longValue;
	}

	@JsonIgnore
	public BigInteger getBigIntValue() {
		return ((BigInteger) value);
	}

	public void setBigIntValue(BigInteger longValue) {
		type = ValueType.BIGINT;
		this.value = longValue;
	}

	@JsonIgnore
	public String getStringValue() {
		return (String) value;
	}

	public void setStringValue(String stringValue) {
		type = ValueType.STRING;
		this.value = stringValue;
	}

	@JsonIgnore
	public boolean isNull() {
		return value == null;
	}

	public void setNullValue() {
		this.value = null;
	}

	@Override
	public int hashCode() {
		Object value = getValue();
		return value == null ? 0 : value.hashCode();
	}

	@Override
	public boolean equals(Object obj) {
		if (!(obj instanceof CellData)) {
			return false;
		}
		CellData tmp = (CellData) obj;
		if (this.type != tmp.type) {
			return false;
		}
		return value == null ? tmp.value == null : value.equals(tmp.value);
	}

}

最后

以上就是淡定汽车为你收集整理的BigDecimal返回前端数据超过16位导致精度丢失，比如6916750604278803063 变成 6916750604278803000的全部内容，希望文章能够帮你解决BigDecimal返回前端数据超过16位导致精度丢失，比如6916750604278803063 变成 6916750604278803000所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。