JDBC学习

        jdbc:java database connectivity 代表一组接口规范，对所有的数据库进行连接操作

1.获取连接

        1.新建lib文件夹在模块下

        2.导jar包到lib中(mysql-connector-java),并设置依赖

        3.注册驱动

        4.获取连接

        5.关闭连接

public class TestConnect {
    @Test
    public void Test()throws Exception{
//        1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
//        2.获取连接
        Connection connection = DriverManager.
                getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
        System.out.println("connection = " + connection);
//        3.关闭连接
        connection.close();
    }
}

2.jdbc完成添加操作

        1.注册驱动
        2.获取连接
        3.准备sql
        4.创建命令发送器
        5.执行命令获取结果
        6.展示结果
        7.关闭资源

    @Test
    public void Test03()throws Exception{
        Connection connection = DriverManager.
                getConnection("jdbc:mysql:///test", "root", "root");
        String sql = "insert into student values(null,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1,"张三");
        ps.setInt(2,19);
        int i = ps.executeUpdate();
        System.out.println(i>0?"添加成功":"添加失败");
        connection.close();
    }

 3.jdbc完成修改操作

    @Test
    public void Test04()throws Exception{
        Connection connection = DriverManager.
                getConnection("jdbc:mysql:///test", "root", "root");
        String sql = "update student set sname = ? where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1,"张学友");
        ps.setInt(2,4);
        int i = ps.executeUpdate();
        System.out.println(i>0?"修改成功":"修改失败");
        connection.close();
    }

4.jdbc完成删除操作

    @Test
    public void Test05()throws Exception{
        Connection connection = DriverManager.
                getConnection("jdbc:mysql:///test", "root", "root");
        String sql = "delete from student where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1,4);
        int i = ps.executeUpdate();
        System.out.println(i>0?"删除成功":"删除失败");
        connection.close();
    }

5.jdbc完成查询操作

    @Test
    public void Test06() throws  Exception{
        Connection connection = DriverManager.
                getConnection("jdbc:mysql:///test", "root", "root");
        String sql = "select * from student";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        List list = new ArrayList<>();
        while (resultSet.next()){
            int id = resultSet.getInt(1);
            String sname = resultSet.getString(2);
            int age = resultSet.getInt(3);
            Map map = new HashMap();
            map.put("id",id);
            map.put("sname",sname);
            map.put("age",age);
            list.add(map);
        }
        System.out.println("list = " + list);
        connection.close();
    }
}

6.sql注入的问题

        一种常见的安全漏洞，插入恶意的sql代码执行未授权的操作或者获取敏感数据
        会出现问题:(+++)使用键盘输入+拼接id演示sql注入
        措施:(???)防范sql注入不能去使用拼接的方式去添加参数，而是通过设置值的方式去做。sql当中？代表后期的参数