你的位置:首页 > 数据库

[数据库]Web前端开发:SQL Jsp小项目(一)


  Jsp的学习算是告一段落,针对这段时间的学习,写了一个Jsp小项目来巩固学到的知识。

 

  需要的界面效果:

 

 

  需要工具:Eclipse、TomCat v8.0和PL/SQL

  先用PL/SQL创建一个用户c##cms,并创建一个cms_user表单

  

  首先,需要写一个JSP——add界面

<%@ page language="java" contentType="text/html; charset=utf-8"  pageEncoding="utf-8"%><%  String path = request.getContextPath();  String basePath = request.getScheme() + "://"      + request.getServerName() + ":" + request.getServerPort()      + path + "/";%><html><head><base href="<%=basePath%>" /><title>用户添加</title><link rel="stylesheet" type="text/css" title="xp"  href="css/skins/xp/validator/component.css" /><link rel="stylesheet" type="text/css" title="xp"  href="css/skins/xp/navbar/nav.css" /><link rel="stylesheet" type="text/css" title="xp"  href="css/skins/xp/table/skin.css" /><link rel="stylesheet" type="text/css" title="xp"  href="css/skins/xp/time/skin.css" /><script type="text/javascript" src="jscript/time/calendar.js"></script><script type="text/javascript" src="jscript/time/calendar-zh.js"></script><script type="text/javascript" src="jscript/time/calendar-setup.js"></script><script type="text/javascript" src="jscript/common.js"></script><script type="text/javascript" src="jscript/validator/form_validator.js" /></script><style type="text/css">body, table, td, select, textarea, input {  font-family: Verdana, Arial, Helvetica, sans-serif;  font-size: 11px;}</style></head><body>  <div id="main">    <form name="backuserform" method="post" action="admin/UserAddServlet"      onSubmit='return submitForm(document.forms[0]);'>      <table class="standard">        <thead>          <tr>            <th align="center" colspan="2">用户添加</th>          </tr>        </thead>        <tbody>          <tr>            <td align="left">用户名</td>            <td align="left"><input name="name" type="text" TABINDEX="1"              id="name" />              <div class="Info">                <div id="name_info"></div>              </div></td>          </tr>          <tr>            <td align="left">用户密码</td>            <td align="left"><input name="password" type="password"              value="" TABINDEX="2" id="password" />              <div class="Info">                <div id="password_info"></div>              </div></td>          </tr>          <tr>            <td align="left">角色</td>            <td align="left"><select name="role" TABINDEX="4" id="role">                <option value="1">超级管理员</option>            </select>              <div class="Info">                <div id="role_info"></div>              </div></td>          </tr>          <tr>            <td align="left">是否有效</td>            <td align="left"><select name="valid" TABINDEX="3" id="valid">                <option value="1">有效</option>                <option value="0">无效</option>            </select>              <div class="Info">                <div id="valid_info"></div>              </div></td>          </tr>          <tr>            <td align="left">EMAIL</td>            <td align="left"><input name="email" type="text" value=""              TABINDEX="5" id="email" />              <div class="Info">                <div id="email_info"></div>              </div></td>          </tr>          <tr>            <td align="left">电话</td>            <td align="left"><input name="phone" type="text" value=""              TABINDEX="6" id="phone" />              <div class="Info">                <div id="phone_info"></div>              </div></td>          </tr>          <tr>            <td colspan="2" align="center"><input class="submitButton"              type="submit" TABINDEX="7" name="submit" value="提&nbsp;交">              <input type="button" name="返回" class="submitButton" value="返回"              onclick="history.back();"></td>          </tr>        </tbody>        <tfoot>          <tr>            <td colspan="2" style="text-align: left"></td>          </tr>        </tfoot>      </table>    </form>  </div></body></html>

本页面接收的数据打包封装并发送到UserServlet

package com.jaovo.jcms.user;import java.io.IOException;import java.sql.Timestamp;import java.util.Date;import javax.servlet.ServletException;import javax.servlet.ServletRequest;import javax.servlet.ServletResponse;import javax.servlet.http.HttpServlet;import com.jaovo.jcms.service.UserService;public class UserAddServlet extends HttpServlet{  public UserAddServlet() {    super();  }    @Override  public void service(ServletRequest request, ServletResponse response)      throws ServletException, IOException {    //获取 客户端(浏览器)提交的数据    String name = request.getParameter("name");    String password = request.getParameter("password");    String valid = request.getParameter("valid");    String email = request.getParameter("email");    String phone = request.getParameter("phone");        //把数据封装到User对象里面去    User user = new User();    user.setName(name);    user.setEmail(email);    user.setPassword(password);    user.setPhone(phone);    user.setTime_stamp(new Timestamp(new Date().getTime()));//获取系统当前时间,当做注册时间        //这里要进行插入数据库    UserService us = new UserService();    us.addUser(user);        this.getServletContext()      .getRequestDispatcher("/admin/user_list.jsp")      .forward(request, response);  }}

UserServlet接收到之后,还需要一个实体类,来封装数据,创建User实体类:

package com.jaovo.jcms.user;import java.util.Date;public class User {  private String name;  private String password;  private int valid;  private String email;  private String phone;  private Date time_stamp;  private int id;  //private String role;      public String getName() {    return name;  }  public void setName(String name) {    this.name = name;  }  public String getPassword() {    return password;  }  public void setPassword(String password) {    this.password = password;  }  public int getValid() {    return valid;  }  public void setValid(int valid) {    this.valid = valid;  }  public String getEmail() {    return email;  }  public void setEmail(String email) {    this.email = email;  }  public String getPhone() {    return phone;  }  public void setPhone(String phone) {    this.phone = phone;  }  public Date getTime_stamp() {    return time_stamp;  }  public void setTime_stamp(Date time_stamp) {    this.time_stamp = time_stamp;  }  public int getId() {    return id;  }  public void setId(int id) {    this.id = id;  }  public User() {    super();    // TODO Auto-generated constructor stub  }  public User(String name, String password, int valid, String email,      String phone, Date time_stamp, int id) {    super();    this.name = name;    this.password = password;    this.valid = valid;    this.email = email;    this.phone = phone;    this.time_stamp = time_stamp;    this.id = id;  }  @Override  public String toString() {    return "User [name=" + name + ", password=" + password + ", valid="        + valid + ", email=" + email + ", phone=" + phone        + ", time_stamp=" + time_stamp + ", id=" + id + "]";  }}

调用UserService方法写入数据库(注: 真正连接数据库的方法另外写在一个工具类中,以方便后面优化):

package com.jaovo.jcms.service;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Timestamp;import java.util.ArrayList;import java.util.Date;import com.jaovo.jcms.user.User;import com.jaovo.jcms.util.DB;//服务:是为了往数据库中操作(添加)来创建一个层public class UserService {  //---------------用户添加的方法  public void addUser(User user) {    Connection conn = DB.getConnection();//-------已经把事务提交给关闭了    PreparedStatement pstmt = null;    String sql = "insert into cms_user(name,password,valid,email,phone,time_stamp) values(?,?,?,?,?,?)";    try {      pstmt = conn.prepareStatement(sql);      pstmt.setString(1, user.getName());      pstmt.setString(2, user.getPassword());      pstmt.setInt(3, user.getValid());      pstmt.setString(4, user.getEmail());      pstmt.setString(5, user.getPhone());      pstmt.setTimestamp(6, new Timestamp(new Date().getTime()));      //执行到数据库      pstmt.executeUpdate();      DB.commit(conn);    } catch (SQLException e) {      DB.rollback(conn);//回滚 其实一条语句失败了不需要回滚      e.printStackTrace();    }finally{      DB.close(pstmt);      DB.close(conn);    }  }    //用户的查询方法  public ArrayList getUser(String name){    String sql = "select name,password,valid,time_stamp,email,phone,id from cms_user where name like ?";    Connection conn = DB.getConnection();    PreparedStatement pstmt = null;    ResultSet rs = null;        ArrayList<User> list = new ArrayList<User>();        try {      pstmt = conn.prepareStatement(sql);      pstmt.setString(1, "%"+name+"%");      rs = pstmt.executeQuery();            while (rs.next()) {        User user = new User();        user.setName(rs.getString(1));        user.setPassword(rs.getString(2));        user.setValid(rs.getInt(3));        user.setTime_stamp(rs.getTimestamp(4));        user.setEmail(rs.getString(5));        user.setPhone(rs.getString(6));        user.setId(rs.getInt(7));//------返回数据库,查看序列                list.add(user);      }    } catch (SQLException e) {      DB.rollback(conn);      e.printStackTrace();    }finally{      DB.close(rs);      DB.close(pstmt);      DB.commit(conn);      DB.close(conn);    }    return list;  }}

真正连接数据库的工具类,DB类:

package com.jaovo.jcms.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;//DB.java 是一个Jdbc链接类/* * 只是一个工具类 */public class DB {  //用来获取数据库链接  public static Connection getConnection() {    Connection conn = null;      try {      Class.forName("oracle.jdbc.driver.OracleDriver");      conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","c##cms","cms");      //-------------------      //事务提交为false      conn.setAutoCommit(false);    } catch (SQLException | ClassNotFoundException e) {      e.printStackTrace();    }    return conn;  }    //关闭语句对象  public static void close(PreparedStatement pstmt){    try {      if (pstmt != null) {        pstmt.close();      }    } catch (SQLException e) {      System.out.println("关闭异常-语句对象DB---------");      e.printStackTrace();    }  }  //关闭返回集  public static void close(ResultSet rs) {    try {      if (rs != null) {        rs.close();      }    } catch (SQLException e) {      System.out.println("关闭异常-返回集DB-----------");      e.printStackTrace();    }  }    //关闭链接    public static void close(Connection conn) {      try {        if (conn != null) {          conn.close();        }      } catch (SQLException e) {        System.out.println("关闭异常-资源DB-----------");        e.printStackTrace();      }    }        //事务回滚的方法    public static void rollback(Connection conn) {      try {        conn.rollback();      } catch (SQLException e) {        e.printStackTrace();      }    }        //设置手动提交方法    public static void commit(Connection conn) {      if (conn != null) {        try {          conn.commit();        } catch (SQLException e) {          e.printStackTrace();        }      }    }}

需要配置

<?"1.0" encoding="UTF-8"?><web-app "http://www.w3.org/2001/" "http://java.sun.com/" xsi:schemaLocation="http://java.sun.com/" id="WebApp_ID" version="3.0"> <display-name>JCMS</display-name> <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> <servlet>  <servlet-name>userAddServlet</servlet-name>  <servlet-class>com.jaovo.jcms.user.UserAddServlet</servlet-class> </servlet> <servlet-mapping>  <servlet-name>userAddServlet</servlet-name>  <url-pattern>/admin/userAddServlet</url-pattern> </servlet-mapping>   <servlet>  <servlet-name>userListServlet</servlet-name>  <servlet-class>com.jaovo.jcms.user.UserListServlet</servlet-class> </servlet> <servlet-mapping>  <servlet-name>userListServlet</servlet-name>  <url-pattern>/admin/userListServlet</url-pattern> </servlet-mapping></web-app>

 

回到正题,在上面UserServlet里面的,数据封装之后返回user_list.jsp,这个界面负责遍历显示数据库中的用户并提供更改,删除功能(暂未实现,下个版本实现)

<%@page import="java.util.ArrayList"%><%@ page language="java" contentType="text/html; charset=utf-8"  pageEncoding="utf-8"%><%@ page import="java.util.*" %><%@ page import="com.jaovo.jcms.user.User" %><%  String path = request.getContextPath();  String basePath = request.getScheme() + "://"      + request.getServerName() + ":" + request.getServerPort()      + path + "/";%><html><base href="<%=basePath%>" />  <head>    <title>backuser</title>    <style type="text/css">body,table,td,select,textarea,input {  font-family: Verdana, Arial, Helvetica, sans-serif;  font-size: 11px;}</style>    <link rel="stylesheet" type="text/css" title="xp"      href="css/skins/xp/validator/component.css" />    <link rel="stylesheet" type="text/css" title="xp"      href="css/skins/xp/navbar/nav.css" />    <link rel="stylesheet" type="text/css" title="xp"      href="css/skins/xp/table/skin.css" />    <link rel="stylesheet" type="text/css" title="xp"      href="css/skins/xp/time/skin.css" />    <script type="text/javascript">        function turn(frm,oper,totalpage,curpage,msg){        if(oper=='first'){       if(curpage==1){      return;    }    frm.pagenum.value = 1;    frm.submit();    return;  }else if(oper=='prev'){    if(curpage==1){      return;    }    frm.pagenum.value = (curpage-1);    frm.submit();    return;  }else if(oper=='next'){    if(curpage>=totalpage){      return;    }    frm.pagenum.value = (curpage+1);    frm.submit();    return;  }else if(oper=='last'){    if(curpage>=totalpage){      return;    }    frm.pagenum.value = totalpage;    frm.submit();    return;  }else if(oper=='jump'){    var jpage = document.getElementById("jumpto");    var jpagev = curpage;    if(jpage.value==""||!(jpage.value.search(/^(-|\+)?\d+$/) != -1)){      alert(msg);      jpage.focus();      jpage.select();      return;    }else{      jpagev = parseInt(jpage.value);    }    if(jpagev==curpage||jpagev>totalpage||jpagev<=0){      return;    }    frm.pagenum.value = jpagev;    frm.submit();    return;  }}        </script>  </head>  <%    ArrayList userList = (ArrayList)request.getAttribute("userList");    if(userList == null){      userList = new ArrayList();    }  %>  <body>    <div id="main">      <form name="sportform" method="post"        action="admin/userListServlet">        <table class="sadminheading" style="top-margin: 10">          <tr>            <td nowrap class="admintitle" colspan="3" align="center">              用户列表            </td>          </tr>          <tr>            <td align="left" width="10%">              用户名:            </td>            <td align="left" width="40%">              <input name="name" type="text" />            </td>            <td align="right">              <input type="submit" name="提交" value="提交"/>&nbsp;&nbsp;&nbsp;              <input type="hidden" name="pagenum" value="" />              <input type="hidden" name="pagerows" value="" />            </td>          </tr>        </table>      </form>      <table class="standard">        <thead>          <tr>            <th>              id            </th>            <th>              用户名            </th>            <th>              用户密码            </th>            <th>              角色            </th>            <th>              email            </th>            <th>              是否有效            </th>            <th>              &nbsp;            </th>          </tr>        </thead>        <tbody>        <!-- 循环遍历出每个用户的信息 -->        <%          for(Iterator i = userList.iterator();i.hasNext();){            User user = (User)i.next();        %>          <tr>            <td>              <%=user.getId() %>            </td>            <td>              <%=user.getName() %>            </td>            <td>              <%=user.getPassword() %>            </td>            <td>              --Null--            </td>            <td>              <%=user.getEmail() %>            </td>            <td>              <%=user.getValid() %>            </td>            <td>              <a href='#' onclick="location.href='user_update.html';">修改</a>              <a href='#'                onclick="if(confirm('delete')) location.href='user_list.html?id=1';">删除</a>            </td>          </tr>          <%          }          %>          <!-- 循环结束 -->          <tr>            <td colspan="7">              No data found            </td>          </tr>        </tbody>        <tfoot>          <tr>            <td colspan="3" style="text-align: left">              1/1 total rows 1            </td>            <td colspan="4" align="right">              <a href="#"                onclick="turn(document.forms[0],'first',5,1,'jump page');">first</a>              <a href="#"                onclick="turn(document.forms[0],'prev', 5,1,'jump page');">prev</a>              <a href="#"                onclick="turn(document.forms[0],'next',5,1,'jump page');">next</a>              <a href="#"                onclick="turn(document.forms[0],'last',5,1,'jump page');">last</a>              go              <input type="text" name="cpage" size="5" id="jumpto" />              <a href="#"                onclick="turn(document.forms[0],'jump',5,1,'jump page');">go</a>            </td>          </tr>        </tfoot>      </table>    </div>  </body></html>

真正遍历显示数据在userListServlet:

package com.jaovo.jcms.user;import java.io.IOException;import java.util.ArrayList;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.jaovo.jcms.service.UserService;public class UserListServlet extends HttpServlet{  @Override  protected void service(HttpServletRequest request, HttpServletResponse response)      throws ServletException, IOException {    String name = request.getParameter("name");    UserService us = new UserService();    //---------调用它里面的查询方法-----------    ArrayList<User> userList = us.getUser(name);    //---------设置到request对象里面去    request.setAttribute("userList", userList);    this.getServletContext().getRequestDispatcher("/admin/user_list.jsp").forward(request, response);//-------------跳转回去  }}

遍历出数据,携带数据跳转回到list界面,这就算初步完成了。

效果图:

创建一个用户之后查看当前所有用户:

做一个勤劳的码农