使用JDBC进行增删改查

1. 添加数据

public class Demo02 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useSSL=false", "root", "123456");
        String sql = "insert into t_fruit values (0,?,?,?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1,"凤梨");
        preparedStatement.setObject(2,7);
        preparedStatement.setObject(3,100);
        preparedStatement.setString(4,"凤梨竟然不是梨");
        int i = preparedStatement.executeUpdate();
        System.out.println((i>0)?"更新成功":"更新失败");
        preparedStatement.close();
        conn.close();
    }
}

1. 修改数据

public class Demo03 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb", "root", "123456");
        String sql = "update t_fruit set fname=?,fcount = ? where fid =?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,"大西瓜");
        ps.setInt(2,20);
        ps.setInt(3,33);
        int i = ps.executeUpdate();
        System.out.println((i>0)?"修改成功":"修改失败");
        ps.close();
        conn.close();

    }
}

1. 删除数据

public class Demo04 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb", "root", "123456");
        String sql = "delete from t_fruit where fid = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,33);
        int i = ps.executeUpdate();
        System.out.println((i>0)?"删除成功":"删除失败");
        ps.close();
        conn.close();
    }
}

• 查询所有结果集

public class Demo05 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb", "root", "123456");
        String sql = "select * from t_fruit";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        List<Fruit> fruitList = new ArrayList<>();
        while (rs.next()){
            int fid = rs.getInt(1);
            String fname = rs.getString(2);
            int price = rs.getInt(3);
            int count = rs.getInt(4);
            String remark = rs.getString(5);
            Fruit fruit = new Fruit(fid,fname,price,count,remark);
            fruitList.add(fruit);
        }
        fruitList.forEach(System.out::println);
        rs.close();
        ps.close();
        conn.close();
    }
}

• 查询指定id的库存记录

public class Demo06 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb", "root", "123456");
        String sql = "select * from t_fruit where fid = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,1);
        ResultSet rs = ps.executeQuery();
        if(rs.next()){
            int fid = rs.getInt(1);
            String fname = rs.getString(2);
            int fprice = rs.getInt(3);
            int fcount = rs.getInt(4);
            String fremask = rs.getString(5);
            Fruit fruit = new Fruit(fid,fname,fprice,fcount,fremask);
            System.out.println(fruit);
        }
        rs.close();
        ps.close();
        conn.close();
    }
}

• 查询总记录条数

public class Demo07 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useSSL=false", "root", "123456");
        String sql = "select count(*) from t_fruit";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()){
            int count = rs.getInt(1);
            System.out.println(count);
        }
        rs.close();
        ps.close();
        conn.close();
    }
}