（JDBC）对数据库的数据进行增删查改

新建表

表数据

表查询

public List<UserInfo> findAll(){
    ResultSet rs=null;
    PreparedStatement statement=null;
    Connection connection=null;
    List<UserInfo> list=new ArrayList<>();
    try {
        connection= DBUtil.getConnection();
        System.out.println("创建连接成功");
        //3.写sql
        String sql="select * from userinfo";
        //4.得到statement对象
        statement = connection.prepareStatement(sql);
        //5.执行sql得到结果集
        rs = statement.executeQuery();
        //6.处理结果集
        while (rs.next()) {
            UserInfo userInfo=new UserInfo();
            userInfo.setId(rs.getInt(1));
            userInfo.setUsername(rs.getString(2));
            userInfo.setPassword(rs.getString(3));
            list.add(userInfo);
        }
       //7.关闭资源
       } catch (Exception e) {
            e.printStackTrace();
       } finally {
          DBUtil.close(rs, statement, connection);
       }
       return list;
}

UserInfo.java

package test.bean;
public class UserInfo {
    private int id;
    private String username;
    private String password;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    @Override
    public String toString() {
        return "UserInfo{" +
            "id=" + id +
            ", username='" + username + '\'' +
            ", password='" + password + '\'' +
            '}';
     }
}

表插入

public void add(){
   PreparedStatement statement=null;
   Connection connection=null;
   try {
        connection=DBUtil.getConnection();
        String sql="insert into userinfo (username,password) values(?,?)";
        statement=connection.prepareStatement(sql);
        statement.setString(1,"aaa");
        statement.setString(2,"bbb");
        statement.executeUpdate();
   } catch (Exception e) {
        e.printStackTrace();
   }finally {
        DBUtil.close(null,statement,connection);
   }
}

表删除

public void delete(){
   ResultSet rs=null;
   PreparedStatement statement=null;
   Connection connection=null;
   try {
        connection=DBUtil.getConnection();
        String sql="delete from userinfo where id='1'";
        statement=connection.prepareStatement(sql);
        statement.executeUpdate();
   } catch (Exception e) {
        e.printStackTrace();
   }finally {
        DBUtil.close(rs, statement, connection);
   }
}

表修改

public void modify(){
   ResultSet rs=null;
   PreparedStatement statement=null;
   Connection connection=null;
   try {
       connection=DBUtil.getConnection();
       String sql="update userinfo set password='ccc' where id='2'";
       statement=connection.prepareStatement(sql);
       statement.executeUpdate();
   } catch (Exception e) {
       e.printStackTrace();
   }finally {
       DBUtil.close(rs, statement, connection);
   }
}

DBUtil.java

package test.util;
import java.sql.*;
public class DBUtil {
    public static Connection getConnection(){
         Connection connection =null;
         try {
             //1.加载驱动
             Class.forName("com.mysql.jdbc.Driver");
             //2.创建连接
             connection = DriverManager.getConnection
                ("jdbc:mysql://127.0.0.1:3306/user?useSSL=true&" +
                        "characterEncoding=utf-8&user=" +
                        "root&password=123");
          }catch (Exception e) {
             e.printStackTrace();
          }
          return connection;
    }
    public static void close(ResultSet rs, Statement statement,Connection connection){
          if (rs != null) {
             try {
                 rs.close();
              } catch (SQLException e) {
                  e.printStackTrace();
              }  
          }
          if (statement != null) {
             try {
                 statement.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
          }
          if (connection != null) {
             try {
                connection.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
          }
    }
}