viola123
asked on
How to confing multiple DB connections FOR TOMCAT 5.0?
hi guys,
i try to use multiple db connections. but it seems not working. how to do that in Context.xml file? and how to map the connection to Tomcat 5?
i am new to this stuff, pls help me!!!
thanks a lot
viola
i try to use multiple db connections. but it seems not working. how to do that in Context.xml file? and how to map the connection to Tomcat 5?
i am new to this stuff, pls help me!!!
thanks a lot
viola
//this is the way we connect to DB. I think it is JNDI
public WealthFrontService() {
try {
Context initContext = new InitialContext();
ds = (DataSource)initContext.lookup("java:/comp/env/jdbc/OmlWebDS");
}
catch (Throwable e) {
// Make sure you log the exception, as it might be swallowed
logger.error("Cannot create service", e);
throw new ExceptionInInitializerError(e);
}
}
public List getClientsByPartialName(String partialName) throws Exception {
PreparedStatement statement = null;
ResultSet resultSet = null;
Connection conn = ds.getConnection(); //this is the connection
List clients = new ArrayList();
try {
statement = conn.prepareStatement(sqlstring );
statement.setString(1, "%" + partialName + "%");
statement.setString(2, "%" + partialName + "%");
resultSet = statement.executeQuery();
while (resultSet.next()) {
Client client = new Client();
............
clients.add(client);
}
}
finally {
if (resultSet != null) resultSet.close();
if (statement != null) statement.close();
conn.close();
}
return clients;
}
***************************************
in our context.xml
<Context>
//can i create multiple <Realm> under this <context>? i tried, but it does not work. could you guys give me an example?
<Realm className="com.gbst.security.auth.GbstOmlWebTomcatLoginModule"
dataSourceName="jdbc/OmlWebDS"
digest="SHA-1"
localDataSource="true"
userTable="webuser"
userRoleTable="allroles"
userNameCol="username"
roleNameCol="userrole"
userCredCol="password"/>
<Resource name="mail/Session" auth="Container" type="javax.mail.Session"/>
<ResourceParams name="mail/Session">
<parameter><name>mail.smtp.host</name><value>mail</value></parameter>
</ResourceParams>
<Resource name="jdbc/OmlWebDS" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/OmlWebDS">
<parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
<parameter><name>maxActive</name><value>10</value></parameter>
<parameter><name>maxIdle</name><value>10</value></parameter>
<parameter><name>maxWait</name><value>20000</value></parameter>
<parameter><name>username</name><value>ourusername</value></parameter>
<parameter><name>password</name><value>pwd</value></parameter>
<parameter><name>driverClassName</name><value>oracle.jdbc.driver.OracleDriver</value></parameter>
<parameter><name>url</name><value>jdbc:oracle:thin:@........</value></parameter>
<parameter><name>removeAbandoned</name><value>true</value></parameter>
<parameter><name>removeAbandonedTimeout</name><value>60</value></parameter>
<parameter><name>logAbandoned</name><value>true</value></parameter>
<parameter><name>testOnBorrow</name><value>true</value></parameter>
</ResourceParams>
</Context>
**********************************
this is the DB connection i want to do in the context.xml file:
i have put the mapping in our web.xml like the following:
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/CMTWebDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
in context.xml:
<Context>
<Realm className="com.gbst.security.auth.GbstOmlWebTomcatLoginModule"
dataSourceName="jdbc/OmlWebDS"
digest="SHA-1"
localDataSource="true"
userTable="webuser"
userRoleTable="allroles"
userNameCol="username"
roleNameCol="userrole"
userCredCol="password"/>
<Resource name="mail/Session" auth="Container" type="javax.mail.Session"/>
<ResourceParams name="mail/Session">
<parameter><name>mail.smtp.host</name><value>mail</value></parameter>
</ResourceParams>
<Resource name="jdbc/OmlWebDS" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/OmlWebDS">
<parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
<parameter><name>maxActive</name><value>10</value></parameter>
<parameter><name>maxIdle</name><value>10</value></parameter>
<parameter><name>maxWait</name><value>20000</value></parameter>
<parameter><name>username</name><value>XXX</value></parameter>
<parameter><name>password</name><value>XXXX</value></parameter>
<parameter><name>driverClassName</name><value>oracle.jdbc.driver.OracleDriver</value></parameter>
<parameter><name>url</name><value>jdbc:oracle:thin:@XXXXX</value></parameter>
<parameter><name>removeAbandoned</name><value>true</value></parameter>
<parameter><name>removeAbandonedTimeout</name><value>60</value></parameter>
<parameter><name>logAbandoned</name><value>true</value></parameter>
<parameter><name>testOnBorrow</name><value>true</value></parameter>
</ResourceParams>
// new DB CONNECTIONS:
<Realm className="com.gbst.security.auth.GbstOmlWebTomcatLoginModule"
dataSourceName="jdbc/CMTWebDS"
digest="SHA-1"
localDataSource="true"
userTable="webuser"
userRoleTable="allroles"
userNameCol="username"
roleNameCol="userrole"
userCredCol="password"/>
<Resource name="jdbc/CMTWebDS" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/CMTWebDS">
<parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
<parameter><name>maxActive</name><value>10</value></parameter>
<parameter><name>maxIdle</name><value>10</value></parameter>
<parameter><name>maxWait</name><value>20000</value></parameter>
<parameter><name>username</name><value>XXXXX</value></parameter>
<parameter><name>password</name><value>XXXXX</value></parameter>
<parameter><name>driverClassName</name><value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value></parameter>
<parameter><name>url</name><value>jdbc:sqlserver://XXXX</value></parameter>
<parameter><name>removeAbandoned</name><value>true</value></parameter>
<parameter><name>removeAbandonedTimeout</name><value>60</value></parameter>
<parameter><name>logAbandoned</name><value>true</value></parameter>
<parameter><name>testOnBorrow</name><value>true</value></parameter>
</ResourceParams>
</Context>
what exactly isn't working?
ASKER
hi,
when go to the first page to login, it does not do anything when you click the submit button. i mean it stays on the same page after you click the button.
thanks
when go to the first page to login, it does not do anything when you click the submit button. i mean it stays on the same page after you click the button.
thanks
ASKER
if you remove the part i add into the context.xml file, it will work.
do you need to add access to an additional db?
check the logs, you mind find some error messages in there that will help point at the cause
check the logs, you mind find some error messages in there that will help point at the cause
ASKER
hi, i found the problem: i have to add sqljdbc.jar to Tomcat lib folder, otherwise, the connection won't work. but one more question: how to specify the database name in <Realm>?
SOLUTION:
in web.xml file, add the following:
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/CMTWebDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
**************************
in context.xml:
<Context>
<Realm className="com.gbst.security.auth.GbstOmlWebTomcatLoginModule"
dataSourceName="jdbc/OmlWebDS"
digest="SHA-1"
localDataSource="true"
userTable="webuser"
userRoleTable="allroles"
userNameCol="username"
roleNameCol="userrole"
userCredCol="password"/>
<Resource name="mail/Session" auth="Container" type="javax.mail.Session"/>
<ResourceParams name="mail/Session">
<parameter><name>mail.smtp.host</name><value>mail</value></parameter>
</ResourceParams>
<Resource name="jdbc/OmlWebDS" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/OmlWebDS">
<parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
<parameter><name>maxActive</name><value>10</value></parameter>
<parameter><name>maxIdle</name><value>10</value></parameter>
<parameter><name>maxWait</name><value>20000</value></parameter>
<parameter><name>username</name><value>XXX</value></parameter>
<parameter><name>password</name><value>XXXX</value></parameter>
<parameter><name>driverClassName</name><value>oracle.jdbc.driver.OracleDriver</value></parameter>
<parameter><name>url</name><value>jdbc:oracle:thin:@XXXXX</value></parameter>
<parameter><name>removeAbandoned</name><value>true</value></parameter>
<parameter><name>removeAbandonedTimeout</name><value>60</value></parameter>
<parameter><name>logAbandoned</name><value>true</value></parameter>
<parameter><name>testOnBorrow</name><value>true</value></parameter>
</ResourceParams>
// new DB CONNECTIONS:
//one more question: how to set the database name?????
<Resource name="jdbc/CMTWebDS" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/CMTWebDS">
<parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
<parameter><name>maxActive</name><value>10</value></parameter>
<parameter><name>maxIdle</name><value>10</value></parameter>
<parameter><name>maxWait</name><value>20000</value></parameter>
<parameter><name>username</name><value>XXXXX</value></parameter>
<parameter><name>password</name><value>XXXXX</value></parameter>
<parameter><name>driverClassName</name><value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value></parameter>
<parameter><name>url</name><value>jdbc:sqlserver://XXXX</value></parameter>
<parameter><name>removeAbandoned</name><value>true</value></parameter>
<parameter><name>removeAbandonedTimeout</name><value>60</value></parameter>
<parameter><name>logAbandoned</name><value>true</value></parameter>
<parameter><name>testOnBorrow</name><value>true</value></parameter>
</ResourceParams>
</Context>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Didn't my direction help get you to the solution?
split between me and viola123