JDBC介绍
JDBC介绍
一、数据库驱动
驱动：声卡、显卡、数据库
没有驱动，应用程序无法直达数据库。
我们的程序会通过数据库驱动，和数据库打交道！
但即使有了数据库驱动了，不同的数据库有不同的数据库驱动，那我们有10套数据库，岂不是要10套程序？JDBC就应运诞生了。
架构上：没有什么是加一层不能做的。
二、JDBC
oracle公司为了简化 开发人员的操作，提供了一个（java操作数据库的）规范，实际上一套接口，俗称JDBC，这些规范的实现由具体的厂商去做~
对开发人员来说，只用掌握jdbc接口的操作即可，和JDBC打交道。
需要两个包：
java.jar
javax.jar
还需要导入一个数据库驱动包
三、第一个JDBC程序
创建一个普通项目
创建测试数据库环境：
create table user(
id int primary key,
name varchar(40),
password varchar(40),
email varchar(40),
birthday date
);
INSERT INTO USER(id ,NAME, PASSWORD,email,birthday)
VALUES(1,'zhang2','123456','gaoxing@ccb.com','1997-05-18'),(2,'lisi','123456','gaoxing2@ccb.com','1997-01-21'),(3,'wang5','123456','gaoxing3@ccb.com','1997-02-18')
3.1 创建一个项目
3.2 导入数据库驱动
3.3 入门程序：
package com.happy;
import java.sql.*;
public class firstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1.加载驱动,固定写法
// 利用反射加载数据库驱动
Class clazz=Class.forName("com.mysql.cj.jdbc.Driver");
// 2.用户信息和url
String url="jdbc:mysql://localhost:3306/school?useUnicode=true&character=Encoding=utf8&useSSL=true";
String username="root";
String password="1987518g";
// 3.连接成功，数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
// 4.执行sql对象
Statement statement = connection.createStatement();
// 5.执行sql的对象，去执行sql，可能存在结果，查看返回结果
String sql="select * from user";
// resultset封装了所有结果
ResultSet resultSet = statement.executeQuery(sql);
int i=1;
while (resultSet.next()){
System.out.println("=========================第"+i+ "条数据");
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("name"));
System.out.println("password="+resultSet.getObject("password"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
i++;
}
// 6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
3.4 步骤总结：
- 加载驱动
- 连接数据库DriverManager
- 获得执行sql对象 statement
- 获得返回的结果集resultSet
- 释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver()) 这种方式看源码会加载两次
//固定写法，加载驱动Class.forName("com.mysql.jdbc.Driver")
URL
String url="jdbc:mysql://localhost:3306/school?useUnicode=true&character=Encoding=utf8&useSSL=true";
//mysql --3306
jdbc:mysql://主机地址:3306/数据库名?useUnicode=true&character=Encoding=utf8&useSSL=true
//oracle --1521
jdbc:oracle:thin@主机地址:1521:sid
connection
connection代表数据库
//数据库设置自动提交
//支持事务
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
Statement对象 和 PrepareStatement对象 ，执行对象
statement.executeQuery(); //查询操作返回resultset
statement.execute(); //执行任何SQL
statement.executeUpdate(); //更新、输入、删除，都是用这个，返回一个受影响的行数
statement.exexuteBatch();
ResultSet 查询结果集对象，封装了所有的查询结果
获得指定的数据类型
result.getObject(); //在不知道类型的情况下使用
result.getString();
result.getInt();
result.getFloat();
result.getDate();
...
遍历： next 指针
resultSet.next(); //移动到指定行
resultSet.previous();
resultSet.beforeFrist(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.absolute(2); //移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();
四、重要对象详解
4.1 statement对象
JDBC中的statement对象用于向数据库发送sql语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
- statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)
- statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据库添加操作，示例操作：
statement st=conn.createStatement();
String sql="insert into user(...) values(...)";
int num=st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功！");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据库添加操作，示例操作：
statement st=conn.createStatement();
String sql="delete from user where id=1";
int num=st.executeUpdate(sql);
if(num>0){
System.out.println("删除成功！！");
}
CRUD操作-update
使用executeUpdate(String sql)方法完成数据库添加操作，示例操作：
statement st=conn.createStatement();
String sql="update user set name=‘happy’ where id=1";
int num=st.executeUpdate(sql);
if(num>0){
System.out.println("删除成功！！");
}
CRUD操作-select
使用executeUpdate(String sql)方法完成数据库添加操作，示例操作：
statement st=conn.createStatement();
String sql="select * from user where id=1";
int num=st.executeQuery(sql);
while(rs.next()){
System.out.println("查询成功！！");
}
4.2 PreparedStatement对象
- 可以防止sql注入，会用单引号包裹字符串，遇到特殊字符会转义。
- 效率更好
package com.happy;
import com.utils.JdbcUtils;
import org.junit.jupiter.api.Test;
import java.sql.*;
import java.util.Date;
public class demo3 {
public static void main(String[] args) {
// String name="aaa";
// String password="bbb";
String name = "gaoyihen' or '1=1";
String password = "124' or '1=1";
testSqlInjection(name,password);
}
@Test
public void insert() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
// String sql="select * from user where name='happy'";
// 使用問號佔位符
String sql = "insert into user(id,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
// 預編譯，這個時候沒有執行
ps = conn.prepareStatement(sql);
ps.setInt(1, 9);
ps.setString(2, "aaa");
ps.setString(3, "bbb");
ps.setString(4, "103098723@qq.com");
// 注意點：
// 1.sql.Date 數據庫
// 2.utils java
ps.setDate(5, new java.sql.Date(new Date().getTime()));
int i = ps.executeUpdate();
if (i > 0) {
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, ps, null);
}
}
@Test
public void delete() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
// String sql="select * from user where name='happy'";
// 使用問號佔位符
String sql = "delete from user where id=?";
// 預編譯，這個時候沒有執行
ps = conn.prepareStatement(sql);
ps.setInt(1, 6);
int i = ps.executeUpdate();
if (i > 0) {
System.out.println("刪除成功！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, ps, null);
}
}
@Test
public void update() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
// String sql="select * from user where name='happy'";
// 使用問號佔位符
String sql = "update user set name=? where id=?";
// 預編譯，這個時候沒有執行
ps = conn.prepareStatement(sql);
ps.setString(1, "happy518");
ps.setInt(2, 6);
int i = ps.executeUpdate();
if (i > 0) {
System.out.println("更新成功！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, ps, null);
}
}
@Test
public void select() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
// String sql="select * from user where name='happy'";
// 使用問號佔位符
String sql = "select * from user where name=?";
// 預編譯，這個時候沒有執行
ps = conn.prepareStatement(sql);
ps.setString(1, "happy");
rs = ps.executeQuery();
while (rs.next()) {
System.out.println("###########");
System.out.println("username:" + rs.getString("name"));
System.out.println("password:" + rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, ps, null);
}
}
//无法sql注入
public static void testSqlInjection(String name,String password){
Connection connection = null;
PreparedStatement ps=null;
ResultSet rs=null;
int result;
try {
connection = JdbcUtils.getConnection();
String sql="select * from user where name=? and password=?";
ps = connection.prepareStatement(sql);
ps.setString(1,name);
ps.setString(2,password);
rs = ps.executeQuery();
while(rs.next()){
System.out.println("==================");
System.out.println("id："+rs.getInt("id"));
System.out.println("username："+rs.getString("name"));
System.out.println("password："+rs.getString("password"));
System.out.println("email："+rs.getString("email"));
System.out.println("birthday："+rs.getDate("birthday"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,ps,rs);
}
}
}
五、提取工具类-代码重写
5.1 将jdbc信息放到properties文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&character=Encoding=utf8&useSSL=true
username=root
password=1987518g
5.2 提取工具类：
package com.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;
static{
try{
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties=new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
// 驱动只加载一次,所以把代码放到static块下
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
// 释放连接资源
public static void release(Connection connection, Statement st, ResultSet rs){
try {
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
if (connection != null) {
connection.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
5.3 增删改查
package com.happy;
import com.utils.JdbcUtils;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class demo2 {
public static void main(String[] args) {
}
@Test
public void insert(){
Connection connection = null;
Statement st=null;
ResultSet rs=null;
int result;
try {
connection = JdbcUtils.getConnection();
st = connection.createStatement();
String sql="insert into user values(123,'aaa','bbb','ccc','2020-5-20');";
result = st.executeUpdate(sql);
if(result>0){
System.out.println("插入成功數據條數："+result);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,st,rs);
}
}
@Test
public void delete(){
Connection connection = null;
Statement st=null;
ResultSet rs=null;
int result;
try {
connection = JdbcUtils.getConnection();
st = connection.createStatement();
String sql="delete from user where id='123'";
result = st.executeUpdate(sql);
if(result>0){
System.out.println("刪除成功數據條數："+result);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,st,rs);
}
}
@Test
public void update(){
Connection connection = null;
Statement st=null;
ResultSet rs=null;
int result;
try {
connection = JdbcUtils.getConnection();
st = connection.createStatement();
String sql="update user set name='happy2' where id='123'";
result = st.executeUpdate(sql);
if(result>0){
System.out.println("更新成功數據條數："+result);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,st,rs);
}
}
@Test
public void select(){
Connection connection = null;
Statement st=null;
ResultSet rs=null;
int result;
try {
connection = JdbcUtils.getConnection();
st = connection.createStatement();
String sql="select * from user";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println("==================");
System.out.println("id："+rs.getInt("id"));
System.out.println("username："+rs.getString("name"));
System.out.println("password："+rs.getString("password"));
System.out.println("email："+rs.getString("email"));
System.out.println("birthday："+rs.getDate("birthday"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,st,rs);
}
}
}
六、SQL注入问题
sql存在注入漏洞
package com.happy;
import com.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLInjection {
public static void main(String[] args) {
// 1.正常登陸
// String username = "gaoyiheng";
// 2.sql注入登陸
String username = "gaoyihen' or '1=1";
String pwd = "124' or '1=1";
login(username, pwd);
}
public static void login(String username, String pwd) {
Connection connection = null;
Statement st = null;
ResultSet rs = null;
try {
connection = JdbcUtils.getConnection();
st = connection.createStatement();
// 1.正常用戶登陸
// String sql = "select * from user where name='" + username + "' and password='" + pwd + "'";
// 2.正常用戶錯誤密碼，登陸失敗
String sql = "select * from user where name='" + username + "' and password='" + pwd + "'";
// 3.非法用戶用錯誤密碼，登陸成功
// String sql = "select * from user where name='" + username + "' and password='" + pwd + "'";
rs = st.executeQuery(sql);
if(rs==null){
System.out.println("結果集為null");
}else{
int rowCount=0;
while (rs.next()) {
String userName = rs.getString("name");
String password = rs.getString("password");
System.out.println("登陸成功=》" + userName + ":" + password);
rowCount++;
}
// int rows=rs.getRow();
if(rowCount==0){
System.out.println("登陸失敗，沒有找到用戶和密碼匹配");
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, st, rs);
}
}
}
七、使用idea连接数据库
修改驱动版本
八、JDBC操作事务
8.1 ACID
ACID
- A 原则性：要么一起失败要么一起成功
- C 一致性：前后逻辑一致，总数不变
- I 隔离性：多个事务之间不会相互影响
- D 持久性：durability，一旦成功不能再更改，持久化到数据库
8.2 隔离性问题：
脏读：一个事务读取了另一个没有提交的事务
不可重复读：在同一个事务内，重复读取表两次，读取内容不一样
虚读：在一个事务内读取到了别人插入的数据，导致前后结果不一致。
8.3 测试jdbc操作事务
create table account(
id int primary key auto_increment,
name varchar(40),
money float
)
insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('C',1000);
8.4 代码实现
-
开启事务
conn.setAutoCommit(false); -
一组业务执行完毕，提交事务
-
可以在catch语句中显示的定义回滚语句，但默认失败就会回滚
package com.happy;
import com.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TransactionTest {
public static void main(String[] args) {
transfer("A","B",100);
}
public static void transfer(String A,String B,float num) {
Connection connection = null;
PreparedStatement prepareStatement = null;
ResultSet rs = null;
try {
connection = JdbcUtils.getConnection();
//相當於數據庫的set autocommit=0; -- 关闭自动提交，=》開啟事務
connection.setAutoCommit(false);
String sql1 = "update account set money=money-? where name=?";
prepareStatement = connection.prepareStatement(sql1);
prepareStatement.setFloat(1, num);
prepareStatement.setString(2, A);
int result1 = prepareStatement.executeUpdate();
if (result1 >= 0) {
System.out.println("A減少錢成功！");
}
int i = 3 / 0;
String sql2 = "update account set money=money+? where name=?";
prepareStatement = connection.prepareStatement(sql2);
prepareStatement.setFloat(1, num);
prepareStatement.setString(2, B);
int result2 = prepareStatement.executeUpdate();
connection.commit();
if (result1 >= 0) {
System.out.println("B增加錢成功！");
}
} catch (Exception e) {
System.out.println("交易失敗");
try {
connection.rollback();
} catch (SQLException ex) {
System.out.println("回滾失敗！");
ex.printStackTrace();
}
e.printStackTrace();
} finally {
JdbcUtils.release(connection, prepareStatement, null);
}
}
}
九、数据库连接池
创建数据连接=》执行完毕=》释放连接
但建立连接和释放连接十分浪费资源，所以数据库连接池应运而生了。
池化技术：准备一些预先的资源，过来就连接预先准备好的。
常用连接数10个=》最小连接数10个
最大连接数：业务最高承载上线
排队等待
等待超时：100ms
9.1 开源数据源实现
使用了这些数据库连接池以后，我们再项目开发中就不需要编写数据连接创建了。
DBCP
jar包
需要用到：
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.6</version>
</dependency>
配置文件
如下：
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&character=Encoding=utf8&useSSL=true
username=root
password=1987518g
#Connection Pooling Info
initialSize=10
maxActive=50
minIdle=5
maxIdle=10
maxWait=10000
timeBetweenEvictionRunsMillis=5000
minEvictableIdleTimeMillis=2000
removeAbandoned=true
removeAbandonedTimeout=60
logAbandoned=true
validationQuery=select 1
testOnBorrow=true
testOnReturn=true
testWhileIdle=true
工具类
稍微改动如下：
package com.happy;
import com.utils.DbcpUtils;
import com.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TransactionTestDBCP {
public static void main(String[] args) {
transfer("A","B",50);
}
public static void transfer(String A,String B,float num) {
Connection connection = null;
PreparedStatement prepareStatement = null;
ResultSet rs = null;
try {
connection = DbcpUtils.getConnection();
//相當於數據庫的set autocommit=0; -- 关闭自动提交，=》開啟事務
connection.setAutoCommit(false);
String sql1 = "update account set money=money-? where name=?";
prepareStatement = connection.prepareStatement(sql1);
prepareStatement.setFloat(1, num);
prepareStatement.setString(2, A);
int result1 = prepareStatement.executeUpdate();
if (result1 >= 0) {
System.out.println("A減少錢成功！");
}
// int i = 3 / 0;
String sql2 = "update account set money=money+? where name=?";
prepareStatement = connection.prepareStatement(sql2);
prepareStatement.setFloat(1, num);
prepareStatement.setString(2, B);
int result2 = prepareStatement.executeUpdate();
connection.commit();
if (result1 >= 0) {
System.out.println("B增加錢成功！");
}
} catch (Exception e) {
System.out.println("交易失敗");
try {
connection.rollback();
} catch (SQLException ex) {
System.out.println("回滾失敗！");
ex.printStackTrace();
}
e.printStackTrace();
} finally {
DbcpUtils.release(connection, prepareStatement, null);
}
}
}
C3P0
jar包
需要用到
<dependency>
<groupId>com.mchange</groupId>
<artifactId>mchange-commons-java</artifactId>
<version>0.2.20</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
配置文件
可以配置多套数据源
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 默认配置，如果没有指定则使用这个配置 -->
<default-config>
<property name="user">root</property>
<property name="password">1987518g</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/school</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="checkoutTimeout">30000</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">3</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">2</property>
<property name="maxStatements">200</property>
</default-config>
<!-- 命名的配置,可以通过方法调用实现 -->
<named-config name="shop">
<property name="user">root</property>
<property name="password">1987518g</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/shop</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<!-- 如果池中数据连接不够时一次增长多少个 -->
<property name="acquireIncrement">5</property>
<!-- 初始化数据库连接池时连接的数量 -->
<property name="initialPoolSize">20</property>
<!-- 数据库连接池中的最大的数据库连接数 -->
<property name="maxPoolSize">25</property>
<!-- 数据库连接池中的最小的数据库连接数 -->
<property name="minPoolSize">5</property>
</named-config>
</c3p0-config>
工具类
package com.happy;
import com.utils.C3P0Utils;
import com.utils.DbcpUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TransactionTestC3P0 {
public static void main(String[] args) {
transfer("A","B",25);
}
public static void transfer(String A,String B,float num) {
Connection connection = null;
PreparedStatement prepareStatement = null;
ResultSet rs = null;
try {
connection = C3P0Utils.getConnection();
//相當於數據庫的set autocommit=0; -- 关闭自动提交，=》開啟事務
connection.setAutoCommit(false);
String sql1 = "update account set money=money-? where name=?";
prepareStatement = connection.prepareStatement(sql1);
prepareStatement.setFloat(1, num);
prepareStatement.setString(2, A);
int result1 = prepareStatement.executeUpdate();
if (result1 >= 0) {
System.out.println("A減少錢成功！");
}
// int i = 3 / 0;
String sql2 = "update account set money=money+? where name=?";
prepareStatement = connection.prepareStatement(sql2);
prepareStatement.setFloat(1, num);
prepareStatement.setString(2, B);
int result2 = prepareStatement.executeUpdate();
connection.commit();
if (result1 >= 0) {
System.out.println("B增加錢成功！");
}
} catch (Exception e) {
System.out.println("交易失敗");
try {
connection.rollback();
} catch (SQLException ex) {
System.out.println("回滾失敗！");
ex.printStackTrace();
}
e.printStackTrace();
} finally {
C3P0Utils.release(connection, prepareStatement, null);
}
}
}
Druid：阿里巴巴
结论
不论使用什么数据源，本质上还是一样的，获取到DataSource后，都是实现同一个接口DataSource，方法就不会变：
return dataSource.getConnection();
