eclipse连接mysql数据库实现怎删改查操作实例（附带源码）

package model;

public class User {
    
    private int id;
    private String name;
    private String password;
    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 getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    
    

}

package dao;



import java.util.List;

import model.User;

public interface IUser {
    public void add(User user);
    public void delete(int id);
    public void update(User user);
    public User load(int id);
    public List<User> load();
    public List<User> load(String content);
}

package dao;

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

import com.sun.xml.internal.bind.v2.runtime.Name;

import model.User;
import util.DBUtil;
import util.UserException;


public class UserImp implements IUser{

    
    public void add(User user) {
        
        Connection connection=DBUtil.getConnection();
        
        String sql = "select count(*) from t_user where name = ?";//准备sql语句
        
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        
        try {
            preparedStatement =  connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getName());
            
            resultSet = preparedStatement.executeQuery();
            
            while(resultSet.next()) {
                if (resultSet.getInt(1) > 0) {
                    throw new UserException("用户已存在") ;
                }
            }
            
            String sql1 = "insert into t_user(name,password) value (?,?)";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            
            e.printStackTrace();
        }finally {
            
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        
    }

    
    public void delete(int id) {
        Connection connection = DBUtil.getConnection();
        String sql = "delete from t_user where id = ?";
        PreparedStatement preparedStatement = null;
        
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
        
            e.printStackTrace();
        }finally {
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        
        
    }

    
    public void update(User user) {
        
        Connection connection = DBUtil.getConnection();
        //准备sql语句
        String sql = "update t_user set name = ? , password=? where id = ?";
        //创建语句传输对象
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setInt(3, user.getId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            
            e.printStackTrace();
        }finally {
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        
    }

    public User load(int id) {
        Connection connection = DBUtil.getConnection();
        //准备sql语句
        String sql = "select * from t_user  where id = ?";
        //创建语句传输对象
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                user = new User();
                user.setId(id);
                user.setName(resultSet.getString("name"));;
                user.setPassword(resultSet.getString("password"));
                
            }
        } catch (SQLException e) {
            
            e.printStackTrace();
        }finally {
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        return  user;
    }


    @Override
    public List<User> load() {
        Connection connection = DBUtil.getConnection();
        //准备sql语句
        String sql = "select * from t_user ";
        //创建语句传输对象
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //集合中只能放入user对象
        List<User> users = new ArrayList<User>();
        User user = null;
        try {
        
                preparedStatement = connection.prepareStatement(sql);
            
                
                
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                
                users.add(user);
            }    
        } catch (SQLException e) {
        
            e.printStackTrace();
        }finally {
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        return  users;
    }


    @Override
    public List <User> load(String content) {
        Connection connection = DBUtil.getConnection();
        //准备sql语句
        String sql = "select * from t_user ";
        //创建语句传输对象
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //集合中只能放入user对象
        List<User> users = new ArrayList<User>();
        User user = null;
        try {
            if (content == null || "".equals(content)) {
                preparedStatement = connection.prepareStatement(sql);
            }else {
                sql += "where name like ? ";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, "%"+ content +"%");
                
            }
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                
                users.add(user);
            }    
        } catch (SQLException e) {
        
            e.printStackTrace();
        }finally {
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        return  users;
    }    
}

package filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

public class CharFilter implements Filter{
    String encoding = null;
    public void init(FilterConfig filterConfig) throws ServletException {
        encoding = filterConfig.getInitParameter("encoding");
    }

    

    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
            throws IOException, ServletException {

            request.setCharacterEncoding(encoding);
            chain.doFilter(request, response);
    }

    public void destroy() {
        
    }
}

package util;

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

public class DBUtil {



        public  static  Connection getConnection() {
            /*
             * 加载驱动
             */
            try {
                
                Class.forName("com.mysql.jdbc.Driver").newInstance();
            } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
                
                e.printStackTrace();
            }
            String user = "root";
            String password = "root";
            String url = "jdbc:mysql://localhost:3306/mysql";
            /*
             * 创建连接对象
             */
            Connection connection = null;
            try {
                
                 connection = DriverManager.getConnection(url,user,password);
            } catch (SQLException e) {
                
                e.printStackTrace();
            }
            return connection;
        }
        /*
         * 关闭资源的方法
         */
        
        public static void close(Connection connection) {//关闭连接对象的方法
            try {
                if (connection != null) {
                    connection.close();
                }
                
            } catch (SQLException e) {
                
                e.printStackTrace();
            }
        }
        public static void close(PreparedStatement preparedStatement ) {//关闭语句传输对象的方法
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                
            } catch (SQLException e) {
                
                e.printStackTrace();
            }
        }
        public static void close(ResultSet resultSet ) {//关闭结果集的方法
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                
            } catch (SQLException e) {
                
                e.printStackTrace();
            }
        }
        



    }

package util;



public class UserException extends RuntimeException  
{

    public UserException() {
        super();
        // TODO Auto-generated constructor stub
    }

    public UserException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
        super(message, cause, enableSuppression, writableStackTrace);
        // TODO Auto-generated constructor stub
    }

    public UserException(String message, Throwable cause) {
        super(message, cause);
        // TODO Auto-generated constructor stub
    }

    public UserException(String message) {
        super(message);
        // TODO Auto-generated constructor stub
    }

    public UserException(Throwable cause) {
        super(cause);
        // TODO Auto-generated constructor stub
    }

}

<%@page import="com.sun.org.apache.xalan.internal.xsltc.compiler.sym"%>
<%@page import="dao.UserImp"%>
<%@page import="model.User"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<%
String name=(String)request.getParameter("username");
String password=(String)request.getParameter("pass");
User user=new User();
user.setName(name);
user.setPassword(password);

System.out.print(user.getName());
System.out.print(user.getPassword());

UserImp userImp=new UserImp();
try{
userImp.add(user);}catch(Exception e){}


response.sendRedirect("list.jsp");
%>

<h2>添加成功！</h2>


</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
        <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Insert title here</title>
        
        </head>
    <body>
        <form action="add.jsp" method="post">
            <table  border="1" bordercolor="#FFFF33" align="center" >
            <tr>
            
            <td align="right" colspan="2">用户名<input type="text"  name="username" style="100px;" /></td><br>
            
            </tr>
            <tr>
            <td align="right" colspan="2">密    码<input type="password"  name="pass" style="100px;"/></td>
            </tr>
                <tr>
                    <td align="left" style="60px;height:10px;font-size:10px;">没有用户名?<a href="register.jsp">[注册]</a>一个吧</td>
                    
                    <td align="right"><input type="submit" value="添加"style="100px;height:30px;font-size:16px;"/></td>
                    
                </tr>
                
            </table>
            
        </form>
        
    </body>
</html>

<%@page import="model.User"%>
<%@page import="dao.UserImp"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<%
int id = Integer.parseInt(request.getParameter("id"));
UserImp userImp=new UserImp();
User user=new User();
userImp.delete(id);
response.sendRedirect("list.jsp");
%>


</html>

<%@page import="model.User"%>
<%@page import="java.util.List"%>
<%@page import="dao.UserImp"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%
    UserImp userDao = new UserImp();
    String content=request.getParameter("content");
    if(content==null||"".equals(content)){
        content="";}
    
        List<User> users = userDao.load(content);
    
%>
</head>
<a href="addInput.jsp">继续添加</a>
<body>
    <table align="center" border="1" width="500">
    <tr align="center">
        <td colspan="5" >增删改查</td>
        </tr>
    <tr>
            <form action="list.jsp" method="post">
                <td colspan="4">
                    请输入用户名 : &nbsp;
                    <input type="text" name="content" size="40" value="<%=content%>"/> &nbsp; 
                    <input type="submit" value="搜索" />
                </td>
            </form>
        </tr>
        
        <tr>
            <td>用户编号</td>
            <td>用户名</td>
            <td>用户密码</td>
            <td colspan="2"> 用户操作</td>
        </tr>
        <%
            for( User user : users ){
        %>
        <tr>
            <td> <%=user.getId() %></td>
            <td> <%=user.getName()%></td>
            <td> <%=user.getPassword() %></td>
            
            <td> <a href="delete.jsp?id=<%=user.getId() %>" >删除</a></td>
            <td> <a href="update.jsp?id=<%=user.getId() %>" >更新</a></td>
            
        </tr>
        <%
            }
        %>
    </table>
    
</body>

</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Insert title here</title>
    </head>
<body>
    <form action="addInput.jsp" method="post">
            <table  border="1" bordercolor="#FFFF33" align="center" >
            <tr>
            
            <td align="right" colspan="1">用户名<input type="text"  name="username" style="100px;" /></td><br>
            
            </tr>
            <tr>
            <td align="right" colspan="1">密    码<input type="password"  name="pass" style="100px;"/></td>
            </tr>
            <tr>
            <td align="right" colspan="1">确认密码<input type="password"  name="pass1" style="100px;"/></td>
            </tr>
            <tr>
            <td align="right" colspan="1">手机号<input type="text"  name="phone" style="100px;"/></td>
            </tr>
                <tr>
                    
                    
                    <td align="right"><input type="submit" value="注册"style="100px;height:30px;font-size:16px;"/></td>
                    
                </tr>
                
            </table>
            
        </form>
        
</body>

</html>

<%@page import="model.User"%>
<%@page import="dao.UserImp"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%
int id = Integer.parseInt(request.getParameter("id"));
UserImp userImp=new UserImp();
User user=new User();
user=userImp.load(id);
%>
</head>
    <body>
        <form action="update1.jsp" method="post">
            <table  border="1" bordercolor="#FFFF33" align="center" >
            <tr>
            <input type="hidden"  name="id" style="100px;" value="<%=user.getId()%>"/>
            <td align="right" colspan="2">用户名<input type="text"  name="username" style="100px;" value="<%=user.getName()%>"/></td><br>
            
            </tr>
            <tr>
            <td align="right" colspan="2">密    码<input type="password"  name="pass" style="100px;"/></td>
            </tr>
                <tr>
                    
                    
                    <td align="right"><input type="submit" value="更新"style="100px;height:30px;font-size:16px;"/></td>
                    
                </tr>
                
            </table>
            
        </form>
    </body>
    
</html>

<%@page import="dao.UserImp"%>
<%@page import="model.User"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%
int id = Integer.parseInt(request.getParameter("id"));
String name=request.getParameter("name");
String password=request.getParameter("pass");
UserImp userImp=new UserImp();
User user=new User();
user = userImp.load(id);
//user.setName(name);
user.setPassword(password);


userImp.update(user);
response.sendRedirect("list.jsp");
%>
</head>
    <body>
    
    </body>
</html>