二、Statement 、PreparedStatement 、CallableStatement
一、Statement执行步骤：
public class Demo {
public static void main(String[] args) {
}
private String url="jdbc:mysql://localhost:3306/day01";
private String user="root";
private String password="root";
@Test
public void test(){
Connection conn=null;
Statement stm=null;
try {
//1.创建驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象
conn=DriverManager.getConnection(url, user, password);
//3.创建Statement
stm=conn.createStatement();
//4.准备sql
String sql="CREATE TABLE worker(id INT ,NAME VARCHAR(20) ,gender VARCHAR(2))";
//5.发送sql语句，执行sql语句，得到返回结果
int count=stm.executeUpdate(sql);
System.out.println(count);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e);
} finally{
//7.关闭连接
if(stm!=null){
try {
stm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
}
二、Statement和PreparedStatement的对比
2.1、效率的比较：
2.2、Sql语句的风险问题：
案列：登陆模块，
输入用户名，密码！
注意，
要避免用户输入的恶意密码！
Mysql 数据库：
-- 创建数据库 CREATE DATABASE jdbc_demo; USE jdbc_demo -- 创建表 CREATE TABLE admin( id INT PRIMARY KEY AUTO_INCREMENT, userName VARCHAR(20), pwd VARCHAR(20) ) -- 插入数据 INSERT INTO admin(userName,pwd) VALUES('jack','12346') -- 查询查看 SELECT * FROM admin
PreparedStatement :在传入参数的时候，能够自动过滤掉注释。
package js; 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 Demo6 { private String url="jdbc:mysql://localhost:3306/jdbc_demo"; private String user="root"; private String password="root"; private Connection conn=null; private Statement stmt=null; private PreparedStatement pstmt=null; private ResultSet rs=null; //1 没有使用防止sql注入案例 @Test public void testLogin(){ //1.0 模拟登录的用户名，密码 String userName="tom"; String pwd=" ' or 1=1 -- "; //Sql 语句 String sql="select * from admin where userName='"+userName+"' and pwd='"+pwd+"' "; try { //1.1 加载驱动，创建连接 Class.forName("com.mysql.jdbc.Driver"); conn=DriverManager.getConnection(url,user,password); //1.2创建Statement stmt=conn.createStatement(); //1.3 执行查询 rs=stmt.executeQuery(sql); //1.4业务判断 if(rs.next()){ System.out.println("登录成功，编号为："+rs.getInt("id")); }else{ System.out.println("登录失败"); } } catch (Exception e) { throw new RuntimeException(e); } finally{ if(rs!=null) { try { rs.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } } //2防止sql注入案例 @Test public void testLogin1(){ //1.0 模拟登录的用户名，密码 String userName="tom"; String pwd=" ' or 1=1 -- "; //Sql 语句 String sql="select * from admin where userName=? and pwd=?"; try { //1.1 加载驱动，创建连接 Class.forName("com.mysql.jdbc.Driver"); conn=DriverManager.getConnection(url,user,password); //1.2创建Statement pstmt=conn.prepareStatement(sql); //设置占位符值 pstmt.setString(1, userName); pstmt.setString(2, pwd); //1.3 执行查询 rs=pstmt.executeQuery(); //1.4业务判断 if(rs.next()){ System.out.println("登录成功，编号为："+rs.getInt("id")); }else{ System.out.println("登录失败"); } } catch (Exception e) { throw new RuntimeException(e); } finally{ if(rs!=null) { try { rs.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } } }
结论：
使用预编译SQL语句的命令对象，好处：
- 避免了频繁sql拼接 (可以使用占位符)
- 可以防止sql注入
三、CallableStatement 操作存储过程
package js;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
/*
* 带有输入的参数 存储过程
*/
public class Demo5 {
@Test
public void test(){
Connection conn=null;
CallableStatement stmt=null;
ResultSet rs=null;
try {
//获取连接
conn=JdbcUtil.getConnection();
//准备sql语句
String sql="CALL pro_findById(?)";
//预编译
stmt=conn.prepareCall(sql);
//设置输入参数
stmt.setInt(1,1);
//发送参数
rs=stmt.executeQuery();
//遍历结果
while(rs.next()){
int id=rs.getInt("id");
String name=rs.getString("name");
System.out.println(id+" "+name);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e);
}finally{
JdbcUtil.close(conn, stmt, rs);
}
}
@Test
public void test1(){
Connection conn=null;
CallableStatement stmt=null;
ResultSet rs=null;
try {
//获取连接
conn=JdbcUtil.getConnection();
//准备sql语句
String sql="CALL pro_findById3(?,?)";
//预编译
stmt=conn.prepareCall(sql);
//设置输入参数
stmt.setInt(1,1);
//设置输出参数(注册输出参数)
/*
* 参数一：参数的位置
* 参数二：存储过程中的输出参数的jdbc的类型 java.sql.Types
*/
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
//发送参数
stmt.executeQuery(); //结果不是返回结果集的，而是在输出参数中接收的。
//得到输出参数的值
String result=stmt.getString(2);
System.out.println(result);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e);
}finally{
JdbcUtil.close(conn, stmt, rs);
}
}
}
有返回
package js;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class app_call {
private Connection conn=null;
private Statement stmt=null;
private PreparedStatement pstmt=null;
private CallableStatement cstmt=null;
private ResultSet rs=null;
String sql="CALL proc_login()";
@Test
public void testCall(){
try {
conn=JdbcUtil.getConnection();
cstmt=conn.prepareCall("CALL proc_login()");
rs=cstmt.executeQuery();
if(rs.next()){
String name=rs.getString("userName");
String pwd=rs.getString("pwd");
System.out.println(name+" "+pwd);
}
} catch (SQLException e) {
}finally{
}
}
}
