JNDI datasource Tomcat 6 and MSSQL 2000

written by aext on April 8, 2008 in Quick Tips with one Comment

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>