java 从java中的mysql存储过程中获取参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15336557/
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
getting out parameter from mysql stored procedure in java
提问by ntstha
I am having problem retrieving OUT parameter from mysql stored procedure in java.
我在从 java 中的 mysql 存储过程检索 OUT 参数时遇到问题。
CALL proc_after_topic_add('newtest',@result);
SELECT @result;
this query gives me desired out parameter but how would i retrieve it in java.I tried using CallableStatement but i get
这个查询给了我想要的 out 参数，但我如何在 java.I 中检索它。我尝试使用 CallableStatement 但我得到了
java.sql.SQLException: Callable statments not supported.
error.Please guys help me. I have tried following
错误。请大家帮帮我。我试过跟随
String sql = "CALL proc_after_topic_add(?,?);";
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.setString(1, topicname);
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
ResultSet rs = cstmt.executeQuery();
if (rs.next()) {
if (rs.getInt(1) == 1) {
res = 0;
} else {
res = -1;
}
}
I havent posted stored procedure code because there is nothing wrong with it.
我没有发布存储过程代码，因为它没有任何问题。
PS:I a using mysql 5.5.21 and yes i should probably mention i am using mysql connector 3.0.15
Okay this is solved.For anyone who encounters the same problem,just download latest version of mysql connector.
好的，这就解决了。对于遇到同样问题的人，只需下载最新版本的 mysql 连接器。
采纳答案by Akshay Joy
Error in this line
此行中的错误
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
change like this
像这样改变
String sql = "CALL proc_after_topic_add(?,?);";
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
回答by Deepak
Create a schema test and create a table employee in schema with 2 columns.
id and employeename and insert some data.
Use this Stored Procedure.
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`get_count_name1` $$
CREATE PROCEDURE `test`.`get_count_name1`(IN the_name VARCHAR(64),OUT
the_count INT)
BEGIN
SELECT COUNT(*) INTO the_count from employee where employeename=the_name;
END$$
DELIMITER ;
Use this example.
username and password are root and root for me. change as per your
requirement. Here i am counting the occurence of employeename="deepak"
使用这个例子。用户名和密码对我来说是 root 和 root。根据您的
要求更改。在这里，我正在计算员工名 =“deepak”的出现
import java.sql.*;
public class JDBCExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/test";
// Database credentials
static final String USER = "root";
static final String PASS = "root";
public static void main(String[] args) {
Connection conn = null;
CallableStatement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
//STEP 4: Execute a query
System.out.println("Creating statement...");
String sql = "{call get_count_name1 (?, ?)}";
stmt = conn.prepareCall(sql);
//Bind IN parameter first, then bind OUT parameter
String name = "Deepak";
stmt.setString(1, name); // This would set ID as 102
// Because second parameter is OUT so register it
stmt.registerOutParameter(2, java.sql.Types.INTEGER);
//Use execute method to run stored procedure.
System.out.println("Executing stored procedure..." );
stmt.execute();
int count=stmt.getInt(2);
System.out.println(count);
stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
}//end JDBCExample
