JDBC基础02 - PD，SQL注入，Statement和PreparedStatement的对比，演示Statement的用途，PreparedStatement完成增删改，JDBC的事务自动提交

• 1、使用IDEA开发JDBC代码配置驱动
  动力节点B站课程链接
• 2、用户登录业务介绍

实现功能;
    1、需求：模拟用户登录功能的实现
    2、业务描述：
        程序运行的时候，提供一个输入的入口，可以让用户输入用户名和密码。
        用户输入用户名和密码之后，提交信息，java程序收集到用户信息。
        Java程序连接到数据库验证用户名和密码是否合法
        合法：显示登录成功
        不合法：显示登录失败
    3、数据的准备：
        在实际开发中，表的涉及会使用专业的建模工具，我们这里安装一个建模工具，PowerDesigner
        使用PD工具来进行数据库表的设计。（参见user-login.sql脚本）

• 3、使用PowerDesigner工具进行物理建模
  动力节点链接
• 4、用户登录功能界面的初始化和登录方法的实现
• 5、SQL注入问题

package com.bjpowernode.jdbc;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.Scanner;

/*
实现功能;
    1、需求：模拟用户登录功能的实现
    2、业务描述：
        程序运行的时候，提供一个输入的入口，可以让用户输入用户名和密码。
        用户输入用户名和密码之后，提交信息，java程序收集到用户信息。
        Java程序连接到数据库验证用户名和密码是否合法
        合法：显示登录成功
        不合法：显示登录失败
    3、数据的准备：
        在实际开发中，表的涉及会使用专业的建模工具，我们这里安装一个建模工具，PowerDesigner
        使用PD工具来进行数据库表的设计。（参见user-login.sql脚本）
    4、当前程序存在的问题：
        用户名： fdsa
        密码： fdsa' or '1'='1
        登录成功
        这种现象被称为SQL注入(安全隐患)。（黑客经常使用）
    5、导致SQL输入的根本原因是什么？
        用户输入的信息中含有sql语句的关键字，并且这些关键字参与sql语句的编译过程。
        导致sql语句的意思被扭曲，进而达到sql注入。
* */
public class JDBCTest06 {
    public static void main(String[] args) {
        // 初始化一个界面
        Map<String,String> userLoginInfo = initUI();
        // 验证用户名和密码
        boolean loginSuccess = login(userLoginInfo);
        // 最后输出结果
        System.out.println(loginSuccess ? "登录成功" : "登录失败");

    }

    /**
     * 用户登录
     * @param userLoginInfo 用户登录信息
     * @return false表示失败，true表示成功
     */
    private static boolean login(Map<String, String> userLoginInfo) {
        // 打标记的意识
        boolean loginSuccess = false;

        // 获取用户的输入信息
        String loginName = userLoginInfo.get("loginName");
        String loginPwd = userLoginInfo.get("loginPwd");

        // JDBC代码
        ResourceBundle bundle = ResourceBundle.getBundle("com/bjpowernode/jdbc/jdbc");
        String driver  = bundle.getString("driver");
        String url = bundle.getString("url");
        String username = bundle.getString("username");
        String password = bundle.getString("password");

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {

            // 1、注册驱动
            Class.forName(driver);

            // 2、获取连接
            conn = DriverManager.getConnection(url,username,password);

            // 3、获取数据库操作对象
            stmt = conn.createStatement();

            // 4、执行sql语句
            // 注意字符串里拼变量，是"+变量名+"
            String sql = "select * from t_user where loginName = '"+loginName+"' and loginPwd = '"+loginPwd+"'";
            // 以上正好完成了sql语句的拼接，以下代码的含义是，发送sql语句给DBMS，DBMS进行sql编译。
            // 正好将用户提供的"非法信息"编译进去，导致了原sql语句的含义被扭曲了。
            rs = stmt.executeQuery(sql);

            // 5、处理查询结果集
            if(rs.next()){
                // 登录成功
                loginSuccess = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6、释放资源
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return loginSuccess;
    }

    /**
     * 初始化用户界面
     * @return 用户输入的用户名和密码等登录信息
     */
    private static Map<String, String> initUI() {
        Scanner s = new Scanner(System.in);
        System.out.print("用户名： ");
        String loginName = s.nextLine();

        System.out.print("密码： ");
        String loginPwd = s.nextLine();

        Map<String,String> userLoginInfo = new HashMap<>();
        userLoginInfo.put("loginName",loginName);
        userLoginInfo.put("loginPwd",loginPwd);

        return userLoginInfo;
    }


}
/*
属性配置文件jdbc.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bjpowernode
username=xxx
password=xxx
*/

• 6、SQL注入问题的解决

package com.bjpowernode.jdbc;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.Scanner;

/**
 * 1、主要解决sql注入问题
 *       只要用户提供的信息不参与sql语句的编译过程，问题就解决了。
 *       即使用户提供的信息中含有SQL语句的关键字，但是没有参与编译，不起作用。
 *       要想用户信息不参与SQL语句的编译，那么必须使用java.sql.PreparedStatement.
 *       PreparedStatement接口继承了java.sql.Statement
 *       PreparedStatement是属于预编译的数据库操作对象
 *       PreparedStatement的原理是：预先对SQL语句的框架进行编译，然后再给SQL语句传“值”。
 * 2、测试结果
 *       用户名： fdsa
 *       密码： fdsa' or '1'='1
 *       登录失败
 * 3、解决SQL注入的关键是什么？
 *    用户提供的信息中即使含有sql语句的关键字，但是这些关键字并没有参与编译，不起作用。
 *
 */

public class JDBCTest07 {
    public static void main(String[] args) {
        // 初始化一个界面
        Map<String,String> userLoginInfo = initUI();
        // 验证用户名和密码
        boolean loginSuccess = login(userLoginInfo);
        // 最后输出结果
        System.out.println(loginSuccess ? "登录成功" : "登录失败");

    }

    /**
     * 用户登录
     * @param userLoginInfo 用户登录信息
     * @return false表示失败，true表示成功
     */
    private static boolean login(Map<String, String> userLoginInfo) {
        boolean loginSuccess = false;

        String loginName = userLoginInfo.get("loginName");
        String loginPwd = userLoginInfo.get("loginPwd");

        ResourceBundle bundle = ResourceBundle.getBundle("com/bjpowernode/jdbc/jdbc");
        String driver  = bundle.getString("driver");
        String url = bundle.getString("url");
        String username = bundle.getString("username");
        String password = bundle.getString("password");

        Connection conn = null;
        PreparedStatement ps = null; // 这里使用哦个PreparedStatement（预编译的数据库操作对象）
        ResultSet rs = null;

        try {
            // 1、注册驱动
            Class.forName(driver);
            // 2、获取连接
            conn = DriverManager.getConnection(url,username,password);
            // 3、获取预编译的数据库操作对象
            // SQL语句的框子。其中一个?：表示一个占位符，一个?将来接收一个“值”，注意：占位符不能使用单引号括起来。
            String sql = "select * from t_user where loginName = ? and loginPwd = ?";
            // 程序执行到此处，会发送sql语句框子给DBMS，然后DBMS进行sql语句的预先编译。
            ps = conn.prepareStatement(sql);
            // 给占位符?传值（第1个问号下标是1，第2个问号下标是2，JDBC中所有下标从1开始。）
            ps.setString(1,loginName);
            ps.setString(2,loginPwd);
            // 4、执行sql语句
            rs = ps.executeQuery();
            // 5、处理查询结果集
            if(rs.next()){
                loginSuccess = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6、释放资源
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return loginSuccess;
    }

    /**
     * 初始化用户界面
     * @return 用户输入的用户名和密码等登录信息
     */
    private static Map<String, String> initUI() {
        Scanner s = new Scanner(System.in);
        System.out.print("用户名： ");
        String loginName = s.nextLine();

        System.out.print("密码： ");
        String loginPwd = s.nextLine();

        Map<String,String> userLoginInfo = new HashMap<>();
        userLoginInfo.put("loginName",loginName);
        userLoginInfo.put("loginPwd",loginPwd);

        return userLoginInfo;
    }

}

• 7、Statement和PreparedStatement的对比

/*
 * 4、对比一下Statement和PrepardStatement？
 *    - Statement存在sql注入问题，PreparedStatement解决了sql注入问题。
 *    - Statement是编译一次执行一次，PrepardStatement是编译一次，可执行n次。PrepardStatement效率较高一些。
 *    - PrepardStatement会在编译阶段做类型的安全检查。
 *    - 综上所述，PrepardStatement使用较多，只有极少数的情况下需要使用Statement。
 * 5、什么情况下必须使用Statement呢？
 *      业务方面要求必须支持SQL注入的时候
 *      Statement支持SQL注入，凡是业务方面要求是需要进行sql语句拼接的，必须使用Statement.

• 8、演示Statement的用途

package com.bjpowernode.jdbc;


import java.util.*;
import java.sql.*;

public class JDBCTest08 {
    public static void main(String[] args) {
        /*
        // 用户在控制台输入desc就是降序，输入asc就是升序。
        Scanner s = new Scanner(System.in);
        System.out.println("输入desc或asc,desc表示降序，asc表示升序");
        System.out.print("请输入： ");
        String keyWords = s.nextLine();

        // 执行sql
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            // 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","0321");

            // 获取预编译的数据库操作对象
            String sql = "select ename from emp order by ename ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,keyWords);
            // 执行SQL
            rs = ps.executeQuery();
            // 遍历结果集
            while(rs.next()){
                System.out.println(rs.getString("ename"));
            }



        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }*/

        // 用户在控制台输入desc就是降序，输入asc就是升序。
        Scanner s = new Scanner(System.in);
        System.out.println("输入desc或asc,desc表示降序，asc表示升序");
        System.out.print("请输入： ");
        String keyWords = s.nextLine();

        // 执行sql
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            // 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","0321");
            // 获取数据库操作对象
            stmt = conn.createStatement();
            // 执行SQL
            String sql = "select ename from emp order by ename "+ keyWords;
            rs = stmt.executeQuery(sql);
            // 遍历结果集
            while(rs.next()){
                System.out.println(rs.getString("ename"));
            }



        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

• 9、PreparedStatement完成增删改

package com.bjpowernode.jdbc;

/**
 * PreparedStatement完成INSERT DELETE UPDATE
 */
import java.sql.*;
import java.util.*;
public class JDBCTest09 {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            // 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","0321");
            // 获取预编译的数据库操作对象
            /*String sql = "insert into dept(deptno,dname,loc) values(?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,60);
            ps.setString(2,"销售部");
            ps.setString(3,"上海");*/
            /*String sql = "update dept set dname=?,loc=? where deptno=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"研发部");
            ps.setString(2,"北京");
            ps.setInt(3,60);*/
            String sql = "delete from dept where deptno = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,60);
            // 执行sql语句
            int count = ps.executeUpdate();
            System.out.println(count);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

• 10、JDBC的事务自动提交机制的演示

package com.bjpowernode.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * JDBC事务机制
 *          1、JDBC中的事务是自动提交的 ，什么是自动提交？
 *              只要执行任意一条DML语句，则自动提交一次。这是JDBC默认的事务行为。
 *              但是在实际的业务当中，通常都是N条DML语句共同联合才能完成的，必须
 *              保证他们这些DML语句在同一个事务中同时成功或者同时失败。
 *          2、以下程序先来验证一下JDBC的事务是否是自动提交机制！
 *              测试结果：JDBC只要执行任意一条DML语句，就提交一次。
 */
public class JDBCTest10 {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            // 1、注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2、获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","0321");
            // 3、获取预编译的数据库操作对象
            String sql = "update dept set dname = ? where deptno = ?";
            ps = conn.prepareStatement(sql);

            // 第一次给占位符传值
            ps.setString(1, "x部门");
            ps.setInt(2,30);
            int count = ps.executeUpdate();// 执行第一条update语句
            System.out.println(count);

            // 重新给占位符传值
            ps.setString(1, "y部门");
            ps.setInt(2,20);
            count = ps.executeUpdate();// 执行第二条update语句
            System.out.println(count);


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6、释放资源
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

• 11、账户转账演示事务

package com.bjpowernode.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * sql脚本
 *  drop table if exists t_act;
 *  create table t_act(
 *      actno int;
 *      balance double(7,2);//注意：7表示有效数字的个数,2表示小数位的个数。
 *  );
 *  insert into t_act(actno,balance) values(111, 20000);
 *  insert into t_act(actno,balance) values(222, 0);
 *  commit;
 *  select * from t_act;
 *
 *  重点三行代码：
 *      conn.setAutoCommit(false);
 *      conn.commit();
 *      conn.rollback();
 */
public class JDBCTest11
{
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            // 1、注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2、获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","0321");
            // 将自动提交机制修改为手动提交
            conn.setAutoCommit(false);// 开启事务

            // 3、获取预编译的数据库操作对象
            String sql = "update t_act set balance = ? where actno = ?";
            ps = conn.prepareStatement(sql);

            // 给?传值
            ps.setDouble(1,10000);
            ps.setInt(2,111);
            int count = ps.executeUpdate();

            //String s = null;
            //s.toString();

            // 给?传值
            ps.setDouble(1,10000);
            ps.setInt(2,222);
            count += ps.executeUpdate();
            System.out.println(count == 2 ? "转账成功" : "转账失败");

            // 程序能走到这里说明以上程序没有异常，事务结束，手动提交数据
            conn.commit();// 提交事务

        } catch (Exception e) {
            // 回滚事务
            // 出现异常，必须要回滚（滚到起点），关闭事务，保证安全性
            if(conn != null){
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 6、释放资源
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

• 12、JDBC工具类的封装

package com.bjpowernode.jdbc.utils;

import java.sql.*;

/**
 * JDBC工具类，简化JDBC编程
 */
public class DBUtil {
    /**
     * 工具类中的构造方法都是私有的。非必须，模仿sun公司代码规范
     * 因为工具类当中的方法都是静态的，不需要new对象，直接采用类名调用。
     */
    private DBUtil(){

    }

    // 静态代码在块类加载时执行，并且只执行一次。
    static{
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接对象
     * @return 返回数据库连接对象
     * @throws SQLException
     */
    public static Connection getConnectin() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","0321");
    }

    /**
     * 释放资源
     * @param conn 连接对象
     * @param stmt 数据库操作对象
     * @param rs 结果集
     */
    public static void close(Connection conn, Statement stmt, ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

• 13、模糊查询like

package com.bjpowernode.jdbc;

import com.bjpowernode.jdbc.utils.DBUtil;

import java.sql.ResultSet;
import java.sql.Statement;

/**
 * 两个任务：
 *  第一，测试DBUtil是否好用
 *  第二，模糊查询怎么使用
 */
import java.sql.*;
public class JDBCTest12 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 获取连接
            conn = DBUtil.getConnectin();
            // 获取预编译的数据库操作对象
            // 错误的写法，会认为?不是占位符
            /*String sql = "select ename from emp where ename like  '_?%'";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"A");*/

            String sql = "select ename from emp where ename like  ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"_A%");
            // 执行sql语句
            rs = ps.executeQuery();
            while(rs.next()){
                System.out.println(rs.getString("ename"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.close(conn,ps,rs);
        }
    }
}

• 14、悲观锁和乐观锁
  • 机制