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

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

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;
    }    
}

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);
        }
    }
}