PreparedStatement对象
PreparedStatement对象可以防止SQL注入,并且效率更高,是预编译的意思。
插入数据的代码
package lesson03;
import lesson02.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsert {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
//创建连接
conn = JdbcUtils.getConnection();
//预编译SQL,先写SQL,不执行
//使用问好占位符
//预编译
String sql = "insert into users(id,`name`,password,email,birthday) values(?,?,?,?,?)";
st = conn.prepareStatement(sql);
//手动给参数赋值
st.setInt(1,6);
st.setString(2,"zhouhao333");
st.setString(3,"123456");
st.setString(4,"6@qq.com");
//注意点: sql.Date 是SQL包下面的Date
// util.Date 是java包下面的Date new Date().getTime()获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
//返回的I值是受影响的行数
int i = st.executeUpdate();
if (i>0){
System.out.println("插入数据成功");
}
JdbcUtils.release(conn,st,null);
}
}
删除数据的代码
package lesson03;
import lesson02.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
//创建连接
conn = JdbcUtils.getConnection();
String sql ="delete from users where id=?";
//SQL的执行对象
//预编译
st = conn.prepareStatement(sql);
//手动给问号复制
st.setInt(1,1);
//执行
//返回值是受影响的行数
int i = st.executeUpdate();
if (i>0){
System.out.println("删除数据成功");
}
//释放连接
JdbcUtils.release(conn,st,null);
}
}
修改数据的代码
package lesson03;
import lesson02.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
conn = JdbcUtils.getConnection();
//SQL语句
String sql = "update users set `name`=? where id=?";
//执行SQL语句的对象
st = conn.prepareStatement(sql);
st.setString(1,"lll");
st.setInt(2,2);
int i = st.executeUpdate();
if (i>0){
System.out.println("修改数据成功");
}
//释放连接
JdbcUtils.release(conn,st,null);
}
}
查询数据的代码
package lesson03;
import lesson02.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
//创建连接
conn = JdbcUtils.getConnection();
//SQL语句
String sql = "select * from users where id = ?";
//预编译SQL
st = conn.prepareStatement(sql);
//手动给SQL语句赋值
st.setInt(1,3);
//手动执行SQL语句
rs = st.executeQuery();
//输出查询到的信息
while (rs.next()){
System.out.println(rs.getString("id"));
System.out.println("名字:"+rs.getString("name"));
}
//关闭连接
JdbcUtils.release(conn,st,rs);
}
}
防止SQL注入的代码
使用PreparedStatement对象防止
package lesson03;
import lesson02.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SQL {
public static void main(String[] args) throws SQLException {
//正常执行查询操作
// login("wangwu","123456");
//sql注入的代码
login("' ' or 1=1","123456");
}
//登录业务
public static void login(String username,String password) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
//创建连接对象
conn = JdbcUtils.getConnection();
//SQL语句
String sql = "select * from users where `name`=? and `password`=?";
//创建SQL语句的执行对象
//prepareStatement可以防止SQL注入,它的本质是,把传进来的参数当作字符。
//如果存在转义字符,比如',会被直接转义
st = conn.prepareStatement(sql);
//手动给SQL语句赋值
st.setString(1,username);
st.setString(2,password);
//执行查询操作
rs = st.executeQuery();
//输出查询结果
while (rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
}
//关闭连接
JdbcUtils.release(conn,st,rs);
}
}
prepareStatement可以防止SQL注入,它的本质是,把传进来的参数当作字符。如果存在转义字符,比如’,会被直接转义。