How to use DB connection pool from DB ?
jaleyba Apr 11, 2006 5:38 AMHi
I'm new to JBoss and want to use a MySQL DB Connection Pool from my servlet application.
Following docs instructions I did.
I've placed mysql jar file driver in lib dir of default server.
I've copied mysql-ds.xml to default server deploy directory and edited it as follows
<?xml version="1.0" encoding="UTF-8"?> <!-- $Id: mysql-ds.xml,v 1.3.2.1 2004/12/01 11:46:00 schrouf Exp $ --> <!-- Datasource config for MySQL using 3.0.9 available from: http://www.mysql.com/downloads/api-jdbc-stable.html --> <datasources> <local-tx-datasource> <jndi-name>MySqlDS</jndi-name> <connection-url>jdbc:mysql://172.31.112.16:3306/proteo1</connection-url> <driver-class>com.mysql.jdbc.Driver</driver-class> <user-name>mapa</user-name> <password>mapa</password> <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name> <!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use --> <min-pool-size>5</min-pool-size> <!-- The maximum connections in a pool/sub-pool --> <max-pool-size>20</max-pool-size> <!-- sql to call when connection is created <new-connection-sql>some arbitrary sql</new-connection-sql> --> <!-- sql to call on an existing pooled connection when it is obtained from pool <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql> --> <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) --> <metadata> <type-mapping>mySQL</type-mapping> </metadata> </local-tx-datasource> </datasources>
In my application WEB_INF directory I did a web.xml like this:
<web-app> <display-name>Apache-Axis</display-name> <servlet> <servlet-name>MyTest</servlet-name> <display-name>MyTest Servlet</display-name> <servlet-class> MyTest </servlet-class> </servlet> <servlet-mapping> <servlet-name>MyTest</servlet-name> <url-pattern>/MyTest</url-pattern> </servlet-mapping> <resource-ref> <description>The default MySQL DS</description> <res-ref-name>jdbc/MySQLDB</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref> </web-app>
and a jboss-web.xml
<jboss-web> <!-- context-root>/axis</context-root --> <resource-ref> <res-ref-name>jdbc/MySQLDB</res-ref-name> <res-type>javax.sql.DataSource</res-type> <jndi-name>java:/MySQLDS</jndi-name> </resource-ref> </jboss-web>
Those are the needed steps to configure a DS AFAIK.
Then in my servlet I did:
public class MyTest extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException { doPost(request, response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException { try { InitialContext ctx = new InitialContext(); /* * Lookup the DataSource, which will be backed by a pool * that the application server provides. DataSource instances * are also a good candidate for caching as an instance * variable, as JNDI lookups can be expensive as well. */ DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MySQLDB"); /* * The following code is what would actually be in your * Servlet, JSP or EJB 'service' method...where you need * to work with a JDBC connection. */ Connection conn = null; Statement stmt = null; conn = ds.getConnection(); if (conn != null) System.out.println("Esta biennnn !!"); else System.out.println("Pal toor !"); } catch (Exception e) { throw new ServletException(e.getMessage()); } }
But I receive the error:
javax.servlet.ServletException: Could not dereference object MyTest.doPost(MyTest.java:53) MyTest.doGet(MyTest.java:18) javax.servlet.http.HttpServlet.service(HttpServlet.java:697) javax.servlet.http.HttpServlet.service(HttpServlet.java:810) org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
Could somebody tell what am I doing wrong ? I've RTFM several times but I can't get it working.... :(
Thanks in advance
J