JNDI를 이용한 db 프로그래밍(java) 완변 샘플

1. META-INF 아래에 context.xml 을 아래와같이 둔다( db정보)

<Context>
    <Resource name="jdbc/edmsDB" auth="Container"
              type="javax.sql.DataSource" 
     driverClassName= "oracle.jdbc.driver.OracleDriver"
     url="jdbc:oracle:thin:@(...)"
     username="uuu"
     password="111"
     maxActive="100"
     maxIdle="10000"
     maxWait="-1"
     />
</Context>

2. 바로 코딩 들어간다.



 PreparedStatement pstmt = null;

 String strQuery = "";

 ResultSet rs = null;
 Connection conn = null;
 
 int nRSTotCount = 0;
 int nRSVewCount = 0;
 List results = new ArrayList();
 try {

  Context initContext = new InitialContext();

  Context envContext = (Context) initContext
    .lookup("java:/comp/env");
  DataSource ds = (DataSource) envContext.lookup("jdbc/edmsDB");

  conn = ds.getConnection();
  
  StringBuffer sbTotCount = new StringBuffer();
  String strDate="";
  if ((!"".equals(fromdate) && !"".equals(todate)) )
  {         
   strDate = " and TRIM(regdate) between  ?  AND  ?  ";
  }
  String strRownum="";
  String strStartNum="0";
  String strEndNum="";
  strStartNum = startIdx+1+"";
  strEndNum = startIdx+pageCount+"";
  
  strRownum = " WHERE RN BETWEEN "+strStartNum+" AND "+strEndNum;
  
  
  sbTotCount.append(" select count(*) from ( ");
  sbTotCount.append(" SELECT QUERY, SUM(TOTALCOUNT) AS SUMCOUNT ");
  sbTotCount.append("  FROM SLA_POPULARQUERYLOG ");
  sbTotCount.append("  WHERE QUERY <> '_NULL_'");
  if (!"".equals(strDate)){
   sbTotCount.append(strDate);
  }
  sbTotCount.append("    GROUP BY QUERY ");
  sbTotCount.append(")");
  
  String sqlTotCount = sbTotCount.toString();
  pstmt = conn.prepareStatement(sqlTotCount);
  if (!"".equals(strDate)){
   pstmt.setString(1,fromdate);
   pstmt.setString(2,todate);
  }
  rs = pstmt.executeQuery();
  
  
  
  if( rs.next() ) {
   nRSTotCount=rs.getInt(1);
  }
  
 
  //------------------------------------------------------------------
  StringBuffer sb = new StringBuffer();
  sb.append(" SELECT RN,QUERY,SUMCOUNT FROM (");
  sb.append("      SELECT ROWNUM RN,QUERY,SUMCOUNT FROM (");
  sb.append("           SELECT QUERY, SUM(TOTALCOUNT) AS SUMCOUNT ");
  sb.append("             FROM SLA_POPULARQUERYLOG ");
  sb.append("            WHERE QUERY <> '_NULL_'");
  if (!"".equals(strDate)){
   sb.append(strDate);
  }
  // sb.append(" where rownum<10");
  //sb.append("  --  WHERE TRIM(regdate) between SUBSTR(#startdate#, 1, 8) AND SUBSTR(#enddate#, 1, 8) AND COLLNAME=#collname#  AND QUERY <> '_NULL_'");
  sb.append("             GROUP BY QUERY ");
  sb.append("             ORDER BY SUMCOUNT DESC ");
   sb.append("      ) ");
   sb.append(" ) ");
  sb.append(strRownum);
   
  
  String sql = sb.toString();
  
  pstmt = conn.prepareStatement(sql);
  if (!"".equals(strDate)){
   pstmt.setString(1,fromdate);
   pstmt.setString(2,todate);
  }
  rs = pstmt.executeQuery();

  

  while (rs.next()) {
   Map row = new HashMap();

   row.put("QUERY", rs.getString("QUERY"));
   row.put("SUMCOUNT", rs.getString("SUMCOUNT"));
   
   results.add(row);
  }

  
  nRSVewCount = results.size();

 } catch (NamingException e) {
  throw new ServletException("JNDI error", e);
 } catch (SQLException e) {
  throw new ServletException("SQL error", e);
 } finally {
  if (rs != null) {
   try {
    rs.close();
   } catch (Exception ignored) {
   }
  }
  if (pstmt != null) {
   try {
    pstmt.close();
   } catch (Exception ignored) {
   }
  }
  if (conn != null) {
   try {
    conn.close();
   } catch (Exception ignored) {
   }
  }
 }



3. 참 쉽죠잉..


샘플 제공자 : 이현민