NetBeans课设，实现网页操作数据库，增删查改

---

前言

最近帮朋友做的一个NetBeans课设，java web项目，对寝室信息进行管理，mysql数据库，查询所有寝室信息，删除信息，按宿舍号查询，按楼号查询，添加寝室信息。
用的是NetBeans8.2版本，mysql数据库是5.7.4。
创作不易，希望多给些建议。

---

一、NetBeans是什么？

NetBeans包括开源的开发环境和应用平台，NetBeans IDE可以使开发人员利用Java平台能够快速创建Web、企业、桌面以及移动的应用程序，NetBeans IDE已经支持PHP、Ruby、JavaScript、Groovy、Grails和C/C++等开发语言。

二、成员变量

字段	类型	字段说明
id	int	主键，自增长
dor_id	Varchar(10)	寝室号
depart	Varchar(10)	楼号
dor num	int	应住人数
dor fact	int	实际人数
privace	Double	寝室费用
dor notes	varchar	备注

三、详细设计

1、创建本地数据库并添加字段

数据库的名字是bigdata21，表名字是student，一共只有一张表
id设置为主键自增

2、新建工程

1. 选择java web项目
   
2. 选择位置
   
3. 服务器和设置
   
4. 选择框架
   

3、导入mysql库jar包

右键添加库，选择你的jar包即可

4、工具类代码分析

1. Student.java
   构造函数以及set，get都是用IDEA自动生成的，超级好用！！

public class Student {
    private int id;//主键
    private String dor_id;//寝室号
    private String depart;//楼号
    private int dor_num;//应住人数
    private int dor_fact;//实际人数
    private double privace;//寝室费用
    private String dor_notes;//备注
    
    public Student(int id, String dor_id, String depart, int dor_num, int dor_fact, double privace, String dor_notes) {
        this.id = id;
        this.dor_id = dor_id;
        this.depart = depart;
        this.dor_num = dor_num;
        this.dor_fact = dor_fact;
        this.privace = privace;
        this.dor_notes = dor_notes;
    }
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getDor_id() {
        return dor_id;
    }
    public void setDor_id(String dor_id) {
        this.dor_id = dor_id;
    }
    public String getDepart() {
        return depart;
    }
    public void setDepart(String depart) {
        this.depart = depart;
    }
    public int getDor_num() {
        return dor_num;
    }
    public void setDor_num(int dor_num) {
        this.dor_num = dor_num;
    }
    public int getDor_fact() {
        return dor_fact;
    }
    public void setDor_fact(int dor_fact) {
        this.dor_fact = dor_fact;
    }
    public double getPrivace() {
        return privace;
    }
    public void setPrivace(double privace) {
        this.privace = privace;
    }
    public String getDor_notes() {
        return dor_notes;
    }
    public void setDor_notes(String dor_notes) {
        this.dor_notes = dor_notes;
    }
    @Override
    public String toString() {
        return "Student{" + "id=" + id + ", dor_id=" + dor_id + ", depart=" + depart + ", dor_num=" + dor_num +
                ", dor_fact=" + dor_fact + ", privace=" + privace + ", dor_notes=" + dor_notes + '}';
    }
}

2. JDBCUtil.java
   对mysql数据库进行连接，关闭连接操作

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author AdminM
 */
public class JDBCUtil {
    
    static {
        try{
            // step1:
            Class.forName("com.mysql.jdbc.Driver");// 指明驱动
        }catch(ClassNotFoundException e){
            System.out.println(e.getMessage());
        }
    }
    
    /***
     * 建立连接
     * @return 
     */
    public static Connection getConn(){
        Connection conn = null;
        // step2:建立连接
        String urlstr = "jdbc:mysql://localhost:3306/bigdata21";
        String username = "root";
        String passwd = "001316";
        try{
            conn = DriverManager.getConnection(urlstr, username, passwd);
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return conn;
    }
    
    /***
     * 关闭连接
     * @param conn
     * @param stat 
     */
    public static void getClose(Connection conn, Statement stat){
        if(conn != null){
            try{
                conn.close();
            }catch(SQLException e){
                System.out.println(e.getMessage());
            }
        }
        if(stat != null){
            try{
                stat.close();
            }catch(SQLException e){
                System.out.println(e.getMessage());
            }
        }
    }
}

3. StudentDaoImpl.java
   mysql工具类，增删查改的实现方法

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import util.JDBCUtil;
import vo.Student;

/**
 *
 * @author AdminM
 */
public class StudentDaoImpl{

    public ArrayList<Student> getStudentByNum(String dor_id) {
        ArrayList<Student> stus = new ArrayList<Student>();
        Connection conn = JDBCUtil.getConn();
        Statement stat = null;
        try{
            stat = conn.createStatement();
            // step4: 建立sql语句，并执行，得到结果
            String sqlstr = "select * from student where dor_id like '"+dor_id+"'";
            ResultSet rs = stat.executeQuery(sqlstr);
            while(rs.next()){
                int Id =  rs.getInt("id");
                String sid = rs.getString("dor_id");
                String sdepart = rs.getString("depart");
                int sdor_num = rs.getInt("dor_num");
                int sdor_fact = rs.getInt("dor_fact");
                double privace = rs.getDouble("privace");
                String  sdor_notes = rs.getString("dor_notes");
                Student temp = new Student(Id,sid,sdepart,sdor_num,sdor_fact,privace,sdor_notes);
                stus.add(temp);
            }
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
        JDBCUtil.getClose(conn, stat);
        return stus;
    }
    //通过寝室号查找
    public ArrayList<Student> getStudentByName(String dor_id) {
        ArrayList<Student> stus = new ArrayList<Student>();
        Connection conn = JDBCUtil.getConn();
        Statement stat = null;
        try{
            stat = conn.createStatement();
            // step4: 建立sql语句，并执行，得到结果
            String sqlstr = "select * from student where dor_id like '"+dor_id+"'";
            ResultSet rs = stat.executeQuery(sqlstr);
            while(rs.next()){
                int Id =  rs.getInt("id");
                String sid = rs.getString("dor_id");
                String sdepart = rs.getString("depart");
                int sdor_num = rs.getInt("dor_num");
                int sdor_fact = rs.getInt("dor_fact");
                double privace = rs.getDouble("privace");
                String  sdor_notes = rs.getString("dor_notes");
                Student temp = new Student(Id,sid,sdepart,sdor_num,sdor_fact,privace,sdor_notes);
                stus.add(temp);
            }
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
        JDBCUtil.getClose(conn, stat);
        return stus;
    }
    //通过楼号查找
    public ArrayList<Student> getStudentByDepart(String depart) {
        ArrayList<Student> stus = new ArrayList<Student>();
        Connection conn = JDBCUtil.getConn();
        Statement stat = null;
        try{
            stat = conn.createStatement();
            // step4: 建立sql语句，并执行，得到结果
            String sqlstr = "select * from student where depart like '"+depart+"'";
            ResultSet rs = stat.executeQuery(sqlstr);
            while(rs.next()){
                int Id =  rs.getInt("id");
                String sid = rs.getString("dor_id");
                String sdepart = rs.getString("depart");
                int sdor_num = rs.getInt("dor_num");
                int sdor_fact = rs.getInt("dor_fact");
                double privace = rs.getDouble("privace");
                String  sdor_notes = rs.getString("dor_notes");
                Student temp = new Student(Id,sid,sdepart,sdor_num,sdor_fact,privace,sdor_notes);
                stus.add(temp);
            }
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
        JDBCUtil.getClose(conn, stat);
        return stus;
    }

    public ArrayList<Student> getAll() {
        ArrayList<Student> stus = new ArrayList<Student>();
        Connection conn = JDBCUtil.getConn();
        Statement stat = null;
        try{
            stat = conn.createStatement();
            // step4: 建立sql语句，并执行，得到结果
            String sqlstr = "select * from student";
            ResultSet rs = stat.executeQuery(sqlstr);
            while(rs.next()){
                int Id =  rs.getInt("id");
                String sid = rs.getString("dor_id");
                String sdepart = rs.getString("depart");
                int sdor_num = rs.getInt("dor_num");
                int sdor_fact = rs.getInt("dor_fact");
                double privace = rs.getDouble("privace");
                String  sdor_notes = rs.getString("dor_notes");
                Student temp = new Student(Id,sid,sdepart,sdor_num,sdor_fact,privace,sdor_notes);
                stus.add(temp);
            }
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
        JDBCUtil.getClose(conn, stat);
        return stus;
    }

    public int insertStudent(Student stu) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement stat = null;
        int res = 0;
        try{
            String sqlstr = "INSERT INTO bigdata21.student\n" +
"(dor_id, depart, dor_num, dor_fact, privace, dor_notes)\n" +
"VALUES(?,?,?,?,?,?);";
            stat = conn.prepareStatement(sqlstr);
            // step4: 建立sql语句，并执行，得到结果
//            stat.setInt(1, stu.getId());
            stat.setString(1, stu.getDor_id());
            stat.setString(2, stu.getDepart());
            stat.setInt(3, stu.getDor_num());
            stat.setInt(4, stu.getDor_fact());
            stat.setDouble(5, stu.getPrivace());
            stat.setString(6, stu.getDor_notes());
            res = stat.executeUpdate();
            // step5: 查看结果
            System.out.println("执行结果："+res);
            System.out.println("查询完毕");
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
        JDBCUtil.getClose(conn, stat);
        return res;
    }
    public int deleteStudent(String dor_id) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement stat = null;
        int res = 0;
        try{
            String sqlstr = "delete from student where dor_id=?";
            stat = conn.prepareStatement(sqlstr);
            // step4: 建立sql语句，并执行，得到结果
            stat.setString(1, dor_id);
            res = stat.executeUpdate();
            // step5: 查看结果
            System.out.println("执行结果："+res);
            System.out.println("查询完毕");
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
        JDBCUtil.getClose(conn, stat);
        return res;
    }
    
    public int updateStudent(Student stu){
        Connection conn = JDBCUtil.getConn();
        PreparedStatement stat = null;
        int res = 0;
        try{
            String sqlstr = "update student set dor_id=?,depart=?,dor_num=?,dor_fact=?,privace=?,dor_notes=? where id=?";
            stat = conn.prepareStatement(sqlstr);
            // step4: 建立sql语句，并执行，得到结果
//            stat.setInt(1, stu.getId());
            stat.setString(1, stu.getDor_id());
            stat.setString(2, stu.getDepart());
            stat.setInt(3, stu.getDor_num());
            stat.setInt(4, stu.getDor_fact());
            stat.setDouble(5, stu.getPrivace());
            stat.setString(6, stu.getDor_notes());
            stat.setInt(7, stu.getId());

            res = stat.executeUpdate();
        
            // step5: 查看结果
            System.out.println("执行结果："+res);
            System.out.println("查询完毕");
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
        JDBCUtil.getClose(conn, stat);
        return res;
    }
}

5、主要代码部分

1. 查询并显示
   这里按楼号查询的input使用了formaction，表示一个表单两种不同的提交方式

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="vo.Student"%>
<%@page import="java.util.ArrayList"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>

        <form action="queryStudentByName.jsp">
        <input type="text" name="temp" placeholder="请输入要搜索的准确内容" required/> <input type="submit" value="按寝室号查询"/> <input type="submit" formaction="queryStudentByDepart.jsp" value="按楼号查询"/> 
        </form>
        
        <br/>
        
        <form action="queryStudent.jsp">
            <input type="submit" value="查询所有">
        </form>
        
        <hr>
        <%
            ArrayList<Student> stus = (ArrayList<Student>)request.getAttribute("allstus");
        %>
        <table border="1">
            <tr>
                <td>寝室号</td>
                <td>楼号</td>
                <td>应住人数</td>
                <td>实际人数</td>
                <td>寝室费用</td>
                <td>备注</td>
                <td>操作1</td>
                <td>操作2</td>
            </tr>
            <%
               if(stus != null) 
                for(Student stu : stus){
            %>
            <tr>
                <td><%=stu.getDor_id() %></td>
                <td><%=stu.getDepart() %></td>
                <td><%=stu.getDor_num() %></td>
                <td><%=stu.getDor_fact() %></td>
                <td><%=stu.getPrivace() %></td>
                <td><%=stu.getDor_notes() %></td>
                <td><a href="stuModInfo.jsp?dor_id=<%=stu.getDor_id()%>">修改</a></td>
                <td><a href="deleteStudent.jsp?dor_id=<%=stu.getDor_id()%>">删除</a></td>
            </tr>
            <%
                }
            %>
        </table>
    </body>
</html>

按寝室号查询 queryStudentByName.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="impl.StudentDaoImpl" %>
<%@page import="vo.Student"%>
<%@page import="java.util.ArrayList"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%
            String dor_id = request.getParameter("temp");
            StudentDaoImpl studao = new StudentDaoImpl();
            ArrayList<Student> stus = studao.getStudentByName(dor_id);
            //ArrayList<Student> stus = studao.getAll();
            request.setAttribute("allstus", stus);
            // 请求转发
            request.getRequestDispatcher("stuinfo.jsp").forward(request, response);
        %>
    </body>
</html>

按楼号查询 queryStudentByDepart.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="impl.StudentDaoImpl" %>
<%@page import="vo.Student"%>
<%@page import="java.util.ArrayList"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%
            String temp = request.getParameter("temp");
            StudentDaoImpl studao = new StudentDaoImpl();
            ArrayList<Student> stus = studao.getStudentByDepart(temp);
            //ArrayList<Student> stus = studao.getAll();
            request.setAttribute("allstus", stus);
            // 请求转发
            request.getRequestDispatcher("stuinfo.jsp").forward(request, response);
        %>
    </body>
</html>

2. 删除 deleteStudent.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="impl.StudentDaoImpl" %>
<%@page import="vo.Student"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%
            String dor_id = request.getParameter("dor_id");
            
            StudentDaoImpl studao = new StudentDaoImpl();
            int rownum = studao.deleteStudent(dor_id);
            if(rownum == 1){
                request.setAttribute("info", "更新成功");
                request.getRequestDispatcher("stuinfo.jsp").forward(request, response);
            }else{
                request.setAttribute("info", "更新失败");
                request.getRequestDispatcher("stuinfo.jsp").forward(request, response);
            }
        %>
    </body>
</html>

3. 修改 stuModInfo.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="impl.StudentDaoImpl" %>
<%@page import="vo.Student"%>
<%@page import="java.util.ArrayList"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>修改学生信息</h1>
        <%
            String info = (String)request.getAttribute("info");
            String dor_id = request.getParameter("dor_id");
            StudentDaoImpl studao = new StudentDaoImpl();
            ArrayList<Student> stus = studao.getStudentByNum(dor_id);
            //request.setAttribute("stu", stus.get(0));
            // 请求转发
            //request.getRequestDispatcher("stuinfo.jsp").forward(request, response);
            Student stu = stus.get(0);
        %>
        <%=dor_id %>
        <%=info%>
        <form action="updateStudent.jsp">
            <table border="1">
                <tr>
                    <td>属性</td>
                    <td>值</td>
                </tr>
                <tr>
                    <td>id</td>
                    <td><input type="text" name="id" value="<%=stu.getId() %>" readonly="true"></td>
                </tr>
                <tr>
                    <td>寝室号</td>
                    <td><input type="text" name="dor_id" value="<%=stu.getDor_id() %>"></td>
                </tr>
                <tr>
                    <td>楼号</td>
                    <td><input type="text" name="depart" value="<%=stu.getDepart() %>"></td>
                </tr>
                <tr>
                    <td>应住人数</td>
                    <td><input type="text" name="dor_num" value="<%=stu.getDor_num() %>"></td>
                </tr>
                <tr>
                    <td>实际人数</td>
                    <td><input type="text" name="dor_fact" value="<%=stu.getDor_fact() %>"></td>
                </tr>
                <tr>
                    <td>寝室费用</td>
                    <td><input type="text" name="privace" value="<%=stu.getPrivace() %>"></td>
                </tr>
                <tr>
                    <td>备注</td>
                    <td><input type="text" name="dor_notes" value="<%=stu.getDor_notes() %>"></td>
                </tr>
                <tr>
                    <td><input type="reset" value="重置"></td>
                    <td><input type="submit" value="修改"></td>
                </tr>
            </table>
        </form>
    </body>
</html>

updateStudent.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="impl.StudentDaoImpl" %>
<%@page import="vo.Student"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%
            int id = Integer.parseInt(request.getParameter("id"));
            String dor_id = request.getParameter("dor_id");
            String depart = request.getParameter("depart");
            int dor_num = Integer.parseInt(request.getParameter("dor_num"));
            int dor_fact = Integer.parseInt(request.getParameter("dor_fact"));
            double privace = Double.parseDouble(request.getParameter("privace"));
            String dor_notes = request.getParameter("dor_notes");
           
            
            Student temp = new Student(id,dor_id,depart,dor_num,dor_fact,privace,dor_notes);
            StudentDaoImpl studao = new StudentDaoImpl();
            int rownum = studao.updateStudent(temp);
            if(rownum == 1){
                request.setAttribute("info", "更新成功");
                request.getRequestDispatcher("stuModInfo.jsp").forward(request, response);
            }else{
                request.setAttribute("info", "更新失败");
                request.getRequestDispatcher("stuModInfo.jsp").forward(request, response);
            }
        %>
    </body>
</html>

4. 添加信息
   id主键不用自己添加，每添加一条数据会自动加1
   stuAddInfo.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="impl.StudentDaoImpl" %>
<%@page import="vo.Student"%>
<%@page import="java.util.ArrayList"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>添加学生信息</h1>
        <%
            String info = (String)request.getAttribute("info");
        %>
        <%=info%>
        <form action="insertStudent.jsp">
            <table border="1">
                <tr>
                    <td>属性</td>
                    <td>值</td>
                </tr>
                <tr>
                    <td>寝室号</td>
                    <td><input type="text" name="dor_id" value=""></td>
                </tr>
                <tr>
                    <td>楼号</td>
                    <td><input type="text" name="depart" value=""></td>
                </tr>
                <tr>
                    <td>应到人数</td>
                    <td><input type="text" name="dor_num" value="0"></td>
                </tr>
                <tr>
                    <td>实到人数</td>
                    <td><input type="text" name="dor_fact" value="0"></td>
                </tr>
                <tr>
                    <td>寝室费用</td>
                    <td><input type="text" name="privace" value="0.0"></td>
                </tr>
                <tr>
                    <td>备注</td>
                    <td><input type="text" name="dor_notes" value=""></td>
                </tr>
                <tr>
                    <td><input type="reset" value="重置"></td>
                    <td><input type="submit" value="添加"></td>
                </tr>
            </table>
        </form>
    </body>
</html>

insertStudent.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="impl.StudentDaoImpl" %>
<%@page import="vo.Student"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%
//            int id = Integer.parseInt(request.getParameter("id"));
            String dor_id = request.getParameter("dor_id");
            String depart = request.getParameter("depart");
            int dor_num = Integer.parseInt(request.getParameter("dor_num"));
            int dor_fact = Integer.parseInt(request.getParameter("dor_fact"));
            double privace = Double.parseDouble(request.getParameter("privace"));
            String dor_notes = request.getParameter("dor_notes");
           
           
            Student temp = new Student(0,dor_id,depart,dor_num,dor_fact,privace,dor_notes);
            StudentDaoImpl studao = new StudentDaoImpl();
            int rownum = studao.insertStudent(temp);
            if(rownum == 1){
                request.setAttribute("info", "添加成功");
                request.getRequestDispatcher("stuAddInfo.jsp").forward(request, response);
            }else{
                request.setAttribute("info", "添加失败");
                request.getRequestDispatcher("stuAddInfo.jsp").forward(request, response);
            }
        %>
    </body>
</html>

四、运行演示

五、项目总结

本次项目使用了mysql语法、html语法、表单提交等知识。相对来说还是很简单的，其实java知识非常多，想要深入学习学通还需要花很长时间。不过我个人来看，程序开发相对于深度学习搞算法来说还是非常容易的，毕竟创造有突破性的算法比创造新的软件要难100倍。

六、源码获取

后续会发到我的资源里面，请大家自行下载。