Làm việc với Stored Procedure

Trong bài viết này tôi sẽ giới thiệu với các bạn cách chúng ta thao tác với các StoredProcedure trong java với đối tượng CallableStatement.

CallableStatement là đối tượng kế thừa từ đối tượng PreparedStatement, như vậy có các tính chất của 1 PreparedStatement (tức có các tính chất của Statement(public interface CallableStatement extends PreparedStatement). Chúng ta tạo 1 CallableStatement bằng cú pháp sau:

CallableStatement prepareCall(String sql) throws SQLException hoặc

CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException.

Sau đó chúng ta triệu gọi các stored procedure bằng các cú pháp sau:

Đối với procedure không có tham số: {call procedure_name}

Đối với procedure có tham số: {call procedure_name(?,?,…)}

Đối với procedure có giá trị trả về (function): {?=call procedure_name(?,?,…)}

Ví dụ: CallableStatement cstmt = con.prepareCall(“{call procedureName(?, ?)}”);

trong đó các dấu ? thay thế cho các tham số kiểu IN, OUT, INOUT.

Đối với tham số kiểu IN, ta sử dụng phương thức setXXX của đối tượng PrepareStatement để chuyển tham số vào. ví dụ: cstmt.setString(1, “c%”);

Đối với tham số kiểu OUT, ta phải đăng ký đầu ra trước khi thực thi, ví dụ:
stmt.registerOutParameter(1, java.sql.Types.INTEGER);

Đối với tham số dạng INOUT, ta kết hợp gồm IN và OUT.

================================================

Dể dễ hiểu, tôi sẽ viết 1 ví dụ thực thi các kiểu StoredProcedure cho các bạn.

Trước hết ta tạo 1 database có tên QLSV trong đó có 1 bảng có tên Lophoc . SQL script cho database như sau:

CREATE TABLE [dbo].[Lophoc]( [classID] [nvarchar](15) COLLATE Vietnamese_CI_AS NOT NULL, [className] [nvarchar](100) COLLATE Vietnamese_CI_AS NOT NULL, [studentsNum] [int] NOT NULL, [deptID] [nvarchar](15) COLLATE Vietnamese_CI_AS NULL, CONSTRAINT [PK_tblClass] PRIMARY KEY CLUSTERED ( [classID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

Sau đó chúng ta tạo các procedure để thử.  Script sau đây sẽ tạo các stored procedure cho chúng ta:

–Tạo proc cho tham số IN create proc exInparams @clsID varchar(15) AS select * from tblClass where tblClass.classID like @clsID GO –Thực thi exec exInparams ‘c%’ –******************************************** -Tạo proc cho tham số output create proc exOutParams @cls varchar(15),@num int OUTPUT as select @num=count(classID) from tblClass where classID like @cls go –Thực thi declare @s int exec exOutParams ‘c%’,@s OUTPUT print @s –******************************************** -Tạo proc cho tham số INOUT create proc exINOUTParams @num int OUT as select @num=count(classID) from tblClass where studentsNum>@num go select * from tblClass –Thực thi declare @s int set @s=100 exec exINOUTParams @s OUTPUT print @s –******************************************** –Hàm Function –Hàm lấy số SV của 1 lớp khi biết mã số lớp create FUNCTION dbo.getNoStudents (@classID varchar(15)) RETURNS int AS BEGIN Declare @c  int set @c=(select studentsNum from tblClass where classID = @classID) return @c END go –Thực thi declare @x int set @x=dbo.getNoStudents(‘cdth4c’) print(@x) –********************************************

Bạn tạo DB rồi chạy các hàm để thử trên hệ quản trị CSDL, chẳng hạn MS SQLserver.

bạn tạo lớp sau để thử các loại:

package vovanhai.wordpress.com; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import session6_JDBC.ConnectDBFactory; //thử tham số đầu vào public class TestCallable { private Connection con; void TestINPro() throws Exception{ con=ConnectDBFactory.CreateMsSqlServerConnection(“QLSV”); String sql=“{call exInparams(?)}”; CallableStatement cstmt=con.prepareCall(sql); cstmt.setString(1, “c%”); ResultSet rs=cstmt.executeQuery(); while(rs.next()) { System.out.println(rs.getString(“className”)); } con.close(); } //thử tham số đầu ra void TestOUTPro() throws Exception{ con= ConnectDBFactory.CreateMsSqlServerConnection(“QLSV”); String sql=“{call exOutParams(?,?)}”; CallableStatement cstmt=con.prepareCall(sql); cstmt.setString(1, “d%”); //đăng ký tham số đầu ra cstmt.registerOutParameter(2, java.sql.Types.INTEGER); cstmt.execute(); //Lấy giá trị trả về int s=cstmt.getInt(2); System.out.println(s); con.close(); } //Thử tham số vừa vào vừa ra void TestINOUTPro() throws Exception{ con=ConnectDBFactory.CreateMsSqlServerConnection(“QLSV”); String sql=“{call exINOUTParams(?)}”; CallableStatement cstmt=con.prepareCall(sql); cstmt.setInt(1, 200); //Đăng ký tham số đầu ra cstmt.registerOutParameter(1, java.sql.Types.INTEGER); cstmt.execute(); //lấy giá trị trả về int s=cstmt.getInt(1); System.out.println(s); con.close(); } //Thử việc triệu gọi 1 function có giá trị trả về public void TestFunctionCall() throws Exception { ConnectDBFactory.CreateMsSqlServerConnection(“QLSV”); CallableStatement cst=con.prepareCall(“{?=call getNoStudents(?)}”); cst.registerOutParameter(1,java.sql.Types.INTEGER); cst.setString(2,“CD”); cst.execute(); int ret=cst.getInt(1); System.out.println (“Số sinh viên: ”+ret); } }

OK. Chúc các bạn thành công! Nếu hữu ích, thanks 1 tiếng nhé!