Monday, July 28, 2008

Configure Tomcat 6 DataSource using Sql Server 2005

This is a step-by-step instructions on how to configure Tomcat 6 DataSource using Sql Server 2005. The installation of Tomcat and SqlServer is not covered.
1. Verify that you can login to the SQL Server using 'SQL Server Authentication'. You may wish to change the 'Server Authentication mode' to 'SQL Server and Windows Authentication mode'. You may also wish to check that the particular user's status of Login is 'Enabled'.
2. Verify that 'Local and remote connections' is enabled.
Go to Microsoft SQL Server 2005>Configuration Tools>SQL Server Surface Area Configuration>Remote Connections: Enable TCP/IP
3. Restart the database
4. Download and drop the JDBC driver to tomcat_home/lib. You may find tomcat-dbcp.jar in the directory. This will make the driver available to both the Tomcat internal classes and the web application.
5. Change the context.xml, for example:

<?xml version="1.0" encoding="UTF-8"?>
<context path="/BFS2">
<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="sa" password="passw0rd" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;databaseName=TestOne" />
</context>

6. Change the web.xml, for example:

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xsi="http://www.w3.org/2001/XMLSchema-instance" schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<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>
</web-app>

7. Write a testing JSP page like this:

<%@ 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>
<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 status";
ResultSet rs=st.executeQuery(sql);
while(rs.next()) {
%>
ID:<%=rs.getInt(1) %>
Value:<%=rs.getString(2) %>
<br>
<%
}
%>
Here is just JNDI datasource SQL Server 2005 + tomcat example
<%
rs.close();
st.close();
conn.close();
%>
</BODY>
</HTML>

That's it!

13 comments:

  1. Thank you! That was a huge help:)

    ReplyDelete
  2. Thank you sooo much! this is the only post worked for me, and very easy btw.

    ReplyDelete
  3. It worked for me too! Really thanks!!

    --sal

    ReplyDelete
  4. Hey,
    thanks so so much... I have been looking for this long time...
    thank you very much.
    jimmygarita

    ReplyDelete
  5. Thanks a lot. You save me so much time on reading Apache docs.

    ReplyDelete
  6. What about this guys ?
    http://www.eclipse.org/articles/article.php?file=Article-EclipseDbWebapps/index.html

    Anyway - this is perfect and wery much useful for me to understand how it working !!

    ReplyDelete
  7. It's good to configure the IDE to be aware of your database. However it's not absolutely necessary for most developers.

    ReplyDelete
  8. May I ask you why is important step 6 ?? I tested web application without this step and it working fine ??!
    Why it it there ?

    Thanks.

    ReplyDelete
  9. My server fails to start(on eclipse 3.4) I tried with the above code as well as my own code. Do i need to add any reference in server.xml?
    Also, can you throw some light on how to implement pooling for sql2005 datasources in tomcat6. Thanks.

    ReplyDelete
  10. Excellent! I got it to work after a day of trying other options. This was the best and easiest i found.

    ReplyDelete