课时7：CallableStatement语句

1.CallableStatement继承preparedStatement，提供了调用存储过程的能力

2.CallableStatement用法：

-调用简单的存储过程

-调用有输入参数的存储过程

-调用有输入、输出参数的存储过程

3.实例

命令行创建存储过程

create procedure all_customers() select* from CustomerTbl;

命令行调用

call all_customers();

使用CallableStatement调用

创建有输入输出参数的存储过程

create procedure insert_customer(in myname varchar(20),in myemail varchar(20)) insert into CustomerTbl(name,email)values(myname,myemail);

在MySQL中执行上面那句话是为了创建insert_customer方法，如果不这么创建的话，在.java文件中不能使用

CallableStatement cstmt = conn

                    .prepareCall("{call insert_customer(?,?)}");

create procedure getnamebyid(in cid int, out return_name varchar(20)) select name into return_name from CustomerTbl where id=cid;

package com.geek99.demo;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

public class Test5 {

    public static void main(String[] args) {

    //    test1();

    //    test2();

        test3();

    }

    static void test1() {

        Connection conn = DBUtil.open();

        try {

            CallableStatement cstmt = conn

                    .prepareCall("{call all_customers()}");

            ResultSet rs = cstmt.executeQuery();

            while (rs.next()) {

                int id = rs.getInt(1);

                String name = rs.getString(2);

                String email = rs.getString(3);

                System.out.println(id + ":" + name + ":" + email);

            }

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } finally {

            DBUtil.close(conn);

        }

    }

    static void test2() {

        Connection conn = DBUtil.open();

        try {

            CallableStatement cstmt = conn

                    .prepareCall("{call insert_customer(?,?)}");

            cstmt.setString(1, "kite123");

            cstmt.setString(2, "[e-mail]");

            cstmt.executeUpdate();

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } finally {

            DBUtil.close(conn);

        }

    }

    static void test3() {

        Connection conn = DBUtil.open();

        try {

            CallableStatement cstmt = conn

                    .prepareCall("{call getnamebyid(?,?)}");

            cstmt.setInt(1,7);

            cstmt.registerOutParameter(2, Types.CHAR);

            cstmt.execute();

            String name=cstmt.getString(2);

            cstmt.executeQuery();

            System.out.println(name);

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } finally {

            DBUtil.close(conn);

        }

    }

}