关于数据库的增删改查（以课程信息管理系统为例）

一、相关的软件下载和环境配置

1、下载并配置JDK。

2、下载eclipse。

3、下载并配置apache-tomcat（服务器）。

4、下载MySQL（数据库）。

5、下载Navicat for MySQL（数据库可视化工具），方便对数据库的操作。

6、下载jdbc用来实现eclipse中的项目与数据库实现连接。

---以上可在网上查询教程

二、实现简单的课程信息管理系统

1、

打开eclipse，点击File—》New—》other—》Dynamic Web Project

（若你的eclipse中找不到或者没有Dynamic Web Project，可上网搜素解决办法）

点击后会出现如下窗口

需要填写Project name，可随意填写（最好是英文的），其他不需填写，填写完毕之后点击Finish

2、

项目建立完之后，eclipse左侧的Project Explorer会出现你建立的项目，如下图

3、

在我的电脑中，打开你下载的jdbc（也就是mysql-connector-java-8.0.13）所在的文件夹，如下图

将mysql-connector-java-8.0.13文件复制

粘贴在eclipse中，你所建立的项目下的 WebContent/WEB-INF/lib 文件夹下，如下图

然后，在你刚刚粘贴的文件上右击，Build Path—》Add to Build Path，如下图

这样就完成了jdbc的导入

4、

在 WebContent 文件夹下，新建如下图中的JSP文件，Login.jsp和signinerror.jsp是用来登陆的，这里不必建立。

相关代码

1.head.jsp

<%@ 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">

.headdiv{
    background-color:#3F51B5;
    width:100%; 
    height:130px;
}

.headdiv P{
    font-family:YouYuan;
    font-size:20px;
    color:#E8EAF6;
    position:relative;
    left:20px;
    top:45px;
}

</style>
</head>
<body>

<div class="headdiv">
    <p>ec-web课程信息综合管理平台</p>
</div>

</body>
</html>

2.left.jsp

<%@ 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">

.leftMenu{
    background-color:#7986CB;
    width:250px; 
    height:600px;
    position:relative;
    top:0px;
}

.menuParent{
    background-color:#BBDEFB;
}



</style>
</head>
<body>

<div class="leftMenu">
    <div class="menu">
    
    
        <div class="menuParent">
            <div class="ListTitlePanel">
                <div class="ListTitle">
                    <strong>课程信息管理</strong>                    
                    <div class="leftbgbt"></div>
                </div>
            </div>
            
            <div class="menuList">
                <div><a target="mainAction" href="USEJSP/add.jsp">课程信息录入</a></div>
                <div><a target="mainAction" href="USEJSP/update.jsp">课程信息修改</a></div>
                <div><a target="mainAction" href="USEJSP/delete.jsp">删除课程信息</a></div>
                <div><a target="mainAction" href="USEJSP/search.jsp">查询课程信息</a></div>                
            </div>
        </div>
        
        
    </div>
</div>

<script type="text/javascript">



</script>

</body>
</html>

3.index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
这是小主页
</body>
</html>

4.main.jsp

<%@ 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">

.headframe{
    width:100%;
    height:130px;
    border:0;
}

.leftframe{
    float:left;
    width:250px;
    height:600px;
    border:0;    
}

.mainframe{
    float:right;
    width:1200px;
    height:600px;
    border:0;
}

</style>

</head>


<iframe src="head.jsp" class="headframe" scrolling="no"></iframe>
<iframe src="left.jsp" class="leftframe" scrolling="no"></iframe>
<iframe src="index.jsp" name="mainAction" class="mainframe"></iframe>


</html>

5、

在WebContent文件夹下建立名为USEJSP的文件夹，在USEJSP文件夹下，建立如下图的JSP文件

6add.jsp

<%@ 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">

.butn{
    display:inline-block;
    padding: 8px 35px;
    background-color:#32CD32;
     color:white;
     border:none;
     text-align:center;
    text-decoration:none;
    border-radius:5px;    
    position:absolute;
     left:40px;
     top:95px;
}

</style>

</head>
<body>

<div class="container">
    <section>
        <form action="${pageContext.request.contextPath}/AddServlet/AddCurrServlet?method=add" method="post" onsubmit="return check()">
        <!---${pageContext.request.contextPath}的目的是找到主工程的名字，/Servlet/Userservlet就是web.xml下面配置的路径，也就是servlet的路径--->
            <span class="cname">课程名称</span>
            <input type="text" name="cname" class="icname" placeholder="  class-name" id="icname">
            <br/>
            
            <span class="teacher">任课教师</span>
            <input type="text" name="teacher" class="iteacher" placeholder="  teacher" id="iteacher">
            <br/>
            
            <span class="place">上课地点</span>
            <input type="text" name="place" class="iplace" placeholder="  place" id="iplace">
            <br/>
            
            <input type="submit" value="保&nbsp;存" class="butn">
            <br/>
        </form>
    </section>
</div>

<script type="text/javascript">
    function check() {
        
        var cname = document.getElementById("icname");
        var teacher = document.getElementById("iteacher");
        var place = document.getElementById("iplace");
        
        var placestr = place.value.substring(0,2);
        
        
        //非空
        if(cname.value == '') {
            alert('课程名称为空');
            cname.focus();
            return false;
        }
        if(teacher.value == '') {
            alert('教师为空');
            teacher.focus();
            return false;
        }
        if(place.value == '') {
            alert('上课地点为空');
            place.focus();
            return false;
        }
        
        //教师
        if(teacher.value != '王建民' && teacher.value != '王辉' && teacher.value != '刘丹' && teacher.value != '刘立嘉' && teacher.value != '杨子光'){
            alert('教师名称错误');
            return false;
        }
        
        //教室
        if(!/^基教/.test(placestr) && !/^一教/.test(placestr) && !/^二教/.test(placestr) && !/^三教/.test(placestr)) {
            alert('上课地点错误');
            return false;
        }
        return true;
    }
    
</script>
    
</body>
</html>

7.addnameerror.jsp

<%@ 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">

.butn{
    display:inline-block;
    padding: 8px 35px;
    background-color:#32CD32;
     color:white;
     border:none;
     text-align:center;
    text-decoration:none;
    border-radius:5px;    
    position:absolute;
     left:40px;
     top:95px;
}

</style>

</head>
<body onload="alt()">

<div class="container">
    <section>
        <form action="${pageContext.request.contextPath}/AddServlet/AddCurrServlet?method=add" method="post" onsubmit="return check()">
        <!---${pageContext.request.contextPath}的目的是找到主工程的名字，/Servlet/Userservlet就是web.xml下面配置的路径，也就是servlet的路径--->
            <span class="cname">课程名称</span>
            <input type="text" name="cname" class="icname" placeholder="  class-name" id="icname">
            <br/>
            
            <span class="teacher">任课教师</span>
            <input type="text" name="teacher" class="iteacher" placeholder="  teacher" id="iteacher">
            <br/>
            
            <span class="place">上课地点</span>
            <input type="text" name="place" class="iplace" placeholder="  place" id="iplace">
            <br/>
            
            <input type="submit" value="保&nbsp;存" class="butn">
            <br/>
        </form>
    </section>
</div>

<script type="text/javascript">
    function check() {
        
        var cname = document.getElementById("icname");
        var teacher = document.getElementById("iteacher");
        var place = document.getElementById("iplace");
        
        var placestr = place.value.substring(0,2);
        
        
        //非空
        if(cname.value == '') {
            alert('课程名称为空');
            cname.focus();
            return false;
        }
        if(teacher.value == '') {
            alert('教师为空');
            teacher.focus();
            return false;
        }
        if(place.value == '') {
            alert('上课地点为空');
            place.focus();
            return false;
        }
        
        //教师
        if(teacher.value != '王建民' && teacher.value != '王辉' && teacher.value != '刘丹' && teacher.value != '刘立嘉' && teacher.value != '杨子光'){
            alert('教师名称错误');
            return false;
        }
        
        //教室
        if(!/^基教/.test(placestr) && !/^一教/.test(placestr) && !/^二教/.test(placestr) && !/^三教/.test(placestr)) {
            alert('上课地点错误');
            return false;
        }
        return true;
    }
    
    function alt() {
        alert('课程名称重复');
    }
</script>
    
</body>
</html>

8.delete.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>

<%@page import="java.sql.*"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="javax.servlet.http.HttpServletRequest"%>
<html>
<head>
<meta charset="UTF-8">
<title></title>

<style type="text/css">

.butn{
    display:inline-block;
    padding: 8px 35px;
    background-color:#32CD32;
     color:white;
     border:none;
     text-align:center;
    text-decoration:none;
    border-radius:5px;    
    position:absolute;
     left:40px;
     top:95px;
}

</style>

</head>
<body>

<div>
    <section>
        <form action="${pageContext.request.contextPath}/DeleteServlet/DeleteCurrServlet?method=add" method="post" onsubmit="return check()">
            <span class="cname">课程名称</span>
            <input type="text" name="cname" id="icname" class="icname" placeholder="  class-name">
            <br/>
    
            <input type="submit" value="删除" class="butn">
            <br/>
        </form>
    </section>
</div>

<script type="text/javascript">
    function check() {
        
        var cname = document.getElementById("icname");
        
        //非空
        if(cname.value == '') {
            alert('课程名称为空,请重新输入');
            cname.focus();
            return false;
        }
        return true;
    }
    
</script>

</body>
</html>

9.search.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>

<%@page import="java.sql.*"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="javax.servlet.http.HttpServletRequest"%>
<html>
<head>
<meta charset="UTF-8">
<title></title>

<style type="text/css">

.butn{
    display:inline-block;
    padding: 8px 35px;
    background-color:#32CD32;
     color:white;
     border:none;
     text-align:center;
    text-decoration:none;
    border-radius:5px;    
    position:absolute;
     left:40px;
     top:95px;
}

</style>

</head>
<body>

<div>
    <section>
        <form action="${pageContext.request.contextPath}/SearchServlet/SearchCurrServlet?method=add" method="post">
            <span class="cname">课程名称</span>
            <input type="text" name="cname" id="cname" class="icname" placeholder="  class-name">
            <br/>
            
            <span class="teacher">任课教师</span>
            <input type="text" name="teacher" id="teacher" class="iteacher" placeholder="  teacher">
            <br/>
        
            <span class="place">上课地点</span>
            <input type="text" name="place" id="place" class="iplace" placeholder="  place">
            <br/>
    
            <input type="submit" value="查询" class="butn">
            <br/>
        </form>
    </section>
</div>


</body>
</html>

10.update.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>

<%@page import="java.sql.*"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>

<form action="${pageContext.request.contextPath}/UpdateServlet/UpdateCurrServlet?method=update" method="post" onsubmit="return check()">
    <span class="cname">需要修改的课程名称</span>
    <input type="text" name="tempcname" class="itempcname" placeholder="  class-name" id="itempcname">
    <br/>

        <table border="1" bgcolor="#ffffff" align="" cellspacing="1" cellpadding="1">
        <caption>输入修改信息</caption>
        <tr>
        <td align="center" width=16%>新课程名称</td>
        <td align="center" width=16%>新任课教师</td>
        <td align="center" width=25%>新上课地点</td>
        <td align="center" width=16%>操作</td>
        </tr>
    
        <tr>
        <td width=16% align="center"><input type="text" name="cname" class="icname" placeholder="  class-name" id="icname"></td>
        <td width=16% align="center"><input type="text" name="teacher" class="iteacher" placeholder="  teacher" id="iteacher"></td>
        <td width=25% align="center"><input type="text" name="place" class="iplace" placeholder="  place" id="iplace"></td>
        <td width=16% align="center"><input type="submit" value="修&nbsp;改" class="butn"></td>
        </tr>

        </table>
</form>

</body>

<script type="text/javascript">

function check() {
    
    var ctempname = document.getElementById("itempcname");
    var cname = document.getElementById("icname");
    var teacher = document.getElementById("iteacher");
    var place = document.getElementById("iplace");
    
    var placestr = place.value.substring(0,2);
    
    
    //非空
    if(ctempname.value == ''){
        alert('需修改课程名称为空');
        teacher.focus();
        return false;
    }
    if(cname.value == '') {
        alert('课程名称为空');
        cname.focus();
        return false;
    }
    if(teacher.value == '') {
        alert('教师为空');
        teacher.focus();
        return false;
    }
    if(place.value == '') {
        alert('上课地点为空');
        place.focus();
        return false;
    }
    
    
    //教师
    if(teacher.value != '王建民' && teacher.value != '王辉' && teacher.value != '刘丹' && teacher.value != '刘立嘉' && teacher.value != '杨子光'){
        alert('教师名称错误');
        return false;
    }
    
    //教室
    if(!/^基教/.test(placestr) && !/^一教/.test(placestr) && !/^二教/.test(placestr) && !/^三教/.test(placestr)) {
        alert('上课地点错误');
        return false;
    }
    return true;
}

</script>  

</html>

下面，生成web.xml文件（用来配置跳转路径）

在你的项目上右击，java EE Tool—》Generate Deployment Descriptor Stub

这样，在WebContent/WEB-INF文件夹下就生成了.xml文件，如下图

6（1）Web.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" version="3.0">
  <display-name>curriculum_information_management_web</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>Userservlet</servlet-name>    <!--servlet的别名，随便取  -->
        
        <!--servlet的包路径，后面再加上.servlet类名 ，这里的类名必须是包下面的servlet类名，目的是让找到该servlet的路径 -->
        
        <servlet-class>com.Servlet.Userservlet</servlet-class>
    </servlet>    
    <servlet-mapping>
        <servlet-name>Userservlet</servlet-name>    <!--servlet的别名，和上面保持一致就行  -->
        
        <!--jsp跳转到servlet的路径名，自己取，用来从jsp界面跳转到servlet的路径，程序会根据路径找到servlet的位置  -->
        
        <url-pattern>/Servlet/Userservlet</url-pattern><!--  -->
    </servlet-mapping>
  
      
      <servlet>
        <servlet-name>AddCurrServlet</servlet-name>        
        <servlet-class>com.Servlet.AddCurrServlet</servlet-class>
    </servlet>    
    <servlet-mapping>
        <servlet-name>AddCurrServlet</servlet-name>
        <url-pattern>/AddServlet/AddCurrServlet</url-pattern><!--  -->
    </servlet-mapping>
    
    
    <servlet>
        <servlet-name>SearchCurrServlet</servlet-name>        
        <servlet-class>com.Servlet.SearchCurrServlet</servlet-class>
    </servlet>    
    <servlet-mapping>
        <servlet-name>SearchCurrServlet</servlet-name>
        <url-pattern>/SearchServlet/SearchCurrServlet</url-pattern><!--  -->
    </servlet-mapping>
    
    
    <servlet>
        <servlet-name>DeleteCurrServlet</servlet-name>        
        <servlet-class>com.Servlet.DeleteCurrServlet</servlet-class>
    </servlet>    
    <servlet-mapping>
        <servlet-name>DeleteCurrServlet</servlet-name>
        <url-pattern>/DeleteServlet/DeleteCurrServlet</url-pattern><!--  -->
    </servlet-mapping>
    
    
    <servlet>
        <servlet-name>UpdateCurrServlet</servlet-name>        
        <servlet-class>com.Servlet.UpdateCurrServlet</servlet-class>
    </servlet>    
    <servlet-mapping>
        <servlet-name>UpdateCurrServlet</servlet-name>
        <url-pattern>/UpdateServlet/UpdateCurrServlet</url-pattern><!--  -->
    </servlet-mapping>
    
</web-app>

7、

在项目下的 Java Resources/src 建立包，如下图

然后，在各包下建立类，如图

7（1）com.Bean

建立CurrBean.java文件

package com.Bean;

public class CurrBean {
    private String name;
    private String teacher;
    private String place;
    
    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 getPlace() {
        return place;
    }
    public void setPlace(String place) {
        this.place = place;
    }
}

7（2）com.Dao（该包中的类是用来登录操作的，在这里只是写出来，不用实现）

建立Userdao.java文件

package com.Dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.DBUtil.*;

public class Userdao {
    public int login(String username,String password)
    {
        Connection conn = DBUtil.getConn();//这里就是从DBUtil类里面得到连接
        Statement state =null;
        ResultSet rs = null;
        int flag=0;
        try
        {
            String sql = "select * from admin";//SQL语句
            state = conn.createStatement();            
            rs=state.executeQuery(sql);
            while(rs.next())
            {
                if(rs.getString("password").equals(password)&&rs.getString("username").equals(username))
                {
                    flag=1;
                }
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }        
        finally
        {
            DBUtil.close(rs, state, conn);
        }
        return flag;
    }
    

}

7（3）com.DBUtil

建立CurrDBUtil.java文件（链接数据库）

package com.DBUtil;

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

public class CurrDBUtil {
    public static String db_url="jdbc:mysql://localhost:3306/curriculum?useSSL=true&serverTimezone=UTC";
    //这是连接数据库，userdome是数据库的名称，userUnicode=true&characterEncoding=UTF-8是将字符集设置为utf-8，避免乱码。
    public static String db_user="root";//数据的用户名
    public static String db_password="lty";//数据库的密码
    public static Connection getConn()//获取连接，返回Connection类型，必须设置为static这样才能在其他类中使用
    {
        Connection conn=null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");//加载驱动
            conn=DriverManager.getConnection(db_url,db_user,db_password);//连接数据库
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return 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();
            }
        }
    }

}

建立DBUtil.java文件（用来链接登录数据库，不必实现）

package com.DBUtil;

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/userdome?useSSL=true&serverTimezone=UTC";
    //这是连接数据库，userdome是数据库的名称，userUnicode=true&characterEncoding=UTF-8是将字符集设置为utf-8，避免乱码。
    public static String db_user="root";//数据的用户名
    public static String db_password="lty100609";//数据库的密码
    public static Connection getConn()//获取连接，返回Connection类型，必须设置为static这样才能在其他类中使用
    {
        Connection conn=null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");//加载驱动
            conn=DriverManager.getConnection(db_url,db_user,db_password);//连接数据库
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return 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();
            }
        }
    }

}

7（4）com.Servlet

AddCurrServlet.java 实现课程的增加

package com.Servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.Bean.CurrBean;
import com.DBUtil.*;

public class AddCurrServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;
    
    protected void doPost(HttpServletRequest req,HttpServletResponse resp)throws ServletException,IOException{
        resp.setContentType("add.jsp");
        req.setCharacterEncoding("UTF-8");
        
        String name;
        String teacher;
        String place;
        
        name = req.getParameter("cname");
        teacher = req.getParameter("teacher");
        place = req.getParameter("place");
        
        CurrBean currbean = new CurrBean();
        
        currbean.setName(name);
        currbean.setTeacher(teacher);
        currbean.setPlace(place);
        
        AddCurrInterface addCurrInterface = new AddCurrInterface();
        @SuppressWarnings("unused")
        int temp = 0;
        
        try {
            temp = addCurrInterface.addUser(currbean);
            if(temp == 1) {
                resp.sendRedirect(req.getContextPath()+"/index.jsp");
            }else if(temp == 2) {
                resp.sendRedirect(req.getContextPath()+"/USEJSP/addnameerror.jsp");
            }
                        
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}


class AddCurrInterface extends HttpServlet{

    private static final long serialVersionUID = 1L;
    
    public int addUser(CurrBean cb) throws SQLException {    
        
        String name;
        String teacher;
        String place;
        
        name = cb.getName();
        teacher = cb.getTeacher();
        place = cb.getPlace();
        
        //连接数据库
        Connection conn = CurrDBUtil.getConn();
        PreparedStatement pstmt = null;
        
        Statement stmt = conn.createStatement();
        String selectsql = "select * from curr_info";
        ResultSet rs = stmt.executeQuery(selectsql);
        int tt = 0;
        
        while(rs.next()) {
            if(rs.getString("name").equals(name)) {
                tt = 2;
            }
        }
        
        
        if(tt == 0) {
            String sql = "insert into curr_info (name,teacher,place) values (?,?,?)";//数据库指令
            pstmt = conn.prepareStatement(sql);//发送sql语句
            //下面是插入到具体的地方
            pstmt.setString(1, name);
            pstmt.setString(2, teacher);
            pstmt.setString(3, place);
            pstmt.executeUpdate();
            
            System.out.println("添加成功！");            
            
            return 1;
        }else if(tt == 2) {            
            return 2;
        }
        
        return 0;
        
    }
    
    
}

DeleteCurrServlet.java 实现课程的删除

package com.Servlet;

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.DBUtil.CurrDBUtil;

public class DeleteCurrServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;

    protected void doPost(HttpServletRequest req,HttpServletResponse resp)throws ServletException,IOException{
        resp.setContentType("delete.jsp");
        req.setCharacterEncoding("UTF-8");
        
        String name;
        @SuppressWarnings("unused")
        int ii = 0;
        
        name = req.getParameter("cname");
        
        //连接数据库
          Connection conn = CurrDBUtil.getConn();
        try {
            Statement stmt = conn.createStatement();
            
            String selectsql = "select * from curr_info";
              ResultSet rs = stmt.executeQuery(selectsql);
              
              while(rs.next()) {
                  if(rs.getString("name").equals(name)) {
                      ii = rs.getInt("id");
                      //Get output stream and writers
                      OutputStream out=resp.getOutputStream();
                      PrintWriter pw=new PrintWriter(new BufferedWriter(new OutputStreamWriter(out)));
                      //Print HTML
                      pw.println("<HTML><HEAD><TITLE>输出HTML标签</HEAD></TITLE><BODY>");
                      pw.println("<table border=\"1\" bgcolor=\"#ffffff\" align=\"\" cellspacing=\"1\" cellpadding=\"1\">");
                      pw.println("<tr>\r\n" + 
                              "<td align=\"center\" width=16%>课程名称</td>\r\n" + 
                              "<td align=\"center\" width=16%>任课教师</td>\r\n" + 
                              "<td align=\"center\" width=35%>上课地点</td>\r\n" + 
                              "</tr>");
                      pw.println("<tr>\r\n" + 
                              "<td width=16% align=\"center\">"+rs.getString("name")+"</td>\r\n" + 
                              "<td width=16% align=\"center\">"+rs.getString("teacher")+"</td>\r\n" + 
                              "<td width=35% align=\"center\">"+rs.getString("place")+"</td>\r\n" + 
                              "</tr>");
                      pw.println("该课程已删除！");
                      pw.println("</BODY></HTML>");
                      pw.flush();
                      pw.close();
                  }
              }
              
              
              String delesql="delete from curr_info where name='"+name+"' ";//生成一条sql语句
              stmt.executeUpdate(delesql);//执行sql语句
              
        } catch (SQLException e) {
            e.printStackTrace();
        }        
    
    }
}

SearchCurrServlet.java 实现课程的查询

package com.Servlet;

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.DBUtil.CurrDBUtil;

public class SearchCurrServlet extends HttpServlet {
    
    private static final long serialVersionUID = 1L;

    protected void doPost(HttpServletRequest req,HttpServletResponse resp)throws ServletException,IOException{
        resp.setContentType("search.jsp");
        req.setCharacterEncoding("UTF-8");
        
        String name;
        String teacher;
        String place;
        
        name = req.getParameter("cname");
        teacher = req.getParameter("teacher");
        place = req.getParameter("place");
        
        int temp = 0;
        //连接数据库
              Connection conn = CurrDBUtil.getConn();
            try {
                Statement stmt = conn.createStatement();
                String selectsql = "select * from curr_info";
                  ResultSet rs = stmt.executeQuery(selectsql);
                  
                  //Get output stream and writers
                  OutputStream out=resp.getOutputStream();
                  PrintWriter pw=new PrintWriter(new BufferedWriter(new OutputStreamWriter(out)));
                  //Print HTML
                  
                  pw.println("<HTML><HEAD><TITLE>输出HTML标签</HEAD></TITLE><BODY>");
                  pw.println("<table border=\"1\" bgcolor=\"#ffffff\" align=\"\" cellspacing=\"1\" cellpadding=\"1\">");
                  pw.println("<tr>\r\n" + 
                          "<td align=\"center\" width=16%>课程名称</td>\r\n" + 
                          "<td align=\"center\" width=16%>任课教师</td>\r\n" + 
                          "<td align=\"center\" width=35%>上课地点</td>\r\n" + 
                          "</tr>");
                  while(rs.next()) {
                      if(rs.getString("name").equals(name) || rs.getString("teacher").equals(teacher) || rs.getString("place").equals(place)) {                          
                          pw.println("<tr>\r\n" + 
                                  "<td width=16% align=\"center\">"+rs.getString("name")+"</td>\r\n" + 
                                  "<td width=16% align=\"center\">"+rs.getString("teacher")+"</td>\r\n" + 
                                  "<td width=35% align=\"center\">"+rs.getString("place")+"</td>\r\n" + 
                                  "</tr>");
                          temp++;
                      }
                      /*
                      else {
                          //Get output stream and writers
                          OutputStream out=resp.getOutputStream();
                          PrintWriter pw=new PrintWriter(new BufferedWriter(new OutputStreamWriter(out)));
                          //Print HTML
                          pw.println("<HTML><HEAD><TITLE>输出HTML标签</HEAD></TITLE><BODY>");
                          pw.println("查询错误！");
                          pw.println("</BODY></HTML>");
                          pw.flush();
                          pw.close();
                      }
                      */                      
                  }
                  if(temp == 0) {
                      pw.println("查询错误！");
                  }
                  
                  pw.println("</BODY></HTML>");
                  pw.flush();
                  pw.close();
                  
            } catch (SQLException e) {
                e.printStackTrace();
            }        
        
      }
}

UpdateCurrServlet.java 实现课程的修改

package com.Servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.DBUtil.CurrDBUtil;

public class UpdateCurrServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;
    
    protected void doPost(HttpServletRequest req,HttpServletResponse resp)throws ServletException,IOException{
        resp.setContentType("update.jsp");
        req.setCharacterEncoding("UTF-8");
        
        String tempname;
        String name;
        String teacher;
        String place;
        
        tempname = req.getParameter("tempcname");
        name = req.getParameter("cname");
        teacher = req.getParameter("teacher");
        place = req.getParameter("place");
        
        //连接数据库
          Connection conn = CurrDBUtil.getConn();
          try {
            Statement stmt = conn.createStatement();            
            String updatesql = "update curr_info set name='"+name+"',teacher='"+teacher+"',place='"+place+"' where name='"+tempname+"'";
            stmt.executeUpdate(updatesql);//执行sql语句
            resp.sendRedirect(req.getContextPath()+"/index.jsp");            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}


原地址链接https://www.cnblogs.com/leity/p/10090498.html

posted on 2018-12-08 22:33 哈弗h6 阅读(547) 评论(0) 收藏举报

刷新页面返回顶部

哈弗h6

关于数据库的增删改查（以课程信息管理系统为例）

导航

公告