PreparedStatement
基本介绍
1.PreparedStatement执行的SQL语句中的参数用 ? 表示,调用PreparedStatement对象的setXxx() 方法设置这些参数。setXxx()有两个参数,第一个参数是要设置SQL语句中的参数的索引从1开始,第二个是设置的SQL语句中的参数的值
例如:
2.调用executeQuery(),返回ResultSet对象
3.调用executeUpdate(),返回影响行数,int型 ,执行更新,包括增删改查
4.类图:
预处理的好处
1.不再使用+拼接SQL语句,减少语法错误
2.有效的解决了SQL注入问题
3.减少编译次数,效率较高
应用实例
前提:建立admin数据表
与Statement主要区别:
预处理查询
代码演示:
注意
executeQuery()方法在PreparedStatement和CallableStatement中使用时不能带参数。
package Jdbc.myjdbc;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;
/**
* @author zq
* 演示PrepareStatement的使用
*/
public class PrepareStatement_ {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
String admin_user;
String admin_pwd;
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名");
admin_user = scanner.nextLine();
System.out.println("请输入密码");
admin_pwd = scanner.nextLine();
//创建配置文件对象
Properties properties = new Properties();
//获取文件中的键值
properties.load(new FileInputStream("src\\Jdbc\\myjdbc\\mysql.properities"));
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
//1.注册驱动
Class.forName(driver);
//2.得到连接
Connection connection = DriverManager.getConnection(url, user, password);
//3.得到PreparedStatement
//3.1 组织SQL语句,?相当于占位符
String sql = "SELECT* FROM admin WHERE NAME =?AND pwd=?";
//preparedStatement对象实现了PreparedStatement接口的实现类的对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//3.2 给?赋值
preparedStatement.setString(1,admin_user);
preparedStatement.setString(2,admin_pwd);
//4.执行SQL语句,查询使用executeQuery
//如果执行的是dml(增删改)用executeUpdate
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
System.out.println("登陆成功");
}else{
System.out.println("登陆失败");
}
resultSet.close();
preparedStatement.close();
connection.close();
}
}
预处理DML
代码演示:
注意:进行不同的dml语句操作时要更改==setXxx()==方法的参数
package Jdbc.myjdbc;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;
/**
* @author zq
*/
public class PreparedStatementDML_ {
public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
String admin_user;
String admin_pwd;
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名");
admin_user = scanner.nextLine();
//System.out.println("请输入密码");
//admin_pwd = scanner.nextLine();
//创建配置文件对象
Properties properties = new Properties();
//获取文件中的键值
properties.load(new FileInputStream("src\\Jdbc\\myjdbc\\mysql.properities"));
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
//1.注册驱动
Class.forName(driver);
//2.得到连接
Connection connection = DriverManager.getConnection(url, user, password);
//3.得到PreparedStatement
//3.1 组织SQL语句,?相当于占位符
//添加记录
//String sql = "insert into admin values(?,?)";
//修改记录
//String sql = "update admin set pwd =? where name =?";
//删除记录
String sql = "delete from admin where name =?";
//preparedStatement对象实现了PreparedStatement接口的实现类的对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//3.2 给?赋值
preparedStatement.setString(1,admin_user);
//preparedStatement.setString(2,admin_user);
//4.执行dml语句,使用executeUpdate 返回影响行数
int rows = preparedStatement.executeUpdate();
System.out.println(rows > 0 ?"成果":"失败");
preparedStatement.close();
connection.close();
}
}