从Java调用存储过程 [英] Calling stored procedures from Java

问题描述

我正在尝试从Java应用程序调用mySQL存储过程.当我从mySQL工作台调用存储过程时，它可以工作，并且根据发送的参数获得正确的行数.当我尝试从Java调用它时，问题就来了，我没有得到任何结果，而且我也找不到原因.我一直在关注oracle文档.

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.

存储过程:

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

我要在其中调用存储过程的Java类:

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) {

    }
  }
}

推荐答案

过程和Java代码几乎不需要采用.因此，让我们从以下过程开始:

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

您要从数据库中检索值"idusuarios".因此，您需要将其保存在参数值中.确保select子句中的参数和值彼此不同，或者通过[tablename].[column]或别名提供列名.

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问题:您根本不需要ResultSet对象.要从过程输出参数访问值，请使用CallableStatement类提供的cs.getInt().

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);
            }
        }
    }
}

这篇关于从Java调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！