Java 如何从 jdbc 调用存储函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19333011/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to call a stored function from jdbc?
提问by Gaurav
I am creating a login page using custom tag, in the below code i want to execute a stored oracle function where that function will take 2 parameter (name,password) to authenticate and return a number. But when i compile the below code it gives an error saying ( found: int) incompatible type. please tell me where am i going wrong ? am i calling the function correctly ?
我正在使用自定义标签创建登录页面，在下面的代码中，我想执行一个存储的 oracle 函数，该函数将采用 2 个参数（名称、密码）进行身份验证并返回一个数字。但是当我编译下面的代码时，它给出了一个错误，说 ( found: int) 不兼容的类型。请告诉我我哪里错了？我是否正确调用了该函数？
package pack.java;
import pack.java.MyModel;
import java.io.*;
import java.lang.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.jsp.*;
import javax.servlet.jsp.tagext.*;
import java.sql.*;
public class MyController extends TagSupport
{
HttpServletRequest request;
HttpServletResponse response;
public int doStartTag()throws JspException
{
request = (HttpServletRequest)pageContext.getRequest();
response = (HttpServletResponse)pageContext.getResponse();
return EVAL_PAGE;
}
public void check()
{
HttpSession mysession = request.getSession();
Connection con;
CallableStatement stmt;
JspWriter out = pageContext.getOut();
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException ex) {}
try {
String aa = (String)MyModel.name.trim();
String bb = (String)MyModel.pass.trim();
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","gaurav","oracle");
stmt = con.prepareCall("select usercheck1(?,?) from dual");
stmt.setString(1, aa);
stmt.setString(2, bb);
rs = stmt.executeQuery();
try {
while (rs.next()) {
String empid = rs.getString (1);
mysession.setAttribute("user", empid);
if (empid != null) {
response.sendRedirect("/Myjsp/selectaction.jsp");
}
else
out.println("InValid User");
}
}
catch(Exception ex) {}
}
catch(SQLException ex) {}
}
public int doEndTag() throws JspException {
check();
return EVAL_PAGE;
}
}
below is the stored function
下面是存储的函数
create or replace function usercheck1
(uname varchar2, upass varchar2)
return number
as
numb number;
begin
select (employe_id)
into numb
from record
where name = uname
and password = upass;
return numb;
end usercheck1;
/
executing the function using the below statement
使用以下语句执行函数
select usercheck1 ('ghg','aa') from dual;
回答by Shashank Kadne
executeUpdate()returns int, See herefor more details. I believe you are expecting a ResultSet,
executeUpdate()返回int，请参阅此处了解更多详细信息。我相信你在期待一个ResultSet，
Change below line,
更改以下行，
rs=stmt.executeUpdate();
to
到
rs=stmt.executeQuery();
rs=stmt.executeQuery();
See executeQueryfor more info.
有关更多信息，请参阅executeQuery。
回答by Sumit
First off you say the proc returns a number but your code is expecting a resultset which returns a string? Anyway, assuming that your proc returns an int. Change these lines
首先，您说 proc 返回一个数字，但您的代码期望返回一个字符串的结果集？无论如何，假设您的 proc 返回一个 int。更改这些行
stmt=con.prepareCall("call usercheck1(?,?)");
stmt.setString(1,aa);
stmt.setString(2,bb);
rs=stmt.executeUpdate();
to
到
stmt=con.prepareCall ("{? = call usercheck1(?, ?)}");
stmt.registerOutParameter (1, Types.INTEGER);
stmt.setString(2,aa);
stmt.setString(3,bb);
stmt.execute();
int output =stmt.getInt (1);
If you are actually expecting a string as a result from the proc, then change to
如果您实际上期望来自 proc 的结果是一个字符串，则更改为
stmt=con.prepareCall ("{? = call usercheck1(?, ?)}");
stmt.registerOutParameter (1, Types.VARCHAR2);
stmt.setString(2,aa);
stmt.setString(3,bb);
stmt.execute();
String output =stmt.getString (1);
ResultSets are used for cursors, and would require you to specify your out parameters as such. Hope that helps.
ResultSets 用于游标，并且需要您指定您的输出参数。希望有帮助。
http://docs.oracle.com/cd/A84870_01/doc/java.816/a81354/samapp2.htm
http://docs.oracle.com/cd/A84870_01/doc/java.816/a81354/samapp2.htm
If you are using a newer version of oracle >= 9i, you may want to use the begin end syntax.
如果您使用的是较新版本的 oracle >= 9i，您可能需要使用 begin end 语法。
回答by Sylvain Leroux
To execute a function, the canonical way is to use a CallableStatement. This is a special case (subclass) of PreparedStatement.
要执行一个函数，规范的方法是使用CallableStatement. 这是 的一个特例（子类）PreparedStatement。
In addition, you have to specify the rightoutput parameters type using one of the registerOutParametermethods. Once the call is completed (execute), you extract the output value from the statement itself. Not through a ResultSet.
此外，您必须使用其中一种方法指定正确的输出参数类型registerOutParameter。一旦调用完成 ( execute)，您就可以从语句本身中提取输出值。不是通过一个ResultSet.
All of this lead to something like:
所有这些都会导致类似的事情：
CallableStatement stmt = con.prepareCall("{? = call usercheck1(?, ?)}");
# ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# Prepare a `call`
stmt.registerOutParameter(1, Types.INTEGER);
# Setup out parameter type ^^^^^^^^^^^^^
stmt.setString(2,aa);
stmt.setString(3,bb);
stmt.execute();
# ^^^^^^^
# execute the statement
int output = stmt.getInt(1);
# ^^^^^^^^^^^
# Extract the result from the statement itself
A totally different way of doing, is to actually use a SELECTquery to call the function. This is what was suggested by the OP in the question. That way, you can use a ResultSetas usual to extract the values:
一种完全不同的方法是实际使用SELECT查询来调用函数。这是 OP 在问题中提出的建议。这样，您可以ResultSet像往常一样使用 a来提取值：
PreparedStatement stmt = con.prepareStatement("SELECT usercheck1(?, ?) FROM DUAL");
# ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# Prepare a statement
stmt.setString(2,aa);
stmt.setString(3,bb);
ResultSet rs = stmt.executeQuery();
# ^^^^^^^^^^^^
# execute the query
while(rs.next()) {
int output = rs.getInt(1);
# ^^^^^^^^^
# Extract the result from the `ResultSet`
# ...
# do whatever you want with the data
}
回答by mancini0
Others suggests running the query through a select statement, but this will throw an exception if the function does any DML. Using a CallableStatement is the best option.
其他人建议通过 select 语句运行查询，但如果该函数执行任何 DML，这将引发异常。使用 CallableStatement 是最好的选择。
回答by Paulo Merson
Here's a more contemporary solution using JPA (javax.persistence.EntityManager):
这是使用 JPA ( javax.persistence.EntityManager)的更现代的解决方案：
@PersistenceContext
private EntityManager entityManager;
. . .
final int myResult;
Session session = entityManager.unwrap(Session.class);
session.doWork(connection -> {
try (CallableStatement function =
connection.prepareCall("{ ? = call usercheck1(?, ?) }")) {
function.registerOutParameter(1, Types.INTEGER);
function.setString(2, aa);
function.setString(3, bb);
function.execute();
myResult = function.getInt(1);
}
} );
These are the main dependencies to indicate what lib versions the solution works with:
这些是指示解决方案适用的 lib 版本的主要依赖项：
compile 'com.oracle:ojdbc6:11.2.0.4.0'
compile 'org.hibernate:hibernate-core:5.4.7.Final'
compile 'org.hibernate.javax.persistence:hibernate-jpa-2.1-api:1.0.2.Final'
As an alternative to doWork()and assigning the stored function result with getXxx(1), you can use doReturningWork().
作为doWork()对存储函数结果的替代和分配getXxx(1)，您可以使用doReturningWork().
In Kotlin
在科特林
Here's the solution in Kotlin (and using doReturningWork):
这是 Kotlin 中的解决方案（并使用doReturningWork）：
@PersistenceContext
lateinit var entityManager: EntityManager
. . .
val session : Session = entityManager.unwrap(Session::class.java)
var myResult = session.doReturningWork { connection: Connection ->
connection.prepareCall("{ ? = call usercheck1(?, ?) }").use { function ->
function.registerOutParameter(1, Types.INTEGER)
function.setString(2, aa)
function.setString(3, bb)
function.execute()
return@doReturningWork function.getInt(1)
}
}
