本文内容大多基于官方文档和网上前辈经验总结,经过个人实践加以整理积累,仅供参考。
1 准备数据表(以MySQL数据库示例)
2 PreparedStatement 示例代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class JDBCTest {
@Test
public void test() {
Connection connection = null;
PreparedStatement preparedStatement = null;
String driverClass = "com.mysql.jdbc.Driver";
// MySQL高版本(5.5.45+ | 5.6.26+ | 5.7.6+)需要在JDBC URL中指明是否建立SSL连接,否则会出现警告
String jdbcUrl = "jdbc:mysql:///test?useSSL=false";
String user = "root";
String password = "123456";
try {
connection = getConnection(driverClass, jdbcUrl, user, password);
// SQL指明占位符
String sql = "INSERT INTO product (label, price, manufacturer) VALUES (?, ?, ?)";
// 生成PreparedStatement对象时需要传入SQL
preparedStatement = connection.prepareStatement(sql);
// 为占位符赋值,第一个参数Index是占位符序号,从1开始
preparedStatement.setString(1, "iPhone 7 128G");
preparedStatement.setDouble(2, 6688.00);
preparedStatement.setString(3, "Apple");
preparedStatement.executeUpdate();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
release(null, preparedStatement, connection);
}
}
private Connection getConnection(String driverClass, String jdbcUrl, String user, String password)
throws ClassNotFoundException, SQLException {
Class.forName(driverClass);
return DriverManager.getConnection(jdbcUrl, user, password);
}
private void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3 优化示例代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class JDBCTest {
@Test
public void test() {
String sql = "INSERT INTO product (label, price, manufacturer) VALUES (?, ?, ?)";
insertProduct(sql, "iPhone 4s", 1000, "FOXCONN");
insertProduct(sql, "iPhone 5s", 2000, "FOXCONN");
insertProduct(sql, "iPhone 6s", 3000, "FOXCONN");
}
public void insertProduct(String sql, Object ... params) {
Connection connection = null;
PreparedStatement preparedStatement = null;
String driverClass = "com.mysql.jdbc.Driver";
// MySQL高版本(5.5.45+ | 5.6.26+ | 5.7.6+)需要在JDBC URL中指明是否建立SSL连接,否则会出现警告
String jdbcUrl = "jdbc:mysql:///test?useSSL=false";
String user = "root";
String password = "123456";
try {
connection = getConnection(driverClass, jdbcUrl, user, password);
// 生成PreparedStatement对象时需要传入SQL
preparedStatement = connection.prepareStatement(sql);
// 为占位符赋值,第一个参数Index是占位符序号,从1开始
for (int i = 1; i <= params.length; i++) {
preparedStatement.setObject(i, params[i - 1]);
}
preparedStatement.executeUpdate();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
release(null, preparedStatement, connection);
}
}
private Connection getConnection(String driverClass, String jdbcUrl, String user, String password)
throws ClassNotFoundException, SQLException {
Class.forName(driverClass);
return DriverManager.getConnection(jdbcUrl, user, password);
}
private void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4 PreparedStatement 防止 SQL 注入
4.1 SQL 注入示例
准备数据表 admin,插入一条管理员账号密码信息用于登录
测试SQL注入的代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class JDBCTest {
@Test
public void test() {
login("ANY STRING' OR '1=1", "ANY STRING' OR '1=1");
}
public void login(String adminAccount, String adminPassword) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
String driverClass = "com.mysql.jdbc.Driver";
String jdbcUrl = "jdbc:mysql:///test?useSSL=false";
String user = "root";
String password = "123456";
String sql = "SELECT * FROM admin WHERE username='" + adminAccount + "' AND password='" + adminPassword + "'";
try {
connection = getConnection(driverClass, jdbcUrl, user, password);
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
System.out.println("OK");
} else {
System.out.println("REJECT");
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
release(resultSet, statement, connection);
}
}
private Connection getConnection(String driverClass, String jdbcUrl, String user, String password)
throws ClassNotFoundException, SQLException {
Class.forName(driverClass);
return DriverManager.getConnection(jdbcUrl, user, password);
}
private void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4.2 改写为 PreparedStatement 后将得到完全不同的执行结果
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class JDBCTest {
@Test
public void test() {
login("ANY STRING' OR '1=1", "ANY STRING' OR '1=1");
}
public void login(String adminAccount, String adminPassword) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
String driverClass = "com.mysql.jdbc.Driver";
String jdbcUrl = "jdbc:mysql:///test?useSSL=false";
String user = "root";
String password = "123456";
String sql = "SELECT * FROM admin WHERE username=? AND password=?";
try {
connection = getConnection(driverClass, jdbcUrl, user, password);
statement = connection.prepareStatement(sql);
statement.setString(1, adminAccount);
statement.setString(2, adminPassword);
resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
System.out.println("OK");
} else {
System.out.println("REJECT");
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
release(resultSet, statement, connection);
}
}
private Connection getConnection(String driverClass, String jdbcUrl, String user, String password)
throws ClassNotFoundException, SQLException {
Class.forName(driverClass);
return DriverManager.getConnection(jdbcUrl, user, password);
}
private void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}