Day54 JDBC
JDBC：SUN公司提供的一套操作数据库的标准规范，就是使用Java语言操作关系型数据库的一套API
JDBC与数据库驱动的关系：接口与实现的关系
给大家画一个jdbc的工作模式图
1.JDBC的四大金刚
1.DriverManager：用于注册驱动
2.Connection: 表示与数据库创建的连接
3.Statement: 操作数据库sql语句的对象
4.ResultSet: sql语句执行后返回的结果集
2.JDBC操作步骤
1.创建工程，导入驱动jar包
2.注册驱动
Class.forName(“com.mysql.jdbc.Driver”);
3.获取连接
Connection conn = DriverManager.getConnection(url, username, password);
Java代码需要发送SQL给MySQL服务端，就需要先建立连接
Connection（数据库连接对象）作用：
- 获取执行 SQL 的对象
2.管理事务
4.定义SQL语句
String sql = “update…” ;
5.获取执行SQL对象
执行SQL语句需要SQL执行对象，而这个执行对象就是Statement对象
Statement stmt = conn.createStatement();
6.执行SQL
stmt.executeUpdate(sql);
ResultSet（结果集对象）作用：封装了SQL查询语句的结果
7.处理返回结果
8.释放资源
创建场景 ----------------------------------------------------------------------------
创建学生表，并添加数据
CREATE TABLE student(
id INT(3) PRIMARY KEY auto_increment,
name VARCHAR(32),
sex VARCHAR(32),
age INT(3),
salary FLOAT(8,2),
course VARCHAR(32)
)
INSERT INTO student(name,sex,age,salary,course) VALUES('龙俊','男',23,10000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('喻平','男',20,12000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('牛西燕','女',19,17000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('刘德华','男',21,15000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('马德华','男',27,10000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('霍建华','男',19,12000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('华晨宇','男',32,5000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('黄日华','男',45,4000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('任达华','男',28,7000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('周华健','男',30,8000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('欧阳震华','男',23,12000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('麻生希','女',30,7000,'HTML');
INSERT INTO student(name,sex,age,salary,course) VALUES('椎名空','女',23,6000,'HTML');
INSERT INTO student(name,sex,age,salary,course) VALUES('水野朝阳','女',28,8000,'HTML');
3.JDBC实现增删改查(最基本)
导包
项目结构
测试代码
public class Test01 {
//添加数据
@Test
public void test01() throws ClassNotFoundException, SQLException {
//导入驱动包
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接对象
String url = "jdbc:mysql://localhost:3306/fy2401javaee?characterEncoding=utf8&serverTimezone=UTC";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
//获取发送指令对象
Statement statement = connection.createStatement();
//发送SQL指令
String sql = "INSERT INTO student(name,sex,age,salary,course) VALUES('京香Julia','女',28,6000,'HTML');";
int num = statement.executeUpdate(sql);
System.out.println("对于" + num + "行造成了影响");
//关闭资源
statement.close();
connection.close();
}
//删除数据
@Test
public void test02() throws ClassNotFoundException, SQLException {
//导入驱动包
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接对象
String url = "jdbc:mysql://localhost:3306/fy2401javaee?characterEncoding=utf8&serverTimezone=UTC";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
//获取发送指令对象
Statement statement = connection.createStatement();
//发送SQL指令
String sql = "DELETE FROM student WHERE id>10;";
int num = statement.executeUpdate(sql);
System.out.println("对于" + num + "行造成了影响");
//关闭资源
statement.close();
connection.close();
}
//修改数据
@Test
public void test03() throws ClassNotFoundException, SQLException {
//导入驱动包
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接对象
String url = "jdbc:mysql://localhost:3306/fy2401javaee?characterEncoding=utf8&serverTimezone=UTC";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
//获取发送指令对象
Statement statement = connection.createStatement();
//发送SQL指令
String sql = "UPDATE student SET age=21,salary=50000 WHERE id=3;";
int num = statement.executeUpdate(sql);
System.out.println("对于" + num + "行造成了影响");
//关闭资源
statement.close();
connection.close();
}
//查询数据
@Test
public void test04() throws ClassNotFoundException, SQLException {
//导入驱动包
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接对象
String url = "jdbc:mysql://localhost:3306/fy2401javaee?characterEncoding=utf8&serverTimezone=UTC";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
//获取发送指令对象
Statement statement = connection.createStatement();
//发送SQL指令，并获取结果集对象
String sql = "select * from student";
ResultSet resultSet = statement.executeQuery(sql);
//遍历结果集
while(resultSet.next()){//判断是否有可迭代的数据行
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
int age = resultSet.getInt("age");
float salary = resultSet.getFloat("salary");
String course = resultSet.getString("course");
System.out.println(id + " -- " + name + " -- " + sex + " -- " + age + " -- " + salary + " -- " + course);
}
//关闭资源
resultSet.close();
statement.close();
connection.close();
}
}
4.资源的正确关闭
Finally ----- 关闭顺序和创建顺序相反
5.创建工具类
1.将变化的配置信息搬到配置文件中
2.工具类提供获取连接的方法
3.工具类提供关闭资源的方法
DBConfig.properties
driverName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/fy2401javaee?characterEncoding=utf8&serverTimezone=UTCuser
name=root
password=123456
数据库工具类
package com.qf.utils;
public class DBUtil {
private static String url;
private static String username;
private static String password;
static{
Properties properties = new Properties();
try {
properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));
} catch (IOException e) {
throw new RuntimeException(e);
}
String driverName = properties.getProperty("driverName");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
/**
* 获取连接对象
*/
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(url,username,password);
return connection;
}
/**
* 关闭资源
*/
public static void close(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
/**
* 更新数据（添加、删除、修改）
*/
public static int commonUpdate(String sql,Object... params) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
paramHandler(statement,params);
int num = statement.executeUpdate();
return num;
}finally {
close(connection,statement,null);
}
}
/**
* 添加数据 - 主键回填(主键是int类型可以返回)
*/
public static int commonInsert(String sql,Object... params) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
paramHandler(statement,params);
statement.executeUpdate();
resultSet = statement.getGeneratedKeys();
int primaryKey = 0;
if(resultSet.next()){
primaryKey = resultSet.getInt(1);
}
return primaryKey;
}finally {
close(connection,statement,resultSet);
}
}
public static <T> List<T> commonQuery(Class<T> clazz,String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
paramHandler(statement,params);
resultSet = statement.executeQuery();
//获取表数据对象
ResultSetMetaData metaData = resultSet.getMetaData();
//获取字段个数
int count = metaData.getColumnCount();
List<T> list = new ArrayList<>();
while(resultSet.next()){
T t = clazz.newInstance();
//获取字段名及数据
for (int i = 1; i <= count; i++) {
String fieldName = metaData.getColumnName(i);
Object fieldVal = resultSet.getObject(fieldName);
setField(t,fieldName,fieldVal);
}
list.add(t);
}
return list;
} finally {
DBUtil.close(connection,statement,resultSet);
}
}
/**
* 处理statement对象参数数据的处理器
*/
private static void paramHandler(PreparedStatement statement,Object... params) throws SQLException {
for (int i = 0; i < params.length; i++) {
statement.setObject(i+1,params[i]);
}
}
/**
* 获取当前类及其父类的属性对象
* @param clazz class对象
* @param name 属性名
* @return 属性对象
*/
private static Field getField(Class<?> clazz,String name){
for(Class<?> c = clazz;c != null;c = c.getSuperclass()){
try {
Field field = c.getDeclaredField(name);
return field;
} catch (NoSuchFieldException e) {
} catch (SecurityException e) {
}
}
return null;
}
/**
* 设置对象中的属性
* @param obj 对象
* @param name 属性名
* @param value 属性值
*/
private static void setField(Object obj,String name,Object value){
Field field = getField(obj.getClass(), name);
if(field != null){
field.setAccessible(true);
try {
field.set(obj, value);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}
5.1 封装DBUtil - v1.0
package com.qf.jdbc;
public class Test01 {
//添加数据
@Test
public void test01() throws SQLException {
//获取连接对象
Connection connection = DBUtil.getConnection();
//获取发送指令对象
Statement statement = connection.createStatement();
//发送SQL指令
String sql = "INSERT INTO student(name,sex,age,salary,course) VALUES('京香Julia','女',28,6000,'HTML');";
int num = statement.executeUpdate(sql);
System.out.println("对于" + num + "行造成了影响");
//关闭资源
DBUtil.close(connection,statement,null);
}
//删除数据
@Test
public void test02() throws SQLException {
//获取连接对象
Connection connection = DBUtil.getConnection();
//获取发送指令对象
Statement statement = connection.createStatement();
//发送SQL指令
String sql = "DELETE FROM student WHERE id>10;";
int num = statement.executeUpdate(sql);
System.out.println("对于" + num + "行造成了影响");
//关闭资源
DBUtil.close(connection,statement,null);
}
//修改数据
@Test
public void test03() throws SQLException {
//获取连接对象
Connection connection = DBUtil.getConnection();
//获取发送指令对象
Statement statement = connection.createStatement();
//发送SQL指令
String sql = "UPDATE student SET age=21,salary=50000 WHERE id=3;";
int num = statement.executeUpdate(sql);
System.out.println("对于" + num + "行造成了影响");
//关闭资源
DBUtil.close(connection,statement,null);
}
//查询数据
@Test
public void test04() throws SQLException {
//获取连接对象
Connection connection = DBUtil.getConnection();
//获取发送指令对象
Statement statement = connection.createStatement();
//发送SQL指令，并获取结果集对象
String sql = "select * from student";
ResultSet resultSet = statement.executeQuery(sql);
//遍历结果集
while(resultSet.next()){//判断是否有可迭代的数据行
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
int age = resultSet.getInt("age");
float salary = resultSet.getFloat("salary");
String course = resultSet.getString("course");
System.out.println(id + " -- " + name + " -- " + sex + " -- " + age + " -- " + salary + " -- " + course);
}
//关闭资源
DBUtil.close(connection,statement,resultSet);
}
}
5.2 SQL注入问题
select * from student where name=’’ or 1=1 #’ and passwd=‘111111’; #：后面都是注释
出现原因：数据库分不清哪些是sql命令，哪些是数据
解决方案：告诉数据库哪些是sql命令，哪些是数据
需求：模拟登录功能
package com.qf.jdbc02;
public class Test01 {
public static void main(String[] args) throws SQLException {
Connection connection = DBUtil.getConnection();
Statement statement = connection.createStatement();
Scanner scan = new Scanner(System.in);
System.out.println("请输入账号：");
String usernameVal = scan.nextLine();
System.out.println("请输入密码：");
String passwordVal = scan.nextLine();
//select * from user where username='' or 1=1 #' and password='12312345'
String sql = "select * from user where username='"+usernameVal+"' and password='"+passwordVal+"'";
ResultSet resultSet = statement.executeQuery(sql);
if(resultSet.next()){
String username = resultSet.getString("username");
String password = resultSet.getString("password");
String nikeName = resultSet.getString("nike_name");
System.out.println("登录成功");
System.out.println(username);
System.out.println(password);
System.out.println(nikeName);
}else{
System.out.println("登录失败");
}
DBUtil.close(connection,statement,resultSet);
}
}
解决方案
采用预编译对象来编程
Statement
PreparedStatement
1.安全性，避免了SQL注入
2.性能，预编译，语句-编译-执行
注意：数据用？表示
package com.qf.jdbc02;
public class Test02 {
public static void main(String[] args) throws SQLException {
Connection connection = DBUtil.getConnection();
String sql = "select * from user where username=? and password=?";
PreparedStatement statement = connection.prepareStatement(sql);
//输入数据后，将数据设置给statement对象
Scanner scan = new Scanner(System.in);
System.out.println("请输入账号：");
String usernameVal = scan.nextLine();
System.out.println("请输入密码：");
String passwordVal = scan.nextLine();
statement.setString(1,usernameVal);
statement.setString(2,passwordVal);
ResultSet resultSet = statement.executeQuery();
if(resultSet.next()){
String username = resultSet.getString("username");
String password = resultSet.getString("password");
String nikeName = resultSet.getString("nike_name");
System.out.println("登录成功");
System.out.println(username);
System.out.println(password);
System.out.println(nikeName);
}else{
System.out.println("登录失败");
}
DBUtil.close(connection,statement,resultSet);
}
}
* 设置参数值
上面的sql语句中参数使用 ? 进行占位，在之前之前肯定要设置这些 ? 的值。
> PreparedStatement对象：setXxx(参数1，参数2)：给 ? 赋值
> * Xxx：数据类型 ； 如 setInt (参数1，参数2)
> * 参数：
> * 参数1： ？的位置编号，从1 开始
> * 参数2： ？的值
* 执行SQL语句
> executeUpdate(); 执行DDL语句和DML语句
> executeQuery(); 执行DQL语句
5.3 jdbc最终版本 封装数据库 - v2.0
增删改都是一样的
添加数据 - 主键回填(主键是int类型可以返回)
数据库工具类
package com.qf.utils;
public class DBUtil {
private static String url;
private static String username;
private static String password;
static{
Properties properties = new Properties();
try {
properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));
} catch (IOException e) {
throw new RuntimeException(e);
}
String driverName = properties.getProperty("driverName");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
/**
* 获取连接对象
*/
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(url,username,password);
return connection;
}
/**
* 关闭资源
*/
public static void close(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
/**
* 更新数据（添加、删除、修改）
*/
public static int commonUpdate(String sql,Object... params) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
paramHandler(statement,params);
int num = statement.executeUpdate();
return num;
}finally {
close(connection,statement,null);
}
}
/**
* 添加数据 - 主键回填(主键是int类型可以返回)
*/
public static int commonInsert(String sql,Object... params) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
paramHandler(statement,params);
statement.executeUpdate();
resultSet = statement.getGeneratedKeys();
int primaryKey = 0;
if(resultSet.next()){
primaryKey = resultSet.getInt(1);
}
return primaryKey;
}finally {
close(connection,statement,resultSet);
}
}
public static <T> List<T> commonQuery(Class<T> clazz,String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
paramHandler(statement,params);
resultSet = statement.executeQuery();
//获取表数据对象
ResultSetMetaData metaData = resultSet.getMetaData();
//获取字段个数
int count = metaData.getColumnCount();
List<T> list = new ArrayList<>();
while(resultSet.next()){
//创建对象
T t = clazz.newInstance();
//获取字段名及数据
for (int i = 1; i <= count; i++) {
String fieldName = metaData.getColumnName(i);
Object fieldVal = resultSet.getObject(fieldName);
setField(t,fieldName,fieldVal);
}
list.add(t);
}
return list;
} finally {
DBUtil.close(connection,statement,resultSet);
}
}
/**
* 处理statement对象参数数据的处理器
*/
private static void paramHandler(PreparedStatement statement,Object... params) throws SQLException {
for (int i = 0; i < params.length; i++) {
statement.setObject(i+1,params[i]);
}
}
//下面是反射的内容
/**
* 获取当前类及其父类的属性对象
* @param clazz class对象
* @param name 属性名
* @return 属性对象
*/
private static Field getField(Class<?> clazz,String name){
for(Class<?> c = clazz;c != null;c = c.getSuperclass()){
try {
Field field = c.getDeclaredField(name);
return field;
} catch (NoSuchFieldException e) {
} catch (SecurityException e) {
}
}
return null;
}
/**
* 设置对象中的属性
* @param obj 对象
* @param name 属性名
* @param value 属性值
*/
private static void setField(Object obj,String name,Object value){
Field field = getField(obj.getClass(), name);
if(field != null){
field.setAccessible(true);
try {
field.set(obj, value);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}
学生类
package com.qf.jdbc03;
public class Student {
private int id;
private String name;
private String sex;
private int age;
private float salary;
private String course;
//无参构造，有参构造，set，get，tostring省略
测试类 封装数据库 - v2.0
package com.qf.jdbc03;
public class Test01 {
/**
* 知识点：封装数据库 - v2.0
*/
//添加数据
@Test
public void test01() throws SQLException {
String sql = "INSERT INTO student(name,sex,age,salary,course) VALUES(?,?,?,?,?)";
int num = DBUtil.commonUpdate(sql, "天使萌", "女", 23, 10000, "HTML");
System.out.println("对于" + num + "行造成了影响");
}
//删除数据
@Test
public void test02() throws SQLException {
String sql = "delete from student where id=?";
int num = DBUtil.commonUpdate(sql, 3);
System.out.println("对于" + num + "行造成了影响");
}
//修改数据
@Test
public void test03() throws SQLException {
String sql = "update student set salary=? where id=?";
int num = DBUtil.commonUpdate(sql, 15000,1);
System.out.println("对于" + num + "行造成了影响");
}
//添加数据 -
@Test
public void test04() throws SQLException {
String sql = "INSERT INTO student(name,sex,age,salary,course) VALUES(?,?,?,?,?)";
int primaryKey = DBUtil.commonInsert(sql, "铃原爱蜜莉", "女", 23, 10000, "HTML");
System.out.println("返回的主键是：" + primaryKey);
}
//查询数据
@Test
public void test05() throws SQLException, InstantiationException, IllegalAccessException {
String sql = "select * from student where id<?";
List<Student> list = DBUtil.commonQuery(Student.class, sql, 8);
for (Student stu : list) {
System.out.println(stu);
}
}
}
intln(“对于” + num + “行造成了影响”);
}
//修改数据
@Test
public void test03() throws SQLException {
String sql = "update student set salary=? where id=?";
int num = DBUtil.commonUpdate(sql, 15000,1);
System.out.println("对于" + num + "行造成了影响");
}
//添加数据 -
@Test
public void test04() throws SQLException {
String sql = "INSERT INTO student(name,sex,age,salary,course) VALUES(?,?,?,?,?)";
int primaryKey = DBUtil.commonInsert(sql, "铃原爱蜜莉", "女", 23, 10000, "HTML");
System.out.println("返回的主键是：" + primaryKey);
}
//查询数据
@Test
public void test05() throws SQLException, InstantiationException, IllegalAccessException {
String sql = "select * from student where id<?";
List<Student> list = DBUtil.commonQuery(Student.class, sql, 8);
for (Student stu : list) {
System.out.println(stu);
}
}
}
![[算法刷题—二分法]寻找插入位置](https://img-blog.csdnimg.cn/direct/4d5da675ec0a4b12a01b30e8cdc475ed.png)
