CREATE DATABASE MyDatabase
========================SQL Server======================
create table Employee
(
id varchar(10) primary key,
name nvarchar(50),
[address] nvarchar(100),
email nvarchar(50)
)
Download Driver SQL Server 
ConnectionFactory.java
Java Jsp/Servet 2017
package com.giaima.dbconnect;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionFactory {
// static reference to itself
private static ConnectionFactory instance = new ConnectionFactory();
String url = "jdbc:sqlserver://localhost;databaseName=MyDatabase;useUnicode=true;characterEncoding=UTF-8";
String user = "sa";
String password = "12345678";
String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
// private constructor
private ConnectionFactory() {
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static ConnectionFactory getInstance() {
return instance;
}
public Connection getConnection() throws SQLException, ClassNotFoundException {
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
// Test connection database
// public static void main(String[] args) throws ClassNotFoundException, SQLException {
// System.out.println(getInstance().getConnection());
// }
}
Test connect thành công!
CrudDAO.java
Java Jsp/Servet 2017
package com.giaima.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.giaima.dbconnect.ConnectionFactory;
import com.giaima.model.Employee;
public class CrudDAO {
private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
private static Connection getConnection() throws SQLException, ClassNotFoundException {
Connection con = ConnectionFactory.getInstance().getConnection();
return con;
}
public ArrayList<Employee> searchEmployee(String name) {
ArrayList<Employee> list = new ArrayList<Employee>();
String sql = "SELECT * FROM Employee WHERE name = ?";
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
rs = pstmt.executeQuery();
Employee empl = new Employee();
while (rs.next()) {
empl.setId(rs.getString("id"));
empl.setName(rs.getString("name"));
empl.setAddress(rs.getString("address"));
empl.setEmail(rs.getString("email"));
list.add(empl);
}
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return list; //Trả kết quả về List
}
}
SearchEmployee.java
Java Jsp/Servet 2017
package com.giaima.controller;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.giaima.dao.CrudDAO;
import com.giaima.model.Employee;
import com.google.gson.Gson;
/**
* Servlet implementation class SearchEmployee
*/
@WebServlet("/SearchEmployee")
public class SearchEmployee extends HttpServlet {
private static final long serialVersionUID = 1L;
public SearchEmployee() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String name = request.getParameter("name");
CrudDAO dao = new CrudDAO();
ArrayList<Employee> list = dao.searchEmployee(name);
if (!list.isEmpty()) {
PrintWriter out = response.getWriter();
response.setCharacterEncoding("UTF8");
response.setContentType("application/json");
//Import gson-2.2.2.jar
Gson gson = new Gson();
String objectToReturn = gson.toJson(list); //Convert List -> Json
out.write(objectToReturn); //Đưa Json trả về Ajax
out.flush();
} else {
PrintWriter out = response.getWriter();
response.setCharacterEncoding("UTF8");
response.setContentType("application/json");
out.write("{\"check\":\"fail\"}");
out.flush();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
}
Employee.java
Java Jsp/Servet 2017
package com.giaima.model;
public class Employee {
private String id;
private String name;
private String address;
private String email;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Employee(String id, String name, String address, String email) {
super();
this.id = id;
this.name = name;
this.address = address;
this.email = email;
}
public Employee() {
super();
}
}
index.jsp
Java Jsp/Servet 2017
<%@ 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>Search Employee</title> <script src="js/jquery-1.10.0.js"></script> //Thư việc hỗ trợ Jquery không được thiếu <script src="js/ajaxEmployee.js"></script> <style type="text/css"> th{ //Css cho thẻ <th> phần t head background: rgba(0, 135, 255, 0.46); } .text-center{ //Css cho các class mới tạo bằng Jquery trong thẻ <td> text-align: center; } .trOnColor{ //Css cho class mới tạo bằng Jquery trong thẻ <tr> background: rgba(0, 121, 128, 0.23); } </style> </head> <body> <p id="message" align="left"></p>//Hiển thị thông báo không tìm thấy bản ghi Name<input type="text" id="name" name="name"/> <input type="button" class="search" value="Search" /> <table border="1px" width="500px"> <thead> <tr> <th>No</th> <th>ID</th> <th>Name</th> <th>Address</th> <th>Email</th> </tr> </thead> <tbody id="row"> //Dữ liệu sẽ được thêm mới bằng việc thêm các thẻ <tr> <td> và giá trị bắt đầu từ id=row </tbody> </table> </body> </html>
ajaxEmployee.js
Java Jsp/Servet 2017
$(function () {
$('.search').click(function () {
var name = $("#name").val();
$.ajax({
type: "GET",
url: "SearchEmployee", //Tên servlet
data: "name=" + name, //Gán giá trị vào name mục đich để servlet nhận được Parameter
dataType: "json",
async: true,
cache: false,
success: function (result) {
//Lấy size của list này
var listSize = Object.keys(result).length;
//Nếu List lấy từ Dao là null thì thông báo Not found và return
if (result.check == "fail") {
$('#message').text("List isEmpty! Name not found!");
$('#message').css('color', 'red');
return;
}
//Nếu list tồn tại thì reset thông điệp cảnh báo về rỗng và loop dữ liệu hiển thị ra table
if (listSize > 0) {
$('#message').text("");
//Khai báo table kiểu global để sử dụng ngoài fuction này
table = document.getElementById("row");
for (i = 0; i < listSize; i++) {
var row = table.insertRow(i);
var cell = row.insertCell(0);
var cell1 = row.insertCell(1);
var cell2 = row.insertCell(2);
var cell3 = row.insertCell(3);
var cell4 = row.insertCell(4);
cell.innerHTML = i;
cell1.innerHTML = result[i].id;
cell2.innerHTML = result[i].name;
cell3.innerHTML = result[i].address;
cell4.innerHTML = result[i].email;
// Thêm class vào tr
row.className += 'trOnColor';// class có tên trOnColor
// Thêm class vào td
cell.className += 'text-center'; // Class có tên text-center
// Thêm class vào td tạo kích thước width cho từng cột
cell1.className += 'text-center';
cell2.className += 'text-center';
cell3.className += 'text-center';
cell4.className += 'text-center';
}
}
}
});
});
//Reset table về trạng thái không có bản ghi nào
$('.search').click(function () {
var trLength = table.getElementsByTagName("tr").length;
if (trLength > 0) {
for (i = 0; i < trLength; i++) {
table.deleteRow(0);
};
}
});
});
Note: Các bạn nhớ download hỗ trợ chạy Jquery: jquery-1.10.0.jsRUN App:
Database
0 nhận xét:
Post a Comment