pom.xml
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.example</groupId> <artifactId>jdbc</artifactId> <version>1.0-SNAPSHOT</version> <properties> <maven.compiler.source>17</maven.compiler.source> <maven.compiler.target>17</maven.compiler.target> </properties> <dependencies> <!-- Add your dependencies here --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.33</version> </dependency> </dependencies> <build> <finalName>jdbc</finalName> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-shade-plugin</artifactId><!--构建包含依赖的jar包--> <version>3.2.1</version> <executions> <execution> <phase>package</phase> <goals> <goal>shade</goal> </goals> <configuration> <transformers> <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer"> <mainClass>com.example.JDBCTest01</mainClass><!--指定启动类--> </transformer> </transformers> </configuration> </execution> </executions> </plugin> </plugins> </build> </project>
项目结构
增删改
package com.example;
import com.mysql.cj.jdbc.ConnectionImpl;
import java.sql.*;
import java.util.Properties;
import java.util.ResourceBundle;
/**
* @author hrui
* @date 2024/8/20 8:27
*/
public class JDBCTest01 {
public static void main(String[] args){
//1.注册驱动
// Driver driver = new com.mysql.cj.jdbc.Driver();
// DriverManager.registerDriver(driver);
//Class.forName("com.mysql.cj.jdbc.Driver");//这一步将上面两部做了 新版本这一步也可以省略
//2.获取连接 在JDBC 4.0之后,可以省略注册驱动的步骤 getConnection时候会读META-INF/services/java.sql.Driver中内容并注册驱动
Connection connection = null;
Statement statement = null;
try {
connection = DriverManager.getConnection("jdbc:mysql:/localhost:3306/jdbc", "root", "123456");
System.out.println(connection);//com.mysql.cj.jdbc.ConnectionImpl@548ad73b
//3.获取SQL执行对象
statement = connection.createStatement();
//String sql = "insert into user values(null,\"haha\",\"123456\")";
String sql = "insert into user values(null,\"哈哈\",\"123456\"),(null,\"haha3\",\"123456\")";
//4.执行sql(executeUpdate专门用于执行DML语句insert delete update 返回影响数据的记录条数)
int i = statement.executeUpdate(sql);
System.out.println(i);
} catch (SQLException e) {
e.printStackTrace();
}finally {
//关闭资源依次从小到大
if(statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
增删改 以配置文件方式 配置文件放在resources下
package com.example;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Properties;
/**
* 配置文件方式配置数据库连接
* @author hrui
* @date 2024/8/21 12:39
*/
public class JDBCTest02 {
public static void main(String[] args) {
Connection connection=null;
Statement statement=null;
try {
//使用类加载器加载 properties 文件
InputStream input = JDBCTest02.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties prop = new Properties();
prop.load(input);
String url = prop.getProperty("url");
String username = prop.getProperty("username");
String password = prop.getProperty("password");
//获取连接
connection = DriverManager.getConnection(url, username, password);
//获取执行对象
statement = connection.createStatement();
String sql="insert into user values(null,\"haha3\",\"456\")";
//执行sql
int count = statement.executeUpdate(sql);
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
}finally {
if(statement!=null){
try {
statement.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}
}
resources下创建jdbc.properties
url=jdbc:mysql://localhost:3306/jdbc
username=root
password=123456
基础查询操作
package com.example;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
/**
* 查询数据 需要处理结果集
* @author hrui
* @date 2024/8/21 14:06
*/
public class JDBCTest03 {
public static void main(String[] args) {
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
//使用类加载器加载 properties 文件
InputStream input = JDBCTest03.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties prop = new Properties();
prop.load(input);
String url = prop.getProperty("url");
String username = prop.getProperty("username");
String password = prop.getProperty("password");
connection = DriverManager.getConnection(url, username, password);
statement = connection.createStatement();
String sql="select * from user";
//int executeUpdate (insert/delete/update)
//ResultSet executeQuery (select)
resultSet = statement.executeQuery(sql);//专门执行DQL语句的方法
//注意:所有列都可以转String 类型 但是DECIMAL 或 NUMERIC 可能转换导致丢失精度
//还有一些特殊类型 可能导致转换不是预期
while (resultSet.next()){
//以查询出来的下标获取 注意 下标根据select xxx,xxx,xxx确定
// int id = resultSet.getInt(1);
// String name = resultSet.getString(2);
// String pw = resultSet.getString(3);
//按查询出来的列明获取 注意是查询结果的列明 如 select id, username as ua, password 那么不应该是username 而是ua
int id = resultSet.getInt("id");
String name = resultSet.getString("username");
String pw = resultSet.getString("password");
System.out.println(id+":"+name+":"+pw);
}
}catch (Exception e){
e.printStackTrace();
}finally {
if (resultSet!=null){
try {
resultSet.close();
}catch (Exception e){
e.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
}catch (Exception e){
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
}
}
演示Statement存在sql注入的问题
package com.example;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
/**
* 演示sql注入问题
* @author hrui
* @date 2024/8/21 15:33
*/
public class JDBCTest04 {
public static void main(String[] args) {
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
Scanner scanner = new Scanner(System.in);
System.out.println("输入用户名:");
String username = scanner.nextLine();
System.out.println("输入密码:");
String password = scanner.nextLine();
String sql = "select * from user where username='"+username+"' and password='"+password+"'";
System.out.println(sql);
//使用类加载器加载 properties 文件 获取 url username password
InputStream input = JDBCTest04.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties prop = new Properties();
prop.load(input);
connection = DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
if(resultSet.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
}catch (Exception e){
e.printStackTrace();
}finally {
if (resultSet!=null){
try {
resultSet.close();
}catch (Exception e){
e.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
}catch (Exception e){
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
}
}
SQL注入的原因
- 直接拼接:由于用户输入的数据直接拼接到 SQL 语句中,并被送入数据库引擎进行编译,恶意的 SQL 代码也参与了编译和执行过程,导致 SQL 注入攻击。
- 参与编译:用户输入的未经过滤或转义的数据参与了 SQL 语句的编译,导致了 SQL 注入风险。
解决SQL注入问题
Statement 数据库操作对象
PreparedStatement 预编译的数据库操作对象
public interface PreparedStatement extends Statement
解决SQL注入根本在于,用户提供的信息不参与SQL语句的编译过程,这样即使用户提供的信息包含SQL语句的关键字,但是没有参与编译,就会不起作用
PreparedStatement 的原理是:预先对SQL语句进行了编译
package com.example;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;
/**
* 解决SQL注入问题
* @author hrui
* @date 2024/8/21 15:50
*/
public class JDBCTest05 {
public static void main(String[] args) {
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
Scanner scanner = new Scanner(System.in);
System.out.println("输入用户名:");
String username = scanner.nextLine();
System.out.println("输入密码:");
String password = scanner.nextLine();
String sql = "select * from user where username=? and password=?";
//使用类加载器加载 properties 文件 获取 url username password
InputStream input = JDBCTest04.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties prop = new Properties();
prop.load(input);
connection = DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,username);//是字符串就set字符串 int就setInt
preparedStatement.setString(2,password);
resultSet = preparedStatement.executeQuery();//sql不需要在往里传 如果再传的话,会重新编译
if(resultSet.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
}catch (Exception e){
e.printStackTrace();
}finally {
if (resultSet!=null){
try {
resultSet.close();
}catch (Exception e){
e.printStackTrace();
}
}
if (preparedStatement!=null){
try {
preparedStatement.close();
}catch (Exception e){
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
}
}
对比Statement和PreparedStatement
1.Statement存在SQL注入风险
2.PreparedStatement预编译,Statement是编译一次执行一次,PreparedStatement编译一次 可执行多次,效率相对高.PreparedStatement会做类型安全检查(就是你setString 结果出入的是100 编译器报错)
package com.example;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Properties;
/**
* PrepareStatement 增删改
* @author hrui
* @date 2024/8/21 16:28
*/
public class JDBCTest06 {
public static void main(String[] args) {
Connection connection=null;
PreparedStatement preparedStatement=null;
try {
InputStream resourceAsStream = JDBCTest06.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties prop = new Properties();
prop.load(resourceAsStream);
connection = DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
preparedStatement = connection.prepareStatement("insert into user(username,password) values(?,?)");
preparedStatement.setString(1,"hrui");
preparedStatement.setString(2,"123456");
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
}catch (Exception e){
e.printStackTrace();
}finally {
if (preparedStatement!=null){
try {
preparedStatement.close();
}catch (Exception e){
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
}
}
JDBC事务机制 默认是自动提交
JDBC工具类封装
package com.example;
import java.sql.*;
import java.util.ResourceBundle;
/**
* @author hrui
* @date 2024/8/21 16:51
*/
public class DBUtil {
//静态变量:在类加载时候执行,并且是有先后顺序的.自上而下
private static ResourceBundle bundle=ResourceBundle.getBundle("resource1.jdbc");
//JDBC4开始 DriverManager.getConnection会读Mysql驱动包中的配置文件
//配置文件在META-INF/META-INF/services/java.sql.Driver
//private static String driver=bundle.getString("driver");
private static String url=bundle.getString("url");
private static String username=bundle.getString("username");
private static String password=bundle.getString("password");
// static{
// //注册驱动(注册却动只需要注册一次,放在静态代码块当中.DBUtil类加载的时候执行)
// try {
// //"com.mysql.jdbc.Driver"是连接数据库的驱动,不能写死,因为可能以后改成Oracle数据库
// //如果连接Oracle数据库的时候,还需要修改java代码,显然违背OCP开闭原则
// //OCP开闭原则:对扩展开发,对修改关闭.(什么是复合OCP呢?在进行功能扩展的时候,不需要修改java源代码)
// //Class.forName("com.mysql.cj.jdbc.Driver");
// //Class.forName(driver);
// DriverManager.getConnection(url,username, password);
// } catch (Exception e) {
// e.printStackTrace();
// }
// }
//获取数据库连接对象 抛异常,谁使用,谁负责
public static Connection getConnection() throws SQLException {
//JDBC4开始 DriverManager.getConnection会读Mysql驱动包中的配置文件
//配置文件在META-INF/META-INF/services/java.sql.Driver
Connection connection = DriverManager.getConnection(url, username, password);
return connection;
}
//关闭连接 释放资源
/**
* 释放资源
* @param conn 数据库连接对象
* @param st 执行对象
* @param rs 结果集对象
*/
public static void close(Connection conn, Statement st, ResultSet rs){
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
注意模糊查询时候的错误写法
行级锁 也被称为悲观锁 悲观锁概念:数据锁住了