首先要完成一个java web连接数据库的增删改查，要有前台的.jsp文件，还要有后台的文件，前台的文件负责产生网页，来直接使进行增删改查，而后台的分为util层，这一层主要负责完成与数据库的连接，代码如下。
package util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; //连接数据库 public class DButil { public static String db_url = "jdbc:mysql://localhost:3306/haohaoxuexi"; public static String db_user = "root"; public static String db_pass = "zxh521+."; public static Connection getConn () { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver");//加载驱动 conn = DriverManager.getConnection(db_url, db_user, db_pass); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void main(String[] args) { try { //第三步：获取连接类实例con，用con创建Statement对象类实例 sql_statement Connection con = getConn(); Statement sql_statement = con.createStatement(); String query = "select * from tiantianxiangshang"; ResultSet result = sql_statement.executeQuery(query); //显示数据中network表中的内容： System.out.println("数据库haohaoxuexi表tiantianxiangshang中的数据如下:"); System.out.println("------------------------"); System.out.println("name" + " " + "teacher" + " " + "classroom"); System.out.println("------------------------"); //对获得的查询结果进行处理，对Result类的对象进行操作 while (result.next()) { String name = result.getString("name"); String teacher = result.getString("teacher"); String classroom = result.getString("classroom"); //取得数据库中的数据 System.out.println(" " +name + " " +teacher+ " " + classroom); } } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); } } /** * 关闭连接 * @param state * @param conn */ public static void close (Statement state, Connection conn) { if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close (ResultSet rs, Statement state, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
这个类主要完成数据库的连接，并且把数据库里的内容显示出来。要实现数据库的连接首先要完成驱动，然后完成与conn的连接。如果把数据库比喻成一个小岛，那么数据库的连接就相当于在连接小岛时搭建一座桥，而Connection con = getConn();就是实现搭建桥的过程。
在后台的层中有一层为entity层，这一层建立实体类，里面建立的是数据库中的变量，然后建立set、get方法，并且有无参和有参的构造函数。代码如下。
package entity; public class course { private int id; private String name; private String teacher; private String classroom; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getTeacher() { return teacher; } public void setTeacher(String teacher) { this.teacher = teacher; } public String getClassroom() { return classroom; } public void setClassroom(String classroom) { this.classroom = classroom; } public course() {} public course(int id, String name, String teacher, String classroom) { this.id = id; this.name = name; this.teacher = teacher; this.classroom = classroom; } public course(String name, String teacher, String classroom) { this.name = name; this.teacher = teacher; this.classroom = classroom; } }
建立dao层，dao层是工具层，这一层主要是进行与数据库之间的操作，搭建后台与数据库之间的桥梁。代码如下。
package dao; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import entity.course; import util.DButil; //与数据库建立联系 public class coursedao { public boolean add(course course) {//建立course类的对象 String sql="insert into tiantianxiangshang(name,teacher,classroom)values('"+course.getName()+"','"+course.getTeacher()+"','"+course.getClassroom()+"')"; //添加语句，调用course类里面的方法，完成添加 Connection conn=DButil.getConn();//建立桥 Statement state=null;//建立车 Boolean f=false; int a=0; try { state = conn.createStatement();//桥给车一个可以运输货物的功能 a=state.executeUpdate(sql);//把运输货物的多少赋值给a } catch (Exception e) { e.printStackTrace(); } finally { DButil.close(state, conn);//在运输结束后，毁掉桥和车 } if (a > 0) { f = true; } return f; } public boolean delete (int id) { boolean f = false; String sql = "delete from course where id='" + id + "'"; Connection conn = DButil.getConn(); Statement state = null; int a = 0; try { state = conn.createStatement(); a = state.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { DButil.close(state, conn); } if (a > 0) { f = true; } return f; } public boolean update(course course) { String sql = "update course set name='" + course.getName() + "', teacher='" + course.getTeacher() + "', classroom='" + course.getClassroom() + "' where id='" + course.getId() + "'"; Connection conn = DButil.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); a = state.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { DButil.close(state, conn); } if (a > 0) { f = true; } return f; } public boolean name(String name) { Boolean flag=false; String sql="select from tiantianxiangshang where name='"+name+"'"; Connection conn=DButil.getConn();//建立桥 Statement state=null;//建立车 ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { DButil.close(rs, state, conn); } return flag; } public course getCourseById(int id) { String sql = "select * from course where id ='" + id + "'"; Connection conn = DButil.getConn(); Statement state = null; ResultSet rs = null; course course = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { String name = rs.getString("name"); String teacher = rs.getString("teacher"); String classroom = rs.getString("classroom"); course = new course(id, name, teacher, classroom); } } catch (Exception e) { e.printStackTrace(); } finally { DButil.close(rs, state, conn); } return course; } public course getCourseByName(String name) { String sql = "select * from course where name ='" + name + "'"; Connection conn = DButil.getConn(); Statement state = null; ResultSet rs = null; course course = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { int id = rs.getInt("id"); String teacher = rs.getString("teacher"); String classroom = rs.getString("classroom"); course = new course(id, name, teacher, classroom); } } catch (Exception e) { e.printStackTrace(); } finally { DButil.close(rs, state, conn); } return course; } public List<course> search(String name, String teacher, String classroom) { String sql = "select * from course where "; if (name != "") { sql += "name like '%" + name + "%'"; } if (teacher != "") { sql += "teacher like '%" + teacher + "%'"; } if (classroom != "") { sql += "classroom like '%" + classroom + "%'"; } List<course> list = new ArrayList<>(); Connection conn = DButil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); Course bean = null; while (rs.next()) { int id = rs.getInt("id"); String name2 = rs.getString("name"); String teacher2 = rs.getString("teacher"); String classroom2 = rs.getString("classroom"); bean = new course(id, name2, teacher2, classroom2); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DButil.close(rs, state, conn); } return list; } public List<course> list() { String sql = "select * from course"; List<course> list = new ArrayList<>(); Connection conn = DButil.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); course bean = null; while (rs.next()) { int id = rs.getInt("id"); String name2 = rs.getString("name"); String teacher2 = rs.getString("teacher"); String classroom2 = rs.getString("classroom"); bean = new course(id, name2, teacher2, classroom2); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DButil.close(rs, state, conn); } return list; } }
这一层主要是完成与数据库之间的操作，比如在写添加这个方法时，建立实体类的对象，然后写添加语句，之后是搭建与小岛之间的桥梁，然后建立一个可也在桥梁上运行货物的车，但是先给它赋一个空指针，使用try catch语句，在try语句中给车一个可以拉货物的功能，然后把车拉了的货物赋给一个常量。最后判断如果这个常量的值大于0，那么就返回true,否则就返回false。里面还有可以查找课程名称的方法name，这个方法在service类中可以用来查找输入的名字是否已经存在。还有其他的几个方法。
另一个层是service层，这个层是服务层，用来沟通dao层和sevlet层之间的桥梁。代码如下。
package service; import java.awt.List; import dao.coursedao; import entity.course; /*服务层 * 实现sevelet与dao层之间的联系 */ public class courseservice { coursedao cdao=new coursedao(); public boolean add(course course) { Boolean f=false; if(!cdao.name(course.getName())) { cdao.add(course); f=true; } return f; } public void del(int id) { cdao.delete(id); } public void update(course course) { cdao.update(course); } public course getCourseById(int id) { return cdao.getCourseById(id); } public course getCourseByName(String name) { return cdao.getCourseByName(name); } public List<course> search(String name, String teacher, String classroom) { return cdao.search(name, teacher, classroom); } public List<course> list() { return cdao.list(); } } package service; import java.awt.List; import dao.coursedao; import entity.course; /*服务层 * 实现sevelet与dao层之间的联系 */ public class courseservice { coursedao cdao=new coursedao(); public boolean add(course course) { Boolean f=false; if(!cdao.name(course.getName())) { cdao.add(course); f=true; } return f; } public void del(int id) { cdao.delete(id); } public void update(course course) { cdao.update(course); } public course getCourseById(int id) { return cdao.getCourseById(id); } public course getCourseByName(String name) { return cdao.getCourseByName(name); } public List<course> search(String name, String teacher, String classroom) { return cdao.search(name, teacher, classroom); } public List<course> list() { return cdao.list(); } }
首先建立dao层类的对象，以添加为例，如果输入的名字不存在，则成功输入，否则返回false。
另外一层是servlet层，这一层是完成与后台之间的操作。代码如下。
package sevelet; import java.io.IOException; import java.util.List; import javax.sevelet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class courseservlet extends HttpServlet { private static final long serialVersionUID = 1L; courseservice service = new courseservice(); /** * 方法选择 */ protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String method = req.getParameter("method"); if ("add".equals(method)) { add(req, resp); } else if ("del".equals(method)) { del(req, resp); } else if ("update".equals(method)) { update(req, resp); } else if ("search".equals(method)) { search(req, resp); } else if ("getcoursebyid".equals(method)) { getCourseById(req, resp); } else if ("getcoursebyname".equals(method)) { getCourseByName(req, resp); } else if ("list".equals(method)) { list(req, resp); } } /** * 添加 * @param req * @param resp * @throws IOException * @throws ServletException */ private void add(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException { req.setCharacterEncoding("utf-8"); String name = req.getParameter("name"); String teacher = req.getParameter("teacher"); String classroom = req.getParameter("classroom"); Course course = new Course(name, teacher, classroom); //添加后消息显示 if(service.add(course)) { req.setAttribute("message", "添加成功");//传递参数给jsp req.getRequestDispatcher("add.jsp").forward(req,resp);//与前台add.jsp文件取得联系 } else { req.setAttribute("message", "课程名称重复，请重新录入"); req.getRequestDispatcher("add.jsp").forward(req,resp); } } }
这一层里面我只写了添加的方法。getParameter方法用来接受在文本框中输入的内容。将.jsp中的name、teacher、classroom重新赋值给字符串类型的name,teacher,classroom。然后建立course类的对象，然后把之间字符串类型的name,teacher,classroom封装在一个包里，方便直接使用。使用try catch语句，在try语句中判断，如果在service类中的add方法可以正确运行，那么就使用setAttribute方法，传递参数到.jsp文件，然后用getRequestDispatcher方法，实现servlet与.jsp文件的连接。
后台的文件就是这些。下面是前台，需要运行然后出现网页，直接进行增改查的.jsp文件。
显示主页的为index.jsp文件。代码如下。
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>首页</title> <style> .a{ font-size: 26px; margin-top: 20px; } </style> </head> <body> <div align="center">//给文本框设置格式 <h1 style="color: red;">课程信息管理系统</h1> <div class="a"> <a href="add.jsp">课程信息录入</a> </div> <div class="a"> <a href="courseservlet?method=list">课程信息修改</a> </div> <div class="a"> <a href="del.jsp">课程信息删除</a> </div> <div class="a"> <a href="search.jsp">课程信息查询</a> </div> </div> </body> </html>
在这个里面主要完成给文本框设置格式，然后可以进行增删改查的选择，在选择之后直接进去相关的网页来进行操作。
添加的.jsp代码如下。
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> <style>//设置字体的格式 .a{ margin-top: 20px; } .b{ font-size: 20px; 160px; color: white; background-color: greenyellow; } </style> </head> <body> <% Object message = request.getAttribute("message");//接收从sevelet里面传来的参数 if(message!=null && !"".equals(message)){ %> <script type="text/javascript"> alert("<%=request.getAttribute("message")%>"); </script> <%} %> <div align="center">//给文本框设置格式 <h1 style="color: red;">课程信息录入</h1> <a href="index.jsp">返回主页</a> <form action="courseservlet?method=add" method="post" onsubmit="return check()"> <div class="a"> 课程名称<input type="text" id="name" name="name"/> </div> <div class="a"> 任课教师<input type="text" id="teacher" name="teacher" /> </div> <div class="a"> 上课地点<input type="text" id="classroom" name="classroom" /> </div> <div class="a"> <button type="submit" class="b">保 存</button> </div> </form> </div> <script type="text/javascript"> function check() { var name = document.getElementById("name");; var teacher = document.getElementById("teacher"); var classroom = document.getElementById("classroom"); //非空 if(name.value == '') { alert('课程名称为空'); name.focus(); return false; } if(teacher.value == '') { alert('教师为空'); teacher.focus(); return false; } if(classroom.value == '') { alert('上课地点为空'); classroom.focus(); return false; } //教师 if(teacher.value != 'wangjianmin' && teacher.value != 'wanghui' && teacher.value != 'liudan' && teacher.value != 'liulijia' && teacher.value != 'yangziguang'){ alert('教师名称错误'); return false; } //教室 if(!/^jijiao/.test(classroom.value) && !/^yijiao/.test(classroom.value) && !/^erjiao/.test(classroom.value) && !/^sanjiao/.test(classroom.value)) { alert('上课地点错误'); return false; } } </script> </body> </html>
在servlet类中层使用setAttribute方法来向.jsp文件中传递参数，那么在.jsp文件中就要使用getAttribute方法来接收从servlet文件中传递来的参数。同样需要对文本框设置格式，然后在添加时判断输入的课程名称、任课老师、上课地点是否为空，如果为空就使用alert来弹出一个框提示错误。然后判断输入的老师是否为这五个老师中的一个，否则提示错误。然后运用四则公式判断输入的上课地点是否为以基教、一教、二教、三教开头，否则提示错误。
然后是删，删分为两步，第一步是输入课程名称，然后点击删除，出现这一科课程的全部信息，然后提示是否真的删除。代码如下。
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> <style> .a{ margin-top: 20px; } .b{ font-size: 20px; 160px; color: white; background-color: greenyellow; } </style> </head> <body> <% Object message = request.getAttribute("message"); if(message!=null && !"".equals(message)){ %> <script type="text/javascript"> alert("<%=request.getAttribute("message")%>"); </script> <%} %> <div align="center"> <h1 style="color: red;">课程信息删除</h1> <a href="index.jsp">返回主页</a> <form action="CourseServlet?method=getcoursebyname" method="post" onsubmit="return check()"> <div class="a"> 课程名称<input type="text" id="name" name="name"/> </div> <div class="a"> <button type="submit" class="b">查 找</button> </div> </form> </div> <script type="text/javascript"> function check() { var name = document.getElementById("name");; //非空 if(name.value == '') { alert('课程名称为空'); name.focus(); return false; } } </script> </body> </html> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> <style> .a{ margin-top: 20px; } .b{ font-size: 20px; 160px; color: white; background-color: greenyellow; } .tb, td { border: 1px solid black; font-size: 22px; } </style> </head> <body> <div align="center"> <h1 style="color: red;">课程信息删除</h1> <a href="index.jsp">返回主页</a> <table class="tb"> <tr> <td>课程名称</td> <td>${course.name}</td> </tr> <tr> <td>任课教师</td> <td>${course.teacher}</td> </tr> <tr> <td>上课地点</td> <td>${course.classroom}</td> </tr> </table> <div class="a"> <a onclick="return check()" href="CourseServlet?method=del&id=${course.id}">删 除</a> </div> </div> <script type="text/javascript"> function check() { if (confirm("真的要删除吗？")){ return true; }else{ return false; } } </script> </body> </html>
然后是修改的.jsp文件。代码如下。
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> <style> .a{ margin-top: 20px; } .b{ font-size: 20px; 160px; color: white; background-color: greenyellow; } </style> </head> <body> <% Object message = request.getAttribute("message"); if(message!=null && !"".equals(message)){ %> <script type="text/javascript"> alert("<%=request.getAttribute("message")%>"); </script> <%} %> <div align="center"> <h1 style="color: red;">课程信息修改</h1> <a href="index.jsp">返回主页</a> <form action="CourseServlet?method=update" method="post" onsubmit="return check()"> <div class="a"> 课程名称<input type="text" id="name" name="name" value="${course.name}"/> </div> <div class="a"> 任课教师<input type="text" id="teacher" name="teacher" value="${course.teacher}"/> </div> <div class="a"> 上课地点<input type="text" id="classroom" name="classroom" value="${course.classroom}"/> </div> <input type="hidden" id="id" name="id" value="${course.id}"/> <div class="a"> <button type="submit" class="b">修 改</button> </div> </form> </div> <script type="text/javascript"> function check() { var name = document.getElementById("name");; var teacher = document.getElementById("teacher"); var classroom = document.getElementById("classroom"); //非空 if(name.value == '') { alert('课程名称为空'); name.focus(); return false; } if(teacher.value == '') { alert('教师为空'); teacher.focus(); return false; } if(classroom.value == '') { alert('上课地点为空'); classroom.focus(); return false; } //教师 if(teacher.value != '王建民' && teacher.value != '王辉' && teacher.value != '刘丹' && teacher.value != '刘立嘉' && teacher.value != '杨子光'){ alert('教师名称错误'); return false; } //教室 if(!/^基教/.test(classroom.value) && !/^一教/.test(classroom.value) && !/^二教/.test(classroom.value) && !/^三教/.test(classroom.value)) { alert('上课地点错误'); return false; } } </script> </body> </html>
在进行修改的时候也会进行判断，是否是这五个老师中的一个，并且判断是否是以基教、一教、二教、三教开头的。
然后是查询，查询要求实现模糊查询。代码如下。
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> <style> .a{ margin-top: 20px; } .b{ font-size: 20px; 160px; color: white; background-color: greenyellow; } </style> </head> <body> <div align="center"> <h1 style="color: red;">课程信息查询</h1> <a href="index.jsp">返回主页</a> <form action="CourseServlet?method=search" method="post" onsubmit="return check()"> <div class="a"> 课程名称<input type="text" id="name" name="name"/> </div> <div class="a"> 任课教师<input type="text" id="teacher" name="teacher" /> </div> <div class="a"> 上课地点<input type="text" id="classroom" name="classroom" /> </div> <div class="a"> <button type="submit" class="b">查 询</button> </div> </form> </div> <script type="text/javascript"> function check() { var name = document.getElementById("name");; var teacher = document.getElementById("teacher"); var classroom = document.getElementById("classroom"); //非空 if(name.value == '' && teacher.value == '' && classroom.value == '') { alert('请填写一个条件'); return false; } } </script> </body> </html>
要填写一个条件，否则提示错误。
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> <style> .a{ margin-top: 20px; } .b{ font-size: 20px; 160px; color: white; background-color: greenyellow; } .tb, td { border: 1px solid black; font-size: 22px; } </style> </head> <body> <div align="center"> <h1 style="color: red;">课程信息列表</h1> <a href="index.jsp">返回主页</a> <table class="tb"> <tr> <td>id</td> <td>课程名称</td> <td>任课教师</td> <td>上课地点</td> </tr> <!-- forEach遍历出adminBeans --> <c:forEach items="${courses}" var="item" varStatus="status"> <tr> <td>${item.id}</td> <td><a>${item.name}</a></td> <td>${item.teacher}</td> <td>${item.classroom}</td> </tr> </c:forEach> </table> </div> </body> </html>
然后输入一个条件之后就会遍历出相关的所有信息。