enterprise with jdbc

Question

I have enterprise project, but all queries are implemented using oracle stored procedures, I use jbdc and Spring framework to get results like this :

 public class HoaDonDAOimpl extends JdbcDaoSupport implements HoaDonDAO {

 public List<HoaDon> getDsTatcaHoadonPhathanh(int vthang, int vnam,
   String vmaDvqltb) throws Exception {

  CallableStatement cs = getDataSource().getConnection().prepareCall(
    "{call PKG_QLNO.GetDsTatcaHoadonPhathanh(?,?,?,?)}");
  cs.setInt(1, vthang);
  cs.setInt(2, vnam);
  cs.setString(3, vmaDvqltb);
  cs.registerOutParameter(4, OracleTypes.CURSOR);
  cs.execute();
  ResultSet rs = (ResultSet) cs.getObject(4);

  List<HoaDon> list = new ArrayList<HoaDon>();

  while (rs.next()) {

   HoaDon hoadon = new HoaDon();

   hoadon.setChon(rs.getString("chon"));
   hoadon.setMa_so(rs.getString("ma_so"));
   hoadon.setMa_kh(rs.getString("ma_kh"));
   hoadon.setNam(rs.getString("nam"));
   hoadon.setThang(rs.getString("thang"));
   hoadon.setTien_PS(rs.getString("tien_PS"));
   hoadon.setThue_ps(rs.getString("thue_ps"));
   hoadon.setTien_khmai(rs.getString("tien_khmai"));
   hoadon.setTien_tbi(rs.getString("tien_tbi"));
   hoadon.setTong_ps(rs.getString("tong_ps"));
   hoadon.setTenkh(rs.getString("tenkh"));
   hoadon.setMa_dvqltb(rs.getString("ma_dvqltb"));
   hoadon.setSo_hd_in(rs.getString("so_hd_in"));
   hoadon.setNgay_in(rs.getString("ngay_in"));
   hoadon.setMa_httt(rs.getString("ma_pttt"));
   hoadon.setMa_httt(rs.getString("ma_httt"));
   hoadon.setDchi_khang(rs.getString("dchi_khang"));
   hoadon.setLoaitb(rs.getString("loaitb"));
   hoadon.setKh_hd(rs.getString("kh_hd"));
   hoadon.setLoai_hoadon(rs.getString("loai_hoadon"));
   hoadon.setLoaihd(rs.getString("loaihd"));
   hoadon.setKieu_in(rs.getString("kieu_in"));

   list.add(hoadon);
  }

  return list;
 }

but, the problem is, where I submit or refresh to get data again about 4 to 5 times, my program doesn't run.So, what should I do, plz give me some solutions, thanks you.

Answer 1

If it stays stucked, there's probably a problem in the stored procedure...You should try executing each part of the stored procedure, and see which part hangs...

Answer 2

I think you're going to need to get in there with a debugger to find out where the program is hanging. It's probably either the cs.Execute or the cs.GetObject, but you're going to have to figure out where. Once you know that you'll be in a better position to determine what exactly is going on, but from what I can see here there's no way to offer better advice.

Good luck.

Answer 3

The Connection returned by getDataSource().getConnection() is never closed. This can cause trouble if the DataSource returned by getDataSource() is pooling database connections. This is because pools will often have a maximum number of connections that may be open simultaneously. If the maximum number of connections are currently open, getDataSource() will wait until one of them is "closed", releasing it back to the pool. Because you never call close() on your Connection, it will never be released back to the pool.

You may want to try this to ensure that your connection is always closed after you are done using it:

public List<HoaDon> getDsTatcaHoadonPhathanh(int vthang, int vnam, String vmaDvqltb) throws Exception {
    Connection con = getDataSource().getConnection();
    try {
        CallableStatement cs = con.prepareCall("{call PKG_QLNO.GetDsTatcaHoadonPhathanh(?,?,?,?)}");

        cs.setInt(1, vthang);
        cs.setInt(2, vnam);
        cs.setString(3, vmaDvqltb);
        cs.registerOutParameter(4, OracleTypes.CURSOR);
        cs.execute();

        ResultSet rs = (ResultSet) cs.getObject(4);
        List<HoaDon> list = new ArrayList<HoaDon>();

        while (rs.next()) {

            HoaDon hoadon = new HoaDon();

            hoadon.setChon(rs.getString("chon"));
            hoadon.setMa_so(rs.getString("ma_so"));
            hoadon.setMa_kh(rs.getString("ma_kh"));
            hoadon.setNam(rs.getString("nam"));
            hoadon.setThang(rs.getString("thang"));
            hoadon.setTien_PS(rs.getString("tien_PS"));
            hoadon.setThue_ps(rs.getString("thue_ps"));
            hoadon.setTien_khmai(rs.getString("tien_khmai"));
            hoadon.setTien_tbi(rs.getString("tien_tbi"));
            hoadon.setTong_ps(rs.getString("tong_ps"));
            hoadon.setTenkh(rs.getString("tenkh"));
            hoadon.setMa_dvqltb(rs.getString("ma_dvqltb"));
            hoadon.setSo_hd_in(rs.getString("so_hd_in"));
            hoadon.setNgay_in(rs.getString("ngay_in"));
            hoadon.setMa_httt(rs.getString("ma_pttt"));
            hoadon.setMa_httt(rs.getString("ma_httt"));
            hoadon.setDchi_khang(rs.getString("dchi_khang"));
            hoadon.setLoaitb(rs.getString("loaitb"));
            hoadon.setKh_hd(rs.getString("kh_hd"));
            hoadon.setLoai_hoadon(rs.getString("loai_hoadon"));
            hoadon.setLoaihd(rs.getString("loaihd"));
            hoadon.setKieu_in(rs.getString("kieu_in"));

            list.add(hoadon);
        }

        return list;
    } finally {
        con.close();
    }
}

As well, some pooling Connection objects don't comply with Sun's documentation regarding close():

Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released

You may have to explicitly close cs and rs yourself if you're still suffering from strange issues:

public List<HoaDon> getDsTatcaHoadonPhathanh(int vthang, int vnam, String vmaDvqltb) throws Exception {
    List<HoaDon> list = new ArrayList<HoaDon>();

    Connection con = getDataSource().getConnection();
    try {
        CallableStatement cs = con.prepareCall("{call PKG_QLNO.GetDsTatcaHoadonPhathanh(?,?,?,?)}");

        try {
            cs.setInt(1, vthang);
            cs.setInt(2, vnam);
            cs.setString(3, vmaDvqltb);
            cs.registerOutParameter(4, OracleTypes.CURSOR);
            cs.execute();

            ResultSet rs = (ResultSet) cs.getObject(4);
            try {
                while (rs.next()) {

                    HoaDon hoadon = new HoaDon();

                    hoadon.setChon(rs.getString("chon"));
                    hoadon.setMa_so(rs.getString("ma_so"));
                    hoadon.setMa_kh(rs.getString("ma_kh"));
                    hoadon.setNam(rs.getString("nam"));
                    hoadon.setThang(rs.getString("thang"));
                    hoadon.setTien_PS(rs.getString("tien_PS"));
                    hoadon.setThue_ps(rs.getString("thue_ps"));
                    hoadon.setTien_khmai(rs.getString("tien_khmai"));
                    hoadon.setTien_tbi(rs.getString("tien_tbi"));
                    hoadon.setTong_ps(rs.getString("tong_ps"));
                    hoadon.setTenkh(rs.getString("tenkh"));
                    hoadon.setMa_dvqltb(rs.getString("ma_dvqltb"));
                    hoadon.setSo_hd_in(rs.getString("so_hd_in"));
                    hoadon.setNgay_in(rs.getString("ngay_in"));
                    hoadon.setMa_httt(rs.getString("ma_pttt"));
                    hoadon.setMa_httt(rs.getString("ma_httt"));
                    hoadon.setDchi_khang(rs.getString("dchi_khang"));
                    hoadon.setLoaitb(rs.getString("loaitb"));
                    hoadon.setKh_hd(rs.getString("kh_hd"));
                    hoadon.setLoai_hoadon(rs.getString("loai_hoadon"));
                    hoadon.setLoaihd(rs.getString("loaihd"));
                    hoadon.setKieu_in(rs.getString("kieu_in"));

                    list.add(hoadon);
                }
            } finally {
                rs.close();
            }
        } finally {
            cs.close();
        }
    } finally {
        con.close();
    }

    return list;
}