java - 如何修复包含多行错误的结果

Question

我在 MySQL 中编写了存储过程，如下所示(它有效):

DELIMITER //

CREATE PROCEDURE getBrandRows(
    IN pBrand VARCHAR(30),
    OUT pName VARCHAR(150),
    OUT pType VARCHAR(200),
    OUT pRetailPrice FLOAT)
BEGIN
    SELECT p_name, p_type, p_retailprice INTO pName, pType, pRetailPrice
    FROM part
    WHERE p_brand LIKE pBrand;
END//

DELIMITER ;

我尝试返回多个结果并显示它们。我已经尝试了堆栈和互联网上描述的许多方法，但这对我没有帮助。我已经编辑了整个代码并创建了一个简单的代码，以便你们可以粘贴并编译。它应该可以工作，但有错误。这是代码:

package javamysqlstoredprocedures;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class JavaMySqlStoredProcedures {

    private final String DEFAULT_DRIVER = "com.mysql.jdbc.Driver";
    private final String DB_URL = "jdbc:mysql://anton869.linuxpl.eu:3306/" 
            + "anton869_cars?noAccessToProcedureBodies=true"; 
    private final String DB_USER = "xxx";
    private final String DB_PASSWORD = "xxx";

    class CallStoredProcedureAndSaveXmlFile extends SwingWorker<Void, Void> {

        @Override
        public Void doInBackground() {
            displaySql();
            return null;
        }

        @Override
        public void done() {
        }

        private void displaySql() {
        try {
            System.out.println("Connecting to MySQL database...");
            Class.forName(DEFAULT_DRIVER);
            try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, 
                    DB_PASSWORD)) {
                System.out.println("Connected to MySQL database");
                CallableStatement cs = conn.prepareCall("{CALL getBrandRows("
                        + "?, ?, ?, ?)}");
                cs.setString(1, "Brand#13");
                cs.registerOutParameter(2, Types.VARCHAR);
                cs.registerOutParameter(3, Types.VARCHAR);
                cs.registerOutParameter(4, Types.FLOAT);

                boolean results = cs.execute();
                while (results) {
                    ResultSet rs = cs.getResultSet();
                    while (rs.next()) {
                        System.out.println("p_name=" + rs.getString("p_name"));
                        System.out.println("p_type=" + rs.getString("p_type"));
                        System.out.println("p_retailprice=" + rs
                                .getFloat("p_retailprice"));
                    }
                    rs.close();
                    results = cs.getMoreResults();
                }
                cs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        }

    }

    public JavaMySqlStoredProcedures() {
         new CallStoredProcedureAndSaveXmlFile().execute();
    }

    public static void main(String[] args) {
        JavaMySqlStoredProcedures jmssp = new JavaMySqlStoredProcedures();
    }

}

Answer 1

ResultSet 可以处理多个记录。我在您的代码中发现了一些错误。请尝试这些步骤

1. 将所有 close 方法移至 finally block 。

   try {
      //do something
   } catch (Exception e) {
      //do something
   } finally {
       try{
         resultSet.close();
         statement.close();
         connection.close();
       } catch (SQLException se) {
           //do something
       }
   }
   

2. 您可以将结果放入列表中。查看示例

   List<YourObject> list = new ArrayList<YourObject>();
   while (rs.next()) {
      YourObject obj = new Your Object();
      obj.setName(rs.getString("p_name"));
      obj.setType(rs.getString("p_type"));
      obj.setRetailPrice(rs.getFloat("p_retailprice"));
      list.add(obj);
      }
   

3. 确保您的查询正确且数据库连接正常。