JDBC

JDBC

java数据库连接，便捷访问底层数据库的接口，通过JDBC访问数据库的不同可执行文件（java应用程序，java applets ，java servlet，jsp），用java代码操作数据库增删改查，存储过程，事务等。

JDK自带一个java.sql包，里面定义了大量接口，不同类型的数据库，都可以通过实现此接口，编写适用于数据库的实现，不同数据库都实现这套标准，称为数据库驱动

---

DriverManager

管理数据库驱动的类，通过它调用getConnection()来进行数据库的链接

Connection

数据库连接对象，通过连接对象来创建一个Statement用于执行SQL语句，有普通Statement和prepareStatement，prepareStatement有效预防SQL注入式攻击

Statement

executeQuery -> DQL

executeUpdate -> DML DDL

批处理

多条语句一次性提交，节省时间

public static void main(String[] args) throws Exception {
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
    Statement statement = connection.createStatement();
    statement.addBatch("insert into student values(7,'火爷',89)");
    statement.addBatch("insert into student values(8,'团长',99)");
    statement.executeBatch();
}

查询结果映射为对象

把查询数据转换为一个实体类来操作

public static void main(String[] args) throws Exception {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        Statement statement = connection.createStatement();
        ResultSet set = statement.executeQuery("select * from student");
        while (set.next()){
            System.out.println(new Student(set.getInt(1), set.getString(2), set.getLong(3)));
        }
    }

public class Student {
    Integer id;
    String name;
    Long age;

    public Student(Integer id, String name, Long age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

利用反射查询结果映射对象

public static void main(String[] args) throws Exception {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        Statement statement = connection.createStatement();
        ResultSet set = statement.executeQuery("select * from student");
        while (set.next()){
            Student student = convert(set, Student.class);
            if (student != null) System.out.println(student);

        }
    }
    private static <T> T convert(ResultSet set,Class<T> tClass)throws Exception{
        Constructor<T> constructor = tClass.getConstructor(tClass.getConstructors()[0].getParameterTypes());
        Class<?>[] param = constructor.getParameterTypes(); //获取参数列表
        Object[] object = new Object[param.length];
        for (int i = 0; i < param.length; i++) {
            object[i] = set.getObject(i+1);
            if (object[i].getClass() != param[i])
                throw new SQLException("错误的类型转换："+object[i].getClass()+"->"+param[i]);

        }
        return constructor.newInstance(object);
    }

登录和SQL注入攻击

模拟登录用户

1. 普通statement

public static void main(String[] args) throws Exception {
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
    Statement statement = connection.createStatement();
    Scanner scanner = new Scanner(System.in);
    ResultSet set = statement.executeQuery("select * from user where username='"+scanner.nextLine()+"'and pwd='"+scanner.nextLine()+"';");
    while (set.next()) {
        String username = set.getString(1);
        System.out.println(username + "登录成功！");
    }
}

错误：输入Test 1111' or 1=1;也会实现登录

2. 使用PreparedStatement

解决SQL注入攻击漏洞问题

public static void main(String[] args) throws Exception {
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
    PreparedStatement statement = connection.prepareStatement("select * from user where username= ? and pwd=?;");
    Scanner scanner = new Scanner(System.in);

    statement.setString(1,scanner.nextLine());
    statement.setString(2,scanner.nextLine());
    System.out.println(statement);
    ResultSet set = statement.executeQuery();
    while (set.next()){
        String username = set.getString(1);
        System.out.println(username + "登录成功！");
    }
}

我们提前给PreparedStatement一个SQL语句，使用？占位符，预编译一个SQL语句，直接将我们的内容替换

自动转义 ' -> \ ' ,最外层有' 包裹内容

管理事务

要关闭自动提交

public static void main(String[] args) throws Exception {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        Statement statement = connection.createStatement();
        connection.setAutoCommit(false);
        statement.executeUpdate("insert into user values ('a', 1234)");
        Savepoint savepoint = connection.setSavepoint();// 创建回滚点
        statement.executeUpdate("insert into user values ('b', 1234)");
//        connection.setAutoCommit(true); // 关闭事务
        connection.rollback(savepoint); // 回滚
        statement.executeUpdate("insert into user values ('c', 1234)");
        connection.commit(); // 提交
    }