How to use DB connection pool from DB ?
Hi
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
