JDBC

Java database connectivity
是java语言操作数据库的 api (应用程序编程接口)
java.sql.*

接口
Connection 连接代表了java和数据之间的通道，桥梁
Statement 语句可以用来执行 insert, update, delete , select …
ResultSet 结果集代表的是查询的结果

类
DriverManager 工具类，获取连接对象
SQLException 异常对象是 Exception的子类型，属于检查异常

操作步骤顺序

加载驱动 (Driver) jdbc的驱动就是一个连接工厂，生成的产品是连接对象
com.mysql.jdbc.Driver 是Driver的mysql实现类
oracle.jdbc.Driver 是Driver的oracle实现类

Class.forName(“驱动类名”);
例如：
Class.forName(“com.mysql.jdbc.Driver”);

jdbc 3.0 以上版本都可以省略加载驱动这一步

获取连接对象

DriverManager.getConnection(url, 用户名, 密码); // 内部调用了 Driver 对象获取数据库连接

url 的格式 :

jdbc:mysql://ip地址:端口号/数据库名?参数

例如：mysql数据库

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");

创建语句

Statement stmt = conn.createStatement();
执行sql
执行 insert , update, delete

int rows = stmt.executeUpdate()

rows表示该语句影响行数

执行select

ResultSet rs = stmt.executeQuery(); //rs为返回的结构集

取出查询到的元素

while(rs.next()) { 
    rs.getInt("列名");
    rs,getString("列名");
}

释放资源 ( 先打开的资源后关闭 )

rs.close();
stmt.close();
conn.close();

PreparedStatement 预编译语句对象

Statement stmt = conn.createStatement();
stmt.executeUpdate(String sql);

// 1. 需要预先提供sql语句

PreparedStatement psmt = conn.prepareStatement(String sql);

// 2. 可以在sql中用？占位某个值

insert into student(sid,sname,birthday,sex) values(null, ?, ?, ?)

// 3. 给？赋值

使用PreparedStatement中一系列以 set开头的方法
setString(?的位置, 值)
setInt(?的位置, 值)
setDate(?的位置, 值)
setObject((?的位置, 值);
psmt.setString(1, "李四");
psmt.setString(2, "1999-9-7");

// 4. 执行sql

psmt.executeUpdate();

注意：？能够占位的只有值，不能是表名、列名、关键字

SQL注入攻击

create table user (
username varchar(20) primary key,
password varchar(20)
);
insert into user(username, password) values(‘zhangsan’,‘123’);
防范方法：

对参数内存做检查，内部不能有sql关键字例如：or
PreparedStatement

获取自增的主键值

Statement.RETURN_GENERATED_KEYS 是一个选项，表示要返回自增的主键值

String sql = "insert into student(sid,sname,birthday,sex) values(null, ?, ?, ?)"; 
try (PreparedStatement psmt
                         = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS) ) { 
     psmt.setString(1, "aaa");
     psmt.setString(2, "1999-1-1");
     psmt.setString(3, "男");
     int rows = psmt.executeUpdate();
     System.out.println("影响行数:" + rows);
    // 返回一个结果集，结果集中存储了刚刚生成的主键值
       ResultSet rs = psmt.getGeneratedKeys();
      // 就一行一列
      rs.next();
      int id = rs.getInt(1);
      System.out.println("主键值是：" + id);
}

将插入、查询封装成方法

插入

public static void insert(String sql, Object ... values) { 
        try(Connection conn = getConnection()){ 
            try(PreparedStatement psmt = conn.prepareStatement(sql)){ 
                int i = 1;
                // 循环给？参数赋值
                for (Object value : values) { 
                    psmt.setObject(i, value);
                    i++;
                }
                // 执行sql语句
                psmt.executeUpdate();
            }
        } catch (SQLException e) { 
            e.printStackTrace();
        }
    }
public static void main(String[] args) { 
       String sql = "insert into student(sid,sname,birthday,sex) values(null, ?, ?, ?)";
        insert(sql, "张三", "1999-1-1", "男");
    }

查询

//定义接口
public interface RowMapper<T> { 
    public T mapper(ResultSet rs) throws SQLException;
}
public static <T> List<T> findBy(String sql, RowMapper<T> rowMapper, Object... values) { 
        try(Connection conn = getConnection()){ 
            try(PreparedStatement psmt = conn.prepareStatement(sql)){ 
                int i = 1;
                // 循环给？参数赋值
                for (Object value : values) { 
                    psmt.setObject(i, value);
                    i++;
                }
                // 执行sql语句
                try(ResultSet rs = psmt.executeQuery()){ 
                    List<T> list = new ArrayList<>();
                    while(rs.next()) { 
                        // 把rs 中的一行记录变为 java中的一个对象(Student,...)
                        T obj = rowMapper.mapper(rs);
                        list.add(obj);
                    }
                    return list;
                }
            }
        } catch (SQLException e) { 
            e.printStackTrace();
            return Collections.emptyList();
        }
    }
    ```
 ```java
 public static void main(String[] args) { 
        List<Dept> depts = findBy("select * from dept where dname=?", rs -> { 
            int deptno = rs.getInt("deptno");
            String dname = rs.getString("dname");
            String loc = rs.getString("loc");
            return new Dept(deptno, dname, loc);
        }, "市场部");
        for (Dept dept : depts) { 
            System.out.println(dept);
        }
    }
}

事务控制

begin; // 开始事务 start transaction;
多条sql语句

commit; // 提交事务, 最终确认，让所有修改生效
rollback; // 回滚事务，撤销事务内所有的修改

jdbc 默认是让每条sql的执行作为一个独立的事务
让事务变为手动提交：

try { 
    Connection.setAutoCommit(false);
    // 执行多条sql 接下来，事务会在执行第一条 insert, update, delete 时 开始
    // 提交事务
    Connection.commit();
} catch(Exception e ) { 
    // 回滚事务
    Connection.rollback();
}
try(Connection conn = JdbcUtils.getConnection()) { 
    try { 
        // 让事务变成手动提交
        conn.setAutoCommit(false);
        PreparedStatement stmt = conn.prepareStatement("delete from student where sid = ?");
        stmt.setInt(1, 1005);
        // begin
        stmt.executeUpdate();
        int i = 1 / 0; // 出现异常
        stmt.setInt(1, 1007);
        stmt.executeUpdate();
        conn.commit(); // 手动提交事务
    } catch (SQLException e) { 
        conn.rollback(); // 手动回滚事务
    }
} catch (SQLException e) { 
    e.printStackTrace();
}

性能提升

1. 批量增删改

10 万

create table big (
    id int primary key auto_increment,
    name varchar(100)
);

批处理
PreparedStatement.addBatch() 加入批处理包
PreparedStatement.executeBatch() 把批处理包中所有sql，一次性发送给数据库服务器

对于mysql需要通过连接参数，开启批处理功能：
在连接字符串之后添加：

jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true

示例代码：

try(Connection conn = JdbcUtils.getConnection()) { 
    PreparedStatement psmt = conn.prepareStatement("insert into big(name) values(?)");
    long start = System.currentTimeMillis();
    for (int i = 1; i <= 100001; i++) { 
        psmt.setString(1, "张" +i);
        psmt.addBatch(); // 10001 10002 .... ... 19999 20000
        if(i % 10000 == 0) { 
            psmt.executeBatch(); // 发送给服务器并清空批处理包
        }
    }
    psmt.executeBatch(); // 收尾的
    long end = System.currentTimeMillis();
    System.out.println("共花费了:" +(end-start)+"毫秒");
} catch (SQLException e) { 
    e.printStackTrace();
}

2. 查询性能提升

返回记录行数 10000
PreparedStatement.setFetchSize(50);
mysql 中需要在连接参数中加入： useCursorFetch=true，来开启抓取大小的限制功能
还可以给fetchSize一个默认值：连接参数中加入： defaultFetchSize=50

while(rs.next()) {  2001~3000
}

连接池 (connection pool)

apache dbcp 最老牌
c3p0 连接池
alibaba druid (德鲁伊) sql监控

javax.sql.DataSource 连接池接口
Conneciton conn = datasource.getConnection(); 从池中返回连接对象

conn.close(); // 一般都覆盖了关闭方法，将连接归还给连接池，而不是真正关闭
文档地址：
https://github.com/alibaba/druid/wiki/DruidDataSource配置属性列表

用DriverManager 获取连接，称为直连
用DataSource 获取连接，称为池连

DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(“jdbc//localhost:3306/test”);
dataSource.setUsername(“root”);
dataSource.setPassword(“root”);

// dataSource.setDriverClassName(“com.mysql.jdbc.Driver”); 可选步骤，注册驱动
dataSource.setInitialSize(5); // 初始连接数
dataSource.setMaxActive(10); // 最大连接数
dataSource.setMinIdle(5); // 最小连接数
dataSource.setValidationQuery(“select 1”); // 一条简单的sql语句，用来保活
dataSource.setTestWhileIdle(true); // 当空闲时时不时检查一下连接的有效性, 利用ValidationQuery中的sql语句
dataSource.setTimeBetweenEvictionRunsMillis(60*1000); // 默认一分钟

try(Connection conn = dataSource.getConnection()){
```
// conn.close();// 归还连接池了
```
}