使用JDBC连接数据库

大致流程：

//1. 通过DriverManager来获得数据库连接

try (Connection connection = DriverManager.getConnection("连接URL","用户名","密码");

     //2. 创建一个用于执行SQL的Statement对象

     Statement statement = connection.createStatement()){   //注意前两步都放在try()中，因为在最后需要释放资源！

    //3. 执行SQL语句，并得到结果集

    ResultSet set = statement.executeQuery("select * from 表名");

    //4. 查看结果

    while (set.next()){

        ...

    }

}catch (SQLException e){

    e.printStackTrace();

}

//5. 释放资源，try-with-resource语法会自动帮助我们close

 执行DML操作或DQL操作

import java.sql.*;

public class Main2 {
    public static void main(String[] args) {    //修改student表
        try (Connection connection = DriverManager.getConnection("jdbc:" +
                "mysql://localhost:3306/s?serverTimezone=GMT" +
                "%2B8", "root", "zzp123456");
             Statement statement = connection.createStatement()) {
/*
            @param sql any SQL statement
            @return <code>true</code> if the first result is a <code>ResultSet</code>
             object; <code>false</code> if it is an update count or there are
             no results
 */
//            statement.execute("update  student set ssex='男' where sno ='20200512037';");
//            statement.execute("update  student set ssex='男' where sno ='20200512038';");

            /*
            @param sql an SQL statement to be sent to the database, typically a
       static SQL <code>SELECT</code> statement
            @return a <code>ResultSet</code> object that contains the data produced
        by the given query; never <code>null</code>
             */
//             ResultSet set=statement.executeQuery("select sname,ssex from student;");
//            while (set.next()) {
//                System.out.print(set.getString(1)+" ");
//                System.out.println(set.getString("ssex"));
//            }

            System.out.println(statement.executeUpdate("update student " +
                    "set ssex='女' where sno='20200512037';"));


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

执行批处理操作

当我们要执行很多条语句时，可以不用一次一次地提交，而是一口气全部交给数据库处理，这样会节省很多的时间。

public static void main(String[] args) {    //批处理 增加
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://" +
                "localhost:3306/s?serverTimezone=GMT%2B8","root","zzp123456");
             Statement statement = connection.createStatement()) {
            statement.addBatch("insert into sc values('20200512037',1,80);");
            statement.addBatch("insert into sc values('20200512037',3,82);");
            statement.addBatch("insert into sc values('20200512040',1,80);");
            statement.addBatch("insert into sc values('20200512040',2,60);");
            System.out.println(Arrays.toString(statement.executeBatch()));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
public static void main(String[] args) {    //批处理 删除
        try (Connection connection = DriverManager.getConnection("jdbc:" +
                "mysql://localhost:3306/s?serverTimezone=GMT" +
                "%2B8", "root", "zzp123456");
             Statement statement = connection.createStatement()) {
            statement.addBatch("delete from sc where sno='20200512037';");
            statement.addBatch("delete from sc where sno='20200512040';");
            System.out.println(Arrays.toString(statement.executeBatch()));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

将查询结果映射为对象

public static void main(String[] args) {//将查询结果映射为对象
        try (Connection connection = DriverManager.getConnection("jdbc:" +
                "mysql://localhost:3306/s?serverTimezone=GMT" +
                "%2B8", "root", "zzp123456");
             Statement statement = connection.createStatement()) {
            ArrayList<Student> arrayList = new ArrayList<>();
            ResultSet set = statement.executeQuery("select * from student;");
            while (set.next()) {
                //String name, int age, String dept, String sno, Ssex sex
                arrayList.add(new Student(set.getString("sname"),
                        set.getInt("sage"), set.getString("sdept"),
                        set.getString("sno"), set.getString("ssex")));
            }
            set.close();
            for (Student student : arrayList) System.out.println("{" + student + "}");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

使用PreparedStatement

如果单纯地使用Statement来执行SQL命令，会存在严重的SQL注入攻击漏洞！而这种问题，我们可以使用PreparedStatement来解决。我们需要提前给到PreparedStatement一个SQL语句，并且使用`?`作为占位符，它会预编译一个SQL语句，通过直接将我们的内容进行替换的方式来填写数据。

Test

1111' or 1=1; --

# Test 登陆成功！

使用statement时：输入会转化为

select * from user where username='Test' and pwd='1111' or 1=1; -- '

使用preparedstatement时：输入会转化为

select * from user where username= 'Test' and pwd='123456'' or 1=1; -- ';

案例：

public static void main(String[] args) throws ClassNotFoundException {
    try (Connection connection = DriverManager.getConnection("URL","用户名","密码");
         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 res = statement.executeQuery();
        while (res.next()){
            String username = res.getString(1);
            System.out.println(username+" 登陆成功！");
        }
    }catch (SQLException e){
        e.printStackTrace();
    }
}

管理事务

JDBC默认的事务处理行为是自动提交，所以前面我们执行一个SQL语句就会被直接提交（相当于没有启动事务），所以JDBC需要进行事务管理时，首先要通过Connection对象调用setAutoCommit(false) 方法, 将SQL语句的提交（commit）由驱动程序转交给应用程序负责。

con.setAutoCommit();   //关闭自动提交后相当于开启事务。

// SQL语句

// SQL语句

// SQL语句

con.commit();或 con.rollback();

注意：一旦关闭自动提交，那么现在执行所有的操作如果在最后不进行`commit()`来提交事务的话，那么所有的操作都会丢失，只有提交之后，所有的操作才会被保存！也可以使用`rollback()`来手动回滚之前的全部操作！

public static void main(String[] args) throws ClassNotFoundException {
    try (Connection connection = DriverManager.getConnection("URL","用户名","密码");
         Statement statement = connection.createStatement()){

        connection.setAutoCommit(false);  //关闭自动提交，现在将变为我们手动提交
        statement.executeUpdate("insert into user values ('a', 1234)");
        statement.executeUpdate("insert into user values ('b', 1234)");
        statement.executeUpdate("insert into user values ('c', 1234)");

        connection.commit();   //如果前面任何操作出现异常，将不会执行commit()，之前的操作也就不会生效
    }catch (SQLException e){
        e.printStackTrace();
    }
}

使用回滚操作

public static void main(String[] args) throws ClassNotFoundException {
    try (Connection connection = DriverManager.getConnection("URL","用户名","密码");
         Statement statement = connection.createStatement()){

        connection.setAutoCommit(false);  //关闭自动提交，现在将变为我们手动提交
        statement.executeUpdate("insert into user values ('a', 1234)");
        statement.executeUpdate("insert into user values ('b', 1234)");

        connection.rollback();   //回滚，撤销前面全部操作

        statement.executeUpdate("insert into user values ('c', 1234)");

        connection.commit();   //提交事务（注意，回滚之前的内容都没了）

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

定点回滚

public static void main(String[] args) throws ClassNotFoundException {
    try (Connection connection = DriverManager.getConnection("URL","用户名","密码");
         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.rollback(savepoint);   //回滚到回滚点，撤销前面全部操作

        statement.executeUpdate("insert into user values ('c', 1234)");

        connection.commit();   //提交事务（注意，回滚之前的内容都没了）

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

从事务模式切换为原有的自动提交模式

public static void main(String[] args) throws ClassNotFoundException {
    try (Connection connection = DriverManager.getConnection("URL","用户名","密码");
         Statement statement = connection.createStatement()){

        connection.setAutoCommit(false);  //关闭自动提交，现在将变为我们手动提交
        statement.executeUpdate("insert into user values ('a', 1234)");
        connection.setAutoCommit(true);   //重新开启自动提交，开启时把之前的事务模式下的内容给提交了
        statement.executeUpdate("insert into user values ('d', 1234)");
        //没有commit也成功了！
    }catch (SQLException e){
        e.printStackTrace();
    }