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实现添加、修改、删除数据的功能
熟练使用批量处理数据