JavaWeb学习（Day3）

Day3

1.JDBC基本概念

2.对JDBC中各个接口和类详解

一、JDBC概念

• Java语言操作数据库
• 本质：官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包，真正执行的代码是jar包中的实现类。

二、JDBC入门

public class JdbcDemo1 {
    public static void main(String[] args) throws Exception {
        //1.导入驱动jar包，注册驱动（5后的版本可以省略）
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取数据库连接对象 Connection
        Connection root = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        //3.定义sql语句
        String sql = "update student set Ssex = '男' where Sno = '01'";
        //4.获取执行sql的对象 Statement
        Statement statement = root.createStatement();
        //5.执行sql
        int count = statement.executeUpdate(sql);
        //6.打印处理结果
        System.out.println(count);
        //7.释放资源
        root.close();
        statement.close();
    }
}

1.DriverManager：驱动管理对象

• 注册驱动：告诉程序要使用哪一个数据库驱动jar包

  Class.forName("com.mysql.cj.jdbc.Driver");加载进内存，其中静态代码块直接执行。

• 获取数据库连接：

  DriverManager.getConnection("连接路径", "用户名", "密码");

  连接路径：jdbc:mysql://域名:端口号/ 数据库名称

2.Connection：数据库连接对象

• 获取执行sql的对象

  Statement statement = root.createStatement();

• 管理事务

3.Statement：执行sql对象

4.ResultSet：结果集对象，封装查询结果

5.PreparedStatement：执行sql的对象

6.例子

• 在student表中 添加一条数据

/**
 * 在student表中 添加一条数据
 */
public class JdbcDemo2 {
    public static void main(String[] args) {
        Connection root = null;
        Statement statement = null;
        try {
            root = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            statement = root.createStatement();
            String sql = "insert into student values('02','张三','男')";
            int i = statement.executeUpdate(sql);
            System.out.println(i);
            if(i > 0) {
                System.out.println("执行成功");
            } else {
                System.out.println("执行失败");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                statement.close();
                root.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

• 查询Student表中的所有数据，将一组数据封装为一个对象，将对象封装在List中

//Student.java

public class Student {

    String sno;
    String sname;
    String ssex;

    public Student(String sno, String sname, String ssex) {
        this.sno = sno;
        this.sname = sname;
        this.ssex = ssex;
    }

    public String getSno() {
        return sno;
    }

    public void setSno(String sno) {
        this.sno = sno;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getSsex() {
        return ssex;
    }

    public void setSsex(String ssex) {
        this.ssex = ssex;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sno='" + sno + '\'' +
                ", sname='" + sname + '\'' +
                ", ssex='" + ssex + '\'' +
                '}';
    }
}

//JdbcDemo3.java

public class JdbcDemo3 {

    public List<Student> queryAll() {
        Connection root = null;
        Statement statement = null;
        List<Student> studentList = new ArrayList<Student>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            root = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            statement = root.createStatement();
            String sql = "select * from Student";
            ResultSet resultSet = statement.executeQuery(sql);
            Student student = null;
            while(resultSet.next()) {
                String sno = resultSet.getString("Sno");
                String sname = resultSet.getString("Sname");
                String ssex = resultSet.getString("Ssex");
                student = new Student(sno, sname, ssex);
                studentList.add(student);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                statement.close();
                root.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return studentList;
    }

    public static void main(String[] args) {
        List<Student> studentList = new JdbcDemo3().queryAll();
        for(Student s : studentList) {
            System.out.println(s);
        }
        System.out.println(studentList.size());
    }
}

7.抽取JDBC工具类（简化书写）

用了配置文件

public class JDBCUtils {

    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    /**
     * 使用静态代码块，
     * 原因：只加载一次，不用重复加载
     * */
    static {
        try {
            Properties pro = new Properties();

            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            URL resource = classLoader.getResource("jdbc.properties");
            if (resource != null) {
                String path = resource.getPath();
                pro.load(new FileReader(path));
            } 
            
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");

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

    public static Connection connection() throws SQLException {
            return DriverManager.getConnection(url, user, password);
    }

    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        if(resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

8.sql注入

在拼接sql时，有一些sql的特殊关键字参与字符串的拼接，会造成安全问题。

解决：PreparedStatement对象来解决

预编译的SQL：参数使用？作为占位符

/**
 * 判断用户是否登录成功
 * 若成功，返回：登陆成功
 * 若失败，返回：登录失败
 */
public class JdbcDemo5 {
    public static void main(String[] args) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username = scanner.nextLine();
        System.out.println("请输入密码：");
        String password = scanner.nextLine();
        if(username == null || password == null) {
            System.out.println("用户名或密码为空");
        } else {
            Boolean res = new JdbcDemo5().login(username,password);
            if(res) {
                System.out.println("登陆成功");
            } else {
                System.out.println("登录失败");
            }
        }
    }

    public Boolean login(String username, String password) throws SQLException {
        Connection connection = JDBCUtils.connection();
        /*可能会产生sql注入的写法*/        
        /*ResultSet resultSet = null;
        Statement statement = connection.createStatement();
        String sql = "select * from login where username = '"+username+"' and password = '"+password+"'";
        resultSet = statement.executeQuery(sql);*/
        String sql = "select * from login where username = ? and password = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,username);
        preparedStatement.setString(2,password);
        ResultSet resultSet = preparedStatement.executeQuery();
        Boolean res = resultSet.next();
        JDBCUtils.close(resultSet,preparedStatement,connection);
        return res;
    }
}

9.JDBC控制事务

事务：一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败

使用Connection对象来管理事务

开启事务：connection.setAutoCommit(false);

提交事务：connection.commit();

回滚事务：connection.rollback();

`

/*模拟银行交易，张三给李四打钱*/
public class JdbcDemo6 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement1 = null;
        PreparedStatement preparedStatement2 = null;
        try {
            connection = JDBCUtils.connection();
            //开启事务
            connection.setAutoCommit(false);
            String sql1 = "update money set m = m - ? where name = ?";
            String sql2 = "update money set m = m + ? where name = ?";
            preparedStatement1 = connection.prepareStatement(sql1);
            preparedStatement2 = connection.prepareStatement(sql2);
            preparedStatement1.setInt(1,500);
            preparedStatement1.setString(2,"张三");
            preparedStatement2.setInt(1,500);
            preparedStatement2.setString(2,"李四");
            preparedStatement1.executeUpdate();
            int res = 3/0; //手动制造异常
            preparedStatement2.executeUpdate();
            //提交事务
            connection.commit();
        } catch (Exception throwables) {
            try {
                //事务回滚
                if(connection != null) {
                    connection.rollback();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(null,preparedStatement1,connection);
            JDBCUtils.close(null,preparedStatement2,null);
        }
    }
}