• Author / Uploaded
• zhadraoui

Citation preview

JSP, SERVLET, JSTL AND MYSQL SIMPLE CRUD APPLICATION This is a simple CRUD (Create Read Update Delete) User Management Web Application using Jsp, Servlet, JSTL and MySQL created using NetBeans IDE.

First, create a database and table for User using the following SQL scripts: 1 CREATE TABLE `users` ( 2 `uname` varchar(10) NOT NULL, 3 `password` varchar(10) NOT NULL, `email` varchar(50) default NULL, 4 `registeredon` date default NULL, 5 PRIMARY KEY (`uname`), 6 UNIQUE KEY `email` (`email`) 7 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 8 Now create a project in netbeans with the following project structure.

Create four packages in the src folder. com.bari.controller: contains the servlets(UserController.java) com.bari.dao: contains the logic for database operation(UserDao.java)

com.bari.model: contains the POJO (Plain Old Java Object).(User.java) com.bari.util : contains the class for initiating database connection(Database.java) User.java 1 package com.bari.model; 2 import java.util.Date; 3 4 public class User { String uname, password, email; 5 Date registeredon; 6 7 //put getter and setter here 8 } 9

Database.java 1 2 package com.bari.util; 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 public class Database { 6 public static Connection getConnection() { 7 try { Class.forName("com.mysql.jdbc.Driver"); 8 Connection con = DriverManager.getConnection 9 ("jdbc:mysql://localhost:3306/dbname", 10 "root","dbpass"); 11 return con; 12 } catch(Exception ex) { 13 System.out.println("Database.getConnection() Error -->" + ex.getMessa 14 return null; 15 } 16 } 17 public static void close(Connection con) { 18 19 try { con.close(); 20 } 21 catch(Exception ex) { 22 } 23 } } 24 25 26 UserDao.java 1 package com.bari.dao; 2 java.sql.*; 3 import import java.util.*;

4 import com.bari.model.User; 5 import com.bari.util.Database; 6 public class UserDao { 7 8 private Connection connection; 9 10 public UserDao() { connection = Database.getConnection(); 11 } 12 13 public void checkUser(User user) { 14 try { 15 PreparedStatement ps = connection.prepareStatement("select uname from use 16 ps.setString(1, user.getUname()); ResultSet rs = ps.executeQuery(); 17 if (rs.next()) // found 18 { 19 updateUser(user); 20 } else { addUser(user); 21 } 22 } catch (Exception ex) { 23 System.out.println("Error in check() -->" + ex.getMessage()); 24 } 25 } public void addUser(User user) { 26 try { 27 PreparedStatement preparedStatement = connection.prepareStatement("insert 28 // Parameters start with 1 29 preparedStatement.setString(1, user.getUname()); 30 preparedStatement.setString(2, user.getPassword()); preparedStatement.setString(3, user.getEmail()); 31 preparedStatement.setDate(4, new java.sql.Date(user.getRegisteredon().getT 32 preparedStatement.executeUpdate(); 33 34 } catch (SQLException e) { 35 e.printStackTrace(); } 36 } 37 38 public void deleteUser(String userId) { 39 try { 40 PreparedStatement preparedStatement = connection.prepareStatement("delete 41 // Parameters start with 1 preparedStatement.setString(1, userId); 42 preparedStatement.executeUpdate(); 43 44 } catch (SQLException e) { 45 e.printStackTrace(); 46 } } 47 48 public void updateUser(User user) { 49 try { 50

51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 } 97

PreparedStatement preparedStatement = connection.prepareStatement("update + "where uname=?"); // Parameters start with 1 System.out.println(new java.sql.Date(user.getRegisteredon().getTime())); preparedStatement.setString(1, user.getPassword()); preparedStatement.setString(2, user.getEmail()); preparedStatement.setDate(3, new java.sql.Date(user.getRegisteredon().getT preparedStatement.setString(4, user.getUname()); preparedStatement.executeUpdate();

}

} catch (SQLException e) { e.printStackTrace(); }

public List getAllUsers() { List users = new ArrayList(); try { Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery("select * from users"); while (rs.next()) { User user = new User(); user.setUname(rs.getString("uname")); user.setPassword(rs.getString("password")); user.setEmail(rs.getString("email")); user.setRegisteredon(rs.getDate("registeredon")); users.add(user); } } catch (SQLException e) { e.printStackTrace(); }

}

return users;

public User getUserById(String userId) { User user = new User(); try { PreparedStatement preparedStatement = connection.prepareStatement("select preparedStatement.setString(1, userId); ResultSet rs = preparedStatement.executeQuery(); if (rs.next()) { user.setUname(rs.getString("uname")); user.setPassword(rs.getString("password")); user.setEmail(rs.getString("email")); user.setRegisteredon(rs.getDate("registeredon")); } } catch (SQLException e) { e.printStackTrace(); } return user; }

98 99 10 0 10 1 10 2 10 3 10 4 10 5 10 6 10 7 10 8 10 9 110 111 112 113 114 UserController.java package com.bari.controller; 1 2 import java.io.IOException; 3 import java.text.ParseException; 4 import java.text.SimpleDateFormat; 5 import java.util.Date; 6 import javax.servlet.RequestDispatcher; 7 import javax.servlet.ServletException; 8 import javax.servlet.http.HttpServlet; 9 import javax.servlet.http.HttpServletRequest; 10 import javax.servlet.http.HttpServletResponse; 11 12 import com.bari.dao.UserDao; import com.bari.model.User; 13 14 public class UserController extends HttpServlet { 15 private static final long serialVersionUID = 1L; 16 private static String INSERT_OR_EDIT = "/user.jsp"; 17 private static String LIST_USER = "/listuser.jsp"; private UserDao dao; 18

19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

public UserController() { super(); dao = new UserDao(); }

protected void doGet(HttpServletRequest request, HttpServletResponse response) t String forward=""; String action = request.getParameter("action"); if (action.equalsIgnoreCase("delete")){ String userId = request.getParameter("userId"); dao.deleteUser(userId); forward = LIST_USER; request.setAttribute("users", dao.getAllUsers()); } else if (action.equalsIgnoreCase("edit")){ forward = INSERT_OR_EDIT; String userId = request.getParameter("userId"); User user = dao.getUserById(userId); request.setAttribute("user", user); } else if (action.equalsIgnoreCase("listUser")){ forward = LIST_USER; request.setAttribute("users", dao.getAllUsers()); } else { forward = INSERT_OR_EDIT; } RequestDispatcher view = request.getRequestDispatcher(forward); view.forward(request, response); }

// // // // // // //

protected void doPost(HttpServletRequest request, HttpServletResponse response) User user = new User(); user.setUname(request.getParameter("uname")); user.setPassword(request.getParameter("pass")); try { Date reg = new SimpleDateFormat("yyyy/MM/dd").parse(request.getParamete System.out.println("rrrrrrrrrrr"+ reg); user.setRegisteredon(reg); } catch (ParseException e) { e.printStackTrace(); } user.setEmail(request.getParameter("email")); String userid = request.getParameter("uname"); if(userid == null || userid.isEmpty()) { dao.addUser(user); } else { user.setUname(userid); dao.checkUser(user); } RequestDispatcher view = request.getRequestDispatcher(LIST_USER); request.setAttribute("users", dao.getAllUsers()); view.forward(request, response);

66 67 68 69 70 71 72 73 74 75 76 77 78 79

}

}

Now, it’s time to create three jsp pages. index.jsp 1

2

3

4

5

CRUD Example 6

7

8

9

10 11

user.jsp

1

2

3

4

5

6 Add new user 7

8

9

12

13 User Name :