MySQL三十三:PrepareStatement对象
PrepareStatement 可以防止SQL注入,并且效率高!
1、新增
package lesson03;
import lesson02.utils.JdbcUtils;
import java.sql.*;
public class TestInsert {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st=null;
ResultSet rs=null;
try {
// Connection connection = JdbcUtils.getConnection();
conn = JdbcUtils.getConnection();
//PreparedStatement preparedStatement = conn.prepareStatement();
//使用?占位符代替参数
String sql="insert into users (id,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql); //预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,4); //id
st.setString(2,"xiaoliu");
st.setString(3,"123456");
st.setString(4,"30666864@qq.com");
// 注意点 sql.Date 数据库 java.sql.Date()
// util.Date JAVA new Date().getTime()
st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("插入成功!!!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
2、删除
package lesson03;
import lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st=null;
ResultSet rs=null;
try {
// Connection connection = JdbcUtils.getConnection();
conn = JdbcUtils.getConnection();
//PreparedStatement preparedStatement = conn.prepareStatement();
//使用?占位符代替参数
String sql="DELETE FROM users WHERE id=?";
st = conn.prepareStatement(sql); //预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,4);
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("删除成功!!!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3、更新
package lesson03;
import lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st=null;
ResultSet rs=null;
try {
// Connection connection = JdbcUtils.getConnection();
conn = JdbcUtils.getConnection();
//PreparedStatement preparedStatement = conn.prepareStatement();
//使用?占位符代替参数
String sql="update users set `name`=? where id=?";
st = conn.prepareStatement(sql); //预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setString(1,"王五");
st.setInt(2,2);
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("更新成功!!!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
4、查询
package lesson03;
import lesson02.utils.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) {
Connection conn =null;
PreparedStatement st=null;
ResultSet rs=null;
try {
// Connection connection = JdbcUtils.getConnection();
conn = JdbcUtils.getConnection();
//PreparedStatement preparedStatement = conn.prepareStatement();
//使用?占位符代替参数
String sql="select * from users where id=?";
st = conn.prepareStatement(sql); //预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,1);
rs=st.executeQuery(); //执行
if (rs.next()){
System.out.println(rs.getString("name"));
}
} catch (
SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
5、防止SQL注入
package lesson02.utils;
import java.sql.*;
public class SQL注入 {
public static void main(String[] args) {
//login("xiaochen","123456"); //正常登陆 正常输出
//login("'or'1=1 ","'or'1=1"); //不正常输出,结果是全部用户名和密码
login("xiaochen","123456");
}
public static void login(String username,String password){
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn = JdbcUtils.getConnection(); //获取数据连接
//prepareStatement 防止SQL 注入的本质 把传递进来的参数当作字符
// 假设其中存在转义字符,直接忽略 如'' 会直接被转义掉
String sql="select * from users where `name`=? and `password` =?";
st=conn.prepareStatement(sql);
st.setString(1,username);
st.setString(1,password);
rs=st.executeQuery();
while(rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
System.out.println("=======================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}