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:
CREATE TABLE `users` ( `uname` varchar(10) NOT NULL, `password` varchar(10) NOT NULL, `email` varchar(50) default NULL, `registeredon` date default NULL, PRIMARY KEY (`uname`), UNIQUE KEY `email` (`email`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Now create a project in netbeans with the following project structure.
User.java
package com.bari.model;import java.util.Date;public class User { String uname, password, email; Date registeredon; //put getter and setter here }
Database.java
package com.bari.util;
import java.sql.Connection;
import java.sql.DriverManager;
public class Database {
public static Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection
"root","dbpass");
return con;
}
catch(Exception ex) {
System.out.println("Database.getConnection() Error -->" + ex.getMessage());
return null;
}
}
public static void close(Connection con) {
try {
con.close();
}
catch(Exception ex) {
}
}
}
UserDao.java
package com.bari.dao;
import java.sql.*;
import java.util.*;
import com.bari.model.User;
import com.bari.util.Database;
public class UserDao {
private Connection connection;
public UserDao() {
connection = Database.getConnection();
}
public void checkUser(User user) {
try {
PreparedStatement ps = connection.prepareStatement("select uname from users where uname = ?");
ps.setString(1, user.getUname());
ResultSet rs = ps.executeQuery();
if (rs.next()) // found
{
updateUser(user);
} else {
addUser(user);
}
} catch (Exception ex) {
System.out.println("Error in check() -->" + ex.getMessage());
}
}
public void addUser(User user) {
try {
PreparedStatement preparedStatement = connection.prepareStatement("insert into users(uname, password, email, registeredon) values (?, ?, ?, ? )");
// Parameters start with 1
preparedStatement.setString(1, user.getUname());
preparedStatement.setString(2, user.getPassword());
preparedStatement.setString(3, user.getEmail());
preparedStatement.setDate(4, new java.sql.Date(user.getRegisteredon().getTime()));
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void deleteUser(String userId) {
try {
PreparedStatement preparedStatement = connection.prepareStatement("delete from users where uname=?");
// Parameters start with 1
preparedStatement.setString(1, userId);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void updateUser(User user) {
try {
PreparedStatement preparedStatement = connection.prepareStatement("update users set password=?, email=?, registeredon=?"
+ "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().getTime()));
preparedStatement.setString(4, user.getUname());
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
public List<User> getAllUsers() {
List<User> users = new ArrayList<User>();
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 * from users where uname=?");
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;
}
}
