JDBC增删改查

一、处理流程

step1：添加jar包，一定要加载到当前Module下。
为什么要添加jar包：JDBC是sun防败ode数据库连接规范，也就是接口，不同类型数据库实现了这些接口，也就是jar包。
step2: 加载驱动

Class.forName("com.mysql.jdbc.Driver");

**step3：**获取连接

Connection connection = DriverManager.getConnection(url, user, pwd);

step4：编写sql语句

  String sql = "insert into t_fruit values(0, ?, ?, ?, ?)";

**step5：**创建预处理命令对象, PreparedStatement 承载java程序和数据库之间的数据运输，如果Connection是路，那么PreparedStatement就是路上的车

PreparedStatement preparedStatement = connection.prepareStatement(sql);

如果sql语句中含有占位符 “?”，需要对PreparedStatement每个位置填充具体参数值。

preparedStatement.setString(1, "榴莲");
preparedStatement.setInt(2, 15);
preparedStatement.setInt(3, 100);
preparedStatement.setString(4, "很臭");

**step6：**执行
不同语句执行返回结果不同，增删改返回影响行数，查询返回结果集。

int count = preparedStatement.executeUpdate();

step7：释放资源，从小到大释放，resultSet ->preparedStatement->connection。

resultSet2.close();
preparedStatement.close();
connection.close();

二、具体代码

1、创建

public class Demo01 {
    public static void main(String[] args) {
        // 1. 添加jar包
        // 2. 加载驱动 加载 class.forName
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        // 3. 通过驱动管理器，获取连接, 三个参数 url user password
        // ？后面是解决中文乱码问题的 ?useUnicode=true&characterEncoding=utf-8"
        String url = "jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8";
        String user = "root";
        String pwd = "123456";
        try {
            Connection connection = DriverManager.getConnection(url, user, pwd);
            // 编写sql语句 id fname fcount remark
            String sql = "insert into t_fruit values(0, ?, ?, ?, ?)";
            // 创建预处理命令对象, PreparedStatement 承载java程序和数据库之间的数据运输，如果Connection是路，那么PreparedStatement就是路上的车
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            // 填充参数
            preparedStatement.setString(1, "榴莲");
            preparedStatement.setInt(2, 15);
            preparedStatement.setInt(3, 100);
            preparedStatement.setString(4, "很臭");
            // 执行更新（增删改） 返回影响行数
            int count = preparedStatement.executeUpdate();
            System.out.println(count > 0 ? "成功": "失败");
            // 释放资源，先关闭preparedStatement再关闭connection
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

2、更新和删除

public class Demo02 {
    public static void main(String[] args) {
        // 1. 添加jar包
        // 2. 加载驱动 加载 class.forName
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        // 3. 通过驱动管理器，获取连接, 三个参数 url user password
        // ？后面是解决中文乱码问题的 ?useUnicode=true&characterEncoding=utf-8"
        String url = "jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8";
        String user = "root";
        String pwd = "123456";
        try {
            Connection connection = DriverManager.getConnection(url, user, pwd);
            //Fruit fruit = new Fruit(1, "苹果", 5, 300, "苹果很红");
            Fruit fruit = new Fruit(6, "葡萄", 10, 300, "紫色的");
            String sql = "update t_fruit set fname=?, price=?, fcount=?, remark=? where fid=?";

            String deleteSql = "delete from t_fruit where fid= ?";

            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, fruit.getName());
            preparedStatement.setInt(2, fruit.getPrice());
            preparedStatement.setInt(3, fruit.getFcount());
            preparedStatement.setString(4, fruit.getRemark());
            preparedStatement.setInt(5, fruit.getFid());

            int count = preparedStatement.executeUpdate();
            System.out.println(count > 0 ? "执行成功" : "执行失败");
            preparedStatement.close();


            PreparedStatement preparedStatementDelete = connection.prepareStatement(deleteSql);
            preparedStatementDelete.setInt(1, 7);
            int count1 = preparedStatementDelete.executeUpdate();
            System.out.println(count1 > 0 ? "删除执行成功" : "删除执行失败");
            preparedStatementDelete.close();

            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

3、查询

public class Demo03 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 添加驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 获取连接
        String url = "jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8";
        Connection connection = DriverManager.getConnection(url, "root", "123456");

        String sql = "select * from t_fruit";

        String sql1 = "select * from t_fruit where fid = ?";

        String sql2 = "select count(*) from t_fruit";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
        PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
        preparedStatement1.setInt(1, 33);
        // 查询返回的是结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSet resultSet1 = preparedStatement1.executeQuery();
        ResultSet resultSet2 = preparedStatement2.executeQuery();
        // 解析结果集
        List<Fruit> fruits = new ArrayList<>();
        while (resultSet.next()) {
              // 除了用结果集的index， 还可以用列名, 这个列名是结果集中的列名，也就是as后面那个
              Fruit fruit = new Fruit(
                      resultSet.getInt("fid"),
                      resultSet.getString(2),
                      resultSet.getInt(3),
                      resultSet.getInt(4),
                      resultSet.getString(5));
              System.out.println(fruit.toString());
              fruits.add(fruit);
        }
        resultSet.close();

        // 因为只有一条数据，所以只需要if就可以
        while (resultSet1.next()) {
            System.out.println("通过id查询执行");
            Fruit fruit = new Fruit(
                    resultSet1.getInt("fid"),
                    resultSet1.getString(2),
                    resultSet1.getInt(3),
                    resultSet1.getInt(4),
                    resultSet1.getString(5));
            System.out.println(fruit.toString());
        }

        if (resultSet2.next()) {
            System.out.println("数据总条数为"+ resultSet2.getInt(1));
        }

        resultSet1.close();
        resultSet2.close();
        preparedStatement.close();
        connection.close();
    }
}