Spring Configuring a Datasource Bean

In this post we gonna see how we connect our JNDI DataSource to our DAO objects.

Lets go through the application.

context.xml


<?xml version="1.0" encoding="UTF-8"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
--><!-- The contents of this file will be loaded for each web application --><Context>

<!-- Default set of monitored resources. If one of these changes, the -->
<!-- web application will be reloaded. -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>

<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->

<!-- Uncomment this to enable Comet connection tacking (provides events
on session expiration as well as webapp lifecycle) -->
<!--
<Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />
-->

<Resource name="jdbc/spring" auth="Container" type="javax.sql.DataSource"
maxTotal="100" maxIdle="30" maxWaitMillis="10000"
username="root" password="admin" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/sfos"/>
</Context>

OffersController.java


package com.sourceforopensource.spring.web.controllers;

import java.util.Map;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class OffersController {

@RequestMapping("/")
public String showHome(Model model) {

model.addAttribute("name", "Tiffany");

return "home";
}
}

offers-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:mvc="http://www.springframework.org/schema/mvc"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd">
<context:component-scan base-package="com.sourceforopensource.spring.web.controllers">
</context:component-scan>
<mvc:annotation-driven></mvc:annotation-driven>

<bean id="jspViewResolver"
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/WEB-INF/jsps/"></property>
<property name="suffix" value=".jsp"></property>
</bean>
</beans>

Offer.java


package com.sourceforopensource.spring.web.dao;

public class Offer {
private int id;
private String name;
private String email;
private String text;

public Offer() {

}

public Offer(String name, String email, String text) {
this.name = name;
this.email = email;
this.text = text;
}
public Offer(int id, String name, String email, String text) {
super();
this.id = id;
this.name = name;
this.email = email;
this.text = text;
}

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public String getText() {
return text;
}

public void setText(String text) {
this.text = text;
}

@Override
public String toString() {
return "Offer [id=" + id + ", name=" + name + ", email=" + email
+ ", text=" + text + "]";
}

}

OffersDAO.java


package com.sourceforopensource.spring.web.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

@Component("offersDao")
public class OffersDAO {

private NamedParameterJdbcTemplate jdbc;

public OffersDAO() {
System.out.println("Successfully loaded offers DAO");
}

@Autowired
public void setDataSource(DataSource jdbc) {
this.jdbc = new NamedParameterJdbcTemplate(jdbc);
}

public List<Offer> getOffers() {

return jdbc.query("select * from offers", new RowMapper<Offer>() {

public Offer mapRow(ResultSet rs, int rowNum) throws SQLException {
Offer offer = new Offer();

offer.setId(rs.getInt("id"));
offer.setName(rs.getString("name"));
offer.setText(rs.getString("text"));
offer.setEmail(rs.getString("email"));

return offer;
}

});
}

public boolean update(Offer offer) {
BeanPropertySqlParameterSource params = new BeanPropertySqlParameterSource(offer);

return jdbc.update("update offers set name=:name, text=:text, email=:email where id=:id", params) == 1;
}

public boolean create(Offer offer) {

BeanPropertySqlParameterSource params = new BeanPropertySqlParameterSource(offer);

return jdbc.update("insert into offers (name, text, email) values (:name, :text, :email)", params) == 1;
}

@Transactional
public int[] create(List<Offer> offers) {

SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(offers.toArray());

return jdbc.batchUpdate("insert into offers (id, name, text, email) values (:id, :name, :text, :email)", params);
}

public boolean delete(int id) {
MapSqlParameterSource params = new MapSqlParameterSource("id", id);

return jdbc.update("delete from offers where id=:id", params) == 1;
}

public Offer getOffer(int id) {

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("id", id);

return jdbc.queryForObject("select * from offers where id=:id", params,
new RowMapper<Offer>() {

public Offer mapRow(ResultSet rs, int rowNum)
throws SQLException {
Offer offer = new Offer();

offer.setId(rs.getInt("id"));
offer.setName(rs.getString("name"));
offer.setText(rs.getString("text"));
offer.setEmail(rs.getString("email"));

return offer;
}

});
}

}

dao-context.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"
xmlns:jee="http://www.springframework.org/schema/jee"
xsi:schemaLocation="http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd">
<context:annotation-config></context:annotation-config>
<context:component-scan
base-package="com.sourceforopensource.spring.web.dao">
</context:component-scan>

//here we are configuring the dataSource bean to our spring mvc project
<jee:jndi-lookup jndi-name="jdbc/spring" id="dataSource"
expected-type="javax.sql.DataSource">
</jee:jndi-lookup>
</beans>

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" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
id="WebApp_ID" version="2.5">
<display-name>spring-tutorial-50</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<servlet>
<description></description>
<display-name>offers</display-name>
<servlet-name>offers</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>offers</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>

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

<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>

<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>
classpath:com/sourceforopensource/spring/web/config/dao-context.xml
</param-value>
</context-param>

</web-app>

home.jsp


<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>

<c:out value="${name}"></c:out> <p/>

<sql:query var="rs" dataSource="jdbc/spring">
select id, name, email, text from offers
</sql:query>

<c:forEach var="row" items="${rs.rows}">
ID: ${row.id}<br/>
Name: ${row.name}<br/>
</c:forEach>

</body>
</html>

Balakrishna Pendyala

Author: Balakrishna Pendyala

Balakrishna is a Software Engineer working in Media Streaming Domain, and also worked on Banking and Health Care domains. His areas of interest include Object Oriented Design, SOLID Design principles, Restful Web Services and Open Source softwares including Spring, Spring Boot, and Hibernate.

If you found an error, highlight it and press Shift + Enter or click here to inform us.

Leave a Reply

Your email address will not be published. Required fields are marked *