Simple CRUD Example using JQuery and JAVA/JSP/SERVLET

Step -1- Create a table names USER we are using Oracle as a RDBMS.

  CREATE TABLE "SIDDHU"."USER" 
   ( "USERID" VARCHAR2(30 BYTE), 
"FIRSTNAME" VARCHAR2(20 BYTE), 
  "LASTNAME" VARCHAR2(20 BYTE), 
  "EMAIL" VARCHAR2(20 BYTE))

Step 2-  Download latest JQuery package avaiable from JQuery site. During this example latest version was 2.0.
https://blog.jquery.com/2013/04/18/jquery-2-0-released/

Step 3- Create index.jsp which will give GUI to our application. Make sure to include following js file

Step 4:- Create Model Class User

package com.siddhu.model;
public class User {

private int userid;
private String firstName;
private String lastName; 
private String email;

public int getUserid() {
return userid;
}
public void setUserid(int userid) {
this.userid = userid;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}


public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User [userid=" + userid + ", firstName=" + firstName
+ ", lastName=" + lastName + ", email="
+ email + "]";
}


}

Step 5:- Create database connection class DBUtility

package com.siddhu.dbconnection;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtility {
private static Connection connection = null;

public static Connection getConnection() {
if (connection != null)
return connection;
else {
try {
Properties prop = new Properties();
InputStream inputStream = DBUtility.class.getClassLoader().getResourceAsStream("/config.properties");
//InputStream inputStream = DBUtility.class.getClassLoader().getResourceAsStream("config.properties");
prop.load(inputStream);
String driver = prop.getProperty("driver");
String url = prop.getProperty("url");
String user = prop.getProperty("user");
String password = prop.getProperty("password");
Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);

/*// Store the database URL in a string
String serverName = "127.0.0.1";
String portNumber = "1521";
String sid = "XE";
String dbUrl = "jdbc:oracle:thin:@" + serverName + ":" + portNumber
+ ":" + sid;

Class.forName("oracle.jdbc.driver.OracleDriver");
// set the url, username and password for the database
connection = DriverManager.getConnection(dbUrl, "", ""); */

} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return connection;
}

}

}

Step 6:- Create Dao file

package com.siddhu.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

import com.siddhu.dbconnection.DBUtility;
import com.siddhu.model.User;
public class SiddhuCrudDao {

private Connection connection;

public SiddhuCrudDao() {
connection = DBUtility.getConnection();
}

public void addUser(User user) {
try {

PreparedStatement preparedStatement = connection
.prepareStatement("insert into USER(userid,firstname,lastname,email) values (?,?, ?, ? )");
preparedStatement.setInt(1, user.getUserid());
preparedStatement.setString(2, user.getFirstName());
preparedStatement.setString(3, user.getLastName());   
preparedStatement.setString(4, user.getEmail());
preparedStatement.executeUpdate();

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

public void deleteUser(int userId) {
try {
PreparedStatement preparedStatement = connection
.prepareStatement("delete from USER where userid=?");
preparedStatement.setInt(1, userId);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}

public void updateUser(User user) throws ParseException {
try {
PreparedStatement preparedStatement = connection
.prepareStatement("update USER set lastname=?,email=?" +
"where userid=?");
preparedStatement.setString(1, user.getLastName());
preparedStatement.setString(2, user.getEmail());   
preparedStatement.setInt(3, user.getUserid());
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 USER");
while (rs.next()) {
User user = new User();
user.setUserid(rs.getInt("userid"));
user.setFirstName(rs.getString("firstname"));
user.setLastName(rs.getString("lastname"));    
user.setEmail(rs.getString("email"));
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}

return users;
}

public User getUserById(int userId) {
User user = new User();
try {
PreparedStatement preparedStatement = connection.
prepareStatement("select * from USER where userid=?");
preparedStatement.setInt(1, userId);
ResultSet rs = preparedStatement.executeQuery();

if (rs.next()) {
user.setUserid(rs.getInt("userid"));
user.setFirstName(rs.getString("firstname"));
user.setLastName(rs.getString("lastname"));

user.setEmail(rs.getString("email"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}

}

Step 7 - Create Controller

package com.siddhu.controller;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.reflect.TypeToken;
import com.siddhu.dao.SiddhuCrudDao;
import com.siddhu.model.User;


public class SiddhuCRUDController extends HttpServlet {
private static final long serialVersionUID = 1L;
private SiddhuCrudDao dao;

public SiddhuCRUDController() {
dao=new SiddhuCrudDao();
}


protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

}


protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
if(request.getParameter("action")!=null){
List lstUser=new ArrayList();
String action=(String)request.getParameter("action");
Gson gson = new Gson();
response.setContentType("application/json");

if(action.equals("list")){
try{      
lstUser=dao.getAllUsers();   
JsonElement element = gson.toJsonTree(lstUser, new TypeToken>() {}.getType());
JsonArray jsonArray = element.getAsJsonArray();
String listData=jsonArray.toString();    
listData="{\"Result\":\"OK\",\"Records\":"+listData+"}";   
response.getWriter().print(listData);
}catch(Exception ex){
String error="{\"Result\":\"ERROR\",\"Message\":"+ex.getMessage()+"}";
response.getWriter().print(error);
ex.printStackTrace();
}    
}
else if(action.equals("create") || action.equals("update")){
User user=new User();
if(request.getParameter("userid")!=null){       
int userid=Integer.parseInt(request.getParameter("userid"));
user.setUserid(userid);
}
if(request.getParameter("firstName")!=null){
String firstname=(String)request.getParameter("firstName");
user.setFirstName(firstname);
}
if(request.getParameter("lastName")!=null){
String lastname=(String)request.getParameter("lastName");
user.setLastName(lastname);
}
if(request.getParameter("email")!=null){
String email=(String)request.getParameter("email");
user.setEmail(email);
}
try{           
if(action.equals("create")){
dao.addUser(user);     
lstUser.add(user);
String json=gson.toJson(user);     
String listData="{\"Result\":\"OK\",\"Record\":"+json+"}";           
response.getWriter().print(listData);
}else if(action.equals("update")){
dao.updateUser(user);
String listData="{\"Result\":\"OK\"}";         
response.getWriter().print(listData);
}
}catch(Exception ex){
String error="{\"Result\":\"ERROR\",\"Message\":"+ex.getStackTrace().toString()+"}";
response.getWriter().print(error);
}
}else if(action.equals("delete")){//Delete record
try{
if(request.getParameter("userid")!=null){
String userid=(String)request.getParameter("userid");
dao.deleteUser(Integer.parseInt(userid));
String listData="{\"Result\":\"OK\"}";        
response.getWriter().print(listData);
}
}catch(Exception ex){
String error="{\"Result\":\"ERROR\",\"Message\":"+ex.getStackTrace().toString()+"}";
response.getWriter().print(error);
}    
}
}
}
}


Step 8:- Create config.properties containing follwoing information.

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:XE
user=siddhu
password=siddhu

Step 7:- Create war file and upload on Tomcat and hit the url you will able to see the below screen. Perform Crud Operation on the table.