当前位置：Gxlcms > 数据库问题 > 增删改查（简单版&连接数据库）

增删改查（简单版&连接数据库）

时间：2021-07-01 10:21:17 帮助过：9人阅读

com.bean; public class Bean { private String name; private String teacher; private String classroom; public Bean() { super(); } public Bean(String name, String teacher, String classroom) { super(); this.name = name; this.teacher = teacher; this.classroom = classroom; } 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; } @Override public String toString() { return "Bean [name=" + name + ", teacher=" + teacher + ", classroom=" + classroom + "]"; } }

Dao.java:

package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.db.DB;
import com.servlet.selectNameServlet;
import com.servlet.updateServlet;
import com.db.DB;
import com.bean.Bean;

public class Dao {
    
    //增加
    @SuppressWarnings("static-access")
    public boolean add(Bean bean) {
        String tablename = "test";//表名
        DB db=new DB();
        Connection con = db.getCon();
        
        try {
            String sql="insert into "+tablename+"(name,teacher,classroom) values (‘"+bean.getName()+"‘,‘"+bean.getTeacher()+"‘,‘"+bean.getClassroom()+"‘)";
            Statement stm = con.createStatement();
            System.out.println(sql);
            stm.execute(sql);
            db.close(stm, con);
        }catch(Exception e) {
            e.printStackTrace();
            System.out.println("add false");
            return false;
        }
        System.out.println("add true");
        return true;
    }

    //根据名称进行查询
    @SuppressWarnings("static-access")
    public static Bean selectName(String name) {
        String tablename = "test";
        System.out.println("select bean where name = "+name);
        Bean bean=null;
        DB db=new DB();
        Connection con = db.getCon();
        try {
            Statement stm = con.createStatement();
            //閿熸枻鎷烽敓鏂ゆ嫹value1=name閿熸枻鎷烽敓鏂ゆ嫹閿熸嵎锝忔嫹閿熸枻鎷烽敓鏂ゆ嫹閿熸枻鎷烽敓鏂ゆ嫹閿熸枻鎷烽敓琛楄鎷烽敓鏂ゆ嫹閿熸枻鎷?
            ResultSet rs = stm.executeQuery("select * from "+tablename+" where name=‘" + name + "‘");
            /*
             * 閿熸枻鎷烽敓鏂ゆ嫹value1=name閿熸枻鎷烽敓鏂ゆ嫹閿熸嵎锝忔嫹閿熸枻鎷烽敓鏂ゆ嫹閿熸枻鎷烽敓鏂ゆ嫹value3閿熸枻鎷烽敓琛楄鎷烽敓鏂ゆ嫹閿熸枻鎷?
             * ResultSet rs =stm.executeQuery("select value3 from "+tablename+" where value2="+i);
             */
            if(rs.next())
            {
                bean = new Bean();
                System.out.println("select the bean from mysql");
                bean.setName(rs.getString("name"));
                bean.setTeacher(rs.getString("teacher"));
                bean.setClassroom(rs.getString("classroom"));
                //bean.setValue2(Integer.parseInt(rs.getString("teacher")));
                //bean.setValue3(Boolean.parseBoolean(rs.getString("address")));
                System.out.println("name of the bean is "+rs.getString("name"));
            }
            db.close(rs,stm, con);
        }catch(Exception e) {
            e.printStackTrace();
        }
        return bean;
    }
    
    
    //删除
    @SuppressWarnings("static-access")
    public static boolean delete(String name)
    {
        //String tablename ="test";
        DB db=new DB();
        Connection con = db.getCon();
        //Connection con=null;
        PreparedStatement stm=null;
        try {
            con=DB.getCon();
            String sql="delete from test where name=‘"+name+"‘";
            //String sql="delete from test where name=‘"+bean.getName()+"‘";
            System.out.println(sql);
            stm=con.prepareStatement(sql);
            stm.executeUpdate();
            return true;
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
        finally {
            db.close(stm, con);
        }
        return false;
    }
   //修改
    @SuppressWarnings("static-access")
    public boolean update(Bean bean) {
        Connection con=null;
        PreparedStatement stm=null;
        String tablename="test";
        DB db=new DB();
        try {
            con=DB.getCon();
            String sql="update "+tablename+" set teacher=?,classroom=? where name=?";
            //String sql="update "+tablename+"set teacher=?,classroom=? where name=?";
            stm=con.prepareStatement(sql);
            stm.setString(3,bean.getName());
            stm.setString(1, bean.getTeacher());
            stm.setString(2,bean.getClassroom());
            stm.executeUpdate();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            return false;
        }
        finally {
            db.close(stm, con);
            return true;
        }
    }
  //根据名字进行查找
     public  boolean findName(String name){
            boolean flag=false;
            Connection con=null;
            PreparedStatement psts=null;
            ResultSet rs=null;
            String tablename="test";
            DB db=new DB();
            try {
                con=db.getCon();
                String sql="select * from"+tablename+" where name=?";
                psts=con.prepareStatement(sql);
                psts.setString(1, name);
                rs=psts.executeQuery();
                while(rs.next()){
                    flag=true;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            
            return flag;
     }
    
    //
    @SuppressWarnings("static-access")
     public ArrayList<Bean> selectList(){
        Connection con=null;
        PreparedStatement stm=null;
        ResultSet rs=null;
        ArrayList<Bean> listbean=new ArrayList<Bean>();
        String tablename="test";
        DB db=new DB();
        try {
            con=db.getCon();
            String sql="select * from "+tablename;
            stm=con.prepareStatement(sql);
            rs=stm.executeQuery();
            while(rs.next()){
            
                String name=rs.getString("name");
                String teacher=rs.getString("teacher");
                String classroom=rs.getString("classroom");
                Bean bean=new Bean(name,teacher,classroom);
                listbean.add(bean);
                
            }
        } 
        catch(SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            db.close(stm, con);
        }
        return listbean;
    }

}

    
    //
//    public Bean login(String name,String password) {
//        String tablename = null;
//        DB db=new DB();
//        Connection con = db.getCon();
//        Bean bean = null;
//        try {
//            Statement stm = con.createStatement();
//            ResultSet rs = stm.executeQuery("select * from "+tablename+" where value1=‘" + name + "‘");
//            if(rs.next()) {
//                if (rs.getString("password").equals(password)) {
//                    
//                }
//            }
//        }catch(Exception e) {
//            
//        }
//    }

DB.java:

package com.db;

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

import org.junit.Test;



public class DB {
    private static String mysqlname = "database";//数据库名
    private static Connection con;
    private static Statement stm;
    private static ResultSet rs;
    private static String coursename = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://127.0.0.1:3306/"+mysqlname+"?useUnicode=true&characterEncoding=GB18030&useSSL=false&serverTimezone=GMT&allowPublicKeyRetrieval=true";
    
    public static Connection getCon() {
        try {
            Class.forName(coursename);
            System.out.println("注册驱动成功");
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection(url,"root","20000604");
            System.out.println("建立连接成功");
        }catch(Exception e){
            e.printStackTrace();
            con = null;
        }
        return con;
    }
    
    public static void close(Statement stm,Connection connection) {
        if(stm!=null) {
            try {
                stm.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null) {
            try {
                connection.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
        
    //关闭
    public static void close(ResultSet rs,Statement stm,Connection connection) {
        if(rs!=null) {
            try {
                rs.close();
            }catch(SQLException e) {
            e.printStackTrace();
            }
        }
        if(stm!=null) {
            try {
                stm.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null) {
            try {
                connection.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void main(String[] args)
    {
        getCon();
    }
}

addServlet.java:

package com.servlet;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.bean.Bean;
import com.dao.Dao;

/**
 * Servlet implementation class addServlet
 */
@WebServlet("/addServlet")
public class addServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        request.setCharacterEncoding("UTF-8");
        
        Bean bean = new Bean();
        bean.setName(request.getParameter("name"));
        bean.setTeacher(request.getParameter("teacher"));
        bean.setClassroom(request.getParameter("classroom"));
        Dao dao = new Dao();
        if(dao.add(bean)) {
            
            request.setAttribute("message", "添加成功");
            request.getRequestDispatcher("add.jsp").forward(request,response);
            //response.sendRedirect("index.jsp");
        }
        else {
            request.setAttribute("message","添加失败");
            request.getRequestDispatcher("add.jsp").forward(request,response);
            //response.sendRedirect("add.jsp");
        }
    }

    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        doGet(request, response);
    }

}

deleteServlet.java:

package com.servlet;

import java.io.IOException;
import java.io.PrintWriter;

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

import com.bean.Bean;
import com.dao.Dao;

/**
 * Servlet implementation class deleteServlet
 */
@WebServlet("/deleteServlet")
public class deleteServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");

        Dao dao=new Dao();
        if(dao.delete(request.getParameter("delete"))) {
           //request.setAttribute("message鈥濓紝"鍒犻櫎鎴愬姛");
            //response.sendRedirect("index.jsp");
            request.setAttribute("message", "删除成功");
            request.getRequestDispatcher("delete.jsp").forward(request,response);
            //response.sendRedirect("index.jsp");
        }
        else {
            request.setAttribute("message", "删除失败");
            request.getRequestDispatcher("delete.jsp").forward(request,response);
            //response.sendRedirect("index.jsp");//response.sendRedirect("delete.jsp");
        }
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

selectNameServlet.java:

package com.servlet;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.bean.Bean;
import com.dao.Dao;

/**
 * Servlet implementation class selectNameServlet
 */
@WebServlet("/selectNameServlet")
public class selectNameServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
  
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        Bean bean=new Bean();
        bean.setName(request.getParameter("selectname"));
        Dao dao=new Dao();
        bean = dao.selectName(bean.getName());
        if(bean!=null)
        {
            request.setAttribute("Bean",bean);
            request.getRequestDispatcher("select_show.jsp").forward(request,response);
        }
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

showAllServlet.java:

package com.servlet;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.bean.Bean;
import com.dao.Dao;

/**
 * Servlet implementation class selectNameServlet
 */
@WebServlet("/selectNameServlet")
public class selectNameServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
  
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        Bean bean=new Bean();
        bean.setName(request.getParameter("selectname"));
        Dao dao=new Dao();
        bean = dao.selectName(bean.getName());
        if(bean!=null)
        {
            request.setAttribute("Bean",bean);
            request.getRequestDispatcher("select_show.jsp").forward(request,response);
        }
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

updatenewServlet.java:

package com.servlet;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.bean.Bean;
import com.dao.Dao;

/**
 * Servlet implementation class updatenewServlet
 */
@WebServlet("/updatenewServlet")
public class updatenewServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        //response.getWriter().append("Served at: ").append(request.getContextPath());
        request.setCharacterEncoding("UTF-8");
        Bean beannew=new Bean();
        Dao dao=new Dao();
        beannew.setName(request.getParameter("updatename"));
        beannew.setTeacher(request.getParameter("updateteacher"));
        beannew.setClassroom(request.getParameter("updateclassroom"));
        //System.out.println("接受成功");
        //System.out.println(request.getParameter("updatename"));
        //bean.setValue2(Integer.parseInt(request.getParameter("value2")));
        //bean.setValue3(Boolean.parseBoolean(request.getParameter("value3")));
          if(dao.update(beannew)) {
             response.sendRedirect("index.jsp");
        }
        else {
            response.sendRedirect("update_show.jsp");
        }
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

updateServlet:

package com.servlet;

import java.io.IOException;
import java.io.PrintWriter;

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

import com.bean.Bean;
import com.dao.Dao;

/**
 * Servlet implementation class updateServlet
 */
@WebServlet("/updateServlet")
public class updateServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    
        request.setCharacterEncoding("UTF-8");
        Bean bean=new Bean();
        Bean beanform=new Bean();
        Dao dao=new Dao();
        bean = dao.selectName(request.getParameter("selectname"));
         System.out.println(request.getParameter("selectname"));
        if(bean!=null)
        {
            request.setAttribute("Bean",bean);
            request.getRequestDispatcher("update_show.jsp").forward(request,response);
        }
        
//        else if(bean==null) {
//            request.setAttribute("Bean",bean);
//            request.getRequestDispatcher("delete.jsp").forward(request,response);
//            return ;
//        }
            
        
    
//        beanform.setName(request.getParameter("updatename"));
//        beanform.setTeacher(request.getParameter("updateteacher"));
//        beanform.setClassroom(request.getParameter("updateclassroom"));
//         //bean.setValue2(Integer.parseInt(request.getParameter("value2")));
//         //bean.setValue3(Boolean.parseBoolean(request.getParameter("value3")));
//          if(dao.update(beanform)) {
//            //request.setAttribute("message", "娣诲姞鎴愬姛");
//             response.sendRedirect("index.jsp");
//        }
//        else {
//            //request.setAttribute("message","娣诲姞澶辫触");
//            response.sendRedirect("update.jsp");
//        }
    }

}

2.然后继续完成前端页面：

                                  技术图片

 

 代码展示：

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>

           <div>
            <h1 align="center" color="blue"> 增删改查的主页面</h1>
            <hr/>
            <a href="add.jsp"><h2 align="center">添加课程信息</a></h2>
            <a href="delete.jsp"><h2 align="center">删除课程信息</a></h2>
            <a href="update.jsp"><h2 align="center">修改课程信息</h2></a>
            <a href="select.jsp"><h2 align="center">查询课程信息</h2></a>
            <a href="showAllServlet"  ><h2 align="center">浏览课程全部信息</h2></a>
           </div>
        
</body>
</html>

add.jsp:

<%@page import="java.util.jar.Attributes.Name"%>
<%@page import="com.servlet.addServlet" %> 
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加课程</title>
</head>
<body>
<%
  String mess=(String)request.getAttribute("message");
     if(mess!=null&&!"".equals(mess)){
         %>
         <script type="text/javascript">
         alert("<%=mess%>");
         </script>
         <% 
     }
%>
<div align="center">
 <form action="addServlet" method="post">
   <a href="index.jsp"><h2>返回主界面</h2></a>
    <div >
    <table>
      <tr>
             <td>添加课程:</td

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！