一、JDBC实战技术
1.1JDBC概念
JDBC(Java DataBase Connectivity:java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系型数据库提供统一访问,它是由一组用Java语言编写的类和接口组成的。JDBC的作用:可以通过java代码操作数据库。
1.2创建连接:步骤
1.2.1Mysql对应Jar包下载
下载官网
下载ZIp
1.2.2Idea中导入Jar包
在Maven工程中直接配置依赖信息即可
让项目识别Jar包
1.2.3Java代码实现连接
package JavaSE_JDBC;import java.sql.*;/*** @author Pengwei Qu* @Date 2024/8/17 22:05*/
public class Jdbc_mysql01 {public static void main(String[] args) throws ClassNotFoundException, SQLException {//01、注册驱动Class.forName("com.mysql.jdbc.Driver");//02、获取连接Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_student", "root", "123456");//03、定义SqlString sql = "SELECT * FROM studinf";//04、获取执行者对象Statement stat = conn.createStatement();//05、执行Sql并获取结果ResultSet rs = stat.executeQuery(sql);//06、处理返回结果while(rs.next()) {System.out.println(rs.getInt("ID") + "\t" + rs.getString("Name"));}//07、关闭资源rs.close();stat.close();conn.close();}
}
1.3JDBC中的功能类API
1.3.1DriverManager
(1)作用:
1.加载注册驱动
2.获取连接对象
(2)注册驱动
下面代码中注册驱动用的是:Class.forName("com.mysql.jdbc.Driver");
并不是DriverManager
?
点开Driver的源码:
public static void main(String[] args) throws ClassNotFoundException, SQLException {//01、注册驱动Class.forName("com.mysql.jdbc.Driver");//02、获取连接对象Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_student", "root", "123456");//03、定义SqlString sql = "SELECT * FROM studinf";//04、获取执行者对象Statement stat = conn.createStatement();//05、执行Sql并获取结果ResultSet rs = stat.executeQuery(sql);//06、处理返回结果while(rs.next()) {System.out.println(rs.getInt("ID") + "\t" + rs.getString("Name"));}//07、关闭资源rs.close();stat.close();conn.close();}
(3)获取连接对象
(4)警告解决
当使用高版本的Mysql的时候会提示使用SSL的安全连接,配置好SSL安全连接,就不会出现此警告,但是配置SSL安全连接比较繁琐,而且性能会下降,所以一般不去配置,直接设置不用SSL即可
1.3.2Connection 数据库连接对象
(1)作用
(2)事务管理
事务回滚:当两条执行命令有任何一条出现问题都会执行事务回滚,将数据库的状态恢复到事务开始前的状态。
package JavaSE_JDBC;import java.sql.*;/*** @author Pengwei Qu* @Date 2024/8/17 22:05*/
/*Connection作用:获取连接对象/事务管理*/
public class JdbcAPI_Connection {public static void main(String[] args) throws ClassNotFoundException, SQLException {/*如果连接的是本机可简写省略:localhost:3306*/Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc_student?useSSL=false", "root", "123456");//03、定义SqlString sql = "SELECT * FROM studinf";String sql2 = "update studinf set Age = 31 Where ID=202403";//04、获取执行者对象Statement stat = conn.createStatement();ResultSet rs = null;try {//(1)开启事务conn.setAutoCommit(false);//05、执行Sql并获取结果//事务1:rs = stat.executeQuery(sql);while(rs.next()) {System.out.println(rs.getInt("ID") + "\t" + rs.getString("Name"));}//事务2int count = stat.executeUpdate(sql2);受影响的行数System.out.println(count);//(2)提交事务conn.commit();} catch (Exception e) {//(3)事务回滚conn.rollback();e.printStackTrace();}//07、关闭资源rs.close();stat.close();conn.close();}
}
1.3.3Statement
作用就是执行SQL语句。
1.3.4ResultSet
(1)作用
对于查询的结果如图中右边表格形式存在,列的初始值是1,可以传入getXX(1)参数来获取第一列,或者传入列名效果一样
boolean next() throws SQLException;String getString(int columnIndex) throws SQLException;
boolean getBoolean(int columnIndex) throws SQLException;
byte getByte(int columnIndex) throws SQLException;
int getInt(int columnIndex) throws SQLException;
double getDouble(int columnIndex) throws SQLException;
......
String getString(String columnLabel) throws SQLException;
......
(2)用法
//处理执行结果while(rs.next()) {System.out.println(rs.getInt("ID") + "\t" + rs.getString("Name"));}
(3)案例
实体类
package JavaSE_JDBC.model;/*** @author Pengwei Qu* @Date 2024/8/18 11:12*/
//实体类
public class jdbc_student {public int getID() {return ID;}public void setID(int ID) {this.ID = ID;}public String getName() {return Name;}public void setName(String name) {Name = name;}public String getGender() {return Gender;}public void setGender(String gender) {Gender = gender;}public int getAge() {return Age;}public void setAge(int age) {Age = age;}//对应于数据库表对象一行数据private int ID;private String Name;private String Gender;private int Age;@Overridepublic String toString() {return "jdbc_student{" +"ID=" + ID +", Name='" + Name + '\'' +", Gender='" + Gender + '\'' +", Age=" + Age +'}';}
}
JDBC连接数据库
package JavaSE_JDBC;import JavaSE_JDBC.model.jdbc_student;import java.sql.*;
import java.util.ArrayList;/*** @author Pengwei Qu* @Date 2024/8/18 10:58*/
//案例
public class jdbcAPI_ResultSet {public static void main(String[] args) throws ClassNotFoundException, SQLException {/*DriverManager*///01注册驱动//Class.forName("com.mysql.jdbc.Driver");//02获取连接对象Connection conn= DriverManager.getConnection("jdbc:mysql:///jdbc_student?useSSL=false", "root", "123456");/*Connection:1.获取执行SQL的对象2.事务管理*///03获取执行对象Statement statement = conn.createStatement();String sql = "SELECT * FROM studinf";/*ResultSet*///04执行Sql并获取结果ResultSet resultSet = statement.executeQuery(sql);//05返回结果处理ArrayList<jdbc_student> arrayList = new ArrayList<>();while (resultSet.next()){//创建实体类对象接收数据jdbc_student jdbcStudent = new jdbc_student();jdbcStudent.setID(resultSet.getInt("ID"));jdbcStudent.setName(resultSet.getString("Name"));jdbcStudent.setGender(resultSet.getString("Gender"));jdbcStudent.setAge(resultSet.getInt("Age"));//将对象存入集合arrayList.add(jdbcStudent);}//打印集合System.out.println(arrayList);//06释放资源conn.close();statement.close();resultSet.close();}
}
1.3.5PrepareStatement
(1)SQL注入
正常代码:输入的账号密码正确登陆成功
/*演示SQL注入问题*/@Testpublic void Test() throws SQLException {Scanner scanner = new Scanner(System.in);
/* System.out.println("输入账号");String user = scanner.nextLine();System.out.println("输入密码");String pwd = scanner.nextLine();*/String user="Jack";String pwd="123456";//01获取连接对象Connection conn= DriverManager.getConnection("jdbc:mysql:///jdbc_student?useSSL=false","root","123456");//02获取执行sql对象,执行SqlString Sql="Select * FROM login where User='"+user+"' and Pwd='"+pwd+"'";System.out.println("查看当前拼接好输入的账号信息的Sql语句"+Sql);Statement statement = conn.createStatement();ResultSet resultSet = statement.executeQuery(Sql);if (resultSet.next()){System.out.println("登录成功");}else {System.out.println("登录失败");}}
SQL注入问题:由于原来的Sql语句中存在字符串的现象,很容易被修改原来的语义
@Testpublic void Test2() throws SQLException {Scanner scanner = new Scanner(System.in);
/* System.out.println("输入账号");String user = scanner.nextLine();System.out.println("输入密码");String pwd = scanner.nextLine();*/String user="Jack";String pwd="'or'1'='1";//01获取连接对象Connection conn= DriverManager.getConnection("jdbc:mysql:///jdbc_student?useSSL=false","root","123456");//02获取执行sql对象,执行SqlString Sql="Select * FROM login where User='"+user+"' and Pwd='"+pwd+"'";System.out.println("查看当前拼接好输入的账号信息的Sql语句"+Sql);Statement statement = conn.createStatement();ResultSet resultSet = statement.executeQuery(Sql);if (resultSet.next()){System.out.println("登录成功");}else {System.out.println("登录失败");}}
(2)解决SQL注入
/*03解决Sql注入*/@Testpublic void Test3() throws SQLException {Scanner scanner = new Scanner(System.in);
/* System.out.println("输入账号");String user = scanner.nextLine();System.out.println("输入密码");String pwd = scanner.nextLine();*/String user="Jack";String pwd="'or'1'='1";//01获取连接对象Connection conn= DriverManager.getConnection("jdbc:mysql:///jdbc_student?useSSL=false","root","123456");//02获取执行sql对象,执行SqlString Sql="Select * FROM login where User=? and Pwd=?";//System.out.println("查看当前拼接好输入的账号信息的Sql语句"+Sql);PreparedStatement prSt = conn.prepareStatement(Sql);//设置?对应的值prSt.setString(1,user);prSt.setString(2,pwd);//03执行SqlResultSet resultSet = prSt.executeQuery();if (resultSet.next()){System.out.println("登录成功");}else {System.out.println("登录失败");}}
原理:
//设置?对应的值prSt.setString(1,user);prSt.setString(2,pwd);
(3)原理
- 可以预编译Sql提高性能,但是默认预编译功能是关闭的,需要再url中手动打开