MYSQL存储过程&&JAVA读取结果集

最新推荐文章于 2021-06-20 15:12:05 发布

听说我叫小团团

最新推荐文章于 2021-06-20 15:12:05 发布

阅读量948

点赞数

分类专栏： java 文章标签：存储过程 java

本文链接：https://blog.csdn.net/granton/article/details/44872313

版权

java 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

0.场景是：使用登录ID和密码进行登录验证，返回该用户对应的一行数据

1.定义存储过程:

delimiter $$
drop procedure if exists verify $$
/* 两个输入 loginId，登录ID，psw，登录密码 */
create procedure verify (
    in loginId varchar(99),
    in psw varchar(99)
)
begin
    declare tmppsw varchar(99) default "";
    select * from baseuser where baseuser.loginId = loginId;
    if psw = tmppsw 
        then select * from baseuser where baseuser.loginId = loginId;
    end if;
end $$
delimiter ;

2.获取一个存储过程：(返回值是一个CallableStatement类型的对象)

/**
 * call a procedure
 * 
 * @param procedureName
 * @param argsNumber
 *            : number of parameters
 * @return CallableStatement
 */
public CallableStatement getProcedureStmt(String procedureName,
        int argsNumber, Connection con) {
    CallableStatement stmt = null;
    StringBuilder builder = new StringBuilder();
    builder.append("{call " + procedureName + " (");
    for (int i = 0; i < argsNumber; i++) {
        builder.append(i == argsNumber - 1 ? "?)}" : "?,");
    }
    System.out.println(builder.toString());
    try {
        stmt = con.prepareCall(builder.toString());
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return stmt;
}

3.调用这个存储过程

/**
 * login
 * 
 * @param baseUser
 * @return true if login successfully, false otherwise
 */
public boolean mLogin(BaseUser baseUser) {
    boolean isSuccess = false;
    CallableStatement stmt = getProcedureStmt("verify", 2);
    try {
        stmt.setString(1, baseUser.getLoginId());
        stmt.setString(2, baseUser.getPassword());
        boolean isSuccessful = stmt.execute();
        if (isSuccessful) {
            ResultSet resultSet = stmt.getResultSet();
            while (resultSet != null && resultSet.next()) {
                System.out.println(resultSet.getString("nickName"));
            }
        } else {
            System.out.println("no success");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return isSuccess;
}