Dears,

This blog is moved to a new blog at:

The new update will be found at that new blog, please follow it!
This blog is no more updated.
Thanks for your visit!

Monday, October 7, 2013

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: