Web application based on Spring framework having connection to database

Objective
We will develop a Web application which includes following techniques:
- Spring framework
- Connect to DB
- MVC model
The example will be the user management with two functions: login verification, and registration of new user.

Project development
- Create a new project, named "spring-login" in Eclipse (see detail in: http://coderandcode.blogspot.com/2013/10/helloworld-web-application-based-on.html), with theses folders and files:
- in the src folder, create two packages: control and model. In package control, create three java classes of name LoginControl, UserJDBCTemplate and UserMapper. In package model, create a java class of name User.
- add a config file: Beans.xml
- in the WebContent folder, create six JSP pages: login, loginerror and loginsuccess (for the function of login verification); add, adderror, and addsuccess (for the function of user registration).
 - in the WebContent/WEB-INF/ folder, create files web.xml and spring-login-servlet.xml




- At the database level, create a database named "hotelmanagement" which contains a table users as follow:



 We now have to define all java classes, jsp pages, .properties and .xml file

User.java
package model;

public class User {
    private Integer id;
    private String username;
    private String password;
    private String fullName;
    private String idCardNumber;
    private String idCardType;
    private String address;
    private String description;
   
   
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getFullName() {
        return fullName;
    }
    public void setFullName(String fullName) {
        this.fullName = fullName;
    }
    public String getIdCardNumber() {
        return idCardNumber;
    }
    public void setIdCardNumber(String idCardNumber) {
        this.idCardNumber = idCardNumber;
    }
    public String getIdCardType() {
        return idCardType;
    }
    public void setIdCardType(String idCardType) {
        this.idCardType = idCardType;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }   
}

Beans.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"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">

   <!-- Initialization for data source -->
   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
      <property name="url" value="jdbc:mysql://localhost:3306/hotelmanagement"/>
      <property name="username" value="root"/>
      <property name="password" value="12345678"/>
   </bean>

   <!-- Definition for userJDBCTemplate bean -->
   <bean id="userJDBCTemplate"   class="control.UserJDBCTemplate">
      <property name="dataSource"  ref="dataSource" />   
   </bean>     
</beans>

UserJDBCTemplate.java
package control;
import java.util.List;
import javax.sql.DataSource;
import model.User;
import org.springframework.jdbc.core.JdbcTemplate;

public class UserJDBCTemplate {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplateObject;
      
       public void setDataSource(DataSource dataSource) {
          this.dataSource = dataSource;
          this.jdbcTemplateObject = new JdbcTemplate(dataSource);
       }

       public boolean create(User user) {
           String SQL = "select * from tblUser where username = ?";
           List<User> result = jdbcTemplateObject.query(SQL,
                                new Object[]{user.getUsername()}, new UserMapper());
           if(result.size()>0)
                  return false;
         
           SQL = "insert into tblUser (username, password, fullName, idCardNumber, idCardType, address, description) values (?,?,?,?,?,?,?)";
         
           jdbcTemplateObject.update( SQL, user.getUsername(), user.getPassword(), user.getFullName(),
                  user.getIdCardNumber(), user.getIdCardType(), user.getAddress(), user.getDescription());
           return true;
       }

       public User getUser(Integer id) {
          String SQL = "select * from tblUser where id = ?";
          User user = jdbcTemplateObject.queryForObject(SQL,
                            new Object[]{id}, new UserMapper());
          return user;
       }
      
       public boolean checkLogin(User user) {
              String SQL = "select * from tblUser where username = ? and password = ?";
              List<User> result = jdbcTemplateObject.query(SQL,
                                new Object[]{user.getUsername(),user.getPassword()}, new UserMapper());
              if(result.size()>0)
                  return true;
              return false;
           }

       public List<User> listUsers() {
          String SQL = "select * from tblUser";
          List <User> users = jdbcTemplateObject.query(SQL,
                                    new UserMapper());
          return users;
       }

       public void delete(Integer id){
          String SQL = "delete from tblUser where id = ?";
          jdbcTemplateObject.update(SQL, id);
          return;
       }

       public void updatePassword(User user){
          String SQL = "update Student set password = ? where id = ?";
          jdbcTemplateObject.update(SQL, user.getPassword(), user.getId());
          return;
       }
}

UserMapper.java
package control;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import model.User;

public class UserMapper implements RowMapper<User> {
       public User mapRow(ResultSet rs, int rowNum) throws SQLException {
              User user = new User();
              user.setId(rs.getInt("id"));
              user.setUsername(rs.getString("username"));
              user.setPassword(rs.getString("password"));
              user.setFullName(rs.getString("fullName"));
              user.setIdCardNumber(rs.getString("idCardNumber"));
              user.setIdCardType(rs.getString("idCardType"));
              user.setAddress(rs.getString("address"));
              user.setDescription(rs.getString("description"));
              return user;
           }
}

LoginControl.java
package control;
import model.User;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.ui.ModelMap;

@Controller
public class LoginControl{
    private ApplicationContext context = null;
    private UserJDBCTemplate userJDBCTemplate = null;
   
    public LoginControl(){
        context = new ClassPathXmlApplicationContext("Beans.xml");
        userJDBCTemplate = (UserJDBCTemplate)context.getBean("userJDBCTemplate");
    }
   
    @RequestMapping(value = "/login", method = RequestMethod.GET)
    public ModelAndView userLogin() {
        return new ModelAndView("login", "command", new User());
    }
      
    @RequestMapping(value = "/loginCheck", method = RequestMethod.POST)
    public String checkUser(@ModelAttribute("SpringWeb")User user, ModelMap model) {
          model.addAttribute("username", user.getUsername());                   
          if(userJDBCTemplate.checkLogin(user)){
              return "loginsuccess";
          }         
          return "loginerror";
    }
   
    @RequestMapping(value = "/add", method = RequestMethod.GET)
    public ModelAndView userAdd() {
        return new ModelAndView("add", "command", new User());
    }
   
    @RequestMapping(value = "/addUser", method = RequestMethod.POST)
    public String addUser(@ModelAttribute("SpringWeb")User user, ModelMap model) {
          model.addAttribute("username", user.getUsername());                   
          if(userJDBCTemplate.create(user)){
              return "addsuccess";
          }         
          return "adderror";
    }
}

login.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
<!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>Spring Login Example</title>
</head>
<body>
<h3>Login Form</h3>
<form:form method="POST" action="/spring-login/loginCheck">
<table>
    <tr><td>User Name:</td></tr>
    <tr><td><form:input path="username" /></td></tr>
    <tr><td>Password:</td></tr>
    <tr><td><form:password path="password" /></td></tr>
    <tr><td><input type="submit" value="Submit" /></td></tr>
</table>
</form:form>
</body>
</html>

loginsuccess.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="core" uri="http://java.sun.com/jsp/jstl/core"%>
<!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>Spring Login Example</title>
</head>

<body>
<h3>Welcome <core:out value="${username}" /></h3>
<table>
    <tr>
        <td><a href="login">Back</a></td>
    </tr>
</table>
</body>
</html>

loginerror.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<!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>Spring Login Example</title>
</head>

<body>
<h3>Login Error !!! Click below to login again</h3>
<table>
    <tr>
        <td><a href="login">Retry</a></td>
    </tr>
</table>
</body>
</html>

add.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
<!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>Spring Registration Example</title>
</head>
<body>
<h3>Registration Form</h3>
<form:form method="POST" action="/spring-login/addUser">
<table>
    <tr><td>User Name:</td><td><form:input path="username" /></td></tr>
    <tr><td>Password:</td><td><form:password path="password" /></td></tr>
    <tr><td>Full name:</td><td><form:input path="fullName" /></td></tr>
    <tr><td>ID card number:</td><td><form:input path="idCardNumber" /></td></tr>
    <tr><td>ID card type:</td><td><form:input path="idCardType" /></td></tr>
    <tr><td>Address:</td><td><form:input path="address" /></td></tr>
    <tr><td>Description:</td><td><form:input path="description" /></td></tr>
    <tr><td><input type="submit" value="Submit" /></td></tr>
</table>
</form:form>
</body>
</html>

addsuccess.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="core" uri="http://java.sun.com/jsp/jstl/core"%>
<!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>Spring Login Example</title>
</head>
<body>
<h3>Your registration is success! Click the link below to login!</h3>
<table>
    <tr>
        <td><a href="login">Login</a></td>
    </tr>
</table>
</body>
</html>

adderror.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="core" uri="http://java.sun.com/jsp/jstl/core"%>
<!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>Spring Login Example</title>
</head>
<body>
<h3>The username: <core:out value="${username}" /> is already existed !!! Click below to login again</h3>
<table>
    <tr>
        <td><a href="add">Retry</a></td>
    </tr>
</table>
</body>
</html>

Results
- login page:



- login success:



- login failed:



- add new user:



- add success:



- add failed: