Knowledge Walls
J2EE Technologies Tutorial
Hyderabad, Andhra Pradesh, India
How to use connection pool datasource in spring framework using Apache's BasicDataSource with example
1771 Views
Hints 
Below example descripted how to create connection pool database with Spring. Using BasicDataSource Utility of Apache libraries creating beans with basic database configurations.
Step.1 Start a Web based Spring application 
  1. Select New menu -> Dynamic Web Project
  2. Enter Project Name as "SpringWithJNDIDataSourceExample"
  3. Click Next, Selecting Target runtime as Apache Tomcat 7.0
  4. Click Next, Check Generate web.xml deployment descriptor then click on "Finish"
  5. Copy and paste Spring's 21 Framework Jars, mysql-connector-java-5.0.8-bin.jar, org.apache.commons.dbcp.jar, org.apache.commons.pool.jar and commons-logging-1.1.jar into /WEB-INF/lib
Project Explorer Preview 
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>SpringWithPoolConnectionofApache</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
  </welcome-file-list>
 
  <listener>
    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
  </listener>
  <context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>/WEB-INF/beans-servlet.xml</param-value>
  </context-param>
</web-app>
beans-servlet.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/test" />
        <property name="username" value="root" />
        <property name="password" value="mysql" />
        <property name="initialSize" value="5" />
        <property name="maxActive" value="10" />
    </bean>

</beans>
ShowBookDetails.java
package com.springexample;

import java.io.IOException;
import java.util.List;
import java.util.Map;
 
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
 
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.context.support.WebApplicationContextUtils;
 
@WebServlet("/ShowBookDetails")
public class ShowBookDetails extends HttpServlet {
     
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        ApplicationContext context = WebApplicationContextUtils.getRequiredWebApplicationContext(req.getServletContext());
        DataSource dataSource = (DataSource) context.getBean("myDataSource");
        JdbcTemplate template = new JdbcTemplate(dataSource);
        List<Map<String,Object>> books = template.queryForList("SELECT * FROM books");
         
        for (Map<String,Object> book:books){
            resp.getWriter().write(book.get("id")+":"+book.get("book_name")+"<br />");
        }
    }
}
Output 
Download as Zip 
Link to download
SpringWithPoolConnectionofApache

Hints.
Click on File menu. then click "Download"
Best Lessons of "Spring 3.0 Examples"
Top lessons which are viewed more times.
  Copyright © 2014 Knowledge walls, All rights reserved
KnowledgeWalls
keep your tutorials and learnings with KnowledgeWalls. Don't lose your learnings hereafter. Save and revise it whenever required.
Click here for more details