gpt4 book ai didi

java - 通过Java调用存储过程(MySql)时出错

转载 作者:行者123 更新时间:2023-11-29 23:42:30 25 4
gpt4 key购买 nike

我正在创建一个java代码来从mysql Db调用存储过程。但是当我编译代码时出现以下错误。我已附上代码。我是存储过程的初学者,正在寻求帮助。提前致谢。 存储过程:

    CREATE DEFINER=`root`@`localhost` PROCEDURE `get_in_out_status_for_manager`(IN id int, OUT date_info date,out empname varchar(35),
out EmpID varchar(10),
out CardId Varchar(10),
out EntryTime Datetime,
out ExitTime datetime,
out WorkTime time)
BEGIN
SELECT Date,empname,EmpID,CardID,EntryTime,ExitTime,WorkTime from pax_attd
join employee_master on right(pax_attd.EmpID,3)=employee_master.employee_id
where ((employee_master.reporting_employee_id=id) and (date=date_info));

END

Java Code:

package mysqltoxl;
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/PaxWeb";

// Database credentials
static final String USER = "root";
static final String PASS = "pax";

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_in_out_status_for_manager(?, ?, ?, ?, ?, ?, ?, ?)}";
stmt = conn.prepareCall(sql);

//Bind IN parameter first, then bind OUT parameter
int EmpId = 401;

stmt.setInt(1,EmpId);



// Because second parameter is OUT so register it

stmt.registerOutParameter(2, java.sql.Types.DATE);
stmt.registerOutParameter(3, java.sql.Types.VARCHAR);
stmt.registerOutParameter(4, java.sql.Types.VARCHAR);
stmt.registerOutParameter(5, java.sql.Types.VARCHAR);
stmt.registerOutParameter(6, java.sql.Types.TIME);
stmt.registerOutParameter(7, java.sql.Types.TIME);
stmt.registerOutParameter(8, java.sql.Types.TIME);

//Use execute method to run stored procedure.
System.out.println("Executing stored procedure..." );
stmt.execute();


Date Date = stmt.getDate(2);
String empname = stmt.getString(3);
String EmpID = stmt.getString(4);
String CardId = stmt.getString(4);
Date EntryTime= stmt.getDate(5);
Date ExitTime = stmt.getDate(6);
Time WorkTime = stmt.getTime(7);
System.out.println("Emp Name with ID:" +
EmpId + " is " + empname);
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

我的错误是:

Connecting to database...
Creating statement...
Executing stored procedure...
Emp Name with ID:401 is null
Goodbye!

最佳答案

更改 out 参数名称并在 select 语句中添加 into 子句。创建过程如下:

CREATE DEFINER=root@localhost 过程 get_in_out_status_for_manager(
IN id int,
OUT date_info 日期,
out out_empname varchar(35),
输出 out_EmpID varchar(10),
out out_CardId Varchar(10),
out out_EntryTime 日期时间,
out out_ExitTime 日期时间,
out_WorkTime 时间)
开始
选择日期、员工姓名、员工 ID、卡 ID、进入时间、退出时间、工作时间
进入date_info、out_empname、out_EmpID、out_CardID、out_EntryTime、out_ExitTime、out_WorkTime
来自 pax_attd 加入右侧的employee_master(pax_attd.EmpID,3)=employee_master.employee_id 其中 ((employee_master.reporting_employee_id=id) 和 (date=date_info));

    END

关于java - 通过Java调用存储过程(MySql)时出错,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26092714/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com