程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Web前端開發：SQL Jsp小項目（一），websql

Web前端開發：SQL Jsp小項目（一），websql

編輯：Oracle教程

Web前端開發：SQL Jsp小項目（一），websql

　　Jsp的學習算是告一段落，針對這段時間的學習，寫了一個Jsp小項目來鞏固學到的知識。

　　框架示意圖

User list process

UserAdd process

　　需要的界面效果：

　　需要工具：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" ></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();
                }
            }
        }
}

需要配置xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" 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" >
                    <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" >
                            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界面，這就算初步完成了。

效果圖：

創建一個用戶之後查看當前所有用戶：