数据库增删改查的实现

package bean;

public class Bean {
    private int id;
    private String zhuti;
    private String mudi;
    private String leixing;
    private String time ;
    private String place;
    private String duixiang;
    private String neirong;
    private String anpai;


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getZhuti() {
        return zhuti;
    }

    public void setZhuti(String zhuti) {
        this.zhuti = zhuti;
    }

    public String getMudi() {
        return mudi;
    }

    public void setMudi(String mudi) {
        this.mudi = mudi;
    }

    public String getLeixing() {
        return leixing;
    }

    public void setLeixing(String leixing) {
        this.leixing = leixing;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }

    public String getPlace() {
        return place;
    }

    public void setPlace(String place) {
        this.place = place;
    }

    public String getDuixiang() {
        return duixiang;
    }

    public void setDuixiang(String duixiang) {
        this.duixiang = duixiang;
    }

    public String getNeirong() {
        return neirong;
    }

    public void setNeirong(String neirong) {
        this.neirong = neirong;
    }

    public String getAnpai() {
        return anpai;
    }

    public void setAnpai(String anpai) {
        this.anpai = anpai;
    }
    public Bean() {
        super();
        // TODO 自动生成的构造函数存根
    }
    public Bean(int id, String zhuti, String mudi, String leixing, String time, String place, String duixiang, String neirong, String anpai) {
        super();
        this.id = id;
        this.zhuti = zhuti;
        this.mudi = mudi;
        this.leixing = leixing;
        this.time = time;
        this.place = place;
        this.duixiang = duixiang;
        this.neirong = neirong;
        this.anpai = anpai;
    }
    public Bean( String zhuti, String mudi, String leixing, String time, String place, String duixiang, String neirong, String anpai) {
        super();

        this.zhuti = zhuti;
        this.mudi = mudi;
        this.leixing = leixing;
        this.time = time;
        this.place = place;

　　

package dao;

import Util.DBUtil;
import bean.Bean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Dao {
    public boolean add(Bean ten) throws ClassNotFoundException , SQLException
    {
        String sql="insert into person(zhuti,mudi,leixing,time,place,duixiang,neirong,anpai)values"
                + "('" + ten.getZhuti() + "','" + ten.getMudi() + "','"+ ten.getLeixing() + "','" + ten.getTime() + "','" + ten.getPlace() + "','" + ten.getDuixiang() + "','" + ten.getNeirong() + "','" + ten.getAnpai()+ "')";

        Connection conn= DBUtil.getConnection();
        Statement state=null;
        boolean f=false;
        int a = 0;
        try {
            state = conn.createStatement();
            state.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {

            DBUtil.close(state, conn);
        }
        if(a>0)
            f=true;
        return f;
    }

    public Bean getbyzhuti(String name) throws ClassNotFoundException ,SQLException
    {
        String sql = "select * from shetuan where zhuti ='" + name + "'";
        Connection conn = DBUtil.getConnection();
        Statement state = null;
        ResultSet rs = null;
        Bean ten = null;

        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            while (rs.next()) {
                int id=rs.getInt("id");

                String zhuti2 = rs.getString("zhuti");
                String mudi2 = rs.getString("mudi");
                String leixing2 = rs.getString("leixing");
                String time2=rs.getString("time");
                String place2 = rs.getString("place");
                String duixiang2 =rs.getString("duixiang");
                String neirong2=rs.getString("neirong");
                String anpai2=rs.getString("anpai");

                ten = new Bean(id,zhuti2, mudi2,leixing2,time2,place2, duixiang2, neirong2, anpai2);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, state, conn);
        }

        return ten;
    }


    //delete
    public boolean delete(String name) throws SQLException, ClassNotFoundException {
        String sql="delete from shetuan where zhuti='" + name + "'";
        Connection conn = DBUtil.getConnection();
        Statement state = null;
        int a = 0;
        boolean f = false;
        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;
    }
    //update
    public boolean update(Bean b)
    {
        Connection con=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {
            con=DBUtil.getConnection();
            String sql="update shetuan set zhuti=?,mudi=?,leixing=?,time=?,place=?,duixiang=?,neirong=?,anpai=? where id=?";
            pstmt=con.prepareStatement(sql);
            pstmt.setString(1, b.getZhuti());
            pstmt.setString(2, b.getMudi());
            pstmt.setString(3, b.getLeixing());
            pstmt.setString(4, b.getTime());
            pstmt.setString(5, b.getPlace());
            pstmt.setString(6, b.getDuixiang());
            pstmt.setString(7, b.getNeirong());
            pstmt.setString(7, b.getAnpai());
            pstmt.setInt(8, b.getId());
            pstmt.executeUpdate();
            return true;
        }
        catch (SQLException | ClassNotFoundException e) {
            System.out.println("更新失败");
            e.printStackTrace();
        }
        finally {
            DBUtil.close(rs, pstmt, con);
        }
        return false;
    }
    //查找
    public boolean name(String name) throws SQLException, ClassNotFoundException {
        boolean flag = false;
        String sql = "select zhuti from shetuan where zhuti = '" + name + "'";
        Connection conn = DBUtil.getConnection();
        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 List<Bean> search(String zhuti, String time, String leixing, String place) throws SQLException, ClassNotFoundException {
        String sql = "select * from shetuan where ";


        if (zhuti != "") {
            sql += "zhuti like '%" +zhuti+ "%'";
        }

        if (time != "") {
            sql += "time like '%" +time+ "%'";
        }
        if(leixing!="") {
            sql+="leixing like '%"+leixing+ "%'";
        }

        if(place!="") {
            sql+="place like '%" +place+ "%'";
        }
        List<Bean> list = new ArrayList<>();
        Connection conn = DBUtil.getConnection();
        Statement state = null;
        ResultSet rs = null;
        Bean bean = null;
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            while (rs.next()) {
                String zhuti2 = rs.getString("zhuti");
                String mudi2 = rs.getString("mudi");
                String leixing2 = rs.getString("leixing");
                String time2=rs.getString("time");
                String place2 = rs.getString("place");
                String duixiang2 =rs.getString("duixiang");
                String neirong2=rs.getString("neirong");
                String anpai2=rs.getString("anpai");

                bean = new Bean(zhuti2, mudi2,leixing2,time2,place2, duixiang2, neirong2, anpai2);

                list.add(bean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, state, conn);
        }

        return list;
    }

    public List<Bean> list() throws SQLException, ClassNotFoundException {
        String sql = "select * from shetuan";
        List<Bean> list = new ArrayList<>();
        Connection conn = DBUtil.getConnection();
        Statement state = null;
        ResultSet rs = null;

        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            while (rs.next()) {
                Bean bean = null;
                int id=rs.getInt("id");
                String zhuti2 = rs.getString("zhuti");
                String mudi2 = rs.getString("mudi");
                String leixing2 = rs.getString("leixing");
                String time2=rs.getString("time");
                String place2 = rs.getString("place");
                String duixiang2 =rs.getString("duixiang");
                String neirong2=rs.getString("neirong");
                String anpai2=rs.getString("anpai");

                bean = new Bean(id,zhuti2, mudi2,leixing2,time2,place2, duixiang2, neirong2, anpai2);

                list.add(bean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, state, conn);

　　

package service;

import bean.Bean;
import dao.Dao;

import java.sql.SQLException;
import java.util.List;

public class Service {
    Dao tDao=new Dao();
    public boolean add(Bean ten) throws SQLException, ClassNotFoundException {
        boolean f = false;
        if(!tDao.name(ten.getZhuti()))
        {
            tDao.add(ten);
            f=true;
        }
        return f;
    }

    public boolean del(String zhuti) throws SQLException, ClassNotFoundException {
        tDao.delete(zhuti);
        return true;
    }

    public boolean update(Bean ten)
    {
        tDao.update(ten);
        return true;
    }

    public Bean getbyzhuti(String zhuti) throws SQLException, ClassNotFoundException {
        return tDao.getbyzhuti(zhuti);
    }

    public List<Bean> search(String zhuti, String time, String leixing, String place) throws SQLException, ClassNotFoundException {
        return tDao.search(zhuti,time,leixing,place);
    }


    public List<Bean> list() throws SQLException, ClassNotFoundException {
        return tDao.list();
    }
}

　　

package servlet;

import Util.DBUtil;
import service.Service;

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 java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
@WebServlet("/addServlet")
public class addServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    Service service = new Service();


    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编译格式
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        //接收数据
        String zhuti = request.getParameter("zhuti");
        String mudi = request.getParameter("mudi");
        String leixing = request.getParameter("leixing");
        String time = request.getParameter("time");
        String place = request.getParameter("place");
        String duixiang = request.getParameter("duixiang");
        String neirong = request.getParameter("neirong");
        String anpai = request.getParameter("anpai");

        PrintWriter write = response.getWriter();

            PreparedStatement preparedStatement = null;
            Connection connection = null;
            try {
                connection = DBUtil.getConnection();
                String sql = "insert into shetuan(zhuti,mudi,leixing,time,place,duixiang,neirong,anpai) values(?,?,?,?,?,?,?,?)";
                preparedStatement = null;
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, zhuti);
                preparedStatement.setString(2, mudi);
                preparedStatement.setString(3, leixing);
                preparedStatement.setString(4, time);
                preparedStatement.setString(5, place);
                preparedStatement.setString(6, duixiang);
                preparedStatement.setString(7, neirong);
                preparedStatement.setString(8, anpai);
                preparedStatement.executeUpdate();
            } catch (ClassNotFoundException e) {
                throw new RuntimeException(e);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                DBUtil.close(preparedStatement);
                DBUtil.close(connection);
            }
            request.getRequestDispatcher("liulan.jsp").forward(request,response);
        }


    private boolean isNumeric(String str) {
        for (int i = str.length();--i>=0;){
            if (!Character.isDigit(str.charAt(i))){
                return false;
            }
        }
        return true;
    }
}

　　

package Util;

import java.sql.*;

public class DBUtil {
    public static Connection getConnection() throws ClassNotFoundException, SQLException {

        Connection connection = null;//连接数据库
        Statement stmt = null;//Statement 对象用于将 SQL 语句发送到数据库中。
        ResultSet rs = null;
        //1. 导入驱动jar包
        //2.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
//        好像要设置时区？？
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/CLASS ?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT", "root", "123456");

        return connection;
    }

    public static void close(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void close(PreparedStatement preparedStatement) {
        try {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void close(ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }




    public static void close(Statement state, Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
            if (state != null)
            {
                state.close();
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void close(ResultSet rs, Statement state, Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
            if (state != null)
            {
                state.close();
            }
            if(rs != null)
            {
                rs.close();
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}