Spring Setting Up a JNDI Datasource

Now we are going to configure our project on a server to use the pooled datasource connection that provided by the server. In previous posts http://sourceforopensource.com/spring-querying-using-jdbc-template/ we used apache dbcp database connection pooling. To create a pooled set of connections we could used to connect to our database. When we run a java web app the java application server for example Tomcat will implement some kind of connection pooling for you and how you configure it and how you configure your application to connect to it depends on how you deploy your application and depends on what server you are using what database you are using and also what kind of api’s you are using. So, you upload your application to a free service like google app engine or AWS they will provide you information about how to configure your app to have a datasource within it.

Now we gonna see an example of setting up a connection pool with server that I am using(tomcat) and database I am using(mysql). WE are using JNDI datasource.

First we have cofigure JNDI to our tomcat server by modifying existing context.xml in tomcat server by adding the below configuration

context.xml

//for reference https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html


//we need alter the configuration based on our database.
<pre><code><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"/></code></pre>

Now we have to configure our project to connect to the above

datasource. Lets add some configuration to our project 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>
</web-app>

upto now we have done adding datasource. We need some test code to test that working actually.

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" %>

//we are using jstl here.
<%@ 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>

//with the help of jstl prefix="sql" we are accessing our database and executing the query.

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

Now with the help of foreach loop we are iterating over the result set rows

<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 *