Knowledge Walls
J2EE Technologies Tutorial
Hyderabad, Andhra Pradesh, India
How to use JdbcTemplate in spring using indexed parameters with example
1954 Views
Hints 
JdbcTemplate with JDBC datasource reading records, insert, delete and update all curd actions in Spring using Index based parameters approach with JdbcTemplate.
Start a Web based Spring application 
  1. Select New menu -> Dynamic Web Project
  2. Enter Project Name as "JDBCTemplatesWithIndexedParameters"
  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  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>JDBCTemplatesWithIndexedParameters</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>
  <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="dbDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <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" />
    </bean>
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dbDataSource" />
    </bean>
</beans>
Book.java
package com.springexample;

public class Book {
    private int id;
    private String book_name;
    
    public Book(){}
    public Book(int id,String book_name){
        this.id = id;
        this.book_name = book_name;
    }
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getBook_name() {
        return book_name;
    }
    public void setBook_name(String book_name) {
        this.book_name = book_name;
    }    
}
CRUDWithIndexedParameterAction.java
package com.springexample;

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

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 org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.web.context.support.WebApplicationContextUtils;

@WebServlet("/crud.do")
public class CRUDWithIndexedParameterAction extends HttpServlet {
    
    private String SQL_SELECT = "SELECT id,book_name FROM books";
    private String SQL_SELECT_BY_ID = "SELECT id,book_name FROM books WHERE id = ?";
    private String SQL_INSERT = "INSERT INTO books(book_name) VALUES(?)";
    private String SQL_UPDATE = "UPDATE books SET book_name = ? WHERE id = ?";
    private String SQL_DELETE = "DELETE FROM books WHERE id = ?";
    
    private JdbcTemplate getJdbcTemplate(HttpServletRequest req){
        ApplicationContext context = WebApplicationContextUtils.getRequiredWebApplicationContext(req.getServletContext());
        return (JdbcTemplate) context.getBean("jdbcTemplate");
    }
    
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        
        String action = req.getParameter("action");
        if (action == null || action.equals("showAll")){
            List<Book> bookList = getJdbcTemplate(req).query(SQL_SELECT, new ParameterizedRowMapper<Book>() {

                @Override
                public Book mapRow(ResultSet rs, int rowNum)
                        throws SQLException {
                    Book book = new Book();
                        book.setId(rs.getInt(1));
                        book.setBook_name(rs.getString(2));
                    return book;
                }
                
            });
            
            resp.getWriter().write("All Books\n");
            for (Book book:bookList){
                resp.getWriter().write(book.getId()+":"+book.getBook_name()+"\n");
            }
        }
        if (action.equals("showById")){
            String id = req.getParameter("id");
            Book book = getJdbcTemplate(req).queryForObject(SQL_SELECT_BY_ID, new ParameterizedRowMapper<Book>() {

                @Override
                public Book mapRow(ResultSet rs, int rowNum)
                        throws SQLException {
                    Book book = new Book();
                        book.setId(rs.getInt(1));
                        book.setBook_name(rs.getString(2));
                    return book;
                }
                
            },id);
            
            resp.getWriter().write("Selected Book:\n");
            resp.getWriter().write(book.getId()+":"+book.getBook_name());
        }
        if (action.equals("insert")){
            int status = getJdbcTemplate(req).update(SQL_INSERT,req.getParameter("book_name"));
            resp.getWriter().write("insert status. "+status);
        }
        if (action.equals("update")){
            int status = getJdbcTemplate(req).update(SQL_UPDATE,req.getParameter("book_name"),req.getParameter("id"));
            resp.getWriter().write("update status. "+status);
        }
        if (action.equals("delete")){
            int status = getJdbcTemplate(req).update(SQL_DELETE,req.getParameter("id"));
            resp.getWriter().write("delete status. "+status);
        }
    }
}
Running URL's 
/crud.do?action=showAll
/crud.do?action=showById&id=2
/crud.do?action=insert&book_name=CSS
/crud.do?action=update&book_name=CSS3&id=3
/crud.do?action=delete&id=3
Download as Zip 
Link to download
JDBCTemplatesWithIndexedParameters

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