Nguyen, Lam Duy Blogger: JNDI datasource Tomcat 6 and MSSQL 2000

To connect database with Microsoft SQL 2000 and Tomcat 6 via JNDI Datasource.

- Requirement:

Tomcat 6 (download here)
MSSQL 2000 with services pack 3 (download service pack here)
Anything java editor: MyEclipse, NetBean….

- Edit contex.xml + web.xml

context.xml in tomcat_Home/conf/context.xml and web.xml in your WEB-INF of your web application:

Edit your server contex.xml:

Find <Context> </Context> tag, in tag, add example：

<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource" 
          maxActive="100" maxIdle="30" maxWait="10000" 
          username="sa" password="sa" driverClassName="com.microsoft.jdbc.sqlserver.SQLServerDriver" 
          url="jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=pubs"  />

Edit your server web-inf/web.xml (in your WEB-INF of web application)

Find <web-app></web-app> tag, in tag, add example：

<resource-ref>
  <description>DB  Connection</description>
  <res-ref-name>jdbc/TestDB</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

- Connect example:

Here is example JSP to use JNDI context:

<%@   page   contentType="text/html;charset=UTF-8" %>   
<%@   page   import="java.sql.*" %> 
<%@   page   import="javax.sql.*" %> 
<%@   page   import="javax.naming.*" %> 
<HTML>
<HEAD>
<TITLE>JSP example</TITLE>
</HEAD>
<BODY>
  <%
    out.println("<h1>Hello，test JNDI !  </h1>");
  %>
  <%
    Context ctx = new InitialContext();  
    Context envctx =  (Context) ctx.lookup("java:comp/env"); 
    DataSource ds =  (DataSource) envctx.lookup("jdbc/TestDB");  
    Connection  conn=ds.getConnection();   
    Statement  st=conn.createStatement();
    String    sql="select   *   from    jobs";  
    ResultSet    rs=st.executeQuery(sql); 
    while(rs.next())   {
  %>   
       String  1：<%=rs.getString(1) %>  
       String 2：<%=rs.getString(2) %> 
       <br>
  <%
   }
  %>   
  <%out.print("Here is just JNDI datasource mssql2k +  tomcat example");%> 
  <%
   rs.close(); 
   st.close();  
   conn.close();   
  %> 
</BODY>
</HTML>