JDBC = Java Database Contectivity
同一套 java 代码操作不同的关系型数据库
入门程序
创建工程,导入 jar 包。工程目录结构:
public class JDBCDemo {
public static void main(String[] args) throws Exception {
// 注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://192.168.93.12:3306/itcast?useSSL=false";
String username = "root";
String password = "syc13140";
// 获取连接
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "update account set money = 1000 where name = 'lisi'";
// 执行器
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);
stmt.close();
conn.close();
}
}
API 详解
DriverManager
工具类,作用:
- 注册驱动,静态代码块中的
registerDriver
- 获取数据库连接:静态方法
getConnection
静态代码块(随着类的加载而执行,而且只执行一次):
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!");
}
}
}
Connection
作用:
- 获取 SQL 的执行对象
- 管理事务
事务案例:
public class JDBCDemo_connection {
public static void main(String[] args) throws Exception {
// 注册驱动
// Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://192.168.93.12:3306/itcast?useSSL=false";
String username = "root";
String password = "syc13140";
// 获取连接
Connection conn = DriverManager.getConnection(url, username, password);
String sql1 = "update account set money = 2000 where name = 'lisi'";
String sql2 = "update account set money = 1000 where name = 'zhangsan'";
// 执行器
Statement stmt = conn.createStatement();
try {
conn.setAutoCommit(false);
int count1 = stmt.executeUpdate(sql1);
int count2 = stmt.executeUpdate(sql2);
conn.commit(); // 提交事务
} catch (Exception e) {
conn.rollback(); // 回滚事务
e.printStackTrace();
}
stmt.close();
conn.close();
}
}
Statement
ResultSet
public class JDBCDemo_ResultSet {
public static void main(String[] args) throws Exception {
// 注册驱动
// Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://192.168.93.12:3306/itcast?useSSL=false";
String username = "root";
String password = "syc13140";
// 获取连接
Connection conn = DriverManager.getConnection(url, username, password);
// 执行器
Statement stmt = conn.createStatement();
String sql1 = "select * from account";
ResultSet res = stmt.executeQuery(sql1);
while (res.next()) {
String name = res.getString("name");
// String name = res.getString(1);
int money = res.getInt(2);
System.out.println(name + " " + money);
}
res.close();
stmt.close();
conn.close();
}
}
案例:查询 account 账户表数据,封装为 Account 对象中,并且存储到 ArrayList 集合中
public class JDBCDemo_ResultSet {
public static void main(String[] args) throws Exception {
// 注册驱动
// Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://192.168.93.12:3306/itcast?useSSL=false";
String username = "root";
String password = "syc13140";
// 获取连接
Connection conn = DriverManager.getConnection(url, username, password);
// 执行器
Statement stmt = conn.createStatement();
String sql1 = "select * from account";
ResultSet res = stmt.executeQuery(sql1);
List<Account> accounts = new ArrayList<>();
while (res.next()) {
Account a = new Account();
String name = res.getString(1);
int money = res.getInt(2);
a.setName(name);
a.setMoney(money);
accounts.add(a);
}
System.out.println(accounts);
res.close();
stmt.close();
conn.close();
}
}
PreparedStatement
预编译 SQL 并执行,防止 SQL 注入问题
SQL 注入:输入预先定义好的 SQL 语句,修改 SQL 的执行逻辑
String name = "daisiqi";
String age = "' or '1' = '1";
String sql1 = "select * from tb_user where name='" + name + "' and age = '" + age + "'";
ResultSet res = stmt.executeQuery(sql1);
if (res.next()) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
上面的 SQL 被拼接为:
select * from tb_user where name='daisiqi' and age = '' or '1'='1';
where 条件恒为真
使用 PreparedStatement 防止 SQL 注入:
String name = "daisiqi";
String age = "19";
String sql1 = "select * from tb_user where name = ? and age = ?";
PreparedStatement pstmt = conn.prepareStatement(sql1);
pstmt.setString(1, name);
pstmt.setString(2, age);
ResultSet res = pstmt.executeQuery();
if (res.next()) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
pstmt.close();
conn.close();
原理:
数据库连接池
导入 jar 包
public class Druid {
public static void main(String[] args) throws Exception {
Properties prop = new Properties();
prop.load(new FileReader("jdbc-demo/src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection connection = dataSource.getConnection();
System.out.println(connection);
}
}
练习
准备数据库
drop table if exists tb_brand;
create table tb_brand (
id int primary key auto_increment,
brand_name varchar(20),
company_name varchar(20),
ordered int,
description varchar(100),
status int
)DEFAULT CHARSET=utf8mb4;
insert into tb_brand (brand_name, company_name, ordered, description, status)
values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0),
('华为', '华为技术有限公司', 100, '华为牛逼', 1),
('小米', '小米科技有限公司', 50, 'are you ok', 1);
创建实体类
public class Brand {
private Integer id; // 在实体类中,建议使用其对应的包装类型
private String brandName;
private String companyName;
private Integer ordered;
private String description;
private Integer status;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBrandName() {
return brandName;
}
public void setBrandName(String brandName) {
this.brandName = brandName;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public Integer getOrdered() {
return ordered;
}
public void setOrdered(Integer ordered) {
this.ordered = ordered;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
@Override
public String toString() {
return "Brand{" +
"id=" + id +
", brandName='" + brandName + '\'' +
", companyName='" + companyName + '\'' +
", ordered=" + ordered +
", description='" + description + '\'' +
", status=" + status +
'}';
}
}
查询所有
public class JDBCDemo_ResultSet {
public static void main(String[] args) throws Exception {
// 注册驱动
// Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://192.168.93.12:3306/itcast?useSSL=false&useServerPreStmts=true";
String username = "root";
String password = "syc13140";
// 获取连接
Connection conn = DriverManager.getConnection(url, username, password);
// 执行器
String sql1 = "select * from tb_brand";
PreparedStatement pstmt = conn.prepareStatement(sql1);
ResultSet res = pstmt.executeQuery();
List<Brand> brands = new ArrayList<>();
while (res.next()) {
Brand brand = new Brand();
brand.setId(res.getInt("id"));
brand.setBrandName(res.getString("brand_name"));
brand.setCompanyName(res.getString("company_name"));
brand.setOrdered(res.getInt("ordered"));
brand.setDescription(res.getString("description"));
brand.setStatus(res.getInt("status"));
brands.add(brand);
}
System.out.println(brands);
pstmt.close();
conn.close();
}
}
添加
public class JDBCDemo_ResultSet {
public static void main(String[] args) throws Exception {
// 注册驱动
// Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://192.168.93.12:3306/itcast?useSSL=false&useServerPreStmts=true";
String username = "root";
String password = "syc13140";
// 获取连接
Connection conn = DriverManager.getConnection(url, username, password);
// 执行器
String sql1 = "insert into tb_brand(brand_name, company_name, ordered, description, status) values (?, ?, ?, ?, ?)";
// 参数
String brandName = "香飘飘";
String companyName = "香飘飘";
int ordered = 1;
String description = "绕地球一圈";
int status = 1;
PreparedStatement pstmt = conn.prepareStatement(sql1);
pstmt.setString(1, brandName);
pstmt.setString(2, companyName);
pstmt.setInt(3, ordered);
pstmt.setString(4, description);
pstmt.setInt(5, status);
int count = pstmt.executeUpdate();
System.out.println(count > 0);
pstmt.close();
conn.close();
}
}
修改
public class JDBCDemo_ResultSet {
public static void main(String[] args) throws Exception {
// 注册驱动
// Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://192.168.93.12:3306/itcast?useSSL=false&useServerPreStmts=true";
String username = "root";
String password = "syc13140";
// 获取连接
Connection conn = DriverManager.getConnection(url, username, password);
// 执行器
String sql1 = "update tb_brand set brand_name=?, company_name=?, ordered=?, description=?, status=? where id=?";
// 参数
int id = 4; // 要修改的数据
String brandName = "香飘飘";
String companyName = "香飘飘";
int ordered = 100;
String description = "绕地球三圈";
int status = 1;
PreparedStatement pstmt = conn.prepareStatement(sql1);
pstmt.setString(1, brandName);
pstmt.setString(2, companyName);
pstmt.setInt(3, ordered);
pstmt.setString(4, description);
pstmt.setInt(5, status);
pstmt.setInt(6, id);
int count = pstmt.executeUpdate();
System.out.println(count > 0);
pstmt.close();
conn.close();
}
}
删除
public class JDBCDemo_ResultSet {
public static void main(String[] args) throws Exception {
// 注册驱动
// Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://192.168.93.12:3306/itcast?useSSL=false&useServerPreStmts=true";
String username = "root";
String password = "syc13140";
// 获取连接
Connection conn = DriverManager.getConnection(url, username, password);
// 执行器
String sql1 = "delete from tb_brand where id=?";
// 参数
int id = 4; // 要修改的数据
PreparedStatement pstmt = conn.prepareStatement(sql1);
pstmt.setInt(1, id);
int count = pstmt.executeUpdate();
System.out.println(count > 0);
pstmt.close();
conn.close();
}
}