Kaarel Purde
Kaarel Purde

Reputation: 1265

Setting up connection pool with Tomcat 7 (MySQL)

I am using Tomcat 7.0.52 and mysql-connector-java-5.1.36.jar. I copied the mysql jar into tomcat/lib folder.

DriverManager works, I can get connections and execute queries:

Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(url, usr, pw);
Statement stmnt = con.createStatement();
ResultSet rs = stmnt.executeQuery("SELECT * FROM county");

But I can't get this to work with a connection pool.

MyWebApp.war/WEB-INF/classes/Servu.class (only class in this app, a servlet):

public class Servu extends HttpServlet {

    private static DataSource ds;

    @Override
    public void init() throws ServletException {
        super.init();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Context ctx = new InitialContext();
            ds = (DataSource) ctx.lookup("jdbc/maakler_new");
        } catch (ClassNotFoundException | NamingException ex) {
            Logger.getLogger(Servu.class.getName()).log(Level.SEVERE, "thrown from init()", ex);
        }
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.getWriter().write("Table:\n");
        try (PrintWriter out = resp.getWriter();
             Connection c = ds.getConnection();
             Statement stmnt = c.createStatement();
             ResultSet rs = stmnt.executeQuery("SELECT * FROM county");
        ) {
            while (rs.next()) {
                out.print(rs.getString(2) + "\n");
            }
            out.flush();
        } catch (SQLException ex) {
            Logger.getLogger(Servu.class.getName()).log(Level.SEVERE, "thrown from doGet()", ex);
        }
    }
}

MyWebApp.war\META-INF\context.xml:

<?xml version="1.0" encoding="UTF-8"?>
<Context>

      <Resource name="jdbc/maakler_new" auth="Container" type="javax.sql.DataSource"
               maxActive="40" maxIdle="30" maxWait="15000"
               username="user" password="pw" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://mydblocation.ee:3306/maakler_new"/>

</Context>

MyWebApp.war\WEB-INF\web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.1" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd">

    <servlet>
            <servlet-name>Servu</servlet-name>
            <servlet-class>Servu</servlet-class>
    </servlet>
    <servlet-mapping>
            <servlet-name>Servu</servlet-name>
            <url-pattern>/Servu</url-pattern>
    </servlet-mapping> 

    <resource-ref>
        <description>DB Connection</description>
        <res-ref-name>jdbc/maakler_new</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

</web-app>

If I deploy the app I get this error (from tomcat/logs/tomcat7-stderr.2015-10-31.log):

SEVERE: thrown from init()
javax.naming.NameNotFoundException: Name [jdbc/maakler_new] is not bound in this Context. Unable to find [jdbc].

Why I get this exception and how to fix it?

EDIT1: added ResourceLink to context, but still nothing:

<Context>
            ...

       <ResourceLink global="jdbc/maakler_new" name="jdbc/maakler_new" type="javax.sql.DataSource" />

</Context>

EDIT2: this is my tomcat/conf/server.xml file now:

<?xml version='1.0' encoding='utf-8'?>

  <GlobalNamingResources>

    <Resource name="UserDatabase" 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/maakler_new" auth="Container" type="javax.sql.DataSource"
               maxActive="40" maxIdle="30" maxWait="15000"
               username="usr" password="pw" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://mydblocation.ee:3306/maakler_new"/>

  </GlobalNamingResources>

...

Upvotes: 0

Views: 1123

Answers (2)

Kaarel Purde
Kaarel Purde

Reputation: 1265

The problem was in Java code. I was looking for the DataSource from the wrong context (the root context). This is wrong:

Context ctx = new InitialContext();
ds = (DataSource) ctx.lookup("jdbc/maakler_new");

Correct way would be this:

Context ctx = new InitialContext();
ds = (DataSource) ctx.lookup("java:comp/env/jdbc/maakler_new");

Upvotes: 1

Palak Shah
Palak Shah

Reputation: 71

You are missing an entry in server.xml. this should help

http://examples.javacodegeeks.com/enterprise-java/tomcat/tomcat-connection-pool-configuration-example/

Upvotes: 0

Related Questions