5/4

web实验3:

实验项目名称：实验三 Web数据库程序设计

一、实验目的

通过使用JSP技术设计一个简单的数据库管理系统，了解展示页面和编辑页面的区别，掌握Web服务器与MySQL数据库的连接和数据库操作的方法，掌握使用Java语言编写JSP文件的方法。

二、实验内容和基本要求

从以下列举的四个管理系统中，任选其一完成信息的查看、增加、删除和修改等功能。

1) 学生管理系统：存储的信息包括学生学号、姓名、性别、生日等。

2) 商品管理系统：存储的信息包括商品ID、商品名称、商品数量、生产厂家等。

3) 客户管理系统：存储的信息包括客户ID、客户姓名、客户地址、手机号码等。

4) 车辆管理系统：存储的信息包括汽车ID、品牌、颜色、车主姓名等。

对于客户端增加和修改信息页面，要使用javascript进行必要的数据的非空验证。自行添加一些CSS，使得页面和字体更加美观。将网页代码、程序源代码和浏览器截图写入实验报告。

package Bean;

public class Student {
    private String id;
    private String name;
    private String sex;
    private String birth;
    
    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 getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getBirth() {
        return birth;
    }
    public void setBirth(String birth) {
        this.birth = birth;
    }

}
7)Dao包stuDao.java
package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Bean.Student;
import DBUtil.DBUtil;

public class stuDao {
    
    public int add(Student B) throws ClassNotFoundException, SQLException 
    {
         Connection connection = DBUtil.getConnection();
         String sql = "insert into student(id,name,sex,birth) values(?,?,?,?)";
        PreparedStatement preparedStatement = null;
        int i=0;
         try {
             preparedStatement = connection.prepareStatement(sql);
              preparedStatement.setString(1, B.getId());
             preparedStatement.setString(2, B.getName());
             preparedStatement.setString(3,B.getSex());
             preparedStatement.setString(4,B.getBirth());
             i=preparedStatement.executeUpdate();
         } catch (SQLException e) {
           e.printStackTrace();
        }finally {
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
         return i;        
    }
    
    public List<Student> Show() 
    {
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
        } catch (ClassNotFoundException|SQLException e1) 
        {
            e1.printStackTrace();
        }     
        List<Student> list = new ArrayList<Student>();
        try {
            String sql="select * from  student";
            PreparedStatement pstmt = conn.prepareStatement(sql); 
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) 
            {
                Student data=new Student();
                    data.setId(rs.getString("id"));
                    data.setName(rs.getString("name"));
                    data.setSex(rs.getString("sex"));
                    data.setBirth(rs.getString("birth"));
                list.add(data);
            }
            rs.close();
            pstmt.close();
            conn.close();
        }catch(SQLException e) 
        {
            e.printStackTrace();
        }
        return list;
    }
    
    public void delete(String id) throws ClassNotFoundException, SQLException
    {         
         Connection connection = DBUtil.getConnection();
         String sql ="delete from student where id=?";
         PreparedStatement ps = connection.prepareStatement(sql);
         ps.setString(1, id);
         ps.executeUpdate();
                 
    }
    
     public void update(Student A,String id) throws ClassNotFoundException, SQLException 
     {   
            Connection connection = DBUtil.getConnection();
            String sql = "update student set id=?,name=?,sex=?,birth=? where id=?";
            PreparedStatement preparedStatement = null;
            try {
                 preparedStatement = connection.prepareStatement(sql);
                 preparedStatement.setString(1, A.getId());
                 preparedStatement.setString(2, A.getName());
                 preparedStatement.setString(3, A.getSex());
                 preparedStatement.setString(4, A.getBirth());
                 preparedStatement.setString(5, id);
                 preparedStatement.executeUpdate();
                 } catch (SQLException e) {
                   e.printStackTrace();
                }finally {

                }
      }
    
     public void selectID(List<Student> a,String id) throws ClassNotFoundException, SQLException{
             String sql="select * from student where id = ?";
            
             Connection connection = DBUtil.getConnection();
                 try {
                    PreparedStatement ps = connection.prepareStatement(sql);
                      ps.setString(1, id);
                     ResultSet rs =  ps.executeQuery();  
                    
                     
                 while(rs.next())
                 {
                     Student data = new Student();
                     data.setId(rs.getString(1));
                     data.setName(rs.getString(2));
                     data.setSex(rs.getString(3));
                     data.setBirth(rs.getString(4));
                     a.add(data);
                 }
                 
                 } catch (SQLException e) 
                 {
                        e.printStackTrace();
                    }
          
      }
}
8)Dao包StuServlet
package Dao;

import java.io.IOException;
import java.sql.SQLException;
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 Bean.Student;

/**
 * Servlet implementation class StuServlet
 */
@WebServlet("/StuServlet")
public class StuServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public StuServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html; charset=UTF-8");
        
        String id = request.getParameter("id");
        String name = request.getParameter("name");
        String sex = request.getParameter("sex");
        String birth = request.getParameter("birth");
        String method= request.getParameter("method");
        String id0 = request.getParameter("id0");
        ArrayList <Student> a= new ArrayList<Student>();
        
        stuDao dao = new stuDao();
        
        if(method.equals("add"))
        {
        Student data = new Student();
        data.setId(id);
        data.setName(name);
        data.setSex(sex);
        data.setBirth(birth);
        int i=0;
        try {
            i = dao.add(data);
        } catch (ClassNotFoundException | SQLException e)
        {
            e.printStackTrace();
        }    
        if(i==0)    
            response.getWriter().append("添加失败");
        else if(i>0)
            response.getWriter().append("添加成功");
        }
        
        if(method.equals("show"))
        {        
            try{              
                java.util.List<Student> list=dao.Show();            
                request.setAttribute("list", list);
                request.getRequestDispatcher("show.jsp").forward(request, response);
            }catch(Exception e){
                response.getWriter().append("未能正确显示出学生信息");
                e.printStackTrace();
            }
        }
        if(method.equals("update")) {
            try {
            Student B = new Student();
                B.setId(id);
                B.setName(name);
                B.setSex(sex);
                B.setBirth(birth);
                dao.update(B,id0);
                 
            } catch (ClassNotFoundException | SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(method.equals("delete"))
        {
              if(id==null){
                    response.getWriter().append("不能输入为空");
                }else{
                    
                    try {
                        dao.delete(id);
                        response.getWriter().append("删除成功!");
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
        }
        }
        if(method.equals("selectID"))
        {
            if(id==null)
            {
                response.getWriter().append("输入内容为空");
            }else
            {
                try {
                    dao.selectID(a,id);
                    request.setAttribute("a", a);
                    request.getRequestDispatcher("showID.jsp").forward(request, response);
                 
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                request.setAttribute("student",a); 
                System.out.println("haha");
           }
        }
    
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}
9)DBUtil数据库链接
package DBUtil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {
    public static Connection getConnection() throws ClassNotFoundException, SQLException {    
        Connection connection = null;
        Statement stmt = null;
        ResultSet rs = null;
     Class.forName("com.mysql.cj.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/web", "root", "qaz9583");   
 return connection;
 }
 public static void close(Connection connection ) {
      try {
        if (connection != null) {
             connection.close();
          }
        
     } catch (SQLException e) {
         e.printStackTrace();
    }
  }
  public static void close(PreparedStatement preparedStatement ) {
      try {
         if (preparedStatement != null) {
              preparedStatement.close();
          }            
     } catch (SQLException e) {
          e.printStackTrace();
      }
  }
  public static void close(ResultSet resultSet ) {
     try {
         if (resultSet != null) {
              resultSet.close();
          }
          
      } catch (SQLException e) {
          e.printStackTrace();
      }
  }
}
10)主界面
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学生管理系统</title>
<style type="text/css">

         .title{
            width: 100%;
            height:60px;

        }
        li{
            list-style: none;
            padding: 10px;
            margin:0;
            float:left;

        }
        .ltitle{
            width: 100%;
            height: 50px;
            background: #b9c9fe;
            color: #669;
            font-size: 20px;
            text-align:center;
            letter-spacing:3px;
        }
        iframe{
            width:100%;
            height:500px;
            border: medium none;
            border-radius:10px;
            
        }
        a{
            color:#669;
            text-decoration:none;
            
        }
        a:visited{
            color:#669;
        }
        a:hover{
            font-size:20px;
        }
        .xia{
            width:100%;
            height:auto;
            margin-top:15px;
            border-radius:10px;
            float:left;
            background:#e8edff;
        }
        h1{
            text-align:center;
            color:#669;
        }

</style>
</head>
<body>
<div class="title">
    <h1>学生管理系统</h1>
</div>
<div class="ltitle">
    <li><a href="StuServlet?method=show" target="myframe">查看全部信息</a></li>
    <li><a href="add.jsp" target="myframe">学生信息添加</a></li>
    <li><a href="delete.jsp" target="myframe">学生信息删除</a></li>
    <li><a href="update.jsp" target="myframe">学生信息修改</a></li>
    <li><a href="select.jsp" target="myframe">学生信息查询</a></li>
</div>

<div class="xia">
<iframe name="myframe"></iframe>
</div>

 
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加论文</title>
<style type="text/css">

table
{
    font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
    font-size: 16px;
    align:center;
    margin:0 auto;
    width: 500px;
    text-align: left;
    border-collapse: collapse;
}
th
{
    font-size: 13px;
    font-weight: normal;
    padding: 8px;
    background: #b9c9fe;
    border-top: 4px solid #aabcfe;
    border-bottom: 1px solid #fff;
    color: #039;
}
td
{
    padding: 8px;
    background: #e8edff; 
    border-bottom: 1px solid #fff;
    color: #669;
    border-top: 1px solid transparent;
}

input,submit{
    background: #d0dafd;
    color: #339;
    border: 0;
}
</style>
<script>
</script>
</head>
<body>
<%
         Object message = request.getAttribute("message");
         if(message!=null && !"".equals(message)){
     
    %>
         <script type="text/javascript">
              alert("<%=request.getAttribute("message")%>");
         </script>
    <%} %>
    <div class="leftMenu">
  <table >
  <form action="StuServlet?method=add"  method="post"  >
    <tr>
    <td colspan="2" align="center">添加学生信息</td>
  </tr>
   <tr>
   <td>id：</td>
   <td><input type="text" name="id" id="id"></td>
   </tr>
   <tr>
   <td>name：</td>
   <td><input type="text" name="name" id="name"></td>
   </tr>
   <tr>
   <td>sex：</td>
   <td><input type="text" name="sex" id="sex"></td>
   </tr>
   <tr>
   <td>birrh：</td>
   <td><input type="text" name="birth" id="birth"></td>
   </tr>

    <tr> 
    <tr align="center">
    <th colspan="2">
    <input type="submit" value="提交">
    </th>
    </tr>
    </form>
</table>
</div>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>删除页面</title>
<style type="text/css">

table
{
    font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
    font-size: 16px;
    align:center;
    margin:0 auto;
    width: 500px;
    text-align: left;
    border-collapse: collapse;
}
th
{
    font-size: 13px;
    font-weight: normal;
    padding: 8px;
    background: #b9c9fe;
    border-top: 4px solid #aabcfe;
    border-bottom: 1px solid #fff;
    color: #039;
}
td
{
    padding: 8px;
    background: #e8edff; 
    border-bottom: 1px solid #fff;
    color: #669;
    border-top: 1px solid transparent;
}
input,submit{
    background: #d0dafd;
    color: #339;
    border: 0;
}
</style>
</head>
<body>
<%
         Object message = request.getAttribute("message");
         if(message!=null && !"".equals(message)){
     
    %>
         <script type="text/javascript">
              alert("<%=request.getAttribute("message")%>");
         </script>
    <%} %>
<div>
        <form action="StuServlet?method=delete" method="post">
            <table >
                <tr>
                    <td>请输入要删除的学生学号:</td>
                    <td>
                        <input type="text" name="id">
                    </td>
                </tr>
                <tr align="center">
                    <td><input type="submit" value="确定"></td>
                    <td><input type="submit" value="返回" formaction="select.jsp" formmethod="post"></td>
                </tr>
            </table>
        </form>
        </div>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="Bean.*"  %>
<%@page import= "java.util.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta charset="UTF-8">
<title>显示学生信息</title>

<style type="text/css">
table
{
    font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
    font-size: 16px;
    align:center;
    margin:0 auto;
    width: 500px;
    text-align: left;
    border-collapse: collapse;
}
th
{
    font-size: 13px;
    font-weight: normal;
    padding: 8px;
    background: #b9c9fe;
    border-top: 4px solid #aabcfe;
    border-bottom: 1px solid #fff;
    color: #039;
}
td
{
    padding: 8px;
    background: #e8edff; 
    border-bottom: 1px solid #fff;
    color: #669;
    border-top: 1px solid transparent;
}
</style>
</head>
<body>
 <div class="main">

 <%
                      List<Student> list=(List<Student>)request.getAttribute("list");    
                      if(list==null||list.size()<1){
                          response.setHeader("refresh",
                                "5;URL=index.jsp");
                      %>
                      <font color="red" size="5"> 查询信息时出现错误，未能读出个人的信息<br> 五秒后将跳转到主页面 <br>
                如果没有跳转,请点击 <a href="index.jsp">这里</a>!!! <br>
            </font>
                      <% }
                      else{%>
                          <table border="0" cellspacing="10" cellpadding="0">
                        <tr>
                        <td align="center" colspan="5">
                        <h1>个人信息</h1>
                        </td>
                        </tr>
                        <tr align="center">
                                <td><b>ID</b></td>
                                <td><b>姓名</b></td>
                                <td><b>电话</b></td>
                                <td><b>生日</b></td>
                            </tr>
                       <%
                          for(Student data:list){                         
                  %>
                          <tr align="center">
                          <td><%=data.getId() %></td>
                          <td><%=data.getName() %></td>
                          <td><%=data.getSex() %></td>
                          <td><%=data.getBirth() %></td>
                  </tr>                  
                  <%}                      
                  %>
                 <tr align="center">
                  <font color="red" size="5"> <br><br>
                  请点击 <a href="index.jsp">这里回到上一个页面</a>!<br></font>
                 </tr>
                 <%} %>
    </form>
    </div>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>修改学生信息</title>
<style type="text/css">

table
{
    font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
    font-size: 16px;
    align:center;
    margin:0 auto;
    width: 500px;
    text-align: left;
    border-collapse: collapse;
}
th
{
    font-size: 13px;
    font-weight: normal;
    padding: 8px;
    background: #b9c9fe;
    border-top: 4px solid #aabcfe;
    border-bottom: 1px solid #fff;
    color: #039;
}
td
{
    padding: 8px;
    background: #e8edff; 
    border-bottom: 1px solid #fff;
    color: #669;
    border-top: 1px solid transparent;
}

input,submit{
    background: #d0dafd;
    color: #339;
    border: 0;
}

</style>
<script>
</script>
</head>
<body>
<%
         Object message = request.getAttribute("message");
         if(message!=null && !"".equals(message)){
     
    %>
         <script type="text/javascript">
              alert("<%=request.getAttribute("message")%>");
         </script>
    <%} %>
    <div class="leftMenu">
  <table>
  <form action="StuServlet?method=update"  method="post"  onsubmit="return check()">
  
    <tr>
    <td colspan="2" align="center">修改学生信息</td>
  </tr>
  <td>需要修改的学生id为：</td>
   <td><input type="text" name="id0" id="id"></td>
   </tr>
   <td>修改后:</td>
   </tr>
   <tr>
   <td>id：</td>
   <td><input type="text" name="id" id="id"></td>
   </tr>
   <tr>
   <td>name：</td>
   <td><input type="text" name="name" id="name"></td>
   </tr>

   <tr>
   <td>sex：</td>
   <td><input type="text" name="sex" id="sex"></td>
   </tr>
   <tr>
   <td>birth：</td>
   <td><input type="text" name="birth" id="birth"></td>
   </tr>
    <tr align="center">
    <th colspan="2">
    <input type="submit" value="提交">
    </th>
    </tr>
    </form>
</table>
</div>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
<style type="text/css">
table
{
    font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
    font-size: 16px;
    align:center;
    margin:0 auto;
    width: 500px;
    text-align: left;
    border-collapse: collapse;
}
th
{
    font-size: 13px;
    font-weight: normal;
    padding: 8px;
    background: #b9c9fe;
    border-top: 4px solid #aabcfe;
    border-bottom: 1px solid #fff;
    color: #039;
}
td
{
    padding: 8px;
    background: #e8edff; 
    border-bottom: 1px solid #fff;
    color: #669;
    border-top: 1px solid transparent;
}

input,submit{
    background: #d0dafd;
    color: #339;
    border: 0;
}
</style>
</head>
<body>
<div class="leftMenu">
    <table>
  <tr>
   <td width="200px">
   <form action="StuServlet?method=selectID" method="post">
    <p>按学生学号查询：</p>   
    <p>请输入：<input type="text" name="id"/></p>
    <p><input type="submit" value="查询" ></p>
   </form>

   </td>
                 
  </tr>
 </table>
</div>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="Bean.*"  %>
<%@page import= "java.util.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<meta charset="UTF-8">
<title>显示页面</title>

<style type="text/css">
table
{
    font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
    font-size: 16px;
    align:center;
    margin:0 auto;
    width: 500px;
    text-align: left;
    border-collapse: collapse;
}
th
{
    font-size: 13px;
    font-weight: normal;
    padding: 8px;
    background: #b9c9fe;
    border-top: 4px solid #aabcfe;
    border-bottom: 1px solid #fff;
    color: #039;
}
td
{
    padding: 8px;
    background: #e8edff; 
    border-bottom: 1px solid #fff;
    color: #669;
    border-top: 1px solid transparent;
}
</style>
</head>
<body>
 <div class="main">
                      <%
                      List<Student> list=(List<Student>)request.getAttribute("list");    
                      if(list==null||list.size()<1){
                          response.setHeader("refresh",
                                "5;URL=index.jsp");
                      %>
                      <% }
                      else{%>
                          <table>
                        <tr>
                        <td align="center" colspan="5">
                        <h1>个人信息</h1>
                        </td>
                        </tr>
                        <tr align="center">
                                <td><b>学号</b></td>
                                <td><b>姓名</b></td>
                                <td><b>电话</b></td>
                                <td><b>生日</b></td>
                            </tr>
                       <%
                          for(Student data:list){                         
                  %>
                          <tr align="center">
                          <td><%=data.getId() %></td>
                          <td><%=data.getName() %></td>
                          <td><%=data.getSex() %></td>
                           <td><%=data.getBirth() %></td>
                  </tr>                  
                  <%}                      
                  %>
                 <tr align="center">
                  <font color="red" size="5"> <br><br>
                  请点击 <a href="index.jsp">这里回到上一个页面</a>!<br></font>
                 </tr>
                 <%} %>
    </form>
    </div>
</body>
</html>

posted @ 2022-05-04 23:43 _Pi 阅读(439) 评论(0) 收藏举报

刷新页面返回顶部

5/4

公告