Jsp CRUD tutorial - JAVA DEVELOPMENT Training By Om Sir

3)form.html code :-

<!DOCTYPE html>

<html>

<body>

First name:<br>

<br>

Last name:<br>

<br>

City name:<br>

<br>

Email Id:<br>

</form>

</body>

</html>

4)process.jsp file code :-

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="ISO-8859-1"%>

<%@page import="java.sql.*,java.util.*"%>

String first_name=request.getParameter("first_name");

String last_name=request.getParameter("last_name");

String city_name=request.getParameter("city_name");

String email=request.getParameter("email");

try

{

Class.forName("com.mysql.jdbc.Driver");

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");

Statement st=conn.createStatement();

int i=st.executeUpdate("insert into users(first_name,last_name,city_name,email)values('"+first_name+"','"+last_name+"','"+city_name+"','"+email+"')");

out.println("Data is successfully inserted!");

}

catch(Exception e)

{

out.println("Unable to insert database.");

}

Dispaly data :-

display.jsp file code :-

<%@page import="java.sql.DriverManager"%>

<%@page import="java.sql.ResultSet"%>

<%@page import="java.sql.Statement"%>

<%@page import="java.sql.Connection"%>

String id = request.getParameter("userid");

String driver = "com.mysql.jdbc.Driver";

String connectionUrl = "jdbc:mysql://localhost:3306/";

String database = "test";

String userid = "root";

String password = "";

try {

Class.forName(driver);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

Connection connection = null;

Statement statement = null;

ResultSet resultSet = null;

<!DOCTYPE html>

<html>

<body>

<h1>Retrieve data from database in jsp</h1>

<tr>

<td>first name</td>

<td>Email</td>

</tr>

try{

connection = DriverManager.getConnection(connectionUrl+database, userid, password);

statement=connection.createStatement();

String sql ="select * from users";

resultSet = statement.executeQuery(sql);

while(resultSet.next()){

<tr>

</tr>

}

connection.close();

} catch (Exception e) {

e.printStackTrace();

}

</table>

</body>

</html>

Delete data:-

step 1:- displayall.jsp file code :-

<%@page import="java.sql.DriverManager"%>

<%@page import="java.sql.ResultSet"%>

<%@page import="java.sql.Statement"%>

<%@page import="java.sql.Connection"%>

String driver = "com.mysql.jdbc.Driver";

String connectionUrl = "jdbc:mysql://localhost:3306/";

String database = "test";

String userid = "root";

String password = "";

try {

Class.forName(driver);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

Connection connection = null;

Statement statement = null;

ResultSet resultSet = null;

<!DOCTYPE html>

<html>

<body>

<h1>Retrieve data from database in jsp</h1>

<tr>

<td>first name</td>

<td>Email</td>

<td>Action</td>

</tr>

try{

connection = DriverManager.getConnection(connectionUrl+database, userid, password);

statement=connection.createStatement();

String sql ="select * from users";

resultSet = statement.executeQuery(sql);

int i=0;

while(resultSet.next()){

<tr>

<td><a href="delete.jsp?id=<%=resultSet.getString("id") %>"><button type="button" class="delete">Delete</button></a></td>

</tr>

i++;

}

connection.close();

} catch (Exception e) {

e.printStackTrace();

}

</table>

</body>

</html>

step 2:- delete.jsp file code :-

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>

<%@page import="java.sql.*,java.util.*"%>

String id=request.getParameter("id");

try

{

Class.forName("com.mysql.jdbc.Driver");

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");

Statement st=conn.createStatement();

int i=st.executeUpdate("DELETE FROM users WHERE id="+id);

out.println("Data Deleted Successfully!");

}

catch(Exception e)

{

out.println("Unable to delete database.");

}

update data :-

step 1:- index.jsp file code :-

<%@ 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">

<%@ page import="java.sql.*" %>

<%@ page import="java.io.*" %>

<%@page import="java.sql.DriverManager"%>

<%@page import="java.sql.ResultSet"%>

<%@page import="java.sql.Statement"%>

<%@page import="java.sql.Connection"%>

String id = request.getParameter("id");

String driver = "com.mysql.jdbc.Driver";

String connectionUrl = "jdbc:mysql://localhost:3306/";

String database = "test";

String userid = "root";

String password = "";

try {

Class.forName(driver);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

Connection connection = null;

Statement statement = null;

ResultSet resultSet = null;

<html>

<body>

<h1>Retrieve data from database in jsp</h1>

<tr>

<td>first name</td>

<td>Email</td>

<td>update</td>

</tr>

try{

connection = DriverManager.getConnection(connectionUrl+database, userid, password);

statement=connection.createStatement();

String sql ="select * from users";

resultSet = statement.executeQuery(sql);

while(resultSet.next()){

<tr>

<td><a href="update.jsp?id=<%=resultSet.getString("id")%>">update</a></td>

</tr>

}

connection.close();

} catch (Exception e) {

e.printStackTrace();

}

</table>

</body>

</html>

step 2:- update.jsp file code:-

<%@page import="java.sql.DriverManager"%>

<%@page import="java.sql.ResultSet"%>

<%@page import="java.sql.Statement"%>

<%@page import="java.sql.Connection"%>

String id = request.getParameter("id");

String driver = "com.mysql.jdbc.Driver";

String connectionUrl = "jdbc:mysql://localhost:3306/";

String database = "test";

String userid = "root";

String password = "";

try {

Class.forName(driver);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

Connection connection = null;

Statement statement = null;

ResultSet resultSet = null;

try{

connection = DriverManager.getConnection(connectionUrl+database, userid, password);

statement=connection.createStatement();

String sql ="select * from users where id="+id;

resultSet = statement.executeQuery(sql);

while(resultSet.next()){

<!DOCTYPE html>

<html>

<body>

<h1>Update data from database in jsp</h1>

<br>

First name:<br>

<br>

Last name:<br>

<br>

City name:<br>

<br>

Email Id:<br>

</form>

}

connection.close();

} catch (Exception e) {

e.printStackTrace();

}

</body>

</html>

step 3:- update-process.jsp file code :-

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="ISO-8859-1"%>

<%@ page import="java.sql.*" %>

<%! String driverName = "com.mysql.jdbc.Driver";%>

<%!String url = "jdbc:mysql://localhost:3306/test";%>

<%!String user = "root";%>

<%!String psw = "";%>

String id = request.getParameter("id");

String first_name=request.getParameter("first_name");

String last_name=request.getParameter("last_name");

String city_name=request.getParameter("city_name");

String email=request.getParameter("email");

if(id != null)

{

Connection con = null;

PreparedStatement ps = null;

int personID = Integer.parseInt(id);

try

{

Class.forName(driverName);

con = DriverManager.getConnection(url,user,psw);

String sql="Update users set id=?,first_name=?,last_name=?,city_name=?,email=? where id="+id;

ps = con.prepareStatement(sql);

ps.setString(1,id);

ps.setString(2, first_name);

ps.setString(3, last_name);

ps.setString(4, city_name);

ps.setString(5, email);

int i = ps.executeUpdate();

if(i > 0)

{

out.print("Record Updated Successfully");

}

else

{

out.print("There is a problem in updating Record.");

}

catch(SQLException sql)

{

request.setAttribute("error", sql);

out.println(sql);

}