java - 如何在不从中删除先前结果的情况下更新结果集

Question

我正在使用 jdbc 从数据库中检索数据。我有四个不同的查询，但是，第一个查询的结果用于获取第二个和第四个查询的数据。但是，结果集会在我运行其他查询时得到更新。那么有什么方法可以保留结果集并在其中添加新结果。

这是我的代码:

class GetData{

     String toDate;
     String fromDate;


        GetData(String d1,String d2) throws ClassNotFoundException, SQLException, ParseException, TransformerException, ParserConfigurationException  {

            toDate=d1;
            fromDate=d2;
            Connection connection= null;
            ResultSet resultset= null;

            String customerquery="SELECT o.ordernumber,o.orderdate,o.customernumber,c.customername,c.addressLine1,c.postalCode,c.city,c.country from orders o join customers c  on o.customernumber=c.customernumber where orderdate between ? and ?";
            String orderdetailquery="SELECT orderNumber,productCode,quantityOrdered,priceEach,orderLineNumber,(quantityOrdered * priceEach) as total FROM orderdetails where ordernumber=?";
            String productsquery="SELECT productName,productLine,productVendor FROM products where productcode=?";
            String employeequery="SELECT c.salesRepEmployeeNumber,e.firstname,e.lastname,o.officecode,o.city from customers c join employees e on c.salesRepEmployeeNumber = e.employeeNumber join offices o on e.officecode=o.officecode where c.customernumber=?";

            Class.forName("com.mysql.cj.jdbc.Driver");
            connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/classicmodels","root","root");

            if(d1!=null || d2!=null) {

                PreparedStatement p1= connection.prepareStatement(customerquery);
                p1.setString(1, toDate);
                p1.setString(2,fromDate);
                resultset= p1.executeQuery();

                PreparedStatement p2= connection.prepareStatement(orderdetailquery);
                while(resultset.next()) {
                    p2.setString(1, resultset.getString("orderNumber"));
                }

                resultset=p2.executeQuery();

                PreparedStatement p3= connection.prepareStatement(productsquery);

                while (resultset.next()) {
                    p3.setString(1, resultset.getString("productcode"));
                }

                resultset=p3.executeQuery();

                PreparedStatement p4=connection.prepareStatement(employeequery);
                while(resultset.next()) {
                p4.setString(1, resultset.getString("customernumber"));
                }
                resultset=p4.executeQuery();

                resultset.close();
                connection.close();   
            }
        }

我正在尝试使用包含列 customernumber 的第一个查询的结果来获取数据。同时，执行代码时出现错误，找不到 customernumber 列。那么，如何在其他查询中使用第一个查询的结果。此外，我正在尝试在一个结果集中获取所有查询结果，因为我正在尝试使用 DOM 从中创建一个 xml。

Answer 1

您可以将所有内容放在一个查询 中。使用连接。

select o.ordernumber,o.orderdate
    , o.customernumber,c.customername
    , c.addressLine1,c.postalCode,c.city,c.country 
    , t1.productCode, t1.quantityOrdered, t1.priceEach, t1.orderLineNumber,(t1.quantityOrdered * t1.        priceEach) as total 
    , t2.productName, t2.productLine, t2.productVendor
    , c.salesRepEmployeeNumber,t4.firstname,t4.lastname,t3.officecode,t3.city
from orders o 
join customers c  on o.customernumber=c.customernumber 
left join orderdetails t1 on t1.orderNumber = o.orderNumber
left join products t2 on t2.productCode = t1.productCode
left join offices t3 on t3.offiecode = c.customernumber
left join employees t4 on t3.officecode = t4.officecode