dbutils 调用存储过程 mysql_mysql创建存储过程并通过java程序调用该存储过程

create table users_ning(id primary key auto_increment,pwd int);

insert into users_ning values(id,1234);

insert into users_ning values(id,12345);

insert into users_ning values(id,12);

insert into users_ning values(id,123);

CREATE PROCEDURE login_ning(IN p_id int,IN p_pwd int,OUT flag int)

BEGIN

DECLAREv_pwd int;

select pwd INTO v_pwd from users_ning

where id = p_id;

if v_pwd = p_pwd then

set flag:=1;

else

select v_pwd;

set flag := 0;

end if;

END

package demo20130528;

import java.sql.*;

import demo20130526.DBUtils;

/**

* 测试JDBC API调用过程

* @author tarena

*

*/

public class ProcedureDemo2 {

/**

* @param args

* @throws Exception

*/

public static void main(String[] args) throws Exception {

System.out.println(login(123, 1234));

}

/**

* 调用过程,实现登录功能

* @param id 考生id

* @param pwd 考试密码

* @return if成功:1; if密码错:0; if没有用户:-1

* @throws Exception

*/

public static int login(int id, int pwd) throws Exception{

int flag = -1;

String sql = "{call login_ning(?,?,?)}";//*****

Connection conn = DBUtils.getConnMySQL();

CallableStatement stmt = null;

try{

stmt = conn.prepareCall(sql);

//传递输入参数

stmt.setInt(1, id);

stmt.setInt(2, pwd);

//注册输出参数,第三个占位符的数据类型是整型

stmt.registerOutParameter(3, Types.INTEGER);//*****

//执行过程

stmt.execute();

//获得过程执行后的输出参数

flag = stmt.getInt(3);//*****

}catch(Exception e){

e.printStackTrace();

}finally{

stmt.close();

DBUtils.dbClose();

}

return flag;

}

}

package demo20130526;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;