java - Jackcess 列乱序？

Question

我有一个应用程序，它以 xml 格式从 YQL 表 yahoo.finance.quotes 下载财务数据，然后将此数据写入 MS Access 数据库。我正在使用 Jackcess 与数据库交互，但我在添加行时遇到了一个奇怪的问题。

当我调用 .addRow() 时，所有字段都正确写入文件，只有少数字段被切换。这是我将下载和使用(使用 JAXB)的 xml 文件的示例...

<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng" yahoo:count="1" yahoo:created="2013-08-01T18:03:53Z" yahoo:lang="en-US">
  <results>
    <quote>
      <BookValue>236.698</BookValue>
      <EarningsShare>34.565</EarningsShare>
      <EPSEstimateCurrentYear>43.54</EPSEstimateCurrentYear>
      <EPSEstimateNextYear>51.34</EPSEstimateNextYear>
      <EPSEstimateNextQuarter>11.82</EPSEstimateNextQuarter>
      <DaysLow>895.00</DaysLow>
      <DaysHigh>903.07</DaysHigh>
      <YearLow>623.41</YearLow>
      <YearHigh>928.00</YearHigh>
      <LastTradePriceOnly>901.83</LastTradePriceOnly>
      <FiftydayMovingAverage>893.586</FiftydayMovingAverage>
      <TwoHundreddayMovingAverage>856.999</TwoHundreddayMovingAverage>
      <Open>895.67</Open>
      <PreviousClose>887.75</PreviousClose>
      <PriceSales>5.30</PriceSales>
      <PriceBook>3.75</PriceBook>
      <PERatio>25.68</PERatio>
      <PEGRatio>1.41</PEGRatio>
      <PriceEPSEstimateCurrentYear>20.39</PriceEPSEstimateCurrentYear>
      <PriceEPSEstimateNextYear>17.29</PriceEPSEstimateNextYear>
      <ShortRatio>1.90</ShortRatio>
      <OneyrTargetPrice>985.32</OneyrTargetPrice>
      <Volume>1460983</Volume>
      <Ask>901.90</Ask>
      <AverageDailyVolume>2330360</AverageDailyVolume>
      <Bid>901.54</Bid>
    </quote>
  </results>
</query>
<!--  total: 222  -->
<!--  engine4.yql.ne1.yahoo.com  -->

这是生成的 Access 数据库的 xml 导出...

<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2013-08-01T13:28:55">
  <GOOG>
    <ID>1375380249345</ID>
    <BOOK_VALUE>901.9</BOOK_VALUE>
    <EARNINGS_SHARE>3.75</EARNINGS_SHARE>
    <EPS_ESTIMATE_CURRENT_YEAR>34.565</EPS_ESTIMATE_CURRENT_YEAR>
    <ESP_ESTIMATE_NEXT_YEAR>43.54</ESP_ESTIMATE_NEXT_YEAR>
    <EPS_ESTIMATE_NEXT_QUARTER>51.34</EPS_ESTIMATE_NEXT_QUARTER>
    <DAYS_LOW>11.82</DAYS_LOW>
    <DAYS_HIGH>895</DAYS_HIGH>
    <YEAR_LOW>903.07</YEAR_LOW>
    <YEAR_HIGH>623.41</YEAR_HIGH>
    <LAST_TRADE_PRICE_ONLY>1.9</LAST_TRADE_PRICE_ONLY> <!-- Offending column -->
    <FIFTYDAY_MOVING_AVERAGE>928</FIFTYDAY_MOVING_AVERAGE>
    <TWO_HUNDREDDAY_MOVING_AVERAGE>901.83</TWO_HUNDREDDAY_MOVING_AVERAGE>
    <OPEN>893.586</OPEN>
    <PRICE_SALES>856.999</PRICE_SALES>
    <PRICE_BOOK>895.67</PRICE_BOOK>
    <PE_RATIO>5.3</PE_RATIO>
    <PEG_RATIO>3.75</PEG_RATIO>
    <PRICE_EPSESTIMATE_CURRENT_YEAR>25.68</PRICE_EPSESTIMATE_CURRENT_YEAR>
    <PRICE_EPS_ESTIMATE_NEXT_YEAR>1.41</PRICE_EPS_ESTIMATE_NEXT_YEAR>
    <ONEYR_TARGET_PRICE>985.32</ONEYR_TARGET_PRICE>
    <SHORT_RATIO>1460983</SHORT_RATIO> <!-- Offending column -->
    <VOLUME>2330360</VOLUME> <!-- Offending column -->
    <ASK>20.39</ASK>
    <AVERAGE_DAILY_VOLUME>901</AVERAGE_DAILY_VOLUME> <!-- Offending column -->
    <BID>17.29</BID>
  </GOOG>
</dataroot>

如果有帮助，这是我将 xml 中的值添加到数据库的代码...

public synchronized void storeData(Query query) throws InterruptedException, IOException {
    long idL = System.currentTimeMillis();
    BigDecimal id = new BigDecimal(idL);
    double bookValue = query.results.quote.getPriceBook();
    double earningsShare = query.results.quote.getEarningsShare();
    double epsEstimateCurrentYear = query.results.quote.getEPSEstimateCurrentYear();
    double epsEstimateNextYear = query.results.quote.getEPSEstimateNextYear();
    double epsEstimateNextQuarter = query.results.quote.getEPSEstimateNextQuarter();
    double daysLow = query.results.quote.getDaysLow();
    double daysHigh = query.results.quote.getDaysHigh();
    double yearLow = query.results.quote.getYearLow();
    double yearHigh = query.results.quote.getYearHigh();
    double lastTradePriceOnly = query.results.quote.getLastTradePriceOnly();
    double fiftydayMovingAverage = query.results.quote.getFiftydayMovingAverage();
    double twoHundreddayMovingAverage = query.results.quote.getTwoHundreddayMovingAverage();
    double open = query.results.quote.getOpen();
    double priceSales = query.results.quote.getPriceSales();
    double priceBook = query.results.quote.getPriceBook();
    double peRatio = query.results.quote.getPERatio();
    double pegRatio = query.results.quote.getPEGRatio();
    double priceEPSEstimateCurrentYear = query.results.quote.getPriceEPSEstimateCurrentYear();
    double priceEPSEstimateNextYear = query.results.quote.getPriceEPSEstimateNextYear();
    double shortRatio = query.results.quote.getShortRatio();
    double oneyrTargetPrice = query.results.quote.getOneyrTargetPrice();
    int volume = query.results.quote.getVolume();
    double ask = query.results.quote.getAsk();
    int averageDailyVolume = query.results.quote.getAverageDailyVolume();
    double bid = query.results.quote.getBid();
    dBTable.addRow(id, bookValue, earningsShare, epsEstimateCurrentYear, epsEstimateNextYear, epsEstimateNextQuarter, daysLow, daysHigh,
        yearLow, yearHigh, close, fiftydayMovingAverage, twoHundreddayMovingAverage, open, priceSales, priceBook,
            peRatio, pegRatio, priceEPSEstimateCurrentYear, priceEPSEstimateNextYear, shortRatio, oneyrTargetPrice, volume, ask, averageDailyVolume, bid);
}

如您所见，仅最后交易价格、空头比率、交易量 和平均每日交易量 混杂在一起。

谁能告诉我这是为什么以及如何纠正它？谢谢。

Answer 1

至少您的部分问题似乎是 Jackcess 中的 addRow() 方法需要“按照表列的顺序”提供值“，(强调他们的，引用:“添加行”部分 here )。您的 addRow() 调用指定

... shortRatio, oneyrTargetPrice, volume ...

但是 Access 表的 XML 转储表明列顺序是

<ONEYR_TARGET_PRICE>985.32</ONEYR_TARGET_PRICE>
<SHORT_RATIO>1460983</SHORT_RATIO> <!-- Offending column -->
<VOLUME>2330360</VOLUME> <!-- Offending column -->

您可能还需要根据列类型仔细检查变量类型，以确保它们也匹配。