java - 如何使用PreparedStatement创建列表？

我有以下代码：

- - - -类 - - - - - -

private class SystemHealthAlert implements Work {
        List<MonitorAlertInstance>  systemHealthAlertList;
        private String queryString;
    //  private java.util.Date startDate;
    //  private java.util.Date endDate;

        @Override
        public void execute(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(queryString);

            int index = 1;
            ResultSet rs = ps.executeQuery();


            int columnCount = rs.getMetaData().getColumnCount();
            while(rs.next())
            {
                //String[] row = new String[columnCount];
                //results.set(index, element);
                //for (int i=0; i <columnCount ; i++)
              //  {
              //     row[i] = rs.getString(i + 1);
             //   }
                systemHealthAlertList.add(row);
            }


            rs.close();
            ps.close();
        }
    }

- - - - -方法 - - - - - -

public List<MonitorAlertInstance> getSystemHealthAlert(Long selectedSensorId) {
        List<MonitorAlertInstance>  systemHealthAlertList;

        try {
            // Add SELECT with a nested select to get the 1st row
            String queryString = "select min(MONITOR_ALERT_INSTANCE_ID) as MONITOR_ALERT_INSTANCE_ID, description" +
                    "                      from ems.monitor_alert_instance  " +
                    "                          where description in (select description from monitor_alert_instance" +
                    "                            where co_mod_asset_id = " + selectedSensorId +
                    "                                               )" +
                    "                       group by description";

            SystemHealthAlert work = new SystemHealthAlert();
        //  work.coModAssetId = coModAssetId;
            work.queryString = queryString;

            getSession().doWork(work);

            systemHealthAlertList = work.systemHealthAlertList;

        } catch (RuntimeException re) {
        //  log.error("getMostRecentObservationId() failed", re);
            throw re;
        }
        //log.info("End");
        return systemHealthAlertList;
    }

我的查询从数据库返回三行。如何从具有查询的所有三行的类中返回systemHealthAlertList。

最佳答案

在方法execute中，应使用List<MonitorAlertInstance> systemHealthAlertList实例填充MonitorAlertInstance。在MonitorAlertInstance循环内创建一个while的新实例，您可以在其中检索数据：

//You don't need this line, remove it
//int columnCount = rs.getMetaData().getColumnCount();
while(rs.next()) {
    //create a new instance of MonitorAlertInstance per ResultSet row
    MonitorAlertInstance monitor = new MonitorAlertInstance();
    //set the fields from the ResultSet in your MonitorAlertInstance fields
    //since I don't know the fields of this class, I would use field1 and field2 as examples
    monitor.setField1(rs.getInt(1));
    monitor.setField2(rs.getString(2));
    //and on...
    systemHealthAlertList.add(monitor);
}

除此问题外，还应在使用List<MonitorAlertInstance> systemHealthAlertList变量之前对其进行初始化：

systemHealthAlertList = new ArrayList<MonitorAlertInstance>();
while(rs.next()) {
    //content from previous code...
}