JDBC
1. JABC概念
- JDBC就是使用Java语言操作关系型数据库的一套API
- java研发者定义一套规则（接口），数据库厂商提供实现类（驱动）
- 面向接口编程
2. JDBC快速入门
2.1 导入驱动jar包
- jar包拷贝进工程里
- Add as library
2.2 mysql.properties
1
2
3
4
| user=root
password=****
url=jdbc:mysql:
driver=com.mysql.cj.jdbc.Driver
|
2.3 JDBC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Date;
import java.util.Properties;
public class ResultSet_ {
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, password);
String sql = "insert t1 values(1,'aw','****',0101)";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);
System.out.println(i);
stmt.close();
conn.close();
}
}
|
3 JDBC API
3.1 DriverManager
用于管理一组JDBC驱动的基本服务。
3.1.1 Driver.class
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| package com.mysql.cj.jdbc;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
public Driver() throws SQLException {
}
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
}
|
3.1.2 DriverManager（驱动管理类）的作用
注册驱动
1
| Class.forName("com.mysql.cj.jdbc.Driver");
|
MySQL 5之后的驱动包，可以省略不写上面这句代码↑，原因：
获取连接
1
| Connection conn = DriverManager.getConnection(url, user, password);
|
url：连接路径
语法：jdbc:mysql://ip地址(域名):端口号/数据库名称?参数键值对1&参数键值对2…
示例：jdbc:mysql://127.0.0.1:3306/db01
细节：
如果连接的是本机mysql服务器，并且mysql服务器默认端口是3306，可简写为：
jdbc:mysql:///数据库名称?参数键值对1&参数键值对2…
配置useSSL=false(别加空格)参数，禁用安全连接方式，解决警告提示
user：用户名
password：密码
3.2 Connection
3.2.1 获取执行SQL的对象
普通执行SQL对象
1
| Statement stmt = conn.createStatement();
|
预编译SQL的执行SQL对象：防止SLQ注入
1
| PreparedStatement ps = conn.prepareStatement(sql);
|
执行存储过程的对象
1
| CallableStatement cs = conn.prepareCall(sql);
|
3.2.2 事务管理
3.3 Statement
作用：执行SQL语句
执行DML、DDL语句
DDL和DML：
- DML：对数据的增删改操作
- DDL：对表和库的增删改查操作
1
| int count = stmt.executeUpdate(sql);
|
返回值：
- DML语句影响的行数
- DDL语句执行后，执行成功也可能返回0
执行DQL语句
DQL：对数据的查询操作
1
| ResultSet rs = stmt.execudtQuery(sql);
|
返回值：ResultSet结果集对象
3.4 ResultSet
ResultSet（结果集对象）：封装了DQL查询语句的结果
1
| ResultSet rs = stmt.execudtQuery(sql);
|
rs.next()
1
2
| bollean flag = rs.next();
|
rs.getXxx()
1
2
3
4
| int id = rs.getInt("id");
String name = rs.getString(2);
String pwd = rs.getString(3);
Date birthday = rs.getDate(4);
|
括号里可以有两种参数：
- int ：列的编号，从1开始
- String：列的名称
ResultSet的使用
1
2
3
4
5
6
7
8
| ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
String pwd = rs.getString(3);
Date birthday = rs.getDate(4);
System.out.println(id + "\t" + name + "\t" + pwd + "\t" + birthday);
}
|
3.5 ResultSet案例
需求：查询account账户表数据，封装为Account对象中，把这些对象存储到ArrayList中
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
| public class Account {
private int id;
private String name;
private double money;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
}
|
1
2
3
4
| user=root
password=****
url=jdbc:mysql:
driver=com.mysql.cj.jdbc.Driver
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
| import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class ResultSetDemo {
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
Connection conn = DriverManager.getConnection(url, user, password);
String sql = "select * from account";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
List<Account> list = new ArrayList<>();
while(rs.next()){
Account account = new Account();
int id = rs.getInt(1);
String name = rs.getString(2);
double money = rs.getDouble(3);
account.setId(id);
account.setName(name);
account.setMoney(money);
list.add(account);
}
rs.close();
stmt.close();
conn.close();
}
}
|
3.6 PreparedStatement
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
| import java.io.FileInputStream;
import java.sql.*;
import java.util.Properties;
public class PreparedStatementDemo {
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
Connection conn = DriverManager.getConnection(url, user, password);
String sql = "select * from account where name = ? and password = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"awsama");
pstmt.setString(2,"13579");
ResultSet rs = pstmt.executeQuery();
System.out.println(rs.next() ? "登陆成功" : "登陆失败" );
rs.close();
pstmt.close();
conn.close();
}
}
|
预编译SQL，性能更高
用Statement重复执行同一条SQL时，每次都要检查语法、编译、再执行
用PreparedStatement重复执行同一条SQL时，只是在第一次的时候完成检查语法、编译、执行，从第二次开始就可以跳过前两步直接执行语句了
PreparedStatement的预编译功能默认是关闭的，开启方式如下：
在url后面加上==useServerPrepStmts=true==
1
2
3
| url = "jdbc:mysql:///db01?useServerPrepStmts=true";
String url = properties.getProperty("url") + "?useServerPrepStmts=true";
|
