[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

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
//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>

Open in new window

0
viola123
Asked:
viola123
  • 5
  • 4
2 Solutions
 
objectsCommented:
what exactly isn't working?

0
 
viola123Author Commented:
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
0
 
viola123Author Commented:
if you remove the part i add into the context.xml file, it will work.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
objectsCommented:
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

0
 
viola123Author Commented:
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>

Open in new window

0
 
objectsCommented:
you don't , the database is specified in the datasource (in the url)

0
 
viola123Author Commented:
hi, i found how to do it:

<parameter><name>url</name><value>jdbc:oracle:thin:@XXXXX;databaseName=xxx;</value></parameter>
it works.

thanks a lot
0
 
objectsCommented:
Didn't my direction help get you to the solution?
0
 
objectsCommented:
split between me and viola123

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now