一、JDBC 简介
二、JDBC 快速入门
例如:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JDBCDemo {
public static void main(String[] args) throws Exception {
//1. 注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2. 获取连接
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url,username,password);
//3. 定义sql
String sql = "update stu set score = 98 where id = 1";
//4. 获取执行sql的对象 Statement
Statement stmt = conn.createStatement();
//5. 执行sql
int count = stmt.executeUpdate(sql);//受影响的行数
//6. 处理结果
System.out.println(count);
//7. 释放资源
stmt.close();
conn.close();
}
}
三、JDBC API 详解
1. DriverManager
DriverManager(驱动管理类)作用:
- 注册驱动
- 获取数据库连接
2. Connection
Connection(数据库连接对象)作用:
- 获取执行 SQL 的对象
- 管理事务
例如:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JDBCDemo_Connection {
public static void main(String[] args) throws Exception {
//1. 注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2. 获取连接
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url,username,password);
//3. 定义sql
String sql1 = "update stu set score = 98 where id = 1";
String sql2 = "update stu set score = 98 where id = 2";
//4. 获取执行sql的对象 Statement
Statement stmt = conn.createStatement();
try {
//开启事务
conn.setAutoCommit(false);
//5. 执行sql
int count1 = stmt.executeUpdate(sql1);//受影响的行数
//6. 处理结果
System.out.println(count1);
int count2 = stmt.executeUpdate(sql2);//受影响的行数
System.out.println(count2);
//提交事务
conn.commit();
} catch (Exception throwables) {
//回滚事务
conn.rollback();
throwables.printStackTrace();
}
//7. 释放资源
stmt.close();
conn.close();
}
}
3. Statement
4. ResultSet
例如:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCDemo_ResultSet {
public static void main(String[] args) throws Exception {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url,username,password);
//定义sql
String sql = "select * from dept";
//获取执行sql的对象 Statement
Statement stmt = conn.createStatement();
//执行sql
ResultSet rs = stmt.executeQuery(sql);
//处理结果,遍历 rs 中的所有数据
//光标向下移动一行,并且判断当前行是否有数据
while (rs.next()) {
//获取数据 getXxx()
int id = rs.getInt("id");
String dname = rs.getString("dname");
String loc = rs.getString("loc");
System.out.println(id);
System.out.println(dname);
System.out.println(loc);
System.out.println("----------------");
}
//释放资源
rs.close();
stmt.close();
conn.close();
}
}
运行结果为:
10
教研部
北京
----------------
20
学工部
上海
----------------
30
销售部
广州
----------------
40
财务部
深圳
----------------
案例:查询 dept 账户表数据,封装为 Department 对象中,并且存储到 ArrayList 集合中。
import com.company.pojo.Department;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
public class JDBCDemo_ResultSet_Test {
public static void main(String[] args) throws Exception {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url,username,password);
//定义sql
String sql = "select * from dept";
//获取执行sql的对象 Statement
Statement stmt = conn.createStatement();
//执行sql
ResultSet rs = stmt.executeQuery(sql);
ArrayList<Department> list = new ArrayList<>();
//处理结果,遍历 rs 中的所有数据
//光标向下移动一行,并且判断当前行是否有数据
while (rs.next()) {
Department dept = new Department();
//获取数据 getXxx()
int id = rs.getInt("id");
String dname = rs.getString("dname");
String loc = rs.getString("loc");
//赋值
dept.setId(id);
dept.setDname(dname);
dept.setLoc(loc);
//加入集合
list.add(dept);
}
System.out.println(list);
//释放资源
rs.close();
stmt.close();
conn.close();
}
}
运行结果为:
[Department{id = 10, dname = 教研部, loc = 北京}, Department{id = 20, dname = 学工部, loc = 上海}, Department{id = 30, dname = 销售部, loc = 广州}, Department{id = 40, dname = 财务部, loc = 深圳}]
5. PreparedStatement
解决 sql 注入的步骤:
例如:
public class JDBCDemo_PreparedStatement {
public static void main(String[] args) throws Exception {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
String url = "jdbc:mysql://127.0.0.1:3306/db1";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url,username,password);
String name = "zhangsan";
String pwd = "123";
//定义sql
String sql = "select * from user where usenamer = ? and passward = ?";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,name);
pstmt.setString(2,pwd);
//执行sql
ResultSet rs = pstmt.executeQuery();
//判断登录是否成功
if (rs.next()) {
System.out.println("登陆成功~");
}else {
System.out.println("登录失败~");
}
//释放资源
rs.close();
pstmt.close();
conn.close();
}
}
四、数据库连接池
1. 数据库连接池简介
2. 数据库连接池实现
定义配置文件 driud.properties:
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/db1?serverTimezone=GMT%2B8&useOldAliasMetadataBehavior=true
username=root
password=1234
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000
格式:
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;
public class DriudDemo {
public static void main(String[] args) throws Exception {
//1.导入jar包
//2.定义配置文件
//3. 加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/com/company/driud.properties"));
//4. 获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5. 获取数据库连接 Connection
Connection connection = dataSource.getConnection();
System.out.println(connection);
}
}
五、JDBC 练习
完成商品品牌数据的增删改查操作
- 查询:查询所有数据
- 添加:添加品牌
- 修改:根据 id 修改
- 删除:根据 id 删除
商品品牌表如下:
查询所有数据:
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.company.pojo.Brand;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Properties;
public class Test {
public static void main(String[] args) throws Exception {
//1. 获取连接
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/com/company/driud.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection();
//2. 定义SQL
String sql = "select * from tb_brand";
//3. 获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4. 设置参数(此用例不需要)
//5. 执行SQL
ResultSet rs = pstmt.executeQuery();
//6. 创建集合储存商品品牌信息
ArrayList<Brand> list = new ArrayList<>();
//7. 处理结果
while (rs.next()) {
//创建对象
Brand brand = new Brand();
//获取数据
int id = rs.getInt("id");
String brandName = rs.getString("brand_name");
String companyName = rs.getString("company_name");
int ordered = rs.getInt("ordered");
String description = rs.getString("description");
int status = rs.getInt("status");
//封装Brand对象
brand.setId(id);
brand.setBrand_name(brandName);
brand.setCompany_name(companyName);
brand.setOrdered(ordered);
brand.setDescription(description);
brand.setStatus(status);
//装载集合
list.add(brand);
}
System.out.println(list);
//8. 释放资源
rs.close();
pstmt.close();
conn.close();
}
}
添加品牌:
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
public class Test_Add {
public static void main(String[] args) throws Exception {
String brandName = "香飘飘";
String companyName = "香飘飘";
int ordered = 1;
String description = "666";
int status = 1;
//1. 获取连接
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/com/company/driud.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection();
//2. 定义SQL
String sql = "Insert into tb_brand(brand_name,company_name,ordered,description,status) values (?,?,?,?,?)";
//3. 获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4. 设置参数
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
//5. 执行SQL
int count = pstmt.executeUpdate();
//6. 判断操作是否成功
if (count > 0) {
System.out.println("增加成功~");
}else {
System.out.println("增加失败~");
}
//7. 释放资源
pstmt.close();
conn.close();
}
}
根据 id 修改:
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
public class Test_Update {
public static void main(String[] args) throws Exception {
int id = 4;
String brandName = "香飘飘";
String companyName = "香飘飘";
int ordered = 100;
String description = "888";
int status = 1;
//1. 获取连接
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/com/company/driud.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection();
//2. 定义SQL
String sql = "update tb_brand set brand_name = ?,company_name = ?,ordered = ?,description = ?,status = ? where id = ?";
//3. 获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4. 设置参数
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
pstmt.setInt(6,id);
//5. 执行SQL
int count = pstmt.executeUpdate();
//6. 判断操作是否成功
if (count > 0) {
System.out.println("修改成功~");
}else {
System.out.println("修改失败~");
}
//7. 释放资源
pstmt.close();
conn.close();
}
}
根据 id 删除:
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
public class Test_Delete {
public static void main(String[] args) throws Exception {
int id = 4;
//1. 获取连接
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/com/company/driud.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection();
//2. 定义SQL
String sql = "delete from tb_brand where id = ?";
//3. 获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4. 设置参数
pstmt.setInt(1,id);
//5. 执行SQL
int count = pstmt.executeUpdate();
//6. 判断操作是否成功
if (count > 0) {
System.out.println("删除成功~");
}else {
System.out.println("删除失败~");
}
//7. 释放资源
pstmt.close();
conn.close();
}
}