JDBC笔记（二）

JDBC实现模拟用户登录功能

package com.jdbc;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
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> userLogininInfe = initUI();
        //验证用户名和密码
        boolean loginSuccess = login(userLogininInfe);
        //最后输出结果
        System.out.println(loginSuccess ? "登录成功" : "登录失败");
    }

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

        //打标记
        boolean loginSuccess = false;

        //单独定义变量
        String loginName = userLogininInfe.get("loginName");
        String loginPwd = userLogininInfe.get("loginPwd");

        //JDBC代码
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cqd","root","cqd244");
            //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 (ClassNotFoundException | SQLException 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);

        s.close();

        return userLoginInfo;
    }
}

解决SQL注入问题

package com.jdbc;

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

/**
 * 解决SQL注入
 * 只要用户提供的信息不参与sql语句的编译过程，问题就解决了
 * 要想用户信息不参与sql语句的编译，必须使用java.sql.preparedStatement
 * preparedStatement接口继承了java.sql.Statement
 * preparedStatement是属于预编译的数据库操作对象
 * preparedStatement的原理是：预先对sql语句的框架进行编译，然后再给sql语句传值
 *
 * 测试结果：
 * 用户名：fdas
 * 密码：fdas' or '1'='1
 * 登录失败
 *
 * 对比Statement和PreparedStatement
 * 1.Statement存在SQL注入问题，PreparedStatement解决了SQL注入问题
 * 2.Statement是编译一次执行一次，PreparedStatement是编译一次可以执行N次，PreparedStatement效率较高一些
 * 3.PreparedStatement会在编译阶段做类型的安全检查
 *
 * 综上所述：PreparedStatement使用较多，只有极少数的情况下需要使用Statement
 *
 * 什么情况下使用Statement？
 * 业务方面要求必须支持SQL注入的时候
 * Statement支持SQL注入，凡是业务方面要求进行sql语句拼接的，必须使用Statement
 */

public class JDBCTest07 {
    public static void main(String[] args) {

        //初始化界面
        Map<String,String> userLogininInfe = initUI();
        //验证用户名和密码
        boolean loginSuccess = login(userLogininInfe);
        //最后输出结果
        System.out.println(loginSuccess ? "登录成功" : "登录失败");
    }

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

        //打标记
        boolean loginSuccess = false;

        //单独定义变量
        String loginName = userLogininInfe.get("loginName");
        String loginPwd = userLogininInfe.get("loginPwd");

        //JDBC代码
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cqd","root","cqd244");
            //3.获取预编译的数据库操作对象
            //SQL语句的框子，其中一个？表示一个占位符，？将来接收一个值
            //注意：占位符不能使用单引号括起来
            String sql = "select * from t_user where loginName = ? and loginPwd = ?";
            //程序执行到此处会发送sql语句框子到DBMCS，然后DBMS进行sql语句的预先编译
            ps = conn.prepareStatement(sql);
            //给占位符？传值（第一个？下标是1，第二个？下标是2，JDBC中所有下标从1开始）
            ps.setString(1,loginName);
            ps.setString(2,loginPwd);
            //4.执行sql
            rs = ps.executeQuery();
            //5.处理结果集
            if(rs.next()){
                //登录成功
                loginSuccess = true;
            }
        } catch (ClassNotFoundException | SQLException 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);

        s.close();

        return userLoginInfo;
    }
}

使用Statement的例子

package com.jdbc;

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

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 keyWordes = 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/cqd","root","cqd244");
            //获取数据库操作对象
            stmt = conn.createStatement();
            //执行sql
            String sql = " select sname from t_student order by sname " + keyWordes;
           rs = stmt.executeQuery(sql);
            //遍历结果集
            while (rs.next()){
                System.out.println(rs.getString("sname"));
            }
        } 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();
                }
            }
        }
    }
}

PreparedStatement完成INSERT DELETE UPDATE

package com.jdbc;

import java.sql.*;

public class JDBCTest09 {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cqd","root","cqd244");
            //3.获取预编译的数据库操作对象
            /**
             *  插入数据
             *  String sql = " insert into t_student(sno,sname,cno) values(?,?,?) ";
             *  ps = conn.prepareStatement(sql);
             *  ps.setInt(1,5);
             *  ps.setString(2,"zs5");
             *  ps.setInt(3,101);
             *
             *  原先数据
             *  mysql> select * from t_student;
             * +-----+-------+------+
             * | sno | sname | cno  |
             * +-----+-------+------+
             * |   1 | zs1   |  101 |
             * |   2 | zs2   |  102 |
             * |   3 | zs3   |  101 |
             * |   4 | zs4   |  102 |
             * +-----+-------+------+
             *
             * 执行结果
             * mysql> select * from t_student;
             * +-----+-------+------+
             * | sno | sname | cno  |
             * +-----+-------+------+
             * |   1 | zs1   |  101 |
             * |   2 | zs2   |  102 |
             * |   3 | zs3   |  101 |
             * |   4 | zs4   |  102 |
             * |   5 | zs5   |  101 |
             * +-----+-------+------+
             */
            /**
             *  更新数据
             *  String sql = " update t_student set sname = ? where cno = ? ";
             *
             *  把cno是101的sname改成xy
             *  ps = conn.prepareStatement(sql);
             *  ps.setString(1,"xy");
             *  ps.setInt(2,101);
             *
             *  执行结果
             *  mysql> select * from t_student;
             * +-----+-------+------+
             * | sno | sname | cno  |
             * +-----+-------+------+
             * |   1 | xy    |  101 |
             * |   2 | zs2   |  102 |
             * |   3 | xy    |  101 |
             * |   4 | zs4   |  102 |
             * |   5 | xy    |  101 |
             * +-----+-------+------+
             * 5 rows in set (0.00 sec)
             */
            //删除数据
            String sql = " delete from t_student where cno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,101);
            /**
             * 把cno为101的删除
             * mysql> select * from t_student;
             * +-----+-------+------+
             * | sno | sname | cno  |
             * +-----+-------+------+
             * |   2 | zs2   |  102 |
             * |   4 | zs4   |  102 |
             * +-----+-------+------+
             * 2 rows in set (0.00 sec)
             */
            //4.执行sql
            int count = ps.executeUpdate();
            System.out.println(count);//成功几行就输出几

        } 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();
                }
            }
        }
    }
}

JDBC的事务自动提交机制

JDBC事务机制:
JDBC中的事务是自动提交的，只要执行任意一条DML语句，则自动提交一次，这是JDBC默认的事务行为，但是在实际的业务中，通常都是N条DML语句共同联合才能完成的，必须保证它们这些DML语句在同一个事务中同时成功或失败。

IDEA快捷键

按住ALT键拖动复制可忽略注释中的星星
/**
* 
*/
    
批量编辑
ALT + Shift + Insert

package com.jdbc;

import java.sql.*;

/**
 * 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;
 *
 * 重点三行代码：
 *      1.conn.setAutoCommit(false);
 *      2.conn.commit();
 *      3.conn.rollback();
 */
public class JDBCTest10 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cqd","root","cqd244");
            //将自动提交机制修改为手动提交
            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);
            //4.执行sql
            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 (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();
                }
            }
        }
    }
}
/*
修改之前的执行结果：丢了10000块。。。
mysql> select * from t_act;
+-------+----------+
| actno | balance  |
+-------+----------+
|   111 | 20000.00 |
|   222 |     0.00 |
+-------+----------+
2 rows in set (0.00 sec)

mysql> select * from t_act;
+-------+----------+
| actno | balance  |
+-------+----------+
|   111 | 10000.00 |
|   222 |     0.00 |
+-------+----------+

修改之后的执行结果：
mysql> select * from t_act;
+-------+----------+
| actno | balance  |
+-------+----------+
|   111 | 20000.00 |
|   222 |     0.00 |
+-------+----------+
2 rows in set (0.00 sec)

mysql> select * from t_act;
+-------+----------+
| actno | balance  |
+-------+----------+
|   111 | 10000.00 |
|   222 | 10000.00 |
+-------+----------+
2 rows in set (0.00 sec)
 */

JDBC工具类的封装

package com.jdbc.utils;

import java.sql.*;

/**
 * JDBC工具类，简化JDBC编程
 */
public class DBUtil {

    /**
     * 工具类中的构造方法都是私有的
     * 因为工具类当中的方法都是静态的，不需要new对象，直接采用类名调用
     */
    private DBUtil(){}

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

    public static Connection getConnection() throws SQLException {

        return DriverManager.getConnection("jdbc:mysql://localhost:3306/cqd","root","cqd244");
    }

    /**
     * 关闭资源
     * @param conn 连接对象
     * @param ps 数据库操作对象
     * @param rs 结果集
     */
    public static void close(Connection conn, Statement ps, ResultSet rs){
        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();
            }
        }
    }
}

package com.jdbc;

import com.jdbc.utils.DBUtil;
import jdk.nashorn.internal.codegen.DumpBytecode;

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

/**
 * 这个程序两个任务
 *  1.测试DBUtil是否好用
 *  2.模糊查询怎么写？
 */
public class JDBCTest11 {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            //获取连接
            conn = DBUtil.getConnection();

            //获取预编译的数据库操作对象
            //查找第二个字母含有b的登录名（大小写都行）
            String sql = " select loginName from t_user where loginName like ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"_B%");

            rs = ps.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("loginName"));
            }

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

悲观锁和乐观锁

悲观锁：事务必须排队执行，数据锁住了，不允许并发。（行级锁：select语句后面添加for update）

乐观锁：支持并发，事务也不需要排队，只不过需要一个版本号。

package com.jdbc;

import com.jdbc.utils.DBUtil;

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

/**
 * 这个程序开启一个事务，这个事务专门进行查询，并且使用行级锁/悲观锁，锁住相关的记录
 */
public class JDBCTest12 {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            //开启事务
            conn.setAutoCommit(false);
            
            //loginName为abo的加行级锁
            String sql = " select loginName,loginPwd from t_user where loginName = ? for update ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"abo");

            rs = ps.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("loginName") + "," + rs.getInt("loginPwd"));
            }
            //提交事务（事务结束）
            conn.commit();
        } catch (Exception e) {
            if (conn != null) {
                try {
                    //回滚事务（事务结束）
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,rs);
        }
    }
}

package com.jdbc;

import com.jdbc.utils.DBUtil;

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

/**
 * 这个程序负责修改被锁定的记录
 */
public class JDBCTest13 {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);

            //把被行级锁锁住的abo的loginPwd修改为456
            String sql = " update t_user set loginPwd = 456 where loginName = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"abo");

            int count = ps.executeUpdate();
            System.out.println(count);

            conn.commit();

        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
    }
}
/*
    修改前：
    +----+-----------+----------+----------+
    | id | loginName | loginPwd | readName |
    +----+-----------+----------+----------+
    |  1 | abo       | 123      | 闃挎尝    |
    |  2 | bobo      | 123      | 娉㈡尝    |
    +----+-----------+----------+----------+

    修改后：
    +----+-----------+----------+----------+
    | id | loginName | loginPwd | readName |
    +----+-----------+----------+----------+
    |  1 | abo       | 456      | 闃挎尝    |
    |  2 | bobo      | 123      | 娉㈡尝    |
    +----+-----------+----------+----------+
 */