<%@ page import="java.io.*,
                 java.lang.String"%>
<%@ page pageEncoding="UTF-8"%>
<jsp:useBean id="TBID" class="bean.TableID" scope="page"/>
<%=TBID.getID(20) + "<br>"%>

public class ConnectionBean 
{
  private static Connection con = null;
 

  public static Connection getConnection() 
  {
    try 
    {
            InitialContext context = new InitialContext();
            //Look up our data source
            DataSource ds = (DataSource) context.lookup("jdbc/connpool");
            //Allocate and use a connection from the pool
            con = ds.getConnection();
    }
    catch (Exception e) 
    {
      System.out.println("getConnection" + e);
    }
    return con;
  }
 
  public synchronized static void close() throws Exception 
  {
    if (con != null) 
    {
          con.close();
        con = null;
    }
  }
 
}
 
public class sqlMethod {
      
      private static ResultSet rs=null;
      private static Connection con=null;
      private static Statement st=null;
 
      public static ResultSet rsQuery(String sql)
        {
              try
              {
                    con=ConnectionBean.getConnection();
                    st=con.createStatement();
                    rs=st.executeQuery(sql);
                    
                    
              }
              catch(Exception e)
              {
                    System.out.println("sqlMethod rsQuery is abnormal"+e);
              }
              finally
              {
                    close();
              }
              return rs;
        }
 
       public synchronized static void close()
        {
              try
              {
                    if (con != null) {
                          ConnectionBean.close();
                    }
              } 
              catch(Exception er)
              {
                    System.out.println("con='" + con + "' close is abnormal "+er);
              }
 
        }
  
        public synchronized static void closeStmt()
        {
              try
              {
                    if (st != null) {
                          st.close();
                          st = null;
                    }
              } 
              catch(Exception er)
              {
                    System.out.println("st='" + st + "' close is abnormal "+er);
              }
 
        }
}
 
public class TableID {
public int getID(int iCandID) {
            int iID = 0
            ResultSet rs = null;
            try
            {
                  String query = "SELECT * FROM ID WHERE (FKID= " + iCandID + ")";
                  rs=sqlMethod.rsQuery(query);      
            
            if(rs.next()) {
                  iID = rs.getInt("PKID");
            }
            }
            catch(Exception E)
        {
            System.err.println(E);
        }
        finally
        {
              if(rs != null)
              {
                    try{
                      rs.close();
                rs = null;
                    } catch(Exception E) 
                {
                          E.printStackTrace();
                }
                      
              }
              sqlMethod.closeStmt();
              sqlMethod.close();
        }
 
      return iID;
    }
}