Excellent point Mark.
David, you have to decide how your database connections are managed:
a) Tomcat-managed datasource (your current setup)
- datasource is defined in Tomcat server.xml/context.xml and gets
registered in JNDI (e.g. java:comp/env/jdbc/smswebdb)
- datasource is referenced in hibernate.cfg.xml (using JNDI lookup)
- JDBC drivers need to be on the classpath for the server, e.g.
CATALINA_BASE/lib or CATALINA_HOME/lib
- you can write a sample application that can test the database connections
(before you deploy your SystemsMadeSimple.war)
- alternatively, you can use JMX to test the datasource (use
jvisualvm/jconsole)
b) Hibernate-managed datasource
- datasource is defined in hibernate.cfg.xml
- JDBC drivers are typically included with the application, e.g.
SystemsMadeSimple.war/WEB-INF/lib/*.jar
Here's a sample application to test your current setup ("quick and dirty
way")
1. Create new directory, TestDatabase
2. Place test.jsp in TestDatabase directory.
3. Add the following code to TestDatabase/test.jsp
<html>
<body>
<h1>TestDatabase</h1>
<pre>
<%
javax.naming.InitialContext naming = null;
javax.sql.Connection connection = null;
try {
naming = new javax.naming.InitialContext();
datasource = (DataSource)
naming.lookup("java:comp/env/jdbc/smswebdb");
connection = datasource.getConnection();
connection.close();
out.println("Connection has successfully obtained from datasource.
connection=" + connection);
} catch (javax.naming.NamingException ne) {
ne.printStackTrace();
out.println("Datasource is not bound to JNDI: " + ne);
} catch (javax.sql.SQLException sqle) {
sqle.printStackTrace();
out.println("Problem with a SQL connection: " + sqle);
} catch (Exception e) {
e.printStackTrace();
out.println("Something else went wrong: " + e);
}
%>
</pre>
</body>
</html>
4. Copy "TestDatabase" directory to Tomcat deploy directory,
CATALINA_HOME/webapps
5. Observe the console, see if the application has deployed correctly.
6. Test the application: http://localhost:8080/TestDatabase/test.jsp
7. Observe the output.
(NOTE: This is a quick and very-dirty way to test the Tomcat-managed
datasource. You should really never have a need to put Java code inside
your JSPs.)
Alternative is calling JMX bean(s) directly, e.g.
Catalina:type=DataSource,context=/,host=localhost,class=javax.sql.DataSource,name="jdbc/
smswebdb" or
Catalina:type=Resource,resourcetype=Context,context=/,host=localhost,class=javax.sql.DataSource,name="jdbc/smswebdb"
As typing this, Konstantin's reply just came through:
Definitely, your context.xml resource element is case sensitive, so you
should correct: driverClassName, maxActive, maxIdle and maxWait:
<Resource name="jdbc/smswebdb"
auth="Container"
*driverClassName*="com.microsoft.sqlserver.jdbc.SQLServerDriver"
*maxActive*="100"
*maxIdle*="30"
*maxWait*="10000"
username="SMSWEB_user"
password="PASSWORD_GOES_HERE"
type="javax.sql.DataSource"
url="jdbc:sqlserver://10.2.152.11:1433;databaseName=
SMSwebDB;SelectMethod=Cursor;">
</Resource>
Hope that helps.
Let us know how that worked out.
Cheers!
Neven
