通过Java调用存储过程(MySql)时出错
正在创建一个java代码来调用mysql db中的存储过程。 但是当我编译代码时出现以下错误。我附上了代码。是一个初学存储过程寻求一些帮助。提前感谢。 存储过程:通过Java调用存储过程(MySql)时出错
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!
改变了参数名称,并添加到子句中的select语句。 创建过程如下:
CREATE DEFINER = root
@localhost
PROCEDURE get_in_out_status_for_manager
(
IN ID INT,
OUT date_info日期,
出out_empname VARCHAR(35),
出out_EmpID VARCHAR( 10),
out out_CardId Varchar(10),
out out_EntryTime日期时间,
出out_ExitTime日期时间,
出out_WorkTime时间)
BEGIN
选择日期,empname,的EmpID,CardId中,EntryTime,EXITTIME,工作时间
到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_info));
END
您是否忘记在存储过程中进行操作?
CREATE DEFINER = root
@localhost
PROCEDURE get_in_out_status_for_manager
(IN ID INT,IN date_info日期,出empname VARCHAR(35), 出的EmpID VARCHAR(10), 出CardId中VARCHAR(10), 出EntryTime日期时间, 出EXITTIME日期时间, 了工作时间的时间) BEGIN 选择日期,empname,的EmpID,CardId中,EntryTime,EXITTIME,工作时间INTO DATE_INFO,EMPNAME,EMPID,CardId中,ENTRYTIME,EXITTIME,从pax_attd 工作时间加入employee_master上右(pax_attd.EmpID,3)= EMP loyee_master.employee_id where((employee_master.reporting_employee_id = id)and(date = date_info));
END
我试着按照你所说的,同样的错误 – 2014-09-29 05:43:41
date_info应该是IN,你需要在你的Java代码中设置它。因为从你选择的查询中你在where子句中使用date_info参数。 – 2014-09-29 05:56:18
你已经把它作为IN,但你要声明的Java代码? int EmpId = 401; stmt.setInt(1,EmpId); stmt.java.sql.Date.valueOf(2,“2013-09-04”); – 2014-09-29 07:26:51
我创建了,但同样的错误。 – 2014-09-29 05:42:56
out参数不会为程序带来任何价值,但它会返回一些值给java程序。这里的date_info是一个out参数,用在condition子句中。那是问题请检查一次。 – 2014-09-29 06:05:12
是的,我检查并更改为“IN date_info date”,即使这样结果也是如此。 – 2014-09-29 06:26:14