How to get return value in function sql by Java

08:05



Function in sql server

create function checkFunc(
 @MaSP varchar(10)
)
returns int
AS
BEGIN
 declare @kq int
 IF EXISTS(SELECT * FROM SANPHAM WHERE MaSP=@MaSP) set @kq=2
 ELSE 
  BEGIN
   set @kq = 0
  END
 return @kq
END
//execute
select dbo.checkFunc('sp02') 

How to get data by resultset in java

public int checkSanPham() {
  sql = "select dbo.checkFunc(?)";
  connection = connectDB.getConnect();
  int kq = 0;
  try {
   ps = connection.prepareStatement(sql);
   ps.setString(1, "sp02");
   rs = ps.executeQuery();
   if(rs.next()){
    System.out.println(rs.getInt(1));
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    rs.close();
    ps.close();
    connection.close();
   } catch (Exception e2) {
   }
  }
  
  return kq;
 }


SQL.

How to use CallableStatement 

store-proceduce in sql

 
create proc sp_insert 
 @MaSP varchar(10),
 @TenSP varchar(50),
 @MaLoaiSP varchar(10),
 @GiaBan int,
 @SoLuong int
AS
BEGIN 
 --kiem tra Ma loai san pham co ton tai trong bang LOAISP
 IF EXISTS(SELECT TOP 1 MaLoaiSP FROM LOAISP WHERE MaLoaiSP = @MaLoaiSP)
  BEGIN
   -- Kiem tra neu k ton tai ma san pham thi cho insert
   IF NOT EXISTS(SELECT TOP 1 MaSP FROM SANPHAM WHERE MaSP = @MaSP)
    BEGIN
     INSERT INTO SANPHAM (MaSP,TenSP,MaLoaiSP,GiaBan,SoLuong) VALUES(@MaSP, @TenSP, @MaLoaiSP, @GiaBan, @SoLuong)
    END
   ELSE
    BEGIN
     print ('Trung khoa chinh')
    END
  END
 ELSE
  BEGIN
   print ('MaLoaiSP khong ton tai')
  END
END

--execute
exec sp_insert 'sp09','kaka','type001','10','10'

Insert method in Java
public void themSanPham(String maSP, String tenSP, String maLoaiSP,
   String giaBan, String soLuong) {
  sql = "{call sp_insert(?,?,?,?,?)}";
  connection = connectDB.getConnect();
  CallableStatement cs = null;
  try {
   cs = connection.prepareCall(sql);
   cs.setString(1, maSP);
   cs.setString(2, tenSP);
   cs.setString(3, maLoaiSP);
   cs.setInt(4, Integer.valueOf(giaBan));
   cs.setInt(5, Integer.valueOf(soLuong));
   int i = cs.executeUpdate();
   System.out.println(i);
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    cs.close();
    connection.close();
   } catch (Exception e2) {
   }
  }
  
 }

Khi update, or insert data by store-procedure thì khi update, insert thành công thì nó sẽ trả về giá trị 1, fail thì trả về -1. ta lấy giá trị này để xác định là thành công hay chưa.
                        int i = cs.executeUpdate();

System.out.println(i);

You Might Also Like

0 nhận xét

Popular Posts

Like us on Facebook

Flickr Images

Subscribe