JDBC

JDBC笔记
JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。
JDBC为开发人员提供了一个标准的API，据此可以构建更高级的工具和接口，使数据库开发人员能够使用java API编写数据库应用程序，并且可跨平台运行，并且不受数据库供应商的限制。
JDBC为我们提供了java连接数据库的驱动。而这个驱动也是由Java开发出来的，我们只需要将这个驱动放进项目中，通过这个驱动，我们就可以用Java连接数据库，进行数据库的管理操作。
连接、增删改查、建库建表）。
JDBC是SUN开发的java连接数据库的标准
1、准备工作
1.0 数据操作步骤
  加载驱动（只做一次）
  建立数据库连接
  创建执行SQL的语句（Statement）
  执行SQL语句
----如果是查询，需要处理执行的结果（如：接收查询数据）Resultset结果集来接收
  释放资源
1.1 创建java工程
1.2 导入jar包
File>Project Structure>Libraries>"+">Java>jar包
2、JDBC连接数据库
经过上面的准备工作，我们就可以开始使用Java连接数据库了。
2.1 加载JDBC驱动（在新版的jdbc中可以省略此步骤）
在连接数据库之前，首先要加载想要连接的数据库的驱动到JVM（Java虚拟机），这通过java.lang.Class类的静态方法forName(String  className)实现。
        Class.forName("com.mysql.jdbc.Driver");// mysql 5.1
        Class.forName("com.mysql.cj.jdbc.Driver"); // mysql 8.8
2.2 创建连接，创建Connection对象
        // jdbc:mysql: 称为连接协议
        // localhost： mysql服务器的ip地址
        // 3306： 连接端口号
        // test3: 数据库名称
        // serverTimezone=GMT%2B8 设置连接时区与数据库服务器一致 (连接8.0 mysql时新增 )
        String url = "jdbc:mysql://localhost:3306/ku1?serverTimezone=GMT%2B8&useSSL=false"; // 数据库的连接字符串
        String username="root"; // 数据库用户名
        String password="root"; // 数据库的密码
        Connection conn = DriverManager.getConnection(url, username, password);
2.3 创建Statement 对象
 Statement stmt = conn.createStatement();
2.4 执行sql语句（执行增删改或查询）
        stmt.executeUpdate(sql) // 用来执行 insert, update, delete
        stmt.executeQuery(sql)  // 用来执行 select
        stmt.execute(sql)
        executeUpdate方法返回的整数，代表增删改影响的记录行数
        int i = stmt.executeUpdate("insert into dept(deptno,dname,loc) values(50, '研发部', '上海'),(60, '市场部', '杭州')");
        System.out.println("影响行数是：" +i);

       结果集对象
       ResultSet rs = stmt.executeQuery("select deptno,dname,loc from dept");
       rs.next() 返回值是一个boolean 表示是否有下一条记录
        while(rs.next()) {
            int deptno = rs.getInt(1);// getXXX方法的参数，代表第几列,从1开始
            String dname = rs.getString(2);
            String loc = rs.getString(3);
            int deptno = rs.getInt("deptno");
            String dname = rs.getString("dname");
            String loc = rs.getString("loc");
            System.out.println(deptno + "\t" + dname + "\t" + loc);
        }

2.5 关闭释放资源, 先打开的资源最后关闭
        rs.close();
        stmt.close();
        conn.close();
3、数据库操作
数据库操作分为更新和查询两种操作。
我们想要执行SQL语句，就必须要创建一个java.sql.Statement对象，Statement对象可以将SQL语句发送到数据库，并返回一个执行结果（如：添加数据时会返回数据库的影响行数）。
Statement实例分为以下3种类型：
  执行静态SQL语句。通常通过Statement实例实现
  执行动态SQL语句。通常通过PreparedStatement实例实现
3.0 Statement与PreparedStatement
Statement与PreparedStatement两者都可以把SQL语句从Java程序发送到指定数据库，并执行SQL语句，但是他们也具有如下区别：
  Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出；
  PreparedStatement会形成预编译的过程，对于多次重复执行的语句，PreparedStatement的效率要高一些，而且适合批处理数据（批量添加）；
  最重要的是，PreparedStatement能有效防止危险自负的注入，即SQL注入问题。

根据比较结果可知，我们需要选用PreparedStatement更好、更快速、更安全。因此，在以后的JDBC操作中，建议使用PreparedStatement。
Statement接口提供了两种常用的执行SQL语句的方法：executeQuery 、executeUpdate。executeUpdate一般用于添加、删除、修改数据；executeQuery一般用于查询数据。
PreparedStatement接口继承自Statement。PreparedStatement在sql语句中通过使用占位符?来代替常量字段值，setXXX方法来填充字段值，取代掉占位符，形成完整的可执行的sql语句。
3.1 添加数据
  静态SQL添加
         // 用拼接字符串的方法生成sql会导致安全漏洞，SQL注入攻击漏洞
    public static boolean login(String username, String password) throws SQLException {
        Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        Statement stmt = conn.createStatement();
        // 拼接字符串的办法生成了sql语句
        String sql = "select * from user where username='" +username+"' and password='"+password+"'";
        System.out.println(sql);
        ResultSet rs = stmt.executeQuery(sql);
        // 看他是否有下一条记录
        boolean r = rs.next();
        rs.close();
        stmt.close();
        conn.close();
        // 如果有下一个记录，表示找到了，用户名密码都正确
        return r;
 
 
  动态SQL添加
          PreparedStatement 预编译的Statement ，可以避免SQL注入攻击漏洞
    // 让代码的可读性更好
    public static boolean login2(String username, String password) throws SQLException {
        Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        // sql语句是预先准备好的, 用？作为值的占位符

        String sql  = "select * from USER where username=? and password=?";
        PreparedStatement stmt = conn.prepareStatement(sql);
        // 给sql语句中的? 赋值
        stmt.setString(1, username); // 将username变量赋值给sql语句中第一个?， ?下标从1开始
        stmt.setString(2, password); // 不会把其中的关键字，如or进行解析了，把它当做普通值
        ResultSet rs = stmt.executeQuery();
        boolean r = rs.next();
        rs.close();
        stmt.close();
        conn.close();
        return r;
 
3.2 修改数据
语法规则与添加类似
3.3 删除数据
语法规则与添加类似
3.4 事物
把多条sql语句看做一个整体执行，ACID（原子性，一致性，隔离性，持久性）
Connection conn = ...
conn.setAutoCommit( true | false ); // 自动提交, 默认值是true
所谓的自动提交，就是在每条执行的增删改sql语句之后，由jdbc自动加一条commit语句，让事务提交（更改生效）

stmt.executeUpdate("insert into..."); // commit;
stmt.executeUpdate("update ..."); // commit;
stmt.executeUpdate("delete ..."); // commit;

当调用 conn.setAutoCommit(false) 含义就是让事务手动提交, 后续的多条sql就视为一个事务
try {
    stmt.executeUpdate("insert into...");  // ok    
    stmt.executeUpdate("update ..."); // 异常
    conn.commit(); // 手动提交事务（更改生效）
} catch(Exception e) {
    conn.rollback(); // 手动回滚事务
}
例子：
```java
Connection conn = null;
PreparedStatement stmt = null;
try {
    conn = Utils.getConnection();
    // 让事务手动提交（自己来控制事务）
    conn.setAutoCommit(false);
    stmt = conn.prepareStatement("delete from emp where empno=?");
    stmt.setInt(1, 7900);
    stmt.executeUpdate();
    System.out.println("delete from emp where empno=7900");
    int i = 1/0;

    stmt.setInt(1, 7902);
    stmt.executeUpdate();
    System.out.println("delete from emp where empno=7902");

    // 让所有操作都成功了，再commit
    conn.commit();
} catch(Exception e) {
    e.printStackTrace();
    if(conn != null) {
        try {
            // 一旦有异常，回滚之前的操作
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }
} finally {
    Utils.close(stmt, conn);
}
3.5 效率提升
3.5.1充分利用预编译sql
  1) 在jdbc中使用PreparedStatement接口
  2) 对于mysql 需要在连接字符串上加两个参数： &useServerPrepStmts=true&cachePrepStmts=true
  
3.5.2批处理(对增删改提升,对查询无效)
采用批量处理数据的优点：
减少访问数据库的次数，大大提高了批量SQL执行的效率。
  注意：
  对于mysql 要启用批处理功能需要添加参数： &rewriteBatchedStatements=true
```java
public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = Utils.getConnection();
        long start = System.currentTimeMillis();
        stmt = conn.prepareStatement("insert into bigtable(id,name) values(?,?)");
        int max = 500;
        for (int i = 0; i < 1501; i++) {
            stmt.setInt(1,i);
            stmt.setString(2,"aaa");
    //                stmt.executeUpdate(); // 调用一次exeucteUpdate方法，就会跟数据库服务器交互一次
            stmt.addBatch(); // 将insert语句加入至批处理包
            if( (i+1) % 500 == 0 ) { // 批处理包满500发一次
                stmt.executeBatch();
            }
        }
        stmt.executeBatch(); // 将批处理包中所有sql一次性发送给服务器
        long end = System.currentTimeMillis();
        System.out.println(end-start);
    } catch(Exception e) {
        e.printStackTrace();
    } finally {
        Utils.close(stmt, conn);
    }
}
```  
3.5.3 fetchSize 抓包大小(跟查询有关)
mysql 默认查询，会把查询到的所有记录都包装在ResultSet中返回, 当结果记录太多时，效率，内存都会受到影响
需要添加： &useCursorFetch=true&defaultFetchSize=100  来启用基于游标的ResultSet查询
3.5.4从数据库角度考虑效率提升 
对于查询，建立索引：
create index 索引名字 on 表名(列名);
create index idx_bigtable_id on bigtable(id);
1) 对经常做查询的列建索引
2) 索引会影响增删改效率，如果查询操作远远高于增删改，适合建立索引
3) create table 表名( id int primary key )
    主键列上会自动建立索引
4) 外键列上建立了索引，会有助于表连接的效率
5) 查询列如果应用了函数，则不会利用索引
 select * from bigtable where abs(-2) = 263161;
    -2
    -1
    1
    2
    3
6) 模糊查询
select * from 表 where 列 like '%...'  ; /*不会走索引*/
select * from 表 where 列 like 'aaaa%...'  ; /*会走索引*/

ctrl 

3.5.5 数据库连接池
预先创建一些数据库连接，用的时候从连接池借，每次使用完连接，要还回连接池，而不是真正关闭.
连接池的接口和实现：
javax.sql.DataSource;  // 接口
DataSource.getConnection() // 从连接池获取一个空闲的连接， 池连
DriverManager.getConnection() // 直接与数据库建立新的连接， 直连

实现：
各个应用程序服务器
Tomcat 内置了连接池
weblogic 内置了连接池
websphere 内置了连接池

第三方的独立连接池实现
apache dbcp 
c3p0
alibaba druid 德鲁伊
  
小结：
连接池的作用：1. 实现连接的重用 2. 限制了连接的上限，不至于把整个数据库拖垮
涉及到的设计模式：1) 连接的重用体现了享元模式  2) close方法行为的改变体现的是装饰器模式
  
  
 
模板模式  Template 把能够重用的代码留在方法内部，变化的部分作为参数传递进来，以实现代码的重用
参数可以是普通类型，还可以是接口类型（可以包含多条变化的语句）
例如：下面代码可以抽取为：

抽取前：
```java
public void insert(Student stu){
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = Utils.getConnection();
        String sql = "insert into student(sname,birthday,sex)values(?,?,?)";
        System.out.println(sql);
        stmt = conn.prepareStatement(sql);
        // 用 set方法给sql语句中的？占位符赋值
        stmt.setString(1, stu.getSname());
        Date birthday = stu.getBirthday();
        stmt.setDate(2, new java.sql.Date(birthday.getTime()));
        stmt.setString(3, stu.getSex());
        stmt.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally{
        Utils.close(stmt,conn);
    }
}
```
  
抽取后：
```java
public static void update(String sql, Object... args){ // 其中 Object... 等价于 Object[]
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = Utils.getConnection();
        System.out.println(sql);
        stmt = conn.prepareStatement(sql);
        // 用 set方法给sql语句中的？占位符赋值
        int idx = 1;
        // 遍历所有的 ? 值
        for (Object arg : args) {
            if(arg instanceof java.util.Date) {
                java.util.Date date = (java.util.Date) arg;
                java.sql.Date sdate = new java.sql.Date(date.getTime());
                stmt.setObject(idx, sdate);
            } else {
                stmt.setObject(idx, arg);
            }
            idx ++;
        }
        stmt.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally{
        Utils.close(stmt,conn);
    }
}
```
抽取后调用的例子，变得简单了：
```java
String sql = "insert into student(sname,birthday,sex)values(?,?,?)";
update(sql, stu.getSname(), stu.getBirthday(), stu.getSex());

String sql = "update student set sname=?, birthday=?, sex=? where sid=?";
update(sql, stu.getSname(), stu.getBirthday(), stu.getSex(), stu.getSid());

String sql = "delete from student where sid=?";
update(sql, sid);
```
4、小结
  连接数据库时用到的三个类
  静态SQL与动态SQL的区别
  熟练使用动态SQL实现添加、修改、删除数据的功能
  熟练使用批量处理数据