MySQL学习总结（5）--- JDBC 实现对数据库的增删改查

MySQL学习总结（5）— JDBC 实现对数据库的增删改查

首先，数据库的创建和连接，以及JDBC工具类的编写同上一篇文章，此篇文章只写核心代码。

1. 增

 @Test
    public void insert() {
    
        Connection connection = null;
        Statement statement = null;

        try {
            //1.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.创建statement对象
            statement = connection.createStatement();
            //3.编写Sql语句
            String sql = "INSERT INTO users(id,NAME,PASSWORD,email,birthday) VALUES(4,'liuhu','234567','liuhu@sina.com','1980-10-05');";
            //4.执行sql语句
            int i = statement.executeUpdate(sql); //返回受影响的行数

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            JDBCUtils.closeAll(null,statement,connection);
        }
    }

2. 删

@Test
    public void delete() {

        Connection connection = null;
        Statement statement = null;

        try {
            //1.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.创建statement对象
            statement = connection.createStatement();
            //3.编写Sql语句
            String sql = "delete from users where id = 5";
            //4.执行sql语句
            int i = statement.executeUpdate(sql); //返回受影响的行数

            if (i>0){
                System.out.println("删除成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            JDBCUtils.closeAll(null,statement,connection);
        }
    }

3. 改

@Test
    public void update() {

        Connection connection = null;
        Statement statement = null;

        try {
            //1.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.创建statement对象
            statement = connection.createStatement();
            //3.编写Sql语句
            String sql = "update users set name = 'qinjiang' where id = 4";
            //4.执行sql语句
            int i = statement.executeUpdate(sql); //返回受影响的行数

            if (i>0){
                System.out.println("修改成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            JDBCUtils.closeAll(null,statement,connection);
        }
    }

4. 查

 public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            //1.获取数据库连接
            connection = JDBCUtils.getConnection();
            //2.创建statement对象
            statement = connection.createStatement();

            //3.编写Sql语句

            System.out.print("请输入你要查询的用户名：");
            String username = scanner.nextLine();


            String sql = "select * from users where name = " +  username;

            System.out.println(sql);

            //select * from users where name = qinjiang or 1=1;


            resultSet = statement.executeQuery(sql);

            while (resultSet.next()){
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            JDBCUtils.closeAll(resultSet,statement,connection);
        }
    }