该系统需要设计以下有关的组件,主要是设计3个大类和一个接口。
(1)描述学生信息的数据类:Students类。
(2)数据库连接和关闭的工具:JavaBean类。
(3 实现数据库访问和业务逻辑的结合体DAO类:StudentDAO类,该DAO类的实例对象应负责处理数据库记录的基本操作(增删改查),即完成对CRUD操作的封装。
(4)实现业务逻辑处理的接口:IStudentDAO。
(5)实现诗句信息提交,查询,修改,删除等有关操作的JSP页面。
代码如下:
1.建立对应数据库表结构的JavaBean——学生表Student
package bean;
public class Student {
// 学号
private String Sno;
// 学生名称
private String Sname;
// 性别
private String Ssex;
// 年龄
private int Sage;
// 专业
private String Sdept;
public String getSno() {
return Sno;
}
public void setSno(String sno) {
this.Sno = sno;
}
public String getSname() {
return Sname;
}
public void setSname(String sname) {
this.Sname = sname;
}
public String getSsex() {
return Ssex;
}
public void setSsex(String ssex) {
this.Ssex = ssex;
}
public int getSage() {
return Sage;
}
public void setSage(int sage) {
this.Sage = sage;
}
public String getSdept() {
return Sdept;
}
public void setSdept(String sdept) {
this.Sdept = sdept;
}
}
2.数据库连接与关闭资源工具JavaBean类的设计
package db;
import java.sql.*;
public class Dbconnect {
private static String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static String userName = "sa";
private static String userPwd = "jiayou417";
private static String dbName = "lzy";
public static Connection getDBconnection(){
String url = "jdbc:sqlserver://localhost:1433; DatabaseName=lzy";
try {
Class.forName(driverName);
Connection con = DriverManager.getConnection(url,userName,userPwd);
return con;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void closeDB(Connection con,PreparedStatement pstm,ResultSet rs){
try{
if(rs != null)
rs.close();
if(pstm != null)
pstm.close();
if(con != null)
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
3.建立实现数据库处理的接口
package dao;
import java.util.List;
import bean.Student;
public interface IStudentDAO {
public abstract Student create(Student stu) throws Exception;
public abstract void remove(Student stu) throws Exception;
public abstract Student find(Student stu) throws Exception;
public abstract List<Student> findAll() throws Exception;
public abstract void update(Student stu) throws Exception;
}
4.对接口的实现以及逻辑访问类StudentDAO类
package dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import db.Dbconnect;
import bean.Student;
public class StudentDAO implements IStudentDAO{
protected static final String FIELDS_INSERT="sno,sname,ssex,sage,sdept";
protected static String INSERT_SQL="insert into Student("+FIELDS_INSERT+")"+"values(?,?,?,?,?)";
protected static String SELECT_SQL="select "+FIELDS_INSERT+" from Student where sno=?";
protected static String UPDATE_SQL="update Student set"+" sno=?,sname=?,ssex=?,sage=?,sdept=? where sno=?";
protected static String DELETE_SQL="delete from Student where sno=?";
@Override
public Student create(Student stu) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
ResultSet rs=null;
try{
con=Dbconnect.getDBconnection();
prepStmt=con.prepareStatement(INSERT_SQL);
prepStmt.setString(1, stu.getSno());
prepStmt.setString(2, stu.getSname());
prepStmt.setString(3, stu.getSsex());
prepStmt.setInt(4, stu.getSage());
prepStmt.setString(5, stu.getSdept());
prepStmt.executeUpdate();
}catch(Exception e){
}finally{
Dbconnect.closeDB(con, prepStmt, rs);
}
return stu;
}
@Override
public void remove(Student stu) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
ResultSet rs=null;
try{
con=Dbconnect.getDBconnection();
prepStmt=con.prepareStatement(DELETE_SQL);
prepStmt.setString(1, stu.getSno());
prepStmt.executeUpdate();
}catch(Exception e){
}finally{
Dbconnect.closeDB(con, prepStmt, rs);
}
}
@Override
public Student find(Student stu) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
ResultSet rs=null;
Student stu2=null;
try{
con=Dbconnect.getDBconnection();
prepStmt=con.prepareStatement(SELECT_SQL);
prepStmt.setString(1,stu.getSno());
rs=prepStmt.executeQuery();
if(rs.next()){
stu2=new Student();
stu2.setSno(rs.getString(1));
stu2.setSname(rs.getString(2));
stu2.setSsex(rs.getString(3));
stu2.setSage(rs.getInt(4));
stu2.setSdept(rs.getString(5));
}
}catch(Exception e){
}finally{
Dbconnect.closeDB(con, prepStmt, rs);
}
return stu2;
}
@Override
public List<Student> findAll() throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
ResultSet rs=null;
List<Student> student=new ArrayList<Student>(); //创建集合存放Student引用
con=Dbconnect.getDBconnection();
prepStmt=con.prepareStatement("select * from Student");
rs=prepStmt.executeQuery();
while(rs.next()){
Student stu2=new Student();
stu2.setSno(rs.getString(1));
stu2.setSname(rs.getString(2));
stu2.setSsex(rs.getString(3));
stu2.setSage(rs.getInt(4));
stu2.setSdept(rs.getString(5));
student.add(stu2); //将引用添加到集合中
}
return student;
}
@Override
public void update(Student stu) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
ResultSet rs=null;
try{
con=Dbconnect.getDBconnection();
prepStmt=con.prepareStatement(UPDATE_SQL);
prepStmt.setString(1, stu.getSno());
prepStmt.setString(2, stu.getSname());
prepStmt.setString(3, stu.getSsex());
prepStmt.setInt(4, stu.getSage());
prepStmt.setString(5, stu.getSdept());
prepStmt.setString(6,stu.getSno()); //要求学号不可更改
int rowCount = prepStmt.executeUpdate();
if(rowCount == 0){
throw new Exception("update Error:Student Sno:"+stu.getSno());
}
}catch(Exception e){
}finally{
Dbconnect.closeDB(con, prepStmt, rs);
}
}
}
4.对应数据库操作的Servlet设计
package servlet;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import bean.Student;
import dao.StudentDAO;
public class create extends HttpServlet {
/**
* Constructor of the object.
*/
public create() {
super();
}
/**
* Destruction of the servlet. <br>
*/
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("GB2312");
Student stu=new Student();
stu.setSno(request.getParameter("sno"));
stu.setSname(request.getParameter("sname"));
stu.setSsex(request.getParameter("ssex"));
stu.setSage(Integer.parseInt(request.getParameter("sage")));
stu.setSdept(request.getParameter("sdept"));
StudentDAO insert=new StudentDAO();
try {
insert.create(stu);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Initialization of the servlet. <br>
*
* @throws ServletException if an error occurs
*/
public void init() throws ServletException {
// Put your code here
}
}
package servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import bean.Student;
import dao.StudentDAO;
public class find extends HttpServlet {
/**
* Constructor of the object.
*/
public find() {
super();
}
/**
* Destruction of the servlet. <br>
*/
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Student stu=new Student();
stu.setSno(request.getParameter("sno"));
StudentDAO find=new StudentDAO();
try {
Student student=find.find(stu);
request.setAttribute("stu",student);
System.out.println("查找成功");
} catch (Exception e) {
System.out.println("查找失败");
}
request.getRequestDispatcher("findshow.jsp").forward(request, response);
}
/**
* Initialization of the servlet. <br>
*
* @throws ServletException if an error occurs
*/
public void init() throws ServletException {
// Put your code here
}
}
package servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import bean.Student;
import dao.StudentDAO;
public class findAll extends HttpServlet {
/**
* Constructor of the object.
*/
public findAll() {
super();
}
/**
* Destruction of the servlet. <br>
*/
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
StudentDAO findAll=new StudentDAO();
List<Student> student=new ArrayList<Student>();
try {
student = findAll.findAll();
request.setAttribute("list", student);
} catch (Exception e) {
e.printStackTrace();
}
request.getRequestDispatcher("findAll.jsp").forward(request, response);
}
/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
/**
* Initialization of the servlet. <br>
*
* @throws ServletException if an error occurs
*/
public void init() throws ServletException {
// Put your code here
}
}
package servlet;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import bean.Student;
import dao.StudentDAO;
public class remove extends HttpServlet {
/**
* Constructor of the object.
*/
public remove() {
super();
}
/**
* Destruction of the servlet. <br>
*/
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Student stu = new Student();
stu.setSno(request.getParameter("sno"));
StudentDAO remove=new StudentDAO();
try{
remove.remove(stu);
System.out.println("删除成功");
}catch (Exception e) {
System.out.println("删除失败");
}
}
/**
* Initialization of the servlet. <br>
*
* @throws ServletException if an error occurs
*/
public void init() throws ServletException {
// Put your code here
}
}
package servlet;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import bean.Student;
import dao.StudentDAO;
public class update extends HttpServlet {
/**
* Constructor of the object.
*/
public update() {
super();
}
/**
* Destruction of the servlet. <br>
*/
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Student stu=new Student();
stu.setSno(request.getParameter("sno"));
stu.setSname(request.getParameter("sname"));
stu.setSsex(request.getParameter("ssex"));
stu.setSage(Integer.parseInt(request.getParameter("sage")));
stu.setSdept(request.getParameter("sdept"));
StudentDAO update=new StudentDAO();
try {
update.update(stu);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Initialization of the servlet. <br>
*
* @throws ServletException if an error occurs
*/
public void init() throws ServletException {
// Put your code here
}
}
5.JSP页面的设计
JSP页面设计一共有八个,如有需要私聊我或者留言,这里就不一一贴出。
6.XML文件
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance http://www.springmodules.org/schema/cache/springmodules-cache.xsd http://www.springmodules.org/schema/cache/springmodules-ehcache.xsd" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
<display-name>Super数据库</display-name>
<servlet>
<servlet-name>create</servlet-name>
<display-name>This is the display name of my J2EE component</display-name>
<description>This is the description of my J2EE component</description>
<servlet-class>servlet.create</servlet-class>
</servlet>
<servlet>
<servlet-name>find</servlet-name>
<display-name>This is the display name of my J2EE component</display-name>
<description>This is the description of my J2EE component</description>
<servlet-class>servlet.find</servlet-class>
</servlet>
<servlet>
<servlet-name>findAll</servlet-name>
<display-name>FindAll</display-name>
<description>This is the description of my J2EE component</description>
<servlet-class>servlet.findAll</servlet-class>
</servlet>
<servlet>
<servlet-name>remove</servlet-name>
<display-name>This is the display name of my J2EE component</display-name>
<description>This is the description of my J2EE component</description>
<servlet-class>servlet.remove</servlet-class>
</servlet>
<servlet>
<servlet-name>update</servlet-name>
<display-name>This is the display name of my J2EE component</display-name>
<description>This is the description of my J2EE component</description>
<servlet-class>servlet.update</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>create</servlet-name>
<url-pattern>/create</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>find</servlet-name>
<url-pattern>/find</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>findAll</servlet-name>
<url-pattern>/findAll</url-pattern>`````````
</servlet-mapping>
<servlet-mapping>
<servlet-name>remove</servlet-name>
<url-pattern>/remove</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>update</servlet-name>
<url-pattern>/update</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
</web-app>
运行界面如下图: