Servlet + JSP + MySQL Example


Ok sau bài này mình hướng dẫn các bạn bài tập kết nối đến database mysql . và 1 trang quản lì sản phẩm cơ bản như sau :

Chúng ta có database như hình vẻ sau :

d1

Chương trình chúng ta như sau : trang index sẻ hiển thị như sau :

d1

Khi ta nhấn vào Link Insert New chúng ta sẻ hiển thị trang thêm sản phấm : insert.jsp

d1

Khi ta nhấn insert button sẻ hiển thị trở lại trang index.jsp với sản phẩm vửa được nhập :

d1

Khi ta nhấn Edit sẻ hiển thị trang edit.jsp củng với data hiển thi sẳn trong table của chúng ta :

d1

Tương tự khi update xong chúng ta củng hiển thị lại List các sản phẩm . và sản phẩm được cập nhật. Tương ứng khi ta nhận delete thì sản phẩm sẻ bị xóa đi, và cập nhật lại table của chúng ta. OK bài này như sau :

Để kết nối mysql chúng ta cần driver : mysql_connector-java-5.x.x-bin.jar. các bạn có thể search ở google. và down load về bỏ vào thư mục lib bên dưới thư mục Webcontent/Web-inf/lib.

Hình ảnh toàn bộ Project của mình như sau :

d1

Code: index.jsp


<%@page import="com.example.thaihoanghai.ConnectDB"%>
<%@page import="com.example.thaihoanghai.SanPham"%>
<%@page import="java.util.ArrayList"%>
<%@ 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">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Product Information</title>
</head>
<body>
 <% ArrayList<SanPham> lst = new ConnectDB().getAllProducts(); %>
 <h1 align="center">List Product</h1>
 <table border="1" width="80%" align="center">
 <tr>
 <th>Product's ID</th>
 <th>Product's Name</th>
 <th>Provider</th>
 <th>Unit's Price</th>
 <th colspan="2"><a href="insert.jsp">Insert New</a></th>
 </tr>
 <%
 for(SanPham sp : lst){
 String editURL = "edit.jsp?masp="+sp.getMasp();
 String deleteURL = "DeleteServlet?masp="+sp.getMasp();
 %>
 <tr>
 <td><%=sp.getMasp() %></td>
 <td><%=sp.getTensp() %></td>
 <td><%=sp.getNhacc() %></td>
 <td><%=sp.getGiadv() %></td>
 <td><a href="<%=editURL%>">Edit</a></td>
 <td><a href="<%=deleteURL%>">Delete</a></td>
 </tr>
 <%
 }
 %>
 </table>
</body>
</html>

page insert.jsp


<%@ 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">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert Product</title>
</head>
<body>
<form action="InsertServlet">
 <h1>Insert New Product</h1>
 <table>
 <tr>
 <td>Product's ID</td>
 <td><input type="text" name="masp"></td>
 </tr>
 <tr>
 <td>Product's Name</td>
 <td><input type="text" name="tensp"></td>
 </tr>
 <tr>
 <td>Provider</td>
 <td><input type="text" name="nhacc"></td>
 </tr>
 <tr>
 <td>Unit's Price</td>
 <td><input type="text" name="giadv"></td>
 </tr>
 <tr>

 <td colspan="2" align="right">
 <button type="submit" name="insert">Insert</button>
 <button type="reset" name="reset">Reset</button>
 </td>
 </tr>
 </table>
</form>
</body>
</html>

page edit.jsp


<%@page import="com.example.thaihoanghai.ConnectDB"%>
<%@page import="com.example.thaihoanghai.SanPham"%>
<%@ 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">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Edit Product</title>
</head>
<body>
<% SanPham sp = new ConnectDB().getProductByID(request.getParameter("masp")); %>
<form action="UpdateServlet">
 <h1>Edit Product</h1>
 <table>
 <tr>
 <td>Product's ID</td>
 <td><input type="text" name="masp" value="<%=sp.getMasp()%>" readonly="readonly"></td>
 </tr>
 <tr>
 <td>Product's Name</td>
 <td><input type="text" name="tensp" value="<%=sp.getTensp()%>"></td>
 </tr>
 <tr>
 <td>Provider</td>
 <td><input type="text" name="nhacc" value="<%=sp.getNhacc()%>" /></td>
 </tr>
 <tr>
 <td>Unit's Price</td>
 <td><input type="text" name="giadv" value="<%=sp.getGiadv()%>" /></td>
 </tr>
 <tr>

 <td colspan="2" align="right">
 <button type="submit" name="update">Update</button>
 <button type="reset" name="reset">Reset</button>
 </td>
 </tr>
 </table>
</form>
</body>
</html>

Class ConnectDB.java


package com.example.thaihoanghai;
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
/**
 * This class used : open/close Connect to DB and create Query
 * @author kobe
 */
public class ConnectDB {

Connection connect = null;
 Statement stmt = null;
 ResultSet rs = null;

/**
 * Check Driver connect to MySQL
 */
 protected void hasDriver() throws Exception{
 try {
 Class.forName("com.mysql.jdbc.Driver");
 } catch (ClassNotFoundException ex) {
 throw new Exception ("Invalid Driver!!Please check this drver....");
 }
 }

/**
 * Function used to get the connection to the Database
 * Step 1 - I check my connection or not!!
 * Step 2 - If not, it will be null and initialization.
 * Step 3 - Then it return
 * @return Connection
 */
 protected Connection openConnect() throws Exception{
 if(connect == null){
 hasDriver();
 String url = "jdbc:mysql://localhost/db_bai3";
 // url_sql = "....";
 try {
 this.connect = DriverManager.getConnection(url,"root","");
 } catch (SQLException e) {
 throw new Exception(e.getMessage() + "Connect failed to database .... ");
 }
 }
 return connect;
 }
 /**
 * Make a Statement to execute the SQL statement
 * @return Statement
 */
 protected Statement getStatement() throws SQLException, Exception{
 if(stmt == null){
 stmt = openConnect().createStatement();
 }
 return stmt;
 }
 /**
 * Used to execute the Select statement
 * @param strSQL Query VD: Select * from Employee
 * @return ResultSet
 */
 public ArrayList<SanPham> getAllProducts() throws Exception{
 ArrayList<SanPham> lst = new ArrayList<SanPham>();
 String strSQL = "select * from SanPham";
 try {
 rs = getStatement().executeQuery(strSQL);
 while(rs.next()){
 String ms = rs.getString("masp");
 String ten = rs.getString("tensp");
 String ncc = rs.getString("nhacc");
 double gia = Double.parseDouble(rs.getString("giadv"));
 SanPham sp = new SanPham(ms, ten, ncc, gia);
 lst.add(sp);
 }
 } catch (Exception e) {
 throw new Exception(e.getMessage() +" Error at : " + strSQL);
 }
 closeConnet();
 return lst;
 }

 public boolean insertNew(SanPham sp) throws Exception{
 String sql = "insert into SanPham values(?,?,?,?)";
 PreparedStatement pst = openConnect().prepareStatement(sql);
 pst.setString(1, sp.getMasp());
 pst.setString(2, sp.getTensp());
 pst.setString(3, sp.getNhacc());
 pst.setDouble(4, sp.getGiadv());

 return pst.executeUpdate() > 0;

 }

 public boolean deleteProduct(String masp) throws Exception{
 String sql = "delete from SanPham where masp=?";
 PreparedStatement pst = openConnect().prepareStatement(sql);
 pst.setString(1, masp);
 return pst.executeUpdate() > 0;
 }

 /**
 * Used to execute the Insert, Update, Delete statement
 * @param strSQL Query VD: Insert into TableName values ('??','??')
 * @return The number of lines affected by the command
 */
 public int executeUpdate(String strSQL) throws Exception{
 int result = 0;
 try {
 result = getStatement().executeUpdate(strSQL);
 } catch (Exception ex) {
 throw new Exception(ex.getMessage() + " Error at: " + strSQL);
 }finally{
 this.closeConnet();
 }
 return result;
 }

 public SanPham getProductByID(String masp)throws Exception{
 String sql = "select * from SanPham where masp=?";
 PreparedStatement pst = openConnect().prepareStatement(sql);
 pst.setString(1, masp);
 ResultSet rs = pst.executeQuery();
 SanPham sp = null;
 if(rs.next()){
 String ms = rs.getString("masp");
 String ten = rs.getString("tensp");
 String ncc = rs.getString("nhacc");
 double gia = Double.parseDouble(rs.getString("giadv"));
 sp = new SanPham(ms, ten, ncc, gia);
 }
 return sp;
 }

 public boolean UpdateProduct(String masp,SanPham newsp) throws Exception{
 String sql = "update SanPham set tensp=?, nhacc=?, giadv=? where masp=?";
 PreparedStatement pst = openConnect().prepareStatement(sql);
 pst.setString(1, newsp.getTensp());
 pst.setString(2, newsp.getNhacc());
 pst.setDouble(3, newsp.getGiadv());
 pst.setString(4, newsp.getMasp());
 return pst.executeUpdate()>0;
 }
 /**
 * A method to close the connection.
 * @throws SQLException
 */
 public void closeConnet() throws SQLException{
 if(rs != null && !rs.isClosed())
 rs.close();
 if(stmt != null)
 stmt.close();
 if(connect != null)
 connect.close();
 }

 public static void main(String[] args) throws Exception {
 new ConnectDB().UpdateProduct("sp01", new SanPham("sp01", "AAAA", "BBBB", 1000));
 System.out.println(new ConnectDB().getProductByID("sp01").getTensp());

 }

}

Class Sanpham.java


package com.example.thaihoanghai;

public class SanPham {
 private String masp;
 private String tensp;
 private String nhacc;
 private double giadv;

 public SanPham(String masp, String tensp, String nhacc, double giadv) {
 this.masp = masp;
 this.tensp = tensp;
 this.nhacc = nhacc;
 this.giadv = giadv;
 }

public SanPham() {
 }

 public String getMasp() {
 return masp;
 }
 public void setMasp(String masp) {
 this.masp = masp;
 }
 public String getTensp() {
 return tensp;
 }
 public void setTensp(String tensp) {
 this.tensp = tensp;
 }
 public String getNhacc() {
 return nhacc;
 }
 public void setNhacc(String nhacc) {
 this.nhacc = nhacc;
 }
 public double getGiadv() {
 return giadv;
 }
 public void setGiadv(double giadv) {
 this.giadv = giadv;
 }
 @Override
 public int hashCode() {
 final int prime = 31;
 int result = 1;
 result = prime * result + ((masp == null) ? 0 : masp.hashCode());
 return result;
 }
 @Override
 public boolean equals(Object obj) {
 if (this == obj)
 return true;
 if (obj == null)
 return false;
 if (getClass() != obj.getClass())
 return false;
 SanPham other = (SanPham) obj;
 if (masp == null) {
 if (other.masp != null)
 return false;
 } else if (!masp.equals(other.masp))
 return false;
 return true;
 }

}

Trang servlet : InsertServlet.java tùy chỉnh trong doget như sau :


protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 String masp = request.getParameter("masp");
 String tensp = request.getParameter("tensp");
 String nhacc = request.getParameter("nhacc");
 double giadv = Double.parseDouble(request.getParameter("giadv"));

 SanPham sp = new SanPham(masp, tensp, nhacc, giadv);
 ConnectDB db = new ConnectDB();
 try {
 if(db.insertNew(sp))
 response.sendRedirect("index.jsp");
 else
 response.sendRedirect("error.jsp");
 } catch (Exception e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }
 }

Trang Servlet UpdateServlet.java


protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 String masp = request.getParameter("masp");
 String tensp = request.getParameter("tensp");
 String nhacc = request.getParameter("nhacc");
 double giadv = Double.parseDouble(request.getParameter("giadv"));

 SanPham sp = new SanPham(masp, tensp, nhacc, giadv);
 ConnectDB db = new ConnectDB();
 try {
 if(db.UpdateProduct(masp, sp))
 response.sendRedirect("index.jsp");
 else
 response.sendRedirect("error.jsp");
 } catch (Exception e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }

 }

Page servlet DeleteServlet.java


protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 String masp = request.getParameter("masp");
 ConnectDB connect = new ConnectDB();
 try {
 if(connect.deleteProduct(masp))
 response.sendRedirect("index.jsp");
 else
 response.sendRedirect("error.jsp");
 } catch (Exception e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }

}

Ok chúc các bạn thành công.

Posted in Web. 2 Comments »

2 Responses to “Servlet + JSP + MySQL Example”

  1. tiến Says:

    mình gặp vấn đề với lỗi font chữ khi load từ database mysql lên : có cách nào xử lí không bạn? mình đã thử dùng N”text” nhưng ko được


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: