In this section, Let us see how to configure JDBC in Spring application.
For any Application to interact with Database, it needs a JDBC Connectivity. Using the JDBC Connection, it can perform data retrieval, insertion and update operations.
Let us see how to achieve this in Spring.
1. Create a Servlet Application Context as follows
In the above configuration, Database Name is ORCL, User Name and Password are test/test. Oracle Connection port is 1521.
2. Create an Interface for JDBC operations as follows,
JdbcIfc Interface
import java.util.ArrayList;
/**
* JDBC Operations
*/
public interface JdbcIfc
{
ArrayList<String> getDbRecords();
}
3. Create a JDBC Implementation class as follows,
Jdbc_Implementation Class
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.sql.DataSource;
/**
* JDBC Implementation class for performing DB Operations
*/
public class Jdbc_Implementation implements JdbcIfc
{
private DataSource dataSource;
public void setDataSource(DataSource dataSource)
{
this.dataSource = dataSource;
}
public ArrayList<String> getDbRecords()
{
String sql = "SELECT NAME FROM TEST_TABLE";
ArrayList<String> list = new ArrayList<String>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try
{
conn = dataSource.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next())
{
list.add(rs.getString("NAME"));
}
return list;
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
if (rs != null)
{
try
{
rs.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if (ps != null)
{
try
{
ps.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if (conn != null)
{
try
{
conn.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
}
}
4. In the Controller class, inject the above JDBC Object so that the Controller can make use of the JDBC template to perform database operations.
TestController Class
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.Controller;
/**
* Controller Class
*/
public class TestController implements Controller
{
private JdbcIfc jdbcTemplate = null;
public ModelAndView handleRequest(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException
{
ArrayList<String> list = getJdbcTemplate().getDbRecords();
HashMap map = new HashMap();
map.put("db_records", list);
return new ModelAndView("output", "responseData", map);
}
public JdbcIfc getJdbcTemplate()
{
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcIfc jdbcTemplate)
{
this.jdbcTemplate = jdbcTemplate;
}
}
5. Create a JSP page named output.jsp inside /WEB-INF/jsp folder.
/WEB-INF/jsp/output.jsp
<%@ page pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<html>
<head>
<title>JDBC Demo</title>
</head>
<body>
<h2>Database Records</h2>
<table>
<c:forEach items="${responseData.db_records}" var="db_item">
<tr>
<td>
<c:out value="${db_item}" />
</td>
</tr>
</c:forEach>
</table>
</body>
</html>
6. Open the url http://localhost:8080/springapp/test.htm
Now your Controller gets executed and fetches the data from the database and sent to the JSP for display.
JSP Output