JDBC
JDBC简介
概念
JDBC就是用Java语言操作关系型数据库的一套API，全称Java数据库连接
本质
- 官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口
- 哥哥数据库厂商去实现这套接口，提供数据库驱动jar包
- 我们使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类
JDBC快速入门操作
0.创建工程，导入jar包
1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
2.获取连接
Connection conn = DriverManager.getConnection(url, username, password);
3.定义SQL语句
String sql = "update ...";
4.获取执行SQL对象
Statement stmt = conn.createStatement();
5.执行SQL
stmt.executeUpdate(sql);
6.处理返回结果
7.释放资源
API
DriverManager
1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
提示：
- MySQL5之后的驱动包，可以省略注册驱动的步骤
- 自动加载jar包中META-INF/services/java.sql.Driver文件中的驱动类
2.获取连接
static Connection getConnection(String url, String user, String password)
参数：
-
url：连接路径
语法：jdbc:mysql://ip地址(域名):端口号/数据库名称?参数键值对1&参数键值对2…
示例：jdbc:mysql://127.0.0.1:3306/db1
细节：
- 如果连接的是本机的mysql服务器，并且mysql服务器默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称?参数键值对
- 配置useSSL=false参数，禁用安全连接方式，解决警告提示
-
user：用户名
-
password：密码
Connection
1.获取执行SQL的对象
-
普通执行SQL对象
Statement createStatement() -
预编译SQL的执行SQL对象：防止SQL注入
PreparedStatement prepareStatement(sql) -
执行存储过程的对象（不常用）
CallableStatement prepareCall(sql)
2.事务管理
-
MySQL事务管理
开启事务:BEGIN; / START TRANSACTION; 提交事务:COMMIT; 回滚事务:ROLLBACK; MySQL默认自动提交事务 -
JDBC事务管理：Connection接口中定义了3个对应的方法
开启事务:setAutoCommit(boolean autoCommit): true为自动提交事务; false为手动提交事务，即为开启事务 提交事务:commit() 回滚事务:rollback()
eg:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo3_Connection {
public static void main(String[] args) throws Exception {
//Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql:///test";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
String sql1 = "update account set money = 1000 where name = '小宇'";
String sql2 = "update account set money = 2000 where id = 4";
Statement stmt = conn.createStatement();
try {
//开启事务
conn.setAutoCommit(false);
//执行sql
int count1 = stmt.executeUpdate(sql1); //受影响的行数
//处理结果
System.out.println(count1);
int count2 = stmt.executeUpdate(sql2); //受影响的行数
System.out.println(count2);
//提交事务
conn.commit();
} catch (Exception e) {
//回滚事务
conn.rollback();
throw new RuntimeException(e);
}
stmt.close();
conn.close();
}
}
Statement
1.执行SQL语句
int executeUpdate(sql): 执行DML、DDL语句
返回值：(1)DML语句影响的行数（2）DDL语句执行后，执行成功也可能返回0
ResultSet excuteQuery(sql): 执行DQL语句
返回值：ResultSet结果集对象
import org.junit.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JDBCDemo4_Statement {
@Test
public void testDML() throws Exception {
//Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "create database db";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);
if (count > 0) {
System.out.println("操作成功！");
}else {
System.out.println("操作失败！");
}
System.out.println(count);
stmt.close();
conn.close();
}
}
ResultSet
-
ResultSet(结果集对象)作用
封装了DQL查询语句的结果
ResultSet stmt.executeQuery(sql): 执行DQL语句，返回ResultSet对象 -
获取查询结果
boolean nest(): (1)将光标从当前位置向前移动一行 (2)判断当前行是否为有效行返回值：
- true：有效行，当前行有数据
- false：无效行，当前行没有数据
xxx getXxx(参数): 获取数据 xxx：数据类型；如：int getInt(参数); String getString(参数)参数：
- int：列的编号，从1开始
- String：列的名称
-
使用步骤：
1.游标向下移动一行，并判断该行是否有数据：next()
2.获取数据：getXxx(参数)
//循环判断游标是否是最后一行末尾 while(rs.next()) { //获取数据 rs.getXxx(参数) }
eg：
package com.jdbc;
import com.pojo.Account;
import org.junit.Test;
import java.sql.*;
import java.util.List;
public class JDBCDemo5_ResultSet {
@Test
public void test() throws Exception {
//Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
String sql1 = "use test";
String sql2 = "select * from account";
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql1);
ResultSet rs = stmt.executeQuery(sql2);
List<Account> list = new ArrayList<>();
//处理结果，遍历rs中的所有数据
//光标向下移动一行，并且判断当前行是否有数据
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
double money = rs.getDouble(3);
Account account = new Account(id, name, money);
list.add(account);
}
System.out.println(list.toString());
//释放资源
rs.close();
stmt.close();
conn.close();
}
}
PreparedStatement
-
作用：
预编译SQL语句并执行：预防SQL注入问题
-
SQL注入
SQL注入是通过操作输入来修改事先定义好的SQL语句，用以达到执行代码对服务器进行攻击的方法
SQL注入演示：
package com.jdbc;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCDemo6_UserLogin {
@Test
public void testLogin() throws Exception {
//Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
String name = "qweqwe";
String pwd = "123";
String sql = "select * from user where name = '"+name+"' and pwd = '"+pwd+"'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
}
/**
* 演示SQL注入
* @throws Exception
*/
@Test
public void testLogin_Inject() throws Exception {
//Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
String name = "auisghdui";
String pwd = "' or '1' = '1";
String sql = "select * from user where name = '"+name+"' and pwd = '"+pwd+"'";
System.out.println(sql); //select * from user where name = 'auisghdui' and pwd = '' or '1' = '1'
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
}
}
PreparedStatement使用步骤：
-
获取PreparedStatement对象
//SQL语句中的参数值，使用?占位符替代 String sql = "select * from user username = ? and password = ?"; //通过Connection对象获取，并传入对应的sql语句 PreparedStatement pstmt = conn.preparedStatement(sql); -
设置参数值
PreparedStatement对象：setXxx(参数1, 参数2)：给?赋值 Xxx：数据类型；如setInt(参数1, 参数2) 参数： 参数1：?的位置编号，从1开始 参数2：?的值 -
执行SQL
executeUpdate(); / executeQuery(); //不需要再传递sql
eg：
package com.jdbc;
import org.junit.Test;
import java.sql.*;
public class JDBCDemo7_PreparedStatement {
@Test
public void test_PreparedStatement() throws Exception {
//Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
String name = "qweqwe";
String pwd = "12222";
String sql = "select * from user where name = ? and pwd = ?";
//获取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("登录失败");
}
}
}
数据库连接池
简介
数据库连接池是一个容器，负责分配、管理数据库连接。它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个。释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。
好处：
- 资源重用
- 提升系统响应速度
- 避免数据库连接遗漏
实现
标准接口：DataSource
-
官方（SUN）提供的数据库连接池标准接口，由第三方组织实现此接口
-
功能：获取连接
Connection getConnection()
常见数据库连接池：DBCP、C3P0、Druid
Druid的使用
步骤：
-
导入jar包 druid-1.1.12jar
-
定义配置文件
//druid.properties driverClassName = com.mysql.cj.jdbc.Driver url=jdbc:mysql:///test?useSSL=false&useServerPrepStmts=true&serverTimezone=GMT%2B8 username=root password=123456 initialSize=5 maxActive=10 maxWait=3000 -
加载配置文件
-
获取数据库连接池对象
-
获取连接
package com.druid; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.FileInputStream; import java.sql.Connection; import java.util.Properties; public class DruidDemo { public static void main(String[] args) throws Exception { //1.导入jar包 //2.定义配置文件 //3.加载配置文件 Properties prop = new Properties(); prop.load(new FileInputStream("C:\\Users\\卢虹宇\\IdeaProjects\\jdbc\\jdbc-demo\\src\\com\\druid.properties")); //4.获取连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); //获取数据库连接 Connection conn = dataSource.getConnection(); System.out.println(System.getProperty("user.dir")); System.out.println(conn); } }
练习
package com.example;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.pojo.Brand;
import org.junit.Test;
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.List;
import java.util.Properties;
/**
* 品牌数据的增删改查操作
*/
public class BrandTest {
/**
* 查询所有
*/
@Test
public void selectAll() throws Exception {
//获取Connection
Properties prop = new Properties();
prop.load(new FileInputStream("C:\\Users\\卢虹宇\\IdeaProjects\\jdbc\\jdbc-demo\\src\\com\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection();
//定义sql
String sql = "select * from tb_brand";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置参数
//执行sql
ResultSet res = pstmt.executeQuery();
//处理结果
List<Brand> list = new ArrayList<>();
while (res.next()) {
//获取数据
int id = res.getInt("id");
String brandName = res.getString("brandName");
String companyName = res.getString("companyName");
int ordered = res.getInt("ordered");
String description = res.getString("description");
int status = res.getInt("status");
//封装对象
Brand b = new Brand(id, brandName, companyName, ordered, description, status);
//装载集合
list.add(b);
}
System.out.println(list);
//释放资源
res.close();
pstmt.close();
conn.close();
}
@Test
public void testAdd() throws Exception {
//接受页面提交的参数
String brandName = "香飘飘";
String companyName = "香飘飘";
int ordered = 1;
String description = "绕地球一圈";
int status = 1;
//获取连接
Properties prop = new Properties();
prop.load(new FileInputStream("C:\\Users\\卢虹宇\\IdeaProjects\\jdbc\\jdbc-demo\\src\\com\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection();
String sql = "insert into tb_brand (brandName, companyName, ordered, description, status) values (?,?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置参数
pstmt.setString(1, brandName);
pstmt.setString(2, companyName);
pstmt.setInt(3, ordered);
pstmt.setString(4, description);
pstmt.setInt(5, status);
//执行sql
int count = pstmt.executeUpdate(); //影响的行数
//处理结果
System.out.println(count > 0);
//释放资源
pstmt.close();
conn.close();
}
@Test
public void testUpdate() throws Exception {
String brandName = "香飘飘";
String companyName = "香飘飘食品有限公司";
int ordered = 20;
String description = "绕地地球三圈";
int status = 1;
int id = 4;
Properties prop = new Properties();
prop.load(new FileInputStream("C:\\Users\\卢虹宇\\IdeaProjects\\jdbc\\jdbc-demo\\src\\com\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection();
String sql = "update tb_brand " +
"set brandName = ?," +
"companyName = ?," +
"ordered = ?," +
"description = ?," +
"status = ? " +
"where id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
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);
}
@Test
public void testDelete() throws Exception {
int id = 5;
Properties prop = new Properties();
prop.load(new FileInputStream("C:\\Users\\卢虹宇\\IdeaProjects\\jdbc\\jdbc-demo\\src\\com\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection();
String sql = "delete from tb_brand where id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
int count = pstmt.executeUpdate();
System.out.println(count > 0);
}
}
1287
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
