PreparedStatement提供了可以预编译的sql执行策略,因此可以通过面向对象的方法,通过占位符“?”来预先告知传入参数的位置,然后调用设置参数的方法setXXX(int ParameterIndex,String ParameterValue)来设定参数的值,最后调用executeUpdate()或者executeQuery()来执行修改或者查询。
PreparedStatement和Statement的区别:
1.语法不同
PreparedStatement支持占位符,预编译
Statement只能执行静态sql
2.PreparedStatement支持使用缓冲区
支持SQL缓冲区的数据库:oracle,SQL Server
不支持SQL缓冲区的数据库:mysql
结论:mysql数据库不支持PreparedStatement优化!
3.安全机制
PreparedStatement可以防止SQl注入攻击
常见的sql注入:
查询注入:
private String name = "tom' or 1=1 -- ";
private String password = "123456";
String sql = "SELECT * FROM users WHERE NAME='"+name+"' AND PASSWORD='"+password+"' ;";
使用Statement执行查询之后sql语句会变成:
SELECT * FROM users WHERE NAME='tom' OR 1=1 -- AND PASSWORD='123456' ;
注意:参数“OR 1=1 –”将强制执行查询,并忽略后面的password参数
这句sql最终变成了:
SELECT * FROM users WHERE 1=1 ;
返回的结果将始终成立
恶意删除注入:
DELETE FROM users WHERE id=1 OR 1=1;
这一句sql相当于执行DELETE FROM users WHERE 1=1;
将清空整个user表,造成重大损失
增删改查代码示例:
/**
* 使用PreparedStatement进行预编译
* @author llj
*
*/
public class Demo1 {
/**
* 增加
*/
@Test
public void testAdd() {
Connection conn = null;
PreparedStatement pstmt =null;
try {
// 1.获取连接
conn = JdbcUtil.getConnection();
// 2.准备sql
String sql = "insert into student(name,gender) values (?,?)";
// 3.执行预编译sql(检查语法)
pstmt = conn.prepareStatement(sql);
// 4.设置参数
pstmt.setString(1, "hhhaa");
pstmt.setString(2, "男");
// 5.发送参数执行sql
int count = pstmt.executeUpdate();
System.out.println("本次执行影响了"+count+"行");
} catch (Exception e) {
e.printStackTrace();
}finally{
if(pstmt != null){
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
/**
* 删除
*/
@Test
public void testDelete() {
Connection conn = null;
PreparedStatement pstmt =null;
try {
// 1.获取连接
conn = JdbcUtil.getConnection();
// 2.准备sql
String sql = "delete from student where id=?";
// 3.执行预编译sql(检查语法)
pstmt = conn.prepareStatement(sql);
// 4.设置参数
pstmt.setString(1, "4");
// 5.发送参数执行sql
int count = pstmt.executeUpdate();
System.out.println("本次执行影响了"+count+"行");
} catch (Exception e) {
e.printStackTrace();
}finally{
if(pstmt != null){
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
/**
* 修改
*/
@Test
public void testUpdate() {
Connection conn = null;
PreparedStatement pstmt =null;
try {
// 1.获取连接
conn = JdbcUtil.getConnection();
// 2.准备sql
String sql = "update student set name=? where id=?";
// 3.执行预编译sql(检查语法)
pstmt = conn.prepareStatement(sql);
// 4.设置参数
pstmt.setString(1, "王老五");
pstmt.setString(2, "3");
// 5.发送参数执行sql
int count = pstmt.executeUpdate();
System.out.println("本次执行影响了"+count+"行");
} catch (Exception e) {
e.printStackTrace();
}finally{
if(pstmt != null){
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
/**
* 查询
*/
@Test
public void testQuery() {
Connection conn = null;
PreparedStatement pstmt =null;
try {
// 1.获取连接
conn = JdbcUtil.getConnection();
// 2.准备sql
String sql = "select name from student";
// 3.执行预编译sql(检查语法)
pstmt = conn.prepareStatement(sql);
// 4.设置参数
// pstmt.setString(1, "3");
// 5.发送参数执行sql
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
String name = rs.getString("name");
System.out.println("name:"+name);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
if(pstmt != null){
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
}