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

Question

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 1

Your stored procedure returns more than one row. Just correct logic behind your select query inside the stored procedure it should return only one row.

here how to return multiple value

Answer 2

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. You can 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);
      }
   

3. Make sure your query is correct and database connection is Ok.

Answer 3

I am posting correct solution to everybody who have smiliar problem:

1. Corrected Stored Procedure:

DELIMITER //

CREATE PROCEDURE getBrandRows(
    IN pBrand VARCHAR(30))
BEGIN
    SELECT p_name, p_type, p_retailprice 
    FROM part
    WHERE p_brand = pBrand;
END//

DELIMITER ;

2. Corrected Java 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() {
            Connection conn = null;
            CallableStatement cs = null;
            ResultSet rs = null;
            try {
                System.out.println("Connecting to MySQL database...");
                Class.forName(DEFAULT_DRIVER);
                conn = DriverManager.getConnection(DB_URL, DB_USER, 
                        DB_PASSWORD);
                System.out.println("Connected to MySQL database");

                cs = conn.prepareCall("{CALL getBrandRows(?)}");
                cs.setString(1, "Brand#13");

                boolean results = cs.execute();
                while (results) {
                    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"));
                    }
                    results = cs.getMoreResults();
                }
            } catch (SQLException | ClassNotFoundException e) {
            } finally {
                try {
                    if (rs != null ) rs.close();
                    if (cs != null) cs.close();
                    if (conn != null) conn.close();
                } catch (SQLException e) {
                }
            }
        }

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

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

}

Answer 4

Don't use IN or OUT parameter if you just simply want to display result. And also you should add '%%' in your LIKE clause with the help of CONCAT function. Please try this one:

DELIMITER //

CREATE PROCEDURE getBrandRows(
    pBrand VARCHAR(30)
)

BEGIN

    SELECT p_name, p_type, p_retailprice INTO pName, pType, pRetailPrice
    FROM part
    WHERE p_brand LIKE CONCAT("%", pBrand, "%");

END//

DELIMITER ;