Calling stored procedures from Java

4
votes

I'm trying to call a mySQL stored procedure from my Java application. When I call the stored procedure from mySQL workbench, it works and I get the correct number of rows depending of the parameters I send. The problem comes when I try to call it from Java, I don't get any result and I can't find out why. I've been following the oracle documentation.

The stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `comprobarUsuario`(
IN usu varchar(20),
IN pass varchar(20),
OUT idusuarios int)
BEGIN
  SELECT idusuarios
  FROM usuarios
  WHERE usuarios.nombreUsuario = usu and usuarios.contraseña = pass;

END

The Java Class where I'm trying to call the stored procedure:

public class ConectorSQL {

public static final String URL = "jdbc:mysql://localhost:3306/ProyectoBD?autoReconnect=true&useSSL=false";
public static final String USERNAME = "root";
public static final String PASSWORD = "1627Admin";

public static Connection getConnection() {
    Connection con = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        con = (Connection) DriverManager.getConnection(URL, USERNAME, PASSWORD);
        //System.out.println("Conexion exitosa");

    } catch (Exception e) {
        System.out.println("Error de conexión con la base de datos");
    }
    return con;
}

public void mostrarDatos() {
    try {
        Connection con = null;
        con = getConnection();

        CallableStatement cs;
        cs = con.prepareCall("{CALL comprobarUsuario(?,?,?)}");
        cs.setString(1,"Jorge" );
        cs.setString(2, "1627Jorge");
        cs.registerOutParameter(3, Type.INT);
        ResultSet rs2 = cs.executeQuery();

        if(rs2.next()){
            System.out.println(true);
        }
        int resultado = cs.getInt("idusuarios");
        System.out.println(resultado);
        con.close();

    } catch (Exception e) {

    }
  }
}
3
javamysql

3 Answers

7
votes

Procedure and Java code need little adoption. So lets start with the procedure:

create DEFINER=`root`@`localhost` PROCEDURE `comprobarUsuario`(
IN usu varchar(20),
IN pass varchar(20),
OUT idusuarios int)
BEGIN
  SELECT usuarios.idusuarios
  into idusuarios
  FROM usuarios
  WHERE usuarios.nombreUsuario = usu and usuarios.contraseña = pass;
end

You want to retrieve the value "idusuarios" from the database. So you need to save it in the parameter value. Make sure parameter and value in the select clause are different from each other or provide the column name via [tablename].[column] or alias.

Java problem: You don't need the ResultSet Object at all. To access the value from a procedure output parameter use cs.getInt() provided by the CallableStatement class.

public void mostrarDatos(){
    Connection con = null;
    try {
        con = getConnection();
        CallableStatement cs = con.prepareCall("{CALL comprobarUsuario(?,?,?)}");
        cs.setString(1, "Jorge");
        cs.setString(2, "1627Jorge");
        cs.registerOutParameter(3, java.sql.Types.INTEGER);
        cs.executeUpdate();

        int resultado = cs.getInt(3);
        System.out.println(resultado);  

    } catch (Exception e) {
        System.out.println(e);
    } finally {
        if(con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                System.out.println(e);
            }
        }
    }
}
0
votes
public void mostrarDatos() 
{
    try {
        Connection con = null;
        con = getConnection();

        CallableStatement cs;
        cs = con.prepareCall("{CALL comprobarUsuario(?,?,?)}");
        cs.setString(1,"Jorge" );
        cs.setString(2, "1627Jorge");
        cs.registerOutParameter(3, Type.INT);
        cs.execute();
        ResultSet rs2 = cs.getResultSet();

        if(rs2.next()){
            System.out.println(true);
        }
        int resultado = cs.getInt(3); // check whether the column name is correct
        System.out.println(resultado);
        con.close();

    } catch (Exception e) {

    }
}
0
votes

If using a third party is acceptable in your case, you could use jOOQ's stored procedure code generation capabilities (you don't have to use jOOQ otherwise).

jOOQ would generate a class like this (simplified):

public class Comprobarusario extends AbstractRoutine<Integer> {
    public void setUsu(String usu) { ... }
    public void setPass(String pass) { ... }
    public Integer getIdusarios() { ... }
}

And you can then call it like this:

Comprobarusario call = new Comprobarusario();
call.setUsu(...);
call.setPass(...);
call.execute(configuration);
Integer idusarios = call.getIdusarios();

// This will contain the contents of your procedure's result set:
Results results = call.getResults();

Disclaimer: I work for the company behind jOOQ.