anton86993 anton86993 - 1 month ago 10
Java Question

How to fix the result consisted of more than one row error

I wrote stored procedure in MySQL which looks like this (it works):

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 ;


I try to return multiple results and display them. I've tried many ways described here on Stack and in Internet but that does not help me. I have edited my entire code and created a simple one so you can guys paste it and compile. It should work but with error. Here is the code:

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

ResultSet can handle multiple records.I found some errors in your code.Try these steps

  1. Move your all close method to finally block.

    try {
       //do something
    } catch (Exception e) {
       //do something
    } finally {
        try{
          resultSet.close();
          statement.close();
          connection.close();
        } catch (SQLException se) {
            //do something
        }
    }
    
  2. Remove the line results = cs.getMoreResults();

  3. Put your result into List. See sample

    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);
       }
    
  4. Make sure your query is correct and database connection is Ok.