There are many web scripting programming language in this world one of its based of Java programming language it is called Java Server Pages or JSP. It is entirely based on Java technology to be specific Servlets which interacts together with the web server. In Java the most common web server is Apache Tomcat which is also an open source web server. One of the benefits of learning how to program in JSP is that almost all Java Enterprise application works with JSP and it is easy to learn and implement with database application such as MySQL.
In this article I will not only show you how to create a basic CRUD application in Java Server Pages but also how it is being done through the use of my program codes. Basically I am using NetBeans 8.1.0 as my IDE or Integrated Development Environment in writing this database application and also I download mysql java connector which I include a link in my article. My program that I wrote is a typical database routine Create, Retrieve, Update and Delete or CRUD using JSP and MySQL.
The code is very short in terms of number of lines of instruction and the same way I make sure it is very easy to understand and use in your programming projects that utilize JSP and MySQL as your tool. The application will accept the name of the person, city address and telephone number it is just like an address book or mailing list application.
It has been along while since I was able to achieve this program I have some difficulty also in learning Java and JSP along the way but through dedication and perseverance I was able to get it. That's why I am sharing this to my fellow software developers.
I hope you will find my work useful and beneficial. If you have some questions about programming, about my work please send me an email at jakerpomperada@gmail.com and jakerpomperada@yahoo.com. People here in the Philippines can contact me at my mobile number 09173084360.
Thank you very much and Happy Programming.
Sample Program Output
list.jsp
<%-- list.java
Written By: Mr. Jake R. Pomperada, MAED-IT
Date : July 17, 2015, Friday
Tools: JSP and MySQL
mysql-connecter-java-5.1.13-bin.jar
netbeans ide 8.0.2
--%>
<%@ page language="java" import="java.sql.*"%>
<head><title>Users Details System</title>
</head>
<style>
h2 {
text-align:center;
font-family: arial;
color: red;
};
td {
text-align:center;
font-family: arial;
font-size: 16;
};
</style>
<%
out.println("<style> p {font-family: arial;"
+ " color: red; font-size: 16; }; "
+ "</style>");
out.println("<style> a,b {font-family: arial;"
+ " color: blue; font-size: 16; "
+ "}; </style>");
out.println("<style> a.space {font-family: arial;"
+ " color: blue; font-size: 18; "
+ "margin:0 0 0 208px;}; </style>");
%>
<body>
<div align="center" width="200%">
<br>
<h2>USERS DETAILS SYSTEM</h2>
<br>
<div align="left" width="200%">
<% out.println("<a class='space' href='insert.jsp'> ADD RECORD </a>"); %>
</div>
<br>
<table border="1" borderColor="black" cellPadding="0" cellSpacing="0" width="920" height="80">
<tbody>
<td bgColor="cyan" width="150" align="center" height="19"><font color="red"><b>
Student No.</b></font></td>
<td bgColor="cyan" width="290" height="19"><font color="red"><b>NAME</b></font></td>
<td bgColor="cyan" width="290" height="19"><font color="red"><b>CITY</b></font></td>
<td bgColor="cyan" width="230" height="19"><font color="red"><b>PHONE</b></font></td>
<td bgColor="cyan" width="290" height="19"><font color="red"><b>ACTIONS </b></font></td>
<td bgColor="cyan" width="290" height="19"><font color="red"><b>TAKEN </b></font></td>
<%
String DRIVER = "com.mysql.jdbc.Driver";
Class.forName(DRIVER).newInstance();
Connection con=null;
ResultSet rst=null;
Statement stmt=null;
try{
String url="jdbc:mysql://localhost:3306/users?user=root&password=";
int i=1;
con=DriverManager.getConnection(url);
stmt=con.createStatement();
rst=stmt.executeQuery("SELECT * FROM student_info ORDER BY name ASC ");
while(rst.next()){
if (i==(i/2)*2){
%>
<tr>
<td bgColor="lightgreen" vAlign="top" width="80" height="19"><%=i%></td>
<td bgColor="lightgreen" vAlign="top" width="110" height="19"><%=rst.getString(2)%></td>
<td bgColor="lightgreen" vAlign="top" width="224" height="19"><%=rst.getString(3)%></td>
<td bgColor="lightgreen" vAlign="top" width="230" height="19"><%=rst.getString(4)%></td>
<td bgColor="lightgreen" vAlign="top" width="220" height="19" >
<a href="edit.jsp?id=<%=rst.getInt("id")%>"> Edit Record </a></td>
<td bgColor="lightgreen" vAlign="top" width="230" height="19">
<a href="delete.jsp?id=<%=rst.getInt("id")%>"> Delete Record </a></td>
</tr>
<%
}else{
%>
<tr>
<td bgColor="lightgreen" vAlign="top" width="80" align="center" height="19"><%=i%></td>
<td bgColor="lightgreen" vAlign="top" width="107" height="19"><%=rst.getString(2)%></td>
<td bgColor="lightgreen" vAlign="top" width="224" height="19"><%=rst.getString(3)%></td>
<td bgColor="lightgreen" vAlign="top" width="230" height="19"><%=rst.getString(4)%></td>
<td bgColor="lightgreen" vAlign="top" width="220" height="19" >
<a href="edit.jsp?id=<%=rst.getInt("id")%>"> Edit Record </a></td>
<td bgColor="lightgreen" vAlign="top" width="230" height="19">
<a href="delete.jsp?id=<%=rst.getInt("id")%>"> Delete Record </a></td>
</tr>
<% }
i++;
}
rst.close();
stmt.close();
con.close();
}catch(Exception e){
System.out.println(e.getMessage());
}
%>
</tbody>
</table>
</center>
</div>
</body>
insert.jsp
<%-- insert.java
Written By: Mr. Jake R. Pomperada, MAED-IT
Date : July 17, 2015, Friday
Tools: JSP and MySQL
mysql-connecter-java-5.1.13-bin.jar
netbeans ide 8.0.2
--%>
<%@ page language="java" import="java.sql.*,java.util.*,java.text.*" %>
<html>
<head>
<title>Add Record </title>
</head>
<body>
<style>
table, td, th {
border: 1px solid green;
font-family: arial;
color: blue;
}
table {
background-color: lightgreen;
}
</style>
<%
out.println("<style> p {font-family: arial;"
+ " color: red; font-size: 16; }; "
+ "</style>");
out.println("<style> a,b {font-family: arial;"
+ " color: blue; font-size: 16; }; "
+ "</style>");
%>
<table border="1" width="50%">
<tr>
<td width="100%">
<form method="POST" action="save.jsp">
<h2 align="center">ADD RECORD</h2>
<table border="1" width="100%">
<tr>
<td width="50%"><b>Name:</b></td>
<td width="50%"><input type="text" name="name" size="50"/> </td>
</tr>
<tr>
<td width="50%"><b>City:</b></td>
<td width="50%"><input type="text" name="city" size="50"></td>
</tr>
<tr>
<td width="50%"><b>Telephone Number:</b></td>
<td width="50%"><input type="text" name="telephone" size="15"></td>
</tr>
</table>
<p><input type="submit" value="Submit" name="submit">
<input type="reset" value="Reset" name="reset"></p>
</form>
</td>
</tr>
</table>
<% out.println("<br>");
out.println("<a href='list.jsp'> RETURN TO MAIN PAGE </a>");
%>
</body>
</html>
save.jsp
<%-- save.java
Written By: Mr. Jake R. Pomperada, MAED-IT
Date : July 17, 2015, Friday
Tools: JSP and MySQL
mysql-connecter-java-5.1.13-bin.jar
netbeans ide 8.0.2
--%>
<%@ page language="java" import="java.sql.*,java.util.*,java.text.*" %>
<%
out.println("<style> p {font-family: arial;"
+ " color: red; font-size: 16; }; "
+ "</style>");
out.println("<style> a,b {font-family: arial;"
+ " color: blue; font-size: 16; }; "
+ "</style>");
%>
<%
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";;
String db = "users";
String driver = "com.mysql.jdbc.Driver";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db,"root","");
try{
Statement st = con.createStatement();
String name=request.getParameter("name");
String city=request.getParameter("city");
String telephone=request.getParameter("telephone");
int val = st.executeUpdate("INSERT student_info "
+ "VALUES(id,'"+name+"','"+city+"','"+telephone +"')");
con.close();
out.println("<p> The record of " +"<b>"+ name +"</b>" + " is successfully saved. </p>");
out.println("<br>");
out.println("<a href='list.jsp'> RETURN TO MAIN PAGE </a>");
}
catch (SQLException ex){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
%>
edit.jsp
<%-- edit.java
Written By: Mr. Jake R. Pomperada, MAED-IT
Date : July 17, 2015, Friday
Tools: JSP and MySQL
mysql-connecter-java-5.1.13-bin.jar
netbeans ide 8.0.2
--%>
<%@ page language="java" import="java.sql.*,java.util.*,java.text.*" %>
<html>
<head>
<title>Update Record</title>
</head>
<style>
table, td, th {
border: 1px solid green;
font-family: arial;
color: blue;
}
table {
background-color: lightgreen;
}
</style>
<body>
<%
out.println("<style> p {font-family: arial;"
+ " color: red; font-size: 16; }; "
+ "</style>");
out.println("<style> a,b {font-family: arial;"
+ " color: blue; font-size: 16; }; "
+ "</style>");
%>
<%
String strId =request.getParameter("id");
int id = Integer.parseInt(strId);
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";;
String db = "users";
String driver = "com.mysql.jdbc.Driver";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db,"root","");
try{
Statement st = con.createStatement();
String query = "SELECT name,city,phone FROM student_info WHERE id="+id;
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
%>
<table border="1" width="50%">
<tr>
<td width="100%">
<form method="POST" action="update.jsp">
<input type="hidden" name="id" value="<%=request.getParameter("id")%>">
<h2 align="center">UPDATE RECORD</h2>
<table border="2" width="100%" bgColor="lightgreen">
<tr>
<td width="50%" bgColor="lightgreen"><b>Name:</b></td>
<td width="50%" bgColor="lightgreen"><input type="text" name="name"
value="<%=rs.getString("name")%>" size="50"/> </td>
</tr>
<tr>
<td width="50%" bgColor="lightgreen"><b>City:</b></td>
<td width="50%" bgColor="lightgreen"><input type="text" name="city" value="<%=rs.getString("city")%>" size="50"></td>
</tr>
<tr>
<td width="50%" bgColor="lightgreen"><b>Telephone:</b></td>
<td width="50%" bgColor="lightgreen"><input type="text" name="phone" value="<%=rs.getString("phone")%>" size="15"></td>
</tr>
</table>
<p><input type="submit" value="Update" name="submit">
<input type="reset" value="Reset" name="reset"></p>
</form>
</td>
</tr>
</table>
<%}
rs.close();
con.close();
}
catch (SQLException ex){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
%>
<% out.println("<br>");
out.println("<a href='list.jsp'> RETURN TO MAIN PAGE </a>");
%>
</body>
</html>
update.jsp
<%-- update.java
Written By: Mr. Jake R. Pomperada, MAED-IT
Date : July 17, 2015
Tools: JSP and MySQL
mysql-connecter-java-5.1.13-bin.jar
netbeans ide 8.0.2
--%>
<%@ page language="java" import="java.sql.*,java.util.*,java.text.*" %>
<%
out.println("<style> p {font-family: arial;"
+ " color: red; font-size: 16; }; "
+ "</style>");
out.println("<style> a,b {font-family: arial;"
+ " color: blue; font-size: 16; }; "
+ "</style>");
%>
<%
String strId =request.getParameter("id");
int id = Integer.parseInt(strId);
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";;
String db = "users";
String driver = "com.mysql.jdbc.Driver";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db,"root","");
try{
Statement st = con.createStatement();
String name=request.getParameter("name");
String city=request.getParameter("city");
String phone=request.getParameter("phone");
int in = st.executeUpdate("UPDATE student_info SET name='"+name+"'"
+ ",city='"+city+"',phone='"+phone+"' "
+ "WHERE id='"+id+"'");
con.close();
out.println("<p> The record of " +"<b>"+ name +"</b>" + " is successfully updated. </p>");
out.println("<br>");
out.println("<a href='list.jsp'> RETURN TO MAIN PAGE </a>");
}
catch (SQLException ex){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
%>
delete.jsp
<%-- delete.java
Written By: Mr. Jake R. Pomperada, MAED-IT
Date : July 17, 2015, Friday
Tools: JSP and MySQL
mysql-connecter-java-5.1.13-bin.jar
netbeans ide 8.0.2
--%>
<%@ page language="java" import="java.sql.*,java.util.*,java.text.*" %>
<%
out.println("<style> p {font-family: arial;"
+ " color: red; font-size: 16; }; "
+ "</style>");
out.println("<style> a,b {font-family: arial;"
+ " color: blue; font-size: 16; }; "
+ "</style>");
%>
<%
String strId =request.getParameter("id");
int id = Integer.parseInt(strId);
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";;
String db = "users";
String driver = "com.mysql.jdbc.Driver";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db,"root","");
try{
Statement st = con.createStatement();
String name=request.getParameter("name");
int in = st.executeUpdate("DELETE FROM student_info WHERE id='"+id+"'");
con.close();
out.println("<p> The record is successfully deleted. </p>");
out.println("<br>");
out.println("<a href='list.jsp'> RETURN TO MAIN PAGE </a>");
}
catch (SQLException ex){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
%>
