CallableStatement 的用法

这是类似函数的用法，先在sql里面输入句子，有变量输入输出，然后再利用CallableStatement来实现，在sql里面输入的内容具体如下：

create procedure all_customers() select * from lib;test1

create procedure itree(in mybook varchar(20)) insert into lib(book)values(mybook);test2;

create procedure getbook(in iid int,out bookname varchar(20)) select book into bookname from lib where id=iid;test3

testdemo5.java代码如下

package my_sql_test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class testdemo5 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub

		test3();
	}
	
	static void test3(){
		Connection conn=DBUtil.open();
		
		try {
			
			CallableStatement cstmt=conn.prepareCall("{call getbook(?,?)}");
			cstmt.setInt(1, 3);
			cstmt.registerOutParameter(2, Types.CHAR);
			cstmt.execute();
			String book=cstmt.getString(2);
			cstmt.executeUpdate();
			System.out.println(book);
			
		} 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 itree(?)}");
			cstmt.setString(1, "tom123");
			cstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.close(conn);
		}
		
	}
	
	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 book=rs.getString(2);
				System.out.println(book+":"+id);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.close(conn);
		}
		
	}

}