JDBC增删改查展示

添加

// 添加
public static boolean add(User user) throws Exception{
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/sys?useSSL=false", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("insert into user (`name`,password,sex) values (?,?,?)");
    statement.setObject(1,user.getName());
    statement.setObject(2,user.getPassword());
    statement.setObject(3,user.getSex());
    int i = statement.executeUpdate();
    connection.close();
    statement.close();
    return i != 0;
}

测试

// 添加
User user = new User("黄忠", "002365", 1);
boolean b = add(user);
System.out.println(b);

根据id来删除

   // 根据id删除
    public static boolean delete(int id) throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/sys?useSSL=false", "root", "123456");
        PreparedStatement statement = connection.prepareStatement("delete from `user` where id =?");
        statement.setObject(1,id);
        int i = statement.executeUpdate();
        connection.close();
        statement.close();
        return i != 0;
    }

测试

// 删除
System.out.println(delete(2));

修改

//修改
public static boolean update(User user) throws Exception{
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/sys?useSSL=false", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("update `user` set `name`=?,password=? sex=? where id=?");
    statement.setObject(1,user.getName());
    statement.setObject(2,user.getPassword());
    statement.setObject(3,user.getSex());
    statement.setObject(4,user.getId());
    int i = statement.executeUpdate();
    connection.close();
    statement.close();
    return i != 0;
}

测试

  // 修改
   User user = new User(2, "小乔", "123654", 0);
   boolean b = update(user);
   System.out.println(b);

查询

//查询
public static List<User> select() throws Exception{
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/sys?useSSL=false", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("select * from user ");
    ResultSet resultSet = statement.executeQuery();
    List<User> list = new ArrayList();
    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String password = resultSet.getString("password");
        int sex = resultSet.getInt("sex");
        list.add(new User(id,name,password,sex));
    }
    resultSet.close();
    connection.close();
    statement.close();
    return list;
}

测试

// 查询
List<User> select = select();
System.out.println(select);

根据 id 来查询用户信息

//根据id查用户信息
public static User selectId(int id) throws Exception{
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/sys?useSSL=false", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("select * from user where id =?");
    statement.setObject(1,id);
    ResultSet resultSet = statement.executeQuery();
    User user = null;
    if (resultSet.next()) {
        int id1 = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String password = resultSet.getString("password");
        int sex = resultSet.getInt("sex");
        user = new User(id1, name, password, sex);
    }
    resultSet.close();
    connection.close();
    statement.close();
    return user;
}

测试

 // 根据id查询信息
 User user = selectId(2);
 System.out.println(user);

查询数据库的总数量

//查询数据库的总数量
public static int countAll() throws Exception{
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/sys?useSSL=false", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("select cout(*) from `user`");
    ResultSet resultSet = statement.executeQuery();
    int count = 0;
    while (resultSet.next()){
        count = resultSet.getInt(1);
    }
    resultSet.close();
    connection.close();
    statement.close();
    return count;
}

测试

 // 查询数据库的总数量
   int i = countAll();
   System.out.println(i);