Java--JDBC

JDBC：java database connectivity（Java数据库连接）

SUN公司提供的一套操作数据库的标准规范；它是Java编程语言和广泛的数据库之间独立于数据库的连接标准的Java API，根本上说JDBC是一种规范，它提供一套完整的接口，允许便捷式访问底层数据库管理系统（DBMS），如不同生产产商的数据库管理系统 Mysql、Oracle、SQL Server等

如下图所示：

　　

JDBC与数据库驱动的关系：接口与实现的关系

JDBC四个核心对象：

DriverManager：用于注册驱动

Connection：表示与数据库创建的连接

Statement：操作数据库sql语句的对象

ResultSet：结果集或一张虚拟表

1、在JDBC开发前，先从对应的数据库管理系统官网下载对应的驱动jar包

（1）对于文本编辑器的方式开发，需要将其配置到 环境变量classpath 当中，以mysql为例

    classpath=.;D:\course\06-JDBC\resources\MySql Connector Java 5.1.XX\mysql-connector-java-5.1.23-bin.jar

（2）对于IDEA开发工具，需要导包

选中相应模块，右击，选择 Open Module Settings

选择 Libraries ，选择 Java

选择自己放置相应数据库管理系统的 驱动 jar 包，点击 Apply ，选择 OK即可

2、Java中JDBC编程主要分为六步
（1）注册驱动（作用：告诉Java程序，即将要连接的是哪个品牌的数据库）

（2）获取连接（表示JVM的进程和数据库进程之间的通道打开了，这属于进程之间的通信，重量级的，使用完之后一定要关闭通道。）

（3）获取数据库操作对象（专门执行sql语句的对象）

（4）执行SQL语句（DQL； DML....）

（5）处理查询结果集（只有当第四步执行select语句时，才会有这第五步处理查询结果集）

（6）释放资源（使用完资源之后一定要关闭资源，从小到大关闭，先关闭结果集ResultSet，再关闭操作数据库对象Statement，最后关闭连接Connection）

一、注册驱动

1、DriverManager.registerDriver 注册

//1、注册驱动（连接的数据库）；导入数据库的驱动jar包，如mysql；mysql-connector-java-8.0.27
            //mysql驱动
            Driver driver = new com.mysql.jdbc.Driver();
            //Oracle驱动
//            Driver driver = new oracle.jdbc.driver.OracleDriver();
            DriverManager.registerDriver(driver);

            /* 
             mysql的URL：jdbc:mysql://127.0.0.1:3306/数据库名
             Oracle的URL：jdbc:oracle:thin:@localhost:1521:数据库名
             Sql Server的URL：jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=数据库名
             * */
            String url = "jdbc:mysql://127.0.0.1:3306/mydb";
            String user = "root";
            String pwd = "123456";
            conn = DriverManager.getConnection(url,user,pwd);

一般不建议采用此种方式

（1）查看Driver的源代码可以看到，采用此种方式，会导致驱动程序注册两次，在内存中会有两个Driver对象

（2）程序依赖数据库管理系统厂商的api，如是mysql就需要注册mysql驱动，同理Oracle需要注册Oracle驱动，扩展性不高

2、类加载

Class.forName("com.mysql.jdbc.Driver");

            //2、获取连接（表示JVM的进程和数据库进程之间的通道打开，属于进程之间的通信）
            String url = "jdbc:mysql://127.0.0.1:3306/mydb";
            String user = "root";
            String pwd = "123456";
            conn = DriverManager.getConnection(url,user,pwd);

以mysql为例，查看com.mysql.jdbc.Driver源码，也会调用DriverManager.registerDriver() 方法

                //类加载会调用静态代码块
                com.mysql.jdbc.Driver中静态代码块会被调用
                     static {
                         try {
                            DriverManager.registerDriver(new Driver());
                         } catch (SQLException var1) {
                            throw new RuntimeException("Can't register driver!");
                         }
                     }

3、数据库的URL

URL用于标识数据库的位置，通过URL地址告诉JDBC程序连接哪个数据库，URL的写法为：

jdbc:mysql://127.0.0.1:3306/数据库名

URL：统一资源定位符（网络中某个资源的绝对路径）

URL中包括： 协议  IP  PORT  资源名

如百度URL：http://182.61.200.7:80/index.html

http://         通信协议（通信协议 通信之前提前定好的数据传送格式）

182.61.200.7         服务器IP地址

80 软件端口

index.html 服务器上某个资源名

常用数据库URL如下： 

mysql的URL：jdbc:mysql://127.0.0.1:3306/数据库名

Oracle的URL：jdbc:oracle:thin:@localhost:1521:数据库名

Sql Server的URL：jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=数据库名

4、属性配置文件

可以将数据库驱动，和URL以及用户密码放在配置文件中，这样修改了数据库信息不需要重新编译Java程序

我们新建一个jdbc.properties属性配置文件，如下：

driver=com.mysql.cj.jdbc.Driver
dburl=jdbc:mysql://127.0.0.1:3306/mydb
user=root
pwd=123456

 获取如下：

//资源绑定器加载属性配置文件；不能带 .properties
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("dburl");
        String user = bundle.getString("user");
        String pwd = bundle.getString("pwd");

        Connection conn = null;
        Statement stmt = null;

        //1、注册驱动（连接的数据库）
        try {
            Class.forName(driver);

            //2、获取连接
            conn = DriverManager.getConnection(url,user,pwd);
        }

5、结果集处理

（1）执行DML语句（insert；update；delete）；主要使用Statement 的executeUpdate()方法

public static void main(String[] args) {
        //资源绑定器加载属性配置文件；不能带 .properties
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc/jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("dburl");
        String user = bundle.getString("user");
        String pwd = bundle.getString("pwd");

        Connection conn = null;
        Statement stmt = null;

        //1、注册驱动（连接的数据库）
        try {
            Class.forName(driver);

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

            //3、获取数据库操作对象（专门执行sql语句的对象）
            stmt = conn.createStatement();

            //4、执行SQL语句（DQL DML....）
            String sql = "update dept2 set deptname = 'rh' where deptno = 50";
            int count = stmt.executeUpdate(sql);
            System.out.println("执行结果条数：" + count);

            //5、处理查询结果集（只有当第四步执行的是select语句的时候，才有处理查询结果集）
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6、释放资源；从小到大关闭
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

（2）执行DQL语句（select）；主要使用Statement 的executeQuery()方法

public static void main(String[] args) {
        //资源绑定器加载属性配置文件；不能带 .properties
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc/jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("dburl");
        String user = bundle.getString("user");
        String pwd = bundle.getString("pwd");

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

        try {
            //1、注册驱动（连接的数据库）
            Class.forName(driver);

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

            //3、获取数据库操作对象（专门执行sql语句的对象）
            stmt = conn.createStatement();

            //4、执行SQL语句（DQL DML....）
            String sql = "select empno,empname as name,sal from emp2 where empno=7369";
            rs = stmt.executeQuery(sql);

            //5、处理查询结果集（只有当第四步执行的是select语句的时候，才有处理查询结果集）
            while (rs.next()){
                /*
                （1）下标取值；下标从 1 开始
                */
                String empno = rs.getString(1);
                String empname = rs.getString(2);
                String sal = rs.getString(3);
                System.out.println(empno + " " + empname + " " + sal);

                /*
                （2）数据类型取值
                */
                int empno1 = rs.getInt(1);
                String empname1 = rs.getString(2);
                Double sal1 = rs.getDouble(3);
                System.out.println(empno1 + " " + empname1 + " " + sal1);

                /*
                （3）字段名取值
                */
                String empno2 = rs.getString("empno");
                //如果执行的SQL语句中有别名，需要使用别名字段取值
                String empname2 = rs.getString("name");
                String sal2 = rs.getString("sal");
                System.out.println(empno2 + " " + empname2 + " " + sal2);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6、释放资源；从小到大关闭
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

JDBC编程中，常常会通过ResultSet rs来获得结果集，判断结果集是否为空往往不能直接判断rs == null

通常来说都是用rs.next()来判断结果集是否为空，但是由于执行rs.next()后指针指向的是结果集中的第一条记录，此时再用while(rs.next())取结果集中的数据就会导致第一条数据无法得到

因此用如下方法

if(!rs.next()) { 
    //结果集为空，执行某操作 
} else { 
    //不为空，循环执行某操作 
    //ResultSet对象具有指向其当前数据行的指针，最初指针被置于第一行记录之前，通过next()方法可以                
     将指针移动到下一行记录 
    //将游标移到第一行前 
    rs.beforeFirst(); 
    while(rs.next()){ 
    } 
}

注：rs.next() 若不为空返回true;若是为空则返回false

二、SQL注入；Statement 和 PreparedStatement

SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令

如下，用户在输入

用户名:admin ；密码：admin' or '1'='1 ；登录成功

select * from login_user where loginName = 'admin' and loginPwd = 'admin' or '1'='1'

这种现象被称为SQL注入（存在安全隐患）

2、SQL注入根本原因

用户输入的SQL语句中含有关键字，并且这些关键字参与了SQL语句的编译过程 导致SQL语句原意被修改，进行SQL注入

public static void main(String[] args) {
        //登录界面
        Map<String,String> loginMap = initLoginUI();
        
        //登录业务
        boolean loginSuccess = login(loginMap);
    }

    // 含参数以及返回值注释快捷键  /**，然后Enter
    /**
     * 用户登录
     * @param loginMap
     * @return
     */
    private static boolean login(Map<String, String>loginMap) {
        boolean loginSuccess = false;

        //资源绑定器加载属性配置文件；不能带 .properties
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc/jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("dburl");
        String user = bundle.getString("user");
        String pwd = bundle.getString("pwd");

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

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

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

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

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

            //4、执行SQL语句
            String sql = "select * from login_user where loginName = '"+ loginName +"' and loginPwd = '" + loginPwd +"'";
            rs = stmt.executeQuery(sql);

            //5、处理结果集
            if (rs.next()){
                loginSuccess = true;
                System.out.println("登录成功");
            }else {
                System.out.println("账号密码错误");
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6、释放资源；从小到大关闭
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return loginSuccess;
    }

    /**
     * 用户登录UI
     * @return
     */
    private static Map<String, String> initLoginUI() {
        Scanner sc = new Scanner(System.in);
        System.out.print("用户名:");
        String loginName = sc.nextLine();
        System.out.print("密码：");
        String loginPwd = sc.nextLine();

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

        return loginInfo;
    }

3、解决SQL注入问题

只要用户输入的信息不参与SQL语句的编译过程即可；即使用户输入的信息包含SQL语句关键字，没有参与编译就不会发生SQL注入

java.sql.PreparedStatement 预编译数据库操作对象

java.sql.PreparedStatement 接口 继承了 java.sql.Statement

java.sql.PreparedStatement原理：预先对SQL语句进行编译，再给SQL语句传值

//SQL语句中 问号 ? 标识占位符，一个问号代表一个占位符；一个占位符传一个值；注：占位符不能使用单引号括起来
            String sql = "select * from login_user where loginName = ? and loginPwd = ?";

            //此处，发送SQL语句给DBMS，然后DBMS对SQL语句进行预编译
            //预编译数据里操作对象
            PreparedStatement = conn.prepareStatement(sql);

            //给占位符传值（第一个问号下标是1；JDBC所有下标从1开始）
            pstmt.setString(1,loginName);
            pstmt.setString(2,loginPwd);

            //4、执行SQL语句
            rs = pstmt.executeQuery();

既然 PreparedStatement 可以放置SQL注入现象，那直接 PreparedStatement 对象不是更好，为何还需要 Statement对象呢？存在即合理 ，因为有些业务场景必须使用Statement

 4、Statement 和 PreparedStatement区别

（1）Statement 存在SQL注入问题；PreparedStatement 解决了SQL注入问题

（2）Statement 是编译一次执行一次；PreparedStatement 编译一次，执行N次，PreparedStatement效率更高 在数据库中，如果下一条SQL语句和上一条SQL语句完全一样（包括SQL大小写，空格位置等完全没变，如果增加了一个空格SQL都会重新编译），则下一条SQL语句执行不需要重新编译

（3）PreparedStatement 会在编译阶段做类型的安全检查

5、使用 Statement 场景

如下，我们在对名字排序时，发现使用PreparedStatement 设置参数报错，只能使用Statement 拼接

/**----- 使用PreparedStatement -----*/
            /*String sql = "select * from emp order by empname ?";
            //3、获取预编译数据库操作对象
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"desc");

            //4、执行SQL语句
            rs = pstmt.executeQuery();
            /*
            报错 java.sql.SQLSyntaxErrorException:
            You have an error in your SQL syntax; check the manual that corresponds to
            your MySQL server version for the right syntax to use near ''desc'' at line 1


            //5、处理查询结果集
            while (rs.next()){
                System.out.println(rs.getString("empname"));
            }*/

            /**----- Statement -----*/
            //3、获取数据库操作对象（专门执行sql语句的对象）
            stmt = conn.createStatement();

            //4、执行SQL语句（DQL DML....）
            String key = "desc";
            String sql = "select * from emp order by empname " + key;
            rs = stmt.executeQuery(sql);

            //5、处理查询结果集（只有当第四步执行的是select语句的时候，才有处理查询结果集）
            while (rs.next()){
                System.out.println(rs.getString("empname"));
            }

三、JDBC事务

JDBC中 事务提交机制：自动提交

执行任意一条DML(insert delete update)语句，自动提交一次

如下，我们假设有一个银行账户111111，有余额20000万；111111向银行账户222222转账5000元，程序执行中间出了异常，导致111111账户上少了5000元，但是222222账户余额还是0；因此证明JDBC是自动提交

 关闭自动提交，conn.setAutoCommit(false);

try {
            //1、注册驱动（连接的数据库）
            Class.forName(driver);

            //2、获取连接
            conn = DriverManager.getConnection(url,user,pwd);
            // 将JDBC事务自动提交机制关闭
            conn.setAutoCommit(false);

            String sql = "update bank_account set balance = ? where account_no =?";
            //3、获取预编译数据库操作对象
            pstmt = conn.prepareStatement(sql);
            pstmt.setDouble(1,15000.00);
            pstmt.setInt(2,111111);

            //4、执行SQL语句
            int count = pstmt.executeUpdate();

            //异常
            String str = null;
            str.toString();

            pstmt.setDouble(1,5000.00);
            pstmt.setInt(2,222222);
            int count1 = pstmt.executeUpdate();

            //程序无问题提交事务
            conn.commit();
        } catch (ClassNotFoundException e) {
            //程序有异常，回滚事务
            if (conn != null){
                try {
                    conn.rollback();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            e.printStackTrace();
        }

如果程序无误，才会更新账户余额操作

 

四、DBUtils工具类

package jdbc.utils;

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

public class DBUtil {
    //驱动类名
    private static String driverClassName;
    //数据库URL
    private static String dbUrl;
    //数据库用户名
    private static String userName;
    //数据库密码
    private static String passWord;

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

    //静态代码快在类加载时执行，并且只执行一次
    static {
        try {
            //资源绑定器加载属性配置文件；不能带 .properties
            ResourceBundle bundle = ResourceBundle.getBundle("jdbc/jdbc");
            driverClassName = bundle.getString("driver");
            dbUrl = bundle.getString("dburl");
            userName = bundle.getString("user");
            passWord = bundle.getString("pwd");

            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     * @return 返回连接
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(dbUrl,userName,passWord);
    }

    // 含参数以及返回值注释快捷键  /** Enter
    /**
     * 关闭JDBC
     * @param rs    结果集
     * @param stmt  数据库操作对象
     * @param conn  连接
     */
    public static void close(ResultSet rs, Statement stmt,Connection conn){
        //释放资源；从小到大关闭
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

五、悲观锁和乐观锁

1、悲观锁 和 乐观锁定义

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

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

2、悲观锁

当要对数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制，在修改数据之前先锁定，再修改的方式被称之为悲观并发控制.

简单来说就是指某些数据被锁住了，事务需要这些数据的话，就必须排队获取，在当前事务结束之前，别的事务根本修改不了锁住的数据，不支持并发操作。

语句：SELECT ENAME ,JOB,SAL FROM EMP WHERE JOB='MANAGER' FOR UPDATE； 在select语句后面加了for update就产生了行级锁，所查询出的数据就会被锁住

 在传统的关系型数据库多使用这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作数据之前先上锁。Java 里面的同步 synchronized 关键字的实现。

悲观锁主要分为共享锁和排他锁：

（1）共享锁【shared locks】又称为读锁，简称S锁。顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

（2）排他锁【exclusive locks】又称为写锁，简称X锁。顾名思义，排他锁就是不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务可以对数据行读取和修改。

3、乐观锁

乐观锁支持并发操作，事务不需要排队，只需要获取版本号，查看事务获取数据时和提交时的version号是否一致，一致就提交，不一致就不提交

当前类开启一个事务，这个事务仅做查询，并使用行级锁/悲观锁，锁住数据记录

//当前类开启一个事务，这个事务仅做查询，并使用行级锁/悲观锁，锁住数据记录
public class JDBCPessimisticLock {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            //开启事务
            conn.setAutoCommit(false);

            //在select语句后面加了for update就产生了行级锁，所查询出的数据就会被锁住。
            String sql = "select empname,job,sal from emp2 where job = ? for update";
            pStmt = conn.prepareStatement(sql);
            pStmt.setString(1,"clerk");

            rs = pStmt.executeQuery();

            while (rs.next()){
                System.out.println(rs.getString("empname") + "," + rs.getString("job") + "," + rs.getString("sal"));
            }

            //提交事务（事务结束）;此处断点，没提交事务，另一个update事务无法执行返回结果
            conn.commit();
        } catch (SQLException throwables) {
            if (conn != null){
                //回滚事务（事务结束）
                try {
                    conn.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            throwables.printStackTrace();
        } finally {
            DBUtil.close(rs,pStmt,conn);
        }
    }
}

当前类负责修改被锁定的记录 

//当前类负责修改被锁定的记录
public class JDBCPessimisticLock1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            //开启事务
            conn.setAutoCommit(false);

            String sql = "update emp2 set sal = sal * 1.1 where job = ?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setString(1,"clerk");

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

            //提交事务（事务结束）
            conn.commit();
        } catch (SQLException throwables) {
            if (conn != null){
                //回滚事务（事务结束）
                try {
                    conn.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            throwables.printStackTrace();
        } finally {
            DBUtil.close(rs,pStmt,conn);
        }
    }
}