JDBC

什么是JDBC

JDBC 概念：
　　使用java程序发送sql语句到数据库服务器端执行，用到了JDBC技术。
　　JDBC是Oracle-Sun公司设计的一套专门用于java程序操作关系型数据库的接口（API）。
　　全称：( Java DataBase Connectivity ) Java 数据库连接
JDBC 本质：
　　官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口
　　各个数据库厂商去实现这套接口，提供数据库驱动jar包
　　我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类
JDBC 好处：
　　各数据库厂商使用相同的接口，Java代码不需要针对不同数据库分别开发
　　可随时替换底层数据库，访问数据库的Java代码基本不变(同一套Java代码，操作不同的关系型数据库)

JDBC API 详解

DriverManager(驱动管理类)

1、注册驱动
　　Class.forName("com.mysql.jdbc.Driver");
查看 Driver 类源码：

注：
MySQL 5之后的驱动包，可以省略注册驱动的步骤
自动加载jar包中META-INF/services/java.sql.Driver文件中的驱动类

2、获取数据库连接
　　static Connection getConnection​(String url, String user, String password) 尝试建立与给定数据库URL的连接。
参数：
　　1. url：连接路径
　　　　语法：jdbc:mysql://ip地址(域名):端口号/数据库名称?参数键值对1&参数键值对2…
　　　　示例：jdbc:mysql://127.0.0.1:3306/db1
　　　　细节：
　　　　　　如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称?参数键值对
　　　　　　参数键值对：配置 useSSL=false ，禁用安全连接方式，解决警告提示
　　　　　　　　配置前：　

　　　　　　　　　　

 　　　　　　　　配置后：

　　　　　　　　　　

　　2. user：用户名
　　3. password：密码

Connection(数据库连接对象)

1、获取执行 SQL 的对象
　　普通执行SQL对象
　　　　Statement createStatement()
　　预编译SQL的执行SQL对象：防止SQL注入
　　　　PreparedStatement prepareStatement​ (sql)
2、管理事务
　　MySQL 事务管理
　　　　开启事务：BEGIN; / START TRANSACTION;
　　　　提交事务：COMMIT;
　　　　回滚事务：ROLLBACK;
　　　　MySQL默认自动提交事务
　　JDBC 事务管理：Connection接口中定义了3个对应的方法
　　　　开启事务：setAutoCommit(boolean autoCommit)：true为自动提交事务；false为手动提交事务，即为开启事务
　　　　提交事务：commit()
　　　　回滚事务：rollback()

Statement

作用：执行SQL语句
　　int executeUpdate(sql)：执行DML、DDL语句
　　　　返回值：(1) DML语句影响的行数 (2) DDL语句执行后，执行成功也可能返回 0
　　ResultSet executeQuery(sql)：执行DQL 语句
　　　　返回值： ResultSet 结果集对象

ResultSet(结果集对象)

封装了DQL查询语句的结果
　　ResultSet stmt.executeQuery(sql)：执行DQL 语句，返回 ResultSet 对象
获取查询结果
　　boolean next()：(1) 将光标从当前位置向前移动一行 （2）判断当前行是否为有效行
　　　　返回值：
　　　　　　true：有效行，当前行有数据
　　　　　　false：无效行，当前行没有数据
　　xxx getXxx(参数)：获取数据
　　　　xxx：数据类型；如：int getInt(参数) ; String getString(参数)
　　　　　　参数：
　　　　　　　　int：列的编号，从1开始
　　　　　　　　String：列的名称
使用步骤：
　　1.游标向下移动一行，并判断该行否有数据：next()
　　2.获取数据：getXxx(参数)
　　　　//循环判断游标是否是最后一行末尾
　　　　while(rs.next()){
　　　　　　//获取数据
　　　　　　rs.getXxx(参数);
　　　　}

PreparedStatement

SQL注入

SQL注入是通过操作输入来修改事先定义好的SQL语句，用以达到执行代码对服务器进行攻击的方法

public void testLogin_Inject() throws  Exception {
    //2. 获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
    String url = "jdbc:mysql:///db1?useSSL=false";
    String username = "root";
    String password = "1234";
    Connection conn = DriverManager.getConnection(url, username, password);

    // 接收用户输入 用户名和密码
    String name = "asd";
    String pwd = "' or '1' = '1";

    String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";
    System.out.println(sql);
    // 获取stmt对象
    Statement stmt = conn.createStatement();
    // 执行sql
    ResultSet rs = stmt.executeQuery(sql);

    // 判断登录是否成功
    if(rs.next()){
        System.out.println("登录成功~");
    }else{
        System.out.println("登录失败~");
    }

    //7. 释放资源
    rs.close();
    stmt.close();
    conn.close();
}

会将输入的[' or '1' = '1]默认为sql语句：select * from tb_user where username = 'asd' and password = '' or '1' = '1'
对数据造成影响，不安全。称为sql注入行为。

PreparedStatement

作用：预编译SQL语句并执行：预防SQL注入问题

1.获取 PreparedStatement 对象
　　// SQL语句中的参数值，使用？占位符替代
　　String sql = "select * from user where username = ? and password = ?";
　　// 通过Connection对象获取，并传入对应的sql语句
　　PreparedStatement pstmt = conn.prepareStatement(sql);
2.设置参数值
　　PreparedStatement对象：setXxx(参数1，参数2)：给 ? 赋值
　　Xxx：数据类型 ； 如 setInt(参数1，参数2)
　　　　参数：
　　　　　　参数1： ？的位置编号，从1开始
　　　　　　参数2： ？的值
3.执行SQL
　　executeUpdate(); / executeQuery(); ：不需要再传递sql

PreparedStatement 原理

PreparedStatement好处：
1、预编译SQL，性能更高
　　1.PreparedStatement预编译功能开启：useServerPrepStmts=true

String url = "jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true";

　　2.配置MySQL执行日志(重启mysql服务后生效)
　　　　log-output=FILE
　　　　general-log=1
　　　　general_log_file="D:\mysql.log"
　　　　slow-query-log=1
　　　　slow_query_log_file="D:\mysql_slow.log"
　　　　long_query_time=2
　　原理：
　　　　1.在获取PreparedStatement对象时，将sql语句发送给mysql服务器进行检查SQL语法、编译SQL（这些步骤很耗时）
　　　　2.执行时就不用再进行这些步骤了，速度更快
　　　　3.如果sql模板一样，则只需要进行一次检查、编译
2、防止SQL注入：将敏感字符进行转义
　　会将输入的 [' or '1' = '1] 加转义字符防止SQL注入：
　　select * from tb_user where username = 'hfkjsfhskj' and password = '\' or \'1\' = \'1';

 

使用Statement执行sql语句

JDBC-新增

1  导入jdbc驱动包
2  加载驱动
3  获取数据库连接
4  获取执行sql对象
5  定义sql命令
6  执行sql
7  关闭资源

public class TestInsert {
    public static void main(String[] args){
        //声明jdbc变量
        Connection conn=null;
        Statement stmt=null;
        //声明JDBC参数
            String driver="oracle.jdbc.driver.OracleDriver";
            String url="jdbc:oracle:thin:@localhost:1521:orcl"; //jdbc:jdbc协议；oracle:数据库协议；localhost:主机地址；3306:端口号；orcl:连接的数据库名称
            String username="scott";
            String password="oracle";
        //1 加载驱动
        try {
            Class.forName(driver);
            //2 获取数据库连接(连接指定的数据库)
            conn=DriverManager.getConnection(url,username,password);
            //3 获取执行sql对象(编译和发送sql命令给数据库)
            stmt=conn.createStatement();
            //4 定义sql命令
            String sql="insert into dept values(1,'清华','北京')";
            //5 执行sql
            int i=stmt.executeUpdate(sql);
            System.out.println("执行结果:"+i);
            
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            //6 关闭资源
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        
    }
}

JDBC-修改（与JDBC-新增对比：除sql语句不同外，其他一致）

public class TestUpdate {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2创建连接对象
        Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott","oracle");
        //3创建sql命令对象
        Statement stmt=conn.createStatement();
        //4创建sql命令
        String sname="张三";
        String sql="update student set sname='"+sname+"' where snum=2";
        //5执行sql命令
        int i=stmt.executeUpdate(sql);
        System.out.println(i);
        //6关闭资源
        stmt.close();
        conn.close();
        
    }
}

JDBC-删除（与JDBC-新增对比：除sql语句不同外，其他一致）

public class TestDel {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1 加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
        //2创建连接对象
            Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@LocalHost:1521:orcl", "scott", "oracle");
        //3创建sql命令对象
            Statement stmt=conn.createStatement();
        //4创建sql命令
            String sql="delete from student where snum='2'";
        //5执行sql命令
            int i=stmt.executeUpdate(sql);
            System.out.println("删除数据量:"+i);
        //6关闭资源
            stmt.close();
            conn.close();
    }
}

JDBC-事务管理

示例：张三给李四转账1000元.
注意：JDBC中的事务是自动提交的.
问题：如果在业务的处理过程中,某条Sql语句执行失败,但是数据已经被更改了.
解决：
　　设置JDBC的事务为手动提交.
　　sql语句都执行成功后再统一提交,只要有失败的就回滚.
使用：
　　conn.setAutoCommit(false)//设置为手动提交，开启事务
　　使用try catch进行SQL命令执行的异常处理
　　　　try中是使用conn.commit() 提交数据
　　　　catch中使用conn.rollback() 回滚数据

事务4大特性：
　　1、原子性： 要么一起成功过，要么一起失败
　　2、一致性： 数据库应该从一个一致性的状态到另一个一致性的状态，保持不变
　　3、隔离性： 多个并发事务过程中，希望可以相互隔离
　　　　(1)多个事务并发产生以下现象：
　　　　　　脏读：一个事务读到了另一个事务没有提交的数据
　　　　　　不可重复读：一个事务读到了另一个事务已经提交的更新数据(update) ，导致在一个事务内两次读到的数据是不一样的情况。
　　　　　　幻读：一个事务读到了另一个事务已经提交的插入数据(insert) ，在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，好像发生了幻觉一样，称为幻读。
　　　　(2)数据库设置不同的隔离级别来防止以上现象：(以下√表示是否会导致的问题)
　　　　　　SQL 标准定义了四个隔离级别：
　　　　　　　　READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
　　　　　　　　READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
　　　　　　　　REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
　　　　　　　　SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

　　　　　　

　　　　　　结论： 隔离性越高，数据库的性能越差。 

　　4、持久性： 事务一旦提交，应该永久保持下来。

public class TestTran {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
        //创建连接对象
            Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott","oracle");
        //设置事务为手动提交
            conn.setAutoCommit(false);
        //创建sql命令对象
            Statement stmt=conn.createStatement();
        //创建sql命令
            String sql1="update student set money=money-1000 where snum=6";//转账
            String sql2="update student set money=money2+1000 where snum=7";//入账
        //执行sql命令
            try {
                int i1=stmt.executeUpdate(sql1);
                int i2=stmt.executeUpdate(sql2);
                System.out.println(i1+"----"+i2);
                conn.commit();
            } catch (Exception e) {
                conn.rollback();//数据回滚
            }
        //关闭资源
            stmt.close();
            conn.close();
        
        
    }
}

JDBC-查询

JDBC的查询:
　　加载驱动
　　创建连接对象
　　创建sql命令对象
　　创建sql命令
　　执行sql命令
　　　　返回存储了查询到的数据的对象(ResultSet)
　　　　ResultSet对象是基于指针进行数据存储的,类似枚举.
　　关闭资源
问题:
　　查询的返回值类型是ResultSet,是基于指针进行数据存储的,不便于数据的针对性的获取.
解决:
　　将数据转换到ArrayList中进行存储.
使用:
　　创建和表结构相同的实体类进行单条数据的存储(一条数据就是一个实例化对象)
　　将对象存储到ArrayList中.

创建实体类：

package com.su.pojo;

import java.io.Serializable;

public class Student implements Serializable{
    private int snum;
    private String sname;
    private int sage;
    private double money;
    public int getSnum() {
        return snum;
    }
    public void setSnum(int snum) {
        this.snum = snum;
    }
    public String getSname() {
        return sname;
    }
    public void setSname(String sname) {
        this.sname = sname;
    }
    public int getSage() {
        return sage;
    }
    public void setSage(int sage) {
        this.sage = sage;
    }
    public double getMoney() {
        return money;
    }
    public void setMoney(double money) {
        this.money = money;
    }
    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        long temp;
        temp = Double.doubleToLongBits(money);
        result = prime * result + (int) (temp ^ (temp >>> 32));
        result = prime * result + sage;
        result = prime * result + ((sname == null) ? 0 : sname.hashCode());
        result = prime * result + snum;
        return result;
    }
    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Student other = (Student) obj;
        if (Double.doubleToLongBits(money) != Double.doubleToLongBits(other.money))
            return false;
        if (sage != other.sage)
            return false;
        if (sname == null) {
            if (other.sname != null)
                return false;
        } else if (!sname.equals(other.sname))
            return false;
        if (snum != other.snum)
            return false;
        return true;
    }
    @Override
    public String toString() {
        return "Student [snum=" + snum + ", sname=" + sname + ", sage=" + sage + ", money=" + money + "]";
    }
    public Student(int snum, String sname, int sage, double money) {
        super();
        this.snum = snum;
        this.sname = sname;
        this.sage = sage;
        this.money = money;
    }
    public Student() {
        super();
    }
    
}

查询：

public class TestSelect {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //声明List集合
        　  ArrayList<Student> list=new ArrayList<>();
        //加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
        //创建连接对象
            Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","oracle");
        //创建sql命令对象
            Statement stmt=conn.createStatement();
        //创建Sql命令
            String sql="select * from student";
        //执行Sql命令
             ResultSet rs = stmt.executeQuery(sql);
             while(rs.next()){
                 //创建学生对象
                 Student stu=new Student();
                 stu.setSnum(rs.getInt("snum"));
                 stu.setSname(rs.getString("sname"));
                 stu.setSage(rs.getInt("sage"));
                 stu.setMoney(rs.getDouble("money"));
                 //将对象存储到ArrayList中
                 list.add(stu);
             }
        
        System.out.println(list.get(2));

    }
}

使用PreparedStatement执行sql语句

特点:
　　防止sql注入
　　提升sql语句的执行效率(preparedStatement有预编译的过程) 

使用:

　　　　Statement　　　　　　　　　　　　　　PreparedStatement
　　声明集合或者实体类对象(可选-查询)       声明集合或者实体类对象(可选-查询)
　　加载驱动       　　　　　　　　　　　　加载驱动
　　获取连接对象      　　　　　　　　　　 获取连接对象
　　　　　　　　　　　　　　　　　　　   创建sql命令
　　获取SQL命令对象(statement)   　　　　获取SQL命令对象(preparedStatement)
　　创建sql命令      　　　　　　　　　　　给占位符赋值
　　执行sql命令     　　　　　　 　　　　　执行sql命令
　　遍历结果(可选-查询)     　　　　　　　  遍历结果(可选-查询)  
　　关闭资源       　　　　　　　　　　　　关闭资源
　　返回结果      　　　　　　　　 　　　　返回结果

JDBC-新增

public int insUser2() throws ClassNotFoundException, SQLException{
    //加载驱动
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //创建连接对象
    Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "oracle");
    //创建sql命令
    String sql="insert into t_user values(?,?,?)";
    //创建sql命令对象
    PreparedStatement ps=conn.prepareStatement(sql);
    //给占位符赋值
        ps.setInt(1, 7);　　//1代表参数位置，代表第一个？号；7代表参数值
        ps.setString(2,"赵六");
        ps.setString(3,"666");
        //执行sql命令
        int i=ps.executeUpdate();
    //关闭资源
        ps.close();
        conn.close();
    //返回结果
        return i;
}    

注：参数占位符 ？ ，一个问号代表一个参数，从1开始。

JDBC-修改

public int upUser(String uname,int unum) throws ClassNotFoundException, SQLException{
    //加载驱动
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //获取连接对象
    Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "oracle");
    //创建sql命令
    String sql="update t_user set uname=? where unum=?";
    //获取sql命令对象
    PreparedStatement ps=conn.prepareStatement(sql);
    //给占位符赋值
    ps.setString(1, uname);
    ps.setInt(2, unum);
    //执行sql命令
    int i=ps.executeUpdate();
    //关闭资源
    ps.close();
    conn.close();
    //返回结果    
    return i;
}    

JDBC-删除

public int delUser(int unum) throws ClassNotFoundException, SQLException{
    
    //加载驱动
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //获取连接对象
    Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "oracle");
    //创建sql命令
    String sql="delete from t_user where unum=?";
    //获取sql命令对象
    PreparedStatement ps=conn.prepareStatement(sql);
    //给占位符赋值
    ps.setObject(1, unum);
    //执行sql命令
    int i=ps.executeUpdate();
    //关闭资源
    ps.close();
    conn.close();
    //返回结果    
    return i;
    
}

JDBC-查询

public User getUserInfo(String uname,String upwd) throws ClassNotFoundException, SQLException{
    //声明User对象
    User u=null;
    //加载驱动
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //创建连接对象
    Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "oracle");
    //创建sql命令
    String sql="select * from t_user where uname=? and upwd=?";
    //创建sql命令对象
    PreparedStatement ps=conn.prepareStatement(sql);
    //给占位符赋值(占位符从左到右角标从1开始)
    ps.setString(1, uname);
    ps.setString(2, upwd);
    //执行sql命令
    ResultSet rs=ps.executeQuery();
    //遍历查询结果
    while(rs.next()){
        u=new User();
        u.setUnum(rs.getInt("unum"));
        u.setUname(rs.getString("uname"));
        u.setUpwd(rs.getString("upwd"));
        return u;
    }
    //关闭资源
    rs.close();
    ps.close();
    conn.close();
    //返回执行结果
    return u;
}

Statement与PreparedStatement的区别

1、语法不同：Statement只能执行静态的sql。PreparedStatement既可以执行静态的sql语句，也可以执行预编译sql语句。
2、安全性不同：Statement可以被用户进行sql注入，不安全。PreparedStatement不能被用户注入sql，比Statement安全。
3、执行效率不同：Statement不能利用数据库sql缓存功能。PreparedStatement可以利用数据库sql的缓存功能。比Statement执行效率更高。

数据库连接池

数据库连接池简介

数据库连接池是个容器，负责分配、管理数据库连接(Connection)；

它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；

释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。

好处：

　　资源重用

　　提升系统响应速度

　　避免数据库连接遗漏

 

 

数据库连接池实现

标准接口：DataSource
　　官方(SUN) 提供的数据库连接池标准接口，由第三方组织实现此接口。
　　功能：获取连接
　　　　Connection getConnection()
常见的数据库连接池：
　　DBCP
　　C3P0
　　Druid

Druid(德鲁伊)

Druid连接池是阿里巴巴开源的数据库连接池项目
功能强大，性能优秀，是Java语言最好的数据库连接池之一

Driud使用步骤

1、导入jar包 druid-1.1.12.jar

2、定义配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true
username=root
password=1234
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

3、加载配置文件
4、获取数据库连接池对象
5、获取连接

public static void main(String[] args) throws Exception {
    //1. 导入jar包
    //2. 定义配置文件
    //3. 加载配置文件
    Properties prop = new Properties();
    prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
    //4. 获取连接池对象
    DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
    //5. 获取数据库连接 Connection
    Connection connection = dataSource.getConnection();

    System.out.println(connection);
}