Basic Scaffolding(CRUD) Using JSP and MySQL

Scaffolding (CRUD Application) is a fundamental item for every single programmer that must be learned and known. Scaffolding contains of insert, view, update and delete data from program UI into database. So briefly, scaffolding will let user to manipulate database through application easily. In this opportunity I’d like to share about scaffolding using JSP (Java Server Pages). But we will prepare the requirement first:
1. Java Software Development Kit(JDK) latest version.
2. Web container, in here we use Apache Tomcat.
3. IDE or text editor to write down the program. But in here I suggest to use NETBEANS IDE.
4. Database server, we will use mysql
5. Database driver, we will use mysql jdbc driver
6. Web browser, IE, Mozilla Firefox, Google Chrome, Opera, Safari etc.
Alright, after we prepare the requirements, just install all of those requirements and I’m going to show you several steps in building this application.

Create Database like this :


-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 06, 2013 at 10:58 AM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `datafilm`
--

-- --------------------------------------------------------

--
-- Table structure for table `film`
--

CREATE TABLE IF NOT EXISTS `film` (
`id` int(50) NOT NULL AUTO_INCREMENT,
`judul` varchar(100) NOT NULL,
`kategori` varchar(100) NOT NULL,
`direktor` varchar(100) NOT NULL,
`penulis` varchar(100) NOT NULL,
`ceritasingkat` varchar(2000) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `film`
--

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

1. We will use Netbeans IDE. Create new project choose web application2. Insert project name as BasicScaffoldingJSP or anything . for the location just use the default one then next3. For the server choose Apache Tomcat4. For the frameworks don’t choose any of them because we will make it pure in JSP then finish5. If we success to make it, we will have project structure like this6. Before we code, don’t forget include MySQL JDBC Driver into our project through Library 7. Start the code.

a. create index.jsp


<%--
 Document   : index
 Created on : Sep 5, 2013, 5:09:57 PM
 Author     : Administrator
 --%>

<%@page import="java.sql.DriverManager"%>
 <%@page import="java.sql.Statement"%>
 <%@page import="java.sql.ResultSet"%>
 <%@page import="java.sql.Connection"%>
 <%@page contentType="text/html" pageEncoding="UTF-8"%>
 <!DOCTYPE html>
 <html>
 <head>
 <style type="text/css">
 .kolomKiri{
 text-align: right;
 font-style: oblique;
 }
 .kolomKanan{
 text-align: left;
 font-style: inherit;
 }
 .kolomTenga{
 text-align: center;
 font-style: inherit;
 }
 </style>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <title>Scaffolding JSP</title>
 </head>
 <body>
 <h1 align="center">Basic Scaffolding in JSP</h1>
 <hr/>
 <h3 align="center">INSERT</h3>
 <table align="center" >
 <form method="post" action="save.jsp">
 <tr>
 <td>Judul :</td>
 <td><input type="text" name="judul"></td>
 </tr>
 <tr>
 <td>Kategori :</td>
 <td>
 <select name="kategori">
 <option value="Science- Fiction">Science- Fiction</option>
 <option value="Action">Action</option>
 <option value="Horor">Horor</option>
 <option value="Comedy">Comedy</option>
 </select>
 </td>
 </tr>
 <tr>
 <td>Direktor :</td>
 <td><input type="text" name="direktor"></td>
 </tr>
 <tr>
 <td>Penulis :</td>
 <td><input type="text" name="penulis"></td>
 </tr>
 <tr>
 <td>Cerita Singkat :</td>
 <td><textarea cols="30" name="ceritasingkat"></textarea></td>
 </tr>
 <tr>
 <td><input type="submit" value="save"></td>
 <td><input type="reset"></td>
 </tr>
 </form>
 </table>
 <hr/>
 <%@include file="view.jsp" %>
 </body>
 </html>

b. Create koneksi.jsp


<%--
 Document   : koneksi
 Created on : Sep 5, 2013, 11:17:54 PM
 Author     : Administrator
 --%>

<%@page import="java.sql.DriverManager"%>
 <%@page import="java.sql.Connection"%>
 <%@page contentType="text/html" pageEncoding="UTF-8"%>
 <!DOCTYPE html>
 <%
 Connection conn = null;
 final String URL = "jdbc:mysql://localhost:3306/datafilm?";
 final String UNAME= "root";
 final String PASS= "";
 final String QUERYSELECT = "SELECT * FROM film";
 final String QUERYINSERT = "INSERT INTO FILM (judul, kategori, direktor, penulis, ceritasingkat) values (?, ?, ?, ?, ?)";
 final String QUERYUPDATE = "UPDATE film SET judul=?, kategori=?, direktor=?, penulis=?, ceritasingkat=? where id=?";
 final String QUERYDELETE = "DELETE FROM film WHERE id=?";

Class.forName("com.mysql.jdbc.Driver");
 conn = DriverManager.getConnection(URL, UNAME, PASS);
 %>

c. Create save.jsp


<%--
 Document   : save
 Created on : Sep 5, 2013, 6:51:41 PM
 Author     : Administrator
 --%>

<%@page import="java.sql.DriverManager"%>
 <%@page import="java.sql.PreparedStatement"%>
 <%@page import="java.sql.Connection, java.sql.*"%>
 <%@page contentType="text/html" pageEncoding="UTF-8"%>
 <!DOCTYPE html>
 <html>
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <%! Boolean status = false; %>
 </head>
 <body>
 <%@include file="koneksi.jsp" %>
 <%
 String judul = request.getParameter("judul");
 String kategori = request.getParameter("kategori");
 String direktor = request.getParameter("direktor");
 String penulis = request.getParameter("penulis");
 String ceritaSingkat = request.getParameter("ceritasingkat");
 PreparedStatement ps = null;
 try{
 conn.setAutoCommit(false);
 ps = conn.prepareStatement(QUERYINSERT);
 ps.setString(1, judul);
 ps.setString(2, kategori);
 ps.setString(3, direktor);
 ps.setString(4, penulis);
 ps.setString(5, ceritaSingkat);
 ps.executeUpdate();
 conn.commit();
 status = true;
 }catch(SQLException sqle){
 out.println("Error karena "+sqle);
 conn.rollback();
 }finally{
 if(ps!=null){
 ps.close();
 }
 conn.setAutoCommit(true);
 }

if(status == true){
 out.println("<script>alert('Berhasil Menyimpan')</script>");
 }else{
 out.println("<script>alert('Gagal Menyimpan')</script>");
 }
 out.println("<script>document.location.href='index.jsp'</script>");
 %>
 </body>
 </html>

d. create view.jsp


<%--
 Document   : view
 Created on : Sep 5, 2013, 10:34:40 PM
 Author     : Administrator
 --%>

<%@page import="java.sql.Statement"%>
 <%@page import="java.sql.ResultSet"%>
 <%@page import="java.sql.DriverManager"%>
 <%@page import="java.sql.Connection"%>
 <%@page contentType="text/html" pageEncoding="UTF-8"%>
 <!DOCTYPE html>
 <html>
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 </head>
 <body>
 <%
 String judul = request.getParameter("judul");
 String kategori = request.getParameter("kategori");
 String direktor = request.getParameter("direktor");
 String penulis = request.getParameter("penulis");
 String ceritaSingkat = request.getParameter("ceritasingkat");
 %>
 <h3 align="center">VIEW, UPDATE, DELETE</h3>
 <table align="center" border="1" cellspacing="0" cellpadding="2">
 <tr>
 <th>Id</th><th>Judul</th><th>Kategori</th><th>Direktor</th><th>Penulis</th><th>Cerita Singkat</th><th colspan="2"></th>
 </tr>
 <%@include file="koneksi.jsp" %>
 <%
 Statement s = conn.createStatement();
 ResultSet rs = s.executeQuery(QUERYSELECT);
 while(rs.next()){
 String id = rs.getString("id");
 %>
 <tr>
 <td><% out.println(rs.getString("id")); %></td>
 <td><% out.println(rs.getString("judul")); %></td>
 <td><% out.println(rs.getString("kategori")); %></td>
 <td><% out.println(rs.getString("direktor")); %></td>
 <td><% out.println(rs.getString("penulis")); %></td>
 <td><% out.println(rs.getString("ceritasingkat")); %></td>
 <td><a href="update.jsp?id=<%=id%>"><img src="image/b_edit.png"/></a></td>
 <td><a href="delete.jsp?id=<%=id%>"  onclick="return confirm('apakah anda yakin akan menghapus data dengan id <%=id%> ?')"><img src="image/b_drop.png"/></a></td>
 </tr>
 <%
 out.println("");
 }
 %>
 </table>
 <hr/>
 </body>
 </html>

e. create update.jsp


<%--
 Document   : update
 Created on : Sep 5, 2013, 7:45:03 PM
 Author     : Administrator
 --%>

<%@page import="java.sql.Statement"%>
 <%@page import="java.sql.ResultSet"%>
 <%@page contentType="text/html" pageEncoding="UTF-8"%>
 <!DOCTYPE html>
 <html>
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <title>Update</title>
 </head>
 <body>
 <%@include file="koneksi.jsp" %>
 <%
 String id = request.getParameter("id");
 Statement s = conn.createStatement();
 ResultSet rs = s.executeQuery("SELECT * FROM film WHERE id="+id);
 String judul = "";
 String kategori = "";
 String direktor = "";
 String penulis = "";
 String ceritaSingkat = "";
 if(rs.next()){
 judul = rs.getString("judul");
 kategori = rs.getString("kategori");
 direktor = rs.getString("direktor");
 penulis = rs.getString("penulis");
 ceritaSingkat = rs.getString("ceritasingkat");
 }
 %>
 <hr/>
 <h3 align="center">UPDATE</h3>
 <table align="center" >
 <form method="post" action="update_exec.jsp">
 <input type="hidden" name="id" value="<%=id%>">
 <tr>
 <td>Judul :</td>
 <td><input type="text" name="judul" value="<%=judul%>"></td>
 </tr>
 <tr>
 <td>Kategori :</td>
 <td>
 <select name="kategori" selected="<%=kategori%>">
 <option value="Science- Fiction">Science- Fiction</option>
 <option value="Action">Action</option>
 <option value="Horor">Horor</option>
 <option value="Comedy">Comedy</option>
 </select>
 </td>
 </tr>
 <tr>
 <td>Direktor :</td>
 <td><input type="text" name="direktor" value="<%=direktor%>"></td>
 </tr>
 <tr>
 <td>Penulis :</td>
 <td><input type="text" name="penulis" value="<%=penulis%>"></td>
 </tr>
 <tr>
 <td>Cerita Singkat :</td>
 <td><textarea cols="30" name="ceritasingkat"><%=ceritaSingkat%></textarea></td>
 </tr>
 <tr>
 <td><input type="submit" value="save"></td>
 <td><input type="reset"></td>
 </tr>
 </form>
 </table>
 <hr/>
 </body>
 </html>

f. create update_exec.jsp


<%--
 Document   : update_exec
 Created on : Sep 5, 2013, 11:15:21 PM
 Author     : Administrator
 --%>

<%@page import="java.sql.SQLException"%>
 <%@page import="java.sql.PreparedStatement"%>
 <%@page contentType="text/html" pageEncoding="UTF-8"%>
 <!DOCTYPE html>
 <html>
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 </head>
 <body>
 <%@include file="koneksi.jsp"%>
 <%
 boolean status = false;
 String id = request.getParameter("id");
 String judul = request.getParameter("judul");
 String kategori = request.getParameter("kategori");
 String direktor = request.getParameter("direktor");
 String penulis = request.getParameter("penulis");
 String ceritaSingkat = request.getParameter("ceritasingkat");
 PreparedStatement ps = null;
 try{
 conn.setAutoCommit(false);
 ps = conn.prepareStatement(QUERYUPDATE);
 ps.setString(1, judul);
 ps.setString(2, kategori);
 ps.setString(3, direktor);
 ps.setString(4, penulis);
 ps.setString(5, ceritaSingkat);
 ps.setString(6, id);
 ps.executeUpdate();
 conn.commit();
 status = true;
 }catch(SQLException sqle){
 conn.rollback();
 }finally{
 if(ps!=null){
 ps.close();
 }
 conn.setAutoCommit(true);
 }
 if(status == true){
 out.println("<script>alert('Update Berhasil')</script>");
 }else{
 out.println("<script>alert('Update Gagal')</script>");
 }
 out.println("<script>document.location.href='index.jsp'</script>");
 %>
 </body>
 </html>
 </sourcecode>

g. create delete.jsp



<%--
 Document   : delete
 Created on : Sep 5, 2013, 7:45:10 PM
 Author     : Administrator
 --%>

<%@page import="java.sql.SQLException"%>
 <%@page contentType="text/html" pageEncoding="UTF-8"%>
 <!DOCTYPE html>
 <html>
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 </head>
 <body>
 <%@include file="koneksi.jsp" %>%>
 <%
 Boolean status = false;
 String id = request.getParameter("id");
 java.sql.PreparedStatement ps = null;
 try{
 conn.setAutoCommit(false);
 ps = conn.prepareStatement(QUERYDELETE);
 ps.setString(1, id);
 ps.executeUpdate();
 conn.commit();
 status = true;
 }catch(SQLException sqle){
 out.println("gagal delete karena = "+sqle);
 conn.rollback();
 }finally{
 if(ps!=null){
 ps.close();
 }
 conn.setAutoCommit(true);
 }
 if(status == true){
 out.println("<script>alert('Delete Berhasil')</script>");
 }else{
 out.println("<script>alert('Delete Gagal')</script>");
 }
 out.println("<script>document.location.href='index.jsp'</script>");
 %>
 </body>
 </html>

h. For image edit and delete, we can put in folder image. So, just create folder image under Web Pages then copy the images in there.

i. run the program

INSERT

VIEW

UPDATE

DELETE

DELETE SUCCESS

Tagged: , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: