(How to setup tomcat in windows to use a data source to connect to MSSQL database)
Requirements:
(This guide assumes the following)
Tomcat 7.0 installed and working with defaults settings
MSSQL setup and working with an account that can access the database
Icon
For these examples:
"test" is used for resource name and "testDB" is used for the connection name: This can be changed but make sure to change all of them.
- Step 1: Log into the server running Tomcat
- Step 2: Download the following file JDBC Drive for SQL
- Step 3: Place the file sqljdbc4.jar in the following folder (%Tomcat Root%\lib)
- Step 4: Edit the server.xml file (%Tomcat Root%\conf)
- The following code should be added in the <GlobalNamingResources> section
<Resource name="test" auth="Container"
type="org.apache.catalina.UserDatabase"
description="User database that can be updated and saved"
factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
pathname="conf/tomcat-users.xml" />
<Resource name="jdbc/testDB" auth="Container" type="javax.sql.DataSource" />
<ResourceParams name="jdbc/test">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>5</value> <!-- consult Tomcat docs for this value's meaning -->
</parameter>
<parameter>
<name>maxIdle</name>
<value>5</value> <!-- consult Tomcat docs for this value's meaning -->
</parameter>
<parameter>
<name>maxWait</name>
<value>10000</value> <!-- consult Tomcat docs for this value's meaning -->
</parameter>
<parameter>
<name>username</name>
<value>DB User Name</value> <!-- Change "DB User Name" to user name of account used to access SQL -->
</parameter>
<parameter>
<name>password</name>
<value>DB Password</value> <!-- Change "DB Password" to password of account used to access SQL -->
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:sqlserver://IPAddress:1433/DBName</value> <!—Change "IPAddress" to the IP address of the DB server or the DB cluster IP address, Change "DBName" to the database name you want to connect to, 1433 is the default port change if using something else -->
</parameter>
</ResourceParams>
- Step 5: Update the context.xml file (%Tomcat Root%\conf)
This code need to be in the <Context> section
<Resource name="jdbc/testDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="DBUser_Name" password="DB_Password"
<!-- Change "DBUser_Name" to user name of account used to access SQL, Change "DB_Password" to password of account used to access SQL -->
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://IPAddress:1433;databaseName=DBName"/>
<!-- Change "IPAddress" to the IP address of the DB server or the DB cluster IP address, Change "DBName" to the database name you want to connect to, 1433 is the default port change if using something else -->
- Step 6: The following .jsp page can be used to test the connection, Name the file dbtest.jsp
Viewing the file in a browser should show a list of numbers, these are numbers of the rows in what database you are connecting to.
<html>
<head><title>Enter to database</title></head>
<body>
<table>
<%@ page import="java.util.*" %>
<%@ page import="javax.sql.*;" %>
<%
java.sql.Connection c1;
java.sql.Statement s1;
java.sql.ResultSet rs1;
java.sql.PreparedStatement pst1;
DataSource testDB;
c1=null;
s1=null;
pst1=null;
rs1=null;
javax.naming.Context initCtx = new javax.naming.InitialContext();
javax.naming.Context envCtx = (javax.naming.Context) initCtx.lookup("java:comp/env");
testDB = (DataSource) envCtx.lookup("jdbc/testDB");
try{
if(testDB == null) {
javax.naming.Context initCtx1 = new javax.naming.InitialContext();
javax.naming.Context envCtx1 = (javax.naming.Context) initCtx1.lookup("java:comp/env");
testDB = (DataSource) envCtx1.lookup("jdbc/testDB");
}
}
catch(Exception e){
System.out.println("inside the context exception");
e.printStackTrace();
}
c1 = testDB.getConnection();
String sq1= "select * from master.dbo.sysdatabases"; <!-- This is the query that will run on the database -->
pst1 = c1.prepareStatement(sq1);
rs1 = pst1.executeQuery();
while( rs1.next() ){
%>
<tr>
<td><%= rs1.getString("dbid") %></td> <!--This is the string the will be displayed on the web page -->
</tr>
<%
}
if(pst1!=null) pst1.close();
if(rs1!=null) rs1.close();
if(c1!=null) c1.close();
%>
</body>
</html>
To test: On the tomcat server open IE and go to http://localhost/<site folder>/dbtest.jsp
For based on these examples ("jdbc/testDB") is what clients would need use for DataSource in there code to connect to the configured data base.
