Java class file from PL/SQL procedure

I have a following PL/SQL procedure:

CREATE OR REPLACE PROCEDURE getDogInfo
(Dog_ID IN NUMBER, Dog_name OUT VARCHAR) AS
BEGIN
  SELECT Dog_name INTO Name
  FROM Dog_family
  WHERE ID = Dog_ID;
END;

I need to make a java class file that does the same. I've been trying like this:

import java.sql.*;
import java.io.*;

public class Procedure {

  public static void getDogInfo (int Dog_ID, String Dog_name)
    throws SQLException
    { String sql =
      "SELECT Dog_name INTO Name FROM Dog_family WHERE ID = Dog_ID";
    try { Connection conn = DriverManager.getConnection("jdbc:default:connection:");
      PreparedStatement apstmt = conn.prepareStatement(sql);
      apstmt.setInt(1, Dog_ID);
      apstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
      ResultSet rset = apstmt.executeQuery();
      rset.close();
      apstmt.close(); //Connection close
      }
    catch (SQLException e) {System.err.println(e.getMessage());
    }
  }
}

What am I doing wrong? Can someone help me get this working? Thanks

---

Have alook at this link showing you how to correctly use PreparedStatements.

You will find that the parameter should be ? not Dog_ID

Try

SELECT Name FROM Dog_family WHERE ID = ?

It will also show you how to iterate through your resultSet

---

Well, you do not tell us what the problem is, but I see several issue right away:

Your select statement should not have an INTO clause. That is a PL/SQL construct. You need to return the result of the query back as a result set.

Your input parameter, Dog_ID will not be used, because you have not named the parameter correctly in the SQL statement.

Java string parameters cannot be updated within the method, which I am assuming that is what you are attempting. You either need to return a string value from the method, or use a StringBuilder reference, or some other container to pass in. See this link There is no "out" parameter to register. Read up on result sets here

So, change your SQL statement to something like this(since you are using a positional parameter as opposed to a named parameter):

"SELECT Dog_name FROM Dog_family WHERE ID = ?"

---

You should read about JDBC (and Java in general too).

The query should be :

SELECT Name 
FROM Dog_family 
WHERE ID = ?

(assuming Name is the column name you are selecting from the table - it wasn't clear whether Name or Dog_name was the column name).

Then after you execute the query and get a result set :

String name = null;

if (rset.next()) {
  name = rset.getInt (1);
}
...
return name;

Finally, your function should return a String. You can't pass the String as a parameter and update its value. String is immutable in Java.

One more thing - the line apstmt.registerOutParameter(2, java.sql.Types.VARCHAR); is not needed. registerOutParameter is only used with CallableStatement , which is a statement you use to execute a stored procedure.

链接地址: http://www.djcxy.com/p/20896.html

上一篇: 使用数组数组作为指向Java中可扩展的类列表的指针

下一篇: 来自PL / SQL过程的Java类文件